Database Preparation

CREATE TABLE wf_ms_employee (
    employee_id CHARACTER VARYING(15) NOT NULL,            
    employee_name CHARACTER VARYING(60), 
    employee_fname CHARACTER VARYING(30), 
    employee_lname CHARACTER VARYING(30), 
    position_id CHARACTER VARYING(30), 
    email CHARACTER VARYING(50), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (employee_id)
); 
    
INSERT INTO wf_ms_employee (employee_id, employee_name, employee_fname, employee_lname, position_id, email, create_by, create_date, update_by, update_date) 
VALUES ('rm2', 'rm2', 'ja', null, null, 'rm2@avalant.co.th', null, null, null, null);
    
CREATE TABLE wf_ms_hardware (
    hardware_id CHARACTER VARYING(10) NOT NULL, 
    hardware_name CHARACTER VARYING(30), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (hardware_id)
); 

INSERT INTO wf_ms_hardware (hardware_id, hardware_name, create_by, create_date, update_by, update_date) 
VALUES ('01', 'Personal Computer', null, null, null, null); INSERT INTO wf_ms_hardware (hardware_id, hardware_name, create_by, create_date, update_by, update_date) VALUES ('02', 'Notebook', null, null, null, null); INSERT INTO wf_ms_hardware (hardware_id, hardware_name, create_by, create_date, update_by, update_date) VALUES ('03', 'Projector', null, null, null, null);

CREATE TABLE wf_ms_list_box (
    list_box_id CHARACTER VARYING(10) NOT NULL, 
    display_code CHARACTER VARYING(10), 
    display_name CHARACTER VARYING(50), 
    category_code CHARACTER VARYING(50), 
    status CHARACTER VARYING(5), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (list_box_id)
);

INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) 
VALUES ('LB00000001', '01', 'Approve', 'BACK_OFFICE_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000002', '02', 'Reject', 'BACK_OFFICE_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000004', '02', 'Reject', 'ADMIN_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000003', '01', 'Approve', 'ADMIN_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000005', '01', 'Approve', 'AUTHORIZE_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000006', '02', 'Reject', 'AUTHORIZE_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000007', '01', 'Approve', 'SUP_PM_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000008', '02', 'Reject', 'SUP_PM_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000009', '01', 'Approve', 'HEAD_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000010', '02', 'Reject', 'HEAD_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000011', '01', 'Approve', 'AUTHORIZE_DECISION', 'A', null, null, null, null); INSERT INTO wf_ms_list_box (list_box_id, display_code, display_name, category_code, status, create_by, create_date, update_by, update_date) VALUES ('LB00000012', '02', 'Reject', 'AUTHORIZE_DECISION', 'A', null, null, null, null);

CREATE TABLE wf_ms_phase (
    phase_id CHARACTER VARYING(10) NOT NULL, 
    phase_name CHARACTER VARYING(50), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (phase_id)
); 

INSERT INTO wf_ms_phase (phase_id, phase_name, create_by, create_date, update_by, update_date) 
VALUES ('01', 'Development', null, null, null, null); INSERT INTO wf_ms_phase (phase_id, phase_name, create_by, create_date, update_by, update_date) VALUES ('02', 'UAT', null, null, null, null); INSERT INTO wf_ms_phase (phase_id, phase_name, create_by, create_date, update_by, update_date) VALUES ('03', 'Production', null, null, null, null);

CREATE TABLE wf_ms_project (
    project_id CHARACTER VARYING(10) NOT NULL, 
    project_name CHARACTER VARYING(50), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    project_manager CHARACTER VARYING(15), 
    project_status CHARACTER VARYING(20), 
    PRIMARY KEY (project_id)
); 

INSERT INTO wf_ms_project (project_id, project_name, create_by, create_date, update_by, update_date, project_manager, project_status) 
VALUES ('01', 'Internal Project', null, null, null, null, 'admin', 'A'); INSERT INTO wf_ms_project (project_id, project_name, create_by, create_date, update_by, update_date, project_manager, project_status) VALUES ('02', 'MSIG - Digital Project', null, null, null, null, 'admin', 'A');

CREATE TABLE wf_ms_status (
    status_id CHARACTER VARYING(10) NOT NULL, 
    status_name CHARACTER VARYING(100), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (status_id)
); 

CREATE TABLE wf_ms_sub_request_type (
    sub_req_type_id CHARACTER VARYING(10) NOT NULL, 
    sub_req_name CHARACTER VARYING(30), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    request_type CHARACTER VARYING(10), 
    PRIMARY KEY (sub_req_type_id)
);

CREATE TABLE wf_service_request (
    request_id CHARACTER VARYING(10) NOT NULL, 
    requester CHARACTER VARYING(50), 
    request_type CHARACTER VARYING(10), 
    project CHARACTER VARYING(10), 
    phase CHARACTER VARYING(10), 
    sub_request_type CHARACTER VARYING(10), 
    description CHARACTER VARYING(200), 
    status CHARACTER VARYING(200) DEFAULT 'New'::character varying, 
    decision CHARACTER VARYING(2), 
    decision_remark CHARACTER VARYING(200), 
    decision_role CHARACTER VARYING(10), 
    contact_info CHARACTER VARYING(200), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    total_amt NUMERIC(24,2), 
    decision_sup_pm_remark CHARACTER VARYING(200), 
    decision_sup_pm CHARACTER VARYING(2), 
    decision_head CHARACTER VARYING(2), 
    decision_head_remark CHARACTER VARYING(200), 
    decision_hr CHARACTER VARYING(2), 
    decision_hr_remark CHARACTER VARYING(200), 
    decision_auth CHARACTER VARYING(2), 
    decision_auth_remark CHARACTER VARYING(200), 
    PRIMARY KEY (request_id)
);

CREATE TABLE wf_order_req_item (
    order_item_id CHARACTER VARYING(20) NOT NULL, 
    request_id CHARACTER VARYING(10) NOT NULL, 
    hardwareid CHARACTER VARYING(12) NOT NULL, 
    spec CHARACTER VARYING(500) NOT NULL, 
    remark CHARACTER VARYING(500) NOT NULL, 
    unit INTEGER, summary_unit INTEGER, 
    summary_price NUMERIC(24,2), 
    create_by CHARACTER VARYING(50), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(50), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (order_item_id)
);

CREATE TABLE wf_order_req_item_supplier (
    order_item_supplier_id CHARACTER VARYING(20) NOT NULL, 
    order_item_id CHARACTER VARYING(20) NOT NULL, 
    remark CHARACTER VARYING(50), 
    supplier_name CHARACTER VARYING(50), 
    spec CHARACTER VARYING(500), 
    unit_price NUMERIC(24,2), 
    in_stock INTEGER, 
    remain_order_unit INTEGER, 
    unit_by_back_office INTEGER, 
    back_office_remark CHARACTER VARYING(200), 
    unit_by_approval INTEGER, 
    approval_remark CHARACTER VARYING(200), 
    total_price NUMERIC(24,2), 
    create_by CHARACTER VARYING(20), 
    create_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    update_by CHARACTER VARYING(20), 
    update_date TIMESTAMP(6) WITHOUT TIME ZONE, 
    PRIMARY KEY (order_item_supplier_id)
);