/* 공통 T_CODE 수신 */ MERGE INTO TM_CODE USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, P_CODE, P_CODE_NM, C_CODE, C_CODE_NM, CODE_DIV, C_CODE_ENM, CODE_NO, IN_OUT_YN, ETC, USE_YN, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{P_CODE}, #{P_CODE_NM}, #{C_CODE}, #{C_CODE_NM}, #{CODE_DIV}, #{C_CODE_ENM}, #{CODE_NO}, #{IN_OUT_YN}, #{ETC}, #{USE_YN}, 'DFXAGENT_USER', SYSTIMESTAMP, #{REG_ID}, TO_TIMESTAMP(#{REG_DATE}, 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET P_CODE = #{P_CODE} , P_CODE_NM = #{P_CODE_NM} , C_CODE = #{C_CODE} , C_CODE_NM = #{C_CODE_NM} , CODE_DIV = #{CODE_DIV} , C_CODE_ENM = #{C_CODE_ENM} , CODE_NO = #{CODE_NO} , IN_OUT_YN = #{IN_OUT_YN} , ETC = #{ETC} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_COMM_METH 수신 */ MERGE INTO TM_COMM_METH USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( C_CODE_NM, C_CODE, CODE, USE_YN, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{C_CODE_NM}, #{C_CODE}, #{CODE}, #{USE_YN}, 'DFXAGENT_USER', SYSTIMESTAMP, 'DFXAGENT_USER', SYSTIMESTAMP ) WHEN MATCHED THEN UPDATE SET C_CODE_NM = #{C_CODE_NM} , CODE = #{CODE} , USE_YN = #{USE_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_DEPT 수신 */ MERGE INTO TM_DEPT USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, DEPT1, DEPT1_NM, DEPT2, DEPT2_NM, NO, NO1, DEPT1_ENG_NM, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{DEPT1}, #{DEPT1_NM}, #{DEPT2}, #{DEPT2_NM}, #{NO}, #{NO1}, #{DEPT1_ENG_NM}, 'DFXAGENT_USER', SYSTIMESTAMP, 'DFXAGENT_USER', SYSTIMESTAMP ) WHEN MATCHED THEN UPDATE SET DEPT1 = #{DEPT1} , DEPT1_NM = #{DEPT1_NM} , DEPT2 = #{DEPT2} , DEPT2_NM = #{DEPT2_NM} , NO = #{NO} , NO1 = #{NO1} , DEPT1_ENG_NM = #{DEPT1_ENG_NM} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_ERR_CAUSE 수신 */ MERGE INTO TM_ERR_CAUSE USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, P_CODE, P_CODE_NM, C_CODE, C_CODE_NM, C_CODE_ENM, CODE_DIV, CODE_NO, PREWORK_YN, AUTO_RECVR_YN, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{P_CODE}, #{P_CODE_NM}, #{C_CODE}, #{C_CODE_NM}, #{C_CODE_ENM}, #{CODE_DIV}, #{CODE_NO}, #{PREWORK_YN}, #{AUTO_RECVR_YN}, 'DFXAGENT_USER', SYSTIMESTAMP, #{REG_ID}, TO_TIMESTAMP(#{REG_DATE}, 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET P_CODE = #{P_CODE} , P_CODE_NM = #{P_CODE_NM} , C_CODE = #{C_CODE} , C_CODE_NM = #{C_CODE_NM} , C_CODE_ENM = #{C_CODE_ENM} , CODE_DIV = #{CODE_DIV} , CODE_NO = #{CODE_NO} , PREWORK_YN = #{PREWORK_YN} , AUTO_RECVR_YN = #{AUTO_RECVR_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_ERR_TYPE 수신 */ MERGE INTO TM_ERR_TYPE USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, P_CODE, P_CODE_NM, C_CODE, C_CODE_NM, C_CODE_ENM, CODE_DIV, CODE_NO, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{P_CODE}, #{P_CODE_NM}, #{C_CODE}, #{C_CODE_NM}, #{C_CODE_ENM}, #{CODE_DIV}, #{CODE_NO}, 'DFXAGENT_USER', SYSTIMESTAMP, #{REG_ID}, TO_TIMESTAMP(#{REG_DATE}, 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET P_CODE = #{P_CODE} , P_CODE_NM = #{P_CODE_NM} , C_CODE = #{C_CODE} , C_CODE_NM = #{C_CODE_NM} , C_CODE_ENM = #{C_CODE_ENM} , CODE_DIV = #{CODE_DIV} , CODE_NO = #{CODE_NO} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_KDN_DEPT 수신 */ MERGE INTO TM_KDN_DEPT USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, KDN_DEPT1, KDN_DEPT1_NM, KDN_DEPT2, KDN_DEPT2_NM, NO, NO1, DEPT1, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{KDN_DEPT1}, #{KDN_DEPT1_NM}, #{KDN_DEPT2}, #{KDN_DEPT2_NM}, #{NO}, #{NO1}, #{DEPT1}, 'DFXAGENT_USER', SYSTIMESTAMP, 'DFXAGENT_USER', SYSTIMESTAMP ) WHEN MATCHED THEN UPDATE SET KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT1_NM = #{KDN_DEPT1_NM} , KDN_DEPT2 = #{KDN_DEPT2} , KDN_DEPT2_NM = #{KDN_DEPT2_NM} , NO = #{NO} , NO1 = #{NO1} , DEPT1 = #{DEPT1} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_ORDER 수신 */ MERGE INTO TM_ORDER USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, ODR, STA_DATE, END_DATE, USE_YN, ODR_CD, CONT_YN, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{ODR}, TO_DATE(#{STA_DATE}, 'YYYYMMDD'), TO_DATE(#{END_DATE}, 'YYYYMMDD'), #{USE_YN}, #{ODR_CD}, #{CONT_YN}, NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{MOD_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{REG_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET ODR = #{ODR} , STA_DATE = #{STA_DATE} , END_DATE = #{END_DATE} , USE_YN = #{USE_YN} , ODR_CD = #{ODR_CD} , CONT_YN = #{CONT_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 T_WORK_GROUP 수신 */ MERGE INTO TM_WORK_GROUP USING DUAL ON (WORK_GP_NO = #{WORK_GP_NO}) WHEN NOT MATCHED THEN INSERT ( WORK_GP_NO, WORK_GP_NM, KDN_DEPT1, KDN_DEPT2, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, SV_SEQ, USE_YN, WORKER1_DIV, WORKER2_DIV, WORKER3_DIV, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{WORK_GP_NO}, #{WORK_GP_NM}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORKER1_ID}, #{WORKER2_ID}, #{WORKER3_ID}, #{SV_ID}, #{USE_YN}, #{WORKER1_DIV}, #{WORKER2_DIV}, #{WORKER3_DIV}, NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{MOD_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{REG_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET WORK_GP_NM = #{WORK_GP_NM} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORKER1_SEQ = #{WORKER1_ID} , WORKER2_SEQ = #{WORKER2_ID} , WORKER3_SEQ = #{WORKER3_ID} , WORKER1_DIV = #{WORKER1_DIV} , WORKER2_DIV = #{WORKER2_DIV} , WORKER3_DIV = #{WORKER3_DIV} , SV_SEQ = #{SV_ID} , USE_YN = #{USE_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 첨부파일 송신 */ SELECT ATCH_FILE_ID, USE_YN, MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT ATCH_FILE_ID, USE_YN, MOD_ID, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ATCH_FILE_ID) AS R_NUM FROM TM_ATCH_FILE WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 공통 첨부파일 송신 상태 변경 */ UPDATE TM_ATCH_FILE SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND ATCH_FILE_ID IN ( SELECT ATCH_FILE_ID FROM ( SELECT ATCH_FILE_ID, USE_YN, MOD_ID, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ATCH_FILE_ID) AS R_NUM FROM TM_ATCH_FILE WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 공통 첨부파일상세 송신 */ SELECT ATCH_FILE_ID, FILE_SN, FILE_STRE_COURS, STRE_FILE_NM, ORIGN_FILE_NM, FILE_EXTSN, FILE_CN, FILE_SIZE, FILE_TYPE, DEL_YN, IMAGE_WIDTH, IMAGE_HEIGHT, FILE_IMSI, MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT ATCH_FILE_ID, FILE_SN, FILE_STRE_COURS, STRE_FILE_NM, ORIGN_FILE_NM, FILE_EXTSN, FILE_CN, FILE_SIZE, FILE_TYPE, DEL_YN, IMAGE_WIDTH, IMAGE_HEIGHT, 'Y' AS FILE_IMSI, MOD_ID, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ATCH_FILE_ID, FILE_SN) AS R_NUM FROM TM_ATCH_FILE_DETAIL WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 공통 첨부파일상세 송신 상태 변경 */ UPDATE TM_ATCH_FILE_DETAIL SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND (ATCH_FILE_ID, FILE_SN) IN ( SELECT ATCH_FILE_ID, FILE_SN FROM ( SELECT ATCH_FILE_ID, FILE_SN, FILE_STRE_COURS, STRE_FILE_NM, ORIGN_FILE_NM, FILE_EXTSN, FILE_CN, FILE_SIZE, FILE_TYPE, DEL_YN, IMAGE_WIDTH, IMAGE_HEIGHT, FILE_IMSI, MOD_ID, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ATCH_FILE_ID, FILE_SN) AS R_NUM FROM TM_ATCH_FILE_DETAIL WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 공통 내선업체 수신 */ MERGE INTO TM_INLN_CO USING DUAL ON (COCMP_BUPE_ID = #{COCMP_BUPE_ID}) WHEN NOT MATCHED THEN INSERT ( COCMP_BUPE_ID, TRNA, BIZNO, CORP_REGST_NO, RPSNT_NM, TYPBU_NM, TOIN_NM, ZIPCD, SIDO_ADDR, BUPE_ADDR, USE_YN , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{COCMP_BUPE_ID}, #{TRNA}, #{BIZNO}, #{CORP_REGST_NO}, #{RPSNT_NM}, #{TYPBU_NM}, #{TOIN_NM}, #{ZIPCD}, #{SIDO_ADDR}, #{BUPE_ADDR}, #{USE_YN} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{MOD_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{REG_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET TRNA = #{TRNA} , BIZNO = #{BIZNO} , CORP_REGST_NO = #{CORP_REGST_NO} , RPSNT_NM = #{RPSNT_NM} , TYPBU_NM = #{TYPBU_NM} , TOIN_NM = #{TOIN_NM} , ZIPCD = #{ZIPCD} , SIDO_ADDR = #{SIDO_ADDR} , BUPE_ADDR = #{BUPE_ADDR} , USE_YN = #{USE_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 공통 하도급사 수신 */ MERGE INTO TM_SBCN_CO USING DUAL ON (COCMP_BUPE_ID = #{COCMP_BUPE_ID}) WHEN NOT MATCHED THEN INSERT ( COCMP_BUPE_ID, TRNA, BIZNO, CORP_REGST_NO, RPSNT_NM, TYPBU_NM, TOIN_NM, ZIPCD, SIDO_ADDR, BUPE_ADDR, USE_YN , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{COCMP_BUPE_ID}, #{TRNA}, #{BIZNO}, #{CORP_REGST_NO}, #{RPSNT_NM}, #{TYPBU_NM}, #{TOIN_NM}, #{ZIPCD}, #{SIDO_ADDR}, #{BUPE_ADDR}, #{USE_YN} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{MOD_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_TIMESTAMP(NVL(#{REG_DATE}, TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS') ) WHEN MATCHED THEN UPDATE SET TRNA = #{TRNA} , BIZNO = #{BIZNO} , CORP_REGST_NO = #{CORP_REGST_NO} , RPSNT_NM = #{RPSNT_NM} , TYPBU_NM = #{TYPBU_NM} , TOIN_NM = #{TOIN_NM} , ZIPCD = #{ZIPCD} , SIDO_ADDR = #{SIDO_ADDR} , BUPE_ADDR = #{BUPE_ADDR} , USE_YN = #{USE_YN} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))