의약품 유통 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