chpark/wace_rps
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
wace_rps/docs/migration/project/ddl-extracted/200_pms_wbs.sql
T
hjjeong 50669a66ee 프로젝트관리>제품구분_WBS관리 메뉴 신설 — wace WBS 템플릿 1:1 이식 
· 메인 그리드 5컬럼(제품구분/제목/WBS/등록자/등록일) + 통합 팝업(트리 CRUD + 엑셀 임포트 + 템플릿 다운로드)
· 운영 매핑: pms_wbs_template(헤더) + pms_wbs_task_standard(트리) — 활성 갈래 확정 (_info/_standard2 갈래는 2021년 멈춘 레거시)
· wace mergeExcelUploadWBS 1:1: 신규=헤더+트리 INSERT, 수정=트리 일괄 DELETE→INSERT (헤더 변경 없음)
· objid 채번 gen_random_uuid()::text, 엑셀 파싱 xlsx(SheetJS), 정적 템플릿 frontend/public/templates/
· DataGrid 컬럼 단위 onClick 추가 (WBS 폴더 셀 클릭용)
· DDL: 8개 테이블 162컬럼 (docs/migration/project/ddl-extracted/200_pms_wbs.sql) / GAP: docs/migration/project/02-wbs-template.md
· 프로젝트 자동 복사/진행관리 연계는 wace도 미완성 — P2 범위 외

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
2026-05-12 13:43:51 +09:00

334 lines
16 KiB
PL/PgSQL
Raw Permalink Blame History

-- ============================================================
-- 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;
Reference in New Issue View Git Blame Copy Permalink