#----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_001) #--申込明細番号に紐づく申込情報を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_001 SELECT MSKM.MSKM_NO, MSKM.GENE_ADD_DTM, MSKM.MSKM_STAT, MSKM.SYSID, MSKM.MSKM_SBT_CD, MSKM.MSKMSHO_ARIV_YMD, MSKM.MSKM_UK_DTM, MSKM.MSKM_UK_TNT_USER_ID, MSKM.MSKM_YMD, MSKM.TTL_BUSINESS_CENTER_UK_NO, MSKM.TTL_BUSINESS_MSKM_OPT_CD, MSKM.MSKM_RRKS_TELNO, MSKM.MSKM_JSSIS_SBT_CD, MSKM.CUST_YOBO_JIKO, MSKM.HNIN_CFM_BUNSHO_KIND_NM, MSKM.TEL_RRK_KIBO_YMD, MSKM.TEL_RRK_KIBO_TIME_CD, MSKM.TAKCHO_KIBO_YMD, MSKM.TAKCHO_KIBO_TIME_CD, MSKM.MSKM_PDING_NO, MSKM.MSKM_PDING_DTM, MSKM.MSKM_PDING_RSN_CD, MSKM.MSKM_PDING_RLS_YMD, MSKM.MSKM_RTRN_YMD, MSKM.MSKM_RTRN_RSN_CD, MSKM.KARI_MSKM_FLG, MSKM.MSKM_NAIYO_CFM_FIN_YMD, MSKM.MSKM_CANCEL_YMD, MSKM.MSKM_CANCEL_RSN_CD, MSKM.MSKM_CANCEL_CL_YMD, MSKM.REFERER, MSKM.LAST_SCREEN_ID, MSKM.MSKM_NYO_SHONIN_SHIN_YMD, MSKM.MSKM_NYO_SHONIN_YMD, MSKM.KEPCO_CTINFO_JUJU_DOI_UM, MSKM.AGNT_SHUK_KMK_CD, MSKM.ADD_DTM, MSKM.ADD_OPEACNT, MSKM.UPD_DTM, MSKM.UPD_OPEACNT, MSKM.DEL_DTM, MSKM.DEL_OPEACNT, MSKM.MK_FLG FROM KK_T_MSKM MSKM INNER JOIN KK_T_MSKM_DTL MSKM_DTL ON MSKM.MSKM_NO = MSKM_DTL.MSKM_NO WHERE MSKM_DTL.MSKM_DTL_NO = ? AND MSKM.GENE_ADD_DTM = (SELECT MAX(KK_T_MSKM_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_MAX FROM KK_T_MSKM KK_T_MSKM_RCNT WHERE KK_T_MSKM_RCNT.MSKM_NO = MSKM.MSKM_NO AND KK_T_MSKM_RCNT.MK_FLG = '0') AND MSKM_DTL.GENE_ADD_DTM = (SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX FROM KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT WHERE KK_T_MSKM_DTL_RCNT.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO AND KK_T_MSKM_DTL_RCNT.MK_FLG = '0') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_002) #--申込ステータスが「申請中」かつ申込内容承認申請年月日が設定済かつ申込内容申請年月日が未設定かつ申込受付年月日時分秒が対象日付より前の最新データを取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_002 SELECT MSKM.MSKM_NO ,MSKM.GENE_ADD_DTM ,MSKM.MSKM_STAT ,MSKM.SYSID ,MSKM.MSKM_SBT_CD ,MSKM.MSKMSHO_ARIV_YMD ,MSKM.MSKM_UK_DTM ,MSKM.MSKM_UK_TNT_USER_ID ,MSKM.MSKM_YMD ,MSKM.TTL_BUSINESS_CENTER_UK_NO ,MSKM.TTL_BUSINESS_MSKM_OPT_CD ,MSKM.MSKM_RRKS_TELNO ,MSKM.MSKM_JSSIS_SBT_CD ,MSKM.CUST_YOBO_JIKO ,MSKM.TEL_RRK_KIBOD_SBT_CD ,MSKM.TEL_RRK_KIBO_TIME_CD ,MSKM.TAKCHO_KIBOD_SBT_CD ,MSKM.TAKCHO_KIBO_TIME_CD ,MSKM.MSKM_PDING_DTM ,MSKM.MSKM_PDING_RSN_CD ,MSKM.MSKM_PDING_RLS_YMD ,MSKM.MSKM_RTRN_YMD ,MSKM.MSKM_RTRN_RSN_CD ,MSKM.KARI_MSKM_FLG ,MSKM.MSKM_NAIYO_CFM_FIN_YMD ,MSKM.MSKM_CANCEL_YMD ,MSKM.MSKM_CANCEL_RSN_CD ,MSKM.MSKM_CANCEL_CL_YMD ,MSKM.REFERER ,MSKM.LAST_SCREEN_ID ,MSKM.MSKM_NYO_SHONIN_SHIN_YMD ,MSKM.MSKM_NYO_SHONIN_YMD ,MSKM.KEPCO_CUSTINFO_JUJU_DOI_UM ,MSKM.AGNT_SHUK_KMK_CD ,MSKM.SHOSA_BF_RRK_JIKO_YH ,MSKM.SHOSA_BF_RRK_JIKO ,MSKM.USE_SVC_KEIZK_SBT_CD ,MSKM.ANTENNA_RENT_KIBO_UM ,MSKM.RTRN_CHU_MSKM_ABDMNT_DTM ,MSKM.CONSMBSN_MSKM_STAT_SKBT_CD ,MSKM.UK_TNTSHA_SKBT_CD ,MSKM.KOJI_REQ_INFO_RRK_JIKO ,MSKM.MSKM_ROUTE_SKBT_CD ,MSKM.AGNT_MSKM_TRTG_UM ,MSKM.ADD_DTM ,MSKM.ADD_OPEACNT ,MSKM.UPD_DTM ,MSKM.UPD_OPEACNT ,MSKM.DEL_DTM ,MSKM.DEL_OPEACNT ,MSKM.MK_FLG ,MSKM.TKJCP_CD ,MSKM.TKJCP_NM ,MSKM.TKJCP_STNEGS_NM ,MSKM.TKJCPTTS_NM ,MSKM.TKJCPTTS_SHKSNM ,MSKM.TKJCPTTS_TLN ,MSKM.BUNJOCHI_NM ,MSKM.BUNJOCHI_KKK_NO_MEMO ,MSKM.NYUKYO_RSV_JK ,MSKM.AGNT_MKM_UK_EOSTF_USER_ID ,MSKM.AGNT_MKM_UK_RHSTF_KANA_SEI ,MSKM.EOELEC_DOJI_MSKM_ISHI_UM ,MSKM.THNX_CALL_JSSI_YMD ,MSKM.SET_WARI_MKM_SKBT_DIV ,MSKM.SET_WARI_MKM_SKBT_NO ,MSKM.TEGAK_SVC_MSKM_KIKI ,MSKM.SKBT_KEY FROM KK_T_MSKM MSKM WHERE MSKM.MSKM_STAT = '100' AND MSKM.MSKM_NYO_SHONIN_YMD IS NULL AND MSKM.CONSMBSN_MSKM_STAT_SKBT_CD = '03' AND MSKM.MSKM_UK_DTM < ? AND MSKM.MSKM_NYO_SHONIN_SHIN_YMD <= ? AND (MSKM.KARI_MSKM_FLG IS NULL OR MSKM.KARI_MSKM_FLG = '2') AND MSKM.GENE_ADD_DTM >= ? AND MSKM.GENE_ADD_DTM = (SELECT SUB2.GENE_ADD_DTM FROM (SELECT SUB.GENE_ADD_DTM,SUB.MSKM_NO, RANK() OVER(PARTITION BY SUB.MSKM_NO ORDER BY SUB.GENE_ADD_DTM DESC) AS SUB_RANK FROM KK_T_MSKM SUB WHERE SUB.MK_FLG = '0') SUB2 WHERE SUB2.SUB_RANK = 1 AND MSKM.MSKM_NO = SUB2.MSKM_NO)FOR UPDATE #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_003) #--申込IDをキーにして更新年月日を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_003 SELECT A.MSKM_NO, A.UPD_DTM FROM KK_T_MSKM A WHERE A.MSKM_NO = ? AND A.GENE_ADD_DTM = (SELECT MAX(SUB.GENE_ADD_DTM) AS SUB_MAX FROM KK_T_MSKM SUB WHERE A.MSKM_NO = SUB.MSKM_NO AND SUB.MK_FLG = '0') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_005) #--申込番号から申込受付年月日時分秒を取得する #----------------------------------------------------------- --KEY:KK_SELECT_005 SELECT KK0011.MSKM_UK_DTM FROM KK_T_MSKM KK0011 WHERE KK0011.MSKM_NO = ? AND KK0011.GENE_ADD_DTM = (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE .MK_FLG = '0') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_006) #--KKPRC09701(割引サービス自動適用ＣＣの設定値取得） #----------------------------------------------------------- --KEY:KK_SELECT_006 SELECT KK0011.MSKM_NO, KK0011.MSKM_SBT_CD FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_DTL_NO = ? AND KK0021.SYSID = ? AND KK0021.MK_FLG = '0' WHERE KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) as MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.SYSID = KK0021.SYSID AND KK0021_GENE.MK_FLG = '0' ) AND KK0011.GENE_ADD_DTM = (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) as MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.SYSID = KK0011.SYSID AND KK0011_GENE.MK_FLG = '0' ) AND KK0011.MSKM_NO = KK0021.MSKM_NO AND KK0011.SYSID = KK0021.SYSID AND KK0011.MK_FLG = '0' #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_007) #--日報データ集計取得 #----------------------------------------------------------- --KEY:KK_SELECT_007 SELECT MAIN_SHUK.MSKM_NYO_SHONIN_YMD, MAIN_SHUK.AGNT_SBT_CD, MAIN_SHUK.MSKM_SYO_SBT_CD, MAIN_SHUK.INTR_CMP_FLG, MAIN_SHUK.TTL_BUSINESS_MSKM_OPT_CD, MAIN_SHUK.MSKM_FORM_CD, MAIN_SHUK.KARI_MSKM_FLG, COUNT(*) MSKM_CNT, SUM(MAIN_SHUK.EOHNT_MSKMSHA_FLG) EOHNT_MSKMSHA_CNT, SUM(MAIN_SHUK.EOHTV_MSKMSHA_FLG) EOHTV_MSKMSHA_CNT, SUM(MAIN_SHUK.EOHTL_MSKMSHA_FLG) EOHTL_MSKMSHA_CNT, SUM(MAIN_SHUK.MOBILE_MSKMSHA_FLG) MOBILE_MSKMSHA_CNT, SUM(MAIN_SHUK.EOHNT) EOHNT, SUM(MAIN_SHUK.EOHTV) EOHTV, SUM(MAIN_SHUK.EOHTL) EOHTL, SUM(MAIN_SHUK.MOBILE) MOBILE, SUM(MAIN_SHUK.EOHNT_EOHTV) EOHNT_EOHTV, SUM(MAIN_SHUK.EOHNT_EOHTL) EOHNT_EOHTL, SUM(MAIN_SHUK.EOHNT_MOBILE) EOHNT_MOBILE, SUM(MAIN_SHUK.EOHTV_EOHTL) EOHTV_EOHTL, SUM(MAIN_SHUK.EOHTV_MOBILE) EOHTV_MOBILE, SUM(MAIN_SHUK.EOHTL_MOBILE) EOHTL_MOBILE, SUM(MAIN_SHUK.SANTEN_SET) SANTEN_SET, SUM(MAIN_SHUK.EOHNT_EOHTL_MOBILE) EOHNT_EOHTL_MOBILE, SUM(MAIN_SHUK.EOHNT_EOHTV_MOBILE) EOHNT_EOHTV_MOBILE, SUM(MAIN_SHUK.EOHTV_EOHTL_MOBILE) EOHTV_EOHTL_MOBILE, SUM(MAIN_SHUK.YONTEN_SET) YONTEN_SET, SUM(MAIN_SHUK.EOHTV_DOJI_MSKM) EOHTV_DOJI_MSKM, SUM(MAIN_SHUK.MOBILE_DOJI_MSKM) MOBILE_DOJI_MSKM, SUM(MAIN_SHUK.EOHNT_ADD) EOHNT_ADD, SUM(MAIN_SHUK.MOBILE_ADD) MOBILE_ADD, SUM(MAIN_SHUK.EOHTV_SNGL) EOHTV_SNGL, SUM(MAIN_SHUK.MOBILE_SNGL) MOBILE_SNGL FROM ( SELECT SUB_SHUK.MSKM_NYO_SHONIN_YMD, SUB_SHUK.AGNT_SBT_CD, SUB_SHUK.MSKM_SYO_SBT_CD, SUB_SHUK.INTR_CMP_FLG, SUB_SHUK.TTL_BUSINESS_MSKM_OPT_CD, SUB_SHUK.MSKM_FORM_CD, SUB_SHUK.KARI_MSKM_FLG, SUB_SHUK.SYSID, SUB_SHUK.MSKM_NO, SUB_SHUK.EOHNT_MSKMSHA_FLG, SUB_SHUK.EOHTV_MSKMSHA_FLG, SUB_SHUK.EOHTL_MSKMSHA_FLG, SUB_SHUK.MOBILE_MSKMSHA_FLG, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHNT, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHTV, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHTL, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHNT_EOHTV, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHNT_EOHTL, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHNT_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END EOHTV_EOHTL, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHTV_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHTL_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' ELSE '0' END SANTEN_SET, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHNT_EOHTL_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHNT_EOHTV_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHTV_EOHTL_MOBILE, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END YONTEN_SET, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '0' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHTV_DOJI_MSKM, CASE WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '0' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.EOHNT_MSKMSHA_FLG = '1' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' AND SUB_SHUK.EOHTL_MSKMSHA_FLG = '1' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END MOBILE_DOJI_MSKM, CASE WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND (KZN_SVC_MAIN.KZN_EOHNT >= '1' OR KZN_SVC_MAIN.KZN_EOHTL >= '1') AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END EOHNT_ADD, CASE WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND (KZN_SVC_MAIN.KZN_EOHNT >= '1' OR KZN_SVC_MAIN.KZN_EOHTL >= '1') AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END MOBILE_ADD, CASE WHEN SUB_SHUK.MSKM_SBT_CD = '00001' AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' THEN '1' WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND KZN_SVC_MAIN.KZN_EOHNT = '0' AND KZN_SVC_MAIN.KZN_EOHTL = '0' AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND KZN_SVC_MAIN.KZN_EOHNT IS NULL AND KZN_SVC_MAIN.KZN_EOHTL IS NULL AND KZN_SVC_MAIN.KZN_EOHTV IS NULL AND KZN_SVC_MAIN.KZN_MOBILE IS NULL AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.EOHTV_MSKMSHA_FLG= '1' THEN '1' ELSE '0' END EOHTV_SNGL, CASE WHEN SUB_SHUK.MSKM_SBT_CD = '00001' AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND KZN_SVC_MAIN.KZN_EOHNT = '0' AND KZN_SVC_MAIN.KZN_EOHTL = '0' AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0' AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' WHEN SUB_SHUK.MSKM_SBT_CD = '00002' AND KZN_SVC_MAIN.KZN_EOHNT IS NULL AND KZN_SVC_MAIN.KZN_EOHTL IS NULL AND KZN_SVC_MAIN.KZN_EOHTV IS NULL AND KZN_SVC_MAIN.KZN_MOBILE IS NULL AND SUB_SHUK.EOHNT_MSKMSHA_FLG = '0'AND SUB_SHUK.MOBILE_MSKMSHA_FLG = '1' THEN '1' ELSE '0' END MOBILE_SNGL FROM ( SELECT SUB_SUB_SHUK.MSKM_NYO_SHONIN_YMD, SUB_SUB_SHUK.AGNT_SBT_CD, SUB_SUB_SHUK.MSKM_SYO_SBT_CD, SUB_SUB_SHUK.INTR_CMP_FLG, SUB_SUB_SHUK.TTL_BUSINESS_MSKM_OPT_CD, SUB_SUB_SHUK.MSKM_FORM_CD, SUB_SUB_SHUK.KARI_MSKM_FLG, SUB_SUB_SHUK.MSKM_SBT_CD, SUB_SUB_SHUK.SYSID, SUB_SUB_SHUK.MSKM_NO, SUM(EOHNT_MSKMSHA_FLG) EOHNT_MSKMSHA_FLG, SUM(MOBILE_MSKMSHA_FLG) MOBILE_MSKMSHA_FLG, SUM(EOHTL_MSKMSHA_FLG) EOHTL_MSKMSHA_FLG, SUM(EOHTV_MSKMSHA_FLG) EOHTV_MSKMSHA_FLG FROM (SELECT KK0011.MSKM_NYO_SHONIN_YMD, CASE WHEN KK0021.MSKM_FORM_CD != '02' AND KK0021.MSKM_FORM_CD ! = '03' THEN AGNT.AGNT_SBT_CD WHEN KK0021.MSKM_FORM_CD = '02' THEN '03' WHEN KK0021.MSKM_FORM_CD = '03' THEN '04' END AGNT_SBT_CD, CASE WHEN KK0021.MSKMSHO_SBT_CD = '1' OR KK0021.MSKMSHO_SBT_CD = '5' THEN 'HF' WHEN KK0021.MSKMSHO_SBT_CD = '2' THEN 'MF' WHEN KK0021.MSKMSHO_SBT_CD = '3' THEN 'MZ' END MSKM_SYO_SBT_CD, CASE WHEN KK0081.INTR_CD IS NULL THEN '0' ELSE '1' END INTR_CMP_FLG, KK0011.TTL_BUSINESS_MSKM_OPT_CD, KK0021.MSKM_FORM_CD, KK0011.KARI_MSKM_FLG, KK0011.MSKM_SBT_CD, KK0011.SYSID, KK0011.MSKM_NO, CASE WHEN KK0081.PRC_GRP_CD BETWEEN '02' AND '06' THEN '1' ELSE '0' END EOHNT_MSKMSHA_FLG, CASE WHEN KK0081.PRC_GRP_CD IN ('07', '08', '09', '16') THEN '1' ELSE '0' END MOBILE_MSKMSHA_FLG, CASE WHEN KK0081.PRC_GRP_CD = '10' THEN '1' ELSE '0' END EOHTL_MSKMSHA_FLG, CASE WHEN KK0081.PRC_GRP_CD BETWEEN '11' AND '13' THEN '1' ELSE '0' END EOHTV_MSKMSHA_FLG FROM (SELECT /*+ LEADING(KK0011) INDEX (KK0011 KK_T_MSKM_IDX_03) */ KK0011.MSKM_NYO_SHONIN_YMD, KK0011.TTL_BUSINESS_MSKM_OPT_CD, KK0011.KARI_MSKM_FLG, KK0011.MSKM_SBT_CD, KK0011.SYSID, KK0011.MSKM_NO FROM KK_T_MSKM KK0011 WHERE KK0011.MSKM_STAT = '130' AND KK0011.MSKM_NYO_SHONIN_YMD > ? AND KK0011.MSKM_NYO_SHONIN_YMD <= ? AND (KK0011.KARI_MSKM_FLG IS NULL OR KK0011.KARI_MSKM_FLG = '2') AND KK0011.GENE_ADD_DTM = ( SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0') ) KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0011.MSKM_NO = KK0021.MSKM_NO AND ((KK0011.KARI_MSKM_FLG IS NULL AND KK0021.MSKM_FORM_CD = '04') OR (KK0011.KARI_MSKM_FLG = '2' AND KK0021.MSKM_FORM_CD IN ('01', '02', '03'))) AND KK0021.MSKMSHO_SBT_CD IN ('1','2','3','5') AND KK0021.MSKM_DTL_STAT != '920' AND KK0021.GENE_ADD_DTM = ( SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081 ON KK0021.MSKM_DTL_NO = KK0081.MSKM_DTL_NO AND KK0081.PRC_GRP_CD IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '16') AND KK0081.SVC_KEI_STAT NOT IN ('910','920') AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_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') INNER JOIN ( SELECT MSKM_NO , NVL(AGNT_SBT_CD_1,AGNT_SBT_CD_3) AGNT_SBT_CD FROM ( SELECT KK0071.MSKM_NO , MAX(DECODE(KK0071.DSP_JUN, 1, DECODE(KK0071.AGNT_CD, ?, '01', ?, '02', ?, '08', ?, '05', ?, '06', ?, '07'), NULL)) AGNT_SBT_CD_1 , MAX(DECODE(KK0071.DSP_JUN, 3, DECODE(KK0071.AGNT_CD, ?, '01', ?, '02', ?, '08', ?, '05', ?, '06', ?, '07'), NULL)) AGNT_SBT_CD_3 FROM KK_T_MSKM_AGNT KK0071 WHERE KK0071.AGNT_CD IN(?, ?, ?, ?, ?, ?) AND KK0071.DSP_JUN IN (1, 3) AND KK0071.MK_FLG = '0' GROUP BY KK0071.MSKM_NO ) WHERE NVL(AGNT_SBT_CD_1,AGNT_SBT_CD_3) IS NOT NULL ) AGNT ON AGNT.MSKM_NO = KK0011.MSKM_NO ) SUB_SUB_SHUK GROUP BY SUB_SUB_SHUK.MSKM_NYO_SHONIN_YMD, SUB_SUB_SHUK.AGNT_SBT_CD, SUB_SUB_SHUK.MSKM_SYO_SBT_CD, SUB_SUB_SHUK.INTR_CMP_FLG, SUB_SUB_SHUK.TTL_BUSINESS_MSKM_OPT_CD, SUB_SUB_SHUK.MSKM_FORM_CD, SUB_SUB_SHUK.KARI_MSKM_FLG, MSKM_SBT_CD, SUB_SUB_SHUK.SYSID, SUB_SUB_SHUK.MSKM_NO ) SUB_SHUK LEFT OUTER JOIN ( SELECT KZN_SVC_SUB.SYSID, SUM(KZN_SVC_SUB.KZN_EOHNT) KZN_EOHNT, SUM(KZN_SVC_SUB.KZN_MOBILE) KZN_MOBILE, SUM(KZN_SVC_SUB.KZN_EOHTL) KZN_EOHTL, SUM(KZN_SVC_SUB.KZN_EOHTV) KZN_EOHTV FROM ( SELECT KK0081_MAIN.SYSID, CASE WHEN KK0081_MAIN.PRC_GRP_CD BETWEEN '02' AND '06' THEN '1' ELSE '0' END KZN_EOHNT, CASE WHEN KK0081_MAIN.PRC_GRP_CD IN ('07', '08', '09', '16') THEN '1' ELSE '0' END KZN_MOBILE, CASE WHEN KK0081_MAIN.PRC_GRP_CD = '10' THEN '1' ELSE '0' END KZN_EOHTL, CASE WHEN KK0081_MAIN.PRC_GRP_CD BETWEEN '11' AND '13' THEN '1' ELSE '0' END KZN_EOHTV FROM KK_T_SVC_KEI KK0081_MAIN INNER JOIN KK_T_MSKM_DTL KK0021_SUB ON KK0021_SUB.MSKM_DTL_NO = KK0081_MAIN.MSKM_DTL_NO AND KK0021_SUB.GENE_ADD_DTM = ( SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021_SUB.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0') INNER JOIN KK_T_MSKM KK0011_SUB ON KK0021_SUB.MSKM_NO = KK0011_SUB.MSKM_NO AND KK0011_SUB.MSKM_STAT != '920' AND (KK0011_SUB.MSKM_NYO_SHONIN_YMD <= ? OR (KK0021_SUB.MSKM_FORM_CD IN ('01','02','03') AND KK0011_SUB.KARI_MSKM_FLG IS NULL) OR KK0021_SUB.MSKM_FORM_CD = '05') AND KK0011_SUB.GENE_ADD_DTM = ( SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011_SUB.MSKM_NO AND KK0011_GENE.MK_FLG = '0') WHERE KK0081_MAIN.MSKM_DTL_NO = KK0021_SUB.MSKM_DTL_NO AND KK0081_MAIN.SVC_KEI_STAT NOT IN ('910','920') AND KK0081_MAIN.PRC_GRP_CD IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '16') AND KK0081_MAIN.RSV_APLY_YMD || KK0081_MAIN.GENE_ADD_DTM = ( SELECT MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX FROM KK_T_SVC_KEI KK0081_GENE WHERE KK0081_GENE.SVC_KEI_NO = KK0081_MAIN.SVC_KEI_NO AND KK0081_GENE.RSV_APLY_YMD <= ? AND KK0081_GENE.RSV_APLY_CD = '2' AND KK0081_GENE.MK_FLG = '0') ) KZN_SVC_SUB GROUP BY KZN_SVC_SUB.SYSID ) KZN_SVC_MAIN ON KZN_SVC_MAIN.SYSID = SUB_SHUK.SYSID ) MAIN_SHUK GROUP BY MAIN_SHUK.MSKM_NYO_SHONIN_YMD, MAIN_SHUK.AGNT_SBT_CD, MAIN_SHUK.MSKM_SYO_SBT_CD, MAIN_SHUK.INTR_CMP_FLG, MAIN_SHUK.TTL_BUSINESS_MSKM_OPT_CD, MAIN_SHUK.MSKM_FORM_CD, MAIN_SHUK.KARI_MSKM_FLG #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_008) #--進捗登録情報取得 #----------------------------------------------------------- --KEY:KK_SELECT_008 SELECT KK0011.MSKM_SBT_CD, KK0021.MSKM_DTL_NO, KK0021.MSKMSHO_NO, KK0081.SVC_KEI_NO FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0011.MSKM_NO = KK0021.MSKM_NO AND KK0021.MSKM_DTL_STAT != '920' AND KK0021.GENE_ADD_DTM = ( SELECT MAX(KK0021_SUB.GENE_ADD_DTM) KK0021_SUB_MAX FROM KK_T_MSKM_DTL KK0021_SUB WHERE KK0021_SUB.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_SUB.MK_FLG = '0' ) INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081.SVC_KEI_STAT NOT IN ('910' , '920') AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MAX(KK0081_SUB.RSV_APLY_YMD || KK0081_SUB.GENE_ADD_DTM) AS KK0081_SUB_MAX FROM KK_T_SVC_KEI KK0081_SUB WHERE KK0081_SUB.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_SUB.RSV_APLY_YMD <= ? AND KK0081_SUB.RSV_APLY_CD = '2' AND KK0081_SUB.MK_FLG = '0') WHERE KK0011.MSKM_NO = ? AND KK0011.GENE_ADD_DTM = ( SELECT MAX(KK0011_SUB.GENE_ADD_DTM) KK0011_SUB_MAX FROM KK_T_MSKM KK0011_SUB WHERE KK0011_SUB.MSKM_NO = KK0011.MSKM_NO AND KK0011_SUB.MK_FLG = '0' ) #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_009) #--申込実績データ集計 #----------------------------------------------------------- --KEY:KK_SELECT_009 SELECT MAIN_SHUK.MSKM_NYO_SHONIN_YMD, MAIN_SHUK.AGNT_CD, COUNT(MAIN_SHUK.NET_TEL_FLG) MSKM_CNT, COUNT(MAIN_SHUK.TV_FLG) MSKM_CNT_TV, COUNT(MAIN_SHUK.RYOHAN_MSKM_NO) MSKM_CNT_RHSTF, COUNT(MAIN_SHUK.EOSTUFF_MSKM_NO) MSKM_CNT_EOSTF FROM ( SELECT SUB_SHUK.MSKM_NYO_SHONIN_YMD, SUB_SHUK.AGNT_CD, (CASE WHEN SUB_SHUK.NET_FLG = '1' OR SUB_SHUK.TEL_FLG = '1' THEN '1' ELSE NULL END) NET_TEL_FLG, SUB_SHUK.TV_FLG, (CASE WHEN SUB_SHUK.UK_TNTSHA_SKBT_CD='02' THEN SUB_SHUK.MSKM_NO ELSE NULL END) RYOHAN_MSKM_NO, (CASE WHEN SUB_SHUK.UK_TNTSHA_SKBT_CD='01' THEN SUB_SHUK.MSKM_NO ELSE NULL END) EOSTUFF_MSKM_NO FROM ( SELECT /*+ LEADING(KK0011) USE_NL(KK0021) */ KK0011.MSKM_NO, KK0011.MSKM_NYO_SHONIN_YMD, KK0011.UK_TNTSHA_SKBT_CD, KK0061.AGNT_CD, SUM((CASE WHEN EO_NET.SVC_KEI_NO IS NULL THEN NULL ELSE '1' END)) NET_FLG, SUM((CASE WHEN EO_TEL.SVC_KEI_NO IS NULL THEN NULL ELSE '1' END)) TEL_FLG, SUM((CASE WHEN EO_TV.SVC_KEI_NO IS NULL THEN NULL ELSE '1' END)) TV_FLG FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0011.MSKM_NO = KK0021.MSKM_NO AND KK0021.MSKM_DTL_STAT <> '920' AND (KK0021.MSKMSHO_SBT_CD = '1' OR KK0021.MSKMSHO_SBT_CD = '3') AND KK0021.GENE_ADD_DTM = ( SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0' ) INNER JOIN KK_T_MSKM_AGNT KK0071 ON KK0021.MSKM_NO = KK0071.MSKM_NO AND KK0071.DSP_JUN = 1 AND KK0071.MK_FLG = '0' INNER JOIN KK_M_AGNT KK0061 ON KK0071.AGNT_CD = KK0061.AGNT_CD AND KK0061.TRAT_CD_YO_MKM_CHANNEL_CD = '01' AND KK0061.RSV_APLY_YMD || KK0061.GENE_ADD_DTM= ( SELECT MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) AS KK0061_GENE_MAX FROM KK_M_AGNT KK0061_GENE WHERE KK0061_GENE.AGNT_CD = KK0061.AGNT_CD AND KK0061_GENE.RSV_APLY_YMD <= ? AND KK0061_GENE.MK_FLG = '0' ) INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081.SVC_KEI_STAT NOT IN ('910','920') AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM ) AS KK0081_GENE_MAX 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' ) LEFT OUTER JOIN ( SELECT KK0091.SVC_KEI_NO FROM KK_T_SVC_KEI_EOH_NET KK0091 WHERE (KK0091.SVC_KEI_NO, KK0091.GENE_ADD_DTM) = ( SELECT KK0091_GENE.SVC_KEI_NO, MAX(KK0091_GENE.GENE_ADD_DTM) AS KK0091_GENE_MAX FROM KK_T_SVC_KEI_EOH_NET KK0091_GENE WHERE KK0091_GENE.SVC_KEI_NO = KK0091.SVC_KEI_NO AND KK0091_GENE.RSV_APLY_CD = '2' AND KK0091_GENE.MK_FLG = '0' GROUP BY KK0091_GENE.SVC_KEI_NO ) ) EO_NET ON EO_NET.SVC_KEI_NO = KK0081.SVC_KEI_NO LEFT OUTER JOIN ( SELECT KK0111.SVC_KEI_NO FROM KK_T_SVC_KEI_EOH_TEL KK0111 WHERE (KK0111.SVC_KEI_NO, KK0111.GENE_ADD_DTM) = ( SELECT KK0111_GENE.SVC_KEI_NO, MAX(KK0111_GENE.GENE_ADD_DTM) AS KK0111_GENE_MAX FROM KK_T_SVC_KEI_EOH_TEL KK0111_GENE WHERE KK0111_GENE.SVC_KEI_NO = KK0111.SVC_KEI_NO AND KK0111_GENE.RSV_APLY_CD = '2' AND KK0111_GENE.MK_FLG = '0' GROUP BY KK0111_GENE.SVC_KEI_NO ) ) EO_TEL ON EO_TEL.SVC_KEI_NO = KK0081.SVC_KEI_NO LEFT OUTER JOIN ( SELECT KK0101.SVC_KEI_NO FROM KK_T_SVC_KEI_EOH_TV KK0101 WHERE (KK0101.SVC_KEI_NO, KK0101.GENE_ADD_DTM) = ( SELECT KK0101_GENE.SVC_KEI_NO, MAX(KK0101_GENE.GENE_ADD_DTM) AS KK0101_GENE_MAX FROM KK_T_SVC_KEI_EOH_TV KK0101_GENE WHERE KK0101_GENE.SVC_KEI_NO = KK0101.SVC_KEI_NO AND KK0101_GENE.RSV_APLY_CD = '2' AND KK0101_GENE.MK_FLG = '0' GROUP BY KK0101_GENE.SVC_KEI_NO ) ) EO_TV ON KK0081.SVC_KEI_NO = EO_TV.SVC_KEI_NO WHERE KK0011.MSKM_STAT = '130' AND KK0011.MSKM_NYO_SHONIN_YMD > ? AND KK0011.MSKM_NYO_SHONIN_YMD <= ? AND KK0011.GENE_ADD_DTM = ( SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0' ) GROUP BY KK0011.MSKM_NO, KK0011.MSKM_NYO_SHONIN_YMD,KK0011.UK_TNTSHA_SKBT_CD,KK0061.AGNT_CD ) SUB_SHUK ) MAIN_SHUK WHERE NET_TEL_FLG IS NOT NULL GROUP BY MAIN_SHUK.MSKM_NYO_SHONIN_YMD, MAIN_SHUK.AGNT_CD #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_010) #--【保存期間経過データ物理削除】 #--削除対象の申込番号を取得する。 #----------------------------------------------------------- --KEY:KK_SELECT_010 SELECT DISTINCT MSKM.MSKM_NO FROM KK_T_MSKM MSKM INNER JOIN KK_T_MSKM_DTL MSKM_DTL ON MSKM.MSKM_NO = MSKM_DTL.MSKM_NO WHERE MSKM_DTL.MSKM_NO = ? AND MSKM.MK_FLG = '0' AND MSKM_DTL.MK_FLG = '0' #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_011) #--地図情報差分抽出--申込 #--対象日付に登録・更新のあった申込を抽出 #----------------------------------------------------------- --KEY:KK_SELECT_011 SELECT /*+ LEADING(KK0011) USE_NL(CK0011) */ KK0011.MSKM_UK_DTM, KK0021.MSKM_YMD, KK0021.MSKMSHO_NO, KK0081.SYSID, KK0081.SVC_KEI_NO FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_NO = KK0011.MSKM_NO AND KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0' GROUP BY KK0021_GENE.MSKM_NO) INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT 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') INNER JOIN CK_T_CUST CK0011 ON CK0011.SYSID = KK0011.SYSID AND CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM = (SELECT MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX FROM CK_T_CUST CK0011_GENE WHERE CK0011_GENE.SYSID = CK0011.SYSID AND CK0011_GENE.RSV_APLY_YMD <= ? AND CK0011_GENE.RSV_APLY_CD = '2' AND CK0011_GENE.MK_FLG = '0') WHERE KK0011.GENE_ADD_DTM = (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0') AND (KK0011.ADD_UNYO_YMD = ? OR KK0011.UPD_UNYO_YMD = ?) AND KK0011.MSKM_SBT_CD IN ('00001', '00002') AND KK0081.PRC_GRP_CD IN ('02', '03', '04', '10', '11', '12', '13') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_012) #--番ポ工事当日通知ファイル作成 #--サービス契約内訳の作成起因となる初めの申込明細番号から申込種別を取得する #----------------------------------------------------------- --KEY:KK_SELECT_012 SELECT KK0011.MSKM_SBT_CD FROM KK_T_SVC_KEI_UCWK KK0161 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_DTL_NO = KK0161.MSKM_DTL_NO INNER JOIN KK_T_MSKM KK0011 ON KK0011.MSKM_NO = KK0021.MSKM_NO WHERE KK0161.SVC_KEI_UCWK_NO = ? AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( SELECT MIN(KK0161_M.RSV_APLY_YMD || KK0161_M.GENE_ADD_DTM) FROM KK_T_SVC_KEI_UCWK KK0161_M WHERE KK0161_M.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO AND KK0161_M.RSV_APLY_YMD <= ? AND KK0161_M.RSV_APLY_CD = '2' AND KK0161_M.MK_FLG = '0') AND KK0021.GENE_ADD_DTM = ( SELECT MAX(KK0021_G.GENE_ADD_DTM) FROM KK_T_MSKM_DTL KK0021_G WHERE KK0021_G.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_G.MK_FLG = '0') AND KK0011.GENE_ADD_DTM = ( SELECT MAX(KK0011_G.GENE_ADD_DTM) FROM KK_T_MSKM KK0011_G WHERE KK0011_G.MSKM_NO = KK0011.MSKM_NO AND KK0011_G.MK_FLG = '0') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KV_SELECT_001) #--申込明細番号に紐づく申込情報を取得する。 #----------------------------------------------------------- --KEY:KV_SELECT_001 SELECT MSKM.MSKM_NO, MSKM.GENE_ADD_DTM, MSKM.MSKM_STAT, MSKM.SYSID, MSKM.MSKM_SBT_CD, MSKM.MSKMSHO_ARIV_YMD, MSKM.MSKM_UK_DTM, MSKM.MSKM_UK_TNT_USER_ID, MSKM.MSKM_YMD, MSKM.TTL_BUSINESS_CENTER_UK_NO, MSKM.TTL_BUSINESS_MSKM_OPT_CD, MSKM.MSKM_RRKS_TELNO, MSKM.MSKM_JSSIS_SBT_CD, MSKM.CUST_YOBO_JIKO, MSKM.TEL_RRK_KIBO_TIME_CD, MSKM.TAKCHO_KIBO_TIME_CD, MSKM.MSKM_PDING_DTM, MSKM.MSKM_PDING_RSN_CD, MSKM.MSKM_PDING_RLS_YMD, MSKM.MSKM_RTRN_YMD, MSKM.MSKM_RTRN_RSN_CD, MSKM.KARI_MSKM_FLG, MSKM.MSKM_NAIYO_CFM_FIN_YMD, MSKM.MSKM_CANCEL_YMD, MSKM.MSKM_CANCEL_RSN_CD, MSKM.MSKM_CANCEL_CL_YMD, MSKM.REFERER, MSKM.LAST_SCREEN_ID, MSKM.MSKM_NYO_SHONIN_SHIN_YMD, MSKM.MSKM_NYO_SHONIN_YMD, MSKM.KEPCO_CUSTINFO_JUJU_DOI_UM, MSKM.AGNT_SHUK_KMK_CD, MSKM.ADD_DTM, MSKM.ADD_OPEACNT, MSKM.UPD_DTM, MSKM.UPD_OPEACNT, MSKM.DEL_DTM, MSKM.DEL_OPEACNT, MSKM.MK_FLG FROM KK_T_MSKM MSKM INNER JOIN KK_T_MSKM_DTL MSKM_DTL ON MSKM.MSKM_NO = MSKM_DTL.MSKM_NO WHERE MSKM_DTL.MSKM_DTL_NO = ? AND MSKM.GENE_ADD_DTM = (SELECT MAX(KK_T_MSKM_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_MAX FROM KK_T_MSKM KK_T_MSKM_RCNT WHERE KK_T_MSKM_RCNT.MSKM_NO = MSKM.MSKM_NO AND KK_T_MSKM_RCNT.MK_FLG = '0') AND MSKM_DTL.GENE_ADD_DTM = (SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX FROM KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT WHERE KK_T_MSKM_DTL_RCNT.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO AND KK_T_MSKM_DTL_RCNT.MK_FLG = '0') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_013) #--KKPRC02204(割引サービス自動適用ＣＣの設定値取得） #----------------------------------------------------------- --KEY:KK_SELECT_013 SELECT KK0011.MSKM_NO, KK0011.MSKM_SBT_CD, KK0021.SYSID FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_DTL_NO = ? AND KK0021.MK_FLG = '0' WHERE KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) as MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.SYSID = KK0021.SYSID AND KK0021_GENE.MK_FLG = '0' ) AND KK0011.GENE_ADD_DTM = (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) as MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.SYSID = KK0011.SYSID AND KK0011_GENE.MK_FLG = '0' ) AND KK0011.MSKM_NO = KK0021.MSKM_NO AND KK0011.SYSID = KK0021.SYSID AND KK0011.MK_FLG = '0' #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_039) #--申込番号を元に申込種別コードを取得する #----------------------------------------------------------- --KEY:KK_SELECT_039 SELECT CASE WHEN KK0011_1.MSKM_SBT_CD = '00017' AND KK0011_1.ADCHG_STAT = '003' THEN '00001' ELSE KK0011_1.MSKM_SBT_CD END AS MSKM_SBT_CD FROM ( SELECT KK0011.MSKM_SBT_CD ,KK2091.ADCHG_STAT FROM KK_T_MSKM KK0011 LEFT OUTER JOIN KK_T_ADCHG KK2091 ON KK0011.MSKM_NO = KK2091.MSKM_NO AND KK2091.MK_FLG = '0' WHERE KK0011.MSKM_NO = ? AND KK0011.GENE_ADD_DTM = (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE .MK_FLG = '0') )KK0011_1 #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_SELECT_040) #--申込書番号・サービス契約番号・サービスコードを取得する #----------------------------------------------------------- --KEY:KK_SELECT_040 SELECT KK0021.MSKMSHO_NO , KK0081_MAX.SVC_KEI_NO , KK0081_MAX.SVC_CD FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_NO = KK0011.MSKM_NO AND KK0011.MSKM_NO = ? AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) = ( SELECT KK0011.MSKM_NO , MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0' GROUP BY KK0011_GENE.MSKM_NO ) AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = ( SELECT KK0021_GENE.MSKM_DTL_NO , MIN(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MIN FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0' GROUP BY KK0021_GENE.MSKM_DTL_NO ) INNER JOIN KK_T_SVC_KEI KK0081 ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = ( SELECT MIN(KK0081_GENE1.RSV_APLY_YMD || KK0081_GENE1.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE1 WHERE KK0081_GENE1.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_GENE1.RSV_APLY_YMD <= ? AND KK0081_GENE1.RSV_APLY_CD = '2' AND KK0081_GENE1.MK_FLG = '0' ) INNER JOIN KK_T_SVC_KEI KK0081_MAX ON KK0081_MAX.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK0081_MAX.RSV_APLY_YMD || KK0081_MAX.GENE_ADD_DTM = ( SELECT MAX(KK0081_GENE2.RSV_APLY_YMD || KK0081_GENE2.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE2 WHERE KK0081_GENE2.SVC_KEI_NO = KK0081_MAX.SVC_KEI_NO AND KK0081_GENE2.RSV_APLY_YMD <= ? AND KK0081_GENE2.RSV_APLY_CD = '2' AND KK0081_GENE2.MK_FLG = '0' ) WHERE KK0081_MAX.SVC_KEI_STAT IN ('020', '030', '100') AND KK0081_MAX.SHOSA_DSL_FIN_CD != '2' AND KK0081_MAX.PRC_GRP_CD IN ('02', '03', '04', '10', '11', '12', '13') AND KK0081_MAX.PCRS_CD != 'A23' ORDER BY KK0081_MAX.SVC_CD #- #----------------------------------------------------------- #--申込(KK_T_MSKM-KK_UPDATE_001) #--申込書明細番号・サービス契約番号・サービスコードを取得する #----------------------------------------------------------- --KEY:KK_UPDATE_001 UPDATE KK_T_MSKM KK0011 SET KK0011.SHOSA_FIN_TCHI = ? , KK0011.UPD_DTM = 'UPD_DTM' , KK0011.UPD_OPEACNT = 'UPD_OPE', KK0011.UPD_UNYO_YMD = 'UPD_UNYO', KK0011.UPD_TRN_ID = 'UPD_TRN' WHERE KK0011.MSKM_NO = ? AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) = ( SELECT KK0011.MSKM_NO , MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MIN FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0' GROUP BY KK0011_GENE.MSKM_NO ) #- #----------------------------------------------------------- #--申込(KK_T_MSKM-CN_SELECT_001) #--CNPRC03302_コンテンツ利用自動開始登録 #--申込番号に紐づくサービス契約情報を取得する #----------------------------------------------------------- --KEY:CN_SELECT_001 SELECT KK0081_CUR.PCRS_CD ,KK0081_CUR.SVC_KEI_STAT ,KK0081_CUR.PRC_GRP_CD ,KK0081_CUR.SVC_KEI_NO FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_NO = KK0011.MSKM_NO AND KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081_MIN ON KK0081_MIN.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081_MIN.RSV_APLY_YMD || KK0081_MIN.GENE_ADD_DTM = (SELECT MIN(KK0081_GENE1.RSV_APLY_YMD || KK0081_GENE1.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE1 WHERE KK0081_GENE1.SVC_KEI_NO = KK0081_MIN.SVC_KEI_NO AND KK0081_GENE1.RSV_APLY_YMD <= ? AND KK0081_GENE1.RSV_APLY_CD = '2' AND KK0081_GENE1.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081_CUR ON KK0081_CUR.SVC_KEI_NO = KK0081_MIN.SVC_KEI_NO AND KK0081_CUR.RSV_APLY_YMD || KK0081_CUR.GENE_ADD_DTM = (SELECT MAX(KK0081_GENE2.RSV_APLY_YMD || KK0081_GENE2.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE2 WHERE KK0081_GENE2.SVC_KEI_NO = KK0081_CUR.SVC_KEI_NO AND KK0081_GENE2.RSV_APLY_YMD <= ? AND KK0081_GENE2.RSV_APLY_CD = '2' AND KK0081_GENE2.MK_FLG = '0') WHERE KK0011.MSKM_NO = ? AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) = (SELECT KK0011_GENE.MSKM_NO, MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0' GROUP BY KK0011_GENE.MSKM_NO) AND KK0081_CUR.PRC_GRP_CD IN ('02', '03', '04', '10', '11', '12', '13', '16') #- #----------------------------------------------------------- #--申込(KK_T_MSKM-CN_SELECT_002) #--CNPRC03302_コンテンツ利用自動開始登録 #--申込番号に紐づくネットのサービス契約情報を取得する #----------------------------------------------------------- --KEY:CN_SELECT_002 SELECT KK0081_CUR.SVC_KEI_NO ,KK0081_CUR.SVC_KEI_STAT ,KK0081_CUR.SYSID AS SYSID_CUR ,KK0081_MIN.SYSID AS SYSID_MIN FROM KK_T_MSKM KK0011 INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0021.MSKM_NO = KK0011.MSKM_NO AND KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_GENE_MAX FROM KK_T_MSKM_DTL KK0021_GENE WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0021_GENE.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081_MIN ON KK0081_MIN.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND KK0081_MIN.RSV_APLY_YMD || KK0081_MIN.GENE_ADD_DTM = (SELECT MIN(KK0081_GENE1.RSV_APLY_YMD || KK0081_GENE1.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE1 WHERE KK0081_GENE1.SVC_KEI_NO = KK0081_MIN.SVC_KEI_NO AND KK0081_GENE1.RSV_APLY_YMD <= ? AND KK0081_GENE1.RSV_APLY_CD = '2' AND KK0081_GENE1.MK_FLG = '0') INNER JOIN KK_T_SVC_KEI KK0081_CUR ON KK0081_CUR.SVC_KEI_NO = KK0081_MIN.SVC_KEI_NO AND KK0081_CUR.RSV_APLY_YMD || KK0081_CUR.GENE_ADD_DTM = (SELECT MAX(KK0081_GENE2.RSV_APLY_YMD || KK0081_GENE2.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK0081_GENE2 WHERE KK0081_GENE2.SVC_KEI_NO = KK0081_CUR.SVC_KEI_NO AND KK0081_GENE2.RSV_APLY_YMD <= ? AND KK0081_GENE2.RSV_APLY_CD = '2' AND KK0081_GENE2.MK_FLG = '0') WHERE KK0011.MSKM_NO = ? AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) = (SELECT KK0011_GENE.MSKM_NO, MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX FROM KK_T_MSKM KK0011_GENE WHERE KK0011_GENE.MSKM_NO = KK0011.MSKM_NO AND KK0011_GENE.MK_FLG = '0' GROUP BY KK0011_GENE.MSKM_NO) AND KK0081_CUR.PRC_GRP_CD IN ('02', '03', '04', '16') AND KK0081_CUR.SVC_KEI_STAT IN ('100', '210', '220', '910', '920') ORDER BY KK0081_CUR.PRC_GRP_CD ASC #-