-- ============================================================ -- WBS관리(P2) 운영 DDL — wace_plm 운영DB(211.115.91.141:11133/waceplm) 추출 -- 추출일: 2026-05-11 -- 추출 방법: information_schema 쿼리 (pg_dump 14.19 ↔ PG 16.8 mismatch) -- 대상 테이블 8개 (운영 카운트): -- pms_wbs_task 58 cols / 0건 -- pms_wbs_task_info 22 cols / 518건 ← 일반 task 리스트 (실사용) -- pms_wbs_task_confirm 7 cols / 0건 (objid numeric) -- pms_wbs_task_standard 10 cols / 5건 (트리 표준) -- pms_wbs_task_standard2 20 cols / 74건 ← 일반 task 표준 (실사용) -- pms_wbs_template 6 cols / 1건 -- setup_wbs_task 20 cols / 2,576건 ← 진척율 데이터 -- setup_wbs_task_standard 19 cols / 46건 -- -- 비고: -- · 운영 스키마 1:1 보존 — 길이 명시 없는 varchar 그대로(무제한). -- · objid 컬럼은 wace Java 측에서 UUID/시퀀스 생성(시퀀스 없음). -- · company_code 분기 없음(vexplor_rps는 COMPANY_16 단독). -- ============================================================ BEGIN; -- ------------------------------------------------------------ -- 1) pms_wbs_task (WBS 트리 — 설계/구매/제작/자체검사/최종검사/출하/셋업 단계별) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_task CASCADE; CREATE TABLE pms_wbs_task ( objid varchar, contract_objid varchar, parent_objid varchar, task_name varchar(1000) DEFAULT NULL, task_seq varchar, design_user_id varchar, design_plan_start varchar, design_plan_end varchar, design_act_start varchar, design_act_end varchar, purchase_user_id varchar, purchase_plan_start varchar, purchase_plan_end varchar, purchase_act_start varchar, purchase_act_end varchar, produce_user_id varchar, produce_plan_start varchar, produce_plan_end varchar, produce_act_start varchar, produce_act_end varchar, selfins_user_id varchar, selfins_plan_start varchar, selfins_plan_end varchar, selfins_act_start varchar, selfins_act_end varchar, finalins_user_id varchar, finalins_plan_start varchar, finalins_plan_end varchar, finalins_act_start varchar, finalins_act_end varchar, ship_user_id varchar, ship_plan_start varchar, ship_plan_end varchar, ship_act_start varchar, ship_act_end varchar, setup_user_id varchar, setup_plan_start varchar, setup_plan_end varchar, setup_act_start varchar, setup_act_end varchar, writer varchar, design_rate varchar DEFAULT '0', purchase_rate varchar DEFAULT '0', produce_rate varchar DEFAULT '0', selfins_rate varchar DEFAULT '0', finalins_rate varchar DEFAULT '0', ship_rate varchar DEFAULT '0', setup_rate varchar DEFAULT '0', unit_no varchar, reg_date timestamp, update_date timestamp, modifier varchar, task_level varchar(10) DEFAULT '', wbs_type varchar(20) DEFAULT '', remark text DEFAULT '', upper_task_objid varchar(255) DEFAULT '', template_task_objid varchar(255) DEFAULT '', progress varchar(10) DEFAULT '' ); CREATE UNIQUE INDEX wbs_task_pk ON pms_wbs_task USING btree (objid); CREATE INDEX pms_wbs_task_contract_objid_idx ON pms_wbs_task USING btree (contract_objid); COMMENT ON COLUMN pms_wbs_task.objid IS '키'; COMMENT ON COLUMN pms_wbs_task.contract_objid IS '계약키값'; COMMENT ON COLUMN pms_wbs_task.parent_objid IS '부모키'; COMMENT ON COLUMN pms_wbs_task.task_name IS 'task이름'; COMMENT ON COLUMN pms_wbs_task.task_seq IS 'task순번'; COMMENT ON COLUMN pms_wbs_task.design_user_id IS '설계담당'; COMMENT ON COLUMN pms_wbs_task.design_plan_start IS '설계계획시작일'; COMMENT ON COLUMN pms_wbs_task.design_plan_end IS '설계계획종료일'; COMMENT ON COLUMN pms_wbs_task.design_act_start IS '설계시작일'; COMMENT ON COLUMN pms_wbs_task.design_act_end IS '설계종료일'; COMMENT ON COLUMN pms_wbs_task.purchase_user_id IS '구매담당'; COMMENT ON COLUMN pms_wbs_task.purchase_plan_start IS '구매계획시작일'; COMMENT ON COLUMN pms_wbs_task.purchase_plan_end IS '구매계획종료일'; COMMENT ON COLUMN pms_wbs_task.purchase_act_start IS '구매시작일'; COMMENT ON COLUMN pms_wbs_task.purchase_act_end IS '구매종료일'; COMMENT ON COLUMN pms_wbs_task.produce_user_id IS '제작담당자'; COMMENT ON COLUMN pms_wbs_task.produce_plan_start IS '제작계획시작일'; COMMENT ON COLUMN pms_wbs_task.produce_plan_end IS '제작계획종료일'; COMMENT ON COLUMN pms_wbs_task.produce_act_start IS '제작시작일'; COMMENT ON COLUMN pms_wbs_task.produce_act_end IS '제작종료일'; COMMENT ON COLUMN pms_wbs_task.writer IS '작성자'; COMMENT ON COLUMN pms_wbs_task.design_rate IS '설계진척율'; COMMENT ON COLUMN pms_wbs_task.reg_date IS '등록일'; COMMENT ON COLUMN pms_wbs_task.update_date IS '수정일'; COMMENT ON COLUMN pms_wbs_task.modifier IS '수정자'; -- ------------------------------------------------------------ -- 2) pms_wbs_task_info (일반 task 리스트 — 실사용 518건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_task_info CASCADE; CREATE TABLE pms_wbs_task_info ( objid varchar(64) NOT NULL, target_objid varchar(64), task_step varchar(32), task_name varchar(256), task_seq varchar(32), dept_code varchar(32), manager_user_id varchar(32), task_perform_day varchar(32), plan_start_date varchar(64), plan_end_date varchar(64), result_start_date varchar(64), result_end_date varchar(64), expected_point varchar(32), standard_doc_name varchar(512), task_status varchar(32), pm_user_id varchar(32), pm_confirm_status varchar(32), pm_confirm_date varchar(64), remark varchar(256), writer varchar(32), reg_date timestamp, update_date timestamp, CONSTRAINT pms_wbs_task_info_pkey PRIMARY KEY (objid) ); COMMENT ON COLUMN pms_wbs_task_info.objid IS '유일키'; COMMENT ON COLUMN pms_wbs_task_info.target_objid IS '프로젝트 유일키'; COMMENT ON COLUMN pms_wbs_task_info.task_step IS '테스크 단계(Phase)'; COMMENT ON COLUMN pms_wbs_task_info.task_name IS '테스크 명'; COMMENT ON COLUMN pms_wbs_task_info.task_seq IS '테스트 순서'; COMMENT ON COLUMN pms_wbs_task_info.dept_code IS '부서코드'; COMMENT ON COLUMN pms_wbs_task_info.manager_user_id IS '담당자코드'; COMMENT ON COLUMN pms_wbs_task_info.task_perform_day IS '수행소요일'; COMMENT ON COLUMN pms_wbs_task_info.plan_start_date IS '계획 시작일'; COMMENT ON COLUMN pms_wbs_task_info.plan_end_date IS '계획 종료일'; COMMENT ON COLUMN pms_wbs_task_info.result_start_date IS '실적 시작일'; COMMENT ON COLUMN pms_wbs_task_info.result_end_date IS '실적 종료일'; COMMENT ON COLUMN pms_wbs_task_info.expected_point IS '예상시점'; COMMENT ON COLUMN pms_wbs_task_info.standard_doc_name IS '표준문서명'; COMMENT ON COLUMN pms_wbs_task_info.task_status IS '테스트 상태'; COMMENT ON COLUMN pms_wbs_task_info.pm_user_id IS 'PM 아이디'; COMMENT ON COLUMN pms_wbs_task_info.pm_confirm_status IS 'PM 승인 상태'; COMMENT ON COLUMN pms_wbs_task_info.pm_confirm_date IS 'PM 승인 일자'; COMMENT ON COLUMN pms_wbs_task_info.remark IS '비고'; COMMENT ON COLUMN pms_wbs_task_info.writer IS '작성자'; COMMENT ON COLUMN pms_wbs_task_info.reg_date IS '등록일'; COMMENT ON COLUMN pms_wbs_task_info.update_date IS '수정일'; -- ------------------------------------------------------------ -- 3) pms_wbs_task_confirm (작업 확정 — objid numeric, 0건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_task_confirm CASCADE; CREATE TABLE pms_wbs_task_confirm ( objid numeric, target_objid numeric, confirm_type varchar(32), contents varchar(4000), result varchar(32), regdate timestamp, writer varchar(32) ); -- ------------------------------------------------------------ -- 4) pms_wbs_task_standard (트리 표준 — 5건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_task_standard CASCADE; CREATE TABLE pms_wbs_task_standard ( objid varchar NOT NULL, parent_objid varchar, task_name varchar, task_seq varchar, user_id varchar, writer varchar, reg_date timestamp, unit_no varchar, upper_task_objid varchar, task_level varchar, CONSTRAINT pms_wbs_task_standard_pkey PRIMARY KEY (objid) ); -- ------------------------------------------------------------ -- 5) pms_wbs_task_standard2 (일반 task 표준 — 실사용 74건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_task_standard2 CASCADE; CREATE TABLE pms_wbs_task_standard2 ( task_step varchar(32), task_name varchar(256), task_seq varchar(32), dept_code varchar(32), manager_user_id varchar(32), task_perform_day varchar(32), plan_start_date varchar(64), plan_end_date varchar(64), result_start_date varchar(64), result_end_date varchar(64), expected_point varchar(64), standard_doc_name varchar(512), task_status varchar(32), pm_user_id varchar(32), pm_confirm_status varchar(32), pm_confirm_date varchar(64), remark varchar(256), writer varchar(32), reg_date timestamp, update_date timestamp ); COMMENT ON COLUMN pms_wbs_task_standard2.task_step IS '테스크 단계(Phase)'; COMMENT ON COLUMN pms_wbs_task_standard2.task_name IS '테스크 명'; COMMENT ON COLUMN pms_wbs_task_standard2.dept_code IS '부서코드'; COMMENT ON COLUMN pms_wbs_task_standard2.manager_user_id IS '담당자코드'; COMMENT ON COLUMN pms_wbs_task_standard2.task_perform_day IS '수행소요일'; COMMENT ON COLUMN pms_wbs_task_standard2.plan_start_date IS '계획 시작일'; COMMENT ON COLUMN pms_wbs_task_standard2.plan_end_date IS '계획 종료일'; COMMENT ON COLUMN pms_wbs_task_standard2.result_start_date IS '실적 시작일'; COMMENT ON COLUMN pms_wbs_task_standard2.result_end_date IS '실적 종료일'; COMMENT ON COLUMN pms_wbs_task_standard2.expected_point IS '예상시점'; COMMENT ON COLUMN pms_wbs_task_standard2.standard_doc_name IS '표준문서명'; COMMENT ON COLUMN pms_wbs_task_standard2.task_status IS '테스트 상태'; COMMENT ON COLUMN pms_wbs_task_standard2.pm_user_id IS 'PM 아이디'; COMMENT ON COLUMN pms_wbs_task_standard2.pm_confirm_status IS 'PM 승인 상태'; COMMENT ON COLUMN pms_wbs_task_standard2.pm_confirm_date IS 'PM 승인 일자'; COMMENT ON COLUMN pms_wbs_task_standard2.remark IS '비고'; COMMENT ON COLUMN pms_wbs_task_standard2.writer IS '작성자'; COMMENT ON COLUMN pms_wbs_task_standard2.reg_date IS '등록일'; COMMENT ON COLUMN pms_wbs_task_standard2.update_date IS '수정일'; -- ------------------------------------------------------------ -- 6) pms_wbs_template (제품별 WBS 템플릿 — 1건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS pms_wbs_template CASCADE; CREATE TABLE pms_wbs_template ( objid varchar NOT NULL, product_objid varchar, title varchar, writer varchar, reg_date timestamp, customer_product varchar, CONSTRAINT pms_wbs_template_pkey PRIMARY KEY (objid) ); -- ------------------------------------------------------------ -- 7) setup_wbs_task (셋업 작업 진척 — 운영 2,576건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS setup_wbs_task CASCADE; CREATE TABLE setup_wbs_task ( objid varchar, contract_objid varchar, parent_objid varchar, task_category varchar, task_name varchar(1000) DEFAULT NULL, standard_objid varchar, setup_plan_start varchar, setup_plan_end varchar, setup_act_start varchar, setup_act_end varchar, setup_delaye_day varchar, writer varchar, employees_in varchar, employees_out varchar, employees_total varchar, setup_rate varchar DEFAULT '0', unit_no varchar, task_seq varchar, proj_step varchar, regdate timestamp ); CREATE UNIQUE INDEX setup_wbs_task_pk ON setup_wbs_task USING btree (objid); CREATE INDEX setup_wbs_task_contract_objid_idx ON setup_wbs_task USING btree (contract_objid); COMMENT ON COLUMN setup_wbs_task.objid IS 'objid'; COMMENT ON COLUMN setup_wbs_task.contract_objid IS 'project_objid'; COMMENT ON COLUMN setup_wbs_task.parent_objid IS 'task부모키'; COMMENT ON COLUMN setup_wbs_task.task_category IS 'TASK구분'; COMMENT ON COLUMN setup_wbs_task.task_name IS 'TASK명'; COMMENT ON COLUMN setup_wbs_task.setup_plan_start IS '계획시작일'; COMMENT ON COLUMN setup_wbs_task.setup_plan_end IS '계획완료일'; COMMENT ON COLUMN setup_wbs_task.setup_act_start IS '실적시작일'; COMMENT ON COLUMN setup_wbs_task.setup_act_end IS '실적완료일'; COMMENT ON COLUMN setup_wbs_task.writer IS '작성자'; COMMENT ON COLUMN setup_wbs_task.employees_in IS '자사투입인원'; COMMENT ON COLUMN setup_wbs_task.employees_out IS '외주투입인원'; COMMENT ON COLUMN setup_wbs_task.regdate IS '등록일'; -- ------------------------------------------------------------ -- 8) setup_wbs_task_standard (셋업 표준 — 46건) -- ------------------------------------------------------------ DROP TABLE IF EXISTS setup_wbs_task_standard CASCADE; CREATE TABLE setup_wbs_task_standard ( objid varchar, contract_objid varchar, parent_objid varchar, task_category varchar, task_name varchar(1000) DEFAULT NULL, setup_user_id varchar, setup_plan_start varchar, setup_plan_end varchar, setup_act_start varchar, setup_act_end varchar, setup_delaye_day varchar, writer varchar, employees_in varchar, employees_out varchar, employees_total varchar, setup_rate varchar DEFAULT '0', unit_no varchar, task_seq varchar, proj_step varchar ); -- 운영 인덱스 이름에 공백 포함됨(타이포로 추정): "setup_wbs_task_standard _objid_key" CREATE UNIQUE INDEX "setup_wbs_task_standard _objid_key" ON setup_wbs_task_standard USING btree (objid); COMMIT;