# Database Preparation

This section describes the **database preparation** required for the **Order Management App (Web)**.\
You will create **master tables**, **transaction tables**, and **database sequences** in the `ERP_ONEWEB` schema using **PostgreSQL**.

These tables support the full lifecycle of an **order request**, including requester information, hardware items, approval decisions, and supplier processing.

***

### Objective

By completing this section, you will:

* Prepare **master data** used across the application
* Create **transaction tables** for order requests and items
* Define database **sequences** for unique ID generation
* Establish the backend foundation for Process Designer and App Designer usage

***

### Database Overview

The database structure is divided into two main parts:

1. **Master Tables** – Reference and configuration data
2. **Transaction Tables** – Order requests and related items

All objects are created under the schema:\
\&#xNAN;**`ERP_ONEWEB`**

***

### Part 1: Create Master Tables

Master tables provide reference data that is reused across forms, processes, and workflows.

***

#### Employee Master (`wf_ms_employee`)

Stores basic employee information used in order requests and approvals.

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

***

#### Hardware Master (`wf_ms_hardware`)

Defines electronic equipment types that can be requested.

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

***

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

Stores approval and decision options used across workflows.

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

*(Includes values for BACK\_OFFICE\_DECISION, ADMIN\_DECISION, AUTHORIZE\_DECISION, SUP\_PM\_DECISION, HEAD\_DECISION)*

***

#### Phase Master (`wf_ms_phase`)

Represents project lifecycle phases.

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

***

#### Project Master (`wf_ms_project`)

Defines active projects for which equipment can be requested.

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

***

#### Status & Sub Request Type Masters

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

***

### Part 2: Create Transaction Tables

Transaction tables store runtime order requests and related items.

***

#### Service Request (`wf_service_request`)

Stores the main order request and approval workflow state.

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

***

#### Order Request Item (`wf_order_req_item`)

Stores individual hardware items for a request.

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

***

#### Supplier Items (`wf_order_req_item_supplier`)

Tracks supplier quotes and approval details.

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

***

### Part 3: Create Database Sequences

Create the following database sequences for ID generation:

* `REQUEST_ID_SEQ`
* `ORDER_ITEM_ID_SEQ`
* `ORDER_ITEM_SUPPLIER_ID_SEQ`

These sequences are typically referenced by Microflows or Java Activities during request creation.

***

### Summary

In this section, you have:

* Created all required **master tables**
* Created **transaction tables** for order processing
* Prepared database **sequences** for unique IDs

This completes the database foundation for the **Order Management App (Web)**.\
In the next section, you will begin building the **Process Flow** 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/oneweb-platform-th/tutorials-examples/order-management-app-web/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.