231 lines
8.1 KiB
SQL
231 lines
8.1 KiB
SQL
-- 견적 품목 테이블 생성 스크립트
|
|
-- 작성일: 2025-10-17
|
|
-- 설명: 견적 요청 시 여러 품목을 등록할 수 있도록 품목 정보를 별도 테이블로 관리
|
|
|
|
-- ====================================
|
|
-- 1. 견적 품목 테이블 (CONTRACT_ITEM)
|
|
-- ====================================
|
|
-- 견적(CONTRACT_MGMT)에 속한 품목 정보를 저장
|
|
CREATE TABLE IF NOT EXISTS CONTRACT_ITEM (
|
|
OBJID VARCHAR(50) PRIMARY KEY, -- 품목 고유 ID
|
|
CONTRACT_OBJID VARCHAR(50) NOT NULL, -- 견적 OBJID (FK)
|
|
SEQ INTEGER NOT NULL, -- 품목 순번
|
|
PART_NO VARCHAR(100) NOT NULL, -- 품번
|
|
PART_NAME VARCHAR(200) NOT NULL, -- 품명
|
|
QUANTITY INTEGER NOT NULL DEFAULT 1, -- 수량
|
|
DUE_DATE VARCHAR(10), -- 요청납기 (YYYY-MM-DD)
|
|
CUSTOMER_REQUEST TEXT, -- 고객요청사항
|
|
REGDATE TIMESTAMP NOT NULL DEFAULT NOW(), -- 등록일시
|
|
WRITER VARCHAR(50), -- 등록자
|
|
CHGDATE TIMESTAMP, -- 수정일시
|
|
CHG_USER_ID VARCHAR(50), -- 수정자
|
|
STATUS VARCHAR(20) DEFAULT 'ACTIVE', -- 상태 (ACTIVE/INACTIVE)
|
|
|
|
-- 제약조건
|
|
CONSTRAINT FK_CONTRACT_ITEM_CONTRACT FOREIGN KEY (CONTRACT_OBJID)
|
|
REFERENCES CONTRACT_MGMT(OBJID) ON DELETE CASCADE,
|
|
CONSTRAINT CHK_CONTRACT_ITEM_QUANTITY CHECK (QUANTITY > 0)
|
|
);
|
|
|
|
-- 인덱스 생성
|
|
CREATE INDEX IDX_CONTRACT_ITEM_CONTRACT ON CONTRACT_ITEM(CONTRACT_OBJID);
|
|
CREATE INDEX IDX_CONTRACT_ITEM_SEQ ON CONTRACT_ITEM(CONTRACT_OBJID, SEQ);
|
|
|
|
-- 테이블 코멘트
|
|
COMMENT ON TABLE CONTRACT_ITEM IS '견적 품목 정보';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.OBJID IS '품목 고유 ID';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.CONTRACT_OBJID IS '견적 OBJID (FK)';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.SEQ IS '품목 순번';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.PART_NO IS '품번';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.PART_NAME IS '품명';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.QUANTITY IS '수량';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.DUE_DATE IS '요청납기 (YYYY-MM-DD)';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.CUSTOMER_REQUEST IS '고객요청사항';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.REGDATE IS '등록일시';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.WRITER IS '등록자';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.CHGDATE IS '수정일시';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.CHG_USER_ID IS '수정자';
|
|
COMMENT ON COLUMN CONTRACT_ITEM.STATUS IS '상태 (ACTIVE/INACTIVE)';
|
|
|
|
|
|
-- ====================================
|
|
-- 2. 품목별 S/N 테이블 (CONTRACT_ITEM_SERIAL)
|
|
-- ====================================
|
|
-- 각 품목에 속한 S/N 정보를 저장
|
|
CREATE TABLE IF NOT EXISTS CONTRACT_ITEM_SERIAL (
|
|
OBJID VARCHAR(50) PRIMARY KEY, -- S/N 고유 ID
|
|
ITEM_OBJID VARCHAR(50) NOT NULL, -- 품목 OBJID (FK)
|
|
SEQ INTEGER NOT NULL, -- S/N 순번
|
|
SERIAL_NO VARCHAR(200) NOT NULL, -- S/N
|
|
REGDATE TIMESTAMP NOT NULL DEFAULT NOW(), -- 등록일시
|
|
WRITER VARCHAR(50), -- 등록자
|
|
STATUS VARCHAR(20) DEFAULT 'ACTIVE', -- 상태 (ACTIVE/INACTIVE)
|
|
|
|
-- 제약조건
|
|
CONSTRAINT FK_CONTRACT_ITEM_SERIAL_ITEM FOREIGN KEY (ITEM_OBJID)
|
|
REFERENCES CONTRACT_ITEM(OBJID) ON DELETE CASCADE,
|
|
CONSTRAINT UQ_CONTRACT_ITEM_SERIAL UNIQUE (ITEM_OBJID, SERIAL_NO)
|
|
);
|
|
|
|
-- 인덱스 생성
|
|
CREATE INDEX IDX_CONTRACT_ITEM_SERIAL_ITEM ON CONTRACT_ITEM_SERIAL(ITEM_OBJID);
|
|
CREATE INDEX IDX_CONTRACT_ITEM_SERIAL_SEQ ON CONTRACT_ITEM_SERIAL(ITEM_OBJID, SEQ);
|
|
|
|
-- 테이블 코멘트
|
|
COMMENT ON TABLE CONTRACT_ITEM_SERIAL IS '품목별 S/N 정보';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.OBJID IS 'S/N 고유 ID';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.ITEM_OBJID IS '품목 OBJID (FK)';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.SEQ IS 'S/N 순번';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.SERIAL_NO IS 'S/N';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.REGDATE IS '등록일시';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.WRITER IS '등록자';
|
|
COMMENT ON COLUMN CONTRACT_ITEM_SERIAL.STATUS IS '상태 (ACTIVE/INACTIVE)';
|
|
|
|
|
|
-- ====================================
|
|
-- 3. 시퀀스 생성 (OBJID 자동 생성용)
|
|
-- ====================================
|
|
-- 품목 OBJID 시퀀스
|
|
CREATE SEQUENCE IF NOT EXISTS SEQ_CONTRACT_ITEM
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
-- S/N OBJID 시퀀스
|
|
CREATE SEQUENCE IF NOT EXISTS SEQ_CONTRACT_ITEM_SERIAL
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
-- ====================================
|
|
-- 4. 샘플 데이터 조회 쿼리
|
|
-- ====================================
|
|
/*
|
|
-- 견적별 품목 목록 조회
|
|
SELECT
|
|
CI.OBJID,
|
|
CI.SEQ,
|
|
CI.PART_NO,
|
|
CI.PART_NAME,
|
|
CI.QUANTITY,
|
|
CI.DUE_DATE,
|
|
CI.CUSTOMER_REQUEST,
|
|
STRING_AGG(CIS.SERIAL_NO, ', ' ORDER BY CIS.SEQ) AS SERIAL_NOS,
|
|
COUNT(CIS.OBJID) AS SERIAL_COUNT
|
|
FROM
|
|
CONTRACT_ITEM CI
|
|
LEFT JOIN CONTRACT_ITEM_SERIAL CIS ON CI.OBJID = CIS.ITEM_OBJID AND CIS.STATUS = 'ACTIVE'
|
|
WHERE
|
|
CI.CONTRACT_OBJID = 'CONTRACT_OBJID_HERE'
|
|
AND CI.STATUS = 'ACTIVE'
|
|
GROUP BY
|
|
CI.OBJID, CI.SEQ, CI.PART_NO, CI.PART_NAME, CI.QUANTITY, CI.DUE_DATE, CI.CUSTOMER_REQUEST
|
|
ORDER BY
|
|
CI.SEQ;
|
|
|
|
-- 품목별 S/N 목록 조회
|
|
SELECT
|
|
OBJID,
|
|
SEQ,
|
|
SERIAL_NO
|
|
FROM
|
|
CONTRACT_ITEM_SERIAL
|
|
WHERE
|
|
ITEM_OBJID = 'ITEM_OBJID_HERE'
|
|
AND STATUS = 'ACTIVE'
|
|
ORDER BY
|
|
SEQ;
|
|
*/
|
|
|
|
|
|
-- ====================================
|
|
-- 5. 기존 데이터 마이그레이션 (선택사항)
|
|
-- ====================================
|
|
-- 기존 CONTRACT_MGMT 테이블의 PART_NO, PART_NAME, SERIAL_NO, QUANTITY 데이터를
|
|
-- 새로운 CONTRACT_ITEM 테이블로 마이그레이션
|
|
|
|
/*
|
|
-- 기존 품목 데이터를 CONTRACT_ITEM으로 복사
|
|
INSERT INTO CONTRACT_ITEM (
|
|
OBJID,
|
|
CONTRACT_OBJID,
|
|
SEQ,
|
|
PART_NO,
|
|
PART_NAME,
|
|
QUANTITY,
|
|
DUE_DATE,
|
|
CUSTOMER_REQUEST,
|
|
REGDATE,
|
|
WRITER,
|
|
STATUS
|
|
)
|
|
SELECT
|
|
'ITEM_' || CM.OBJID AS OBJID,
|
|
CM.OBJID AS CONTRACT_OBJID,
|
|
1 AS SEQ,
|
|
CM.PART_NO,
|
|
CM.PART_NAME,
|
|
COALESCE(CM.QUANTITY, 1) AS QUANTITY,
|
|
CM.DUE_DATE,
|
|
CM.CUSTOMER_REQUEST,
|
|
CM.REGDATE,
|
|
CM.WRITER,
|
|
'ACTIVE' AS STATUS
|
|
FROM
|
|
CONTRACT_MGMT CM
|
|
WHERE
|
|
CM.PART_NO IS NOT NULL
|
|
AND CM.PART_NO != ''
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM CONTRACT_ITEM CI WHERE CI.CONTRACT_OBJID = CM.OBJID
|
|
);
|
|
|
|
-- 기존 S/N 데이터를 CONTRACT_ITEM_SERIAL로 복사 (쉼표로 구분된 경우)
|
|
-- PostgreSQL의 string_to_array 함수 사용
|
|
INSERT INTO CONTRACT_ITEM_SERIAL (
|
|
OBJID,
|
|
ITEM_OBJID,
|
|
SEQ,
|
|
SERIAL_NO,
|
|
REGDATE,
|
|
WRITER,
|
|
STATUS
|
|
)
|
|
SELECT
|
|
'SN_' || CI.OBJID || '_' || ROW_NUMBER() OVER (PARTITION BY CI.OBJID ORDER BY sn_value) AS OBJID,
|
|
CI.OBJID AS ITEM_OBJID,
|
|
ROW_NUMBER() OVER (PARTITION BY CI.OBJID ORDER BY sn_value) AS SEQ,
|
|
TRIM(sn_value) AS SERIAL_NO,
|
|
CI.REGDATE,
|
|
CI.WRITER,
|
|
'ACTIVE' AS STATUS
|
|
FROM
|
|
CONTRACT_ITEM CI
|
|
CROSS JOIN LATERAL UNNEST(string_to_array(
|
|
(SELECT SERIAL_NO FROM CONTRACT_MGMT WHERE OBJID = CI.CONTRACT_OBJID),
|
|
','
|
|
)) AS sn_value
|
|
WHERE
|
|
TRIM(sn_value) != ''
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM CONTRACT_ITEM_SERIAL CIS
|
|
WHERE CIS.ITEM_OBJID = CI.OBJID
|
|
AND CIS.SERIAL_NO = TRIM(sn_value)
|
|
);
|
|
*/
|
|
|
|
|
|
-- ====================================
|
|
-- 6. 권한 설정 (필요시)
|
|
-- ====================================
|
|
-- GRANT SELECT, INSERT, UPDATE, DELETE ON CONTRACT_ITEM TO plm_user;
|
|
-- GRANT SELECT, INSERT, UPDATE, DELETE ON CONTRACT_ITEM_SERIAL TO plm_user;
|
|
-- GRANT USAGE, SELECT ON SEQUENCE SEQ_CONTRACT_ITEM TO plm_user;
|
|
-- GRANT USAGE, SELECT ON SEQUENCE SEQ_CONTRACT_ITEM_SERIAL TO plm_user;
|
|
|