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'))