/* AMI공사관리 공사기본정보 수신 */ MERGE INTO TM_SIGONG_MASTER USING DUAL ON (CONS_NO = #{CONS_NO} AND HDQR_CD = #{HDQR_CD}) WHEN NOT MATCHED THEN INSERT ( 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 ) VALUES ( #{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} , 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 MCONS_NO = #{MCONS_NO} , BROF_CD = #{BROF_CD} , CONS_NM = #{CONS_NM} , CONS_OTLN = #{CONS_OTLN} , ORGN_CONS_DDCNT = #{ORGN_CONS_DDCNT} , SPSR_EMPID = #{SPSR_EMPID} , CSNTI_SEQNO = #{CSNTI_SEQNO} , ACPT_YMD = #{ACPT_YMD} , CONS_BUEN_CLCD = #{CONS_BUEN_CLCD} , CONS_TPCD = #{CONS_TPCD} , DESN_END_YMD = #{DESN_END_YMD} , DESN_CHGER_EMPID = #{DESN_CHGER_EMPID} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* AMI공사관리 공사별 하도급업체 수신 */ MERGE INTO TM_SIGONG_SBCN_CO USING DUAL ON (CONS_NO = #{CONS_NO} AND DEPT1 = #{DEPT1} AND SBSN_CO = #{SBSN_CO}) WHEN NOT MATCHED THEN INSERT ( CONS_NO, DEPT1, SBSN_CO, USE_YN , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{CONS_NO}, #{DEPT1}, #{SBSN_CO}, #{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 USE_YN = #{USE_YN}
/* AMI공사관리 대상정보_모뎀_신규공사 수신 */ MERGE INTO TM_SIGONG_ORDER_NEW_MODEM USING DUAL ON (BONBU_CD = #{BONBU_CD} AND CUST_NO = #{CUST_NO} AND METER_ID = #{METER_ID}) WHEN NOT MATCHED THEN INSERT ( 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 , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{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} , 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 OFFC_CD = #{OFFC_CD} , CONS_NO = #{CONS_NO} , GUM_DAY = #{GUM_DAY} , METER_TYPE = #{METER_TYPE} , MR_MTHD_CD = #{MR_MTHD_CD} , TR_FEED_NM = #{TR_FEED_NM} , SV_FEED_NM = #{SV_FEED_NM} , ADDR = #{ADDR} , COMMENT = #{COMMENT} , GEN_METER_ID = #{GEN_METER_ID} , UPDT_YMD = #{UPDT_YMD} , CMMT_CUST_NO = #{CMMT_CUST_NO} , TR_POLE_NO = #{TR_POLE_NO} , NOT_INSTALL_CNT = #{NOT_INSTALL_CNT} , TOT_CNT = #{TOT_CNT} , COMMENT_CD = #{COMMENT_CD} , CODE_DESC = #{CODE_DESC} , TCHGR_ID = #{TCHGR_ID} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* AMI공사관리 대상정보_모뎀_신호보강 수신 */ MERGE INTO TM_SIGONG_ORDER_SIG_MODEM USING DUAL ON (BONBU_CD = #{BONBU_CD} AND CUST_NO = #{CUST_NO} AND METER_ID_AF = #{METER_ID_AF}) WHEN NOT MATCHED THEN INSERT ( 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 ) VALUES ( #{BONBU_CD}, #{OFFC_CD}, #{CONS_NO}, #{PPA_YN}, #{PWRPLAN_YN}, #{RECONST_YN}, #{CUST_NO}, #{DCU_ID}, #{MAC}, #{RM_RSN}, #{EFEC_YM}, TO_TIMESTAMP(#{OLD_RM_YMD}, 'YYYYMMDDHH24MISS'), #{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} , 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 OFFC_CD = #{OFFC_CD} , CONS_NO = #{CONS_NO} , PPA_YN = #{PPA_YN} , PWRPLAN_YN = #{PWRPLAN_YN} , RECONST_YN = #{RECONST_YN} , DCU_ID = #{DCU_ID} , MAC = #{MAC} , RM_RSN = #{RM_RSN} , EFEC_YM = #{EFEC_YM} , OLD_RM_YMD = TO_TIMESTAMP(#{OLD_RM_YMD}, 'YYYYMMDDHH24MISS') , REG_DT = #{REG_DT} , LP_FIRST = #{LP_FIRST} , DIFF_B = #{DIFF_B} , METER_ID_BF = #{METER_ID_BF} , METER_TYPE = #{METER_TYPE} , BIZ_CD_BF = #{BIZ_CD_BF} , COMM_CD_BF = #{COMM_CD_BF} , MR_MTHD_CD_BF = #{MR_MTHD_CD_BF} , MASTER_YN_BF = #{MASTER_YN_BF} , NW_DCU_ID = #{NW_DCU_ID} , NW_MAC = #{NW_MAC} , METER_TYPE_AF = #{METER_TYPE_AF} , BIZ_CD_AF = #{BIZ_CD_AF} , COMM_CD_AF = #{COMM_CD_AF} , MR_MTHD_CD_AF = #{MR_MTHD_CD_AF} , MASTER_YN_AF = #{MASTER_YN_AF} , SEAL_YN = #{SEAL_YN} , TR_FEED = #{TR_FEED} , SV_FEED = #{SV_FEED} , ADDR = #{ADDR} , CUST_ROAD_ADDR = #{CUST_ROAD_ADDR} , COMBLD_NM = #{COMBLD_NM} , TRADE_NM = #{TRADE_NM} , DCU_EVENT_STATUS = #{DCU_EVENT_STATUS} , DCU_EVENT_STATUS1 = #{DCU_EVENT_STATUS1} , CUST_GUM_DAY = #{CUST_GUM_DAY} , TCHGR_ID = #{TCHGR_ID} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSTIMESTAMP, TO_TIMESTAMP(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* AMI공사관리 시공조치내역_DCU 송신 */ SELECT 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 FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, DATA_NUM, EXT_DCU_ID) AS R_NUM FROM TM_SIGONG_ACT_DCU WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 시공조치내역_DCU 송신 상태 변경 */ UPDATE TM_SIGONG_ACT_DCU SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND (DEPT1, BUSI_NUM, DATA_NUM, EXT_DCU_ID) IN ( SELECT DEPT1, BUSI_NUM, DATA_NUM, EXT_DCU_ID FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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 , 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, DATA_NUM, EXT_DCU_ID) AS R_NUM FROM TM_SIGONG_ACT_DCU WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 시공조치내역_모뎀 송신 */ SELECT 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 FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID) AS R_NUM FROM TM_SIGONG_ACT_MODEM WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 시공조치내역_DCU 송신 상태 변경 */ UPDATE TM_SIGONG_ACT_MODEM SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND (DEPT1, BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID) IN ( SELECT DEPT1, BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, INSTR_NUM, MAC_MODEM, EXT_FCTY_ID) AS R_NUM FROM TM_SIGONG_ACT_MODEM WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 설치불가/철거내역_DCU 송신 */ SELECT 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 FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, DATA_NUM, DCU_ID) AS R_NUM FROM TM_SIGONG_UNABLE_DCU WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 설치불가/철거내역_DCU 송신 상태 변경 */ UPDATE TM_SIGONG_UNABLE_DCU SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND (DEPT1, BUSI_NUM, DATA_NUM, DCU_ID) IN ( SELECT DEPT1, BUSI_NUM, DATA_NUM, DCU_ID FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS WORK_DATE, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, ATCH_FILE_ID_1, ATCH_FILE_ID_2, ATCH_FILE_ID_3 , 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, DATA_NUM, DCU_ID) AS R_NUM FROM TM_SIGONG_UNABLE_DCU WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* AMI공사관리 설치불가/철거내역_모뎀 송신 */ SELECT 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 FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, INSTR_NUM, EXT_FCTY_ID) AS R_NUM FROM TM_SIGONG_UNABLE_MODEM WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* AMI공사관리 설치불가/철거내역_모뎀 송신 상태 변경 */ UPDATE TM_SIGONG_UNABLE_MODEM SET MKAMI_SEND_YN = 'Y' , MKAMI_SEND_DATE = SYSTIMESTAMP WHERE 1 = 1 AND (DEPT1, BUSI_NUM, INSTR_NUM, EXT_FCTY_ID) IN ( SELECT DEPT1, BUSI_NUM, INSTR_NUM, EXT_FCTY_ID FROM ( SELECT 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, TO_CHAR(WORK_DATE, 'YYYYMMDDHH24MISS') AS 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, TO_CHAR(MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, BUSI_NUM, INSTR_NUM, EXT_FCTY_ID) AS R_NUM FROM TM_SIGONG_UNABLE_MODEM WHERE 1 = 1 AND MKAMI_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )