의약품 유통 ERP

테이블 (33)

ACCOUNTS_PAYABLE

  • AP_ID — NUMBER — 기본 키 — NOT NULL
  • SUPPLIER_ID — NUMBER — 외래 키 → SUPPLIER.SUPPLIER_ID — NOT NULL
  • PURCHASE_INVOICE_ID — NUMBER — 외래 키 → PURCHASE_INVOICE.PURCHASE_INVOICE_ID — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • PAID_AMOUNT — NUMBER(15,2) — NOT NULL
  • REMAIN_AMOUNT — NUMBER(15,2) — NOT NULL
  • DUE_DATE — DATE
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE

ACCOUNTS_RECEIVABLE

  • AR_ID — NUMBER — 기본 키 — NOT NULL
  • CUSTOMER_ID — NUMBER — 외래 키 → CUSTOMER.CUSTOMER_ID — NOT NULL
  • SALES_INVOICE_ID — NUMBER — 외래 키 → SALES_INVOICE.SALES_INVOICE_ID — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • PAID_AMOUNT — NUMBER(15,2) — NOT NULL
  • REMAIN_AMOUNT — NUMBER(15,2) — NOT NULL
  • DUE_DATE — DATE
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE

AI_QUERY_LOG

  • QUERY_ID — NUMBER — 기본 키 — NOT NULL
  • EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • USER_QUESTION — CLOB — NOT NULL
  • GENERATED_SQL — CLOB
  • RESULT_SUMMARY — CLOB
  • CREATED_AT — DATE

AI_REPORT

  • REPORT_ID — NUMBER — 기본 키 — NOT NULL
  • REPORT_TYPE — VARCHAR2(30) — NOT NULL
  • TITLE — VARCHAR2(200) — NOT NULL
  • CONTENT — CLOB
  • CREATED_BY — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • CREATED_AT — DATE — NOT NULL

ALERT

  • ALERT_ID — NUMBER — 기본 키 — NOT NULL
  • PRODUCT_ID — NUMBER
  • INVENTORY_LOT_ID — NUMBER
  • ALERT_TYPE — VARCHAR2(30) — NOT NULL
  • MESSAGE — VARCHAR2(500) — NOT NULL
  • CREATED_AT — TIMESTAMP(6)
  • ALERT_LEVEL — VARCHAR2(30)

ALERT_DETAIL

  • ALERT_ID — NUMBER — 외래 키 → ALERT.ALERT_ID — NOT NULL
  • EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • DEPT_ID — NUMBER — NOT NULL
  • ROLE_CODE — VARCHAR2(20)
  • IS_READ — CHAR — NOT NULL
  • IS_DELIVERED — CHAR — NOT NULL

API_LOG

  • API_LOG_ID — NUMBER — 기본 키 — NOT NULL
  • API_NAME — VARCHAR2(100) — NOT NULL
  • REQUEST_URL — VARCHAR2(1000)
  • REQUEST_PARAM — CLOB
  • RESPONSE_CODE — VARCHAR2(20)
  • RESPONSE_BODY — CLOB
  • CREATED_AT — DATE

ATTENDANCE

  • ATTENDANCE_ID — NUMBER — 기본 키 — NOT NULL
  • EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • WORK_DATE — DATE — NOT NULL
  • CHECK_IN — TIMESTAMP
  • CHECK_OUT — TIMESTAMP
  • WORK_HOURS — NUMBER(5,2)
  • STATUS — VARCHAR2(20) — NOT NULL
  • MEMO — VARCHAR2(300)
  • CREATED_AT — DATE

CUSTOMER

  • CUSTOMER_ID — NUMBER — 기본 키 — NOT NULL
  • CUSTOMER_NAME — VARCHAR2(100) — NOT NULL
  • CUSTOMER_TYPE — VARCHAR2(30) — NOT NULL
  • BUSINESS_NO — VARCHAR2(20)
  • CREDIT_LIMIT — NUMBER(15,2) — NOT NULL
  • RECEIVABLE_BALANCE — NUMBER(15,2) — NOT NULL
  • PHONE — VARCHAR2(20)
  • ADDRESS — VARCHAR2(300)
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE
  • UPDATED_AT — DATE

DEPARTMENT

  • DEPT_ID — NUMBER — 기본 키 — NOT NULL
  • DEPT_NAME — VARCHAR2(50) — NOT NULL
  • DEPT_CODE — VARCHAR2(20)
  • DESCRIPTION — VARCHAR2(300)
  • USE_YN — CHAR(1) — NOT NULL
  • CREATED_AT — DATE

DEPT_ROLE_EXCEPTION

  • EXCEPTION_ID — NUMBER — 기본 키 — NOT NULL
  • DEPT_CODE — VARCHAR2(20) — NOT NULL
  • ROLE_CODE — VARCHAR2(20) — NOT NULL
  • EXCEPTION_TITLE — VARCHAR2(100) — NOT NULL
  • EXCEPTION_AUTH — VARCHAR2(50) — NOT NULL
  • DESCRIPTION — VARCHAR2(500)
  • USE_YN — CHAR(1) — NOT NULL
  • CREATED_AT — DATE — NOT NULL

DISPOSAL

  • DISPOSAL_ID — NUMBER — 기본 키 — NOT NULL
  • INVENTORY_LOT_ID — NUMBER — 외래 키 → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID — NOT NULL
  • DISPOSAL_QTY — NUMBER — NOT NULL
  • REASON — VARCHAR2(500)
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_BY — NUMBER — NOT NULL
  • CREATED_AT — DATE

EMPLOYEE

  • EMP_ID — NUMBER — 기본 키 — NOT NULL
  • LOGIN_ID — VARCHAR2(50) — NOT NULL
  • PASSWORD — VARCHAR2(255) — NOT NULL
  • EMP_NAME — VARCHAR2(50) — NOT NULL
  • PHONE — VARCHAR2(20)
  • EMAIL — VARCHAR2(100)
  • ROLE_CODE — NUMBER — NOT NULL
  • DEPT_ID — NUMBER — 외래 키 → DEPARTMENT.DEPT_ID
  • HIRE_DATE — DATE
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE
  • UPDATED_AT — DATE

INVENTORY_ALERT

  • ALERT_ID — NUMBER — 기본 키 — NOT NULL
  • INVENTORY_LOT_ID — NUMBER — 외래 키 → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
  • ALERT_TYPE — VARCHAR2(20) — NOT NULL
  • MESSAGE — VARCHAR2(500) — NOT NULL
  • IS_READ — CHAR(1) — NOT NULL
  • CREATED_AT — DATE

INVENTORY_LOT

  • INVENTORY_LOT_ID — NUMBER — 기본 키 — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID — NOT NULL
  • LOT_NO — VARCHAR2(50) — NOT NULL
  • EXPIRY_DATE — DATE — NOT NULL
  • CURRENT_QTY — NUMBER — NOT NULL
  • LOCATION — VARCHAR2(100) — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE
  • LAST_MODIFIED_AT — DATE

PAYMENT

  • PAYMENT_ID — NUMBER — 기본 키 — NOT NULL
  • AR_ID — NUMBER — 외래 키 → ACCOUNTS_RECEIVABLE.AR_ID — NOT NULL
  • CUSTOMER_ID — NUMBER — 외래 키 → CUSTOMER.CUSTOMER_ID — NOT NULL
  • PAYMENT_DATE — DATE — NOT NULL
  • PAYMENT_AMOUNT — NUMBER(15,2) — NOT NULL
  • PAYMENT_TYPE — VARCHAR2(20) — NOT NULL
  • CREATED_BY — NUMBER — NOT NULL
  • CREATED_AT — DATE

PRODUCT

  • PRODUCT_ID — NUMBER — 기본 키 — NOT NULL
  • PRODUCT_CODE — VARCHAR2(50) — NOT NULL
  • PRODUCT_NAME — VARCHAR2(300) — NOT NULL
  • MAKER_NAME — VARCHAR2(100)
  • UNIT — VARCHAR2(50) — NOT NULL
  • STANDARD_PURCHASE_PRICE — NUMBER(12,2) — NOT NULL
  • STANDARD_SALES_PRICE — NUMBER(12,2) — NOT NULL
  • SAFETY_QTY — NUMBER
  • IS_PRESCRIPTION — CHAR(1) — NOT NULL
  • STORAGE_TYPE — VARCHAR2(50) — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE
  • UPDATED_AT — DATE

PRODUCT_SPEC

  • SPEC_ID — NUMBER — 기본 키 — NOT NULL
  • SPEC_NAME — VARCHAR2(100) — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID
  • DESCRIPTION — VARCHAR2(300)
  • USE_YN — CHAR(1) — NOT NULL
  • CREATED_AT — DATE

PURCHASE_INVOICE

  • PURCHASE_INVOICE_ID — NUMBER — 기본 키 — NOT NULL
  • PO_ID — NUMBER — 외래 키 → PURCHASE_ORDER.PO_ID — NOT NULL
  • SUPPLIER_ID — NUMBER — 외래 키 → SUPPLIER.SUPPLIER_ID — NOT NULL
  • ISSUE_DATE — DATE — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE

PURCHASE_ORDER

  • PO_ID — NUMBER — 기본 키 — NOT NULL
  • SUPPLIER_ID — NUMBER — 외래 키 → SUPPLIER.SUPPLIER_ID — NOT NULL
  • REQUEST_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • APPROVE_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID
  • PO_DATE — DATE — NOT NULL
  • APPROVE_DATE — DATE
  • STATUS — VARCHAR2(20) — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • MEMO — VARCHAR2(500)
  • CREATED_AT — DATE
  • UPDATED_AT — DATE

PURCHASE_ORDER_DETAIL

  • PO_DETAIL_ID — NUMBER — 기본 키 — NOT NULL
  • PO_ID — NUMBER — 외래 키 → PURCHASE_ORDER.PO_ID — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID — NOT NULL
  • ORDER_QTY — NUMBER — NOT NULL
  • UNIT_PRICE — NUMBER(12,2) — NOT NULL
  • AMOUNT — NUMBER(15,2) — NOT NULL

RECEIVING

  • RECEIVING_ID — NUMBER — 기본 키 — NOT NULL
  • PO_ID — NUMBER — 외래 키 → PURCHASE_ORDER.PO_ID — NOT NULL
  • RECEIVED_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • RECEIVING_DATE — DATE — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • MEMO — VARCHAR2(500)
  • CREATED_AT — DATE

RECEIVING_DETAIL

  • RECEIVING_DETAIL_ID — NUMBER — 기본 키 — NOT NULL
  • RECEIVING_ID — NUMBER — 외래 키 → RECEIVING.RECEIVING_ID — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID — NOT NULL
  • LOT_NO — VARCHAR2(50) — NOT NULL
  • EXPIRY_DATE — DATE — NOT NULL
  • RECEIVED_QTY — NUMBER — NOT NULL
  • UNIT_PRICE — NUMBER(12,2) — NOT NULL

REFRESH_TOKEN

  • JWT_ID — VARCHAR2(36) — NOT NULL
  • EMP_ID — INTEGER — NOT NULL
  • EXPIRES_AT — TIMESTAMP — NOT NULL

RETURN_REQUEST

  • RETURN_ID — NUMBER — 기본 키 — NOT NULL
  • CUSTOMER_ID — NUMBER — 외래 키 → CUSTOMER.CUSTOMER_ID — NOT NULL
  • SO_ID — NUMBER
  • PRODUCT_ID — NUMBER — NOT NULL
  • INVENTORY_LOT_ID — NUMBER — 외래 키 → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
  • RETURN_QTY — NUMBER — NOT NULL
  • REASON — VARCHAR2(500)
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_BY — NUMBER — NOT NULL
  • CREATED_AT — TIMESTAMP(6) — NOT NULL
  • SHIPMENT_DETAIL_ID — NUMBER(10) — 외래 키 → SHIPMENT_DETAIL.SHIPMENT_DETAIL_ID
  • APPROVED_BY — NUMBER(10) — 외래 키 → EMPLOYEE.EMP_ID
  • APPROVED_AT — TIMESTAMP(6)
  • COMPLETED_AT — TIMESTAMP(6)
  • REJECT_REASON — VARCHAR2(500)
  • RETURN_GROUP_ID — NUMBER(10) — NOT NULL

SALES_INVOICE

  • SALES_INVOICE_ID — NUMBER — 기본 키 — NOT NULL
  • SO_ID — NUMBER — 외래 키 → SALES_ORDER.SO_ID — NOT NULL
  • CUSTOMER_ID — NUMBER — 외래 키 → CUSTOMER.CUSTOMER_ID — NOT NULL
  • ISSUE_DATE — DATE — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE

SALES_ORDER

  • SO_ID — NUMBER — 기본 키 — NOT NULL
  • CUSTOMER_ID — NUMBER — 외래 키 → CUSTOMER.CUSTOMER_ID — NOT NULL
  • REQUEST_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • APPROVE_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID
  • ORDER_DATE — DATE — NOT NULL
  • APPROVE_DATE — DATE
  • STATUS — VARCHAR2(20) — NOT NULL
  • TOTAL_AMOUNT — NUMBER(15,2) — NOT NULL
  • MEMO — VARCHAR2(500)
  • CREATED_AT — DATE
  • UPDATED_AT — DATE

SALES_ORDER_DETAIL

  • SO_DETAIL_ID — NUMBER — 기본 키 — NOT NULL
  • SO_ID — NUMBER — 외래 키 → SALES_ORDER.SO_ID — NOT NULL
  • PRODUCT_ID — NUMBER — 외래 키 → PRODUCT.PRODUCT_ID — NOT NULL
  • ORDER_QTY — NUMBER — NOT NULL
  • UNIT_PRICE — NUMBER(12,2) — NOT NULL
  • AMOUNT — NUMBER(15,2) — NOT NULL

SETTLEMENT

  • SETTLEMENT_ID — NUMBER — 기본 키 — NOT NULL
  • START_DATE — DATE — NOT NULL
  • END_DATE — DATE — NOT NULL
  • TOTAL_PURCHASE — NUMBER(15,2) — NOT NULL
  • TOTAL_SALES — NUMBER(15,2) — NOT NULL
  • TOTAL_RECEIVABLE — NUMBER(15,2) — NOT NULL
  • TOTAL_PAYABLE — NUMBER(15,2) — NOT NULL
  • GROSS_PROFIT — NUMBER(15,2) — NOT NULL
  • PROFIT_RATE — NUMBER(5,2) — NOT NULL
  • CREATED_BY — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • CREATED_AT — DATE

SHIPMENT

  • SHIPMENT_ID — NUMBER — 기본 키 — NOT NULL
  • SO_ID — NUMBER — 외래 키 → SALES_ORDER.SO_ID — NOT NULL
  • SHIPPED_EMP_ID — NUMBER — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • SHIPMENT_DATE — DATE — NOT NULL
  • STATUS — VARCHAR2(20) — NOT NULL
  • MEMO — VARCHAR2(500)
  • CREATED_AT — DATE

SHIPMENT_DETAIL

  • SHIPMENT_DETAIL_ID — NUMBER — 기본 키 — NOT NULL
  • SHIPMENT_ID — NUMBER — 외래 키 → SHIPMENT.SHIPMENT_ID — NOT NULL
  • SO_DETAIL_ID — NUMBER — 외래 키 → SALES_ORDER_DETAIL.SO_DETAIL_ID — NOT NULL
  • INVENTORY_LOT_ID — NUMBER — 외래 키 → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
  • PRODUCT_ID — NUMBER — NOT NULL
  • SHIPPED_QTY — NUMBER — NOT NULL

STOCK_MOVEMENT

  • MOVEMENT_ID — NUMBER(10) — 기본 키 — NOT NULL
  • INVENTORY_LOT_ID — NUMBER(10) — 외래 키 → INVENTORY_LOT.INVENTORY_LOT_ID — NOT NULL
  • MOVEMENT_TYPE — VARCHAR2(20) — NOT NULL
  • QTY — NUMBER(10) — NOT NULL
  • BEFORE_QTY — NUMBER(10) — NOT NULL
  • AFTER_QTY — NUMBER(10) — NOT NULL
  • SOURCE_TYPE — VARCHAR2(50)
  • SOURCE_ID — NUMBER(10)
  • CREATED_BY — NUMBER(10) — 외래 키 → EMPLOYEE.EMP_ID — NOT NULL
  • CREATED_AT — TIMESTAMP(6)
  • REASON — VARCHAR2(100)

SUPPLIER

  • SUPPLIER_ID — NUMBER — 기본 키 — NOT NULL
  • SUPPLIER_NAME — VARCHAR2(100) — NOT NULL
  • BUSINESS_NO — VARCHAR2(20)
  • MANAGER_NAME — VARCHAR2(50)
  • PHONE — VARCHAR2(20)
  • ADDRESS — VARCHAR2(300)
  • STATUS — VARCHAR2(20) — NOT NULL
  • CREATED_AT — DATE
  • UPDATED_AT — DATE