/* 유지관리 작업대상_DCU 송신 */ SELECT ERR_ID, WORK_STEP, WORK_DIV, FCTY_CD, COMM_CD, EVENT_START_DT, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, BUSI_DIV, M_COMPANY, ERR_TYPE1, ERR_TYPE2, GUM_DAY, ORDER_DT, ORDERER_ID, METER_DT, MEMO, PRE_ERR_ID, WORK_GP_NO, DCU_ID, DCU_IP, DURAT_TIME, NDIS_WORK_NO, NDIS_STAT, ADDRESS, ROAD_ADDRESS, TR_FEED_NM, MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.COMM_CD, TO_CHAR(A.EVENT_START_DT, 'YYYYMMDDHH24MISS') AS EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, TO_CHAR(A.ORDER_DT, 'YYYYMMDDHH24MISS') AS ORDER_DT, A.ORDERER_ID, TO_CHAR(A.METER_DT, 'YYYYMMDDHH24MISS') AS METER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.DCU_ID, A.DCU_IP, A.DURAT_TIME, A.NDIS_WORK_NO, A.NDIS_STAT , B.ADDRESS, B.ROAD_ADDRESS, B.TR_FEED_NM , A.MOD_ID, TO_CHAR(A.MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, A.REG_ID, TO_CHAR(A.REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , DENSE_RANK() OVER (ORDER BY A.ERR_ID) AS R_NUM , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, B.M_DCU_ID, B.ADDRESS, B.CODN_LAT, B.CODN_LNG, B.TR_FEED_NM DESC) AS P_NUM FROM T_FAC_ERR_CUR_DCU A LEFT JOIN T_FCTY_CS_METER_INFO B ON (A.DCU_ID = B.M_DCU_ID AND A.DEPT1 = B.DEPT1 AND A.DEPT2 = B.DEPT2) WHERE 1 = 1 AND A.AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND P_NUM = 1 AND R_NUM BETWEEN 1 AND 1000
/* 유지관리 작업대상_DCU 송신 상태 변경 */ UPDATE T_FAC_ERR_CUR_DCU SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND ERR_ID IN ( SELECT ERR_ID FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.COMM_CD, TO_CHAR(A.EVENT_START_DT, 'YYYYMMDDHH24MISS') AS EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, TO_CHAR(A.ORDER_DT, 'YYYYMMDDHH24MISS') AS ORDER_DT, A.ORDERER_ID, TO_CHAR(A.METER_DT, 'YYYYMMDDHH24MISS') AS METER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.DCU_ID, A.DCU_IP, A.DURAT_TIME, A.NDIS_WORK_NO, A.NDIS_STAT , B.ADDRESS, B.ROAD_ADDRESS, B.TR_FEED_NM , A.MOD_ID, TO_CHAR(A.MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, A.REG_ID, TO_CHAR(A.REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , DENSE_RANK() OVER (ORDER BY A.ERR_ID) AS R_NUM , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, B.M_DCU_ID, B.ADDRESS, B.CODN_LAT, B.CODN_LNG, B.TR_FEED_NM DESC) AS P_NUM FROM T_FAC_ERR_CUR_DCU A LEFT JOIN T_FCTY_CS_METER_INFO B ON (A.DCU_ID = B.M_DCU_ID AND A.DEPT1 = B.DEPT1 AND A.DEPT2 = B.DEPT2) WHERE 1 = 1 AND A.AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND P_NUM = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 유지관리 작업대상_모뎀 송신 */ SELECT ERR_ID, WORK_STEP, WORK_DIV, FCTY_CD, MODEM_MAC, COMM_CD, EVENT_START_DT, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, BUSI_DIV, M_COMPANY, ERR_TYPE1, ERR_TYPE2, GUM_DAY, ORDER_DT, ORDERER_ID, DCU_DT, METER_DT, SEVER_DT, MEMO, PRE_ERR_ID, WORK_GP_NO, WD_STATE, EMER_YN, MGMT_CD, DESN_ST_YMD, LAY_YMD , METER_ID, DCU_ID, TRADE, COMBLD_NM, TR_FEED_NM, SV_FEED_NM, ADDRESS, ROAD_ADDRESS, INST_S, INST_M , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.MODEM_MAC, A.COMM_CD, A.EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, A.ORDER_DT, A.ORDERER_ID, A.DCU_DT, A.METER_DT, A.SEVER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.WD_STATE, A.EMER_YN, A.MGMT_CD, A.DESN_ST_YMD, A.LAY_YMD , CASE WHEN A.FCTY_CD = 'M' THEN NVL((SELECT TFMI.M_METER_ID FROM T_FCTY_CS_METER_INFO TFMI WHERE TFMI.M_MODEM_MAC = A.MODEM_MAC AND TFMI.DEPT1 = A.DEPT1 AND TFMI.M_USE_YN = 'Y' AND ROWNUM = 1), (SELECT TFMI.M_METER_ID FROM T_FCTY_CS_METER_INFO TFMI WHERE TFMI.M_MODEM_MAC = '012' || SUBSTR(REPLACE(A.MODEM_MAC, '-0', ''), 2) AND TFMI.DEPT1 = A.DEPT1 AND TFMI.M_USE_YN = 'Y' AND ROWNUM = 1)) ELSE A.METER_ID END AS METER_ID , A.DCU_ID , CASE WHEN A.FCTY_CD = 'M' THEN A.TRADE ELSE C.TRADE END AS TRADE , CASE WHEN A.FCTY_CD = 'M' THEN A.COMBLD_NM ELSE C.COMBLD_NM END AS COMBLD_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.TR_FEED_NM ELSE C.TR_FEED_NM END AS TR_FEED_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.SV_FEED_NM ELSE C.SV_FEED_NM END AS SV_FEED_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.ADDRESS ELSE C.ADDRESS END AS ADDRESS , CASE WHEN A.FCTY_CD = 'M' THEN A.ROAD_ADDRESS ELSE C.ROAD_ADDRESS END AS ROAD_ADDRESS , CASE WHEN A.FCTY_CD = 'M' THEN A.INST_S ELSE C.INST_S END AS INST_S , CASE WHEN A.FCTY_CD = 'M' THEN A.INST_M ELSE C.INST_M END AS INST_M , A.MOD_ID, A.MOD_DATE, A.REG_ID, A.REG_DATE , R_NUM, P_NUM_1 , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, C.M_METER_ID, C.ADDRESS, C.TR_FEED_NM, C.TRADE, C.COMBLD_NM, C.SV_FEED_NM, C.INST_S, C.INST_M) AS P_NUM_2 FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.MODEM_MAC, A.COMM_CD, TO_CHAR(A.EVENT_START_DT, 'YYYYMMDDHH24MISS') AS EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, TO_CHAR(A.ORDER_DT, 'YYYYMMDDHH24MISS') AS ORDER_DT, A.ORDERER_ID, TO_CHAR(A.DCU_DT, 'YYYYMMDDHH24MISS') AS DCU_DT, TO_CHAR(A.METER_DT, 'YYYYMMDDHH24MISS') AS METER_DT, TO_CHAR(A.SEVER_DT, 'YYYYMMDDHH24MISS') AS SEVER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.WD_STATE, A.METER_ID, A.EMER_YN, A.MGMT_CD, A.DESN_ST_YMD, A.LAY_YMD , B.DCU_ID , B.TRADE , (SELECT COMBLD_NM FROM T_FCTY_CS_METER_INFO WHERE M_MODEM_MAC = A.MODEM_MAC AND M_USE_YN = 'Y' AND COMBLD_NM IS NOT NULL AND ROWNUM = 1) AS COMBLD_NM , B.TR_FEED_NM, B.SV_FEED_NM, B.ADDRESS, B.ROAD_ADDRESS, B.INST_S, B.INST_M , A.MOD_ID, TO_CHAR(A.MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, A.REG_ID, TO_CHAR(A.REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , DENSE_RANK() OVER (ORDER BY A.ERR_ID) AS R_NUM , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, B.MODEM_MAC, B.ADDRESS, B.TR_FEED_NM, B.TRADE, B.SV_FEED_NM, B.INST_S, B.INST_M) AS P_NUM_1 FROM T_FAC_ERR_CUR_MODEM A LEFT JOIN T_FCTY_CS_MODEM_INFO B ON (A.MODEM_MAC = B.MODEM_MAC AND A.DEPT1 = B.DEPT1) WHERE 1 = 1 AND A.AWMS_SEND_YN = 'N' ) A LEFT JOIN T_FCTY_CS_METER_INFO C ON (A.METER_ID = C.M_METER_ID AND A.DEPT1 = C.DEPT1) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 ) WHERE 1 = 1 AND P_NUM_2 = 1
/* 유지관리 작업대상_모뎀 송신 상태 변경 */ UPDATE T_FAC_ERR_CUR_MODEM SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND ERR_ID IN ( SELECT ERR_ID FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.MODEM_MAC, A.COMM_CD, A.EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, A.ORDER_DT, A.ORDERER_ID, A.DCU_DT, A.METER_DT, A.SEVER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.WD_STATE, A.METER_ID, A.EMER_YN, A.MGMT_CD, A.DESN_ST_YMD, A.LAY_YMD , CASE WHEN A.FCTY_CD = 'M' THEN A.TRADE ELSE C.TRADE END AS TRADE , CASE WHEN A.FCTY_CD = 'M' THEN A.COMBLD_NM ELSE C.COMBLD_NM END AS COMBLD_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.TR_FEED_NM ELSE C.TR_FEED_NM END AS TR_FEED_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.SV_FEED_NM ELSE C.SV_FEED_NM END AS SV_FEED_NM , CASE WHEN A.FCTY_CD = 'M' THEN A.ADDRESS ELSE C.ADDRESS END AS ADDRESS , CASE WHEN A.FCTY_CD = 'M' THEN A.ROAD_ADDRESS ELSE C.ROAD_ADDRESS END AS ROAD_ADDRESS , CASE WHEN A.FCTY_CD = 'M' THEN A.INST_S ELSE C.INST_S END AS INST_S , CASE WHEN A.FCTY_CD = 'M' THEN A.INST_M ELSE C.INST_M END AS INST_M , A.MOD_ID, A.MOD_DATE, A.REG_ID, A.REG_DATE , R_NUM, P_NUM_1 , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, C.M_METER_ID, C.ADDRESS, C.TR_FEED_NM, C.TRADE, C.COMBLD_NM, C.SV_FEED_NM, C.INST_S, C.INST_M) AS P_NUM_2 FROM ( SELECT A.ERR_ID, A.WORK_STEP, A.WORK_DIV, A.FCTY_CD, A.MODEM_MAC, A.COMM_CD, TO_CHAR(A.EVENT_START_DT, 'YYYYMMDDHH24MISS') AS EVENT_START_DT, A.DEPT1, A.DEPT2, A.KDN_DEPT1, A.KDN_DEPT2, A.BUSI_DIV, A.M_COMPANY, A.ERR_TYPE1, A.ERR_TYPE2, A.GUM_DAY, TO_CHAR(A.ORDER_DT, 'YYYYMMDDHH24MISS') AS ORDER_DT, A.ORDERER_ID, TO_CHAR(A.DCU_DT, 'YYYYMMDDHH24MISS') AS DCU_DT, TO_CHAR(A.METER_DT, 'YYYYMMDDHH24MISS') AS METER_DT, TO_CHAR(A.SEVER_DT, 'YYYYMMDDHH24MISS') AS SEVER_DT, A.MEMO, A.PRE_ERR_ID, A.WORK_GP_NO, A.WD_STATE, A.METER_ID, A.EMER_YN, A.MGMT_CD, A.DESN_ST_YMD, A.LAY_YMD , B.TRADE, B.COMBLD_NM, B.TR_FEED_NM, B.SV_FEED_NM, B.ADDRESS, B.ROAD_ADDRESS, B.INST_S, B.INST_M , A.MOD_ID, TO_CHAR(A.MOD_DATE, 'YYYYMMDDHH24MISS') AS MOD_DATE, A.REG_ID, TO_CHAR(A.REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , DENSE_RANK() OVER (ORDER BY A.ERR_ID) AS R_NUM , ROW_NUMBER() OVER (PARTITION BY A.ERR_ID ORDER BY A.ERR_ID, B.M_MODEM_MAC, B.ADDRESS, B.TR_FEED_NM, B.TRADE, B.COMBLD_NM, B.SV_FEED_NM, B.INST_S, B.INST_M) AS P_NUM_1 FROM T_FAC_ERR_CUR_MODEM A LEFT JOIN T_FCTY_CS_METER_INFO B ON (A.FCTY_CD = 'M' AND A.MODEM_MAC = B.M_MODEM_MAC AND A.DEPT1 = B.DEPT1 AND TRIM(B.ADDRESS)||TRIM(B.ROAD_ADDRESS) IS NOT NULL) WHERE 1 = 1 AND A.AWMS_SEND_YN = 'N' ) A LEFT JOIN T_FCTY_CS_METER_INFO C ON (A.METER_ID = C.M_METER_ID AND A.DEPT1 = C.DEPT1) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 ) WHERE 1 = 1 AND P_NUM_2 = 1 )
/* 유지관리 조치내용_DCU 수신 */ DECLARE BEGIN MERGE INTO T_WORK_ACT_DCU USING DUAL ON (ERR_ID = #{ERR_ID}) WHEN NOT MATCHED THEN INSERT ( ERR_ID, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, ACT_FCTY_CD, DCU_ID, ERR_CAUSE1, ERR_CAUSE2, ERR_CAUSE3, ERR_ACTION, NEW_DCU_ID, CHBD_DCU_YN, CHBD_TDU_YN, CHBD_PLC_YN, CHBD_FRU_YN, CHBD_EXT_YN, MAT1_CD, MAT1_PRICE, MAT1_CNT, MAT2_CD, MAT2_PRICE, MAT2_CNT, MAT3_CD, MAT3_PRICE, MAT3_CNT, MAT4_CD, MAT4_PRICE, MAT4_CNT, MAT5_CD, MAT5_PRICE, MAT5_CNT, SV_SEQ, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, REPAIR_DETAIL, REWORK_YN, ACT_DATE, ATCH_FILE_ID1, ATCH_FILE_ID2, ATCH_FILE_ID3, ATCH_FILE_ID4, NEW_DCU_IP, WORKER1_DIV, WORKER2_DIV, WORKER3_DIV, EXT_DCU_MAC, NEW_DCU_MAC , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{ERR_ID}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{ACT_FCTY_CD}, #{DCU_ID}, #{ERR_CAUSE1}, #{ERR_CAUSE2}, #{ERR_CAUSE3}, #{ERR_ACTION}, #{NEW_DCU_ID}, #{CHBD_DCU_YN}, #{CHBD_TDU_YN}, #{CHBD_PLC_YN}, #{CHBD_FRU_YN}, #{CHBD_EXT_YN}, #{MAT1_CD}, #{MAT1_PRICE}, #{MAT1_CNT}, #{MAT2_CD}, #{MAT2_PRICE}, #{MAT2_CNT}, #{MAT3_CD}, #{MAT3_PRICE}, #{MAT3_CNT}, #{MAT4_CD}, #{MAT4_PRICE}, #{MAT4_CNT}, #{MAT5_CD}, #{MAT5_PRICE}, #{MAT5_CNT}, #{SV_SEQ}, #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{REPAIR_DETAIL}, NVL(#{REWORK_YN}, 'N'), TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS'), #{ATCH_FILE_ID1}, #{ATCH_FILE_ID2}, #{ATCH_FILE_ID3}, #{ATCH_FILE_ID4}, #{NEW_DCU_IP}, #{WORKER1_DIV}, #{WORKER2_DIV}, #{WORKER3_DIV}, #{EXT_DCU_MAC}, #{NEW_DCU_MAC} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET DEPT1 = #{DEPT1} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , ACT_FCTY_CD = #{ACT_FCTY_CD} , DCU_ID = #{DCU_ID} , ERR_CAUSE1 = #{ERR_CAUSE1} , ERR_CAUSE2 = #{ERR_CAUSE2} , ERR_CAUSE3 = #{ERR_CAUSE3} , ERR_ACTION = #{ERR_ACTION} , NEW_DCU_ID = #{NEW_DCU_ID} , CHBD_DCU_YN = #{CHBD_DCU_YN} , CHBD_TDU_YN = #{CHBD_TDU_YN} , CHBD_PLC_YN = #{CHBD_PLC_YN} , CHBD_FRU_YN = #{CHBD_FRU_YN} , CHBD_EXT_YN = #{CHBD_EXT_YN} , MAT1_CD = #{MAT1_CD} , MAT1_PRICE = #{MAT1_PRICE} , MAT1_CNT = #{MAT1_CNT} , MAT2_CD = #{MAT2_CD} , MAT2_PRICE = #{MAT2_PRICE} , MAT2_CNT = #{MAT2_CNT} , MAT3_CD = #{MAT3_CD} , MAT3_PRICE = #{MAT3_PRICE} , MAT3_CNT = #{MAT3_CNT} , MAT4_CD = #{MAT4_CD} , MAT4_PRICE = #{MAT4_PRICE} , MAT4_CNT = #{MAT4_CNT} , MAT5_CD = #{MAT5_CD} , MAT5_PRICE = #{MAT5_PRICE} , MAT5_CNT = #{MAT5_CNT} , REPAIR_DATE = TO_DATE(#{REPAIR_DATE}, 'YYYYMMDDHH24MISS') , SV_SEQ = #{SV_SEQ} , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , REPAIR_DETAIL = #{REPAIR_DETAIL} , REWORK_YN = NVL(#{REWORK_YN}, 'N') , ACT_DATE = TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS') , ATCH_FILE_ID1 = #{ATCH_FILE_ID1} , ATCH_FILE_ID2 = #{ATCH_FILE_ID2} , ATCH_FILE_ID3 = #{ATCH_FILE_ID3} , ATCH_FILE_ID4 = #{ATCH_FILE_ID4} , NEW_DCU_IP = #{NEW_DCU_IP} , WORKER1_DIV = #{WORKER1_DIV} , WORKER2_DIV = #{WORKER2_DIV} , WORKER3_DIV = #{WORKER3_DIV} , EXT_DCU_MAC = #{EXT_DCU_MAC} , NEW_DCU_MAC = #{NEW_DCU_MAC} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) WHERE EXISTS (SELECT ERR_ID FROM T_FAC_ERR_CUR_DCU WHERE WORK_STEP IN ('20','50') AND ERR_ID = #{ERR_ID}) ; UPDATE T_FAC_ERR_CUR_DCU SET WORK_STEP = '50' WHERE ERR_ID = #{ERR_ID} AND WORK_STEP IN ('20','50') ; MERGE INTO T_MEMO USING DUAL ON (MEMO_DIV = 'AD' AND TARGET_ID = #{ERR_ID}) WHEN NOT MATCHED THEN INSERT ( MEMO_DIV, TARGET_ID, MEMO_CONTENT , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( 'AD', #{ERR_ID}, #{MEMO} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET MEMO_CONTENT = #{MEMO} ; END;
/* 유지관리 조치내용_모뎀 수신 */ DECLARE V_MEMO_DIV VARCHAR2(2); V_FCTY_CD VARCHAR2(2); BEGIN MERGE INTO T_WORK_ACT_MODEM USING DUAL ON (ERR_ID = #{ERR_ID}) WHEN NOT MATCHED THEN INSERT ( ERR_ID, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, ACT_FCTY_CD, MODEM_MAC, ERR_CAUSE1, ERR_CAUSE2, ERR_CAUSE3, ERR_ACTION, NEW_MODEM_MAC, NEW_DCU_ID, MAT1_CD, MAT1_PRICE, MAT1_CNT, MAT2_CD, MAT2_PRICE, MAT2_CNT, MAT3_CD, MAT3_PRICE, MAT3_CNT, MAT4_CD, MAT4_PRICE, MAT4_CNT, MAT5_CD, MAT5_PRICE, MAT5_CNT, SV_SEQ, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, WORKER1_DIV, WORKER2_DIV, WORKER3_DIV, REPAIR_DETAIL, REWORK_YN, ACT_DATE, ATCH_FILE_ID1, ATCH_FILE_ID2, ATCH_FILE_ID3, ATCH_FILE_ID4, METER_SEAL1, METER_SEAL2, BOX_SEAL1, BOX_SEAL2, METER_ID, MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{ERR_ID}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{ACT_FCTY_CD}, #{MODEM_MAC}, #{ERR_CAUSE1}, #{ERR_CAUSE2}, #{ERR_CAUSE3}, #{ERR_ACTION}, #{NEW_MODEM_MAC}, #{NEW_DCU_ID}, #{MAT1_CD}, #{MAT1_PRICE}, #{MAT1_CNT}, #{MAT2_CD}, #{MAT2_PRICE}, #{MAT2_CNT}, #{MAT3_CD}, #{MAT3_PRICE}, #{MAT3_CNT}, #{MAT4_CD}, #{MAT4_PRICE}, #{MAT4_CNT}, #{MAT5_CD}, #{MAT5_PRICE}, #{MAT5_CNT}, #{SV_SEQ}, #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{WORKER1_DIV}, #{WORKER2_DIV}, #{WORKER3_DIV}, #{REPAIR_DETAIL}, NVL(#{REWORK_YN}, 'N'), TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS'), #{ATCH_FILE_ID1}, #{ATCH_FILE_ID2}, #{ATCH_FILE_ID3}, #{ATCH_FILE_ID4}, #{METER_SEAL1}, #{METER_SEAL2}, #{BOX_SEAL1}, #{BOX_SEAL2}, #{METER_ID}, NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET DEPT1 = #{DEPT1} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , ACT_FCTY_CD = #{ACT_FCTY_CD} , MODEM_MAC = #{MODEM_MAC} , ERR_CAUSE1 = #{ERR_CAUSE1} , ERR_CAUSE2 = #{ERR_CAUSE2} , ERR_CAUSE3 = #{ERR_CAUSE3} , ERR_ACTION = #{ERR_ACTION} , NEW_MODEM_MAC = #{NEW_MODEM_MAC} , NEW_DCU_ID = #{NEW_DCU_ID} , MAT1_CD = #{MAT1_CD} , MAT1_PRICE = #{MAT1_PRICE} , MAT1_CNT = #{MAT1_CNT} , MAT2_CD = #{MAT2_CD} , MAT2_PRICE = #{MAT2_PRICE} , MAT2_CNT = #{MAT2_CNT} , MAT3_CD = #{MAT3_CD} , MAT3_PRICE = #{MAT3_PRICE} , MAT3_CNT = #{MAT3_CNT} , MAT4_CD = #{MAT4_CD} , MAT4_PRICE = #{MAT4_PRICE} , MAT4_CNT = #{MAT4_CNT} , MAT5_CD = #{MAT5_CD} , MAT5_PRICE = #{MAT5_PRICE} , MAT5_CNT = #{MAT5_CNT} , REPAIR_DATE = TO_DATE(#{REPAIR_DATE}, 'YYYYMMDDHH24MISS') , SV_SEQ = #{SV_SEQ} , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , REPAIR_DETAIL = #{REPAIR_DETAIL} , REWORK_YN = NVL(#{REWORK_YN}, 'N') , ACT_DATE = TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS') , ATCH_FILE_ID1 = #{ATCH_FILE_ID1} , ATCH_FILE_ID2 = #{ATCH_FILE_ID2} , ATCH_FILE_ID3 = #{ATCH_FILE_ID3} , ATCH_FILE_ID4 = #{ATCH_FILE_ID4} , METER_SEAL1 = #{METER_SEAL1} , METER_SEAL2 = #{METER_SEAL2} , BOX_SEAL1 = #{BOX_SEAL1} , BOX_SEAL2 = #{BOX_SEAL2} , METER_ID = #{METER_ID} , WORKER1_DIV = #{WORKER1_DIV} , WORKER2_DIV = #{WORKER2_DIV} , WORKER3_DIV = #{WORKER3_DIV} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) WHERE EXISTS (SELECT ERR_ID FROM T_FAC_ERR_CUR_MODEM WHERE WORK_STEP IN ('20','50') AND ERR_ID = #{ERR_ID}) ; UPDATE T_FAC_ERR_CUR_MODEM SET WORK_STEP = '50' WHERE ERR_ID = #{ERR_ID} AND WORK_STEP IN ('20','50') ; SELECT MAX(FCTY_CD) INTO V_FCTY_CD FROM T_FAC_ERR_CUR_MODEM WHERE ERR_ID = #{ERR_ID} ; IF V_FCTY_CD = 'M' THEN V_MEMO_DIV := 'AM'; ELSE V_MEMO_DIV := 'AT'; END IF; MERGE INTO T_MEMO USING DUAL ON (MEMO_DIV = V_MEMO_DIV AND TARGET_ID = #{ERR_ID}) WHEN NOT MATCHED THEN INSERT ( MEMO_DIV, TARGET_ID, MEMO_CONTENT , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( V_MEMO_DIV, #{ERR_ID}, #{MEMO} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET MEMO_CONTENT = #{MEMO} ; END;
/* 유지관리 기타 조치_DCU 수신 */ MERGE INTO T_ETC_ACT_DCU USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, WORK_STEP, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, ACT_FCTY_CD, DATA_NUM, DCU_ID, ERR_CAUSE1, ERR_CAUSE2, ERR_CAUSE3, ERR_ACTION, NEW_DCU_ID, CHBD_DCU_YN, CHBD_TDU_YN, CHBD_PLC_YN, CHBD_FRU_YN, CHBD_EXT_YN, MAT1_CD, MAT1_PRICE, MAT1_CNT, MAT2_CD, MAT2_PRICE, MAT2_CNT, MAT3_CD, MAT3_PRICE, MAT3_CNT, MAT4_CD, MAT4_PRICE, MAT4_CNT, MAT5_CD, MAT5_PRICE, MAT5_CNT, SV_SEQ, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, REPAIR_DETAIL, ACT_DATE, ATCH_FILE_ID1, ATCH_FILE_ID2, ATCH_FILE_ID3, ATCH_FILE_ID4, NEW_DCU_IP, WORKER1_DIV, WORKER2_DIV, WORKER3_DIV, ACT_DETAIL, ADDRESS, ROAD_ADDRESS, EXT_DCU_MAC, NEW_DCU_MAC , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{WORK_STEP}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{ACT_FCTY_CD}, #{DATA_NUM}, #{DCU_ID}, #{ERR_CAUSE1}, #{ERR_CAUSE2}, #{ERR_CAUSE3}, #{ERR_ACTION}, #{NEW_DCU_ID}, #{CHBD_DCU_YN}, #{CHBD_TDU_YN}, #{CHBD_PLC_YN}, #{CHBD_FRU_YN}, #{CHBD_EXT_YN}, #{MAT1_CD}, #{MAT1_PRICE}, #{MAT1_CNT}, #{MAT2_CD}, #{MAT2_PRICE}, #{MAT2_CNT}, #{MAT3_CD}, #{MAT3_PRICE}, #{MAT3_CNT}, #{MAT4_CD}, #{MAT4_PRICE}, #{MAT4_CNT}, #{MAT5_CD}, #{MAT5_PRICE}, #{MAT5_CNT}, #{SV_SEQ}, #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, #{REPAIR_DETAIL}, TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS'), #{ATCH_FILE_ID1}, #{ATCH_FILE_ID2}, #{ATCH_FILE_ID3}, #{ATCH_FILE_ID4}, #{NEW_DCU_IP}, #{WORKER1_DIV}, #{WORKER2_DIV}, #{WORKER3_DIV}, #{ACT_DETAIL}, #{ADDRESS}, #{ROAD_ADDRESS}, #{EXT_DCU_MAC}, #{NEW_DCU_MAC} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET WORK_STEP = #{WORK_STEP} , DEPT1 = #{DEPT1} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , ACT_FCTY_CD = #{ACT_FCTY_CD} , DATA_NUM = #{DATA_NUM} , DCU_ID = #{DCU_ID} , ERR_CAUSE1 = #{ERR_CAUSE1} , ERR_CAUSE2 = #{ERR_CAUSE2} , ERR_CAUSE3 = #{ERR_CAUSE3} , ERR_ACTION = #{ERR_ACTION} , NEW_DCU_ID = #{NEW_DCU_ID} , CHBD_DCU_YN = #{CHBD_DCU_YN} , CHBD_TDU_YN = #{CHBD_TDU_YN} , CHBD_PLC_YN = #{CHBD_PLC_YN} , CHBD_FRU_YN = #{CHBD_FRU_YN} , CHBD_EXT_YN = #{CHBD_EXT_YN} , MAT1_CD = #{MAT1_CD} , MAT1_PRICE = #{MAT1_PRICE} , MAT1_CNT = #{MAT1_CNT} , MAT2_CD = #{MAT2_CD} , MAT2_PRICE = #{MAT2_PRICE} , MAT2_CNT = #{MAT2_CNT} , MAT3_CD = #{MAT3_CD} , MAT3_PRICE = #{MAT3_PRICE} , MAT3_CNT = #{MAT3_CNT} , MAT4_CD = #{MAT4_CD} , MAT4_PRICE = #{MAT4_PRICE} , MAT4_CNT = #{MAT4_CNT} , MAT5_CD = #{MAT5_CD} , MAT5_PRICE = #{MAT5_PRICE} , MAT5_CNT = #{MAT5_CNT} , SV_SEQ = #{SV_SEQ} , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , REPAIR_DETAIL = #{REPAIR_DETAIL} , ACT_DATE = TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS') , ATCH_FILE_ID1 = #{ATCH_FILE_ID1} , ATCH_FILE_ID2 = #{ATCH_FILE_ID2} , ATCH_FILE_ID3 = #{ATCH_FILE_ID3} , ATCH_FILE_ID4 = #{ATCH_FILE_ID4} , NEW_DCU_IP = #{NEW_DCU_IP} , WORKER1_DIV = #{WORKER1_DIV} , WORKER2_DIV = #{WORKER2_DIV} , WORKER3_DIV = #{WORKER3_DIV} , ACT_DETAIL = #{ACT_DETAIL} , ADDRESS = #{ADDRESS} , ROAD_ADDRESS = #{ROAD_ADDRESS} , EXT_DCU_MAC = #{EXT_DCU_MAC} , NEW_DCU_MAC = #{NEW_DCU_MAC} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 유지관리 기타 조치_모뎀계기 수신 */ MERGE INTO T_ETC_ACT_MODEM USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, WORK_STEP, DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2, ACT_FCTY_CD, MODEM_MAC, ERR_CAUSE1, ERR_CAUSE2, ERR_CAUSE3, ERR_ACTION, NEW_MODEM_MAC, NEW_DCU_ID, MAT1_CD, MAT1_PRICE, MAT1_CNT, MAT2_CD, MAT2_PRICE, MAT2_CNT, MAT3_CD, MAT3_PRICE, MAT3_CNT, MAT4_CD, MAT4_PRICE, MAT4_CNT, MAT5_CD, MAT5_PRICE, MAT5_CNT, SV_SEQ, WORKER1_SEQ, WORKER2_SEQ, WORKER3_SEQ, ACT_DATE, ATCH_FILE_ID1, ATCH_FILE_ID2, ATCH_FILE_ID3, ATCH_FILE_ID4, METER_SEAL1, METER_SEAL2, BOX_SEAL1, BOX_SEAL2, METER_ID, M_METER_ID, MEMO, ACT_DETAIL, REPAIR_DETAIL, ADDRESS, ROAD_ADDRESS , MOD_ID, MOD_DATE, REG_ID, REG_DATE ) VALUES ( #{SEQ}, #{WORK_STEP}, #{DEPT1}, #{DEPT2}, #{KDN_DEPT1}, #{KDN_DEPT2}, #{ACT_FCTY_CD}, #{MODEM_MAC}, #{ERR_CAUSE1}, #{ERR_CAUSE2}, #{ERR_CAUSE3}, #{ERR_ACTION}, #{NEW_MODEM_MAC}, #{NEW_DCU_ID}, #{MAT1_CD}, #{MAT1_PRICE}, #{MAT1_CNT}, #{MAT2_CD}, #{MAT2_PRICE}, #{MAT2_CNT}, #{MAT3_CD}, #{MAT3_PRICE}, #{MAT3_CNT}, #{MAT4_CD}, #{MAT4_PRICE}, #{MAT4_CNT}, #{MAT5_CD}, #{MAT5_PRICE}, #{MAT5_CNT}, #{SV_SEQ}, #{WORKER1_SEQ}, #{WORKER2_SEQ}, #{WORKER3_SEQ}, TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS'), #{ATCH_FILE_ID1}, #{ATCH_FILE_ID2}, #{ATCH_FILE_ID3}, #{ATCH_FILE_ID4}, #{METER_SEAL1}, #{METER_SEAL2}, #{BOX_SEAL1}, #{BOX_SEAL2}, #{METER_ID}, #{M_METER_ID}, #{MEMO}, #{ACT_DETAIL}, #{REPAIR_DETAIL}, #{ADDRESS}, #{ROAD_ADDRESS} , NVL(#{MOD_ID}, 'DFXAGENT_USER'), DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), NVL(#{REG_ID}, 'DFXAGENT_USER'), DECODE(#{REG_DATE}, NULL, SYSDATE, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS')) ) WHEN MATCHED THEN UPDATE SET WORK_STEP = #{WORK_STEP} , DEPT1 = #{DEPT1} , DEPT2 = #{DEPT2} , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , ACT_FCTY_CD = #{ACT_FCTY_CD} , MODEM_MAC = #{MODEM_MAC} , ERR_CAUSE1 = #{ERR_CAUSE1} , ERR_CAUSE2 = #{ERR_CAUSE2} , ERR_CAUSE3 = #{ERR_CAUSE3} , ERR_ACTION = #{ERR_ACTION} , NEW_MODEM_MAC = #{NEW_MODEM_MAC} , NEW_DCU_ID = #{NEW_DCU_ID} , MAT1_CD = #{MAT1_CD} , MAT1_PRICE = #{MAT1_PRICE} , MAT1_CNT = #{MAT1_CNT} , MAT2_CD = #{MAT2_CD} , MAT2_PRICE = #{MAT2_PRICE} , MAT2_CNT = #{MAT2_CNT} , MAT3_CD = #{MAT3_CD} , MAT3_PRICE = #{MAT3_PRICE} , MAT3_CNT = #{MAT3_CNT} , MAT4_CD = #{MAT4_CD} , MAT4_PRICE = #{MAT4_PRICE} , MAT4_CNT = #{MAT4_CNT} , MAT5_CD = #{MAT5_CD} , MAT5_PRICE = #{MAT5_PRICE} , MAT5_CNT = #{MAT5_CNT} , SV_SEQ = #{SV_SEQ} , WORKER1_SEQ = #{WORKER1_SEQ} , WORKER2_SEQ = #{WORKER2_SEQ} , WORKER3_SEQ = #{WORKER3_SEQ} , ACT_DATE = TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MISS') , ATCH_FILE_ID1 = #{ATCH_FILE_ID1} , ATCH_FILE_ID2 = #{ATCH_FILE_ID2} , ATCH_FILE_ID3 = #{ATCH_FILE_ID3} , ATCH_FILE_ID4 = #{ATCH_FILE_ID4} , METER_SEAL1 = #{METER_SEAL1} , METER_SEAL2 = #{METER_SEAL2} , BOX_SEAL1 = #{BOX_SEAL1} , BOX_SEAL2 = #{BOX_SEAL2} , METER_ID = #{METER_ID} , M_METER_ID = #{M_METER_ID} , MEMO = #{MEMO} , ACT_DETAIL = #{ACT_DETAIL} , REPAIR_DETAIL = #{REPAIR_DETAIL} , ADDRESS = #{ADDRESS} , ROAD_ADDRESS = #{ROAD_ADDRESS} , MOD_ID = NVL(#{MOD_ID}, 'DFXAGENT_USER') , MOD_DATE = DECODE(#{MOD_DATE}, NULL, SYSDATE, TO_DATE(#{MOD_DATE}, 'YYYYMMDDHH24MISS'))
/* 유지관리 조치내용_노임품목 수신 */ MERGE INTO T_WORK_ACT_SVC_CD USING DUAL ON (ERR_ID = #{ERR_ID} AND SVC_CD = #{SVC_CD}) WHEN NOT MATCHED THEN INSERT ( ERR_ID, SVC_CD, SVC_CNT ) VALUES ( #{ERR_ID}, #{SVC_CD}, #{SVC_CNT} )
/* 유지관리 위험불가개소 송신 */ SELECT DEPT1, DEPT2, FCTY_ID, FCTY_TYPE, PHASE3, MORE10, ETC, MEMO, GOSO_LOCA, CORPS, NEAR_ROAD, LONG_DIS, TOUGH_ROAD, MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT DEPT1, DEPT2, FCTY_ID, FCTY_TYPE, PHASE3, MORE10, ETC, MEMO, GOSO_LOCA, CORPS, NEAR_ROAD, LONG_DIS, TOUGH_ROAD, NULL AS MOD_ID, TO_CHAR(UPDATE_DT, 'YYYYMMDDHH24MISS') AS MOD_DATE, NULL AS REG_ID, TO_CHAR(REG_DT, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, FCTY_ID) AS R_NUM FROM T_FCTY_DANGER_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 유지관리 위험불가개소 송신 상태 변경 */ UPDATE T_FCTY_DANGER_INFO SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND (DEPT1, FCTY_ID) IN ( SELECT DEPT1, FCTY_ID FROM ( SELECT DEPT1, DEPT2, FCTY_ID, FCTY_TYPE, PHASE3, MORE10, ETC, MEMO, GOSO_LOCA, CORPS, NEAR_ROAD, LONG_DIS, TOUGH_ROAD, NULL AS MOD_ID, TO_CHAR(UPDATE_DT, 'YYYYMMDDHH24MISS') AS MOD_DATE, NULL AS REG_ID, TO_CHAR(REG_DT, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY DEPT1, FCTY_ID) AS R_NUM FROM T_FCTY_DANGER_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 유지관리 계기장애처리완료 송신 */ SELECT ERR_ID, DEPT1, DEPT2, NEW_FCTY_YN, EVENT_END_DT, MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT ERR_ID, DEPT1, DEPT2, NEW_FCTY_YN, TO_CHAR(EVENT_END_DT, 'YYYYMMDDHH24MISS') AS EVENT_END_DT, NULL AS MOD_ID, NULL AS MOD_DATE, NULL AS REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ERR_ID) AS R_NUM FROM T_FAC_ERR_CLR WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 유지관리 계기장애처리완료 송신 상태 변경 */ UPDATE T_FAC_ERR_CLR SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND ERR_ID IN ( SELECT ERR_ID FROM ( SELECT ERR_ID, DEPT1, DEPT2, NEW_FCTY_YN, TO_CHAR(EVENT_END_DT, 'YYYYMMDDHH24MISS') AS EVENT_END_DT, NULL AS MOD_ID, NULL AS MOD_DATE, NULL AS REG_ID, TO_CHAR(REG_DATE, 'YYYYMMDDHH24MISS') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY ERR_ID) AS R_NUM FROM T_FAC_ERR_CLR WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 유지관리 설비계기정보 송신 */ SELECT M_METER_ID, M_CREATE_DATE, M_REMOVE_DATE, M_USE_YN, M_DCU_ID, M_MODEM_MAC, M_MRD, M_UPDATE_DATE, METER_TYPE, CUST_NO, CUST_CATEG, CTNR_STAT, CTNR_PWR, COMBLD_NM, DEPT1, DEPT2, DCU_IP, BUSI_DIV, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, MANF_YM, DLVP_YM, CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, ATCH_FILE_ID, ATCH_FILE_ID2, ETC, STATE_CHANGE, MATCH_YN, EXCPT, M_COMPANY, FAC_DIV, CODN_LAT, CODN_LNG, REVERSE_YN, EXCEPT_DIV, IU_DIV, DESN_ST, SV_FEED, TRADE, GUM_DAY, MASTER_YN, TR_FEED_NM, SV_FEED_NM, TR_FEED_NO, SV_FEED_NO, BUILD_CD, MGMT_CD, COMM_CD, ATNT_FCTY_YN, ACT_BUSI_DIV_NM, FCTY_CMNT , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT M_METER_ID, TO_CHAR(M_CREATE_DATE, 'YYYYMMDDHH24MISS') AS M_CREATE_DATE, TO_CHAR(M_REMOVE_DATE, 'YYYYMMDDHH24MISS') AS M_REMOVE_DATE, M_USE_YN, M_DCU_ID, M_MODEM_MAC, M_MRD, TO_CHAR(M_UPDATE_DATE, 'YYYYMMDDHH24MISS') AS M_UPDATE_DATE, METER_TYPE, CUST_NO, CUST_CATEG, CTNR_STAT, CTNR_PWR, COMBLD_NM, DEPT1, DEPT2, DCU_IP, BUSI_DIV, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, MANF_YM, DLVP_YM, TO_CHAR(CONSIGN_DATE, 'YYYYMMDDHH24MISS') AS CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, ATCH_FILE_ID, ATCH_FILE_ID2, DBMS_LOB.SUBSTR(ETC, 4000, 1) AS ETC, STATE_CHANGE, MATCH_YN, EXCPT, M_COMPANY, FAC_DIV, CODN_LAT, CODN_LNG, REVERSE_YN, EXCEPT_DIV, IU_DIV, TO_CHAR(DESN_ST, 'YYYYMMDDHH24MISS') AS DESN_ST, SV_FEED, TRADE, GUM_DAY, MASTER_YN, TR_FEED_NM, SV_FEED_NM, TR_FEED_NO, SV_FEED_NO, BUILD_CD, MGMT_CD, COMM_CD, ATNT_FCTY_YN, ACT_BUSI_DIV_NM, FCTY_CMNT , 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, M_METER_ID) AS R_NUM FROM T_FCTY_CS_METER_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 유지관리 설비계기정보 송신 상태 변경 */ UPDATE T_FCTY_CS_METER_INFO SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND (DEPT1, M_METER_ID) IN ( SELECT DEPT1, M_METER_ID FROM ( SELECT M_METER_ID, TO_CHAR(M_CREATE_DATE, 'YYYYMMDDHH24MISS') AS M_CREATE_DATE, TO_CHAR(M_REMOVE_DATE, 'YYYYMMDDHH24MISS') AS M_REMOVE_DATE, M_USE_YN, M_DCU_ID, M_MODEM_MAC, M_MRD, TO_CHAR(M_UPDATE_DATE, 'YYYYMMDDHH24MISS') AS M_UPDATE_DATE, METER_TYPE, CUST_NO, CUST_CATEG, CTNR_STAT, CTNR_PWR, COMBLD_NM, DEPT1, DEPT2, DCU_IP, BUSI_DIV, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, MANF_YM, DLVP_YM, TO_CHAR(CONSIGN_DATE, 'YYYYMMDDHH24MISS') AS CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, ATCH_FILE_ID, ATCH_FILE_ID2, DBMS_LOB.SUBSTR(ETC, 4000, 1) AS ETC, STATE_CHANGE, MATCH_YN, EXCPT, M_COMPANY, FAC_DIV, CODN_LAT, CODN_LNG, REVERSE_YN, EXCEPT_DIV, IU_DIV, TO_CHAR(DESN_ST, 'YYYYMMDDHH24MISS') AS DESN_ST, SV_FEED, TRADE, GUM_DAY, MASTER_YN, TR_FEED_NM, SV_FEED_NM, TR_FEED_NO, SV_FEED_NO, BUILD_CD, MGMT_CD, COMM_CD, ATNT_FCTY_YN, ACT_BUSI_DIV_NM, FCTY_CMNT , 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, M_METER_ID) AS R_NUM FROM T_FCTY_CS_METER_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 유지관리 설비DCU정보 송신 */ SELECT DCU_ID, DCU_PUBLIC_IP, DCU_USE_YN, DCU_CREATE_DATE, DCU_UPDATE_DATE, BUSI_DIV, DCU_REMOVE_DATE, TRS_FEED_NO, TRS_POLE_NO, TRS_LATITUDE, TRS_LONGITUDE, M_COMPANY, MANF_YM, DLVP_YM, NEW_MAT_NO, DEPT1, DEPT2, FAC_DIV, MODEM_CNT, METER_CNT, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, STATE_CHANGE, B_USER, U_DCU_IP, D_MODEM_MAC, IU_DIV, GN_NAME, DCU_SERIAL, FW_BUILD_VER, BUILD_CD, N_MGMT_CD, N_MGMT_DT, BOARD_MAC, MASTER_YN, TDU_YN, TDU_PHASE, TRS_DIV, NET_MAC, NET_GW, NET_COM, NET_MASK, NET_CD, NET_CNTR_ID, COMM_CD, FCTY_CMNT , MOD_ID, MOD_DATE, REG_ID, REG_DATE FROM ( SELECT DCU_ID, DCU_PUBLIC_IP, DCU_USE_YN, TO_CHAR(DCU_CREATE_DATE, 'YYYYMMDDHH24MISS') AS DCU_CREATE_DATE, TO_CHAR(DCU_UPDATE_DATE, 'YYYYMMDDHH24MISS') AS DCU_UPDATE_DATE, BUSI_DIV, TO_CHAR(DCU_REMOVE_DATE, 'YYYYMMDDHH24MISS') AS DCU_REMOVE_DATE, TRS_FEED_NO, TRS_POLE_NO, TRS_LATITUDE, TRS_LONGITUDE, M_COMPANY, MANF_YM, DLVP_YM, NEW_MAT_NO, DEPT1, DEPT2, FAC_DIV, MODEM_CNT, METER_CNT, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, TO_CHAR(CONSIGN_DATE, 'YYYYMMDDHH24MISS') AS CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, STATE_CHANGE, B_USER, U_DCU_IP, D_MODEM_MAC, IU_DIV, GN_NAME, DCU_SERIAL, FW_BUILD_VER, BUILD_CD, N_MGMT_CD, TO_CHAR(N_MGMT_DT, 'YYYYMMDDHH24MISS') AS N_MGMT_DT, BOARD_MAC, MASTER_YN, TDU_YN, TDU_PHASE, TRS_DIV, NET_MAC, NET_GW, NET_COM, NET_MASK, NET_CD, NET_CNTR_ID, COMM_CD, FCTY_CMNT , 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, DCU_ID) AS R_NUM FROM T_FCTY_DCU_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 유지관리 설비DCU정보 송신 상태 변경 */ UPDATE T_FCTY_DCU_INFO SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND (DEPT1, DCU_ID) IN ( SELECT DEPT1, DCU_ID FROM ( SELECT DCU_ID, DCU_PUBLIC_IP, DCU_USE_YN, TO_CHAR(DCU_CREATE_DATE, 'YYYYMMDDHH24MISS') AS DCU_CREATE_DATE, TO_CHAR(DCU_UPDATE_DATE, 'YYYYMMDDHH24MISS') AS DCU_UPDATE_DATE, BUSI_DIV, TO_CHAR(DCU_REMOVE_DATE, 'YYYYMMDDHH24MISS') AS DCU_REMOVE_DATE, TRS_FEED_NO, TRS_POLE_NO, TRS_LATITUDE, TRS_LONGITUDE, M_COMPANY, MANF_YM, DLVP_YM, NEW_MAT_NO, DEPT1, DEPT2, FAC_DIV, MODEM_CNT, METER_CNT, KDN_DEPT1, KDN_DEPT2, INST_DIV, INST_L, INST_M, INST_S, CONSIGN_YN, DEFECT_YN, TO_CHAR(CONSIGN_DATE, 'YYYYMMDDHH24MISS') AS CONSIGN_DATE, ROAD_ADDRESS, ADDRESS, STATE_CHANGE, B_USER, U_DCU_IP, D_MODEM_MAC, IU_DIV, GN_NAME, DCU_SERIAL, FW_BUILD_VER, BUILD_CD, N_MGMT_CD, TO_CHAR(N_MGMT_DT, 'YYYYMMDDHH24MISS') AS N_MGMT_DT, BOARD_MAC, MASTER_YN, TDU_YN, TDU_PHASE, TRS_DIV, NET_MAC, NET_GW, NET_COM, NET_MASK, NET_CD, NET_CNTR_ID, COMM_CD, FCTY_CMNT , 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, DCU_ID) AS R_NUM FROM T_FCTY_DCU_INFO WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )