Budget
Tables (11)
approval_history (결재 이력)
- hist_id (PK) — NOTNULL — Primary key — NOT NULL
- approval_user_id (결재유저 id) — NOTNULL — Foreign key → user.user_id — NOT NULL
- target_type (대상종류) — NOTNULL (commit, po, expense) — NOT NULL
- target_id (타겟id) — NOTNULL — NOT NULL
- step_no (단계번호) — NOTNULL — NOT NULL
- action_code (액션코드) — NOTNULL APPROVE, REJECT — NOT NULL
- remark (의견) — NOTNULL — ARCHAR(1000
- acted_at (결재일) — NOT NULL
approval_line (결재 라인)
- approval_line_id (PK) — NOTNULL — Primary key — NOT NULL
- line_name (결재선명칭) — NOTNULL — NOT NULL
- steps_json (결제 단계정보) — CLOB — NOT NULL
- active_yn — CHAR(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- CHECK — (active_yn IN ('Y','N'))
budget (연간 부서 예산)
- budget_id (PK) — NOTNULL — Primary key — NOT NULL
- dept_id (부서id) — NOTNULL — Foreign key → department.dept_id — NOT NULL
- budget_year (예산연도) — NOTNULL — NOT NULL
- limit_amount (연간 예산한도) — NOTNULL — NOT NULL
- committed_amount (전체 예약합계) — NOTNULL — NOT NULL
- spent_amount (실제 지출 합계) — NOTNULL — NOT NULL
- status_code (상태코드) — NOTNULL — NOT NULL
- remark (메모) — NOTNULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- CHECK — (status_code IN ('OPEN','CLOSED'))
commit_request (예산 집행 요청 (쓸 예산 예약))
- commit_id (PK) — NOTNULL — Primary key — NOT NULL
- budget_id (연산부서예산 id) — NOTNULL — Foreign key → budget.budget_id — NOT NULL
- dept_id (부서id) — NOTNULL — Foreign key → department.dept_id — NOT NULL
- req_dt (요청일) — NOT NULL
- requeser_user_id (요청유저id) — NOTNULL — Foreign key → user.user_id — NOT NULL
- project_id (프로젝트id) — niull 일 수 있다 단순 예산 일 때 — Foreign key → project.project_id — NOT NULL
- vendor_id (거래처id) — 없을 수 있음 — Foreign key → vendor.vendor_id — NOT NULL
- approval_line_id (결재라인id) — NOTNULL — Foreign key → approval_line.approval_line_id — NOT NULL
- item_desc (항목 설명) — NOTNULL — NOT NULL
- amount_commit (요청금액) — NOTNULL — NOT NULL
- status_code (상태코드) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- procurement_type (PO \ NON_PO) — NOTNULL
- commit_name (집행요청명) — CMT-YYYY-DEPTCODE-SEQS 순
- CHECK — (status_code IN ('REQUESTED','REVIEW','APPROVED','REJECTED','CANCELED','CLOSED'))
department (부서)
- dept_id (PK) — NOTNULL — Primary key — NOT NULL
- manager_user_id (부서장id) — NOTNULL — Foreign key → user.user_id — NOT NULL
- dept_code (부서 코드) — NOTNULL — NOT NULL
- dept_name (부서명) — NOTNULL — NOT NULL
- active_yn (사용 여부) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
expenditure (지출)
- expenditure_id (PK) — NOTNULL — Primary key — NOT NULL
- commit_id (집행요청id) — NOTNULL — Foreign key → commit_request.commit_id — NOT NULL
- po_id (발주id) — (po없이 commit 에서 바로 지출할 수 도 있어서 null 가능성 — Foreign key → po.po_id — NOT NULL
- vendor_id (거래처id) — Foreign key → vendor.vendor_id — NOT NULL
- expenditure_dt (지출일) — NOT NULL
- tax_invoice_no (세금계산서 번호)
- amount_expend (지출금액) — NOTNULL — NOT NULL
- commit_name (예산집행 이름) — NOTNULL
- status_code (상태코드) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- CHECK — (status_code IN ('EXPENSED','REVERSED'))
- expend_tpye (발주/비발주3) — NOTNULL (PO|NON_PO)
- expend_category_code (지출분류) — 자재비 임금 복리후생 공과금 등등 ..
po (발주(승인된 커밋을 기반 발주서))
- po_id (PK) — NOTNULL — Primary key — NOT NULL
- commit_id (집행요청_id) — NOTNULL — Foreign key → commit_request.commit_id — NOT NULL
- vendor_id (거래처id) — NOTNULL — Foreign key → vendor.vendor_id — NOT NULL
- po_no (발주서 번호) — NOTNULL — NOT NULL
- po_dt (발주일) — NOT NULL
- amount_po (발주총액) — NOTNULL (amount_commit >= 0) — NOT NULL
- status_code (상태코드) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- CHECK — (status_code IN ('PO_ISSUED','CANCELED'))
- remark (메모) — VARCHAR(20)
project (프로젝트)
- project_id (PK) — NOTNULL — Primary key — NOT NULL
- project_code (프로젝트 코드) — NOTNULL — NOT NULL
- project_name (프로젝트명) — NOTNULL — NOT NULL
- dept_id (부서명) — NOTNULL — Foreign key → department.dept_id — NOT NULL
- status_code (상태코드) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — DATETIME — NOT NULL
- CHECK — (status_code IN ('ACTIVE','CLOSED',REVENUED))
- unit_price (프로젝트단가) — NOTNULL
- headcount (투입 인원수) — NOTNULL
- duration_month (투입기간) — NOTNULL
- expected_cost (예상원가)
- revenue_target (목표) — NOTNULL
- remark (메모) — NOTNULL
- budget_id (예산id) — NOTNULL — Foreign key → budget.budget_id — NOT NULL
- excution_year (실행연도) — NOTNULL
- status_code (상태) — NOTNULL
revenue (매출)
- revenue_id (PK) — NOTNULL — Primary key — NOT NULL
- dept_id (부서id) — NOTNULL — Foreign key → department.dept_id — NOT NULL
- project_id (프로젝트id) — 프로젝트 매출이면 참조 , 아니면 NULL — Foreign key → project.project_id — NOT NULL
- revenue_year (연도) — NOTNULL
- revenue_date (매출일) — NOTNULL
- revenue_type (매출타입) — PROJECT / NON_PROJECT
- revenue_target (목표매출액)
- revenue_actual (실제매출) — NOTNULL
- remark (메모) — NOTNULL
- create_at (생성일)
- update_at (수정일)
- status_code (상태) — NOTNULL
user (유저)
- user_id (PK) — NOTNULL — Primary key — NOT NULL
- dept_id (부서id) — NOTNULL — Foreign key → department.dept_id — NOT NULL
- user_name (유저명) — NOTNULL — NOT NULL
- email (이메일) — NOTNULL — NOT NULL
- role_code (권한) — NOTNULL — NOT NULL
- active_yn (사용여부) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- CHECK — (role_code IN ('ADMIN','MANAGER','APPROVER','USER'))
- psition (직책) — NOTNULL
vendor (거래처)
- vendor_id (PK) — NOTNULL — Primary key — NOT NULL
- vendor_code (거래처코드) — NOTNULL — NOT NULL
- vendor_name (거래처명) — NOTNULL — NOT NULL
- contact_person (담당자명) — NOTNULL
- phone (연락처) — NOTNULL
- email (이메일) — NOTNULL
- active_yn (사용 여부) — NOTNULL — NOT NULL
- created_at (생성일) — NOT NULL
- updated_at (수정일) — NOT NULL
- CHECK — (active_yn IN ('Y','N'))