#----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-AC_SELECT_001) #--【内容】 #--初回ＣＤＲ発生年月日(KK0191.FIRST_CDR_HASSEI_YMD) #--【検索条件】 #--サービス開始年月日(KK0161.SVC_STA_YMD) #--電話番号(KK0191.TELNO) #--【結合テーブル】 #--サービス契約内訳(KK_T_SVC_KEI_UCWK) #--【結合条件】 #--サービス契約内訳番号(KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO) #--世代登録年月日時分秒(KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM) #--【抽出対象】 #--一意 #--【世代管理】 #--あり #--【予約管理】 #--あり #--【無効フラグ考慮】 #--あり #----------------------------------------------------------- --KEY:AC_SELECT_001 SELECT KK0191.FIRST_CDR_HASSEI_YMD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_STA_YMD = ? AND KK0191.TELNO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-AC_SELECT_002) #--【内容】 #--番ポ有無(KK0191.BMP_UM) #--番ポ工事完了年月日(TU0011.BMP_KOJI_FIN_YMD) #--【検索条件】 #--サービス契約内訳番号(KK0191.SVC_KEI_UCWK_NO) #--ＮＴＴ契約電話回線番号(TU0011.NTT_KEI_TEL_KAISEN_NO) #--【結合テーブル】 #--番ポ工事(TU_T_BMP_KOJI) #--【結合条件】 #--サービス契約内訳番号(TU0011.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO) #--世代登録年月日時分秒(KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM) #--【抽出対象】 #--一意 #--【世代管理】 #--あり #--【予約管理】 #--あり #--【無効フラグ考慮】 #--あり #----------------------------------------------------------- --KEY:AC_SELECT_002 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.BMP_UM FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') AND KK0191.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-AC_SELECT_003) #--【内容】 #--サービス契約内訳.サービス契約内訳ステータス(KK0161.SVC_KEI_UCWK_STAT) #--番ポ工事.番ポ工事ステータス(TU0011.BMP_KOJI_STAT) #--サービス契約内訳.サービス契約番号(KK0161.SVC_KEI_NO) #--番ポ工事.番ポ工事完了年月日(TU0011.BMP_KOJI_FIN_YMD) #--【検索条件】 #--サービス契約内訳＜ｅｏ光電話＞.電話番号(KK0191.TELNO) #--サービス契約内訳.予約適用年月日(KK0161.RSV_APLY_YMD) #--【結合テーブル】 #--サービス契約内訳(KK_T_SVC_KEI_UCWK) #--番ポ工事(TU_T_BMP_KOJI) #--【結合条件】 #--サービス契約内訳番号 #--【抽出対象】 #--一意 #--【世代管理】 #--あり #--【予約管理】 #--あり #--【無効フラグ考慮】 #--あり #----------------------------------------------------------- --KEY:AC_SELECT_003 SELECT * FROM ( SELECT KK0161.SVC_KEI_UCWK_NO, KK0191.TELNO , KK0161.SVC_KEI_UCWK_STAT, TU0011.BMP_KOJI_STAT, KK0161.SVC_KEI_NO, TU0011.BMP_KOJI_FIN_YMD, TU0011.GENE_ADD_DTM FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM=KK0161.GENE_ADD_DTM LEFT OUTER JOIN TU_T_BMP_KOJI TU0011 ON KK0191.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO AND TU0011.MK_FLG = '0' WHERE KK0191.TELNO = ? AND KK0191.BMP_UM = '1' AND (KK0161.SVC_KEI_UCWK_STAT = '91' OR KK0161.SVC_KEI_UCWK_STAT = '92' OR KK0161.SVC_KEI_UCWK_STAT = '93') AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' ) AND KK0191.MK_FLG = '0' AND KK0161.MK_FLG = '0' ORDER BY TU0011.GENE_ADD_DTM DESC ) WHERE ROWNUM <= 1 #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-AC_SELECT_004) #--【内容】 #--サービス契約内訳＜eo光電話＞.電話番号(KK0191.TELNO) #--【検索条件】 #--サービス契約内訳＜ｅｏ光電話＞.電話番号(KK0191.TELNO) #--【結合テーブル】 #--サービス契約内訳(KK_T_SVC_KEI_UCWK) #--オプションサービス契約(KK_T_OP_SVC_KEI) #--回線対象サービス契約(KK_T_KAISEN_TG_SVKEI) #--機器提供サービス契約(KK_T_KKTK_SVC_KEI) #--機器オプションサービス契約(KK_T_KKOP_SVC_KEI) #--【結合条件】 #--サービス契約内訳番号 #--サービス契約番号 #--サービス契約回線内訳番号 #--機器提供サービス契約番号 #--【抽出対象】 #--一意 #--【世代管理】 #--あり #--【予約管理】 #--あり #--【無効フラグ考慮】 #--あり #----------------------------------------------------------- --KEY:AC_SELECT_004 SELECT KK0191.TELNO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN ( SELECT KK0161_WK.SVC_KEI_UCWK_NO ,KK0161_WK.SVC_KEI_NO FROM KK_T_SVC_KEI_UCWK KK0161_WK WHERE KK0161_WK.SVC_STA_YMD <= ? AND (KK0161_WK.SVC_ENDYMD >= ? OR KK0161_WK.SVC_ENDYMD IS NULL) AND KK0161_WK.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161_WK.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') ) KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO INNER JOIN ( SELECT KK0351_WK.SVC_KEI_UCWK_NO FROM KK_T_OP_SVC_KEI KK0351_WK WHERE KK0351_WK.OP_SVC_CD = ? AND KK0351_WK.SVC_STAYMD <= ? AND (KK0351_WK.SVC_ENDYMD >= ? OR KK0351_WK.SVC_ENDYMD IS NULL) AND KK0351_WK.GENE_ADD_DTM = (SELECT MAX(KK0351_GENE.GENE_ADD_DTM) AS KK0351_MAX FROM KK_T_OP_SVC_KEI KK0351_GENE WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351_WK.OP_SVC_KEI_NO AND KK0351_GENE.RSV_APLY_YMD <= ? AND KK0351_GENE.RSV_APLY_CD = '2' AND KK0351_GENE.MK_FLG = '0') ) KK0351 ON KK0351.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO INNER JOIN ( SELECT KK0241_WK.SVC_KEI_NO ,KK0241_WK.SVC_KEI_KAISEN_UCWK_NO FROM KK_T_KAISEN_TG_SVKEI KK0241_WK WHERE KK0241_WK.KAISEN_UCWK_USE_STAYMD <= ? AND (KK0241_WK.KAISEN_UCWK_USE_ENDYMD >= ? OR KK0241_WK.KAISEN_UCWK_USE_ENDYMD IS NULL) AND KK0241_WK.KAISEN_UCWK_USE_STAYMD = (SELECT MAX(KK0241_STA.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAX FROM KK_T_KAISEN_TG_SVKEI KK0241_STA WHERE KK0241_STA.SVC_KEI_NO = KK0241_WK.SVC_KEI_NO AND KK0241_STA.KAISEN_UCWK_USE_STAYMD <= ? AND KK0241_STA.MK_FLG = '0') ) KK0241 ON KK0241.SVC_KEI_NO = KK0161.SVC_KEI_NO INNER JOIN ( SELECT KK0341_WK.SVC_KEI_KAISEN_UCWK_NO ,KK0341_WK.KKTK_SVC_KEI_NO FROM KK_T_KKTK_SVC_KEI KK0341_WK WHERE KK0341_WK.KKTK_SVC_CD IN (?,?) AND KK0341_WK.SVC_STA_YMD <= ? AND (KK0341_WK.SVC_ENDYMD >= ? OR KK0341_WK.SVC_ENDYMD IS NULL) AND KK0341_WK.GENE_ADD_DTM = (SELECT MAX(KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX FROM KK_T_KKTK_SVC_KEI KK0341_GENE WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341_WK.KKTK_SVC_KEI_NO AND KK0341_GENE.RSV_APLY_YMD <= ? AND KK0341_GENE.RSV_APLY_CD = '2' AND KK0341_GENE.MK_FLG = '0') ) KK0341 ON KK0341.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO INNER JOIN ( SELECT KK2811_WK.KKTK_SVC_KEI_NO FROM KK_T_KKOP_SVC_KEI KK2811_WK WHERE KK2811_WK.KKOP_SVC_CD = ? AND KK2811_WK.SVC_STAYMD <= ? AND (KK2811_WK.SVC_ENDYMD >= ? OR KK2811_WK.SVC_ENDYMD IS NULL) AND KK2811_WK.GENE_ADD_DTM = (SELECT MAX(KK2811_GENE.GENE_ADD_DTM) AS KK2811_MAX FROM KK_T_KKOP_SVC_KEI KK2811_GENE WHERE KK2811_GENE.KKOP_SVC_KEI_NO = KK2811_WK.KKOP_SVC_KEI_NO AND KK2811_GENE.RSV_APLY_YMD < ? AND KK2811_GENE.RSV_APLY_CD = '2' AND KK2811_GENE.MK_FLG = '0') ) KK2811 ON KK2811.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO WHERE KK0191.TELNO = ? AND KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_INSERT_001) #--指定したサービス契約内訳番号と世代登録年月日時分秒から全情報を取得し、休止のカレントレコードを生成する。 #----------------------------------------------------------- --KEY:KK_INSERT_001 INSERT INTO KK_T_SVKEIUW_EOH_TEL SELECT SVC_KEI_UCWK_NO, ?, --GENE_ADD_DTM (1) TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, EMG_AD_FIX_AF_CHGE_KH, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, TEL_BAS_HOST_ID, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, ITNTOKI_KIBO_UM, ITNTOKI_SBT_CD, TOKI_TENSO_SK_TELNO, ITNTOKI_STA_YMD, ITNTOKI_END_RSV_YMD, ITNTOKI_END_YMD, ITNTOKI_ADD_CD, ITNTOKI_MAN_STA_RSV_YMD, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_DOJI_KOJI_KIBO_UM, BMP_DOJI_OPEN_YMD, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_PORT_NO, '2', --RSV_APLY_CD ?, --ADD_DTM (2) ?, --ADD_OPEACNT (3) ?, --UPD_DTM (4) ?, --UPD_OPEACNT (5) null, --DEL_DTM null, --DEL_OPEACNT '0' --MK_FLG FROM KK_T_SVKEIUW_EOH_TEL WHERE SVC_KEI_UCWK_NO = ? --(6) AND GENE_ADD_DTM = ? --(7) AND MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_INSERT_002) #--消去実行 #----------------------------------------------------------- --KEY:KK_INSERT_002 INSERT INTO KK_T_SVKEIUW_EOH_TEL( SVC_KEI_UCWK_NO, GENE_ADD_DTM, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, UPD_DTM, UPD_OPEACNT, DEL_DTM, DEL_OPEACNT, MK_FLG, ADD_UNYO_YMD, ADD_TRN_ID, UPD_UNYO_YMD, UPD_TRN_ID, DEL_UNYO_YMD, DEL_TRN_ID ) SELECT SVC_KEI_UCWK_NO, ?, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, 'UPD_DTM', 'UPD_OPE', NULL, NULL, '0', ADD_UNYO_YMD, ADD_TRN_ID, 'UPD_UNYO', 'UPD_TRN', NULL, NULL FROM KK_T_SVKEIUW_EOH_TEL WHERE SVC_KEI_UCWK_NO = ? AND GENE_ADD_DTM = ? #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_INSERT_003) #--サービス契約内訳番号に紐づくカレントレコードを元に、番ポ同時工事希望有無反映 #----------------------------------------------------------- --KEY:KK_INSERT_003 INSERT INTO KK_T_SVKEIUW_EOH_TEL KK0191 ( KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.TELNO, KK0191.DAIHYO_TELNO, KK0191.TELNO_USE_PLACE_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK0191.EMG_KSH_KSITEI_FLG, KK0191.EMG_KSH_NM, KK0191.EMG_KSH_KANA, KK0191.SIP_USER_ID, KK0191.SIP_USER_ID_PWD, KK0191.SIP_USER_ID_DEL_ZUMI_FLG, KK0191.FIRST_CDR_HASSEI_YMD, KK0191.TELNO_JUN, KK0191.NTT_NO_GUIDE_UM, KK0191.HELLOPAGE_KEISAI_UM, KK0191.TUWA_DTL_KOKAI_YH, KK0191.TEL_NO_PRE_TCHI_CD, KK0191.NTT_KEISHA_AD_CD, KK0191.NTT_KEISHA_PCD, KK0191.NTT_KEISHA_STATE_NM, KK0191.NTT_KEISHA_CITY_NM, KK0191.NTT_KEISHA_OAZTSU_NM, KK0191.NTT_KEISHA_AZCHO_NM, KK0191.NTT_KEISHA_BNCHIGO, KK0191.NTT_KEISHA_ADRTTM, KK0191.NTT_KEISHA_ADRRM, KK0191.NKYS_AD_MAN_INPUT_FLG, KK0191.NTT_KSH_KANA, KK0191.NTT_KEISHA_NM, KK0191.NTT_KSH_AD_KSH_AD_SAI_FLG, KK0191.TSJGS_CD, KK0191.ITNM_TSJGS_KTN_CD, KK0191.DOJI_OPEN_KIBO_CD, KK0191.TEL_TTDKI_MSKM_CD, KK0191.MAN_SWITCH_RSV_DTM, KK0191.SHK_TEL_OP_SETTE_RMTS_PWD, KK0191.TEL_OP_SETTE_RMTS_PWD, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0191.DSL_PAUSE_TOKI_SBT_CD, KK0191.DSL_PAUSE_TOKI_KIBO_UM, KK0191.DSL_PAUSE_TOKI_STA_RSYMD, KK0191.DSL_PAUSE_TOKI_STA_YMD, KK0191.DSL_PAUSE_TOKI_END_RSYMD, KK0191.DSL_PAUSE_TOKI_END_YMD, KK0191.PAUSE_TOKI_END_SBT_CD, KK0191.DSL_PAUSE_TOKI_TSS_TLN, KK0191.DSL_PAUSE_TOKI_TSS_SVKUWNO, KK0191.ITENS_OPAF_TOKI_ADD_CD, KK0191.ITENS_OPAF_TOKI_SBT_CD, KK0191.ITENS_OPAF_TOKI_KIBO_UM, KK0191.ITENS_OPAF_TOKI_STA_RSYMD, KK0191.ITENS_OPAF_TOKI_STA_YMD, KK0191.ITENS_OPAF_TOKI_END_RSYMD, KK0191.ITENS_OPAF_TOKI_END_YMD, KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO, KK0191.TOWNPAGE_KEISAI_KANA, KK0191.TOWNPAGE_KEISAI_NM, KK0191.BMP_UM, KK0191.BMP_AF_USE_PLACE_SBT_CD, KK0191.BMP_DOJI_KOJI_KIBO_UM, KK0191.BMP_KOJI_KIBO_YMD, KK0191.BMPKJ_KIBO_TIME_CD, KK0191.BMP_DOJI_OPEN_YMD, KK0191.DOBANITEN_MSKM_UM, KK0191.DOBANITEN_REQ_CTRL_CD, KK0191.DOBANITEN_JDG_CD, KK0191.DBANITEN_MSKMJIG_KIJIRAN_1, KK0191.DBANITEN_MSKMJIG_KIJIRAN_2, KK0191.DOBANITEN_NTT_KEISHA_NM, KK0191.DOBANITEN_NTT_KSH_KANA, KK0191.VA_TAKNKIKI_MODEL_CD, KK0191.VA_KIKI_SEIZO_NO, KK0191.VA_KIKI_CHG_NO, KK0191.VA_PORT_NO, KK0191.RSV_APLY_CD, KK0191.ADD_DTM, KK0191.ADD_OPEACNT, KK0191.UPD_DTM, KK0191.UPD_OPEACNT, KK0191.DEL_DTM, KK0191.DEL_OPEACNT, KK0191.MK_FLG, KK0191.ADD_UNYO_YMD, KK0191.ADD_TRN_ID, KK0191.UPD_UNYO_YMD, KK0191.UPD_TRN_ID, KK0191.DEL_UNYO_YMD, KK0191.DEL_TRN_ID, KK0191.DOBANITEN_TTDK_JSSIS_SKCD, KK0191.DBNITN_KOPT_NAI_SKEKKA_CD, KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG ) SELECT KK0191.SVC_KEI_UCWK_NO, ?, KK0191.TELNO, KK0191.DAIHYO_TELNO, KK0191.TELNO_USE_PLACE_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK0191.EMG_KSH_KSITEI_FLG, KK0191.EMG_KSH_NM, KK0191.EMG_KSH_KANA, KK0191.SIP_USER_ID, KK0191.SIP_USER_ID_PWD, KK0191.SIP_USER_ID_DEL_ZUMI_FLG, KK0191.FIRST_CDR_HASSEI_YMD, KK0191.TELNO_JUN, KK0191.NTT_NO_GUIDE_UM, KK0191.HELLOPAGE_KEISAI_UM, KK0191.TUWA_DTL_KOKAI_YH, KK0191.TEL_NO_PRE_TCHI_CD, KK0191.NTT_KEISHA_AD_CD, KK0191.NTT_KEISHA_PCD, KK0191.NTT_KEISHA_STATE_NM, KK0191.NTT_KEISHA_CITY_NM, KK0191.NTT_KEISHA_OAZTSU_NM, KK0191.NTT_KEISHA_AZCHO_NM, KK0191.NTT_KEISHA_BNCHIGO, KK0191.NTT_KEISHA_ADRTTM, KK0191.NTT_KEISHA_ADRRM, KK0191.NKYS_AD_MAN_INPUT_FLG, KK0191.NTT_KSH_KANA, KK0191.NTT_KEISHA_NM, KK0191.NTT_KSH_AD_KSH_AD_SAI_FLG, KK0191.TSJGS_CD, KK0191.ITNM_TSJGS_KTN_CD, KK0191.DOJI_OPEN_KIBO_CD, KK0191.TEL_TTDKI_MSKM_CD, KK0191.MAN_SWITCH_RSV_DTM, KK0191.SHK_TEL_OP_SETTE_RMTS_PWD, KK0191.TEL_OP_SETTE_RMTS_PWD, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0191.DSL_PAUSE_TOKI_SBT_CD, KK0191.DSL_PAUSE_TOKI_KIBO_UM, KK0191.DSL_PAUSE_TOKI_STA_RSYMD, KK0191.DSL_PAUSE_TOKI_STA_YMD, KK0191.DSL_PAUSE_TOKI_END_RSYMD, KK0191.DSL_PAUSE_TOKI_END_YMD, KK0191.PAUSE_TOKI_END_SBT_CD, KK0191.DSL_PAUSE_TOKI_TSS_TLN, KK0191.DSL_PAUSE_TOKI_TSS_SVKUWNO, KK0191.ITENS_OPAF_TOKI_ADD_CD, KK0191.ITENS_OPAF_TOKI_SBT_CD, KK0191.ITENS_OPAF_TOKI_KIBO_UM, KK0191.ITENS_OPAF_TOKI_STA_RSYMD, KK0191.ITENS_OPAF_TOKI_STA_YMD, KK0191.ITENS_OPAF_TOKI_END_RSYMD, KK0191.ITENS_OPAF_TOKI_END_YMD, KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO, KK0191.TOWNPAGE_KEISAI_KANA, KK0191.TOWNPAGE_KEISAI_NM, KK0191.BMP_UM, KK0191.BMP_AF_USE_PLACE_SBT_CD, ?, KK0191.BMP_KOJI_KIBO_YMD, KK0191.BMPKJ_KIBO_TIME_CD, KK0191.BMP_DOJI_OPEN_YMD, KK0191.DOBANITEN_MSKM_UM, KK0191.DOBANITEN_REQ_CTRL_CD, KK0191.DOBANITEN_JDG_CD, KK0191.DBANITEN_MSKMJIG_KIJIRAN_1, KK0191.DBANITEN_MSKMJIG_KIJIRAN_2, KK0191.DOBANITEN_NTT_KEISHA_NM, KK0191.DOBANITEN_NTT_KSH_KANA, KK0191.VA_TAKNKIKI_MODEL_CD, KK0191.VA_KIKI_SEIZO_NO, KK0191.VA_KIKI_CHG_NO, KK0191.VA_PORT_NO, KK0191.RSV_APLY_CD, 'ADD_DTM', 'ADD_OPE', 'UPD_DTM', 'UPD_OPE', KK0191.DEL_DTM, KK0191.DEL_OPEACNT, '0', 'ADD_UNYO', 'ADD_TRN', 'UPD_UNYO', 'UPD_TRN', KK0191.DEL_UNYO_YMD, KK0191.DEL_TRN_ID, KK0191.DOBANITEN_TTDK_JSSIS_SKCD, KK0191.DBNITN_KOPT_NAI_SKEKKA_CD, KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM WHERE (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO ) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_001) #--サービス契約番号に紐づくサービス契約内訳を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_001 SELECT KK0191.TELNO_JUN ,KK0191.SVC_KEI_UCWK_NO ,KK0191.TELNO ,KK0191.BMP_UM ,KK0161.SHOSA_YMD ,KK0161.UPD_DTM ,KK0161.KEIZK_AF_KEI_CHGECHU_FLG ,KK0161.KEIZK_MT_SVC_KEI_UCWK_NO ,KK0191.TEL_NO_PRE_TCHI_CD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' AND KK0161.SVC_KEI_UCWK_STAT NOT IN ('910', '920') AND KK0161.SVC_KEI_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS GENE_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND NOT EXISTS ( SELECT 1 FROM KK_T_IDO_RSV RSV WHERE RSV.IDO_RSV_DTL_CD = '007' AND RSV.IDO_RSV_STAT_CD IN ('00','03') AND RSV.MK_FLG = '0' AND RSV.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO) ORDER BY KK0191.TELNO_JUN ASC, KK0161.KEIZK_AF_KEI_CHGECHU_FLG DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_002) #--サービス契約内訳番号にひもづくデータを電話番号順番の昇順に取得する（複数指定可） #----------------------------------------------------------- --KEY:KK_SELECT_002 SELECT KK0191.TELNO_JUN, KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.TELNO, KK0191.NTT_NO_GUIDE_UM, KK0191.HELLOPAGE_KEISAI_UM, KK0191.NTT_KEISHA_PCD, KK0191.NTT_KEISHA_STATE_NM, KK0191.NTT_KEISHA_CITY_NM, KK0191.NTT_KEISHA_OAZTSU_NM, KK0191.NTT_KEISHA_AZCHO_NM, KK0191.NTT_KEISHA_BNCHIGO, KK0191.NTT_KEISHA_ADRTTM, KK0191.NTT_KEISHA_ADRRM, KK0191.NTT_KSH_KANA, KK0191.NTT_KEISHA_NM, KK0191.TSJGS_CD, KK0191.BMP_UM, KK0161_KEY.IDO_DIV FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN ( SELECT KK0161.SVC_KEI_UCWK_NO ,KK0161.GENE_ADD_DTM ,KK0161.IDO_DIV FROM KK_T_SVC_KEI_UCWK KK0161 WHERE KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS GENE_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0')) KK0161_KEY ON KK0161_KEY.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161_KEY.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0191.SVC_KEI_UCWK_NO IN (?,?) AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' ORDER BY KK0191.TELNO_JUN ASC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_004) #--サービス契約内訳＜ｅｏ光電話＞の情報を取得します。 #----------------------------------------------------------- --KEY:KK_SELECT_004 SELECT UCWK.SVC_KEI_UCWK_NO, EOH_TEL.TELNO, EOH_TEL.DAIHYO_TELNO, EOH_TEL.VA_PORT_NO, EOH_TEL.TEL_OP_SETTE_RMTS_PWD, EOH_TEL.BMP_UM FROM KK_T_SVC_KEI_UCWK UCWK, KK_T_SVKEIUW_EOH_TEL EOH_TEL WHERE UCWK.SVC_KEI_UCWK_NO = ? AND UCWK.RSV_APLY_YMD || UCWK.GENE_ADD_DTM = ( SELECT MAX(UCWK_GENE.RSV_APLY_YMD || UCWK_GENE.GENE_ADD_DTM) AS UCWK_MAX FROM KK_T_SVC_KEI_UCWK UCWK_GENE WHERE UCWK_GENE.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO AND UCWK_GENE.RSV_APLY_YMD <= ? AND UCWK_GENE.RSV_APLY_CD = '2' AND UCWK_GENE.MK_FLG = '0' ) AND EOH_TEL.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO AND EOH_TEL.GENE_ADD_DTM = ( SELECT MAX(EOH_TEL_GENE.GENE_ADD_DTM) AS EOH_TEL_MAX FROM KK_T_SVKEIUW_EOH_TEL EOH_TEL_GENE WHERE EOH_TEL_GENE.SVC_KEI_UCWK_NO = EOH_TEL.SVC_KEI_UCWK_NO AND EOH_TEL_GENE.RSV_APLY_CD = '2' AND EOH_TEL_GENE.MK_FLG = '0' ) ORDER BY EOH_TEL.TELNO_JUN #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_005) #--サービス契約番号に紐づくサービス契約内訳＜ｅｏ光電話＞情報を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_005 SELECT SVKEIUW_EOH_TEL.EMG_AD_CD, SVKEIUW_EOH_TEL.TELNO, SVKEIUW_EOH_TEL.EMG_HOSEI_PCD, SVKEIUW_EOH_TEL.EMG_STATE_NM, SVKEIUW_EOH_TEL.EMG_CITY_NM, SVKEIUW_EOH_TEL.EMG_OAZTSU_NM, SVKEIUW_EOH_TEL.EMG_AZCHO_NM, SVKEIUW_EOH_TEL.EMG_BNCHIGO, SVKEIUW_EOH_TEL.EMG_KSH_NM, SVKEIUW_EOH_TEL.EMG_KSH_KANA, SVKEIUW_EOH_TEL.BMP_UM FROM KK_T_SVC_KEI_UCWK SVC_KEI_UCWK LEFT OUTER JOIN KK_T_SVKEIUW_EOH_TEL SVKEIUW_EOH_TEL ON SVC_KEI_UCWK.SVC_KEI_UCWK_NO = SVKEIUW_EOH_TEL.SVC_KEI_UCWK_NO AND SVC_KEI_UCWK.GENE_ADD_DTM = SVKEIUW_EOH_TEL.GENE_ADD_DTM WHERE SVC_KEI_UCWK.SVC_KEI_NO = ? AND SVC_KEI_UCWK.RSV_APLY_YMD || SVC_KEI_UCWK.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_UCWK_RCNT.RSV_APLY_YMD || KK_T_SVC_KEI_UCWK_RCNT.GENE_ADD_DTM) AS KK_T_SVC_KEI_UCWK_MAX FROM KK_T_SVC_KEI_UCWK KK_T_SVC_KEI_UCWK_RCNT WHERE KK_T_SVC_KEI_UCWK_RCNT.SVC_KEI_UCWK_NO = SVC_KEI_UCWK.SVC_KEI_UCWK_NO AND KK_T_SVC_KEI_UCWK_RCNT.RSV_APLY_YMD <= ? AND KK_T_SVC_KEI_UCWK_RCNT.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_UCWK_RCNT.MK_FLG = '0') AND SVC_KEI_UCWK.KEIZK_AF_KEI_CHGECHU_FLG = '0' AND SVC_KEI_UCWK.SVC_KEI_UCWK_STAT NOT IN ('910','920') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_007) #--サービス契約内訳番号と世代登録年月日時分秒に紐づくSOD発行に必要なデータを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_007 SELECT KTSET.SIP_USER_ID, KTSET.DAIHYO_TELNO, KTSET.TELNO_JUN, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.VA_PORT_NO, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, KTSET.EMG_KSH_NM, KTSET.EMG_KSH_KANA, KTSET.EMG_AD_CD, KTSET.EMG_BNCHIGO, KTSET.EMG_ADRTTM, KTSET.EMG_ADRRM, KTSET.BMP_UM, KTSET.SHK_TEL_OP_SETTE_RMTS_PWD, KTSET.EMG_STATE_NM, KTSET.EMG_CITY_NM, KTSET.EMG_OAZTSU_NM, KTSET.EMG_AZCHO_NM FROM KK_T_SVKEIUW_EOH_TEL KTSET WHERE KTSET.MK_FLG = '0' AND KTSET.SVC_KEI_UCWK_NO = ? AND KTSET.GENE_ADD_DTM = ? #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_008) #--サービス契約番号と世代登録年月日時分秒に紐づくSOD発行に必要なデータを複数件取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_008 SELECT KTSET.SIP_USER_ID, KTSET.DAIHYO_TELNO, KTSET.TELNO_JUN, KTSET.TEL_BAS_HOST_ID, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.VA_PORT_NO, KTSET.ITNTOKI_SBT_CD, KTSET.TOKI_TENSO_SK_TELNO, KTSET.EMG_KSH_NM, KTSET.EMG_KSH_KANA, KTSET.EMG_AD_CD, KTSET.EMG_BNCHIGO, KTSET.EMG_ADRTTM, KTSET.EMG_ADRRM FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET WHERE KTSKU.MK_FLG = '0' AND KTSET.MK_FLG = '0' AND KTSKU.SVC_KEI_NO = ? AND KTSKU.GENE_ADD_DTM = ? AND KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSET.GENE_ADD_DTM = (SELECT MAX(SUB_KTSET.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL SUB_KTSET WHERE SUB_KTSET.MK_FLG = '0' AND SUB_KTSET.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_009) #--サービス契約番号,VA宅内機器型式コード、VA機器製造番号,世代登録年月日時分秒に紐づくSOD発行に必要なデータを複数件取得する。 #--サービス契約内訳ステータスは020以上910未満とする #----------------------------------------------------------- --KEY:KK_SELECT_009 SELECT KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.VA_PORT_NO, KTSET.TELNO, KTSET.BMP_UM, KTSKU.SVC_KEI_UCWK_NO, KTSKU.GENE_ADD_DTM as SKU_GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET ,KK_T_KKTK_SVC_KEI KK0341 WHERE KTSKU.SVC_KEI_UCWK_NO = ? AND KTSKU.SVC_KEI_UCWK_STAT >= '020' AND KTSKU.SVC_KEI_UCWK_STAT < '910' AND KTSKU.MK_FLG = '0' AND KTSET.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND KTSET.GENE_ADD_DTM = KTSKU.GENE_ADD_DTM AND KTSET.MK_FLG = '0' AND KK0341.SVC_KEI_NO = KTSKU.SVC_KEI_NO AND KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_GENE WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_GENE.RSV_APLY_YMD <= ? AND KK0341_GENE.RSV_APLY_CD = '2' AND KK0341_GENE.MK_FLG = '0') AND KK0341.TAKNKIKI_SBT_CD = '50' AND KK0341.TAKNKIKI_MODEL_CD = ? AND KK0341.KIKI_SEIZO_NO = ? ORDER BY KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_010) #--サービス契約番号,世代登録年月日時分秒に紐づくSOD発行に必要なデータを複数件取得する。 #--サービス契約内訳ステータスは020以上910未満とする #----------------------------------------------------------- --KEY:KK_SELECT_010 SELECT KTSET.TEL_BAS_HOST_ID FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET WHERE KTSKU.MK_FLG = '0' AND KTSET.MK_FLG = '0' AND KTSKU.SVC_KEI_NO = ? AND KTSKU.GENE_ADD_DTM = ? AND KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO(+) AND KTSET.GENE_ADD_DTM = (SELECT MAX(SUB_KTSET.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL SUB_KTSET WHERE SUB_KTSET.MK_FLG = '0' AND SUB_KTSET.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO) AND KTSKU.SVC_KEI_UCWK_STAT >= '020' AND KTSKU.SVC_KEI_UCWK_STAT < '910' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_011) #--サービス契約番号に紐づく、サービス契約内訳＜eo光電話>のサービス内訳番号と判定に必要な項目を取得する。 #--KK14901サービスオーダ送信依頼 #----------------------------------------------------------- --KEY:KK_SELECT_011 SELECT KTSET.SVC_KEI_UCWK_NO, KTSET.GENE_ADD_DTM, KTSET.BMP_UM, KTSET.DSL_PAUSE_TOKI_KIBO_UM, KTSET.ITENS_OPAF_TOKI_KIBO_UM, KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.TELNO_JUN, KTSET.VA_KIKI_CHG_NO, KTSET.VA_PORT_NO, KTSKU.SVC_KEI_UCWK_STAT, KK0081.PCRS_CD, KTSKU.RSV_APLY_YMD FROM KK_T_SVC_KEI_UCWK KTSKU JOIN KK_T_SVKEIUW_EOH_TEL KTSET ON KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSKU.GENE_ADD_DTM = KTSET.GENE_ADD_DTM JOIN KK_T_SVC_KEI KK0081 ON KK0081.SVC_KEI_NO = KTSKU.SVC_KEI_NO WHERE KTSKU.SVC_KEI_NO = ? AND KTSKU.RSV_APLY_YMD || KTSKU.GENE_ADD_DTM = ( SELECT MAX(G.RSV_APLY_YMD || G.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK G WHERE G.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND G.RSV_APLY_YMD <= ? AND G.GENE_ADD_DTM <= ? AND G.RSV_APLY_CD = '2' AND G.MK_FLG = '0') AND KTSET.RSV_APLY_CD = '2' AND KTSET.MK_FLG = '0' AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MAX(KK0081G.RSV_APLY_YMD || KK0081G.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081G WHERE KK0081G.SVC_KEI_NO = KTSKU.SVC_KEI_NO AND KK0081G.RSV_APLY_YMD <= KTSKU.RSV_APLY_YMD AND KK0081G.GENE_ADD_DTM <= KTSKU.GENE_ADD_DTM AND KK0081G.RSV_APLY_CD = '2' AND KK0081G.MK_FLG = '0') ORDER BY KTSET.TELNO_JUN, KTSET.SVC_KEI_UCWK_NO DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_012) #--サービス契約内訳、サービス契約内訳、サービス契約から予定トーキ情報を取得する。 #--抽出条件：①または②に合致 #--　①移転先開通後トーキ登録コードが「移転先登録によるトーキ」で、移転先開通後トーキ終了年月日が未設定 #--　②解約・休止トーキ登録コードが「移転元解約によるトーキ」または「休止によるトーキ」で、解約・休止トーキ終了年月日が未設定 #----------------------------------------------------------- --KEY:KK_SELECT_012 SELECT KK0081.SVC_KEI_NO, KK0081.GENE_ADD_DTM AS SK_GENE_ADD_DTM, KK0161.SVC_KEI_UCWK_NO, KK0161.GENE_ADD_DTM AS SKU_GENE_ADD_DTM, KK0191.GENE_ADD_DTM AS SKUT_GENE_ADD_DTM, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0191.DSL_PAUSE_TOKI_STA_YMD, KK0191.DSL_PAUSE_TOKI_END_RSYMD, KK0191.DSL_PAUSE_TOKI_END_YMD, KK0191.ITENS_OPAF_TOKI_ADD_CD, KK0191.ITENS_OPAF_TOKI_STA_YMD, KK0191.ITENS_OPAF_TOKI_END_RSYMD, KK0191.ITENS_OPAF_TOKI_END_YMD, KK0161.SVC_KEI_UCWK_STAT, KK0161.PAUSE_STP_CD, KK0161.SVC_DLRE_CD, KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO, (SELECT SAKI_KK0161.SVC_KEI_UCWK_STAT FROM KK_T_SVC_KEI_UCWK SAKI_KK0161 WHERE KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO = SAKI_KK0161.SVC_KEI_UCWK_NO AND (SAKI_KK0161.SVC_KEI_UCWK_NO, SAKI_KK0161.RSV_APLY_YMD || SAKI_KK0161.GENE_ADD_DTM) = (SELECT SAKI_KK0161_GENE.SVC_KEI_UCWK_NO, MAX(SAKI_KK0161_GENE.RSV_APLY_YMD || SAKI_KK0161_GENE.GENE_ADD_DTM) AS SAKI_KK0161_MAX FROM KK_T_SVC_KEI_UCWK SAKI_KK0161_GENE WHERE SAKI_KK0161_GENE.SVC_KEI_UCWK_NO = SAKI_KK0161.SVC_KEI_UCWK_NO AND SAKI_KK0161_GENE.RSV_APLY_YMD <= ? AND SAKI_KK0161_GENE.RSV_APLY_CD = '2' AND SAKI_KK0161_GENE.MK_FLG = '0' GROUP BY SAKI_KK0161_GENE.SVC_KEI_UCWK_NO) AND SAKI_KK0161.MK_FLG = '0') AS SAKI_SVC_KEI_UCWK_STAT FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.SVC_KEI_NO = KK0161.SVC_KEI_NO WHERE ((KK0191.ITENS_OPAF_TOKI_ADD_CD = '1' AND (KK0191.ITENS_OPAF_TOKI_END_YMD IS NULL OR KK0191.ITENS_OPAF_TOKI_END_YMD = '20991231') AND KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO = KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO) OR (KK0191.DSL_PAUSE_TOKI_ADD_CD IN ('2', '3') AND (KK0191.DSL_PAUSE_TOKI_END_YMD IS NULL OR KK0191.DSL_PAUSE_TOKI_END_YMD = '20991231')) ) AND KK0191.MK_FLG = '0' AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO) AND KK0161.MK_FLG = '0' AND KK0081.PRC_GRP_CD='10' AND KK0081.PCRS_CD IN ('A31', 'A32') AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_YMD <= ? AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.MK_FLG = '0' GROUP BY KK0081_GENE.SVC_KEI_NO) AND KK0081.MK_FLG = '0' ORDER BY CASE WHEN KK0191.ITENS_OPAF_TOKI_ADD_CD = '1' THEN 1 ELSE 2 END, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0081.SVC_KEI_NO #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_013) #--サービス契約内訳番号に紐つくカレントレコードの電話番号を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_013 SELECT SKET.TELNO FROM KK_T_SVKEIUW_EOH_TEL SKET WHERE SKET.SVC_KEI_UCWK_NO = ? AND SKET.GENE_ADD_DTM = ( SELECT MAX(SKET_SUB.GENE_ADD_DTM) AS MAX_GENE_ADD_DTM FROM KK_T_SVKEIUW_EOH_TEL SKET_SUB WHERE SKET_SUB.SVC_KEI_UCWK_NO = SKET.SVC_KEI_UCWK_NO AND SKET_SUB.RSV_APLY_CD = '2' AND SKET_SUB.MK_FLG = '0') AND SKET.RSV_APLY_CD = '2' AND SKET.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_UPDATE_001) #--番ポなし番号事前通知ファイルの作成対象となったレコードを通知済みに変更する。 #----------------------------------------------------------- --KEY:KK_UPDATE_001 UPDATE KK_T_SVKEIUW_EOH_TEL SET TEL_NO_PRE_TCHI_CD = '2', UPD_DTM = 'UPD_DTM', UPD_UNYO_YMD = 'UPD_UNYO', UPD_TRN_ID = 'UPD_TRN', UPD_OPEACNT = 'UPD_OPE' WHERE SVC_KEI_UCWK_NO = ? AND GENE_ADD_DTM = ? AND MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_014) #--指定したサービス契約番号に紐付くサービス契約内訳＜eo光電話＞情報を、電話番号順番の昇順に取得する。 #--・利用終了していない(サービス終了年月日がNULL または 未来日） #--・当日に利用終了（サービス終了年月日 ＝ 進捗年月日　または　サービスキャンセル年月日 ＝ 進捗年月日） #----------------------------------------------------------- --KEY:KK_SELECT_014 SELECT KK0191.TELNO_JUN, KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM AS KK0191_GENE_ADD_DTM, KK0191.TELNO, KK0191.UPD_UNYO_YMD, KK0161.SVC_KEI_UCWK_STAT, KK0161.SVC_ENDYMD, KK0161.SVC_CANCEL_YMD, KK0161.SVC_DLRE_CD, KK0161.RSV_APLY_YMD FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM WHERE KK0161.SVC_KEI_NO = ? AND KK0161.RSV_APLY_YMD ||KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND (((KK0161.SVC_ENDYMD IS NULL OR KK0161.SVC_ENDYMD > ?) AND KK0161.SVC_KEI_UCWK_STAT < '910') OR ((KK0161.SVC_KEI_UCWK_STAT = '910' AND KK0161.SVC_ENDYMD = ? ) OR (KK0161.SVC_KEI_UCWK_STAT = '920' AND KK0161.SVC_CANCEL_YMD = ?))) AND KK0161.KEIZK_AF_KEI_CHGECHU_FLG = '0' AND KK0161.SHOSA_YMD IS NOT NULL AND KK0161.SHOSA_DSL_FIN_CD = '1' AND KK0191.MK_FLG = '0' ORDER BY KK0191.TELNO_JUN #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_006) #--初回利用情報登録 #--サービス契約内訳＜eo光電話＞のプライマリーキー情報の取得 #----------------------------------------------------------- --KEY:KK_SELECT_006 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.SVC_KEI_UCWK_NO = ? AND KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_015) #--KKPRC00901(対象サービス契約内訳抽出) #--サービス契約内訳TBLから「番ポ有無」を取得 #----------------------------------------------------------- --KEY:KK_SELECT_015 SELECT KK0191.BMP_UM FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_016) #--KKPRC00910 #--サービス契約内訳TBLから「初回ＣＤＲ発生年月日」を取得 #----------------------------------------------------------- --KEY:KK_SELECT_016 SELECT KK0191.FIRST_CDR_HASSEI_YMD FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_017) #--サービス契約内訳番号に紐づく、サービス契約内訳＜eo光電話>の情報を複数件取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_017 SELECT KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.VA_TAKNKIKI_MODEL_CD, KTSET.VA_KIKI_CHG_NO, KTSET.VA_PORT_NO, KTSET.TELNO, KTSET.TELNO_JUN, KTSET.SVC_KEI_UCWK_NO, KTSET.BMP_UM, KTSET.GENE_ADD_DTM, KTSKU.RSV_APLY_YMD FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET WHERE KTSET.SVC_KEI_UCWK_NO = ? AND KTSKU.RSV_APLY_CD = '2' AND KTSKU.MK_FLG = '0' AND KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSKU.GENE_ADD_DTM = KTSET.GENE_ADD_DTM AND KTSKU.RSV_APLY_CD = '2' AND KTSET.MK_FLG = '0' ORDER BY KTSKU.RSV_APLY_YMD || KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_018) #--＜抽出項目＞ #--電話番号 #--＜抽出条件＞ #--サービス契約内訳番号 #--世代登録年月日時分秒 #--電話番号がNULL以外 #--番ポなし(割当通信事業者コードがK-OPT以外) #----------------------------------------------------------- --KEY:KK_SELECT_018 SELECT SVKEIUW_EOH_TEL.TELNO FROM KK_T_SVKEIUW_EOH_TEL SVKEIUW_EOH_TEL INNER JOIN ZM_M_TELNO TELNO ON SVKEIUW_EOH_TEL.TELNO = TELNO.TELNO AND SVKEIUW_EOH_TEL.SVC_KEI_UCWK_NO = ? AND SVKEIUW_EOH_TEL.GENE_ADD_DTM = ? AND SVKEIUW_EOH_TEL.TELNO IS NOT NULL AND SVKEIUW_EOH_TEL.BMP_UM = '0' AND (NOT(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_STA_YMD IS NOT NULL AND ? BETWEEN NVL(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_STA_YMD, '20991231') AND NVL(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_END_YMD, '20991231')) AND NOT(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_STA_YMD IS NOT NULL AND ? BETWEEN NVL(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_STA_YMD, '20991231') AND NVL(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_END_YMD, '20991231')) ) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_019) #---ID通知書データ抽出（守口） #--サービス契約番号に紐づくサービス契約内訳番号を電話番号順に取得する #----------------------------------------------------------- --KEY:KK_SELECT_019 SELECT KK0161.SVC_KEI_UCWK_NO ,KK0161.KEIZK_AF_KEI_CHGECHU_FLG ,KK0191.TELNO_JUN ,KK0161.KEIZK_MT_SVC_KEI_UCWK_NO ,KK0191.VA_PORT_NO FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM WHERE KK0161.SVC_KEI_NO = ? AND KK0161.SVC_KEI_UCWK_STAT NOT IN ('910', '920') AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS GENE_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' ORDER BY KK0191.TELNO_JUN ASC, KK0161.KEIZK_AF_KEI_CHGECHU_FLG DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_020) #--住所変更番号を基に移転元撤去のサービス契約内訳を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_020 SELECT SKU_TEL.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO, SKU_TEL.TELNO AS TELNO, SKU_TEL.BMP_UM AS BMP_UM FROM KK_T_SVKEIUW_EOH_TEL SKU_TEL INNER JOIN KK_T_ADCHG_DTL AD ON SKU_TEL.SVC_KEI_UCWK_NO = AD.CHBF_SKBT_NO AND AD.ADCHG_NO = ? AND AD.ADCHG_DTL_SBT_CD = '02' AND AD.MK_FLG = '0' WHERE SKU_TEL.GENE_ADD_DTM = ( SELECT MAX(SKU_TEL_GENE.GENE_ADD_DTM) MAX_GENE_ADD_DTM FROM KK_T_SVKEIUW_EOH_TEL SKU_TEL_GENE WHERE SKU_TEL.SVC_KEI_UCWK_NO = SKU_TEL_GENE.SVC_KEI_UCWK_NO AND SKU_TEL_GENE.MK_FLG = '0' ) AND AD.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_021) #--電話番号を基に番ポ工事完了日を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_021 SELECT SU_TEL.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO, B_KJ.BMP_KOJI_FIN_YMD AS BMP_KOJI_FIN_YMD FROM KK_T_SVKEIUW_EOH_TEL SU_TEL INNER JOIN TU_T_BMP_KOJI B_KJ ON SU_TEL.SVC_KEI_UCWK_NO = B_KJ.SVC_KEI_UCWK_NO AND B_KJ.MK_FLG = '0' WHERE SU_TEL.TELNO = ? AND SU_TEL.GENE_ADD_DTM = ( SELECT MAX(SU_TEL_GENE.GENE_ADD_DTM) AS SU_TEL_MAX FROM KK_T_SVKEIUW_EOH_TEL SU_TEL_GENE WHERE SU_TEL.TELNO = SU_TEL_GENE.TELNO AND SU_TEL_GENE.MK_FLG = '0' ) AND SU_TEL.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_022) #--電話番号を基にVA出荷完了年月日を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_022 SELECT SU_TEL.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO, SFD.SHUKKA_FIN_YMD AS SHUKKA_FIN_YMD FROM KK_T_SVKEIUW_EOH_TEL SU_TEL INNER JOIN DK_T_SHKA_FIN_DTL SFD ON SU_TEL.VA_TAKNKIKI_MODEL_CD = SFD.TAKNKIKI_MODEL_CD AND SFD.SVC_KEI_NO = ? AND SFD.MK_FLG = '0' WHERE SU_TEL.TELNO = ? AND SU_TEL.GENE_ADD_DTM = ( SELECT MAX(SU_TEL_GENE.GENE_ADD_DTM) AS SU_TEL_MAX FROM KK_T_SVKEIUW_EOH_TEL SU_TEL_GENE WHERE SU_TEL.TELNO = SU_TEL_GENE.TELNO AND SU_TEL_GENE.MK_FLG = '0' ) AND SU_TEL.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_023) #--初回CDR発生年月日を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_023 SELECT SU_TEL.FIRST_CDR_HASSEI_YMD FROM KK_T_SVKEIUW_EOH_TEL SU_TEL WHERE SU_TEL.TELNO = ? AND SU_TEL.GENE_ADD_DTM = ( SELECT MAX(SU_TEL_GENE.GENE_ADD_DTM) AS SU_TEL_MAX FROM KK_T_SVKEIUW_EOH_TEL SU_TEL_GENE WHERE SU_TEL.TELNO = SU_TEL_GENE.TELNO AND SU_TEL_GENE.MK_FLG = '0' ) AND SU_TEL.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_024) #--住所変更番号を基に移転先新設のサービス契約内訳を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_024 SELECT SKU_TEL.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO, SKU_TEL.TELNO AS TELNO, SKU_TEL.BMP_UM AS BMP_UM FROM KK_T_SVKEIUW_EOH_TEL SKU_TEL INNER JOIN KK_T_ADCHG_DTL AD ON SKU_TEL.SVC_KEI_UCWK_NO = AD.CHAF_SKBT_NO AND AD.ADCHG_NO = ? AND AD.ADCHG_DTL_SBT_CD = '02' AND AD.MK_FLG = '0' WHERE SKU_TEL.GENE_ADD_DTM = ( SELECT MAX(SKU_TEL_GENE.GENE_ADD_DTM) MAX_GENE_ADD_DTM FROM KK_T_SVKEIUW_EOH_TEL SKU_TEL_GENE WHERE SKU_TEL.SVC_KEI_UCWK_NO = SKU_TEL_GENE.SVC_KEI_UCWK_NO AND SKU_TEL_GENE.MK_FLG = '0' ) AND AD.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_026) #--サービス契約番号と電話番号から同番移転をチェック #----------------------------------------------------------- --KEY:KK_SELECT_026 SELECT 1 FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN ( SELECT KK0161.SVC_KEI_UCWK_NO ,KK0161.GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KK0161 WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_G.RSV_APLY_YMD || KK0161_G.GENE_ADD_DTM) AS GENE_MAX FROM KK_T_SVC_KEI_UCWK KK0161_G WHERE KK0161_G.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_G.RSV_APLY_YMD <= ? AND KK0161_G.RSV_APLY_CD = '2' AND KK0161_G.MK_FLG = '0')) KK0161_KEY ON KK0191.SVC_KEI_UCWK_NO = KK0161_KEY.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161_KEY.GENE_ADD_DTM WHERE KK0191.DOBANITEN_MSKM_UM = '1' AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' AND EXISTS ( SELECT 1 FROM TU_T_DOBANITEN TU0021 WHERE TU0021.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND TU0021.DOBANITEN_STAT = '050' AND TU0021.GENE_ADD_DTM = ( SELECT MAX(TU0021.GENE_ADD_DTM) AS TU0021_MAX FROM TU_T_DOBANITEN TU0021_GENE WHERE TU0021_GENE.SVC_KEI_UCWK_NO = TU0021.SVC_KEI_UCWK_NO AND TU0021_GENE.MK_FLG = '0')) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_027) #--サービス契約内訳番号から同番移転をチェック #----------------------------------------------------------- --KEY:KK_SELECT_027 SELECT 1 FROM TU_T_DOBANITEN TU0021 WHERE TU0021.SVC_KEI_UCWK_NO = ? AND TU0021.DOBANITEN_STAT = '050' AND TU0021.GENE_ADD_DTM = ( SELECT MAX(TU0021.GENE_ADD_DTM) AS GENE_MAX FROM TU_T_DOBANITEN TU0021_GENE WHERE TU0021_GENE.DOBANITEN_NO = TU0021.DOBANITEN_NO AND TU0021_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_031) #--【廃止住所データ取得】 #--サービス契約番号、緊急通報住所コードを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_031 SELECT KK0081.SYSID, KK0161.SVC_KEI_NO, KK0191.EMG_AD_CD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND KK0161.SVC_KEI_UCWK_STAT != '910' AND KK0161.SVC_KEI_UCWK_STAT != '920' AND KK0161.MK_FLG = '0' INNER JOIN KK_T_SVC_KEI KK0081 ON KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081.GENE_ADD_DTM = (SELECT MAX(KK0081_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.MK_FLG = '0') AND KK0081.MK_FLG = '0' WHERE KK0191.EMG_AD_CD = ? AND KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') AND KK0191.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_032) #--【変更住所データ取得】 #--サービス契約番号、緊急通報住所コードを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_032 SELECT KK0081.SYSID, KK0161.SVC_KEI_NO, KK0191.SVC_KEI_UCWK_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK2661.AD_CD, KK2661.PCD, KK2661.STATE_NM, KK2661.CITY_NM, KK2661.OAZTSU_NM, KK2661.AZCHO_NM, KK2661.AD_SHUSEI_CD, KK2661.NEW_AD_CD, KK2661.NEW_PCD, KK2661.NEW_STATE_NM, KK2661.NEW_CITY_NM, KK2661.NEW_OAZTSU_NM, KK2661.NEW_AZCHO_NM, KK2661.AD_CHG_SBT_CD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081 ON KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081.GENE_ADD_DTM = (SELECT MAX(KK0081_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.MK_FLG = '0') AND KK0081.MK_FLG = '0' INNER JOIN KK_T_CHGTGAD_CD_WK KK2661 ON KK0191.EMG_AD_CD = KK2661.AD_CD AND KK2661.CHG_AD_TRN_STAT_CD = '0' WHERE KK0191.EMG_AD_CD = ? AND KK0191.GENE_ADD_DTM = (SELECT MAX(KK0191_GENE.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_039) #--指示書登録時の電話番号順番取得（電話） #----------------------------------------------------------- --KEY:KK_SELECT_039 SELECT KK0191.TELNO_JUN FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM AND KK0161.RSV_APLY_CD = '2' AND KK0161.MK_FLG = '0' WHERE KK0161.SVC_KEI_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_040) #--サービス契約番号に紐づく、サービス契約内訳＜eo光電話>のサービス内訳番号と判定に必要な項目を取得する。 #--KK14901サービスオーダ送信依頼 #----------------------------------------------------------- --KEY:KK_SELECT_040 SELECT KTSET.SVC_KEI_UCWK_NO, KTSET.GENE_ADD_DTM, KTSET.BMP_UM, KTSET.DSL_PAUSE_TOKI_KIBO_UM, KTSET.ITENS_OPAF_TOKI_KIBO_UM, KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.TELNO_JUN, KTSET.VA_KIKI_CHG_NO, KTSET.VA_PORT_NO, KTSKU.SVC_KEI_UCWK_STAT, KK0081.PCRS_CD, KTSKU.RSV_APLY_YMD, KTSKU.KEIZK_MT_SVC_KEI_UCWK_NO, KTSKU.KEIZK_AF_KEI_CHGECHU_FLG FROM KK_T_SVC_KEI_UCWK KTSKU JOIN KK_T_SVKEIUW_EOH_TEL KTSET ON KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSKU.GENE_ADD_DTM = KTSET.GENE_ADD_DTM JOIN KK_T_SVC_KEI KK0081 ON KK0081.SVC_KEI_NO = KTSKU.SVC_KEI_NO WHERE KTSKU.SVC_KEI_NO = ? AND KTSKU.RSV_APLY_YMD || KTSKU.GENE_ADD_DTM = ( SELECT MAX(G.RSV_APLY_YMD || G.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK G WHERE G.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND G.RSV_APLY_YMD <= ? AND G.RSV_APLY_CD = '2' AND G.MK_FLG = '0' GROUP BY G.SVC_KEI_UCWK_NO ) AND (KTSKU.SVC_KEI_UCWK_STAT != '910' OR KTSKU.IDO_DIV != '00042') AND (KTSKU.SVC_KEI_UCWK_STAT != '920' OR KTSKU.IDO_DIV > '00002') AND KTSET.RSV_APLY_CD = '2' AND KTSET.MK_FLG = '0' AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MAX(KK0081G.RSV_APLY_YMD || KK0081G.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081G WHERE KK0081G.SVC_KEI_NO = KTSKU.SVC_KEI_NO AND KK0081G.RSV_APLY_YMD <= ? AND KK0081G.RSV_APLY_CD = '2' AND KK0081G.MK_FLG = '0' GROUP BY KK0081G.SVC_KEI_NO ) ORDER BY KTSET.TELNO_JUN, KTSKU.SVC_KEI_UCWK_STAT, KTSKU.RSV_APLY_YMD DESC, KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_041) #--条件で指定したサービス契約内訳番号のＶＡ機器変更番号を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_041 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.TELNO, KK0191.TELNO_JUN, KK0191.VA_KIKI_CHG_NO FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_NO = ? AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) IN ( SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_NO = KK0161.SVC_KEI_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO ) AND KK0161.SVC_KEI_UCWK_STAT IN ('010','020','030','100','210','220') AND KK0161.SHOSA_YMD = KK0161.SHOSA_YMD ORDER BY KK0161.SHOSA_YMD DESC, KK0191.TELNO_JUN DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_042) #--KKPRC00909 #--サービス契約内訳のカレント情報のキーと番ポ有無を取得 #----------------------------------------------------------- --KEY:KK_SELECT_042 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.BMP_UM FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_043) #--KKPRC00902 #--サービス契約内訳番号に紐付く電話番号を取得します。 #----------------------------------------------------------- --KEY:KK_SELECT_043 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.TELNO FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_044) #--KKPRC00902 #--VA機器変更番号が設定されている最古のレコード取得 #----------------------------------------------------------- --KEY:KK_SELECT_044 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.VA_KIKI_CHG_NO, KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ON KK0191.VA_KIKI_CHG_NO = KK0341.KIKI_CHG_NO AND KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX FROM KK_T_KKTK_SVC_KEI KK0341_GENE WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_GENE.RSV_APLY_YMD <= ? AND KK0341_GENE.RSV_APLY_CD = '2' AND KK0341_GENE.MK_FLG = '0') WHERE KK0191.SVC_KEI_UCWK_NO = ? AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' ORDER BY KK0191.GENE_ADD_DTM #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_045) #--KKPRC02202 #--サービス契約回線内訳番号から電話サービスを取得 #----------------------------------------------------------- --KEY:KK_SELECT_045 SELECT KK0081.SVC_KEI_NO, KK0161.SVC_KEI_UCWK_NO, KK0161.GENE_ADD_DTM, KK0191.VA_KIKI_CHG_NO FROM KK_T_KAISEN_TG_SVKEI KK0241 INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO AND KK0081.SVC_CD = '02' AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_YMD <= ? AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0161.SVC_KEI_UCWK_STAT = '100' AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM WHERE KK0241.SVC_KEI_KAISEN_UCWK_NO = ? AND KK0241.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_INSERT_004) #----------------------------------------------------------- --KEY:KK_INSERT_004 INSERT INTO KK_T_SVKEIUW_EOH_TEL( SVC_KEI_UCWK_NO, GENE_ADD_DTM, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, UPD_DTM, UPD_OPEACNT, DEL_DTM, DEL_OPEACNT, MK_FLG, ADD_UNYO_YMD, ADD_TRN_ID, UPD_UNYO_YMD, UPD_TRN_ID, DEL_UNYO_YMD, DEL_TRN_ID, DOBANITEN_TTDK_JSSIS_SKCD, DBNITN_KOPT_NAI_SKEKKA_CD, DBNITN_AT_RNKKH_CST_ZM_FLG ) SELECT KK0191.SVC_KEI_UCWK_NO, ?, KK0191.TELNO, KK0191.DAIHYO_TELNO, KK0191.TELNO_USE_PLACE_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK0191.EMG_KSH_KSITEI_FLG, KK0191.EMG_KSH_NM, KK0191.EMG_KSH_KANA, KK0191.SIP_USER_ID, KK0191.SIP_USER_ID_PWD, KK0191.SIP_USER_ID_DEL_ZUMI_FLG, KK0191.FIRST_CDR_HASSEI_YMD, KK0191.TELNO_JUN, KK0191.NTT_NO_GUIDE_UM, KK0191.HELLOPAGE_KEISAI_UM, KK0191.TUWA_DTL_KOKAI_YH, KK0191.TEL_NO_PRE_TCHI_CD, KK0191.NTT_KEISHA_AD_CD, KK0191.NTT_KEISHA_PCD, KK0191.NTT_KEISHA_STATE_NM, KK0191.NTT_KEISHA_CITY_NM, KK0191.NTT_KEISHA_OAZTSU_NM, KK0191.NTT_KEISHA_AZCHO_NM, KK0191.NTT_KEISHA_BNCHIGO, KK0191.NTT_KEISHA_ADRTTM, KK0191.NTT_KEISHA_ADRRM, KK0191.NKYS_AD_MAN_INPUT_FLG, KK0191.NTT_KSH_KANA, KK0191.NTT_KEISHA_NM, KK0191.NTT_KSH_AD_KSH_AD_SAI_FLG, KK0191.TSJGS_CD, KK0191.ITNM_TSJGS_KTN_CD, KK0191.DOJI_OPEN_KIBO_CD, KK0191.TEL_TTDKI_MSKM_CD, KK0191.MAN_SWITCH_RSV_DTM, KK0191.SHK_TEL_OP_SETTE_RMTS_PWD, KK0191.TEL_OP_SETTE_RMTS_PWD, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0191.DSL_PAUSE_TOKI_SBT_CD, KK0191.DSL_PAUSE_TOKI_KIBO_UM, KK0191.DSL_PAUSE_TOKI_STA_RSYMD, KK0191.DSL_PAUSE_TOKI_STA_YMD, KK0191.DSL_PAUSE_TOKI_END_RSYMD, KK0191.DSL_PAUSE_TOKI_END_YMD, KK0191.PAUSE_TOKI_END_SBT_CD, KK0191.DSL_PAUSE_TOKI_TSS_TLN, KK0191.DSL_PAUSE_TOKI_TSS_SVKUWNO, KK0191.ITENS_OPAF_TOKI_ADD_CD, KK0191.ITENS_OPAF_TOKI_SBT_CD, KK0191.ITENS_OPAF_TOKI_KIBO_UM, KK0191.ITENS_OPAF_TOKI_STA_RSYMD, KK0191.ITENS_OPAF_TOKI_STA_YMD, KK0191.ITENS_OPAF_TOKI_END_RSYMD, KK0191.ITENS_OPAF_TOKI_END_YMD, KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO, KK0191.TOWNPAGE_KEISAI_KANA, KK0191.TOWNPAGE_KEISAI_NM, KK0191.BMP_UM, KK0191.BMP_AF_USE_PLACE_SBT_CD, KK0191.BMP_DOJI_KOJI_KIBO_UM, KK0191.BMP_KOJI_KIBO_YMD, KK0191.BMPKJ_KIBO_TIME_CD, KK0191.BMP_DOJI_OPEN_YMD, KK0191.DOBANITEN_MSKM_UM, KK0191.DOBANITEN_REQ_CTRL_CD, KK0191.DOBANITEN_JDG_CD, KK0191.DBANITEN_MSKMJIG_KIJIRAN_1, KK0191.DBANITEN_MSKMJIG_KIJIRAN_2, KK0191.DOBANITEN_NTT_KEISHA_NM, KK0191.DOBANITEN_NTT_KSH_KANA, KK0191.VA_TAKNKIKI_MODEL_CD, KK0191.VA_KIKI_SEIZO_NO, KK0191.VA_KIKI_CHG_NO, KK0191.VA_PORT_NO, KK0191.RSV_APLY_CD, KK0191.ADD_DTM, KK0191.ADD_OPEACNT, 'UPD_DTM', 'UPD_OPE', NULL, NULL, '0', KK0191.ADD_UNYO_YMD, KK0191.ADD_TRN_ID, 'UPD_UNYO', 'UPD_TRN', NULL, NULL, KK0191.DOBANITEN_TTDK_JSSIS_SKCD, KK0191.DBNITN_KOPT_NAI_SKEKKA_CD, KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.SVC_KEI_UCWK_NO = ? AND KK0191.GENE_ADD_DTM = ( SELECT MAX(KK0191_GENE.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.MK_FLG = '0') AND KK0191.MK_FLG = '0' #- --KEY:KK_UPDATE_002 UPDATE KK_T_SVKEIUW_EOH_TEL SET EMG_AD_CD = ? , EMG_HOSEI_PCD = ? , EMG_STATE_NM = ? , EMG_CITY_NM = ? , EMG_OAZTSU_NM = ? , EMG_AZCHO_NM = ? WHERE SVC_KEI_UCWK_NO = ? AND GENE_ADD_DTM = ? #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_034) #--加入御礼管理データ抽出 #--サービス契約番号に紐づくサービス契約内訳に電話番号追加が過去にあったかチェック #----------------------------------------------------------- --KEY:KK_SELECT_034 SELECT KK0161.IDO_DIV FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_NO = ? AND KK0161.IDO_DIV = '00041' AND KK0161.RSV_APLY_CD = '2' AND KK0161.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_035) #--加入御礼書発行 #--電話登録種別判定材料抽出SQL 有効な世代をすべて抽出する #----------------------------------------------------------- --KEY:KK_SELECT_035 SELECT KK0161.IDO_DIV ,KK0191.TELNO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD <= ? AND KK0161.RSV_APLY_CD = '2' AND KK0161.MK_FLG = '0' AND KK0191.RSV_APLY_CD = '2' AND KK0191.MK_FLG = '0' ORDER BY KK0161.RSV_APLY_YMD DESC, KK0161.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_036) #--KKPRC36503 #--サービス契約内訳番号、電話番号よりVA宅内機器型式コード、VA機器変更番号を取得 #----------------------------------------------------------- --KEY:KK_SELECT_036 SELECT KK0191.VA_TAKNKIKI_MODEL_CD, KK0191.VA_KIKI_CHG_NO FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.SVC_KEI_UCWK_NO = ? AND KK0191.TELNO = ? AND NVL(KK0191.VA_TAKNKIKI_MODEL_CD,'0') > '0' AND NVL(KK0191.VA_KIKI_CHG_NO,'0') > '0' AND KK0191.MK_FLG = '0' GROUP BY KK0191.VA_TAKNKIKI_MODEL_CD,KK0191.VA_KIKI_CHG_NO #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_037) #--消去対象SIPユーザーIDの抽出 #--＜抽出項目＞ #--SIPユーザーID #--＜抽出条件＞ #--サービス契約内訳番号 #--世代登録年月日時分秒 #--SIPユーザーIDがNULL以外 #----------------------------------------------------------- --KEY:KK_SELECT_037 SELECT SVKEIUW_EOH_TEL.SIP_USER_ID FROM KK_T_SVKEIUW_EOH_TEL SVKEIUW_EOH_TEL WHERE SVKEIUW_EOH_TEL.SVC_KEI_UCWK_NO = ? AND SVKEIUW_EOH_TEL.GENE_ADD_DTM = ? AND SVKEIUW_EOH_TEL.SIP_USER_ID IS NOT NULL AND (NOT(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_STA_YMD IS NOT NULL AND ? BETWEEN NVL(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_STA_YMD, '20991231') AND NVL(SVKEIUW_EOH_TEL.DSL_PAUSE_TOKI_END_YMD, '20991231')) AND NOT(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_STA_YMD IS NOT NULL AND ? BETWEEN NVL(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_STA_YMD, '20991231') AND NVL(SVKEIUW_EOH_TEL.ITENS_OPAF_TOKI_END_YMD, '20991231')) ) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-DK_INSERT_001) #--サービス契約内訳＜ｅｏ光電話＞のカレントレコードをベースにVA宅内機器型式コードを登録する #----------------------------------------------------------- --KEY:DK_INSERT_001 INSERT INTO KK_T_SVKEIUW_EOH_TEL( SVC_KEI_UCWK_NO, GENE_ADD_DTM, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, UPD_DTM, UPD_OPEACNT, DEL_DTM, DEL_OPEACNT, MK_FLG, ADD_UNYO_YMD, ADD_TRN_ID, UPD_UNYO_YMD, UPD_TRN_ID, DEL_UNYO_YMD, DEL_TRN_ID ) SELECT SVC_KEI_UCWK_NO, ?, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, ?, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, 'UPD_DTM', 'UPD_OPE', NULL, NULL, '0', ADD_UNYO_YMD, ADD_TRN_ID, 'UPD_UNYO', 'UPD_TRN', NULL, NULL FROM KK_T_SVKEIUW_EOH_TEL WHERE SVC_KEI_UCWK_NO = ? AND GENE_ADD_DTM = ? #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_046) #--KKPRC02204 #--サービス契約番号に紐付く電話サービスの電話番号順を取得 #----------------------------------------------------------- --KEY:KK_SELECT_046 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.TELNO, KK0191.TELNO_JUN FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ON KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM AND KK0191.MK_FLG = '0' WHERE KK0161.SVC_KEI_NO = ? AND KK0161.SVC_KEI_UCWK_STAT = '100' AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.MK_FLG = '0' GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO ) AND KK0161.KEIZK_AF_KEI_CHGECHU_FLG = '0' ORDER BY KK0161.SVC_KEI_UCWK_STAT ASC , KK0161.SVC_STA_YMD ASC , KK0191.VA_PORT_NO ASC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_047) #--サービス契約内訳番号に紐づく、サービス契約内訳＜eo光電話>のサービス内訳番号と判定に必要な項目を取得する。 #--KK14901サービスオーダ送信依頼 #----------------------------------------------------------- --KEY:KK_SELECT_047 SELECT KTSET.SVC_KEI_UCWK_NO, KTSET.GENE_ADD_DTM, KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.TELNO_JUN, KTSET.VA_KIKI_CHG_NO, KTSET.VA_PORT_NO, KTSKU.SVC_KEI_UCWK_STAT, KTSKU.RSV_APLY_YMD, KTSKU.KEIZK_MT_SVC_KEI_UCWK_NO, KTSKU.KEIZK_AF_KEI_CHGECHU_FLG FROM KK_T_SVC_KEI_UCWK KTSKU JOIN KK_T_SVKEIUW_EOH_TEL KTSET ON KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSKU.GENE_ADD_DTM = KTSET.GENE_ADD_DTM WHERE KTSKU.SVC_KEI_UCWK_NO = ? AND (KTSKU.SVC_KEI_UCWK_NO, KTSKU.RSV_APLY_YMD || KTSKU.GENE_ADD_DTM) = ( SELECT G.SVC_KEI_UCWK_NO, MAX(G.RSV_APLY_YMD || G.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK G WHERE G.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND G.RSV_APLY_YMD <= ? AND G.RSV_APLY_CD = '2' AND G.MK_FLG = '0' GROUP BY G.SVC_KEI_UCWK_NO ) ORDER BY KTSET.TELNO_JUN, KTSKU.SVC_KEI_UCWK_STAT, KTSKU.RSV_APLY_YMD DESC, KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_048) #--サービス契約番号に紐づく、サービス契約内訳＜eo光電話>のサービス内訳番号と判定に必要な項目を取得する。 #--(サービス契約内訳ステータスが010:受付済、910:解約済、920:キャンセル済を除く) #--KK14901サービスオーダ送信依頼 #----------------------------------------------------------- --KEY:KK_SELECT_048 SELECT KTSET.SVC_KEI_UCWK_NO, KTSET.GENE_ADD_DTM, KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.TELNO, KTSET.TELNO_JUN, KTSET.VA_KIKI_CHG_NO, KTSET.VA_PORT_NO, KTSKU.SVC_KEI_UCWK_STAT, KTSKU.RSV_APLY_YMD, KTSKU.KEIZK_MT_SVC_KEI_UCWK_NO, KTSKU.KEIZK_AF_KEI_CHGECHU_FLG FROM KK_T_SVC_KEI_UCWK KTSKU JOIN KK_T_SVKEIUW_EOH_TEL KTSET ON KTSKU.SVC_KEI_UCWK_NO = KTSET.SVC_KEI_UCWK_NO AND KTSKU.GENE_ADD_DTM = KTSET.GENE_ADD_DTM WHERE KTSKU.SVC_KEI_NO= ? AND KTSKU.SVC_KEI_UCWK_NO != ? AND KTSKU.SVC_KEI_UCWK_STAT IN ('020','030','100','210','220') AND KTSKU.KEIZK_AF_KEI_CHGECHU_FLG != '1' AND (KTSKU.SVC_KEI_UCWK_NO, KTSKU.RSV_APLY_YMD || KTSKU.GENE_ADD_DTM) = ( SELECT G.SVC_KEI_UCWK_NO, MAX(G.RSV_APLY_YMD || G.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK G WHERE G.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND G.RSV_APLY_YMD <= ? AND G.RSV_APLY_CD = '2' AND G.MK_FLG = '0' GROUP BY G.SVC_KEI_UCWK_NO ) AND KTSET.VA_KIKI_CHG_NO = ? ORDER BY KTSET.TELNO_JUN, KTSKU.SVC_KEI_UCWK_STAT, KTSKU.RSV_APLY_YMD DESC, KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_049) #--【変更住所データ取得】 #--サービス契約番号、緊急通報住所コードを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_049 SELECT KK0081.SYSID, KK0161.SVC_KEI_NO, KK0191.SVC_KEI_UCWK_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK2661.AD_CD, KK2661.PCD, KK2661.STATE_NM, KK2661.CITY_NM, KK2661.OAZTSU_NM, KK2661.AZCHO_NM, KK2661.AD_SHUSEI_CD, KK2661.NEW_AD_CD, KK2661.NEW_PCD, KK2661.NEW_STATE_NM, KK2661.NEW_CITY_NM, KK2661.NEW_OAZTSU_NM, KK2661.NEW_AZCHO_NM, KK2661.AD_CHG_SBT_CD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM INNER JOIN KK_T_SVC_KEI KK0081 ON KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO INNER JOIN KK_T_CHGTGAD_CD_WK KK2661 ON KK0191.EMG_AD_CD = KK2661.AD_CD WHERE KK2661.CHG_AD_TRN_STAT_CD = '0' AND KK0191.EMG_AD_CD = ? AND (KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = (SELECT MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.RSV_APLY_YMD <= ? AND KK0081_GENE.MK_FLG = '0') AND ( (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.MK_FLG = '0' ) OR (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD IN ('1','2') AND KK0161_GENE.RSV_APLY_YMD > ? AND KK0161_GENE.MK_FLG = '0' ) ) AND KK2661.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_050) #--【変更住所データ取得】 #--サービス契約内訳番号、緊急連絡先住所コードでカレントと未来予約の情報を取得します。 #----------------------------------------------------------- --KEY:KK_SELECT_050 SELECT KK0191.SVC_KEI_UCWK_NO ,KK0191.GENE_ADD_DTM ,KK0161.RSV_APLY_YMD ,KK0161.RSV_APLY_CD FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0191.EMG_AD_CD = ? AND ( (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.MK_FLG = '0' ) OR (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_CD IN ('1','2') AND KK0161_GENE.RSV_APLY_YMD > ? AND KK0161_GENE.MK_FLG = '0' ) ) #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_INSERT_005) #----------------------------------------------------------- --KEY:KK_INSERT_005 INSERT INTO KK_T_SVKEIUW_EOH_TEL( SVC_KEI_UCWK_NO, GENE_ADD_DTM, TELNO, DAIHYO_TELNO, TELNO_USE_PLACE_NO, EMG_AD_CD, EMG_HOSEI_PCD, EMG_STATE_NM, EMG_CITY_NM, EMG_OAZTSU_NM, EMG_AZCHO_NM, EMG_BNCHIGO, EMG_ADRTTM, EMG_ADRRM, EMG_KSH_KSITEI_FLG, EMG_KSH_NM, EMG_KSH_KANA, SIP_USER_ID, SIP_USER_ID_PWD, SIP_USER_ID_DEL_ZUMI_FLG, FIRST_CDR_HASSEI_YMD, TELNO_JUN, NTT_NO_GUIDE_UM, HELLOPAGE_KEISAI_UM, TUWA_DTL_KOKAI_YH, TEL_NO_PRE_TCHI_CD, NTT_KEISHA_AD_CD, NTT_KEISHA_PCD, NTT_KEISHA_STATE_NM, NTT_KEISHA_CITY_NM, NTT_KEISHA_OAZTSU_NM, NTT_KEISHA_AZCHO_NM, NTT_KEISHA_BNCHIGO, NTT_KEISHA_ADRTTM, NTT_KEISHA_ADRRM, NKYS_AD_MAN_INPUT_FLG, NTT_KSH_KANA, NTT_KEISHA_NM, NTT_KSH_AD_KSH_AD_SAI_FLG, TSJGS_CD, ITNM_TSJGS_KTN_CD, DOJI_OPEN_KIBO_CD, TEL_TTDKI_MSKM_CD, MAN_SWITCH_RSV_DTM, SHK_TEL_OP_SETTE_RMTS_PWD, TEL_OP_SETTE_RMTS_PWD, DSL_PAUSE_TOKI_ADD_CD, DSL_PAUSE_TOKI_SBT_CD, DSL_PAUSE_TOKI_KIBO_UM, DSL_PAUSE_TOKI_STA_RSYMD, DSL_PAUSE_TOKI_STA_YMD, DSL_PAUSE_TOKI_END_RSYMD, DSL_PAUSE_TOKI_END_YMD, PAUSE_TOKI_END_SBT_CD, DSL_PAUSE_TOKI_TSS_TLN, DSL_PAUSE_TOKI_TSS_SVKUWNO, ITENS_OPAF_TOKI_ADD_CD, ITENS_OPAF_TOKI_SBT_CD, ITENS_OPAF_TOKI_KIBO_UM, ITENS_OPAF_TOKI_STA_RSYMD, ITENS_OPAF_TOKI_STA_YMD, ITENS_OPAF_TOKI_END_RSYMD, ITENS_OPAF_TOKI_END_YMD, ITNS_OPAF_TOKI_TSS_SVKUWNO, TOWNPAGE_KEISAI_KANA, TOWNPAGE_KEISAI_NM, BMP_UM, BMP_AF_USE_PLACE_SBT_CD, BMP_DOJI_KOJI_KIBO_UM, BMP_KOJI_KIBO_YMD, BMPKJ_KIBO_TIME_CD, BMP_DOJI_OPEN_YMD, DOBANITEN_MSKM_UM, DOBANITEN_REQ_CTRL_CD, DOBANITEN_JDG_CD, DBANITEN_MSKMJIG_KIJIRAN_1, DBANITEN_MSKMJIG_KIJIRAN_2, DOBANITEN_NTT_KEISHA_NM, DOBANITEN_NTT_KSH_KANA, VA_TAKNKIKI_MODEL_CD, VA_KIKI_SEIZO_NO, VA_KIKI_CHG_NO, VA_PORT_NO, RSV_APLY_CD, ADD_DTM, ADD_OPEACNT, UPD_DTM, UPD_OPEACNT, DEL_DTM, DEL_OPEACNT, MK_FLG, ADD_UNYO_YMD, ADD_TRN_ID, UPD_UNYO_YMD, UPD_TRN_ID, DEL_UNYO_YMD, DEL_TRN_ID, DOBANITEN_TTDK_JSSIS_SKCD, DBNITN_KOPT_NAI_SKEKKA_CD, DBNITN_AT_RNKKH_CST_ZM_FLG ) SELECT KK0191.SVC_KEI_UCWK_NO, ?, KK0191.TELNO, KK0191.DAIHYO_TELNO, KK0191.TELNO_USE_PLACE_NO, KK0191.EMG_AD_CD, KK0191.EMG_HOSEI_PCD, KK0191.EMG_STATE_NM, KK0191.EMG_CITY_NM, KK0191.EMG_OAZTSU_NM, KK0191.EMG_AZCHO_NM, KK0191.EMG_BNCHIGO, KK0191.EMG_ADRTTM, KK0191.EMG_ADRRM, KK0191.EMG_KSH_KSITEI_FLG, KK0191.EMG_KSH_NM, KK0191.EMG_KSH_KANA, KK0191.SIP_USER_ID, KK0191.SIP_USER_ID_PWD, KK0191.SIP_USER_ID_DEL_ZUMI_FLG, KK0191.FIRST_CDR_HASSEI_YMD, KK0191.TELNO_JUN, KK0191.NTT_NO_GUIDE_UM, KK0191.HELLOPAGE_KEISAI_UM, KK0191.TUWA_DTL_KOKAI_YH, KK0191.TEL_NO_PRE_TCHI_CD, KK0191.NTT_KEISHA_AD_CD, KK0191.NTT_KEISHA_PCD, KK0191.NTT_KEISHA_STATE_NM, KK0191.NTT_KEISHA_CITY_NM, KK0191.NTT_KEISHA_OAZTSU_NM, KK0191.NTT_KEISHA_AZCHO_NM, KK0191.NTT_KEISHA_BNCHIGO, KK0191.NTT_KEISHA_ADRTTM, KK0191.NTT_KEISHA_ADRRM, KK0191.NKYS_AD_MAN_INPUT_FLG, KK0191.NTT_KSH_KANA, KK0191.NTT_KEISHA_NM, KK0191.NTT_KSH_AD_KSH_AD_SAI_FLG, KK0191.TSJGS_CD, KK0191.ITNM_TSJGS_KTN_CD, KK0191.DOJI_OPEN_KIBO_CD, KK0191.TEL_TTDKI_MSKM_CD, KK0191.MAN_SWITCH_RSV_DTM, KK0191.SHK_TEL_OP_SETTE_RMTS_PWD, KK0191.TEL_OP_SETTE_RMTS_PWD, KK0191.DSL_PAUSE_TOKI_ADD_CD, KK0191.DSL_PAUSE_TOKI_SBT_CD, KK0191.DSL_PAUSE_TOKI_KIBO_UM, KK0191.DSL_PAUSE_TOKI_STA_RSYMD, KK0191.DSL_PAUSE_TOKI_STA_YMD, KK0191.DSL_PAUSE_TOKI_END_RSYMD, KK0191.DSL_PAUSE_TOKI_END_YMD, KK0191.PAUSE_TOKI_END_SBT_CD, KK0191.DSL_PAUSE_TOKI_TSS_TLN, KK0191.DSL_PAUSE_TOKI_TSS_SVKUWNO, KK0191.ITENS_OPAF_TOKI_ADD_CD, KK0191.ITENS_OPAF_TOKI_SBT_CD, KK0191.ITENS_OPAF_TOKI_KIBO_UM, KK0191.ITENS_OPAF_TOKI_STA_RSYMD, KK0191.ITENS_OPAF_TOKI_STA_YMD, KK0191.ITENS_OPAF_TOKI_END_RSYMD, KK0191.ITENS_OPAF_TOKI_END_YMD, KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO, KK0191.TOWNPAGE_KEISAI_KANA, KK0191.TOWNPAGE_KEISAI_NM, KK0191.BMP_UM, KK0191.BMP_AF_USE_PLACE_SBT_CD, KK0191.BMP_DOJI_KOJI_KIBO_UM, KK0191.BMP_KOJI_KIBO_YMD, KK0191.BMPKJ_KIBO_TIME_CD, KK0191.BMP_DOJI_OPEN_YMD, KK0191.DOBANITEN_MSKM_UM, KK0191.DOBANITEN_REQ_CTRL_CD, KK0191.DOBANITEN_JDG_CD, KK0191.DBANITEN_MSKMJIG_KIJIRAN_1, KK0191.DBANITEN_MSKMJIG_KIJIRAN_2, KK0191.DOBANITEN_NTT_KEISHA_NM, KK0191.DOBANITEN_NTT_KSH_KANA, KK0191.VA_TAKNKIKI_MODEL_CD, KK0191.VA_KIKI_SEIZO_NO, KK0191.VA_KIKI_CHG_NO, KK0191.VA_PORT_NO, KK0191.RSV_APLY_CD, KK0191.ADD_DTM, KK0191.ADD_OPEACNT, 'UPD_DTM', 'UPD_OPE', NULL, NULL, '0', KK0191.ADD_UNYO_YMD, KK0191.ADD_TRN_ID, 'UPD_UNYO', 'UPD_TRN', NULL, NULL, KK0191.DOBANITEN_TTDK_JSSIS_SKCD, KK0191.DBNITN_KOPT_NAI_SKEKKA_CD, KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.SVC_KEI_UCWK_NO = ? AND KK0191.GENE_ADD_DTM = ? #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_051) #--KKPRC40102 #--機器変更番号に電話番号が紐付いているかどうかチェックする。 #----------------------------------------------------------- --KEY:KK_SELECT_051 SELECT 1 FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE (KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM) IN ( SELECT KK0161.SVC_KEI_UCWK_NO, KK0161.GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KK0161 WHERE KK0161.SVC_KEI_NO = (SELECT KK0241.SVC_KEI_NO FROM KK_T_KAISEN_TG_SVKEI KK0241 WHERE KK0241.SVC_KEI_NO = (SELECT KU0081.SVC_KEI_NO FROM KU_T_SVKEI_KOJIAK KU0081 WHERE KU0081.KOJIAK_NO = ? AND KU0081.SVC_CD = '02' AND ? BETWEEN KU0081.SVKEI_KOJIAK_TSTAYMD AND KU0081.SVKEI_KOJIAK_TENDYMD AND KU0081.MK_FLG = '0') AND ? BETWEEN KK0241.KAISEN_UCWK_USE_STAYMD AND KK0241.KAISEN_UCWK_USE_ENDYMD AND KK0241.MK_FLG = '0') AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0') AND KK0161.SVC_KEI_UCWK_STAT IN ('010','020','030','100','210','220') ) AND KK0191.VA_KIKI_CHG_NO = ? AND KK0191.MK_FLG = '0' #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_051) #--オーダ送信処理専用、EG切替時独有の情報を取得します。 #----------------------------------------------------------- --KEY:KK_SELECT_052 SELECT KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.VA_PORT_NO, KTSET.TELNO, KTSET.BMP_UM, KTSKU.SVC_KEI_UCWK_NO, KTSKU.GENE_ADD_DTM as SKU_GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET ,KK_T_KKTK_SVC_KEI KK0341 WHERE KTSKU.SVC_KEI_UCWK_NO = ? AND KTSKU.SVC_KEI_UCWK_STAT >= '020' AND KTSKU.SVC_KEI_UCWK_STAT < '910' AND KTSKU.MK_FLG = '0' AND KTSET.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND KTSET.GENE_ADD_DTM = KTSKU.GENE_ADD_DTM AND KTSET.MK_FLG = '0' AND KK0341.SVC_KEI_NO = KTSKU.SVC_KEI_NO AND (KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO,KK0341.GENE_ADD_DTM) = ( SELECT KKTK_SVC_KEI_NO, KIKI_CHG_NO, GENE_ADD_DTM FROM KK_T_KKTK_SVC_KEI WHERE KKTK_SVC_KEI_NO = ? AND GENE_ADD_DTM = ? AND MK_FLG = '0' ) AND KK0341.KKTK_SVC_KEI_STAT = '110' AND KK0341.RSV_APLY_CD = '1' AND KK0341.IDO_DIV = '00067' AND KK0341.TAKNKIKI_SBT_CD = '50' AND KK0341.TAKNKIKI_MODEL_CD = ? AND KK0341.KIKI_SEIZO_NO = ? ORDER BY KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_053) #--2つのサービス契約内訳番号に登録されている電話番号一致するか確認する #----------------------------------------------------------- --KEY:KK_SELECT_053 SELECT KK0191.TELNO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0191.RSV_APLY_CD = '2' AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( SELECT KK0161GENE.SVC_KEI_UCWK_NO, MAX(KK0161GENE.RSV_APLY_YMD || KK0161GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161GENE WHERE KK0161GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161GENE.RSV_APLY_YMD <= ? AND KK0161GENE.RSV_APLY_CD = '2' AND KK0161GENE.MK_FLG = '0' GROUP BY KK0161GENE.SVC_KEI_UCWK_NO ) #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_054) #--サービス契約番号、VA宅内機器型式コード、VA機器製造番号、世代登録年月日時分秒に紐づくSOD発行に必要なデータを複数件取得する。 #--サービス契約内訳ステータスは020以上910未満とする #--※機器提供サービス契約番号世代登録年月日時分秒、異動区分を指定する場合にKK_T_SVKEIUW_EOH_TEL-KK_SELECT_009の代替として使用する。 #----------------------------------------------------------- --KEY:KK_SELECT_054 SELECT /*KK_T_SVKEIUW_EOH_TEL.SQL KK_SELECT_054*/ KTSET.SIP_USER_ID, KTSET.SIP_USER_ID_PWD, KTSET.VA_PORT_NO, KTSET.TELNO, KTSET.BMP_UM, KTSKU.SVC_KEI_UCWK_NO, KTSKU.GENE_ADD_DTM as SKU_GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KTSKU, KK_T_SVKEIUW_EOH_TEL KTSET ,KK_T_KKTK_SVC_KEI KK0341 WHERE KTSKU.SVC_KEI_UCWK_NO = ? AND KTSKU.SVC_KEI_UCWK_STAT >= '020' AND KTSKU.SVC_KEI_UCWK_STAT < '910' AND KTSKU.MK_FLG = '0' AND KTSET.SVC_KEI_UCWK_NO = KTSKU.SVC_KEI_UCWK_NO AND KTSET.GENE_ADD_DTM = KTSKU.GENE_ADD_DTM AND KTSET.MK_FLG = '0' AND KK0341.SVC_KEI_NO = KTSKU.SVC_KEI_NO AND KK0341.KKTK_SVC_KEI_NO = ? AND KK0341.GENE_ADD_DTM = ? AND KK0341.IDO_DIV = ? AND KK0341.TAKNKIKI_SBT_CD = '50' AND KK0341.TAKNKIKI_MODEL_CD = ? AND KK0341.KIKI_SEIZO_NO = ? ORDER BY KTSKU.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜ｅｏ光電話＞(KK_T_SVKEIUW_EOH_TEL-CH_SELECT_001) #----------------------------------------------------------- --KEY:CH_SELECT_001 SELECT KK0191.TELNO, KK0191.TELNO_JUN FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_NO = ? AND KK0161.SVC_KEI_UCWK_STAT in ('100', '210', '220') AND KK0161.KEIZK_AF_KEI_CHGECHU_FLG = '0' AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( SELECT MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK GENE WHERE GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND GENE.RSV_APLY_YMD <= ? AND GENE.RSV_APLY_CD = '2' AND GENE.MK_FLG = '0' ) AND KK0191.MK_FLG = '0' ORDER BY KK0191.TELNO_JUN #- #----------------------------------------------------------- #--サービス契約内訳＜eo光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_055) #--電話番号追加時のレコードを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_055 SELECT KK0161.SVC_KEI_UCWK_NO, KK0161.MSKM_DTL_NO FROM KK_T_SVC_KEI_UCWK KK0161 WHERE KK0161.SVC_KEI_UCWK_NO IN ( SELECT KK0161_2.SVC_KEI_UCWK_NO FROM KK_T_SVC_KEI_UCWK KK0161_2 INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191_2 ON KK0191_2.SVC_KEI_UCWK_NO = KK0161_2.SVC_KEI_UCWK_NO AND KK0191_2.GENE_ADD_DTM = KK0161_2.GENE_ADD_DTM WHERE KK0161_2.SVC_KEI_NO = ? AND KK0191_2.TELNO = ? ) AND KK0161.GENE_ADD_DTM = ( SELECT MIN(GENE.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK GENE WHERE GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND GENE.RSV_APLY_YMD <= ? AND GENE.RSV_APLY_CD = '2' AND GENE.MK_FLG = '0' ) ORDER BY KK0161.GENE_ADD_DTM DESC #- #----------------------------------------------------------- #--サービス契約内訳＜eo光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_056) #--KKPRC67001 #--サービス契約番号に紐づくサービス契約内訳情報を取得する #----------------------------------------------------------- --KEY:KK_SELECT_056 SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.VA_KIKI_CHG_NO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM WHERE KK0161.SVC_KEI_NO = ? AND KK0161.SVC_KEI_UCWK_STAT NOT IN ('910', '920') AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' ) OR NVL(KK0161.RSV_APLY_YMD,'20991231') || KK0161.GENE_ADD_DTM IN ( SELECT NVL(KK0161_GENE2.RSV_APLY_YMD,'20991231') || KK0161_GENE2.GENE_ADD_DTM FROM KK_T_SVC_KEI_UCWK KK0161_GENE2 WHERE KK0161_GENE2.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND NVL(KK0161_GENE2.RSV_APLY_YMD,'20991231') > ? AND KK0161_GENE2.RSV_APLY_CD IN ('1', '2') AND KK0161_GENE2.MK_FLG = '0' )) AND KK0191.MK_FLG = '0' ORDER BY KK0191.GENE_ADD_DTM ASC #- #----------------------------------------------------------- #--サービス契約内訳＜eo光電話＞(KK_T_SVKEIUW_EOH_TEL-KK_SELECT_057) #--KKPRC67401 #--サービス契約番号に紐づくサービス契約内訳のプライマリーキーの情報・NTT契約者住所コード・電話番号・情報を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_057 SELECT DISTINCT KK0191_01.SVC_KEI_UCWK_NO, KK0191_01.GENE_ADD_DTM, KK0191_01.NTT_KEISHA_AD_CD, KK0191_01.TELNO, KK0161.SVC_KEI_NO FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN ( ( SELECT KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM, KK0191.NTT_KEISHA_AD_CD, KK0191.TELNO FROM KK_T_SVKEIUW_EOH_TEL KK0191 INNER JOIN ZM_M_TELNO ZM0121 ON ZM0121.TELNO = KK0191.TELNO INNER JOIN TU_M_TUSHIN_JGYOSHA TU0051 ON TU0051.TSJGS_CD = ZM0121.TLN_STKU_TSJGS_CD WHERE KK0191.BMP_UM = '1' AND KK0191.MK_FLG = '0' AND TU0051.MK_FLG = '0' AND TU0051.JISHA_TASHA_FLG = '1' AND ZM0121.MK_FLG = '0' ) KK0191_01 ) ON KK0191_01.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0191_01.GENE_ADD_DTM = KK0161.GENE_ADD_DTM INNER JOIN TU_T_BMP_KOJI TU0011 ON TU0011.SVC_KEI_UCWK_NO = KK0191_01.SVC_KEI_UCWK_NO WHERE KK0161.SVC_KEI_UCWK_STAT = '100' AND TU0011.BMP_KOJI_STAT = '050' AND TU0011.MK_FLG = '0' AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX FROM KK_T_SVC_KEI_UCWK KK0161_GENE WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_GENE.RSV_APLY_YMD <= ? AND KK0161_GENE.RSV_APLY_CD = '2' AND KK0161_GENE.MK_FLG = '0' )) #- #----------------------------------------------------------- #--サービス契約内訳＜eo光電話＞(KK_T_SVKEIUW_EOH_TEL-TU_SELECT_001) #--TUPRC00501 #--サービス契約内訳番号に紐づく電話番号を取得する #----------------------------------------------------------- --KEY:TU_SELECT_001 SELECT TELNO FROM KK_T_SVKEIUW_EOH_TEL TEL WHERE SVC_KEI_UCWK_NO = ? AND TEL.GENE_ADD_DTM = ( SELECT MAX(TEL_GENE.GENE_ADD_DTM) FROM KK_T_SVKEIUW_EOH_TEL TEL_GENE WHERE TEL_GENE.SVC_KEI_UCWK_NO = ? AND TEL_GENE.RSV_APLY_CD = '2' AND TEL_GENE.MK_FLG = '0' GROUP BY TEL_GENE.SVC_KEI_UCWK_NO ) #- #----------------------------------------------------------- #--サービス契約内訳＜eo光電話＞(KK_T_SVKEIUW_EOH_TEL-TU_SELECT_002) #--電話番号を元に、対象がトーキ中ではあれば開始日・終了日を取得する。 #----------------------------------------------------------- --KEY:TU_SELECT_002 SELECT KK0191.TELNO , KK0191.DSL_PAUSE_TOKI_STA_YMD , KK0191.DSL_PAUSE_TOKI_END_YMD , KK0191.ITENS_OPAF_TOKI_STA_YMD , KK0191.ITENS_OPAF_TOKI_END_YMD FROM KK_T_SVKEIUW_EOH_TEL KK0191 WHERE KK0191.MK_FLG = '0' AND KK0191.TELNO = ? AND KK0191.GENE_ADD_DTM = ( SELECT MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX FROM KK_T_SVKEIUW_EOH_TEL KK0191_GENE WHERE KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO AND KK0191_GENE.TELNO = KK0191.TELNO AND KK0191_GENE.RSV_APLY_CD = '2' AND KK0191_GENE.MK_FLG = '0' ) AND ( KK0191.DSL_PAUSE_TOKI_STA_YMD <= ? AND ( KK0191.DSL_PAUSE_TOKI_END_YMD IS NULL OR KK0191.DSL_PAUSE_TOKI_END_YMD = '20991231' ) OR ( KK0191.ITENS_OPAF_TOKI_STA_YMD <= ? AND ( KK0191.ITENS_OPAF_TOKI_END_YMD IS NULL OR KK0191.ITENS_OPAF_TOKI_END_YMD = '20991231' ) ) ) #-