/* 임시조치 전월검침정보(데이터) 송신 */ SELECT SEQ, DEPT1, DEPT2, WHM_NO, CNTR_NO, RECIEVE_YM, GUM_DAY, LST_WHME_NDL_DGTS, LST_MDME_NDL_DGTS, LST_VAR_NDL_DGTS, LST_WHME_NDL_DAY_QTT, LST_DM_MT_NDL_DAY_QTT, LST_VAR_NDL_DAY_QTT, LST_WHME_NDL_EVEN_QTT, LST_DM_MT_NDL_EVEN_QTT, LST_VAR_NDL_EVEN_QTT, LST_WHME_NDL_MNGT_QTT, LST_DM_MT_NDL_MNGT_QTT, LST_VAR_NDL_MNGT_QTT, REG_DATE FROM ( SELECT SEQ, DEPT1, DEPT2, WHM_NO, CNTR_NO, RECIEVE_YM, GUM_DAY, LST_WHME_NDL_DGTS, LST_MDME_NDL_DGTS, LST_VAR_NDL_DGTS, LST_WHME_NDL_DAY_QTT, LST_DM_MT_NDL_DAY_QTT, LST_VAR_NDL_DAY_QTT, LST_WHME_NDL_EVEN_QTT, LST_DM_MT_NDL_EVEN_QTT, LST_VAR_NDL_EVEN_QTT, LST_WHME_NDL_MNGT_QTT, LST_DM_MT_NDL_MNGT_QTT, LST_VAR_NDL_MNGT_QTT, TO_CHAR(REG_DATE, 'YYYYMMDD') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY SEQ) AS R_NUM FROM T_METER_NDL_LST WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000
/* 임시조치 전월검침정보(데이터) 송신 상태 변경 */ UPDATE T_METER_NDL_LST SET AWMS_SEND_YN = 'Y' , AWMS_SEND_DATE = SYSDATE WHERE 1 = 1 AND SEQ IN ( SELECT SEQ FROM ( SELECT SEQ, DEPT1, DEPT2, WHM_NO, CNTR_NO, RECIEVE_YM, GUM_DAY, LST_WHME_NDL_DGTS, LST_MDME_NDL_DGTS, LST_VAR_NDL_DGTS, LST_WHME_NDL_DAY_QTT, LST_DM_MT_NDL_DAY_QTT, LST_VAR_NDL_DAY_QTT, LST_WHME_NDL_EVEN_QTT, LST_DM_MT_NDL_EVEN_QTT, LST_VAR_NDL_EVEN_QTT, LST_WHME_NDL_MNGT_QTT, LST_DM_MT_NDL_MNGT_QTT, LST_VAR_NDL_MNGT_QTT, TO_CHAR(REG_DATE, 'YYYYMMDD') AS REG_DATE , ROW_NUMBER() OVER (ORDER BY SEQ) AS R_NUM FROM T_METER_NDL_LST WHERE 1 = 1 AND AWMS_SEND_YN = 'N' ) WHERE 1 = 1 AND R_NUM BETWEEN 1 AND 1000 )
/* 임시조치 당월검침정보(데이터) 수신 */ MERGE INTO T_METER_NDL_ACT USING DUAL ON (SEQ = #{SEQ}) WHEN NOT MATCHED THEN INSERT ( SEQ, WHM_NO, CNTR_NO, WHME_NDL_DAY_QTT, DM_MT_NDL_DAY_QTT, VAR_NDL_DAY_QTT, WHME_NDL_EVEN_QTT, DM_MT_NDL_EVEN_QTT, VAR_NDL_EVEN_QTT, WHME_NDL_MNGT_QTT, DM_MT_NDL_MNGT_QTT, VAR_NDL_MNGT_QTT, METER_NDL_LST_SEQ, CONS_CNTR_DIV, WORK_DIV, WHME_NDL_DAY_ATCH_FILE_ID, DM_MT_NDL_DAY_ATCH_FILE_ID, VAR_NDL_DAY_ATCH_FILE_ID, WHME_NDL_EVEN_ATCH_FILE_ID, DM_MT_NDL_EVEN_ATCH_FILE_ID, VAR_NDL_EVEN_ATCH_FILE_ID, WHME_NDL_MNGT_ATCH_FILE_ID, DM_MT_NDL_MNGT_ATCH_FILE_ID, VAR_NDL_MNGT_ATCH_FILE_ID, ACT_DATE, DEPT1, GENT_WHM_NDL_DAY_QTT, GENT_WHM_NDL_EVEN_QTT, GENT_WHM_NDL_MNGT_QTT, ESS_WHME_NDL_DAY_QTT, ESS_WHME_NDL_EVEN_QTT, ESS_WHME_NDL_MNGT_QTT, REG_DATE, REG_ID, SEND_YN ) VALUES ( #{SEQ}, #{WHM_NO}, #{CNTR_NO}, #{WHME_NDL_DAY_QTT}, #{DM_MT_NDL_DAY_QTT}, #{VAR_NDL_DAY_QTT}, #{WHME_NDL_EVEN_QTT}, #{DM_MT_NDL_EVEN_QTT}, #{VAR_NDL_EVEN_QTT}, #{WHME_NDL_MNGT_QTT}, #{DM_MT_NDL_MNGT_QTT}, #{VAR_NDL_MNGT_QTT}, #{METER_NDL_LST_SEQ}, #{CONS_CNTR_DIV}, #{WORK_DIV}, #{WHME_NDL_DAY_ATCH_FILE_ID}, #{DM_MT_NDL_DAY_ATCH_FILE_ID}, #{VAR_NDL_DAY_ATCH_FILE_ID}, #{WHME_NDL_EVEN_ATCH_FILE_ID}, #{DM_MT_NDL_EVEN_ATCH_FILE_ID}, #{VAR_NDL_EVEN_ATCH_FILE_ID}, #{WHME_NDL_MNGT_ATCH_FILE_ID}, #{DM_MT_NDL_MNGT_ATCH_FILE_ID}, #{VAR_NDL_MNGT_ATCH_FILE_ID}, TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MI'), #{DEPT1}, #{GENT_WHM_NDL_DAY_QTT}, #{GENT_WHM_NDL_EVEN_QTT}, #{GENT_WHM_NDL_MNGT_QTT}, #{ESS_WHME_NDL_DAY_QTT}, #{ESS_WHME_NDL_EVEN_QTT}, #{ESS_WHME_NDL_MNGT_QTT}, TO_DATE(#{REG_DATE}, 'YYYYMMDDHH24MISS'), #{REG_ID}, 'N' ) WHEN MATCHED THEN UPDATE SET WHM_NO = #{WHM_NO} , CNTR_NO = #{CNTR_NO} , WHME_NDL_DAY_QTT = #{WHME_NDL_DAY_QTT} , DM_MT_NDL_DAY_QTT = #{DM_MT_NDL_DAY_QTT} , VAR_NDL_DAY_QTT = #{VAR_NDL_DAY_QTT} , WHME_NDL_EVEN_QTT = #{WHME_NDL_EVEN_QTT} , DM_MT_NDL_EVEN_QTT = #{DM_MT_NDL_EVEN_QTT} , VAR_NDL_EVEN_QTT = #{VAR_NDL_EVEN_QTT} , WHME_NDL_MNGT_QTT = #{WHME_NDL_MNGT_QTT} , DM_MT_NDL_MNGT_QTT = #{DM_MT_NDL_MNGT_QTT} , VAR_NDL_MNGT_QTT = #{VAR_NDL_MNGT_QTT} , METER_NDL_LST_SEQ = #{METER_NDL_LST_SEQ} , CONS_CNTR_DIV = #{CONS_CNTR_DIV} , WORK_DIV = #{WORK_DIV} , WHME_NDL_DAY_ATCH_FILE_ID = #{WHME_NDL_DAY_ATCH_FILE_ID} , DM_MT_NDL_DAY_ATCH_FILE_ID = #{DM_MT_NDL_DAY_ATCH_FILE_ID} , VAR_NDL_DAY_ATCH_FILE_ID = #{VAR_NDL_DAY_ATCH_FILE_ID} , WHME_NDL_EVEN_ATCH_FILE_ID = #{WHME_NDL_EVEN_ATCH_FILE_ID} , DM_MT_NDL_EVEN_ATCH_FILE_ID = #{DM_MT_NDL_EVEN_ATCH_FILE_ID} , VAR_NDL_EVEN_ATCH_FILE_ID = #{VAR_NDL_EVEN_ATCH_FILE_ID} , WHME_NDL_MNGT_ATCH_FILE_ID = #{WHME_NDL_MNGT_ATCH_FILE_ID} , DM_MT_NDL_MNGT_ATCH_FILE_ID = #{DM_MT_NDL_MNGT_ATCH_FILE_ID} , VAR_NDL_MNGT_ATCH_FILE_ID = #{VAR_NDL_MNGT_ATCH_FILE_ID} , ACT_DATE = TO_DATE(#{ACT_DATE}, 'YYYYMMDDHH24MI') , DEPT1 = #{DEPT1} , GENT_WHM_NDL_DAY_QTT = #{GENT_WHM_NDL_DAY_QTT} , GENT_WHM_NDL_EVEN_QTT = #{GENT_WHM_NDL_EVEN_QTT} , GENT_WHM_NDL_MNGT_QTT = #{GENT_WHM_NDL_MNGT_QTT} , ESS_WHME_NDL_DAY_QTT = #{ESS_WHME_NDL_DAY_QTT} , ESS_WHME_NDL_EVEN_QTT = #{ESS_WHME_NDL_EVEN_QTT} , ESS_WHME_NDL_MNGT_QTT = #{ESS_WHME_NDL_MNGT_QTT} , SEND_YN = 'N'