/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEKK0481B004TPDA * ソースファイル名：JSYejbEKK0481B004TPDA.java * 作成者 ：EK911025 * 日付 ：2014年04月30日 *＜機能概要＞ * 割引サービス対象契約一覧照会（サービス契約内訳）テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ9.00.00 2014/04/30 FJ)寺園 ANK-2056-00-00　新規作成 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import com.fujitsu.futurity.model.base.CAANConnectionMgr; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANSQLFacility; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANJDBCUtil; import com.fujitsu.futurity.model.ejb.common.JSYejbConnection; import com.fujitsu.futurity.model.ejb.common.JSYejbLog; import com.fujitsu.futurity.model.ejb.common.StatusCodes; import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler; import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.Map; import eo.ejb.cbs.cbsmsg.EKK0481B004CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0481B004CBSMsg1List; import eo.ejb.cbm.entity.KK0161ETMsg; import eo.ejb.cbm.entity.KK0481ETMsg; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK0481B004_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0481B004TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0481B004TPDA() { } /** * EKK0481B004_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // パラメータのカウント int iPCnt = 0; // 申込明細番号 String mskmDtlNo = null; /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK0481ETMsg.getTableName()); // 直近で解約された「申込明細番号」を取得する mskmDtlNo = getMskmDtlNo(inMsg, inContext); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT KK0481.WRIB_SVC_KEI_NO, ") .append(" KK0481.WRIB_SVC_TRGT_KEI_NO, ") .append(" KK0481.GENE_ADD_DTM, ") .append(" (SELECT KK0021_01.MSKM_YMD ") .append(" FROM KK_T_MSKM_DTL KK0021_01 ") .append(" WHERE KK0021_01.MSKM_DTL_NO = KK0081.MSKM_DTL_NO ") .append(" AND (KK0021_01.MSKM_DTL_NO, KK0021_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_01_GENE.MSKM_DTL_NO, MAX(KK0021_01_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_01_GENE ") .append(" WHERE KK0021_01_GENE.MSKM_DTL_NO = KK0021_01.MSKM_DTL_NO ") .append(" AND KK0021_01_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0021_01_GENE.MSKM_DTL_NO)) AS MSKM_YMD_SVC_KEI, ") .append(" (SELECT KK0021_02.MSKM_YMD ") .append(" FROM KK_T_MSKM_DTL KK0021_02 ") .append(" WHERE KK0021_02.MSKM_DTL_NO = KK0161.MSKM_DTL_NO ") .append(" AND (KK0021_02.MSKM_DTL_NO, KK0021_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_02_GENE.MSKM_DTL_NO, MAX(KK0021_02_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_02_GENE ") .append(" WHERE KK0021_02_GENE.MSKM_DTL_NO = KK0021_02.MSKM_DTL_NO ") .append(" AND KK0021_02_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0021_02_GENE.MSKM_DTL_NO)) AS MSKM_YMD_SVC_KEI_UCWK, ") .append(" (SELECT KK0021_03.MSKM_YMD ") .append(" FROM KK_T_MSKM_DTL KK0021_03 ") .append(" WHERE KK0021_03.MSKM_DTL_NO = KK0351.MSKM_DTL_NO ") .append(" AND (KK0021_03.MSKM_DTL_NO, KK0021_03.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_03_GENE.MSKM_DTL_NO, MAX(KK0021_03_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_03_GENE ") .append(" WHERE KK0021_03_GENE.MSKM_DTL_NO = KK0021_03.MSKM_DTL_NO ") .append(" AND KK0021_03_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0021_03_GENE.MSKM_DTL_NO)) AS MSKM_YMD_OP_SVC_KEI, ") .append(" (SELECT KK0021_04.MSKM_YMD ") .append(" FROM KK_T_MSKM_DTL KK0021_04 ") .append(" WHERE KK0021_04.MSKM_DTL_NO = KK0401.MSKM_DTL_NO ") .append(" AND (KK0021_04.MSKM_DTL_NO, KK0021_04.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_04_GENE.MSKM_DTL_NO, MAX(KK0021_04_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_04_GENE ") .append(" WHERE KK0021_04_GENE.MSKM_DTL_NO = KK0021_04.MSKM_DTL_NO ") .append(" AND KK0021_04_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0021_04_GENE.MSKM_DTL_NO)) AS MSKM_YMD_SBOP_SVC_KEI, ") .append(" (SELECT KK0021_05.MSKM_YMD ") .append(" FROM KK_T_MSKM_DTL KK0021_05 ") .append(" WHERE KK0021_05.MSKM_DTL_NO = KK0341.MSKM_DTL_NO ") .append(" AND (KK0021_05.MSKM_DTL_NO, KK0021_05.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_05_GENE.MSKM_DTL_NO, MAX(KK0021_05_GENE.GENE_ADD_DTM) AS KK0021_MAX ") .append(" FROM KK_T_MSKM_DTL KK0021_05_GENE ") .append(" WHERE KK0021_05_GENE.MSKM_DTL_NO = KK0021_05.MSKM_DTL_NO ") .append(" AND KK0021_05_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0021_05_GENE.MSKM_DTL_NO)) AS MSKM_YMD_KKTK_SVC_KEI, ") .append(" KK0841.DSP_CAMPAIGN_CD, ") .append(" KK0841.WRIB_SVC_NM, ") .append(" KK0841.WRIB_APLY_OPTNTY_CD, ") .append(" (SELECT KK0751.SVC_NM ") .append(" FROM KK_M_SVC KK0751 ") .append(" WHERE KK0751.SVC_CD = KK0081.SVC_CD ") .append(" AND (KK0751.SVC_CD, KK0751.RSV_APLY_YMD || KK0751.GENE_ADD_DTM) = ") .append(" (SELECT KK0751_GENE.SVC_CD, MAX(KK0751_GENE.RSV_APLY_YMD || KK0751_GENE.GENE_ADD_DTM) AS KK0751_MAX ") .append(" FROM KK_M_SVC KK0751_GENE ") .append(" WHERE KK0751_GENE.SVC_CD = KK0751.SVC_CD ") .append(" AND KK0751_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0751_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0751_GENE.SVC_CD)) AS SVC_NM, ") .append(" (SELECT KK0781.PRC_GRP_NM ") .append(" FROM KK_M_PRC_GRP KK0781 ") .append(" WHERE KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD ") .append(" AND (KK0781.PRC_GRP_CD, KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM) = ") .append(" (SELECT KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ") .append(" FROM KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0781_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0781_GENE.PRC_GRP_CD)) AS PRC_GRP_NM, ") .append(" (SELECT KK0791_01.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_01 ") .append(" WHERE KK0791_01.PCRS_CD = KK0081.PCRS_CD ") .append(" AND (KK0791_01.PCRS_CD, KK0791_01.RSV_APLY_YMD || KK0791_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_01_GENE.PCRS_CD, MAX(KK0791_01_GENE.RSV_APLY_YMD || KK0791_01_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_01_GENE ") .append(" WHERE KK0791_01_GENE.PCRS_CD = KK0791_01.PCRS_CD ") .append(" AND KK0791_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_01_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0791_01_GENE.PCRS_CD)) AS PCRS_NM_SVC_KEI, ") .append(" (SELECT KK0791_02.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_02 ") .append(" WHERE KK0791_02.PCRS_CD = KK0161.PCRS_CD ") .append(" AND (KK0791_02.PCRS_CD, KK0791_02.RSV_APLY_YMD || KK0791_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_02_GENE.PCRS_CD, MAX(KK0791_02_GENE.RSV_APLY_YMD || KK0791_02_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_02_GENE ") .append(" WHERE KK0791_02_GENE.PCRS_CD = KK0791_02.PCRS_CD ") .append(" AND KK0791_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_02_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0791_02_GENE.PCRS_CD)) AS PCRS_NM_SVC_KEI_UCWK, ") .append(" (SELECT KK0791_03.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_03 ") .append(" WHERE KK0791_03.PCRS_CD = KK0351.PCRS_CD ") .append(" AND (KK0791_03.PCRS_CD, KK0791_03.RSV_APLY_YMD || KK0791_03.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_03_GENE.PCRS_CD, MAX(KK0791_03_GENE.RSV_APLY_YMD || KK0791_03_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_03_GENE ") .append(" WHERE KK0791_03_GENE.PCRS_CD = KK0791_03.PCRS_CD ") .append(" AND KK0791_03_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_03_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0791_03_GENE.PCRS_CD )) AS PCRS_NM_OP_SVC_KEI, ") .append(" (SELECT KK0791_04.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_04 ") .append(" WHERE KK0791_04.PCRS_CD = KK0401.PCRS_CD ") .append(" AND (KK0791_04.PCRS_CD, KK0791_04.RSV_APLY_YMD || KK0791_04.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_04_GENE.PCRS_CD, MAX(KK0791_04_GENE.RSV_APLY_YMD || KK0791_04_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_04_GENE ") .append(" WHERE KK0791_04_GENE.PCRS_CD = KK0791_04.PCRS_CD ") .append(" AND KK0791_04_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_04_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0791_04_GENE.PCRS_CD)) AS PCRS_NM_SBOP_SVC_KEI, ") .append(" (SELECT KK0791_05.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_05 ") .append(" WHERE KK0791_05.PCRS_CD = KK0341.PCRS_CD ") .append(" AND (KK0791_05.PCRS_CD, KK0791_05.RSV_APLY_YMD || KK0791_05.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_05_GENE.PCRS_CD, MAX(KK0791_05_GENE.RSV_APLY_YMD || KK0791_05_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_05_GENE ") .append(" WHERE KK0791_05_GENE.PCRS_CD = KK0791_05.PCRS_CD ") .append(" AND KK0791_05_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_05_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0791_05_GENE.PCRS_CD)) AS PCRS_NM_KKTK_SVC_KEI, ") .append(" (SELECT KK0591_01.PPLAN_NM ") .append(" FROM KK_M_PPLAN KK0591_01 ") .append(" WHERE KK0591_01.PCRS_CD = KK0081.PCRS_CD ") .append(" AND KK0591_01.PPLAN_CD = KK0081.PPLAN_CD ") .append(" AND KK0591_01.MK_FLG= '0') AS PPLAN_NM_SVC_KEI, ") .append(" (SELECT KK0591_02.PPLAN_NM ") .append(" FROM KK_M_PPLAN KK0591_02 ") .append(" WHERE KK0591_02.PCRS_CD = KK0161.PCRS_CD ") .append(" AND KK0591_02.PPLAN_CD = KK0161.PPLAN_CD ") .append(" AND KK0591_02.MK_FLG= '0') AS PPLAN_NM_SVC_KEI_UCWK, ") .append(" (SELECT KK0591_03.PPLAN_NM ") .append(" FROM KK_M_PPLAN KK0591_03 ") .append(" WHERE KK0591_03.PCRS_CD = KK0351.PCRS_CD ") .append(" AND KK0591_03.PPLAN_CD = KK0351.PPLAN_CD ") .append(" AND KK0591_03.MK_FLG= '0') AS PPLAN_NM_OP_SVC_KEI, ") .append(" (SELECT KK0591_04.PPLAN_NM ") .append(" FROM KK_M_PPLAN KK0591_04 ") .append(" WHERE KK0591_04.PCRS_CD = KK0401.PCRS_CD ") .append(" AND KK0591_04.PPLAN_CD = KK0401.PPLAN_CD ") .append(" AND KK0591_04.MK_FLG= '0') AS PPLAN_NM_SBOP_SVC_KEI, ") .append(" (SELECT KK0591_05.PPLAN_NM ") .append(" FROM KK_M_PPLAN KK0591_05 ") .append(" WHERE KK0591_05.PCRS_CD = KK0341.PCRS_CD ") .append(" AND KK0591_05.PPLAN_CD = KK0341.PPLAN_CD ") .append(" AND KK0591_05.MK_FLG= '0') AS PPLAN_NM_KKTK_SVC_KEI, ") .append(" (SELECT KK0821.OP_SVC_NM ") .append(" FROM KK_M_OP_SVC KK0821 ") .append(" WHERE KK0821.OP_SVC_CD = KK0351.OP_SVC_CD ") .append(" AND (KK0821.OP_SVC_CD, KK0821.RSV_APLY_YMD || KK0821.GENE_ADD_DTM) = ") .append(" (SELECT KK0821_GENE.OP_SVC_CD, MAX(KK0821_GENE.RSV_APLY_YMD || KK0821_GENE.GENE_ADD_DTM) AS KK0821_MAX ") .append(" FROM KK_M_OP_SVC KK0821_GENE ") .append(" WHERE KK0821_GENE.OP_SVC_CD = KK0821.OP_SVC_CD ") .append(" AND KK0821_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0821_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0821_GENE.OP_SVC_CD)) AS OP_SVC_NM, ") .append(" (SELECT KK0771.KKTK_SVC_NM ") .append(" FROM KK_M_KKTK_SVC KK0771 ") .append(" WHERE KK0771.KKTK_SVC_CD = KK0341.KKTK_SVC_CD ") .append(" AND KK0771.MK_FLG= '0') AS KKTK_SVC_NM, ") .append(" KK0481.SVC_KEI_NO, ") .append(" KK0481.WRISVC_TG_KEI_TSTAYMD, ") .append(" KK0481.WRISVC_TG_KEI_TENDYMD, ") .append(" KK0451.WRIB_SVC_KEI_STAT, ") .append(" KK0481.SVC_KEI_UCWK_NO, ") .append(" KK0481.OP_SVC_KEI_NO, ") .append(" KK0481.SBOP_SVC_KEI_NO, ") .append(" KK0481.KKTK_SVC_KEI_NO, ") .append(" KK0481.SEIOPSVC_KEI_NO ") .append(" ,KK0481.TG_KEI_SKBT_CD ") .append(" ,KK0481.WRIB_SVC_TRGT_SVC_CD ") .append(" ,KK0481.SET_ADD_YMD ") .append(" ,(SELECT KK0831.SBOP_SVC_NM ") .append(" FROM KK_M_SBOP_SVC KK0831 ") .append(" WHERE KK0831.SBOP_SVC_CD = KK0401.SBOP_SVC_CD ") .append(" AND (KK0831.SBOP_SVC_CD, KK0831.GENE_ADD_DTM) = ") .append(" (SELECT KK0831_GENE.SBOP_SVC_CD, MAX(KK0831_GENE.GENE_ADD_DTM) AS KK0831_MAX ") .append(" FROM KK_M_SBOP_SVC KK0831_GENE ") .append(" WHERE KK0831_GENE.SBOP_SVC_CD = KK0831.SBOP_SVC_CD ") .append(" AND KK0831_GENE.MK_FLG= '0' ") .append(" GROUP BY KK0831_GENE.SBOP_SVC_CD)) AS SBOP_SVC_NM ") .append(" ,(SELECT KK0761.SEIOPSVC_NM ") .append(" FROM KK_M_SEIOPSVC KK0761 ") .append(" WHERE KK0761.SEIOPSVC_CD = KK0441.SEIOPSVC_CD ") .append(" AND KK0761.MK_FLG= '0') AS SEIOPSVC_NM ") .append(" FROM KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON KK0481.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" AND KK0451.MSKM_DTL_NO = ? ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0081_TMP.SVC_KEI_NO, ") .append(" KK0081_TMP.MSKM_DTL_NO, ") .append(" KK0081_TMP.SVC_CD, ") .append(" KK0081_TMP.PRC_GRP_CD, ") .append(" KK0081_TMP.PCRS_CD, ") .append(" KK0081_TMP.PPLAN_CD ") .append(" FROM KK_T_SVC_KEI KK0081_TMP ") .append(" WHERE (KK0081_TMP.SVC_KEI_NO, KK0081_TMP.RSV_APLY_YMD || KK0081_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081_TMP.SVC_KEI_NO ") .append(" AND KK0081_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0081_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0081_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0081_GENE.SVC_KEI_NO) ") .append(" ) KK0081 ") .append(" ON KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0161_TMP.SVC_KEI_UCWK_NO, ") .append(" KK0161_TMP.MSKM_DTL_NO, ") .append(" KK0161_TMP.PCRS_CD, ") .append(" KK0161_TMP.PPLAN_CD ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_TMP ") .append(" WHERE (KK0161_TMP.SVC_KEI_UCWK_NO, KK0161_TMP.RSV_APLY_YMD || KK0161_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ") .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161_TMP.SVC_KEI_UCWK_NO ") .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0161_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO) ") .append(" ) KK0161 ") .append(" ON KK0481.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0351_TMP.OP_SVC_KEI_NO, ") .append(" KK0351_TMP.MSKM_DTL_NO, ") .append(" KK0351_TMP.PCRS_CD, ") .append(" KK0351_TMP.PPLAN_CD, ") .append(" KK0351_TMP.OP_SVC_CD ") .append(" FROM KK_T_OP_SVC_KEI KK0351_TMP ") .append(" WHERE (KK0351_TMP.OP_SVC_KEI_NO, KK0351_TMP.RSV_APLY_YMD || KK0351_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351_TMP.OP_SVC_KEI_NO ") .append(" AND KK0351_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0351_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0351_GENE.OP_SVC_KEI_NO) ") .append(" ) KK0351 ") .append(" ON KK0481.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0401_TMP.SBOP_SVC_KEI_NO, ") .append(" KK0401_TMP.MSKM_DTL_NO, ") .append(" KK0401_TMP.PCRS_CD, ") .append(" KK0401_TMP.PPLAN_CD, ") .append(" KK0401_TMP.SBOP_SVC_CD ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_TMP ") .append(" WHERE (KK0401_TMP.OP_SVC_KEI_NO, KK0401_TMP.SBOP_SVC_KEI_NO, KK0401_TMP.RSV_APLY_YMD || KK0401_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0401_GENE.OP_SVC_KEI_NO, KK0401_GENE.SBOP_SVC_KEI_NO, MAX(KK0401_GENE.RSV_APLY_YMD || KK0401_GENE.GENE_ADD_DTM) AS KK0401_MAX ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_GENE ") .append(" WHERE KK0401_GENE.OP_SVC_KEI_NO = KK0401_TMP.OP_SVC_KEI_NO ") .append(" AND KK0401_GENE.SBOP_SVC_KEI_NO = KK0401_TMP.SBOP_SVC_KEI_NO ") .append(" AND KK0401_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0401_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0401_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0401_GENE.OP_SVC_KEI_NO, KK0401_GENE.SBOP_SVC_KEI_NO) ") .append(" ) KK0401 ") .append(" ON KK0481.SBOP_SVC_KEI_NO = KK0401.SBOP_SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0341_TMP.KKTK_SVC_KEI_NO, ") .append(" KK0341_TMP.MSKM_DTL_NO, ") .append(" KK0341_TMP.PCRS_CD, ") .append(" KK0341_TMP.PPLAN_CD, ") .append(" KK0341_TMP.KKTK_SVC_CD, ") .append(" KK0341_TMP.SVC_KEI_NO ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_TMP ") .append(" WHERE (KK0341_TMP.KKTK_SVC_KEI_NO, KK0341_TMP.RSV_APLY_YMD || KK0341_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341_TMP.KKTK_SVC_KEI_NO ") .append(" AND KK0341_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0341_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO) ") .append(" ) KK0341 ") .append(" ON KK0481.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0841_TMP.WRIB_SVC_CD, ") .append(" KK0841_TMP.DSP_JUN, ") .append(" KK0841_TMP.DSP_CAMPAIGN_CD, ") .append(" KK0841_TMP.WRIB_SVC_NM, ") .append(" KK0841_TMP.WRIB_APLY_OPTNTY_CD ") .append(" FROM KK_M_WRIB_SVC KK0841_TMP ") .append(" WHERE (KK0841_TMP.WRIB_SVC_CD, KK0841_TMP.RSV_APLY_YMD || KK0841_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0841_GENE.WRIB_SVC_CD, MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX ") .append(" FROM KK_M_WRIB_SVC KK0841_GENE ") .append(" WHERE KK0841_GENE.WRIB_SVC_CD = KK0841_TMP.WRIB_SVC_CD ") .append(" AND KK0841_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0841_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0841_GENE.WRIB_SVC_CD) ") .append(" ) KK0841 ") .append(" ON KK0451.WRIB_SVC_CD = KK0841.WRIB_SVC_CD ") .append(" LEFT OUTER JOIN ") .append(" (SELECT KK0441_TMP.SEIOPSVC_KEI_NO, ") .append(" KK0441_TMP.PCRS_CD, ") .append(" KK0441_TMP.PPLAN_CD, ") .append(" KK0441_TMP.SEIOPSVC_CD, ") .append(" KK0441_TMP.SVC_KEI_NO ") .append(" FROM KK_T_SEIOPSVC_KEI KK0441_TMP ") .append(" WHERE (KK0441_TMP.SEIOPSVC_KEI_NO, KK0441_TMP.RSV_APLY_YMD || KK0441_TMP.GENE_ADD_DTM) = ") .append(" (SELECT KK0441_GENE.SEIOPSVC_KEI_NO, MAX(KK0441_GENE.RSV_APLY_YMD || KK0441_GENE.GENE_ADD_DTM) AS KK0441_MAX ") .append(" FROM KK_T_SEIOPSVC_KEI KK0441_GENE ") .append(" WHERE KK0441_GENE.SEIOPSVC_KEI_NO = KK0441_TMP.SEIOPSVC_KEI_NO ") .append(" AND KK0441_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0441_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0441_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0441_GENE.SEIOPSVC_KEI_NO) ") .append(" ) KK0441 ") .append(" ON KK0441.SEIOPSVC_KEI_NO = KK0481.SEIOPSVC_KEI_NO "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK0481B004CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM) = ") .append(" (SELECT KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO, MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_MAX ") .append(" FROM KK_T_WRISVC_TG_KEI KK0481_GENE ") .append(" WHERE KK0481_GENE.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" AND KK0481_GENE.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO ") .append(" AND KK0481_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO) ") .append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ") .append(" (SELECT KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX ") .append(" FROM KK_T_WRIB_SVC_KEI KK0451_GENE ") .append(" WHERE KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" AND KK0451_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0451_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0451_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0451_GENE.WRIB_SVC_KEI_NO) ") .append(" AND KK0481.SVC_KEI_UCWK_NO = ? ") .append(" AND KK0451.SYSID = ? ") .append(" ORDER BY KK0841.DSP_JUN ASC "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 if ( "1".equals( inMsg.getString(EKK0481B004CBSMsg.FUNC_CODE) ) ) { //運用日付の設定 CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, mskmDtlNo); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); CAANJDBCUtil.setParam(pstmt, ++iPCnt, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0481B004CBSMsg.KEY_SVC_KEI_UCWK_NO)); // パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0481B004CBSMsg.KEY_SYSID)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0481B004CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0481B004CBSMsg1List.WRIB_SVC_KEI_NO , EKK0481B004CBSMsg1List.WRIB_SVC_TRGT_KEI_NO , EKK0481B004CBSMsg1List.GENE_ADD_DTM , EKK0481B004CBSMsg1List.MSKM_YMD_SVC_KEI , EKK0481B004CBSMsg1List.MSKM_YMD_SVC_KEI_UCWK , EKK0481B004CBSMsg1List.MSKM_YMD_OP_SVC_KEI , EKK0481B004CBSMsg1List.MSKM_YMD_SBOP_SVC_KEI , EKK0481B004CBSMsg1List.MSKM_YMD_KKTK_SVC_KEI , EKK0481B004CBSMsg1List.DSP_CAMPAIGN_CD , EKK0481B004CBSMsg1List.WRIB_SVC_NM , EKK0481B004CBSMsg1List.WRIB_APLY_OPTNTY_CD , EKK0481B004CBSMsg1List.SVC_NM , EKK0481B004CBSMsg1List.PRC_GRP_NM , EKK0481B004CBSMsg1List.PCRS_NM_SVC_KEI , EKK0481B004CBSMsg1List.PCRS_NM_SVC_KEI_UCWK , EKK0481B004CBSMsg1List.PCRS_NM_OP_SVC_KEI , EKK0481B004CBSMsg1List.PCRS_NM_SBOP_SVC_KEI , EKK0481B004CBSMsg1List.PCRS_NM_KKTK_SVC_KEI , EKK0481B004CBSMsg1List.PPLAN_NM_SVC_KEI , EKK0481B004CBSMsg1List.PPLAN_NM_SVC_KEI_UCWK , EKK0481B004CBSMsg1List.PPLAN_NM_OP_SVC_KEI , EKK0481B004CBSMsg1List.PPLAN_NM_SBOP_SVC_KEI , EKK0481B004CBSMsg1List.PPLAN_NM_KKTK_SVC_KEI , EKK0481B004CBSMsg1List.OP_SVC_NM , EKK0481B004CBSMsg1List.KKTK_SVC_NM , EKK0481B004CBSMsg1List.SVC_KEI_NO , EKK0481B004CBSMsg1List.WRISVC_TG_KEI_TSTAYMD , EKK0481B004CBSMsg1List.WRISVC_TG_KEI_TENDYMD , EKK0481B004CBSMsg1List.WRIB_SVC_KEI_STAT , EKK0481B004CBSMsg1List.SVC_KEI_UCWK_NO , EKK0481B004CBSMsg1List.OP_SVC_KEI_NO , EKK0481B004CBSMsg1List.SBOP_SVC_KEI_NO , EKK0481B004CBSMsg1List.KKTK_SVC_KEI_NO , EKK0481B004CBSMsg1List.SEIOPSVC_KEI_NO , EKK0481B004CBSMsg1List.TG_KEI_SKBT_CD , EKK0481B004CBSMsg1List.WRIB_SVC_TRGT_SVC_CD , EKK0481B004CBSMsg1List.SET_ADD_YMD , EKK0481B004CBSMsg1List.SBOP_SVC_NM , EKK0481B004CBSMsg1List.SEIOPSVC_NM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0481B004CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0481B004CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0481B004CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { // 資源の解放 try{ if(rsltQuery != null){ rsltQuery.close(); } if(pstmt != null){ pstmt.close(); } if(con1 != null){ closeConnection(con1); } }catch(SQLException e){ inMsg.set(EKK0481B004CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** *

* 対象申込明細番号取得処理 *

* @param inMsg 処理対象のメッセージキャリア * @param inContext Agentから渡されたAgentDispatchContext * @return 取得結果 */ public String getMskmDtlNo(CAANMsg inMsg, AgentDispatchContext inContext) { ArrayList> mapList = new ArrayList>(); // コネクション Connection con = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; try { // 対象テーブルのコネクション取得 con = JSYejbConnection.getConnection(KK0161ETMsg.getTableName()); // SQL文 StringBuffer sbSql = new StringBuffer(); sbSql.append(" SELECT KK0161.GENE_ADD_DTM "); sbSql.append(" , KK0161.SVC_KEI_UCWK_STAT "); sbSql.append(" , KK0161.MSKM_DTL_NO "); sbSql.append(" FROM KK_T_SVC_KEI_UCWK KK0161 "); sbSql.append(" WHERE KK0161.SVC_KEI_UCWK_NO = ? "); sbSql.append(" AND KK0161.RSV_APLY_YMD <= ? "); sbSql.append(" AND KK0161.RSV_APLY_CD = '2' "); sbSql.append(" AND KK0161.MK_FLG = '0' "); sbSql.append(" ORDER BY GENE_ADD_DTM DESC "); // repareStatementにSQL文をセット pstmt = con.prepareStatement(sbSql.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sbSql); // パラメータの設定(サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0481B004CBSMsg.KEY_SVC_KEI_UCWK_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg)); // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // リスト格納用マップ HashMap map = null; // 取得結果をマップに格納 while (rsltQuery.next()) { // レコード情報格納用マップを初期化する map = new HashMap(); // 取得したレコード情報をセットする map.put(KK0161ETMsg.GENE_ADD_DTM, rsltQuery.getString(1)); map.put(KK0161ETMsg.SVC_KEI_UCWK_STAT, rsltQuery.getString(2)); map.put(KK0161ETMsg.MSKM_DTL_NO, rsltQuery.getString(3)); // セットしたMapをListに格納する mapList.add(map); } ArrayList> rsltMapList = mapList; String mskmDtlNo = null; // 直近で解約された「申込明細番号」を取得する for (int i = 1; i < rsltMapList.size(); i++) { if (!rsltMapList.get(1).get(KK0161ETMsg.SVC_KEI_UCWK_STAT).equals(rsltMapList.get(i).get(KK0161ETMsg.SVC_KEI_UCWK_STAT))) { break; } mskmDtlNo = rsltMapList.get(i).get(KK0161ETMsg.MSKM_DTL_NO); } return mskmDtlNo; } catch(SQLException se) { throw new CAANRuntimeException(se); } finally { // 資源の解放 try { if(rsltQuery != null) { rsltQuery.close(); } if(pstmt != null) { pstmt.close(); } if(con != null) { CAANConnectionMgr.getInstance().close(con); } } catch(SQLException se) { throw new CAANRuntimeException(se); } } } }