1. Create master table and master data at schema ERP_ONEWEB with below SQL. (PostgreSQL)


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));

2. Create transaction table at schema ERP_ONEWEB with below SQL. (PostgreSQL)


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));

3. Create database sequence lists below.

    • REQUEST_ID_SEQ
    • ORDER_ITEM_ID_SEQ
    • ORDER_ITEM_SUPPLIER_ID_SEQ