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