hjjeong
6b029e20f9
- DDL: quotation_request_master(14 cols) + quotation_request_detail(15 cols) 운영 → RPS 타입 차이: numeric objid → varchar(64), detail.part_objid bigint(part_mng FK) - 데이터: 운영 sample master 4건 / detail 4건 (sales_request_part 미존재 → NULL fallback) - 백엔드 listQuotationRequest — wace salesMng.xml:5248-5349 매퍼 1:1 (vendor → client_mng JOIN, attach_file_info QUOTATION_RECEIVED 카운트) - listVendorOptions(client_mng) 신규 — 발주서 vendor 옵션이 supply_mng 와 분리됨 - listPurchaseRequest.has_quotation_request 분기 활성화 - quote-request page.tsx UI 문자열 내부 참조 제거, vendor 옵션 client_mng 로 교체
74 lines
3.8 KiB
SQL
74 lines
3.8 KiB
SQL
-- ============================================================
|
|
-- 견적요청서 (Quotation Request) — 구매관리 단독
|
|
-- 원본: 운영DB 211.115.91.141:11133/waceplm (quotation_request_master 4건, quotation_request_detail 4건)
|
|
-- 추출일: 2026-05-15
|
|
-- 적용대상: vexplor_rps (11134)
|
|
--
|
|
-- 운영 ↔ RPS 타입 차이 (feedback_createobjid_pattern.md):
|
|
-- 운영: quotation_request_master.objid numeric → RPS varchar(64)
|
|
-- 운영: sales_request_master_objid / project_mgmt_objid numeric → RPS varchar(64) (FK 호환)
|
|
-- 운영: detail.part_objid numeric → RPS bigint (part_mng.objid bigint 호환)
|
|
-- 운영: detail.sales_request_part_objid numeric → RPS varchar(64)
|
|
--
|
|
-- 비즈니스 흐름:
|
|
-- 구매리스트(sales_request_master) → 견적요청서(quotation_request_master + detail)
|
|
-- → 품의서 → 발주서(purchase_order_master + part) → 입고(arrival_plan + inventory_*)
|
|
--
|
|
-- 매퍼 본문(getQuotationRequestList): wace_plm/src/com/pms/mapper/salesMng.xml:5248-5349
|
|
-- ============================================================
|
|
|
|
-- ── 1. quotation_request_master ──────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS quotation_request_master (
|
|
objid varchar(64) NOT NULL,
|
|
quotation_request_no varchar(50),
|
|
sales_request_master_objid varchar(64),
|
|
project_mgmt_objid varchar(64),
|
|
vendor_objid varchar(64),
|
|
vendor_type varchar(20),
|
|
status varchar(50) DEFAULT 'create',
|
|
mail_send_date timestamp,
|
|
mail_send_yn varchar(1) DEFAULT 'N',
|
|
due_date date,
|
|
remark text,
|
|
writer varchar(50),
|
|
reg_date timestamp DEFAULT now(),
|
|
edit_date timestamp,
|
|
CONSTRAINT quotation_request_master_pkey PRIMARY KEY (objid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_qrm_sales_request ON quotation_request_master (sales_request_master_objid);
|
|
CREATE INDEX IF NOT EXISTS idx_qrm_project ON quotation_request_master (project_mgmt_objid);
|
|
CREATE INDEX IF NOT EXISTS idx_qrm_vendor ON quotation_request_master (vendor_objid);
|
|
CREATE INDEX IF NOT EXISTS idx_qrm_status ON quotation_request_master (status);
|
|
|
|
-- ── 2. quotation_request_detail ──────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS quotation_request_detail (
|
|
objid varchar(64) NOT NULL,
|
|
quotation_request_master_objid varchar(64),
|
|
sales_request_part_objid varchar(64),
|
|
part_objid bigint,
|
|
part_no varchar(100),
|
|
part_name varchar(200),
|
|
raw_material varchar(100),
|
|
size varchar(100),
|
|
qty numeric DEFAULT 0,
|
|
unit_price numeric DEFAULT 0,
|
|
total_price numeric DEFAULT 0,
|
|
remark text,
|
|
delivery_request_date varchar(10),
|
|
reg_date timestamp DEFAULT now(),
|
|
edit_date timestamp,
|
|
CONSTRAINT quotation_request_detail_pkey PRIMARY KEY (objid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_qrd_master ON quotation_request_detail (quotation_request_master_objid);
|
|
CREATE INDEX IF NOT EXISTS idx_qrd_part ON quotation_request_detail (sales_request_part_objid);
|
|
|
|
ALTER TABLE quotation_request_detail
|
|
DROP CONSTRAINT IF EXISTS fk_qrd_master;
|
|
ALTER TABLE quotation_request_detail
|
|
ADD CONSTRAINT fk_qrd_master
|
|
FOREIGN KEY (quotation_request_master_objid)
|
|
REFERENCES quotation_request_master (objid)
|
|
ON DELETE CASCADE;
|