Database preparation

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

DROP TABLE wf_ms_employee;
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);

DROP TABLE wf_ms_hardware;
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);

DROP TABLE wf_ms_list_box;
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', 'HR_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', 'HR_DECISION', 'A', null, null, null, null);

DROP TABLE wf_ms_phase;
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);

DROP TABLE wf_ms_position;
CREATE TABLE wf_ms_position (position_id CHARACTER VARYING(15) NOT NULL, position_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 (position_id));

DROP TABLE wf_ms_project;
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');

DROP TABLE wf_ms_status;
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));

DROP TABLE wf_ms_sub_request_type;
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));
INSERT INTO wf_ms_sub_request_type (sub_req_type_id, sub_req_name, create_by, create_date, update_by, update_date, request_type) VALUES ('6', 'Normal Leave', null, null, null, null, '04');
INSERT INTO wf_ms_sub_request_type (sub_req_type_id, sub_req_name, create_by, create_date, update_by, update_date, request_type) VALUES ('7', 'Replacement Leave', null, null, null, null, '04');

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

CREATE TABLE wf_leave_req_item (
     leave_item_id CHARACTER VARYING(20) NOT NULL, 
     request_id CHARACTER VARYING(10) NOT NULL, 
     leave_type CHARACTER VARYING(10) NOT NULL, 
     specify CHARACTER VARYING(500) NOT NULL, 
     work_date DATE, 
     rw_no CHARACTER VARYING(20), 
     start_date DATE, 
     end_date DATE, 
     total NUMERIC(5,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 (leave_item_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 database sequence lists below.

REQUEST_ID_SEQ

LEAVE_ITEM_ID_SEQ

Last updated 2 years ago