if exists(select * from sys.databases where name = 'DB_REQUEST')
    DROP database DB_REQUEST
GO
CREATE DATABASE DB_REQUEST
GO
USE DB_REQUEST
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)
)
CREATE TABLE SUPPLIER (
	SUPPLIER_ID INT IDENTITY (1,1)  ,
	SUPPLIER_CODE nvarchar(30) NOT NULL DEFAULT '',
	SUPPLIER_NAME nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_COMPANY_REG_ID nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_COMPANY_TAX_ID nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_WEB_ADDRESS nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_EMAIL_ADDRESS nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ADDRESS_LINE_1 nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ADDRESS_LINE_2 nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ADDRESS_LINE_3 nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ADDRESS_LINE_4 nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ADDRESS_LINE_5 nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_COMPANY_TELEPHONE nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_COMPANY_FAX nvarchar(255) 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_SUPPLIER PRIMARY KEY (SUPPLIER_ID)
)
GO
CREATE TABLE SUPPLIER_CONTACT (
	SUPPLIER_CONTACT_ID INT IDENTITY,
	SUPPLIER_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_CONTACT_NAME nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_CONTACT_EMAIL_ADDRESS nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_CONTACT_TELEPHONE nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_CONTACT_FAX nvarchar(255) NOT NULL DEFAULT '',
	CONSTRAINT PK_SUPPLIER_CONTACT PRIMARY KEY (SUPPLIER_CONTACT_ID)
)
GO
CREATE TABLE SUPPLIER_ITEM (
	SUPPLIER_ID INT NOT NULL DEFAULT 0,
	REQUEST_ITEM_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_ITEM_UOM_DEF_ID int NOT NULL DEFAULT 0,
	SUPPLIER_ITEM_TAXES_TYPE_DEF_ID int NOT NULL DEFAULT 0,
	CONSTRAINT PK_SUPPLIER_ITEM PRIMARY KEY (SUPPLIER_ID, REQUEST_ITEM_ID)
)
GO

-- REQUESITION
CREATE TABLE PAYMENT_TERMS (
	PAYMENT_TERMS_ID INT IDENTITY (1,1)  ,
	PAYMENT_TERMS_CODE nvarchar(100) NOT NULL DEFAULT '',
	PAYMENT_TERMS_DESC nvarchar(255) NOT NULL DEFAULT '',
	PAYMENT_TERMS_REF nvarchar(100) NOT NULL DEFAULT '',
	PAYMENT_TERMS_PRINTOUT text NOT NULL DEFAULT '',
	PAYMENT_TERMS_DEFAULT TINYINT DEFAULT 0,
	PAYMENT_TERMS_ORDER INT 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_PAYMENT_TERMS PRIMARY KEY (PAYMENT_TERMS_ID)
)
GO
CREATE TABLE UOM (
	UOM_ID INT IDENTITY (1,1)  ,
	UOM_CODE nvarchar(30) NOT NULL DEFAULT '',
	UOM_DESC nvarchar(255) NOT NULL DEFAULT '',
	UOM_REF nvarchar(100) NOT NULL DEFAULT '',
	PERCENTAGE INT NOT NULL DEFAULT 0,
	UOM_MSG nvarchar(255) 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_UOM PRIMARY KEY (UOM_ID)
)
GO
CREATE TABLE DELIVERY_LOCATION (
	DELIVERY_LOCATION_ID INT IDENTITY (1,1)  ,
	DELIVERY_LOCATION_CODE nvarchar(30) NOT NULL DEFAULT '',
	DELIVERY_LOCATION_DESC nvarchar(255) NOT NULL DEFAULT '',
	DELIVERY_LOCATION_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_DELIVERY_LOCATION PRIMARY KEY (DELIVERY_LOCATION_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
CREATE TABLE REQUEST_HEADER (
	REQUEST_ID INT IDENTITY (1,1),
	REQUEST_TITLE nvarchar(100) NOT NULL DEFAULT '',
	REQUEST_APPROVAL_GROUP INT NOT NULL DEFAULT 0,
	COMPANY_CODE nvarchar(15) NOT NULL DEFAULT '',
	COMPANY_ID INT NOT NULL DEFAULT 0,
	REQUEST_STATUS INT NOT NULL DEFAULT 0,
	REQUEST_WORKFLOW_ID INT NOT NULL DEFAULT 0,
	REQUEST_WORKFLOW_STEP INT NOT NULL DEFAULT 0,
	REQUEST_CODE nvarchar(50) NOT NULL DEFAULT '',
	REQUEST_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	REQUEST_DATE_CREATION DATETIME NOT NULL DEFAULT GETDATE(),
	REQUEST_DATE_SUBMISSION DATETIME NOT NULL DEFAULT GETDATE(),
	VOID_CODE nvarchar(50) NOT NULL DEFAULT '',
	VOID_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	EMPLOYEE_CODE nvarchar(15) NOT NULL DEFAULT '',
	EMPLOYEE_ID INT NOT NULL DEFAULT 0,
	REQUEST_URGENT INT NOT NULL DEFAULT 0,
	REQUEST_URGENT_REASON nvarchar(255) NOT NULL DEFAULT '',
	REASON_NOT_FULFILL_QUOTES nvarchar(255) NOT NULL DEFAULT '',
	PURPOSE_OF_PURCHASE nvarchar(255) NOT NULL DEFAULT '',
	REMARKS nvarchar(255) NOT NULL DEFAULT '',
	REMARKS_INTERNAL_USE nvarchar(255) NOT NULL DEFAULT '',
	PAYMENT_TERMS_ID INT NOT NULL DEFAULT 0,
	DELIVERY_LOCATION_ID INT NOT NULL DEFAULT 0,
	CURRENCY_ID INT NOT NULL DEFAULT 0,
	CURRENCY_FX_RATE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_TYPE TINYINT 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_REQUEST PRIMARY KEY (REQUEST_ID)
)
GO
CREATE TABLE REQUEST_HEADER_ADDITIONAL_ATTACHMENT (
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_ID INT IDENTITY (1,1),
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_REQUEST_ID INT NOT NULL DEFAULT 0,
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_USER_ID INT NOT NULL DEFAULT 0,
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_UPLOAD_DATE DATETIME DEFAULT GETDATE(),
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_WORKFLOW_ID INT NOT NULL DEFAULT 0,
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_WORKFLOW_STEP INT NOT NULL DEFAULT 0,
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_SAVED_NAME nvarchar(255) NOT NULL DEFAULT '',
	REQUEST_HEADER_ADDITIONAL_ATTACHMENT_ORI_NAME nvarchar(255) NOT NULL DEFAULT ''
)
GO
CREATE TABLE REQUEST_SUPPLIER_DETAIL (
	REQUEST_ID INT NOT NULL DEFAULT 0,
	USER_ID INT NOT NULL DEFAULT 0,
	IS_NOMINATED INT NOT NULL DEFAULT 0,
	SUPPLIER_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_CODE nvarchar(15) NOT NULL DEFAULT '',
	SUPPLIER_CONTACT_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_NAME nvarchar(150) NOT NULL DEFAULT '',
	SUPPLIER_BEFORE_TAX decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_TAX decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_TOTAL decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_ATTACH nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ATTACH_ORIGINAL_NAME nvarchar(255) NOT NULL DEFAULT '',
	REASON_NOMINATION nvarchar(255) NOT NULL DEFAULT ''
)
GO
CREATE TABLE REQUEST_DETAIL (
	REQUEST_ID INT NOT NULL DEFAULT 0,
	REQUEST_LINE_NO INT NOT NULL DEFAULT 0,
	REQUEST_DETAIL_STATUS INT NOT NULL DEFAULT 0,
	REQUEST_DESC nvarchar(255) NOT NULL DEFAULT '',
	REQUEST_EXT_DESC TEXT NOT NULL DEFAULT '',
	REQUEST_ITEM_ID INT NOT NULL DEFAULT 0,
	REQUEST_ITEM_UOM INT NOT NULL DEFAULT 0,
	REQUEST_COST_CENTRE nvarchar(15) NOT NULL DEFAULT '',
	REQUEST_QUANTITY DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_UNIT_PRICE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_DISCOUNT_PERCENTAGE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_DISCOUNT_AMOUNT_1 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_DISCOUNT_AMOUNT_2 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_TOTAL_AFTER_DISCOUNT DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_TAX_ID INT NOT NULL DEFAULT 0,
	REQUEST_TAX DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_NET_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_NET_APPROVE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	REQUEST_BUDGET_YEAR INT NOT NULL DEFAULT 0,
	REQUEST_STATUS INT NOT NULL DEFAULT 0,
	REQUEST_COMMENT TEXT NOT NULL DEFAULT '',
	CONSTRAINT PK_REQUEST_DETAIL PRIMARY KEY (REQUEST_ID, REQUEST_LINE_NO)
)
GO
CREATE TABLE PURCHASE_ORDER_HEADER (
	PURCHASE_ORDER_ID INT IDENTITY (1,1),
	PURCHASE_ORDER_TITLE nvarchar(100) NOT NULL DEFAULT '',
	PURCHASE_ORDER_APPROVAL_GROUP INT NOT NULL DEFAULT 0,
	COMPANY_CODE nvarchar(15) NOT NULL DEFAULT '',
	COMPANY_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_STATUS INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_WORKFLOW_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_WORKFLOW_STEP INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_CODE nvarchar(50) NOT NULL DEFAULT '',
	PURCHASE_ORDER_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	PURCHASE_ORDER_DATE_CREATION DATETIME NOT NULL DEFAULT GETDATE(),
	PURCHASE_ORDER_DATE_SUBMISSION DATETIME NOT NULL DEFAULT GETDATE(),
	VOID_CODE nvarchar(50) NOT NULL DEFAULT '',
	VOID_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	VOID_REASON TEXT NOT NULL DEFAULT '',
	EMPLOYEE_CODE nvarchar(15) NOT NULL DEFAULT '',
	EMPLOYEE_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_URGENT INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_URGENT_REASON nvarchar(255) NOT NULL DEFAULT '',
	REASON_NOT_FULFILL_QUOTES nvarchar(255) NOT NULL DEFAULT '',
	PURPOSE_OF_PURCHASE nvarchar(255) NOT NULL DEFAULT '',
	REMARKS nvarchar(255) NOT NULL DEFAULT '',
	REMARKS_INTERNAL_USE nvarchar(255) NOT NULL DEFAULT '',
	PAYMENT_TERMS_ID INT NOT NULL DEFAULT 0,
	DELIVERY_LOCATION_ID INT NOT NULL DEFAULT 0,
	CURRENCY_ID INT NOT NULL DEFAULT 0,
	CURRENCY_FX_RATE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_TYPE	TINYINT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_CONTRACT_TYPE TINYINT NOT NULL DEFAULT 0,
	CONTRACT_START_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONTRACT_END_DATE 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_PURCHASE_ORDER PRIMARY KEY (PURCHASE_ORDER_ID)
)
GO
CREATE TABLE PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT (
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_ID INT IDENTITY (1,1),
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_PURCHASE_ORDER_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_USER_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_UPLOAD_DATE DATETIME DEFAULT GETDATE(),
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_WORKFLOW_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_WORKFLOW_STEP INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_SAVED_NAME nvarchar(255) NOT NULL DEFAULT '',
	PURCHASE_ORDER_HEADER_ADDITIONAL_ATTACHMENT_ORI_NAME nvarchar(255) NOT NULL DEFAULT ''
)
GO
CREATE TABLE PURCHASE_ORDER_SUPPLIER_DETAIL (
	PURCHASE_ORDER_ID INT NOT NULL DEFAULT 0,
	USER_ID INT NOT NULL DEFAULT 0,
	IS_NOMINATED INT NOT NULL DEFAULT 0,
	SUPPLIER_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_CODE nvarchar(15) NOT NULL DEFAULT '',
	SUPPLIER_CONTACT_ID INT NOT NULL DEFAULT 0,
	SUPPLIER_NAME nvarchar(150) NOT NULL DEFAULT '',
	SUPPLIER_BEFORE_TAX decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_TAX decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_TOTAL decimal(18,4) NOT NULL DEFAULT 0.00,
	SUPPLIER_ATTACH nvarchar(255) NOT NULL DEFAULT '',
	SUPPLIER_ATTACH_ORIGINAL_NAME nvarchar(255) NOT NULL DEFAULT '',
	REASON_NOMINATION nvarchar(255) NOT NULL DEFAULT ''
)
GO
CREATE TABLE PURCHASE_ORDER_DETAIL (
	PURCHASE_ORDER_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_LINE_NO INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_DETAIL_STATUS INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_DESC nvarchar(255) NOT NULL DEFAULT '',
	PURCHASE_ORDER_EXT_DESC TEXT NOT NULL DEFAULT '',
	PURCHASE_ORDER_ITEM_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_ITEM_UOM INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_COST_CENTRE nvarchar(15) NOT NULL DEFAULT '',
	PURCHASE_ORDER_QUANTITY DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_UNIT_PRICE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_DISCOUNT_PERCENTAGE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_DISCOUNT_AMOUNT_1 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_DISCOUNT_AMOUNT_2 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_TOTAL_AFTER_DISCOUNT DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_TAX_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_TAX DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_NET_TOTAL DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_NET_APPROVE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	PURCHASE_ORDER_BUDGET_YEAR INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_STATUS INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_COMMENT TEXT NOT NULL DEFAULT '',
	CONSTRAINT PK_PURCHASE_ORDER_DETAIL PRIMARY KEY (PURCHASE_ORDER_ID, PURCHASE_ORDER_LINE_NO)
)
GO
CREATE TABLE PURCHASE_ORDER_FLOW_HISTORY (
	PURCHASE_ORDER_FLOW_HISTORY_ID INT IDENTITY (1,1),
	PURCHASE_ORDER_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_LINE_NO INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_STATUS INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_DETAIL_STATUS INT NOT NULL DEFAULT 0,
	INVOICE_ID INT NOT NULL DEFAULT 0,
    CREATE_BY INT NOT NULL DEFAULT 0,
    MODIFY_BY INT NOT NULL DEFAULT 0,
    CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_PURCHASE_ORDER_FLOW_HISTORY PRIMARY KEY (PURCHASE_ORDER_FLOW_HISTORY_ID)
)
GO
CREATE TABLE REQUEST_PURCHASE_ORDER(
	REQUEST_ID INT NOT NULL DEFAULT 0,
	PURCHASE_ORDER_ID INT NOT NULL DEFAULT 0,
	CONSTRAINT PK_REQUEST_PURCHASE_ORDER PRIMARY KEY (REQUEST_ID, PURCHASE_ORDER_ID)
)
GO
CREATE TABLE REQUEST_HEADER_MSG (
	REQUEST_HEADER_MSG_ID INT IDENTITY (1,1),
	REQUEST_MSG_CONTENT nvarchar (250) NOT NULL DEFAULT '',
	REQUEST_ID INT NOT NULL DEFAULT 0,
	COY_ID INT NOT NULL DEFAULT 0,
	ORG_ID INT NOT NULL DEFAULT 0,
	CREATE_BY INT NOT NULL DEFAULT 0,
	CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_REQUEST_HEADER_MSG PRIMARY KEY (REQUEST_HEADER_MSG_ID)
)
GO
CREATE TABLE REQUEST_REMINDER (
	REQUEST_ID INT NOT NULL DEFAULT '',
	REMINDER_ID INT NOT NULL DEFAULT '',
	SEND_TIME DATETIME NOT NULL DEFAULT GETDATE(),
	EMAIL_ADDRESS nvarchar(50) NOT NULL DEFAULT '',
	CC_LIST nvarchar(255) NOT NULL DEFAULT '',
	WORKFLOW_ID INT NOT NULL DEFAULT 0,
	WORKFLOW_STEP INT NOT NULL DEFAULT 0
)
GO
CREATE TABLE REASON(
	REASON_ID INT IDENTITY (1,1),
	REASON_CODE nvarchar (30) NOT NULL DEFAULT '',
	REASON_CONTENT nvarchar (250) 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_REASON PRIMARY KEY (REASON_ID)
)
GO
CREATE TABLE REQUEST_FLOW_HISTORY (
	REQUEST_FLOW_HISTORY_ID INT IDENTITY (1,1),
	REQUEST_ID INT NOT NULL DEFAULT 0,
	REQUEST_LINE_NO INT NOT NULL DEFAULT 0,
	APPROVE_BY INT NOT NULL DEFAULT 0,
	WORKFLOW_STEP INT NOT NULL DEFAULT 0,
	WORKFLOW_ID INT NOT NULL DEFAULT 0,
	STATUS INT NOT NULL DEFAULT 0,
	REASON_MSG INT NOT NULL DEFAULT 0,
	COMMENT nvarchar(255) NOT NULL DEFAULT '',
	APPROVAL_AMOUNT decimal(18,4) NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_DATE DATETIME NOT NULL DEFAULT GETDATE(),
	CONSTRAINT PK_REQUEST_FLOW PRIMARY KEY (REQUEST_FLOW_HISTORY_ID)
)
GO
CREATE TABLE REQUEST_ITEM_GROUP (
	REQUEST_ITEM_GROUP_ID INT IDENTITY (1,1),
	REQUEST_ITEM_GROUP_CODE nvarchar(30) NOT NULL DEFAULT '',
	REQUEST_ITEM_GROUP_REF nvarchar(100) NOT NULL DEFAULT '',
	REQUEST_ITEM_GROUP_DESC nvarchar(50) 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_REQUEST_ITEM_GROUP PRIMARY KEY (REQUEST_ITEM_GROUP_ID)
)
GO
CREATE TABLE REQUEST_ITEM (
	REQUEST_ITEM_ID INT IDENTITY (1,1),
	REQUEST_ITEM_REF nvarchar(10) NOT NULL DEFAULT'',
	REQUEST_ITEM_GL_CODE nvarchar(20) NOT NULL DEFAULT '',
	REQUEST_ITEM_GROUP_ID INT NOT NULL DEFAULT 0,
	REQUEST_ITEM_DESC nvarchar(100) NOT NULL DEFAULT '',
	REQUEST_ITEM_MESSAGE ntext NOT NULL DEFAULT '',
	REQUEST_ITEM_TAXES_TYPE_ID INT NOT NULL DEFAULT 0,
	REQUEST_ITEM_UOM_ID INT 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_REQUEST_ITEM PRIMARY KEY (REQUEST_ITEM_ID)
)
GO
CREATE TABLE REQUEST_APPROVAL_GROUP_HEAD (
	REQUEST_APPROVAL_GROUP_ID INT NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_USER_ID INT NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_WORKFLOW_ID INT NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_HEAD_ORDER INT NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_HEAD_THRESHOLD_TOP decimal(18,4) NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_HEAD_THRESHOLD_BOTTOM decimal(18,4) NOT NULL DEFAULT 0
)
GO
CREATE INDEX IX_REQUEST_APPROVAL_GROUP_HEAD on REQUEST_APPROVAL_GROUP_HEAD (REQUEST_APPROVAL_GROUP_ID, REQUEST_APPROVAL_GROUP_WORKFLOW_ID) 
GO
CREATE TABLE REQUEST_APPROVAL_GROUP_PREPARER (
	REQUEST_APPROVAL_GROUP_ID INT NOT NULL DEFAULT 0,
	REQUEST_APPROVAL_GROUP_USER_ID INT NOT NULL DEFAULT 0
)
GO
CREATE INDEX IX_REQUEST_APPROVAL_GROUP_PREPARER on REQUEST_APPROVAL_GROUP_PREPARER (REQUEST_APPROVAL_GROUP_ID, REQUEST_APPROVAL_GROUP_USER_ID) 
GO
CREATE TABLE REQUEST_APPROVAL_GROUP (
	REQUEST_APPROVAL_GROUP_ID INT IDENTITY (1,1),
	REQUEST_APPROVAL_GROUP_DESC nvarchar (255) NOT NULL DEFAULT '',
	REQUEST_APPROVAL_GROUP_REF nvarchar (100) NOT NULL DEFAULT '',
	IS_BLANKET_ORDER INT 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_REQUEST_APPROVAL_GROUP PRIMARY KEY (REQUEST_APPROVAL_GROUP_ID)
)
GO
CREATE TABLE WORKFLOW (
	WORKFLOW_ID INT IDENTITY (1,1),
	WORKFLOW_DESC nvarchar (100) NOT NULL DEFAULT '',
	WORKFLOW_OPTIONS ntext NULL,
	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_WORKFLOW_1 PRIMARY KEY (WORKFLOW_ID)
)
GO
CREATE TABLE EMPLOYEE_PASSWORD (
	EMP_ID INT NOT NULL DEFAULT 0,
	EMP_CODE nvarchar(20) NOT NULL DEFAULT '',
	EMP_PWD1 nvarchar(40) NOT NULL DEFAULT '',
	EMP_PWD2 nvarchar(40) NOT NULL DEFAULT '',
	EMP_LAST_LOGIN datetime NOT NULL DEFAULT GETDATE(),
	EMP_CHANGE_PWD INT NOT NULL DEFAULT 0,
	EMP_FAIL_COUNT TINYINT NOT NULL DEFAULT 0
)
GO

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 CONTENT_CATEGORY (
	CATE_ID INT IDENTITY (1,1),
	CATE_DESC nvarchar(50) NOT NULL DEFAULT '',
	CATE_TYPE INT 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_CONTENT_CATEGORY PRIMARY KEY (CATE_ID)
)
GO
CREATE TABLE CONTENT (
	CONTENT_ID INT IDENTITY (1,1),
	CONTENT_DESC nvarchar(250) NOT NULL DEFAULT '',
	CONTENT_TYPE INT NOT NULL DEFAULT 0,
	CONTENT_CATE INT NOT NULL DEFAULT 0,
	CONTENT_DETAIL NTEXT NOT NULL DEFAULT '',
	CONTENT_TITLE NVARCHAR(120) NOT NULL DEFAULT '',
	CONTENT_START DATETIME NOT NULL DEFAULT GETDATE(),
	CONTENT_END DATETIME NOT NULL DEFAULT GETDATE(),
	CONTENT_PRIORITY INT NOT NULL DEFAULT 0,
	CONTENT_URL NVARCHAR(120) 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_CONTENT PRIMARY KEY (CONTENT_ID)
)
GO
CREATE TABLE MESSAGE (
	MESSAGE_ID INT IDENTITY (1,1),
	MESSAGE_TYPE INT NOT NULL DEFAULT 0,
	MESSAGE_DETAIL NTEXT NOT NULL DEFAULT '',
	MESSAGE_TITLE NVARCHAR(120) NOT NULL DEFAULT '',
	MESSAGE_START DATETIME NOT NULL DEFAULT GETDATE(),
	MESSAGE_END 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_MESSAGE PRIMARY KEY (MESSAGE_ID)
)
GO
CREATE TABLE MESSAGE_USER (
	MESSAGE_ID INT NOT NULL DEFAULT 0,
	MESSAGE_EMP_ID INT NOT NULL DEFAULT 0,
	MESSAGE_REPLY NTEXT NOT NULL DEFAULT '',
	MESSAGE_READ_DATE 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()
)
GO
CREATE INDEX IX_MESSAGE_EMP on MESSAGE_USER (MESSAGE_ID,MESSAGE_EMP_ID) 
GO


CREATE TABLE dbo.GOOD_RECEIVING_DETAIL(
    GOOD_RECEIVING_ID int NOT NULL DEFAULT 0,
    GOOD_RECEIVING_LINE_NO int NOT NULL DEFAULT 0,
    PO_ID int NOT NULL DEFAULT 0,
    PO_LINE_NO int NOT NULL DEFAULT 0,
    PO_ITEM_UOM int NOT NULL DEFAULT 0,
    PO_ITEM_ID int NOT NULL DEFAULT 0,
    REMARKS nvarchar(255) NOT NULL DEFAULT '',
    QUANTITY decimal(18, 2) NOT NULL DEFAULT 0,
    CONSTRAINT PK_GOOD_RECEIVING_DETAIL PRIMARY KEY (GOOD_RECEIVING_ID, GOOD_RECEIVING_LINE_NO)
)
GO

CREATE TABLE dbo.GOOD_RECEIVING_HEADER(
    GOOD_RECEIVING_ID int IDENTITY(1,1) NOT NULL,
    SUPPLIER_ID int NOT NULL DEFAULT 0,
    SUPPLIER_DO_NO nvarchar(50) NOT NULL DEFAULT '',
    COMPANY_ID int NOT NULL DEFAULT 0,
    DELIVERY_LOCATION_ID int NULL DEFAULT 0,
    GOOD_RECEIVING_STATUS int NOT NULL DEFAULT 0,
    GOOD_RECEIVING_CODE nvarchar(50) NOT NULL DEFAULT '',
    GOOD_RECEIVING_DATE datetime NOT NULL DEFAULT GETDATE(),
    TOTAL_QUANTITY decimal(18, 0) NOT NULL DEFAULT 0,
    REMARKS nvarchar(255) NOT NULL DEFAULT '',
    REFERENCE_NO nvarchar(100) NOT NULL DEFAULT '',
    COY_ID int NOT NULL DEFAULT 0,
    ORG_ID int NOT NULL DEFAULT 0,
    WORKSTATION_ID nvarchar(50) 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_GOOD_RECEIVING_HEADER PRIMARY KEY (GOOD_RECEIVING_ID)
)
GO


CREATE TABLE INVOICE_APPROVAL_GROUP_PREPARER (
    INVOICE_PREPARER_GROUP_USER_ID INT NOT NULL,
    INVOICE_APPROVAL_GROUP_ID INT NOT NULL,
    CONSTRAINT PK_INVOICE_APPROVAL_GROUP_PREPARER PRIMARY KEY (INVOICE_APPROVAL_GROUP_ID, INVOICE_PREPARER_GROUP_USER_ID)
)
GO

CREATE TABLE INVOICE_APPROVAL_GROUP_HEAD (
    INVOICE_APPROVAL_GROUP_ID INT NOT NULL,
    INVOICE_APPROVAL_GROUP_USER_ID INT NOT NULL,
    INVOICE_APPROVAL_GROUP_WORKFLOW_ID INT NOT NULL,
    INVOICE_APPROVAL_GROUP_ORDER INT NOT NULL,
    CONSTRAINT PK_INVOICE_APPROVAL_GROUP_HEAD PRIMARY KEY (INVOICE_APPROVAL_GROUP_ID, INVOICE_APPROVAL_GROUP_WORKFLOW_ID, INVOICE_APPROVAL_GROUP_USER_ID)
)
GO

CREATE TABLE INVOICE_DETAIL (
    INVOICE_ID INT DEFAULT ((0)) NOT NULL,
    INVOICE_LINE_NO INT DEFAULT ((0)) NOT NULL,
    INVOICE_DETAIL_REMARKS NVARCHAR(255) DEFAULT ('') NOT NULL,
    PO_ITEM_ID INT DEFAULT ((0)) NOT NULL,
    PO_ITEM_UOM INT DEFAULT ((0)) NOT NULL,
    INVOICE_DETAIL_PO_ID INT DEFAULT ((0)) NOT NULL,
    INVOICE_DETAIL_DO_ID INT DEFAULT ((0)) NOT NULL,
    INVOICE_DETAIL_LINE_NO INT DEFAULT ((0)) NOT NULL,
    QUANTITY DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    PRICE DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    DISCOUNT DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    NETT DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    AMOUNT DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    INVOICE_COST_CENTRE nvarchar(15) NOT NULL DEFAULT '',
	INVOICE_DISCOUNT_AMOUNT_1 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	INVOICE_DISCOUNT_AMOUNT_2 DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	INVOICE_TOTAL_AFTER_DISCOUNT DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	INVOICE_DISCOUNT_PERCENTAGE DECIMAL(18,4) NOT NULL DEFAULT 0.00,
	INVOICE_TAX_ID INT NOT NULL DEFAULT 0,
	INVOICE_BUDGET_YEAR INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_INVOICE_DETAIL PRIMARY KEY (INVOICE_ID, INVOICE_LINE_NO)
)
GO

CREATE TABLE INVOICE_HEADER (
    INVOICE_ID INT IDENTITY (1,1),
    SUPPLIER_ID INT DEFAULT ((0)) NOT NULL,
    COMPANY_ID INT DEFAULT ((0)) NOT NULL,
    TOTAL_DISCOUNT DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    INVOICE_TAX DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    TOTAL_AMOUNT DECIMAL(18,6) DEFAULT ((0)) NOT NULL,
    INVOICE_STATUS INT DEFAULT ((0)) NOT NULL,
    INVOICE_CODE NVARCHAR(50) DEFAULT ('') NOT NULL,
    INVOICE_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    REMARKS NVARCHAR(255) DEFAULT ('') NOT NULL,
    INVOICE_WORKFLOW_ID INT DEFAULT ((0)) NOT NULL,
    INVOICE_WORKFLOW_STEP INT DEFAULT ((0)) NOT NULL,
    INVOICE_APPROVAL_GROUP INT DEFAULT ((0)) NOT NULL,
    INVOICE_TYPE TINYINT DEFAULT ((0)) NOT NULL,
    INVOICE_SUPPLIER_NO NVARCHAR(50) DEFAULT ('') NOT NULL,
    INVOICE_SUPPLIER_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    INVOICE_REASON nvarchar(255) NOT NULL DEFAULT '',
    COY_ID INT DEFAULT ((0)) NOT NULL,
    ORG_ID INT DEFAULT ((0)) NOT NULL,
    WORKSTATION_ID NVARCHAR(30) DEFAULT ('') NOT NULL,
    CREATE_BY INT DEFAULT ((0)) NOT NULL,
    CREATE_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    MODIFY_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    MODIFY_BY INT DEFAULT ((0)) NOT NULL,
    CONSTRAINT PK_INVOICE_HEADER PRIMARY KEY (INVOICE_ID)
)
GO

CREATE TABLE INVOICE_APPROVAL_GROUP (
    INVOICE_APPROVAL_GROUP_ID INT IDENTITY (1,1),
    INVOICE_APPROVAL_GROUP_DESC NVARCHAR(255) DEFAULT ('') NOT NULL,
    INVOICE_APPROVAL_GROUP_REF NVARCHAR(100) DEFAULT ('') NOT NULL,
    IS_BLANKET_ORDER INT DEFAULT ((0)) NOT NULL,
    COY_ID INT DEFAULT ((0)) NOT NULL,
    ORG_ID INT DEFAULT ((0)) NOT NULL,
    WORKSTATION_ID NVARCHAR(30) DEFAULT ('') NOT NULL,
    MODIFY_BY INT DEFAULT ((0)) NOT NULL,
    MODIFY_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    CREATE_BY INT DEFAULT ((0)) NOT NULL,
    CREATE_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    CONSTRAINT PK_INVOICE_APPROVAL_GROUP PRIMARY KEY (INVOICE_APPROVAL_GROUP_ID)
)
GO

CREATE TABLE INVOICE_WORKFLOW (
    INVOICE_WORKFLOW_ID INT IDENTITY (1,1),
    INVOICE_WORKFLOW_DESC NVARCHAR(100) DEFAULT ('') NOT NULL,
    INVOICE_WORKFLOW_OPTIONS NTEXT,
    COY_ID INT DEFAULT ((0)) NOT NULL,
    ORG_ID INT DEFAULT ((0)) NOT NULL,
    WORKSTATION_ID NVARCHAR(30) DEFAULT ('') NOT NULL,
    MODIFY_BY INT DEFAULT ((0)) NOT NULL,
    MODIFY_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    CREATE_BY INT DEFAULT ((0)) NOT NULL,
    CREATE_DATE DATETIME DEFAULT (getdate()) NOT NULL,
    CONSTRAINT PK_INVOICE_WORKFLOW PRIMARY KEY (INVOICE_WORKFLOW_ID)
)
GO

CREATE TABLE INVOICE_FLOW_HISTORY (
    INVOICE_FLOW_HISTORY_ID INT IDENTITY (1,1),
    INVOICE_ID INT NOT NULL DEFAULT 0,
    INVOICE_LINE_NO INT NOT NULL DEFAULT 0,
    APPROVE_BY INT NOT NULL DEFAULT 0,
    WORKFLOW_STEP INT NOT NULL DEFAULT 0,
    WORKFLOW_ID INT NOT NULL DEFAULT 0,
    STATUS INT NOT NULL DEFAULT 0,
    REASON_MSG INT NOT NULL DEFAULT 0,
    COMMENT nvarchar(255) NOT NULL DEFAULT '',
    DISCOUNT decimal(18,4) NOT NULL DEFAULT 0,
    AMOUNT decimal(18,4) NOT NULL DEFAULT 0,
    INVOICE_APPROVAL_DATE DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_INVOICE_FLOW_HISTORY PRIMARY KEY (INVOICE_FLOW_HISTORY_ID)
)
GO

CREATE TABLE INVOICE_HEADER_MSG (
    INVOICE_HEADER_MSG_ID INT IDENTITY (1,1),
    INVOICE_MSG_CONTENT nvarchar (250) NOT NULL DEFAULT '',
    INVOICE_ID INT NOT NULL DEFAULT 0,
    COY_ID INT NOT NULL DEFAULT 0,
    ORG_ID INT NOT NULL DEFAULT 0,
    CREATE_BY INT NOT NULL DEFAULT 0,
    CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_INVOICE_HEADER_MSG PRIMARY KEY (INVOICE_HEADER_MSG_ID)
)
GO

CREATE TABLE EXPORT_RECEIPT_NOTE (
    EXPORT_ID INT IDENTITY (1,1),
    EXPORT_RECEIPT_NOTE_ID INT NOT NULL DEFAULT 0,
    EXPORT_RECEIPT_NOTE_USER_CREATE INT NOT NULL DEFAULT 0,
    EXPORT_RECEIPT_NOTE_CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
    EXPORT_RECEIPT_NOTE_COMPANY_ID INT NOT NULL DEFAULT 0,
    EXPORT_RECEIPT_NOTE_PERIOD_YEAR INT NOT NULL DEFAULT 0,
    EXPORT_RECEIPT_NOTE_PERIOD_MONTH INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_EXPORT_RECEIPT_NOTE PRIMARY KEY (EXPORT_ID)
)
GO

CREATE TABLE EXPORT_INVOICE (
    EXPORT_ID INT IDENTITY (1,1),
    EXPORT_INVOICE_ID INT NOT NULL DEFAULT 0,
    EXPORT_INVOICE_USER_CREATE INT NOT NULL DEFAULT 0,
    EXPORT_INVOICE_CREATE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_EXPORT_INVOICE PRIMARY KEY (EXPORT_ID)
)
GO

USE MASTER