if exists(select * from sys.databases where name = 'DB_ACTIVITY')
    DROP database DB_ACTIVITY
GO
CREATE DATABASE DB_ACTIVITY
GO
USE DB_ACTIVITY
GO
CREATE TABLE AUDIT_LOG (
	LOG_ID INT NOT NULL DEFAULT 0,
	LOG_TABLE nvarchar(30) NOT NULL DEFAULT '',
	LOG_USER INT NOT NULL DEFAULT 0,
	LOG_DATE  datetime not null default GETDATE(),
	LOG_DESC NTEXT null	,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0
)
go
CREATE INDEX IX_AUDIT_LOG on AUDIT_LOG (LOG_ID,LOG_TABLE) 
GO
CREATE TABLE ORGANIZATION (
	ORG_ID INT IDENTITY (0,1)  ,
	ORG_CODE nvarchar(10) NOT NULL DEFAULT '',
	ORG_OPTIONS ntext null,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_ORGANIZATION PRIMARY KEY (ORG_ID)
)
GO
CREATE INDEX IX_ORGANIZATION on ORGANIZATION (ORG_CODE) 
GO
insert into ORGANIZATION (ORG_CODE,ORG_OPTIONS) values('DC','<?xml version="1.0" encoding="utf-8"?><configuration><contact><name1>Default Company</name1><name2></name2><address1></address1><address2></address2><address3></address3><address4></address4><address5></address5><telno></telno><faxno></faxno><email></email><website></website></contact><setting><failcount>3</failcount><refno></refno><license></license></setting></configuration>')
go
CREATE TABLE USERS (
	USER_ID INT IDENTITY(1,1),
	USER_NAME nvarchar(20) NOT NULL DEFAULT '',
	USER_FULL_NAME nvarchar(50) NOT NULL DEFAULT '',
	USER_GROUP INT NOT NULL DEFAULT '',
	USER_EMAIL nvarchar(80) NOT NULL DEFAULT '',
	USER_PWD1 nvarchar(40) NOT NULL DEFAULT '',
	USER_PWD2 nvarchar(40) NOT NULL DEFAULT '',
	USER_LAST_LOGIN datetime NOT NULL DEFAULT GETDATE(),
	USER_START_DATE datetime NOT NULL DEFAULT GETDATE(),
	USER_EXPIRY_DATE datetime NOT NULL DEFAULT GETDATE(),
	USER_SUBTITUTE_ID INT NOT NULL DEFAULT 0,
	USER_DEPT_ID INT NOT NULL DEFAULT 0,
	USER_CHANGE_PWD bit NOT NULL DEFAULT 0,
	USER_FAIL_COUNT TINYINT NOT NULL DEFAULT 0,
	USER_COMMENTS ntext NULL,
	BLOCKED bit NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_USERS PRIMARY KEY ([USER_ID])
)
GO
CREATE INDEX IX_USERS on USERS (ORG_ID,USER_NAME) 
GO
INSERT INTO USERS (USER_NAME,USER_FULL_NAME,USER_GROUP,USER_START_DATE,USER_EXPIRY_DATE,USER_LAST_LOGIN,ORG_ID,USER_PWD1,USER_PWD2,USER_COMMENTS, USER_SUBTITUTE_ID, USER_DEPT_ID) VALUES ('admin','administrator',1,'2000-1-1','2999-12-31 23:59:59','1969-12-31 23:59:59',0,'b5ca39df335d882848b409bc221ac465','f210389b91d7a02088b363173a7390949267aa53','',0,0)
go
CREATE TABLE USERS_ABSENT_HISTORY (
	USER_ID INT NOT NULL DEFAULT 0,
	START_DATE datetime NOT NULL DEFAULT GETDATE(),
	END_DATE datetime NOT NULL DEFAULT GETDATE()
)
GO
CREATE TABLE USERS_RESET_PASSWORD_HISTORY (
	USER_ID INT NOT NULL DEFAULT 0,
	USER_ID_TARGET INT NOT NULL DEFAULT 0,
	RESET_TYPE INT NOT NULL DEFAULT 0,
	RESET_DATE datetime NOT NULL DEFAULT GETDATE()
)
GO
CREATE TABLE USER_GROUP (
	GROUP_ID INT IDENTITY(1,1),
	GROUP_DESC nvarchar(30) NOT NULL DEFAULT '',
	GROUP_RIGHTS NTEXT NULL,
	BLOCKED bit NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_USER_GROUP PRIMARY KEY (GROUP_ID)
)
GO
insert into USER_GROUP (GROUP_DESC,GROUP_RIGHTS) values('Administrator','c5dca9967351a5dde1d13275c0689bc15f84d6700e4a5b49f0676d49e52440c3491f0ea017a7db77aa9093a92e2dcc8a2e5c041cbeed1b70f26dd66b0054b73825432035b8fe629ec06b7cbd7cd6094d423363fb904de19ed47e8d24bc97145f')
CREATE TABLE COMPANY (
	COY_ID INT IDENTITY (1,1),
	COY_DESC NVARCHAR(60) NOT NULL DEFAULT '',
	COY_OPTIONS ntext NULL,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_COMPANY PRIMARY KEY (COY_ID)
)
GO
CREATE TABLE BANK (
	BANK_ID INT IDENTITY(1,1),
	BANK_DESC nvarchar(30) NOT NULL DEFAULT '',
	BANK_FORMAT ntext NULL,
	BANK_TYPE INT NOT NULL DEFAULT 0,
	BANK_REF nvarchar(20) not null default '',
	BANK_ACCT_NO nvarchar(20) NOT NULL DEFAULT '',
	BANK_FILE nvarchar(50) not null default '',
	BLOCKED bit NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_BANK PRIMARY KEY (BANK_ID)
)
GO
CREATE TABLE DEPARTMENT (
	DEPT_ID INT IDENTITY (1,1)  ,
	DEPT_DESC nvarchar(30) NOT NULL DEFAULT '',
	DEPT_REF nvarchar(20) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_ID)
)
GO
CREATE TABLE DEPARTMENT_INTER_PERMISSION (
        DEPT_ID INT NOT NULL DEFAULT 0,
        DEPT_INTER_PERMISSION_ID INT NOT NULL DEFAULT 0,
	CONSTRAINT PK_DEPARTMENT_INTER_PERMISSION PRIMARY KEY (DEPT_ID, DEPT_INTER_PERMISSION_ID)
)
/*ACTIVITY WEB*/
CREATE TABLE TAXES_TYPE (
	TAXES_TYPE_ID INT IDENTITY (1,1),
	TAXES_TYPE_CODE nvarchar(50) NOT NULL DEFAULT '',
	TAXES_TYPE_DESC nvarchar(255) NOT NULL DEFAULT '',
	TAXES_TYPE_DEFAULT_PERCENTAGE decimal(18,2) NOT NULL DEFAULT 0,
	TAXES_TYPE_GL_CODE nvarchar(30) NOT NULL DEFAULT '',
	COY_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_TAXES_TYPE PRIMARY KEY (TAXES_TYPE_ID)
)
GO

CREATE TABLE TAXES_TYPE_PERCENTAGE (
	[TAXES_TYPE_ID] [int] NOT NULL,
	[TAXES_TYPE_PERCENTAGE] [decimal](18, 2) NOT NULL,
	[TAXES_TYPE_START_DATE] [datetime] NOT NULL
)
GO
CREATE TABLE CUSTOMER (
	CUSTOMER_ID INT IDENTITY(1,1),
	CUSTOMER_NAME nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_CODE nvarchar(50) NOT NULL DEFAULT '',
	CUSTOMER_EMAIL nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_WEB nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_TELEPHONE nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_FAX nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_ADDR1 nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_ADDR2 nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_ADDR3 nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_ADDR4 nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_ADDR5 nvarchar(255) NOT NULL DEFAULT '',
        CUSTOMER_STATUS INT NOT NULL DEFAULT 1,
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
)
GO
CREATE TABLE CUSTOMER_CONTACT (
	CUSTOMER_CONTACT_ID INT IDENTITY,
	CUSTOMER_ID INT NOT NULL DEFAULT 0,
	CUSTOMER_CONTACT_NAME nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_CONTACT_EMAIL_ADDRESS nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_CONTACT_TELEPHONE nvarchar(255) NOT NULL DEFAULT '',
	CUSTOMER_CONTACT_FAX nvarchar(255) NOT NULL DEFAULT '',
	CONSTRAINT PK_CUSTOMER_CONTACT PRIMARY KEY (CUSTOMER_CONTACT_ID)
)
GO
CREATE TABLE PROJECT (
	PROJECT_ID INT IDENTITY,
	PROJECT_TITLE nvarchar(255) NOT NULL DEFAULT '',
	PROJECT_DESC nvarchar(255) NOT NULL DEFAULT '',
	PROJECT_CODE nvarchar(50) NOT NULL DEFAULT '',
	PROJECT_TOTAL_MANDAYS decimal(18,4) NOT NULL DEFAULT 0,
	CUSTOMER_ID INT NOT NULL DEFAULT 0,
        PROJECT_STATUS INT NOT NULL DEFAULT 1,
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_PROJECT PRIMARY KEY (PROJECT_ID)
)
GO
CREATE TABLE PROJECT_MANDAYS_BILL (
	PROJECT_ID INT NOT NULL DEFAULT 0,
	PROJECT_INVOICE_NO nvarchar(255) NOT NULL DEFAULT '',
	PROJECT_INVOICE_BILLED decimal(18,4) NOT NULL DEFAULT 0,
	PROJECT_INVOICE_PAID decimal(18,4) NOT NULL DEFAULT 0
)
GO
CREATE TABLE PROJECT_PHASE (
	PROJECT_PHASE_ID INT IDENTITY,
	PROJECT_ID INT NOT NULL DEFAULT 0,
	PROJECT_PHASE_DESC nvarchar(255) NOT NULL DEFAULT '',
	PROJECT_PHASE_REF nvarchar(100) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_PROJECT_PHASE PRIMARY KEY (PROJECT_PHASE_ID)
)
GO
CREATE TABLE PROJECT_PHASE_USER (
	PROJECT_PHASE_ID INT NOT NULL DEFAULT 0,
	PROJECT_PHASE_USER_USER_ID INT NOT NULL DEFAULT 0,
	PROJECT_PHASE_USER_ORDER INT NOT NULL DEFAULT 0
)
GO
CREATE TABLE PROJECT_PHASE_USER_RIGHTS (
	PROJECT_PHASE_ID INT NOT NULL DEFAULT 0,
	PROJECT_PHASE_USER_USER_RIGHTS_ID INT NOT NULL DEFAULT 0,
	PROJECT_PHASE_USER_RIGHTS_ORDER INT NOT NULL DEFAULT 0
)
GO
CREATE TABLE EXPENSE_ITEM (
	EXPENSE_ITEM_ID INT IDENTITY,
	EXPENSE_ITEM_DESC nvarchar(255) NOT NULL DEFAULT '',
	EXPENSE_ITEM_CODE nvarchar(3) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_EXPENSE_ITEM PRIMARY KEY (EXPENSE_ITEM_ID)
)
GO
CREATE TABLE LOCATION (
	LOCATION_ID INT IDENTITY,
	LOCATION_DESC nvarchar(255) NOT NULL DEFAULT '',
	LOCATION_CODE nvarchar(3) NOT NULL DEFAULT '',
	CUSTOMER_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_LOCATION_ID PRIMARY KEY (LOCATION_ID)
)
GO
CREATE TABLE ACTIVITY_TYPE (
	ACTIVITY_TYPE_ID INT IDENTITY,
	ACTIVITY_TYPE_DESC nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_TYPE_CODE nvarchar(3) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_ACTIVITY_TYPE_ID PRIMARY KEY (ACTIVITY_TYPE_ID)
)
GO
CREATE TABLE ACTIVITY_STATUS (
	ACTIVITY_STATUS_ID INT IDENTITY,
	ACTIVITY_STATUS_DESC nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_STATUS_CODE nvarchar(3) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_ACTIVITY_STATUS_ID PRIMARY KEY (ACTIVITY_STATUS_ID)
)
GO
CREATE TABLE PRODUCT (
	PRODUCT_ID INT IDENTITY,
	PRODUCT_DESC nvarchar(255) NOT NULL DEFAULT '',
	PRODUCT_CODE nvarchar(3) NOT NULL DEFAULT '',
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE datetime NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE datetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
)
GO
CREATE TABLE CURRENCY (
	CUR_ID INT IDENTITY(1,1),
	CUR_DESC NVARCHAR(30) NOT NULL DEFAULT '',
	CUR_CODE NVARCHAR(3) NOT NULL DEFAULT '',
	CUR_CONVERT_RATE decimal(18,6) NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_CURRENCY PRIMARY KEY (CUR_ID)
)
GO
CREATE TABLE CURRENCY_RATE (
	CUR_FROM_ID INT NOT NULL DEFAULT 0,
	CUR_TO_ID INT NOT NULL DEFAULT 0,
	CUR_CONVERSION_RATE decimal(18,6) NOT NULL DEFAULT 0,
	CUR_CONVERSION_START_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CUR_DESC NVARCHAR(30) NOT NULL DEFAULT '',
	CUR_CODE NVARCHAR(3) NOT NULL DEFAULT '',
	CONSTRAINT PK_CURRENCY_RATE PRIMARY KEY (CUR_FROM_ID, CUR_TO_ID, CUR_CONVERSION_START_DATE)
)
GO
INSERT INTO CURRENCY (CUR_CODE,CUR_DESC,ORG_ID) VALUES ('SGD','Singpore Dollars',0)
GO
INSERT INTO CURRENCY (CUR_CODE,CUR_DESC,ORG_ID) VALUES ('USD','US Dollars',0)
GO
INSERT INTO CURRENCY_RATE (CUR_FROM_ID, CUR_TO_ID, CUR_CONVERSION_RATE, CUR_CONVERSION_START_DATE) VALUES (2, 1, 1.35, '1900-01-01 00:00:00.000')
GO
/*ACTIVITY PORTAL*/
CREATE TABLE ACTIVITY_HEADER (
	ACTIVITY_ID INT IDENTITY (1,1),
	ACTIVITY_TITLE nvarchar(100) NOT NULL DEFAULT '',
	ACTIVITY_CODE nvarchar(50) NOT NULL DEFAULT '',
	ACTIVITY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	ACTIVITY_DESC nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_TYPE INT NOT NULL DEFAULT 0,
	ACTIVITY_BILL_TYPE INT NOT NULL DEFAULT 0,
	ACTIVITY_SITE_TYPE INT NOT NULL DEFAULT 0,
	ACTIVITY_REF nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_PROJECT_STATUS INT NOT NULL DEFAULT 0,
	ACTIVITY_STATUS INT NOT NULL DEFAULT 0,
	ACTIVITY_USER INT NOT NULL DEFAULT 0,
	ACTIVITY_DEDUCT INT NOT NULL DEFAULT 0,
	ACTIVITY_SIGNED INT NOT NULL DEFAULT 0,
	PROJECT_ID INT NOT NULL DEFAULT 0,
	CUSTOMER_ID INT NOT NULL DEFAULT 0,
	CUSTOMER_CONTACT_ID INT NOT NULL DEFAULT 0,
	LOCATION_ID INT NOT NULL DEFAULT 0,
	TIME_TRACKER_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	TIME_TRACKER_TOTAL_OVERRIDE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	EXPENSE_TRACKER_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	COY_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_ACTIVITY_HEADER PRIMARY KEY (ACTIVITY_ID)
)
GO

CREATE TABLE ACTIVITY_EXPENSE_DETAIL (
	ACTIVITY_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_EXPENSE_LINE_NO INT NOT NULL DEFAULT 0,
	EXPENSE_ITEM_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_EXPENSE_TYPE INT NOT NULL DEFAULT 0,
	ACTIVITY_EXPENSE_RECEIPT nvarchar(255) NOT NULL DEFAULT '',
	TAX DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	TAX_ID INT NOT NULL DEFAULT 0,
	AMOUNT DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	AMOUNT_NET DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	ACTIVITY_EXPENSE_DESC nvarchar(255) NOT NULL DEFAULT '',
	CONSTRAINT PK_ACTIVITY_EXPENSE_DETAIL PRIMARY KEY (ACTIVITY_ID, ACTIVITY_EXPENSE_LINE_NO)
)
GO

CREATE TABLE ACTIVITY_TIMING_DETAIL (
	ACTIVITY_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_TIMING_LINE_NO INT NOT NULL DEFAULT 0,
	ACTIVITY_TIMING_DESC ntext NOT NULL DEFAULT '',
	START_TIME nvarchar(255) NOT NULL DEFAULT '',
	END_TIME nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_TIMING_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	ACTIVITY_TIMING_END_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	ACTIVITY_TIMING_PHASE nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_TIMING_BREAK DECIMAL(18,4) NOT NULL DEFAULT 0,
	TOTAL_TIME DECIMAL(18,4) NOT NULL DEFAULT 0,
	TOTAL_AFT_BREAK DECIMAL(18,4) NOT NULL DEFAULT 0,
	ACTIVITY_TIMING_TOTAL_OVERRIDE DECIMAL(18,4) NOT NULL DEFAULT 0,
	CONSTRAINT PK_ACTIVITY_TIMING_DETAIL PRIMARY KEY (ACTIVITY_ID,ACTIVITY_TIMING_LINE_NO)
)
GO

CREATE TABLE ACTIVITY_DOCUMENT (
	ACTIVITY_DOC_ID INT IDENTITY (1,1),
	ACTIVITY_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_DOC_SAVED_NAME nvarchar(255) NOT NULL DEFAULT '',
	ACTIVITY_DOC_ORI_NAME nvarchar(255) NOT NULL DEFAULT ''
)
GO

CREATE TABLE TASK_PLAN (
	TASK_PLAN_ID INT IDENTITY(1,1),
	TASK_PLAN_DESC NVARCHAR(255) NOT NULL DEFAULT '',
	TASK_PLAN_SCHEDULE_TYPE INT NOT NULL DEFAULT 0,
	TASK_PLAN_START_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	TASK_PLAN_END_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	TASK_PLAN_TOLERANCE INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar(30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_TASK_PLAN PRIMARY KEY (TASK_PLAN_ID)
)
GO

CREATE TABLE TASK_PLAN_SCHEDULE (
	TASK_PLAN_ID INT NOT NULL DEFAULT 0,
	TASK_PLAN_SCHEDULE_TIME INT NOT NULL DEFAULT 0
)
GO

CREATE TABLE TASK_PLAN_PARTICIPANT (
	TASK_PLAN_ID INT NOT NULL DEFAULT 0,
	TASK_PLAN_PARTICIPANT_TYPE INT NOT NULL DEFAULT 0,
	TASK_PLAN_USER_ID INT NOT NULL DEFAULT 0,
	TASK_PLAN_PARTICIPANT_NAME NVARCHAR(255) NOT NULL DEFAULT '',
	TASK_PLAN_PARTICIPANT_EMAIL NVARCHAR(255) NOT NULL DEFAULT ''
)
GO

CREATE TABLE TASK_SCHEDULE (
	TASK_PLAN_ID INT NOT NULL DEFAULT 0,
	TASK_SCHEDULE_ID INT IDENTITY (1,1),
	TASK_SCHEDULE_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	TASK_SCHEDULE_LIMIT_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	TASK_SCHEDULE_COMPLETE_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	TASK_SCHEDULE_RESULT INT NOT NULL DEFAULT 0,
	TASK_SCHEDULE_REMARKS NVARCHAR(255) NOT NULL DEFAULT ''
)
GO
CREATE TABLE ACTIVITY_CONSULTANT (
	ACTIVITY_CONSULTANT_ID INT IDENTITY (1,1),
	ACTIVITY_CONSULTANT_NAME_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_CONSULTANT_PROJECT_ID INT NOT NULL DEFAULT 0,
	ACTIVITY_CONSULTANT_BILL_HOUR DATETIME NOT NULL DEFAULT GETDATE(),
	ACTIVITY_CONSULTANT_NONE_BILL_HOUR DATETIME NOT NULL DEFAULT GETDATE(),
	COY_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	WORKSTATION_ID nvarchar (30) NOT NULL DEFAULT '',
	MODIFY_BY INT NOT NULL DEFAULT 0,
	MODIFY_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_ACTIVITY_CONSULTANT PRIMARY KEY (ACTIVITY_CONSULTANT_ID)
)
GO

USE MASTER