Database Preparation
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)
);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)
);Create database sequence lists below. REQUEST_ID_SEQ ORDER_ITEM_ID_SEQ ORDER_ITEM_SUPPLIER_ID_SEQ
Last updated