ERP_PRJ
Tables (30)
ACCOUNT_PAYABLE
- AP_ID — INTEGER — Primary key — NOT NULL
- SUPPLIER_ID — INTEGER — Foreign key → SUPPLIER.SUPPLIER_ID — NOT NULL
- PURCHASE_INVOICE_ID — INTEGER — Foreign key → PURCHASE_INVOICE.PURCHASE_INVOICE_ID — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- PAID_AMOUNT — DECIMAL(15,2) — NOT NULL
- REMAIN_AMOUNT — DECIMAL(15,2) — NOT NULL
- DUE_DATE — DATE
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
ACCOUNT_RECEIVABLE
- AR_ID — INTEGER — Primary key — NOT NULL
- CUSTOMER_ID — INTEGER — Foreign key → CUSTOMER.CUSTOMER_ID — NOT NULL
- SALES_INVOICE_ID — INTEGER — Foreign key → SALES_INVOICE.SALES_INVOICE_ID — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- PAID_AMOUNT — DECIMAL(15,2) — NOT NULL
- REMAIN_AMOUNT — DECIMAL(15,2) — NOT NULL
- DUE_DATE — DATE
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
AI_QUERY_LOG
- QUERY_ID — INTEGER — Primary key — NOT NULL
- EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- USER_QUESTION — TEXT — NOT NULL
- GENERATED_SQL — TEXT
- RESULT_SUMMARY — TEXT
- CREATED_AT — TIMESTAMP
AI_REPORT
- REPORT_ID — INTEGER — Primary key — NOT NULL
- REPORT_TYPE — VARCHAR(30) — NOT NULL
- TITLE — VARCHAR(200) — NOT NULL
- CONTENT — TEXT
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
ATTENDANCE
- ATTENDANCE_ID — INTEGER — Primary key — NOT NULL
- EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- WORK_DATE — DATE — NOT NULL
- CHECK_IN — TIMESTAMP
- CHECK_OUT — TIMESTAMP
- WORK_HOURS — DECIMAL(5,2)
- STATUS — VARCHAR(20) — NOT NULL
- MEMO — VARCHAR(300)
- CREATED_AT — TIMESTAMP
CUSTOMER
- CUSTOMER_ID — INTEGER — Primary key — NOT NULL
- CUSTOMER_NAME — VARCHAR(100) — NOT NULL
- CUSTOMER_TYPE — VARCHAR(30) — NOT NULL
- BUSINESS_NO — VARCHAR(20)
- CREDIT_LIMIT — DECIMAL(15,2) — NOT NULL
- RECEIVABLE_BALANCE — DECIMAL(15,2) — NOT NULL
- PHONE — VARCHAR(20)
- ADDRESS — VARCHAR(300)
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
DEPARTMENT
- DEPT_ID — INTEGER — Primary key — NOT NULL
- DEPT_NAME — VARCHAR(50) — NOT NULL
- DESCRIPTION — VARCHAR(300)
- USE_YN — CHAR(1) — NOT NULL
- CREATED_AT — TIMESTAMP
DISPOSAL
- DISPOSAL_ID — INTEGER — Primary key — NOT NULL
- INVENTORY_LOT_ID — INTEGER — Foreign key → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- DISPOSAL_QTY — INTEGER — NOT NULL
- REASON — VARCHAR(500)
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
EMPLOYEE
- EMP_ID — INTEGER — Primary key — NOT NULL
- LOGIN_ID — VARCHAR(50) — NOT NULL
- PASSWORD — VARCHAR(255) — NOT NULL
- EMP_NAME — VARCHAR(50) — NOT NULL
- PHONE — VARCHAR(20)
- EMAIL — VARCHAR(100)
- ROLE_ID — INTEGER — Foreign key → ROLE.ROLE_ID — NOT NULL
- DEPT_ID — INTEGER — Foreign key → DEPARTMENT.DEPT_ID
- HIRE_DATE — DATE
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
EXPIRY_ALERT
- ALERT_ID — INTEGER — Primary key — NOT NULL
- INVENTORY_LOT_ID — INTEGER — Foreign key → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
- ALERT_TYPE — VARCHAR(20) — NOT NULL
- MESSAGE — VARCHAR(500) — NOT NULL
- IS_READ — CHAR(1) — NOT NULL
- CREATED_AT — TIMESTAMP
INVENTORY_LOT
- INVENTORY_LOT_ID — INTEGER — Primary key — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- LOT_NO — VARCHAR(50) — NOT NULL
- EXPIRY_DATE — DATE — NOT NULL
- CURRENT_QTY — INTEGER — NOT NULL
- SAFETY_QTY — INTEGER — NOT NULL
- LOCATION — VARCHAR(100)
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
PAYMENT
- PAYMENT_ID — INTEGER — Primary key — NOT NULL
- AR_ID — INTEGER — Foreign key → ACCOUNT_RECEIVABLE.AR_ID — NOT NULL
- CUSTOMER_ID — INTEGER — Foreign key → CUSTOMER.CUSTOMER_ID — NOT NULL
- PAYMENT_DATE — DATE — NOT NULL
- PAYMENT_AMOUNT — DECIMAL(15,2) — NOT NULL
- PAYMENT_TYPE — VARCHAR(20) — NOT NULL
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
PRODUCT
- PRODUCT_ID — INTEGER — Primary key — NOT NULL
- PRODUCT_CODE — VARCHAR(50) — NOT NULL
- PRODUCT_NAME — VARCHAR(150) — NOT NULL
- CATEGORY_ID — INTEGER — Foreign key → PRODUCT_CATEGORY.CATEGORY_ID
- MAKER_NAME — VARCHAR(100)
- UNIT — VARCHAR(20) — NOT NULL
- STANDARD_PURCHASE_PRICE — DECIMAL(12,2) — NOT NULL
- STANDARD_SALES_PRICE — DECIMAL(12,2) — NOT NULL
- IS_PRESCRIPTION — CHAR(1) — NOT NULL
- STORAGE_TYPE — VARCHAR(20) — NOT NULL
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
PRODUCT_CATEGORY
- CATEGORY_ID — INTEGER — Primary key — NOT NULL
- CATEGORY_NAME — VARCHAR(100) — NOT NULL
- DESCRIPTION — VARCHAR(300)
- USE_YN — CHAR(1) — NOT NULL
- CREATED_AT — TIMESTAMP
PUBLIC_API_LOG
- API_LOG_ID — INTEGER — Primary key — NOT NULL
- API_NAME — VARCHAR(100) — NOT NULL
- REQUEST_URL — VARCHAR(1000)
- REQUEST_PARAM — TEXT
- RESPONSE_CODE — VARCHAR(20)
- RESPONSE_BODY — TEXT
- CREATED_AT — TIMESTAMP
PURCHASE_INVOICE
- PURCHASE_INVOICE_ID — INTEGER — Primary key — NOT NULL
- PO_ID — INTEGER — Foreign key → PURCHASE_ORDER.PO_ID — NOT NULL
- SUPPLIER_ID — INTEGER — Foreign key → SUPPLIER.SUPPLIER_ID — NOT NULL
- ISSUE_DATE — DATE — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
PURCHASE_ORDER
- PO_ID — INTEGER — Primary key — NOT NULL
- SUPPLIER_ID — INTEGER — Foreign key → SUPPLIER.SUPPLIER_ID — NOT NULL
- REQUEST_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- APPROVE_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID
- PO_DATE — DATE — NOT NULL
- APPROVE_DATE — DATE
- STATUS — VARCHAR(20) — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- MEMO — VARCHAR(500)
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
PURCHASE_ORDER_DETAIL
- PO_DETAIL_ID — INTEGER — Primary key — NOT NULL
- PO_ID — INTEGER — Foreign key → PURCHASE_ORDER.PO_ID — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- ORDER_QTY — INTEGER — NOT NULL
- UNIT_PRICE — DECIMAL(12,2) — NOT NULL
- AMOUNT — DECIMAL(15,2) — NOT NULL
RECEIVING
- RECEIVING_ID — INTEGER — Primary key — NOT NULL
- PO_ID — INTEGER — Foreign key → PURCHASE_ORDER.PO_ID — NOT NULL
- RECEIVED_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- RECEIVING_DATE — DATE — NOT NULL
- STATUS — VARCHAR(20) — NOT NULL
- MEMO — VARCHAR(500)
- CREATED_AT — TIMESTAMP
RECEIVING_DETAIL
- RECEIVING_DETAIL_ID — INTEGER — Primary key — NOT NULL
- RECEIVING_ID — INTEGER — Foreign key → RECEIVING.RECEIVING_ID — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- LOT_NO — VARCHAR(50) — NOT NULL
- EXPIRY_DATE — DATE — NOT NULL
- RECEIVED_QTY — INTEGER — NOT NULL
- UNIT_PRICE — DECIMAL(12,2) — NOT NULL
RETURN_REQUEST
- RETURN_ID — INTEGER — Primary key — NOT NULL
- CUSTOMER_ID — INTEGER — Foreign key → CUSTOMER.CUSTOMER_ID — NOT NULL
- SO_ID — INTEGER — Foreign key → SALES_ORDER.SO_ID
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- INVENTORY_LOT_ID — INTEGER — Foreign key → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
- RETURN_QTY — INTEGER — NOT NULL
- REASON — VARCHAR(500)
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
ROLE
- ROLE_ID — INTEGER — Primary key — NOT NULL
- ROLE_CODE — VARCHAR(30) — NOT NULL
- ROLE_NAME — VARCHAR(50) — NOT NULL
- DESCRIPTION — VARCHAR(300)
- CREATED_AT — TIMESTAMP
SALES_INVOICE
- SALES_INVOICE_ID — INTEGER — Primary key — NOT NULL
- SO_ID — INTEGER — Foreign key → SALES_ORDER.SO_ID — NOT NULL
- CUSTOMER_ID — INTEGER — Foreign key → CUSTOMER.CUSTOMER_ID — NOT NULL
- ISSUE_DATE — DATE — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
SALES_ORDER
- SO_ID — INTEGER — Primary key — NOT NULL
- CUSTOMER_ID — INTEGER — Foreign key → CUSTOMER.CUSTOMER_ID — NOT NULL
- REQUEST_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- APPROVE_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID
- ORDER_DATE — DATE — NOT NULL
- APPROVE_DATE — DATE
- STATUS — VARCHAR(20) — NOT NULL
- TOTAL_AMOUNT — DECIMAL(15,2) — NOT NULL
- MEMO — VARCHAR(500)
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP
SALES_ORDER_DETAIL
- SO_DETAIL_ID — INTEGER — Primary key — NOT NULL
- SO_ID — INTEGER — Foreign key → SALES_ORDER.SO_ID — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- ORDER_QTY — INTEGER — NOT NULL
- UNIT_PRICE — DECIMAL(12,2) — NOT NULL
- AMOUNT — DECIMAL(15,2) — NOT NULL
SETTLEMENT
- SETTLEMENT_ID — INTEGER — Primary key — NOT NULL
- START_DATE — DATE — NOT NULL
- END_DATE — DATE — NOT NULL
- TOTAL_PURCHASE — DECIMAL(15,2) — NOT NULL
- TOTAL_SALES — DECIMAL(15,2) — NOT NULL
- TOTAL_RECEIVABLE — DECIMAL(15,2) — NOT NULL
- TOTAL_PAYABLE — DECIMAL(15,2) — NOT NULL
- GROSS_PROFIT — DECIMAL(15,2) — NOT NULL
- PROFIT_RATE — DECIMAL(5,2) — NOT NULL
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
SHIPMENT
- SHIPMENT_ID — INTEGER — Primary key — NOT NULL
- SO_ID — INTEGER — Foreign key → SALES_ORDER.SO_ID — NOT NULL
- SHIPPED_EMP_ID — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- SHIPMENT_DATE — DATE — NOT NULL
- STATUS — VARCHAR(20) — NOT NULL
- MEMO — VARCHAR(500)
- CREATED_AT — TIMESTAMP
SHIPMENT_DETAIL
- SHIPMENT_DETAIL_ID — INTEGER — Primary key — NOT NULL
- SHIPMENT_ID — INTEGER — Foreign key → SHIPMENT.SHIPMENT_ID — NOT NULL
- SO_DETAIL_ID — INTEGER — Foreign key → SALES_ORDER_DETAIL.SO_DETAIL_ID — NOT NULL
- INVENTORY_LOT_ID — INTEGER — Foreign key → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
- PRODUCT_ID — INTEGER — Foreign key → PRODUCT.PRODUCT_ID — NOT NULL
- SHIPPED_QTY — INTEGER — NOT NULL
STOCK_MOVEMENT
- MOVEMENT_ID — INTEGER — Primary key — NOT NULL
- INVENTORY_LOT_ID — INTEGER — Foreign key → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
- MOVEMENT_TYPE — VARCHAR(20) — NOT NULL
- QTY — INTEGER — NOT NULL
- BEFORE_QTY — INTEGER — NOT NULL
- AFTER_QTY — INTEGER — NOT NULL
- REF_TABLE — VARCHAR(50)
- REF_ID — INTEGER
- CREATED_BY — INTEGER — Foreign key → EMPLOYEE.EMP_ID — NOT NULL
- CREATED_AT — TIMESTAMP
SUPPLIER
- SUPPLIER_ID — INTEGER — Primary key — NOT NULL
- SUPPLIER_NAME — VARCHAR(100) — NOT NULL
- BUSINESS_NO — VARCHAR(20)
- MANAGER_NAME — VARCHAR(50)
- PHONE — VARCHAR(20)
- ADDRESS — VARCHAR(300)
- STATUS — VARCHAR(20) — NOT NULL
- CREATED_AT — TIMESTAMP
- UPDATED_AT — TIMESTAMP