/* 사용자 관리 KAMI 사용자 정보 수신 */ MERGE INTO SMN_USER USING DUAL ON (USER_ID = #{USER_ID}) WHEN NOT MATCHED THEN INSERT ( USER_ID, PWD, USER_NM, USER_ENM, DEFAULT_AUTH_CD, USER_GB, BIRTHDAY, GENDER_GB, DEPT_CD, DEPT_NM, TEL_NO , HP_NO, ZIPNO, ADDR1, ADDR2, JOIN_DT, USE_YN, USER_PIC , ADD_COL1, ADD_COL2, ADD_COL3, ADD_COL4, ADD_COL5, ADD_COL6, ADD_COL7, ADD_COL8, ADD_COL9, ADD_COL10 , INSTANT_KEY, INSERT_DTTM, INSERT_ID, INSERT_IP, INSERT_PGM , UPDATE_DTTM, UPDATE_ID, UPDATE_IP, UPDATE_PGM , DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2 , BLON_CL_CD , EMP_CL_CD, INLN_BUPE_ID, SBCN_BUPE_ID, SELF_CERT_CRTR_DT, SELF_CERT_NO, USER_SEQ ) VALUES ( #{USER_ID}, SHA2(#{USER_ID}, 256), #{USER_ID}, NULL, 'U0009', '9', NULL, NULL, NULL, NULL, NULL , #{USER_MPHN_NO}, NULL, NULL, NULL, NULL, #{USE_YN}, NULL , NULL, NULL, NULL, #{AMMS_BLON_CL_CD}, #{AMMS_MBL_USER_AUTH_CD}, NULL, NULL, NULL, NULL, NULL , NULL, DECODE(#{FRST_REG_DT}, NULL, NULL, TO_DATETIME(#{FRST_REG_DT}, 'YYYYMMDDHH24MISS')), DECODE(#{FRST_REGR_EMPNO}, NULL, NULL, #{FRST_REGR_EMPNO}), NULL, 'DFXAGENT' , DECODE(#{LST_CHG_DT}, NULL, NULL, TO_DATETIME(#{LST_CHG_DT}, 'YYYYMMDDHH24MISS')), DECODE(#{LST_CHGR_EMPNO}, NULL, NULL, #{LST_CHGR_EMPNO}), NULL, NULL , #{AMMS_HDQR_CL_CD}, #{AMMS_BIZP_CL_CD}, NULL, NULL , DECODE(#{AMMS_BLON_CL_CD}, NULL, NULL, '1', '10', '2', '30') , NULL, DECODE(#{AMMS_BLON_CL_CD}, '2', #{CO_REG_NO}, NULL), NULL, NULL, NULL, NULL ) WHEN MATCHED THEN UPDATE SET HP_NO = #{USER_MPHN_NO} , USE_YN = #{USE_YN} , ADD_COL4 = #{AMMS_BLON_CL_CD} , ADD_COL5 = #{AMMS_MBL_USER_AUTH_CD} , DEPT1 = #{AMMS_HDQR_CL_CD} , DEPT2 = #{AMMS_BIZP_CL_CD} , BLON_CL_CD = DECODE(#{AMMS_BLON_CL_CD}, NULL, NULL, '1', '10', '2', '30') , INLN_BUPE_ID = DECODE(#{AMMS_BLON_CL_CD}, '2', #{CO_REG_NO}, NULL) , UPDATE_DTTM = DECODE(#{LST_CHG_DT}, NULL, NULL, TO_DATETIME(#{LST_CHG_DT}, 'YYYYMMDDHH24MISS')) , UPDATE_ID = DECODE(#{LST_CHGR_EMPNO}, NULL, NULL, #{LST_CHGR_EMPNO}) ; /* 한전 한전관리자 */ /* 계기관리 U0003, 자재관리 U0004, 지역관리 U0006, 계기관리(Web) U0007, 자재관리(WEB) U0008, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{USER_ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0003', 'U0004', 'U0006', 'U0007', 'U0008', 'U0009') AND #{AMMS_BLON_CL_CD} IN ('1', '2', '3', '4') ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ; /* 내선 계기 계기 담당자 */ /* 계기관리 U0003, 자재관리 U0004, 지역관리 U0006, 계기관리(Web) U0007, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{USER_ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0003', 'U0004', 'U0006', 'U0007', 'U0009') AND #{AMMS_BLON_CL_CD} = '5' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ; /* 제조사 자재 담당자 */ /* 자재관리 U0004, 자재관리(WEB) U0008, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{USER_ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0004', 'U0008', 'U0009') AND #{AMMS_BLON_CL_CD} = '6' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ;
/* 사용자 관리 AD_USER 정보 수신 */ MERGE INTO SMN_USER USING DUAL ON (USER_ID = #{ID}) WHEN NOT MATCHED THEN INSERT ( USER_ID, PWD, USER_NM, USER_ENM, DEFAULT_AUTH_CD, USER_GB, BIRTHDAY, GENDER_GB, DEPT_CD, DEPT_NM, TEL_NO , HP_NO, ZIPNO, ADDR1, ADDR2, JOIN_DT, USE_YN, USER_PIC , ADD_COL1, ADD_COL2, ADD_COL3, ADD_COL4, ADD_COL5, ADD_COL6, ADD_COL7, ADD_COL8, ADD_COL9, ADD_COL10 , INSTANT_KEY, INSERT_DTTM, INSERT_ID, INSERT_IP, INSERT_PGM , UPDATE_DTTM, UPDATE_ID, UPDATE_IP, UPDATE_PGM , DEPT1, DEPT2, KDN_DEPT1, KDN_DEPT2 , BLON_CL_CD , EMP_CL_CD, INLN_BUPE_ID, SBCN_BUPE_ID, SELF_CERT_CRTR_DT, SELF_CERT_NO, USER_SEQ ) VALUES ( #{ID}, SHA2('ami'||#{ID}||'!', 256), #{NAME}, NULL, 'U0009', '9', NULL, NULL, NULL, NULL, #{TEL} , #{HP}, NULL, NULL, NULL, NULL, #{USE_YN}, NULL , #{AUTH_CODE}, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL , NULL, DECODE(#{REG_DATE}, NULL, NULL, TO_DATETIME(#{REG_DATE}, 'YYYYMMDDHH24MISS')), DECODE(#{REG_ID}, NULL, NULL, #{REG_ID}), NULL, NULL , DECODE(#{MOD_DATE}, NULL, NULL, TO_DATETIME(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), DECODE(#{MOD_ID}, NULL, NULL, #{MOD_ID}), NULL, NULL , (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1}), NULL, #{KDN_DEPT1}, #{KDN_DEPT2} , '20' , 'E', NULL, NULL, NULL, NULL, #{SEQ} ) WHEN MATCHED THEN UPDATE SET HP_NO = #{HP} , USE_YN = #{USE_YN} , ADD_COL1 = #{AUTH_CODE} , DEPT1 = (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1}) , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , USER_SEQ = #{SEQ} , UPDATE_DTTM = DECODE(#{MOD_DATE}, NULL, NULL, TO_DATETIME(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , UPDATE_ID = DECODE(#{MOD_ID}, NULL, NULL, #{MOD_ID}) ;

/* KDN 시스템 관리자 */ /* 유지관리 U0001, AMI공사관리 U0002, 계기관리 U0003, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 계기관리(Web) U0007, 자재관리(WEB) U0008, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0001', 'U0002', 'U0003', 'U0004', 'U0005', 'U0006', 'U0007', 'U0008', 'U0009') AND #{AUTH_CODE} = '1' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ;

/* KDN 사업소 관리자 */ /* 유지관리 U0001, AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 계기관리(Web) U0007, 자재관리(WEB) U0008, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0001', 'U0002', 'U0004', 'U0005', 'U0006', 'U0007', 'U0008', 'U0009') AND #{AUTH_CODE} = '2' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ;

/* KDN 사업소 담당자 */ /* 유지관리 U0001, AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0001', 'U0002', 'U0004', 'U0005', 'U0006', 'U0009') AND #{AUTH_CODE} = '3' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ;
/* 사용자 관리 ETC_USER 정보 수신 */ MERGE INTO SMN_USER USING DUAL ON (USER_ID = #{ID}) WHEN NOT MATCHED THEN INSERT ( USER_ID, PWD, USER_NM, USER_ENM, DEFAULT_AUTH_CD, USER_GB, BIRTHDAY, GENDER_GB, DEPT_CD, DEPT_NM, TEL_NO , HP_NO, ZIPNO, ADDR1, ADDR2, JOIN_DT, USE_YN, USER_PIC , ADD_COL1, ADD_COL2, ADD_COL3, ADD_COL4, ADD_COL5, ADD_COL6, ADD_COL7, ADD_COL8, ADD_COL9, ADD_COL10 , INSTANT_KEY, INSERT_DTTM, INSERT_ID, INSERT_IP, INSERT_PGM , UPDATE_DTTM, UPDATE_ID, UPDATE_IP, UPDATE_PGM , DEPT1 , DEPT2, KDN_DEPT1, KDN_DEPT2 , BLON_CL_CD , EMP_CL_CD, INLN_BUPE_ID , SBCN_BUPE_ID , SELF_CERT_CRTR_DT, SELF_CERT_NO, USER_SEQ ) VALUES ( #{ID}, SHA2('ami'||#{ID}||'!', 256), #{NAME}, NULL, 'U0009', '9', NULL, NULL, NULL, NULL, NULL , #{HP}, NULL, NULL, NULL, NULL, #{USE_YN}, NULL , NULL, #{BLON_CO_CL_CD}, #{WORK_DIV}, NULL, NULL, NULL, NULL, NULL, NULL, NULL , NULL, DECODE(#{REG_DATE}, NULL, NULL, TO_DATETIME(#{REG_DATE}, 'YYYYMMDDHH24MISS')), DECODE(#{REG_ID}, NULL, NULL, #{REG_ID}), NULL, NULL , DECODE(#{MOD_DATE}, NULL, NULL, TO_DATETIME(#{MOD_DATE}, 'YYYYMMDDHH24MISS')), DECODE(#{MOD_ID}, NULL, NULL, #{MOD_ID}), NULL, NULL , CASE WHEN #{KDN_DEPT1} = '721A' THEN (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1} AND KDN_DEPT2 = #{KDN_DEPT2}) ELSE (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1}) END , NULL, #{KDN_DEPT1}, #{KDN_DEPT2} , CASE WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} = '1' THEN '20' WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} = '1' THEN '40' WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} = '2' THEN '20' WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} = '2' THEN '40' END , CASE WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} IN ('1', '2') THEN 'T' ELSE NULL END, NULL , CASE WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} IN ('1', '2') THEN #{SBCN_CO} ELSE NULL END , NULL, NULL, #{SEQ} ) WHEN MATCHED THEN UPDATE SET HP_NO = #{HP} , USE_YN = #{USE_YN} , ADD_COL2 = #{BLON_CO_CL_CD} , ADD_COL3 = #{WORK_DIV} , DEPT1 = CASE WHEN #{KDN_DEPT1} = '721A' THEN (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1} AND KDN_DEPT2 = #{KDN_DEPT2}) ELSE (SELECT MAX(DEPT1) AS DEPT1 FROM TM_KDN_DEPT WHERE KDN_DEPT1 = #{KDN_DEPT1}) END , KDN_DEPT1 = #{KDN_DEPT1} , KDN_DEPT2 = #{KDN_DEPT2} , BLON_CL_CD = CASE WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} = '1' THEN '20' WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} = '1' THEN '40' WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} = '2' THEN '20' WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} = '2' THEN '40' END , EMP_CL_CD = CASE WHEN #{BLON_CO_CL_CD} = '10' AND #{WORK_DIV} IN ('1', '2') THEN 'T' ELSE NULL END , SBCN_BUPE_ID = CASE WHEN #{BLON_CO_CL_CD} = '20' AND #{WORK_DIV} IN ('1', '2') THEN #{SBCN_CO} ELSE NULL END , USER_SEQ = #{SEQ} , UPDATE_DTTM = DECODE(#{MOD_DATE}, NULL, NULL, TO_DATETIME(#{MOD_DATE}, 'YYYYMMDDHH24MISS')) , UPDATE_ID = DECODE(#{MOD_ID}, NULL, NULL, #{MOD_ID}) ; /* KDN 외 KDN 일용직 유지보수 담당자 */ /* 유지관리 U0001, AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0001', 'U0002', 'U0004', 'U0005', 'U0006', 'U0009') AND #{WORK_DIV} = '1' AND #{BLON_CO_CL_CD} = '10' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ; /* KDN 외 하도급사 유지보수 담당자 */ /* 유지관리 U0001, AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0001', 'U0002', 'U0004', 'U0005', 'U0006', 'U0009') AND #{WORK_DIV} = '1' AND #{BLON_CO_CL_CD} = '20' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ; /* KDN 외 KDN 일용직 AMI 공사 담당자 */ /* AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0002', 'U0004', 'U0005', 'U0006', 'U0009') AND #{WORK_DIV} = '2' AND #{BLON_CO_CL_CD} = '10' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ; /* KDN 외 하도급사 AMI 공사 담당자 */ /* AMI공사관리 U0002, 자재관리 U0004, 검침관리 U0005, 지역관리 U0006, 커뮤니티 U0009 */ MERGE INTO SMN_USER_AUTH T USING ( SELECT #{ID} AS USER_ID, AUTH_CD FROM SMN_AUTH WHERE AUTH_CD IN ('U0002', 'U0004', 'U0005', 'U0006', 'U0009') AND #{WORK_DIV} = '2' AND #{BLON_CO_CL_CD} = '20' ) S ON (T.USER_ID = S.USER_ID AND T.AUTH_CD = S.AUTH_CD) WHEN NOT MATCHED THEN INSERT (USER_ID, AUTH_CD, INSERT_DTTM) VALUES (S.USER_ID, S.AUTH_CD, SYSDATETIME) ;