CREATE TABLE SGC.TCS_ERP_SITE ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, SITE_NM VARCHAR(100), SITE_ABB_NM VARCHAR(100), MNG_HQT_CD VARCHAR(20), MNG_HQT_NM VARCHAR(100), SITE_PRGS_GB_CD VARCHAR(20), SITE_MNG_GB_CD VARCHAR(20), DCNT NUMBER, HHCNT NUMBER, CONST_PRD_FROM_DT VARCHAR(8), CONST_PRD_TO_DT VARCHAR(8), COMPL_DT VARCHAR(8), SPCMT_CN VARCHAR(4000), OFC_TEL_NO VARCHAR(100), OFC_FAX_NO VARCHAR(100), BZPJ_APRO_DT VARCHAR(8), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_ERP_SITE IS '하자) ERP 현장'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.BZPJ_APRO_DT IS '사업_승인_일자'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.COMPL_DT IS '준공_일자'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.CONST_PRD_FROM_DT IS '공사 기간 FROM'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.CONST_PRD_TO_DT IS '공사 기간 TO'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.DCNT IS '동수'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.HHCNT IS '세대수'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.MNG_HQT_CD IS '관리본부 코드'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.MNG_HQT_NM IS '관리본부 명'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.OFC_FAX_NO IS '사무실_팩스번호'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.OFC_TEL_NO IS '사무실_전화번호'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SITE_ABB_NM IS '현장_약어_명'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SITE_MNG_GB_CD IS '현장관리구분코드[CS019]'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SITE_NM IS '현장_명'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SITE_PRGS_GB_CD IS '현장진행구분코드[CS015]'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.SPCMT_CN IS '특기사항_내용'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_ERP_SITE.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_ERP_SITE ON SGC.TCS_ERP_SITE ( CMPN_CD ASC, SITE_CD ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_ERP_SITE ADD CONSTRAINT PK_TCS_ERP_SITE PRIMARY KEY ( CMPN_CD, SITE_CD ); CREATE TABLE SGC.TCS_SITE ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, SITE_NM VARCHAR(100), SITE_ABB_NM VARCHAR(100), SITE_PRGS_GB_CD VARCHAR(20), SITE_MNG_GB_CD VARCHAR(20), PROD_NO NUMBER, DCNT NUMBER, HHCNT NUMBER, CONST_PRD_FROM_DT VARCHAR(8), CONST_PRD_TO_DT VARCHAR(8), COMPL_DT VARCHAR(8), SPCMT_CN VARCHAR(4000), OFC_TEL_NO VARCHAR(100), OFC_FAX_NO VARCHAR(100), BZPJ_APRO_DT VARCHAR(8), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_SITE IS '하자) 하자대상현장'; COMMENT ON COLUMN SGC.TCS_SITE.BZPJ_APRO_DT IS '사업_승인_일자'; COMMENT ON COLUMN SGC.TCS_SITE.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_SITE.COMPL_DT IS '준공_일자'; COMMENT ON COLUMN SGC.TCS_SITE.CONST_PRD_FROM_DT IS '공사 기간 FROM'; COMMENT ON COLUMN SGC.TCS_SITE.CONST_PRD_TO_DT IS '공사 기간 TO'; COMMENT ON COLUMN SGC.TCS_SITE.DCNT IS '동수'; COMMENT ON COLUMN SGC.TCS_SITE.HHCNT IS '세대수'; COMMENT ON COLUMN SGC.TCS_SITE.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_SITE.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_SITE.OFC_FAX_NO IS '사무실_팩스번호'; COMMENT ON COLUMN SGC.TCS_SITE.OFC_TEL_NO IS '사무실_전화번호'; COMMENT ON COLUMN SGC.TCS_SITE.PROD_NO IS '상품분류 상품번호'; COMMENT ON COLUMN SGC.TCS_SITE.SITE_ABB_NM IS '현장_약어_명'; COMMENT ON COLUMN SGC.TCS_SITE.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_SITE.SITE_MNG_GB_CD IS '현장관리구분코드[CS019]'; COMMENT ON COLUMN SGC.TCS_SITE.SITE_NM IS '현장_명'; COMMENT ON COLUMN SGC.TCS_SITE.SITE_PRGS_GB_CD IS '현장진행구분코드[CS015]'; COMMENT ON COLUMN SGC.TCS_SITE.SPCMT_CN IS '특기사항_내용'; COMMENT ON COLUMN SGC.TCS_SITE.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_SITE.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_SITE ON SGC.TCS_SITE ( CMPN_CD ASC, SITE_CD ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_SITE ADD CONSTRAINT PK_TCS_SITE PRIMARY KEY ( CMPN_CD, SITE_CD ); CREATE TABLE SGC.TCS_SITE_TRADE_CHRGR ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, COVEND_ID NUMBER NOT NULL, CHRGR_TYPE_CD VARCHAR(20) NOT NULL, CHRGR_NM VARCHAR(100), CHRGR_CRRY_TEL_NO VARCHAR(100), CHRGR_SMS_PERM_YN VARCHAR(1) DEFAULT 'N', CHRGR_EMAIL VARCHAR(400), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_SITE_TRADE_CHRGR IS '하자)현장협력업체 담당자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CHRGR_CRRY_TEL_NO IS '담당자_휴대_전화번호'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CHRGR_EMAIL IS '담당자_이메일'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CHRGR_NM IS '담당자_명'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CHRGR_SMS_PERM_YN IS '담당자_SMS_허용_여부'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CHRGR_TYPE_CD IS '담당자유형코드[CS006]'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.COVEND_ID IS '협력업체 ID'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_CHRGR.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_SITE_TRADE_CHRGR ON SGC.TCS_SITE_TRADE_CHRGR ( CMPN_CD ASC, SITE_CD ASC, COVEND_ID ASC, CHRGR_TYPE_CD ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; CREATE INDEX SGC.IF1_TCS_SITE_TRADE_CHRGR ON SGC.TCS_SITE_TRADE_CHRGR ( CMPN_CD ASC, SITE_CD ASC, COVEND_ID ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_SITE_TRADE_CHRGR ADD CONSTRAINT PK_TCS_SITE_TRADE_CHRGR PRIMARY KEY ( CMPN_CD, SITE_CD, COVEND_ID, CHRGR_TYPE_CD ); ALTER TABLE SGC.TCS_SITE_TRADE_CHRGR ADD CONSTRAINT R_812 FOREIGN KEY ( CMPN_CD, SITE_CD, COVEND_ID ) REFERENCES SGC.TCS_SITE_TRADE ( CMPN_CD, SITE_CD, COVEND_ID ); CREATE TABLE SGC.TCS_SITE_TRADE_DEFGR ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, COVEND_ID NUMBER NOT NULL, DEFGR_SEQ NUMBER(10) NOT NULL, CNTRT_NM VARCHAR(300), CNTRT_AMT NUMBER DEFAULT 0 NOT NULL, JOBTYP_NM VARCHAR(300), GUAR_TYPE_CD VARCHAR(20), GUAR_AMT NUMBER DEFAULT 0 NOT NULL, GUAR_STRT_DT VARCHAR(8), GUAR_END_DT VARCHAR(8), GUAR_INTU_NM VARCHAR(100), GUAR_INTU_CD VARCHAR(20), SCRT_NO VARCHAR(50), DEFT_CNTRT_YN VARCHAR(1), CNTRT_NO VARCHAR(20), CNTRT_DGR NUMBER, IF_GUAR_SEQ NUMBER, CRT_SYS_CD VARCHAR(20), GUAR_CHG_GB_CD VARCHAR(20), BDMT_PO_REQ_DT VARCHAR(8), GRNTL_AFL_ID VARCHAR(30), ELEC_GUAR_YN VARCHAR(1), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_SITE_TRADE_DEFGR IS '하자)현장협력업체 하자보증'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.BDMT_PO_REQ_DT IS '자재_발주_요청_일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CNTRT_AMT IS '계약_금액'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CNTRT_DGR IS '계약_차수'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CNTRT_NM IS '계약_명'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CNTRT_NO IS '계약번호'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.COVEND_ID IS '협력업체 ID'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.CRT_SYS_CD IS '생성시스템코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.DEFGR_SEQ IS '하자보증_순번'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.DEFT_CNTRT_YN IS '하자_계약_여부'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.ELEC_GUAR_YN IS '전자_보증_여부'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GRNTL_AFL_ID IS '보증서_첨부파일ID'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_AMT IS '보증_금액'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_CHG_GB_CD IS '보증변경구분코드[CO018]'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_END_DT IS '보증_종료_일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_INTU_CD IS '보증기관코드[CO008]'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_INTU_NM IS '보증_기관_명'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_STRT_DT IS '보증_시작_일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.GUAR_TYPE_CD IS '보증유형코드[CS007]'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.IF_GUAR_SEQ IS '인터페이스_보증_순번'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.JOBTYP_NM IS '공종_명'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.SCRT_NO IS '증권번호'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE_DEFGR.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_SITE_TRADE_DEFGR ON SGC.TCS_SITE_TRADE_DEFGR ( CMPN_CD ASC, SITE_CD ASC, COVEND_ID ASC, DEFGR_SEQ ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; CREATE INDEX SGC.IF1_TCS_SITE_TRADE_DEFGR ON SGC.TCS_SITE_TRADE_DEFGR ( CMPN_CD ASC, SITE_CD ASC, COVEND_ID ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_SITE_TRADE_DEFGR ADD CONSTRAINT PK_TCS_SITE_TRADE_DEFGR PRIMARY KEY ( CMPN_CD, SITE_CD, COVEND_ID, DEFGR_SEQ ); ALTER TABLE SGC.TCS_SITE_TRADE_DEFGR ADD CONSTRAINT R_813 FOREIGN KEY ( CMPN_CD, SITE_CD, COVEND_ID ) REFERENCES SGC.TCS_SITE_TRADE ( CMPN_CD, SITE_CD, COVEND_ID ); CREATE TABLE SGC.TCS_SITE_DHS ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, DONG VARCHAR(100) NOT NULL, HSNO VARCHAR(100) NOT NULL, CNTPR_OCPH_NM VARCHAR(100), CP_NO VARCHAR(100), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_SITE_DHS IS '하자) 현장 동호수'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.CNTPR_OCPH_NM IS '계약 입주 자'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.CP_NO IS '핸드폰 번호'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.DONG IS '동'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.HSNO IS '호수'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_SITE_DHS.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_SITE_DHS ON SGC.TCS_SITE_DHS ( CMPN_CD ASC, SITE_CD ASC, DONG ASC, HSNO ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_SITE_DHS ADD CONSTRAINT PK_TCS_SITE_DHS PRIMARY KEY ( CMPN_CD, SITE_CD, DONG, HSNO ); CREATE TABLE SGC.TCS_SITE_TRADE ( CMPN_CD VARCHAR(10) NOT NULL, SITE_CD VARCHAR(20) NOT NULL, COVEND_ID NUMBER NOT NULL, JOB_GB_CD VARCHAR(20), RMK_CN VARCHAR(4000), CRT_SYS_CD VARCHAR(20), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_SITE_TRADE IS '하자)현장협력업체'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.COVEND_ID IS '협력업체 ID'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.CRT_SYS_CD IS '생성시스템코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.JOB_GB_CD IS '협력업체유형코드[CS004]'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.RMK_CN IS '비고_내용'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_SITE_TRADE.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_SITE_TRADE ON SGC.TCS_SITE_TRADE ( CMPN_CD ASC, SITE_CD ASC, COVEND_ID ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_SITE_TRADE ADD CONSTRAINT PK_TCS_SITE_TRADE PRIMARY KEY ( CMPN_CD, SITE_CD, COVEND_ID ); CREATE TABLE SGC.TCS_DEFT ( CMPN_CD VARCHAR(20) NOT NULL, DEFT_NO VARCHAR(20) NOT NULL, SITE_CD VARCHAR(20), DONG VARCHAR(100), HSNO VARCHAR(100), OFFR_DT DATE, APLNT_NM VARCHAR(100), APLNT_REL VARCHAR(50), CRRY_TEL_NO VARCHAR(100), TEL_NO VARCHAR(100), UPPER_LOC_NO NUMBER, LOC_NO NUMBER, DEFT_LJOBTYP_NO NUMBER, DEFT_MJOBTYP_NO NUMBER, DEFT_JOBTYP_NO NUMBER, DEFT_TYPE_NO NUMBER, DEFT_CAUS_NO NUMBER, EMG_GB_CD VARCHAR(20), DEFT_CN VARCHAR(4000), DEFT_IMG_AFL_ID VARCHAR(30), REQ_CN VARCHAR(4000), CONS_MNBD_CD VARCHAR(20), CONS_COVEND_ID NUMBER, RCPT_MTD_CD VARCHAR(20), RCPT_DT DATE, RCVPR_NM VARCHAR(100), RCVPR_EMPNO VARCHAR(20), WORK_WKI_NO VARCHAR(20), PRCG_MNBD_CD VARCHAR(20), PRCG_COVEND_ID NUMBER, ASMNG_CHRGR_EMPNO VARCHAR(20), VENDR_LBPR_NM VARCHAR(100), VIS_ELPS_SEQ NUMBER, WORK_ELPS_SEQ NUMBER, PRCG_ELPS_SEQ NUMBER, PRCG_DT DATE, PGPR_NM VARCHAR(100), PRCG_CN VARCHAR(500), PRCG_AFL_ID VARCHAR(30), DURA_DYS NUMBER, RPDT_SCOR NUMBER, CPLT_ELPS_SEQ NUMBER, CPLT_DT VARCHAR(8), CPLPR_NM VARCHAR(100), MBIL_PRCG_CN VARCHAR(500), MBIL_CN_CONF_YN VARCHAR(1), DEFT_PRCG_STUS_CD VARCHAR(20), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_DEFT IS '하자) 하자접수처리'; COMMENT ON COLUMN SGC.TCS_DEFT.APLNT_NM IS '신청자_명'; COMMENT ON COLUMN SGC.TCS_DEFT.APLNT_REL IS '신청자 관계'; COMMENT ON COLUMN SGC.TCS_DEFT.ASMNG_CHRGR_EMPNO IS '사후관리_담당자_사번'; COMMENT ON COLUMN SGC.TCS_DEFT.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_DEFT.CONS_COVEND_ID IS '시공_협력업체ID'; COMMENT ON COLUMN SGC.TCS_DEFT.CONS_MNBD_CD IS '시공주체코드[CS011]'; COMMENT ON COLUMN SGC.TCS_DEFT.CPLPR_NM IS '완료자_명'; COMMENT ON COLUMN SGC.TCS_DEFT.CPLT_DT IS '완료_일자'; COMMENT ON COLUMN SGC.TCS_DEFT.CPLT_ELPS_SEQ IS '완료 경과 순번'; COMMENT ON COLUMN SGC.TCS_DEFT.CRRY_TEL_NO IS '휴대_전화번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_CAUS_NO IS '하자원인번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_CN IS '하자_내용'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_IMG_AFL_ID IS '하자사진_첨부파일ID'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_JOBTYP_NO IS '하자공종번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_LJOBTYP_NO IS '하자대공종번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_MJOBTYP_NO IS '하자중공종번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_NO IS '하자번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_PRCG_STUS_CD IS '하자처리상태코드[CS009]'; COMMENT ON COLUMN SGC.TCS_DEFT.DEFT_TYPE_NO IS '하자유형번호'; COMMENT ON COLUMN SGC.TCS_DEFT.DONG IS '동'; COMMENT ON COLUMN SGC.TCS_DEFT.DURA_DYS IS '소요_일수'; COMMENT ON COLUMN SGC.TCS_DEFT.EMG_GB_CD IS '긴급 구분코드[CS020]'; COMMENT ON COLUMN SGC.TCS_DEFT.HSNO IS '호수'; COMMENT ON COLUMN SGC.TCS_DEFT.LOC_NO IS '위치번호'; COMMENT ON COLUMN SGC.TCS_DEFT.MBIL_CN_CONF_YN IS '모바일_내용_확인_여부'; COMMENT ON COLUMN SGC.TCS_DEFT.MBIL_PRCG_CN IS '모바일_처리_내용'; COMMENT ON COLUMN SGC.TCS_DEFT.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_DEFT.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_DEFT.OFFR_DT IS '신청 일자'; COMMENT ON COLUMN SGC.TCS_DEFT.PGPR_NM IS '처리자_명'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_AFL_ID IS '처리_첨부파일ID'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_CN IS '처리_내용'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_COVEND_ID IS '처리_협력업체ID'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_DT IS '처리_일자'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_ELPS_SEQ IS '처리 경과 순번'; COMMENT ON COLUMN SGC.TCS_DEFT.PRCG_MNBD_CD IS '처리주체코드[CS011]'; COMMENT ON COLUMN SGC.TCS_DEFT.RCPT_DT IS '접수_일자'; COMMENT ON COLUMN SGC.TCS_DEFT.RCPT_MTD_CD IS '접수방법코드[CS010]'; COMMENT ON COLUMN SGC.TCS_DEFT.RCVPR_EMPNO IS '접수자 사원번호'; COMMENT ON COLUMN SGC.TCS_DEFT.RCVPR_NM IS '접수자_명'; COMMENT ON COLUMN SGC.TCS_DEFT.REQ_CN IS '요청_내용'; COMMENT ON COLUMN SGC.TCS_DEFT.RPDT_SCOR IS '신속도_점수'; COMMENT ON COLUMN SGC.TCS_DEFT.SITE_CD IS '현장코드'; COMMENT ON COLUMN SGC.TCS_DEFT.TEL_NO IS '전화번호'; COMMENT ON COLUMN SGC.TCS_DEFT.UPPER_LOC_NO IS '상위_위치번호'; COMMENT ON COLUMN SGC.TCS_DEFT.VENDR_LBPR_NM IS '업체_작업자_명'; COMMENT ON COLUMN SGC.TCS_DEFT.VIS_ELPS_SEQ IS '방문 경과 순번'; COMMENT ON COLUMN SGC.TCS_DEFT.WORK_ELPS_SEQ IS '작업 경과 순번'; COMMENT ON COLUMN SGC.TCS_DEFT.WORK_WKI_NO IS '작업_지시서_번호'; COMMENT ON COLUMN SGC.TCS_DEFT.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_DEFT.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_DEFT ON SGC.TCS_DEFT ( CMPN_CD ASC, DEFT_NO ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_DEFT ADD CONSTRAINT PK_TCS_DEFT PRIMARY KEY ( CMPN_CD, DEFT_NO ); CREATE TABLE SGC.TCS_DEFT_CUSTSIGN ( CMPN_CD VARCHAR(20) NOT NULL, DEFT_NO VARCHAR(20) NOT NULL, SIGN_AFL_ID VARCHAR(30), SIGN_DT DATE, FILE_KND_NM VARCHAR(200), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_DEFT_CUSTSIGN IS '하자) 고객서명'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.DEFT_NO IS '하자번호'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.FILE_KND_NM IS '파일_종류_명'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.SIGN_AFL_ID IS '서명_첨부파일ID'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.SIGN_DT IS '서명_일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_DEFT_CUSTSIGN ON SGC.TCS_DEFT_CUSTSIGN ( CMPN_CD ASC, DEFT_NO ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_DEFT_CUSTSIGN ADD CONSTRAINT PK_TCS_DEFT_CUSTSIGN PRIMARY KEY ( CMPN_CD, DEFT_NO ); ALTER TABLE SGC.TCS_DEFT_CUSTSIGN ADD CONSTRAINT R_788 FOREIGN KEY ( CMPN_CD, DEFT_NO ) REFERENCES SGC.TCS_DEFT ( CMPN_CD, DEFT_NO ); CREATE TABLE SGC.TCS_DEFT_ELPS ( CMPN_CD VARCHAR(20) NOT NULL, DEFT_NO VARCHAR(20) NOT NULL, PRCG_ELPS_SEQ NUMBER NOT NULL, PRCG_TYPE_CD VARCHAR(20), DEFT_PRCG_GB_CD VARCHAR(20), PRCG_PR_STRT_DT VARCHAR(8), PRCG_PR_STRT_TM VARCHAR(4), PRCG_PR_END_TM VARCHAR(4), PRCG_PR_END_DT VARCHAR(8), RMK_CN VARCHAR(4000), MAIL_CN CLOB, PGPR_NM VARCHAR(100), PGPR_GB_CD VARCHAR(20), PRCG_AMT NUMBER DEFAULT 0, WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING LOB (MAIL_CN) STORE AS _SGC_LOB325300 ( TABLESPACE TS_SGCDATA ENABLE STORAGE IN ROW ) NOPARALLEL; COMMENT ON TABLE SGC.TCS_DEFT_ELPS IS '하자) 하자처리경과'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.DEFT_NO IS '하자번호'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.DEFT_PRCG_GB_CD IS '하자처리구분코드[CS013]'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.MAIL_CN IS '메일 내용'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PGPR_GB_CD IS '처리자 구분코드[CS016]'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PGPR_NM IS '처리자 명'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_AMT IS '처리 비용'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_ELPS_SEQ IS '처리_경과_순번'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_PR_END_DT IS '처리_예정_종료_일자'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_PR_END_TM IS '처리_예정_종료_시간TO'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_PR_STRT_DT IS '처리_예정_시작_일자'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_PR_STRT_TM IS '처리_예정_시작_시간FROM'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.PRCG_TYPE_CD IS '처리유형코드[CS012]'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.RMK_CN IS '비고_내용'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_ELPS.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_DEFT_ELPS ON SGC.TCS_DEFT_ELPS ( CMPN_CD ASC, DEFT_NO ASC, PRCG_ELPS_SEQ ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_DEFT_ELPS ADD CONSTRAINT PK_TCS_DEFT_ELPS PRIMARY KEY ( CMPN_CD, DEFT_NO, PRCG_ELPS_SEQ ); CREATE TABLE SGC.TCS_DEFT_CUSTSIGN ( CMPN_CD VARCHAR(20) NOT NULL, DEFT_NO VARCHAR(20) NOT NULL, SIGN_AFL_ID VARCHAR(30), SIGN_DT DATE, FILE_KND_NM VARCHAR(200), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.TCS_DEFT_CUSTSIGN IS '하자) 고객서명'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.CMPN_CD IS '회사 코드'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.DEFT_NO IS '하자번호'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.FILE_KND_NM IS '파일_종류_명'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.MDFT_DT IS '수정 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.MDFT_PR IS '수정 자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.SIGN_AFL_ID IS '서명_첨부파일ID'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.SIGN_DT IS '서명_일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.TCS_DEFT_CUSTSIGN.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_TCS_DEFT_CUSTSIGN ON SGC.TCS_DEFT_CUSTSIGN ( CMPN_CD ASC, DEFT_NO ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.TCS_DEFT_CUSTSIGN ADD CONSTRAINT PK_TCS_DEFT_CUSTSIGN PRIMARY KEY ( CMPN_CD, DEFT_NO ); CREATE TABLE SGC.MO_PUSHHIST ( PUSH_HIST_SEQ NUMBER NOT NULL, USER_ID VARCHAR(100), PUSH_SEND_DTTM TIMESTAMP(6), PUSH_TYPE VARCHAR(20) NOT NULL, PUSH_TITLE VARCHAR(200) NOT NULL, PUSH_CONTENTS VARCHAR(4000) NOT NULL, PUSH_LINK VARCHAR(1000), PUSH_STATUS VARCHAR(20) NOT NULL, PUSH_ERRMSG VARCHAR(4000), PUSH_SEQ NUMBER NOT NULL, WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL; COMMENT ON TABLE SGC.MO_PUSHHIST IS '모바일) PUSH 알림이력'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_CONTENTS IS '알림내용'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_ERRMSG IS '알림에러내용'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_HIST_SEQ IS '알림이력번호'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_LINK IS '알림링크'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_SEND_DTTM IS '알림 일시'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_SEQ IS '일련 번호'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_STATUS IS '알림상태'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_TITLE IS '알림제목'; COMMENT ON COLUMN SGC.MO_PUSHHIST.PUSH_TYPE IS '알림구분'; COMMENT ON COLUMN SGC.MO_PUSHHIST.USER_ID IS '아이디'; COMMENT ON COLUMN SGC.MO_PUSHHIST.WRTE_DT IS '작성 일자'; COMMENT ON COLUMN SGC.MO_PUSHHIST.WRTE_PR IS '작성 자'; CREATE UNIQUE INDEX SGC.PK_PUSHHIST ON SGC.MO_PUSHHIST ( PUSH_HIST_SEQ ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; CREATE INDEX SGC.FK_PUSHHIST ON SGC.MO_PUSHHIST ( PUSH_SEQ ASC ) LOGGING TABLESPACE TS_SGCIDX PCTFREE 10 INITRANS 2; ALTER TABLE SGC.MO_PUSHHIST ADD CONSTRAINT PK_PUSHHIST PRIMARY KEY ( PUSH_HIST_SEQ ); ALTER TABLE SGC.MO_PUSHHIST ADD CONSTRAINT PUSHMESSAGE_01 FOREIGN KEY ( PUSH_SEQ ) REFERENCES SGC.MO_PUSH ( PUSH_SEQ ); ZEHR_ROM_LOCTN IS '회의실)위치정보'; ATCHFILE_ID IS '첨부파일ID'; LOCATION_ID IS '위치 코드'; MDFT_DT IS '수정 일자'; MDFT_PR IS '수정 자'; MR_LOCATION IS '위치 명'; WRTE_DT IS '작성 일자'; WRTE_PR IS '작성 자'; ZEHR_ROM_INFO IS '회의실)회의실정보'; ADMITTED_NUM IS '수용 인원'; BIGO IS '비고'; BUNHO IS '번호'; EQUIPMENT IS '비품'; LOCATION_ID IS '위치 코드'; MDFT_DT IS '수정 일자'; MDFT_PR IS '수정 자'; MEETINGROOM_ID IS '회의실 ID'; SORT IS '정렬순서'; TEL IS '전화 번호'; VISIBLE_YN IS '표시 여부'; WRTE_DT IS '작성 일자'; WRTE_PR IS '작성 자'; ZEHR_ROM_RESRVTN IS '회의실)회의실예약'; ATTEND_NUM IS '참석 인원'; DEPT_CD IS '부서 코드'; DEPT_NAME IS '부서 명'; END_HHMM IS '예약 종료시간'; END_YYYYMMDD IS '예약 종료일자'; ETC_DEPT_CD IS '기타 부서코드'; ETC_DEPT_NAME IS '기타 부서명'; ETC_NAME IS '기타 사원명'; ETC_REMARK IS '기타 후기'; ETC_SABUN IS '기타 사원번호'; GUBUN IS '예약 구분'; KEYSEND_YN IS '열쇠 지급여부'; KEY_YN IS '열쇠 사용여부'; MDFT_DT IS '수정 일자'; MDFT_PR IS '수정 자'; MEETINGROOM_ID IS '회의실_ID'; MEETING_CON IS '회의 내용'; MEETING_NAME IS '회의 제목'; REMARK IS '사용 후기'; RESERVATION_ID IS '예약_ID'; START_HHMM IS '예약 시작시간'; START_YYYYMMDD IS '예약 시작일자'; STATUS IS '상태'; SUBSC_CMPN_CD IS '예약자 회사코드'; SUBSC_NAME IS '예약자 명'; SUBSC_SABUN IS '예약자 사원번호'; WEEK_FR IS '요일 금'; WEEK_MU IS '요일 월'; WEEK_TH IS '요일 목'; WEEK_TU IS '요일 화'; WEEK_WE IS '요일 수'; WRTE_DT IS '작성 일자'; WRTE_PR IS '작성 자'; delete ZEHR_ROM_RESRVTN; delete ZEHR_ROM_HISTR; delete ZEHR_ROM_DISPL; select * from ZEHR_ROM_DISPL; DECLARE asMessage VARCHAR2(2000); asSuccessCd VARCHAR2(2000); BEGIN ZEHR_ROM_DIS_PRDALL ( '20210101', '20221231', 'T001', asSuccessCd, asMessage ); dbms_output.put_line(asSuccessCd); dbms_output.put_line(asMessage); END; CREATE TABLE SGC.TMP_DEFT_CUSTSIGN ( CMPN_CD VARCHAR(20) NOT NULL, DEFT_NO VARCHAR(20) NOT NULL, SIGN_AFL_ID VARCHAR(30), SIGN_DT DATE, FILE_KND_NM VARCHAR(200), WRTE_DT DATE DEFAULT sysdate, WRTE_PR VARCHAR(20), MDFT_DT DATE, MDFT_PR VARCHAR(20) ) TABLESPACE TS_SGCDATA PCTFREE 10 INITRANS 2 STORAGE ( MAXEXTENTS UNLIMITED ) LOGGING NOPARALLEL;