통합 구매관리 시스템

테이블 (47)

approval_line

  • line_id — INT
  • line_name — VARCHAR(100) — NOT NULL
  • dept_id — INT — NOT NULL
  • description — TEXT
  • is_active — CHAR(1)

approval_request

  • request_id — INT
  • line_id — INT — NOT NULL
  • requester_id — INT — NOT NULL
  • dept_id — INT — NOT NULL
  • current_step — INT — NOT NULL
  • upper_code — VARCHAR(255)
  • lower_code — INT

approval_status

  • status_id — INT
  • request_id — INT — NOT NULL
  • step_id — INT — NOT NULL
  • approver_id — INT — NOT NULL
  • approved — BOOLEAN
  • decision_date — DATETIME
  • comments — TEXT

approval_step

  • step_id — INT
  • line_id — INT — NOT NULL
  • approver_id — INT — NOT NULL
  • position_code — VARCHAR(20) — NOT NULL
  • step_order — INT — NOT NULL

attachment

  • attachment_id — INT
  • related_table — VARCHAR(50) — NOT NULL
  • related_id — INT — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_path — VARCHAR(255) — NOT NULL
  • file_type — VARCHAR(100)
  • file_size — BIGINT — NOT NULL

bidding

  • bidding_id — INT
  • title — VARCHAR(100) — NOT NULL
  • project_id — INT — NOT NULL
  • dept_id — INT — NOT NULL
  • member_id — INT
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • price_weight — INT
  • quality_weight — INT
  • delivery_weight — INT
  • reliability_weight — INT
  • description — TEXT
  • status_lower_code — INT
  • status_upper_code — VARCHAR(255)

bidding_analysis

  • analysis_id — INT
  • bidding_id — INT — NOT NULL
  • participation_id — INT — NOT NULL
  • partner_id — INT
  • member_id — INT — NOT NULL
  • bid_price — DECIMAL(15,2) — NOT NULL
  • price_score — INT
  • quality_score — INT
  • delivery_score — INT
  • reliability_score — INT
  • total_score — INT
  • comments — TEXT

bidding_item

  • bidding_item_id — INT
  • bidding_id — INT — NOT NULL
  • item_id — INT — NOT NULL
  • quantity — INT — NOT NULL
  • due_date — DATE
  • note — VARCHAR(500)

bidding_participation

  • participation_id — INT
  • bidding_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • proposal — TEXT
  • status_lower_code — INT
  • status_upper_code — VARCHAR(255)

bidding_participation_item

  • participation_item_id — INT
  • participation_id — INT — NOT NULL
  • bidding_item_id — INT — NOT NULL
  • bid_unit_price — DECIMAL(15,2) — NOT NULL
  • bid_quantity — INT — NOT NULL
  • delivery_date — DATE

bidding_selected

  • selected_id — INT
  • bidding_id — INT — NOT NULL
  • participation_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • selected_by — INT — NOT NULL

chat_invitation

  • id — INT
  • chat_room_id — INT — NOT NULL
  • inviting_member_id — INT — NOT NULL
  • invited_member_id — INT — NOT NULL
  • invitation_message — VARCHAR(255)
  • status — VARCHAR(20) — NOT NULL

chat_message

  • msg_id — INT
  • chat_room_id — INT — NOT NULL
  • sender_id — INT — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • is_read — BOOLEAN — NOT NULL

chat_participant

  • id — INT
  • chat_room_id — INT — NOT NULL
  • member_id — INT — NOT NULL
  • joined_at — DATETIME — NOT NULL

chat_room

  • chat_room_id — INT
  • name — VARCHAR(255) — NOT NULL
  • owner_id — INT — NOT NULL

contract

  • bidding_id — INT
  • project_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • partner_contact_id — INT — NOT NULL
  • approver_id — INT — NOT NULL
  • contract_name — VARCHAR(255)
  • contract_date — DATE — NOT NULL
  • contract_period_start — DATE — NOT NULL
  • contract_period_end — DATE — NOT NULL
  • contract_amount — DECIMAL(15,2) — NOT NULL
  • contract_document — TEXT
  • contract_file_path — VARCHAR(255)
  • lower_status_code — INT — NOT NULL
  • upper_status_code — VARCHAR(255) — NOT NULL

contract_item

  • contract_item_id — INT
  • contract_id — INT — NOT NULL
  • item_id — INT — NOT NULL
  • contracted_quantity — INT — NOT NULL
  • unit_price — DECIMAL(15,2) — NOT NULL

department

  • dept_id — INT
  • dept_code — VARCHAR(20) — NOT NULL
  • dept_name — VARCHAR(50) — NOT NULL
  • is_active — CHAR(1)

final_evaluation

  • evaluation_id — INT
  • project_id — INT — NOT NULL
  • contract_id — INT — NOT NULL
  • project_completion_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • evaluation_date — DATETIME — NOT NULL
  • price_score — DOUBLE — NOT NULL
  • quality_score — DOUBLE — NOT NULL
  • delivery_score — DOUBLE — NOT NULL
  • service_response_score — DOUBLE — NOT NULL
  • total_score — DOUBLE — NOT NULL
  • grade — VARCHAR(2)
  • confirmation_status — TINYINT(1) — NOT NULL

inspection_history

  • inspection_history_id — INT
  • receipt_item_id — INT — NOT NULL
  • project_id — INT — NOT NULL
  • inspector_id — INT — NOT NULL
  • inspection_date — DATETIME — NOT NULL
  • previous_status_lower — INT
  • previous_status_upper — VARCHAR(255)
  • new_status_lower — INT
  • new_status_upper — VARCHAR(255)
  • total_quantity — INT
  • defective_quantity — INT
  • notes — VARCHAR(255)

invoice

  • invoice_id — INT
  • invoice_number — VARCHAR(255) — NOT NULL
  • partner_id — INT — NOT NULL
  • project_id — INT — NOT NULL
  • amount — DECIMAL(15,2) — NOT NULL
  • due_date — DATE — NOT NULL
  • vat_rate — DECIMAL(5,2) — NOT NULL
  • lower_code — INT — NOT NULL
  • upper_code — VARCHAR(255) — NOT NULL

invoice_item

  • item_id — INT
  • invoice_id — INT — NOT NULL
  • contract_item_id — INT — NOT NULL
  • description — VARCHAR(255)
  • quantity — INT — NOT NULL
  • unit_price — DECIMAL(15,2) — NOT NULL
  • total_price — DECIMAL(15,2) — NOT NULL

item_category

  • category_id — INT
  • category_name — VARCHAR(100) — NOT NULL

item_prices

  • item_price_id — INT
  • item_id — INT — NOT NULL
  • partner_id — INT
  • unit_price — DECIMAL(15,2) — NOT NULL
  • effective_date — DATE — NOT NULL
  • expiry_date — DATE

items

  • item_id — INT
  • item_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • item_category — INT

job_position

  • position_code — VARCHAR(20)
  • position_name — VARCHAR(50) — NOT NULL
  • level — INT
  • is_active — CHAR(1)

lower_status_code

  • lower_code — INT — 기본 키 — NOT NULL
  • upper_code — VARCHAR(255) — 기본 키 — NOT NULL
  • status_name — VARCHAR(50) — NOT NULL
  • description — VARCHAR(255)

member

  • member_id — INT
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(20)
  • employee_number — VARCHAR(6)
  • role — VARCHAR(20)
  • dept_id — INT
  • position_code — VARCHAR(20)
  • is_active — CHAR(1) — NOT NULL

message

  • msg_id — INT
  • sender_id — INT — NOT NULL
  • receiver_id — INT — NOT NULL
  • content — VARCHAR(255) — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • deleted_by_sender — BOOLEAN — NOT NULL
  • deleted_by_receiver — BOOLEAN — NOT NULL

notification

  • notification_id — INT
  • recipient_id — INT — NOT NULL
  • sender_id — INT — NOT NULL
  • notification_type_upper — VARCHAR(255) — NOT NULL
  • notification_type_lower — INT — NOT NULL
  • reference_table — VARCHAR(255) — NOT NULL
  • reference_id — INT — NOT NULL
  • message — VARCHAR(500) — NOT NULL
  • is_read — BOOLEAN — NOT NULL

order_item

  • order_item_id — INT
  • order_id — INT — NOT NULL
  • item_id — INT — NOT NULL
  • ordered_quantity — INT — NOT NULL
  • unit_price — DECIMAL(15,2) — NOT NULL

orders

  • order_id — INT
  • order_number — VARCHAR(255) — NOT NULL
  • project_id — INT — NOT NULL
  • contract_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • approver_id — INT — NOT NULL
  • order_date — DATETIME — NOT NULL
  • delivery_due_date — DATE
  • delivery_address — VARCHAR(255)
  • delivery_contact — VARCHAR(255)
  • payment_due_date — DATE
  • payment_method — VARCHAR(255)
  • tax_rate — DOUBLE
  • notes — TEXT
  • lower_status_code — INT — NOT NULL
  • upper_status_code — VARCHAR(255) — NOT NULL

partner

  • partner_id — INT
  • company_name — VARCHAR(100) — NOT NULL
  • business_number — VARCHAR(20) — NOT NULL
  • address — TEXT
  • contact_id — INT — NOT NULL
  • is_active — CHAR(1)

partner_rating

  • rating_id — INT
  • partner_id — INT — NOT NULL
  • company_name — VARCHAR(100) — NOT NULL
  • evaluation_date — DATETIME — NOT NULL
  • evaluation_round — INT — NOT NULL
  • price_score — DOUBLE — NOT NULL
  • quality_score — DOUBLE — NOT NULL
  • delivery_score — DOUBLE — NOT NULL
  • service_score — DOUBLE — NOT NULL
  • total_score — DOUBLE — NOT NULL
  • grade — VARCHAR(2) — NOT NULL

payment

  • payment_id — INT
  • invoice_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • project_id — INT — NOT NULL
  • amount — DECIMAL(15,2) — NOT NULL
  • payment_date — DATETIME
  • payment_method — VARCHAR(20) — NOT NULL
  • reference_number — VARCHAR(100)
  • lower_code — INT — NOT NULL
  • upper_code — VARCHAR(255) — NOT NULL

project

  • project_id — INT
  • project_name — VARCHAR(100) — NOT NULL
  • purchase_request_id — INT — NOT NULL
  • start_date — DATE
  • end_date — DATE
  • total_cost — DECIMAL(15,2) — NOT NULL
  • lower_status_code — INT — NOT NULL
  • upper_status_code — VARCHAR(255) — NOT NULL

project_completion

  • completion_id — INT
  • project_id — INT — NOT NULL
  • completion_date — VARCHAR(255) — NOT NULL
  • is_completed — BOOLEAN — NOT NULL
  • final_evaluation — VARCHAR(255)
  • lower_status_code — INT — NOT NULL
  • upper_status_code — VARCHAR(255) — NOT NULL

purchase_request

  • request_id — INT
  • title — VARCHAR(100) — NOT NULL
  • requester_id — INT — NOT NULL
  • dept_id — INT — NOT NULL
  • line_id — INT
  • approval_request_id — INT
  • lower_status_code — INT — NOT NULL
  • upper_status_code — VARCHAR(255) — NOT NULL
  • current_step — INT — NOT NULL
  • is_active — CHAR(1)

purchase_request_item

  • item_id — INT
  • request_id — INT — NOT NULL
  • item_master_id — INT — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — DECIMAL(15,2) — NOT NULL
  • due_date — DATE — NOT NULL
  • specifications — TEXT

receipt

  • receipt_id — INT
  • project_id — INT — NOT NULL
  • order_id — INT — NOT NULL
  • partner_id — INT — NOT NULL
  • receipt_date — DATETIME — NOT NULL
  • receiver_id — INT — NOT NULL
  • inspector_id — INT
  • total_received_quantity — INT — NOT NULL
  • total_approved_quantity — INT — NOT NULL
  • total_rejected_quantity — INT — NOT NULL
  • status_lower — INT
  • status_upper — VARCHAR(255)
  • notes — VARCHAR(255)

receipt_defects

  • defect_id — INT
  • receipt_item_id — INT — NOT NULL
  • defect_type_lower — INT — NOT NULL
  • defect_type_upper — VARCHAR(255) — NOT NULL
  • defect_severity_lower — INT — NOT NULL
  • defect_severity_upper — VARCHAR(255) — NOT NULL
  • defect_quantity — INT — NOT NULL
  • description — VARCHAR(255)
  • image_file_path — VARCHAR(255)

receipt_items

  • receipt_item_id — INT
  • receipt_id — INT — NOT NULL
  • order_item_id — INT — NOT NULL
  • item_id — INT — NOT NULL
  • received_quantity — INT — NOT NULL
  • approved_quantity — INT — NOT NULL
  • rejected_quantity — INT — NOT NULL
  • inspection_status_lower — INT
  • inspection_status_upper — VARCHAR(255)
  • inspection_date — DATETIME
  • report_file — VARCHAR(255)

refresh_token

  • id — INT
  • email — VARCHAR(255) — NOT NULL
  • refresh_token — VARCHAR(255) — NOT NULL

report_history

  • report_id — INT
  • report_name — VARCHAR(200) — NOT NULL
  • template_id — INT
  • generator_id — INT — NOT NULL
  • report_params — TEXT
  • report_path — VARCHAR(500)
  • file_format — VARCHAR(10)
  • is_public — CHAR(1)

report_template

  • template_id — INT
  • template_name — VARCHAR(100) — NOT NULL
  • report_type — VARCHAR(20) — NOT NULL
  • template_config — TEXT — NOT NULL
  • creator_id — INT — NOT NULL
  • is_active — CHAR(1)

return_exchange

  • return_exchange_id — INT
  • receipt_item_id — INT — NOT NULL
  • project_id — INT — NOT NULL
  • request_quantity — INT — NOT NULL
  • processed_quantity — INT — NOT NULL
  • return_type_lower — INT — NOT NULL
  • return_type_upper — VARCHAR(255) — NOT NULL
  • return_status_lower — INT — NOT NULL
  • return_status_upper — VARCHAR(255) — NOT NULL
  • reason — VARCHAR(255)
  • result — VARCHAR(255)
  • image_file_path — VARCHAR(255)

upper_status_code

  • upper_code — VARCHAR(20)
  • upper_name — VARCHAR(50) — NOT NULL