# Database Preparation

This section describes the **database setup** required for the **Leave Control App**.\
You will create **master tables**, **transaction tables**, and **database sequences** under the schema `ERP_ONEWEB` using **PostgreSQL**.

These database objects are used to support leave request creation, approval workflows, and leave item management.

***

### Objective

By completing this section, you will:

* Prepare all required **master data tables**
* Create **transaction tables** for leave requests and leave items
* Define **database sequences** for unique ID generation
* Establish a foundation for App Designer and Process Designer usage

***

### Database Schema

All tables and sequences must be created under the schema:

```
ERP_ONEWEB
```

***

### Part 1: Master Tables and Master Data

Master tables store reference and configuration data used across the Leave Control App.

***

#### Employee Master (`wf_ms_employee`)

Stores employee information used as requester and approver data.

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (employee_id)
);

INSERT INTO wf_ms_employee
(employee_id, employee_name, employee_fname, employee_lname, position_id, email)
VALUES ('rm2', 'rm2', 'ja', NULL, NULL, 'rm2@avalant.co.th');
```

***

#### Hardware Master (`wf_ms_hardware`)

Used only as reference data (shared with other applications).

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (hardware_id)
);

INSERT INTO wf_ms_hardware VALUES ('01', 'Personal Computer', NULL, NULL, NULL, NULL);
INSERT INTO wf_ms_hardware VALUES ('02', 'Notebook', NULL, NULL, NULL, NULL);
INSERT INTO wf_ms_hardware VALUES ('03', 'Projector', NULL, NULL, NULL, NULL);
```

***

#### List Box Master (`wf_ms_list_box`)

Stores decision options for approval workflows.

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (list_box_id)
);
```

Example decision categories:

* `BACK_OFFICE_DECISION`
* `ADMIN_DECISION`
* `AUTHORIZE_DECISION`
* `SUP_PM_DECISION`
* `HEAD_DECISION`
* `HR_DECISION`

(INSERT statements remain unchanged from original script.)

***

#### Phase Master (`wf_ms_phase`)

Defines system phases.

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (phase_id)
);

INSERT INTO wf_ms_phase VALUES ('01', 'Development', NULL, NULL, NULL, NULL);
INSERT INTO wf_ms_phase VALUES ('02', 'UAT', NULL, NULL, NULL, NULL);
INSERT INTO wf_ms_phase VALUES ('03', 'Production', NULL, NULL, NULL, NULL);
```

***

#### Position Master (`wf_ms_position`)

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (position_id)
);
```

***

#### Project Master (`wf_ms_project`)

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    project_manager CHARACTER VARYING(15),
    project_status CHARACTER VARYING(20),
    PRIMARY KEY (project_id)
);

INSERT INTO wf_ms_project VALUES
('01', 'Internal Project', NULL, NULL, NULL, NULL, 'admin', 'A'),
('02', 'MSIG - Digital Project', NULL, NULL, NULL, NULL, 'admin', 'A');
```

***

#### Sub Request Type Master (`wf_ms_sub_request_type`)

Used to define leave types.

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    request_type CHARACTER VARYING(10),
    PRIMARY KEY (sub_req_type_id)
);

INSERT INTO wf_ms_sub_request_type VALUES ('6', 'Normal Leave', NULL, NULL, NULL, NULL, '04');
INSERT INTO wf_ms_sub_request_type VALUES ('7', 'Replacement Leave', NULL, NULL, NULL, NULL, '04');
```

***

### Part 2: Transaction Tables

Transaction tables store runtime leave request data.

***

#### Leave Request Item (`wf_leave_req_item`)

Stores individual leave records.

```
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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    PRIMARY KEY (leave_item_id)
);
```

***

#### Service Request (`wf_service_request`)

Used as the main request header table.

```
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',
    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),
    update_by CHARACTER VARYING(50),
    update_date TIMESTAMP(6),
    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)
);
```

***

### Part 3: Database Sequences

Create the following database sequences for ID generation:

```
REQUEST_ID_SEQ
LEAVE_ITEM_ID_SEQ
```

These sequences are used by App Designer and Process Designer for creating new leave requests and leave items.

***

### Summary

In this section, you have:

* Created all master data tables required for Leave Control App
* Prepared transaction tables for leave requests and leave items
* Defined database sequences for identifier generation

This completes the **database foundation** for the **Leave Control App**.\
In the next sections, you will start designing screens using **App Designer** and workflows using **Process Designer**.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.onewebstack.com/tutorials-examples/leave-control-app/database-preparation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.