/* AMI공사관리 공사기본정보 송신 */ SELECT CONS_NO, MCONS_NO, HDQR_CD, BROF_CD, CONS_NM, CONS_OTLN, ORGN_CONS_DDCNT, SPSR_EMPID, CSNTI_SEQNO, ACPT_YMD, CONS_BUEN_CLCD, CONS_TPCD, DESN_END_YMD, DESN_CHGER_EMPID , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT CONS_NO, MCONS_NO, HDQR_CD, BROF_CD, CONS_NM, CONS_OTLN, ORGN_CONS_DDCNT, SPSR_EMPID, CSNTI_SEQNO, ACPT_YMD, CONS_BUEN_CLCD, CONS_TPCD, DESN_END_YMD, DESN_CHGER_EMPID , 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 CONS_NO, HDQR_CD) AS R_NUM FROM T_SIGONG_MASTER WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 공사기본정보 송신 상태 변경 */ UPDATE T_SIGONG_MASTER SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND (CONS_NO, HDQR_CD) IN ( SELECT CONS_NO, HDQR_CD FROM ( SELECT CONS_NO, MCONS_NO, HDQR_CD, BROF_CD, CONS_NM, CONS_OTLN, ORGN_CONS_DDCNT, SPSR_EMPID, CSNTI_SEQNO, ACPT_YMD, CONS_BUEN_CLCD, CONS_TPCD, DESN_END_YMD, DESN_CHGER_EMPID , 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 CONS_NO, HDQR_CD) AS R_NUM FROM T_SIGONG_MASTER WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 공사별 하도급업체 송신 */ SELECT CONS_NO, DEPT1, SBSN_CO, USE_YN , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT CONS_NO, DEPT1, SBSN_CO, 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 CONS_NO, DEPT1, SBSN_CO) AS R_NUM FROM T_SIGONG_SBCN_CO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 공사별 하도급업체 송신 상태 변경 */ UPDATE T_SIGONG_SBCN_CO SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND (CONS_NO, DEPT1, SBSN_CO) IN ( SELECT CONS_NO, DEPT1, SBSN_CO FROM ( SELECT CONS_NO, DEPT1, SBSN_CO, 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 CONS_NO, DEPT1, SBSN_CO) AS R_NUM FROM T_SIGONG_SBCN_CO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 대상정보_모뎀_신규공사 송신 */ SELECT BONBU_CD, OFFC_CD, CONS_NO, CUST_NO, GUM_DAY, METER_ID, METER_TYPE, MR_MTHD_CD, TR_FEED_NM, SV_FEED_NM, ADDR, "COMMENT", GEN_METER_ID, UPDT_YMD, CMMT_CUST_NO, TR_POLE_NO, NOT_INSTALL_CNT, TOT_CNT, COMMENT_CD, CODE_DESC, TCHGR_ID , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT BONBU_CD, OFFC_CD, CONS_NO, CUST_NO, GUM_DAY, METER_ID, METER_TYPE, MR_MTHD_CD, TR_FEED_NM, SV_FEED_NM, ADDR, "COMMENT", GEN_METER_ID, UPDT_YMD, CMMT_CUST_NO, TR_POLE_NO, NOT_INSTALL_CNT, TOT_CNT, COMMENT_CD, CODE_DESC, TCHGR_ID , 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 CUST_NO) AS R_NUM FROM T_SIGONG_ORDER_NEW_MODEM WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 대상정보_모뎀_신규공사 송신 상태 변경 */ UPDATE T_SIGONG_ORDER_NEW_MODEM SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND CUST_NO IN ( SELECT CUST_NO FROM ( SELECT BONBU_CD, OFFC_CD, CONS_NO, CUST_NO, GUM_DAY, METER_ID, METER_TYPE, MR_MTHD_CD, TR_FEED_NM, SV_FEED_NM, ADDR, "COMMENT", GEN_METER_ID, UPDT_YMD, CMMT_CUST_NO, TR_POLE_NO, NOT_INSTALL_CNT, TOT_CNT, COMMENT_CD, CODE_DESC, TCHGR_ID , 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 CUST_NO) AS R_NUM FROM T_SIGONG_ORDER_NEW_MODEM WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 대상정보_모뎀_신호보강 송신 */ SELECT BONBU_CD, OFFC_CD, CONS_NO, PPA_YN, PWRPLAN_YN, RECONST_YN, CUST_NO, DCU_ID, MAC, RM_RSN, EFEC_YM, OLD_RM_YMD, REG_DT, LP_FIRST, DIFF_B, METER_ID_BF, METER_TYPE, BIZ_CD_BF, COMM_CD_BF, MR_MTHD_CD_BF, MASTER_YN_BF, NW_DCU_ID, NW_MAC, METER_ID_AF, METER_TYPE_AF, BIZ_CD_AF, COMM_CD_AF, MR_MTHD_CD_AF, MASTER_YN_AF, SEAL_YN, TR_FEED, SV_FEED, ADDR, CUST_ROAD_ADDR, COMBLD_NM, TRADE_NM, DCU_EVENT_STATUS, DCU_EVENT_STATUS1, CUST_GUM_DAY, TCHGR_ID , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT BONBU_CD, OFFC_CD, CONS_NO, PPA_YN, PWRPLAN_YN, RECONST_YN, CUST_NO, DCU_ID, MAC, RM_RSN, EFEC_YM, TO_CHAR(OLD_RM_YMD, 'YYYYMMDDHH24MISS') AS OLD_RM_YMD, REG_DT, LP_FIRST, DIFF_B, METER_ID_BF, METER_TYPE, BIZ_CD_BF, COMM_CD_BF, MR_MTHD_CD_BF, MASTER_YN_BF, NW_DCU_ID, NW_MAC, METER_ID_AF , METER_TYPE_AF, BIZ_CD_AF, COMM_CD_AF, MR_MTHD_CD_AF, MASTER_YN_AF, SEAL_YN, TR_FEED, SV_FEED, ADDR, CUST_ROAD_ADDR, COMBLD_NM, TRADE_NM, DCU_EVENT_STATUS, DCU_EVENT_STATUS1, CUST_GUM_DAY, TCHGR_ID , 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 CUST_NO) AS R_NUM FROM T_SIGONG_ORDER_SIG_MODEM WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 대상정보_모뎀_신호보강 송신 상태 변경 */ UPDATE T_SIGONG_ORDER_SIG_MODEM SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND CUST_NO IN ( SELECT CUST_NO FROM ( SELECT BONBU_CD, OFFC_CD, CONS_NO, PPA_YN, PWRPLAN_YN, RECONST_YN, CUST_NO, DCU_ID, MAC, RM_RSN, EFEC_YM, TO_CHAR(OLD_RM_YMD, 'YYYYMMDDHH24MISS') AS OLD_RM_YMD, REG_DT, LP_FIRST, DIFF_B, METER_ID_BF, METER_TYPE, BIZ_CD_BF, COMM_CD_BF, MR_MTHD_CD_BF, MASTER_YN_BF, NW_DCU_ID, NW_MAC, METER_ID_AF , METER_TYPE_AF, BIZ_CD_AF, COMM_CD_AF, MR_MTHD_CD_AF, MASTER_YN_AF, SEAL_YN, TR_FEED, SV_FEED, ADDR, CUST_ROAD_ADDR, COMBLD_NM, TRADE_NM, DCU_EVENT_STATUS, DCU_EVENT_STATUS1, CUST_GUM_DAY, TCHGR_ID , 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 CUST_NO) AS R_NUM FROM T_SIGONG_ORDER_SIG_MODEM WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 시공조치내역_DCU 수신 */ DECLARE V_EXT_DCU_ID VARCHAR2(100); V_COUNT NUMBER(10); V_STAGE VARCHAR2(100); BEGIN V_EXT_DCU_ID := #{EXT_DCU_ID}; IF V_EXT_DCU_ID IS NULL THEN BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_DCU WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_NEW'; ELSE V_STAGE := 'INSERT_NEW'; END IF; END; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_DCU WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_FROM_NEW'; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_DCU WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = #{EXT_DCU_ID} ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE'; ELSE V_STAGE := 'INSERT'; END IF; END; END IF; END; END IF; IF V_STAGE = 'INSERT' THEN BEGIN INSERT INTO T_SIGONG_ACT_DCU ( BUSI_NUM, DATA_NUM, EXT_DCU_ID, DCU_ID, WORK_STEP, GN_NAME, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, WORK_DIV, REMV_MEMO, INST_M, INST_S, IND_CBD_DIV_CD, FAC1, MANU_NUM, LINE_FAIR, KVA_A, KVA_B, KVA_C, USE_CT, USE_POWER, AM_BAND, FILM_BAND, GRADEL, G_WIRE, ETC, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, INST_KIND, INST_TYPE, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5, ATCH_FILE_ID_6, ATCH_FILE_ID_7, ATCH_FILE_ID_8, ATCH_FILE_ID_9, ATCH_FILE_ID_10, TRF_MFG_YM_A, TRF_MFG_YM_B, TRF_MFG_YM_C, TRF_MRF_A, TRF_MRF_B, TRF_MRF_C, EXT_DCU_MAC, NEW_DCU_MAC , DCU_SIGONG_CD, TDU_USE_YN, EXT_MLN_MAC_MODEM, MLN_MAC_MODEM, EXT_MAC_MODEM, MAC_MODEM, EXT_INSTR_NUM, INSTR_NUM, EXT_MTRL_NO, EXT_MANU_CD, EXT_MNFCT_YM, MTRL_NO, MANU_CD, MNFCT_YM, BOX_NO , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{DATA_NUM}, #{EXT_DCU_ID}, #{DCU_ID}, '50', #{GN_NAME}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORK_DIV}, #{REMV_MEMO}, #{INST_M}, #{INST_S}, #{IND_CBD_DIV_CD}, #{FAC1}, #{MANU_NUM}, #{LINE_FAIR}, #{KVA_A}, #{KVA_B}, #{KVA_C}, #{USE_CT}, #{USE_POWER}, #{AM_BAND}, #{FILM_BAND}, #{GRADEL}, #{G_WIRE}, #{ETC}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{INST_KIND}, #{INST_TYPE}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5}, #{ATCH_FILE_ID_6}, #{ATCH_FILE_ID_7}, #{ATCH_FILE_ID_8}, #{ATCH_FILE_ID_9}, #{ATCH_FILE_ID_10}, #{TRF_MFG_YM_A}, #{TRF_MFG_YM_B}, #{TRF_MFG_YM_C}, #{TRF_MRF_A}, #{TRF_MRF_B}, #{TRF_MRF_C}, #{EXT_DCU_MAC}, #{NEW_DCU_MAC} , #{DCU_SIGONG_CD}, #{TDU_USE_YN}, #{EXT_MLN_MAC_MODEM}, #{MLN_MAC_MODEM}, #{EXT_MAC_MODEM}, #{MAC_MODEM}, #{EXT_INSTR_NUM}, #{INSTR_NUM}, #{EXT_MTRL_NO}, #{EXT_MANU_CD}, #{EXT_MNFCT_YM}, #{MTRL_NO}, #{MANU_CD}, #{MNFCT_YM}, #{BOX_NO} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE' THEN BEGIN UPDATE T_SIGONG_ACT_DCU SET DCU_ID = #{DCU_ID} , WORK_STEP = '50' , GN_NAME = #{GN_NAME} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FAC1 = #{FAC1} , MANU_NUM = #{MANU_NUM} , LINE_FAIR = #{LINE_FAIR} , KVA_A = #{KVA_A} , KVA_B = #{KVA_B} , KVA_C = #{KVA_C} , USE_CT = #{USE_CT} , USE_POWER = #{USE_POWER} , AM_BAND = #{AM_BAND} , FILM_BAND = #{FILM_BAND} , GRADEL = #{GRADEL} , G_WIRE = #{G_WIRE} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , INST_KIND = #{INST_KIND} , INST_TYPE = #{INST_TYPE} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , ATCH_FILE_ID_6 = #{ATCH_FILE_ID_6} , ATCH_FILE_ID_7 = #{ATCH_FILE_ID_7} , ATCH_FILE_ID_8 = #{ATCH_FILE_ID_8} , ATCH_FILE_ID_9 = #{ATCH_FILE_ID_9} , ATCH_FILE_ID_10 = #{ATCH_FILE_ID_10} , TRF_MFG_YM_A = #{TRF_MFG_YM_A} , TRF_MFG_YM_B = #{TRF_MFG_YM_B} , TRF_MFG_YM_C = #{TRF_MFG_YM_C} , TRF_MRF_A = #{TRF_MRF_A} , TRF_MRF_B = #{TRF_MRF_B} , TRF_MRF_C = #{TRF_MRF_C} , EXT_DCU_MAC = #{EXT_DCU_MAC} , NEW_DCU_MAC = #{NEW_DCU_MAC} , DCU_SIGONG_CD = #{DCU_SIGONG_CD} , TDU_USE_YN = #{TDU_USE_YN} , EXT_MLN_MAC_MODEM = #{EXT_MLN_MAC_MODEM} , MLN_MAC_MODEM = #{MLN_MAC_MODEM} , EXT_MAC_MODEM = #{EXT_MAC_MODEM} , MAC_MODEM = #{MAC_MODEM} , EXT_INSTR_NUM = #{EXT_INSTR_NUM} , INSTR_NUM = #{INSTR_NUM} , EXT_MTRL_NO = #{EXT_MTRL_NO} , EXT_MANU_CD = #{EXT_MANU_CD} , EXT_MNFCT_YM = #{EXT_MNFCT_YM} , MTRL_NO = #{MTRL_NO} , MANU_CD = #{MANU_CD} , MNFCT_YM = #{MNFCT_YM} , BOX_NO = #{BOX_NO} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = #{EXT_DCU_ID} ; END; ELSIF V_STAGE = 'INSERT_NEW' THEN BEGIN INSERT INTO T_SIGONG_ACT_DCU ( BUSI_NUM, DATA_NUM, EXT_DCU_ID, DCU_ID, WORK_STEP, GN_NAME, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, WORK_DIV, REMV_MEMO, INST_M, INST_S, IND_CBD_DIV_CD, FAC1, MANU_NUM, LINE_FAIR, KVA_A, KVA_B, KVA_C, USE_CT, USE_POWER, AM_BAND, FILM_BAND, GRADEL, G_WIRE, ETC, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, INST_KIND, INST_TYPE, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5, ATCH_FILE_ID_6, ATCH_FILE_ID_7, ATCH_FILE_ID_8, ATCH_FILE_ID_9, ATCH_FILE_ID_10, TRF_MFG_YM_A, TRF_MFG_YM_B, TRF_MFG_YM_C, TRF_MRF_A, TRF_MRF_B, TRF_MRF_C, EXT_DCU_MAC, NEW_DCU_MAC , DCU_SIGONG_CD, TDU_USE_YN, EXT_MLN_MAC_MODEM, MLN_MAC_MODEM, EXT_MAC_MODEM, MAC_MODEM, EXT_INSTR_NUM, INSTR_NUM, EXT_MTRL_NO, EXT_MANU_CD, EXT_MNFCT_YM, MTRL_NO, MANU_CD, MNFCT_YM, BOX_NO , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{DATA_NUM}, 'NEW', #{DCU_ID}, '50', #{GN_NAME}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORK_DIV}, #{REMV_MEMO}, #{INST_M}, #{INST_S}, #{IND_CBD_DIV_CD}, #{FAC1}, #{MANU_NUM}, #{LINE_FAIR}, #{KVA_A}, #{KVA_B}, #{KVA_C}, #{USE_CT}, #{USE_POWER}, #{AM_BAND}, #{FILM_BAND}, #{GRADEL}, #{G_WIRE}, #{ETC}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{INST_KIND}, #{INST_TYPE}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5}, #{ATCH_FILE_ID_6}, #{ATCH_FILE_ID_7}, #{ATCH_FILE_ID_8}, #{ATCH_FILE_ID_9}, #{ATCH_FILE_ID_10}, #{TRF_MFG_YM_A}, #{TRF_MFG_YM_B}, #{TRF_MFG_YM_C}, #{TRF_MRF_A}, #{TRF_MRF_B}, #{TRF_MRF_C}, #{EXT_DCU_MAC}, #{NEW_DCU_MAC} , #{DCU_SIGONG_CD}, #{TDU_USE_YN}, #{EXT_MLN_MAC_MODEM}, #{MLN_MAC_MODEM}, #{EXT_MAC_MODEM}, #{MAC_MODEM}, #{EXT_INSTR_NUM}, #{INSTR_NUM}, #{EXT_MTRL_NO}, #{EXT_MANU_CD}, #{EXT_MNFCT_YM}, #{MTRL_NO}, #{MANU_CD}, #{MNFCT_YM}, #{BOX_NO} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE_NEW' THEN BEGIN UPDATE T_SIGONG_ACT_DCU SET DCU_ID = #{DCU_ID} , WORK_STEP = '50' , GN_NAME = #{GN_NAME} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FAC1 = #{FAC1} , MANU_NUM = #{MANU_NUM} , LINE_FAIR = #{LINE_FAIR} , KVA_A = #{KVA_A} , KVA_B = #{KVA_B} , KVA_C = #{KVA_C} , USE_CT = #{USE_CT} , USE_POWER = #{USE_POWER} , AM_BAND = #{AM_BAND} , FILM_BAND = #{FILM_BAND} , GRADEL = #{GRADEL} , G_WIRE = #{G_WIRE} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , INST_KIND = #{INST_KIND} , INST_TYPE = #{INST_TYPE} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , ATCH_FILE_ID_6 = #{ATCH_FILE_ID_6} , ATCH_FILE_ID_7 = #{ATCH_FILE_ID_7} , ATCH_FILE_ID_8 = #{ATCH_FILE_ID_8} , ATCH_FILE_ID_9 = #{ATCH_FILE_ID_9} , ATCH_FILE_ID_10 = #{ATCH_FILE_ID_10} , TRF_MFG_YM_A = #{TRF_MFG_YM_A} , TRF_MFG_YM_B = #{TRF_MFG_YM_B} , TRF_MFG_YM_C = #{TRF_MFG_YM_C} , TRF_MRF_A = #{TRF_MRF_A} , TRF_MRF_B = #{TRF_MRF_B} , TRF_MRF_C = #{TRF_MRF_C} , EXT_DCU_MAC = #{EXT_DCU_MAC} , NEW_DCU_MAC = #{NEW_DCU_MAC} , DCU_SIGONG_CD = #{DCU_SIGONG_CD} , TDU_USE_YN = #{TDU_USE_YN} , EXT_MLN_MAC_MODEM = #{EXT_MLN_MAC_MODEM} , MLN_MAC_MODEM = #{MLN_MAC_MODEM} , EXT_MAC_MODEM = #{EXT_MAC_MODEM} , MAC_MODEM = #{MAC_MODEM} , EXT_INSTR_NUM = #{EXT_INSTR_NUM} , INSTR_NUM = #{INSTR_NUM} , EXT_MTRL_NO = #{EXT_MTRL_NO} , EXT_MANU_CD = #{EXT_MANU_CD} , EXT_MNFCT_YM = #{EXT_MNFCT_YM} , MTRL_NO = #{MTRL_NO} , MANU_CD = #{MANU_CD} , MNFCT_YM = #{MNFCT_YM} , BOX_NO = #{BOX_NO} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = 'NEW' ; END; ELSIF V_STAGE = 'UPDATE_FROM_NEW' THEN BEGIN UPDATE T_SIGONG_ACT_DCU SET EXT_DCU_ID = #{EXT_DCU_ID} , DCU_ID = #{DCU_ID} , WORK_STEP = '50' , GN_NAME = #{GN_NAME} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FAC1 = #{FAC1} , MANU_NUM = #{MANU_NUM} , LINE_FAIR = #{LINE_FAIR} , KVA_A = #{KVA_A} , KVA_B = #{KVA_B} , KVA_C = #{KVA_C} , USE_CT = #{USE_CT} , USE_POWER = #{USE_POWER} , AM_BAND = #{AM_BAND} , FILM_BAND = #{FILM_BAND} , GRADEL = #{GRADEL} , G_WIRE = #{G_WIRE} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , INST_KIND = #{INST_KIND} , INST_TYPE = #{INST_TYPE} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , ATCH_FILE_ID_6 = #{ATCH_FILE_ID_6} , ATCH_FILE_ID_7 = #{ATCH_FILE_ID_7} , ATCH_FILE_ID_8 = #{ATCH_FILE_ID_8} , ATCH_FILE_ID_9 = #{ATCH_FILE_ID_9} , ATCH_FILE_ID_10 = #{ATCH_FILE_ID_10} , TRF_MFG_YM_A = #{TRF_MFG_YM_A} , TRF_MFG_YM_B = #{TRF_MFG_YM_B} , TRF_MFG_YM_C = #{TRF_MFG_YM_C} , TRF_MRF_A = #{TRF_MRF_A} , TRF_MRF_B = #{TRF_MRF_B} , TRF_MRF_C = #{TRF_MRF_C} , EXT_DCU_MAC = #{EXT_DCU_MAC} , NEW_DCU_MAC = #{NEW_DCU_MAC} , DCU_SIGONG_CD = #{DCU_SIGONG_CD} , TDU_USE_YN = #{TDU_USE_YN} , EXT_MLN_MAC_MODEM = #{EXT_MLN_MAC_MODEM} , MLN_MAC_MODEM = #{MLN_MAC_MODEM} , EXT_MAC_MODEM = #{EXT_MAC_MODEM} , MAC_MODEM = #{MAC_MODEM} , EXT_INSTR_NUM = #{EXT_INSTR_NUM} , INSTR_NUM = #{INSTR_NUM} , EXT_MTRL_NO = #{EXT_MTRL_NO} , EXT_MANU_CD = #{EXT_MANU_CD} , EXT_MNFCT_YM = #{EXT_MNFCT_YM} , MTRL_NO = #{MTRL_NO} , MANU_CD = #{MANU_CD} , MNFCT_YM = #{MNFCT_YM} , BOX_NO = #{BOX_NO} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND EXT_DCU_ID = 'NEW' ; END; END IF; END;
/* AMI공사관리 시공조치내역_모뎀 수신 */ DECLARE V_EXT_FCTY_ID VARCHAR2(100); V_COUNT NUMBER(10); V_STAGE VARCHAR2(100); BEGIN V_EXT_FCTY_ID := #{EXT_FCTY_ID}; IF V_EXT_FCTY_ID IS NULL THEN BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_NEW'; ELSE V_STAGE := 'INSERT_NEW'; END IF; END; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_FROM_NEW'; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_ACT_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = #{EXT_FCTY_ID} ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE'; ELSE V_STAGE := 'INSERT'; END IF; END; END IF; END; END IF; IF V_STAGE = 'INSERT' THEN BEGIN INSERT INTO T_SIGONG_ACT_MODEM ( BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID, WORK_STEP, DATA_NUM, DCU_ID, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, WORK_DIV, REMV_MEMO, INST_M, INST_S, IND_CBD_DIV_CD, FCLTY_DIV, MODEM_DIV, SEAL_BOX1, SEAL_BOX2, SEAL_METER1, SEAL_METER2, SEAL_OUTER1, SEAL_OUTER2, LINE_TYPE, VISIT_DIV, SIG_LEVEL, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, BUNGI, ETC1, ETC2, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5, EXT_CONN_DEV , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{INSTR_NUM}, #{MAC_MODEM}, #{EXT_FCTY_ID}, '50', #{DATA_NUM}, #{DCU_ID}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORK_DIV}, #{REMV_MEMO}, #{INST_M}, #{INST_S}, #{IND_CBD_DIV_CD}, #{FCLTY_DIV}, #{MODEM_DIV}, #{SEAL_BOX1}, #{SEAL_BOX2}, #{SEAL_METER1}, #{SEAL_METER2}, #{SEAL_OUTER1}, #{SEAL_OUTER2}, #{LINE_TYPE}, #{VISIT_DIV}, #{SIG_LEVEL}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{BUNGI}, #{ETC1}, #{ETC2}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5}, #{EXT_CONN_DEV} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE' THEN BEGIN UPDATE T_SIGONG_ACT_MODEM SET WORK_STEP = '50' , DATA_NUM = #{DATA_NUM} , DCU_ID = #{DCU_ID} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FCLTY_DIV = #{FCLTY_DIV} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , LINE_TYPE = #{LINE_TYPE} , VISIT_DIV = #{VISIT_DIV} , SIG_LEVEL = #{SIG_LEVEL} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , BUNGI = #{BUNGI} , ETC1 = #{ETC1} , ETC2 = #{ETC2} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , EXT_CONN_DEV = #{EXT_CONN_DEV} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = #{EXT_FCTY_ID} ; END; ELSIF V_STAGE = 'INSERT_NEW' THEN BEGIN INSERT INTO T_SIGONG_ACT_MODEM ( BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID, WORK_STEP, DATA_NUM, DCU_ID, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, WORK_DIV, REMV_MEMO, INST_M, INST_S, IND_CBD_DIV_CD, FCLTY_DIV, MODEM_DIV, SEAL_BOX1, SEAL_BOX2, SEAL_METER1, SEAL_METER2, SEAL_OUTER1, SEAL_OUTER2, LINE_TYPE, VISIT_DIV, SIG_LEVEL, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, BUNGI, ETC1, ETC2, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5, EXT_CONN_DEV , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{INSTR_NUM}, #{MAC_MODEM}, 'NEW', '50', #{DATA_NUM}, #{DCU_ID}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORK_DIV}, #{REMV_MEMO}, #{INST_M}, #{INST_S}, #{IND_CBD_DIV_CD}, #{FCLTY_DIV}, #{MODEM_DIV}, #{SEAL_BOX1}, #{SEAL_BOX2}, #{SEAL_METER1}, #{SEAL_METER2}, #{SEAL_OUTER1}, #{SEAL_OUTER2}, #{LINE_TYPE}, #{VISIT_DIV}, #{SIG_LEVEL}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{BUNGI}, #{ETC1}, #{ETC2}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5}, #{EXT_CONN_DEV} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE_NEW' THEN BEGIN UPDATE T_SIGONG_ACT_MODEM SET WORK_STEP = '50' , DATA_NUM = #{DATA_NUM} , DCU_ID = #{DCU_ID} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FCLTY_DIV = #{FCLTY_DIV} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , LINE_TYPE = #{LINE_TYPE} , VISIT_DIV = #{VISIT_DIV} , SIG_LEVEL = #{SIG_LEVEL} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , BUNGI = #{BUNGI} , ETC1 = #{ETC1} , ETC2 = #{ETC2} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , EXT_CONN_DEV = #{EXT_CONN_DEV} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = 'NEW' ; END; ELSIF V_STAGE = 'UPDATE_FROM_NEW' THEN BEGIN UPDATE T_SIGONG_ACT_MODEM SET EXT_FCTY_ID = #{EXT_FCTY_ID} , WORK_STEP = '50' , DATA_NUM = #{DATA_NUM} , DCU_ID = #{DCU_ID} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , REMV_MEMO = #{REMV_MEMO} , INST_M = #{INST_M} , INST_S = #{INST_S} , IND_CBD_DIV_CD = #{IND_CBD_DIV_CD} , FCLTY_DIV = #{FCLTY_DIV} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , LINE_TYPE = #{LINE_TYPE} , VISIT_DIV = #{VISIT_DIV} , SIG_LEVEL = #{SIG_LEVEL} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , BUNGI = #{BUNGI} , ETC1 = #{ETC1} , ETC2 = #{ETC2} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , EXT_CONN_DEV = #{EXT_CONN_DEV} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND MAC_MODEM = #{MAC_MODEM} AND EXT_FCTY_ID = 'NEW' ; END; END IF; END;
/* AMI공사관리 설치불가/철거내역_DCU 수신 */ MERGE INTO T_SIGONG_UNABLE_DCU USING DUAL ON (DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND DATA_NUM = #{DATA_NUM} AND DCU_ID = #{DCU_ID}) WHEN NOT MATCHED THEN INSERT ( BUSI_NUM, DATA_NUM, DCU_ID, WORK_STEP, GN_NAME, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, WORK_DIV, UNABLE_RESULT, UNABLE_DETAIL, UNABLE_INP_DETAIL, ETC, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, EXT_DCU_MAC , DCU_SIGONG_CD, TDU_USE_YN, EXT_MLN_MAC_MODEM, EXT_MAC_MODEM, EXT_INSTR_NUM, EXT_MTRL_NO, EXT_MANU_CD, EXT_MNFCT_YM, EXT_BOX_NO , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{DATA_NUM}, #{DCU_ID}, '50', #{GN_NAME}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{WORK_DIV}, #{UNABLE_RESULT}, #{UNABLE_DETAIL}, #{UNABLE_INP_DETAIL}, #{ETC}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{EXT_DCU_MAC} , #{DCU_SIGONG_CD}, #{TDU_USE_YN}, #{EXT_MLN_MAC_MODEM}, #{EXT_MAC_MODEM}, #{EXT_INSTR_NUM}, #{EXT_MTRL_NO}, #{EXT_MANU_CD}, #{EXT_MNFCT_YM}, #{EXT_BOX_NO} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) WHEN MATCHED THEN UPDATE SET WORK_STEP = '50' , GN_NAME = #{GN_NAME} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , WORK_DIV = #{WORK_DIV} , UNABLE_RESULT = #{UNABLE_RESULT} , UNABLE_DETAIL = #{UNABLE_DETAIL} , UNABLE_INP_DETAIL = #{UNABLE_INP_DETAIL} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , EXT_DCU_MAC = #{EXT_DCU_MAC} , DCU_SIGONG_CD = #{DCU_SIGONG_CD} , TDU_USE_YN = #{TDU_USE_YN} , EXT_MLN_MAC_MODEM = #{EXT_MLN_MAC_MODEM} , EXT_MAC_MODEM = #{EXT_MAC_MODEM} , EXT_INSTR_NUM = #{EXT_INSTR_NUM} , EXT_MTRL_NO = #{EXT_MTRL_NO} , EXT_MANU_CD = #{EXT_MANU_CD} , EXT_MNFCT_YM = #{EXT_MNFCT_YM} , EXT_BOX_NO = #{EXT_BOX_NO} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N'
/* AMI공사관리 설치불가/철거내역_모뎀 수신 */ DECLARE V_EXT_FCTY_ID VARCHAR2(100); V_COUNT NUMBER(10); V_STAGE VARCHAR2(100); BEGIN V_EXT_FCTY_ID := #{EXT_FCTY_ID}; IF V_EXT_FCTY_ID IS NULL THEN BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_UNABLE_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_NEW'; ELSE V_STAGE := 'INSERT_NEW'; END IF; END; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_UNABLE_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = 'NEW' ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE_FROM_NEW'; ELSE BEGIN SELECT COUNT(*) INTO V_COUNT FROM T_SIGONG_UNABLE_MODEM WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = #{EXT_FCTY_ID} ; IF V_COUNT > 0 THEN V_STAGE := 'UPDATE'; ELSE V_STAGE := 'INSERT'; END IF; END; END IF; END;

END IF; IF V_STAGE = 'INSERT' THEN BEGIN INSERT INTO T_SIGONG_UNABLE_MODEM ( BUSI_NUM, INSTR_NUM, EXT_FCTY_ID, WORK_STEP, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, INST_S, FCLTY_DIV, INST_M, MODEM_DIV, SEAL_BOX1, SEAL_BOX2, SEAL_METER1, SEAL_METER2, SEAL_OUTER1, SEAL_OUTER2, WORK_DIV, UNABLE_RESULT, UNABLE_DETAIL, UNABLE_INP_DETAIL, ETC, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5 , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{INSTR_NUM}, #{EXT_FCTY_ID}, '50', #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{INST_S}, #{FCLTY_DIV}, #{INST_M}, #{MODEM_DIV}, #{SEAL_BOX1}, #{SEAL_BOX2}, #{SEAL_METER1}, #{SEAL_METER2}, #{SEAL_OUTER1}, #{SEAL_OUTER2}, #{WORK_DIV}, #{UNABLE_RESULT}, #{UNABLE_DETAIL}, #{UNABLE_INP_DETAIL}, #{ETC}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE' THEN BEGIN UPDATE T_SIGONG_UNABLE_MODEM SET WORK_STEP = '50' , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , INST_S = #{INST_S} , FCLTY_DIV = #{FCLTY_DIV} , INST_M = #{INST_M} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , WORK_DIV = #{WORK_DIV} , UNABLE_RESULT = #{UNABLE_RESULT} , UNABLE_DETAIL = #{UNABLE_DETAIL} , UNABLE_INP_DETAIL = #{UNABLE_INP_DETAIL} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = #{EXT_FCTY_ID} ; END; ELSIF V_STAGE = 'INSERT_NEW' THEN BEGIN INSERT INTO T_SIGONG_UNABLE_MODEM ( BUSI_NUM, INSTR_NUM, EXT_FCTY_ID, WORK_STEP, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, INST_S, FCLTY_DIV, INST_M, MODEM_DIV, SEAL_BOX1, SEAL_BOX2, SEAL_METER1, SEAL_METER2, SEAL_OUTER1, SEAL_OUTER2, WORK_DIV, UNABLE_RESULT, UNABLE_DETAIL, UNABLE_INP_DETAIL, ETC, WORK_REG_ID, WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3, ATCH_FILE_ID_4, ATCH_FILE_ID_5 , MOD_ID, MOD_DATE, REG_ID, REG_DATE, SEND_YN ) VALUES ( #{BUSI_NUM}, #{INSTR_NUM}, 'NEW', '50', #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{INST_S}, #{FCLTY_DIV}, #{INST_M}, #{MODEM_DIV}, #{SEAL_BOX1}, #{SEAL_BOX2}, #{SEAL_METER1}, #{SEAL_METER2}, #{SEAL_OUTER1}, #{SEAL_OUTER2}, #{WORK_DIV}, #{UNABLE_RESULT}, #{UNABLE_DETAIL}, #{UNABLE_INP_DETAIL}, #{ETC}, #{WORK_REG_ID}, TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS'), #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{ATCH_FILE_ID_1}, #{ATCH_FILE_ID_2}, #{ATCH_FILE_ID_3}, #{ATCH_FILE_ID_4}, #{ATCH_FILE_ID_5} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{MOD_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), NVL(#{REG_ID}, 'DFXAGENT_USER'), TO_DATE(NVL(#{REG_DATE}, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')), 'YYYYMMDDHH24MISS'), 'N' ) ; END; ELSIF V_STAGE = 'UPDATE_NEW' THEN BEGIN UPDATE T_SIGONG_UNABLE_MODEM SET WORK_STEP = '50' , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , INST_S = #{INST_S} , FCLTY_DIV = #{FCLTY_DIV} , INST_M = #{INST_M} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , WORK_DIV = #{WORK_DIV} , UNABLE_RESULT = #{UNABLE_RESULT} , UNABLE_DETAIL = #{UNABLE_DETAIL} , UNABLE_INP_DETAIL = #{UNABLE_INP_DETAIL} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = #{EXT_FCTY_ID} ; END; ELSIF V_STAGE = 'UPDATE_FROM_NEW' THEN BEGIN UPDATE T_SIGONG_UNABLE_MODEM SET EXT_FCTY_ID = #{EXT_FCTY_ID} , WORK_STEP = '50' , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , INST_S = #{INST_S} , FCLTY_DIV = #{FCLTY_DIV} , INST_M = #{INST_M} , MODEM_DIV = #{MODEM_DIV} , SEAL_BOX1 = #{SEAL_BOX1} , SEAL_BOX2 = #{SEAL_BOX2} , SEAL_METER1 = #{SEAL_METER1} , SEAL_METER2 = #{SEAL_METER2} , SEAL_OUTER1 = #{SEAL_OUTER1} , SEAL_OUTER2 = #{SEAL_OUTER2} , WORK_DIV = #{WORK_DIV} , UNABLE_RESULT = #{UNABLE_RESULT} , UNABLE_DETAIL = #{UNABLE_DETAIL} , UNABLE_INP_DETAIL = #{UNABLE_INP_DETAIL} , ETC = #{ETC} , WORK_REG_ID = #{WORK_REG_ID} , WORK_DATE = TO_DATE(#{WORK_DATE}, 'YYYYMMDDHH24MISS') , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , ATCH_FILE_ID_1 = #{ATCH_FILE_ID_1} , ATCH_FILE_ID_2 = #{ATCH_FILE_ID_2} , ATCH_FILE_ID_3 = #{ATCH_FILE_ID_3} , ATCH_FILE_ID_4 = #{ATCH_FILE_ID_4} , ATCH_FILE_ID_5 = #{ATCH_FILE_ID_5} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , SEND_YN = 'N' WHERE 1 = 1 AND DEPT1 = #{DEPT1} AND BUSI_NUM = #{BUSI_NUM} AND INSTR_NUM = #{INSTR_NUM} AND EXT_FCTY_ID = 'NEW' ; END; END IF; END;