/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbEKK0081B512TPDA * ソースファイル名：JSYejbEKK0081B512TPDA.java * 作成者 ：EK909582 * 日付 ：2013年01月28日 *＜機能概要＞ * 問合せ対応サービス情報一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ4.00.00 2013/03/29 FJ）伊藤 TAI-2013-0000020 OT-2013-0000455の横並び対策 * ｖ5.00.00 2013/04/30 FJ）伊藤 IKK-2013-0001027関連対応 * ｖ5.00.01 2013/07/12 FJ）伊藤 LT-2013-0000469 * ｖ6.00.00 2013/08/23 FJ) 伊藤 OT-2013-0000752 [横展開]課金先の取得条件に適用期間を追加。 * ｖ6.00.01 2013/09/14 FJ) 伊藤 OT-2013-0002064 回線対象サービス契約の取得条件を変更。 * ｖ6.00.02 2013/12/07 FJ）伊藤 OM-2013-0004056 受付番号の検索条件を変更 * ｖ22.00.00 2016/01/18 FJ）柿坂 OM-2015-0003242 外部結合している提供方式契約料金の検索条件変更 * v71.00.00 2023/12/19 GDC)J.Tagongtong ANK-4468-00-00 _eo光ネット「シンプルプラン」追加対応 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.fujitsu.futurity.model.base.CAANJDBCUtil; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANSQLFacility; 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.AgentDispatchContext; import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler; import eo.common.constant.JCRStrConst; import eo.common.constant.JPCModelConstant; import eo.ejb.cbm.entity.KK0081ETMsg; import eo.ejb.cbs.cbsmsg.EKK0081B512CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0081B512CBSMsg1List; /** * * テンプレートDBアクセス部品　EKK0081B512_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0081B512TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0081B512TPDA() { } /** * EKK0081B512_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; //パラメータ設定リスト List replaceList = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK0081ETMsg.getTableName()); // ＫＥＹ＿サービス契約番号 String svcKeiNo = inMsg.getObject(EKK0081B512CBSMsg.KEY_SVC_KEI_NO).toString(); // ＫＥＹ＿運用日付 String opeDate = inMsg.getObject(EKK0081B512CBSMsg.KEY_OPE_DATE).toString(); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT PRCG.PRC_GRP_CD, ") // ANK-4468-00-00 MOD START // .append(" PRCG.PRC_GRP_NM, ") .append(" (CASE WHEN PRCG.PRC_GRP_CD = '02' ") .append(" AND PCRS.PCRS_CD IN ('A97','A99') ") .append(" THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_GRPNM_01' ") .append(" ) ELSE PRCG.PRC_GRP_NM ") .append(" END ) PRC_GRP_NM, ") // ANK-4468-00-00 MOD END .append(" PCRS.PCRS_CD, ") // ANK-4468-00-00 MOD START // .append(" PCRS.PCRS_NM, ") .append(" (CASE WHEN PRCG.PRC_GRP_CD = '02' ") .append(" AND PCRS.PCRS_CD IN ('A97','A99') ") .append(" THEN CASE ") .append(" WHEN PCRS.PCRS_CD = 'A97' ") .append(" THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_PCRSNM_02' ) ") .append(" WHEN PCRS.PCRS_CD = 'A99' ") .append(" THEN ( SELECT ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_SIMPL_C_PCRSNM_03' ) ") .append(" END ") .append(" ELSE PCRS.PCRS_NM ") .append(" END ) PCRS_NM, ") // ANK-4468-00-00 MOD END .append(" MKDT.MSKM_FORM_CD, ") .append(" (SELECT CD01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD01 ") .append(" WHERE MKDT.MSKM_FORM_CD = CD01.CD_DIV ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD01.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD01.CD_TENDYMD ") .append(" AND CD01.CD_SBT_CD = 'CD00591' ") .append(" AND CD01.MK_FLG = '0' ") .append(" ) AS MSKM_FORM_NM, ") .append(" SVCK.SVC_KEI_STAT, ") .append(" (SELECT CD02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD02 ") .append(" WHERE SVCK.SVC_KEI_STAT = CD02.CD_DIV ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD02.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD02.CD_TENDYMD ") .append(" AND CD02.CD_SBT_CD = 'CD00037' ") .append(" AND CD02.MK_FLG = '0' ") .append(" ) AS SVC_KEI_STAT_NM, ") .append(" SVCK.IDO_NG_STAT_CD, ") .append(" (SELECT CD04.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD04 ") .append(" WHERE SVCK.IDO_NG_STAT_CD = CD04.CD_DIV ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD04.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD04.CD_TENDYMD ") .append(" AND CD04.CD_SBT_CD = 'CD00280' ") .append(" AND CD04.MK_FLG = '0' ") .append(" ) AS IDO_NG_STAT_NM, ") .append(" TKSK.TOKUSOKU_STAT, ") .append(" (SELECT CD06.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD06 ") .append(" WHERE TKSK.TOKUSOKU_STAT = CD06.CD_DIV ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD06.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD06.CD_TENDYMD ") .append(" AND CD06.CD_SBT_CD = 'CD00184' ") .append(" AND CD06.MK_FLG = '0' ") .append(" ) AS TKSK_STAT_NM, ") .append(" SVCK.ADD_DTM, ") .append(" MKDT.MSKM_UK_DTM, ") .append(" MKDT.MSKM_YMD, ") .append(" MKDT.MSKMSHO_NO, ") .append(" MSAG1.AGNT_CD AS AGNT_CD1, ") .append(" MSAG2.AGNT_CD AS AGNT_CD2, ") .append(" MSAG3.AGNT_CD AS AGNT_CD3, ") .append(" MSAG4.AGNT_CD AS AGNT_CD4, ") .append(" AGNT1.AGNT_NM AS AGNT_NM1, ") .append(" AGNT2.AGNT_NM AS AGNT_NM2, ") .append(" AGNT3.AGNT_NM AS AGNT_NM3, ") .append(" AGNT4.AGNT_NM AS AGNT_NM4, ") .append(" KSUW.KAISEN_PLACE_PCD, ") .append(" KSUW.KAISEN_PLACE_STATE_NM, ") .append(" KSUW.KAISEN_PLACE_CITY_NM, ") .append(" KSUW.KAISEN_PLACE_OAZTSU_NM, ") .append(" KSUW.KAISEN_PLACE_AZCHO_NM, ") .append(" KSUW.KAISEN_PLACE_BNCHIGO, ") .append(" KSUW.KAISEN_PLACE_ADRTTM, ") .append(" KSUW.KAISEN_PLACE_ADRRM, ") .append(" SVCK.SVC_STA_YMD, ") .append(" SVCK.SVC_ENDYMD, ") .append(" SVCK.SVC_CHRG_STAYMD, ") .append(" SVCK.SVC_CHRG_ENDYMD, ") .append(" MKDT.OP_SVC_HKTGI_UM, ") .append(" (SELECT CD05.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD05 ") .append(" WHERE MKDT.OP_SVC_HKTGI_UM = CD05.CD_DIV ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD05.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD05.CD_TENDYMD ") .append(" AND CD05.CD_SBT_CD = 'CD00002' ") .append(" AND CD05.MK_FLG = '0' ") .append(" ) AS OP_SVC_HKTGI_UM_NM, ") .append(" MKDT.MSKM_UK_TNT_USER_ID, ") .append(" (SELECT USR1.USER_NM ") .append(" FROM ZM_M_USER USR1 ") .append(" WHERE MKDT.MSKM_UK_TNT_USER_ID = USR1.USER_ID ") .append(" AND USR1.MK_FLG = '0' ") .append(" ) AS UK_USER_NM, ") .append(" TMPP2.TMP_PAY_PRC_NM1 AS TMP_PAY_PRC_NM1, ") .append(" TMPP2.TMP_PAY_PRC_NM2 AS TMP_PAY_PRC_NM2, ") .append(" TMPP2.TMP_PAY_PRC_NM3 AS TMP_PAY_PRC_NM3, ") .append(" TMPP2.TMP_PAY_PRC_NM4 AS TMP_PAY_PRC_NM4, ") .append(" JMRT.ICJKN_SETTE_CD, ") .append(" JMRT.ICJKN_SHITEI_RT, ") .append(" JMRT.ICJKN_SHITEI_AMNT, ") .append(" ( ") .append(" CASE JMTM.MANS_ICJKN_TG_FLG WHEN '1' THEN ") .append(" TKHP1.KANYU_KEI_SEIKY_AMNT ") .append(" ELSE ") .append(" JMTM.TMP_PAY_PRC_AMNT ") .append(" END ") .append(" ) AS TMP_PAY_PRC_AMNT, ") .append(" ( ") .append(" SELECT CD07.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI CD07 ") .append(" WHERE CD07.CD_SBT_CD = 'CD01586' ") .append(" AND CD07.CD_DIV = ") .append(" ( ") .append(" SELECT CASE WHEN SVCK_SUB.SVC_KEI_STAT = '210' AND SVCK_SUB.PAUSE_STP_CD = '02' ") .append(" THEN '211' ") .append(" WHEN SVCK_SUB.SVC_KEI_STAT = '910' AND SVCK_SUB.SVC_DLRE_CD = '02' ") .append(" THEN '911' ") .append(" ELSE SVCK_SUB.SVC_KEI_STAT ") .append(" END DISP_SVC_KEI_STAT ") .append(" FROM KK_T_SVC_KEI SVCK_SUB ") .append(" WHERE SVCK_SUB.SVC_KEI_NO = SVCK.SVC_KEI_NO ") .append(" AND SVCK_SUB.GENE_ADD_DTM = SVCK.GENE_ADD_DTM ") .append(" ) ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) >= CD07.CD_TSTAYMD ") .append(" AND SUBSTR(SVCK.UPD_DTM, 1, 8) <= CD07.CD_TENDYMD ") .append(" AND CD07.MK_FLG = '0' ") .append(" ) AS DSP_SVC_KEI_STAT_NM, ") .append(" SVCK.SVC_DSL_KISAN_YMD ") .append(" FROM KK_T_SVC_KEI SVCK ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT PRCG1.PRC_GRP_CD, ") .append(" PRCG1.PRC_GRP_NM ") .append(" FROM KK_M_PRC_GRP PRCG1 ") .append(" WHERE (PRCG1.PRC_GRP_CD,PRCG1.RSV_APLY_YMD || PRCG1.GENE_ADD_DTM) = (SELECT PRCG1.PRC_GRP_CD, ") .append(" MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE KK0781_GENE.PRC_GRP_CD = PRCG1.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 ") .append(" ) ") .append(" ) PRCG ON (SVCK.PRC_GRP_CD = PRCG.PRC_GRP_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT PCRS1.PCRS_CD, ") .append(" PCRS1.PCRS_NM ") .append(" FROM KK_M_PCRS PCRS1 ") .append(" WHERE (PCRS1.PCRS_CD,PCRS1.RSV_APLY_YMD || PCRS1.GENE_ADD_DTM) = (SELECT KK0791_GENE.PCRS_CD, ") .append(" MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = PCRS1.PCRS_CD ") .append(" AND KK0791_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_GENE.PCRS_CD ") .append(" ) ") .append(" ) PCRS ON (SVCK.PCRS_CD = PCRS.PCRS_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT MKDT3.MSKM_DTL_NO, ") .append(" MKDT3.MSKM_NO, ") .append(" MKDT3.MSKMSHO_NO, ") .append(" MKDT3.MSKM_FORM_CD, ") .append(" MKDT3.OP_SVC_HKTGI_UM, ") .append(" MSKM3.MSKM_UK_DTM, ") .append(" MSKM3.MSKM_YMD, ") .append(" MSKM3.MSKM_UK_TNT_USER_ID, ") .append(" MKDT3.SVC_KEI_NO ") .append(" FROM (SELECT MKDT1.MSKM_DTL_NO, ") .append(" MKDT1.MSKM_NO, ") .append(" MKDT1.MSKMSHO_NO, ") .append(" MKDT1.MSKM_FORM_CD, ") .append(" MKDT1.OP_SVC_HKTGI_UM, ") .append(" SVC1.SVC_KEI_NO ") .append(" FROM KK_T_MSKM_DTL MKDT1 ") .append(" INNER JOIN KK_T_SVC_KEI SVC1 ") .append(" ON (SVC1.MSKM_DTL_NO = MKDT1.MSKM_DTL_NO) ") .append(" WHERE (MKDT1.MSKM_DTL_NO,MKDT1.GENE_ADD_DTM) = (SELECT KK0021_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = MKDT1.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO ") .append(" ) ") .append(" AND SVC1.GENE_ADD_DTM = (SELECT MIN(SVC1_GENE.GENE_ADD_DTM) AS SVC1_MIN ") .append(" FROM KK_T_SVC_KEI SVC1_GENE ") .append(" WHERE SVC1_GENE.SVC_KEI_NO = SVC1.SVC_KEI_NO ") .append(" AND SVC1_GENE.MK_FLG = '0') ") .append(" ) MKDT3, ") .append(" (SELECT MSKM1.MSKM_UK_DTM, ") .append(" MSKM1.MSKM_NO, ") .append(" MSKM1.MSKM_YMD, ") .append(" MSKM1.MSKM_UK_TNT_USER_ID ") .append(" FROM KK_T_MSKM MSKM1 ") .append(" WHERE (MSKM1.MSKM_NO,MSKM1.GENE_ADD_DTM) = (SELECT KK0011_GENE.MSKM_NO, ") .append(" MAX(KK0011_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM KK0011_GENE ") .append(" WHERE KK0011_GENE.MSKM_NO = MSKM1.MSKM_NO ") .append(" AND KK0011_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0011_GENE.MSKM_NO ") .append(" ) ") .append(" AND (MSKM1.MSKM_SBT_CD = '00001' OR MSKM1.MSKM_SBT_CD = '00002') ") .append(" ) MSKM3 ") .append(" WHERE MSKM3.MSKM_NO = MKDT3.MSKM_NO ") .append(" ) MKDT ON (SVCK.SVC_KEI_NO = MKDT.SVC_KEI_NO) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT KTSV1.SVC_KEI_NO, ") .append(" SKUW1.KAISEN_PLACE_PCD, ") .append(" SKUW1.KAISEN_PLACE_STATE_NM, ") .append(" SKUW1.KAISEN_PLACE_CITY_NM, ") .append(" SKUW1.KAISEN_PLACE_OAZTSU_NM, ") .append(" SKUW1.KAISEN_PLACE_AZCHO_NM, ") .append(" SKUW1.KAISEN_PLACE_BNCHIGO, ") .append(" SKUW1.KAISEN_PLACE_ADRTTM, ") .append(" SKUW1.KAISEN_PLACE_ADRRM ") .append(" FROM KK_T_KAISEN_TG_SVKEI KTSV1 ") .append(" LEFT OUTER JOIN (SELECT SKUW01.SVC_KEI_KAISEN_UCWK_NO, ") .append(" SKUW01.KAISEN_PLACE_PCD, ") .append(" SKUW01.KAISEN_PLACE_STATE_NM, ") .append(" SKUW01.KAISEN_PLACE_CITY_NM, ") .append(" SKUW01.KAISEN_PLACE_OAZTSU_NM, ") .append(" SKUW01.KAISEN_PLACE_AZCHO_NM, ") .append(" SKUW01.KAISEN_PLACE_BNCHIGO, ") .append(" SKUW01.KAISEN_PLACE_ADRTTM, ") .append(" SKUW01.KAISEN_PLACE_ADRRM ") .append(" FROM KK_T_SVKEI_KAISEN_UW SKUW01 ") .append(" WHERE (SKUW01.SVC_KEI_KAISEN_UCWK_NO,SKUW01.GENE_ADD_DTM) = (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, ") .append(" MAX(KK0251_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE ") .append(" WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = SKUW01.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0251_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO ") .append(" ) ") .append(" ) SKUW1 ON (KTSV1.SVC_KEI_KAISEN_UCWK_NO = SKUW1.SVC_KEI_KAISEN_UCWK_NO) ") .append(" WHERE "); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" KTSV1.SVC_KEI_NO = ? ") .append(" AND "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" KTSV1.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KTSV1.MK_FLG = '0' ") .append(" AND (KTSV1.SVC_KEI_NO, KTSV1.KAISEN_UCWK_USE_ENDYMD) = ") .append(" ( ") .append(" SELECT KTSV1_SUB.SVC_KEI_NO, MAX(KTSV1_SUB.KAISEN_UCWK_USE_ENDYMD) ") .append(" FROM KK_T_KAISEN_TG_SVKEI KTSV1_SUB ") .append(" WHERE KTSV1_SUB.SVC_KEI_NO = KTSV1.SVC_KEI_NO ") .append(" AND KTSV1_SUB.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KTSV1_SUB.MK_FLG = '0' ") .append(" GROUP BY KTSV1_SUB.SVC_KEI_NO ") .append(" ) ") .append(" ) KSUW ON (SVCK.SVC_KEI_NO = KSUW.SVC_KEI_NO) "); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT KKNS.SVC_KEI_NO, ") .append(" KKNS.SVC_KEI_UCWK_NO, ") .append(" TKSK1.TOKUSOKU_STAT ") .append(" FROM KK_T_KAKINS KKNS, ") .append(" (SELECT SEIKY_KEI_NO, ") .append(" TOKUSOKU_STAT ") .append(" FROM CH_T_TOKUSOKU TKSK1 ") .append(" WHERE ") .append(" TKSK1.TOKUSOKU_STAYMD <= ? ") .append(" AND TKSK1.TOKUSOKU_ENDYMD >= ? ") .append(" AND TKSK1.MK_FLG = '0' ") .append(" ) TKSK1 ") .append(" WHERE KKNS.SEIKY_KEI_NO = TKSK1.SEIKY_KEI_NO ") .append(" AND KKNS.KAKINS_TSTAYMD <= ? ") .append(" AND KKNS.KAKINS_TENDYMD >= ? ") .append(" AND KKNS.MK_FLG = '0' ") .append(" ) TKSK ON (TKSK.SVC_KEI_NO = SVCK.SVC_KEI_NO) "); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); // パラメータ追加(ＫＥＹ＿運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT MSAG.MSKM_NO, ") .append(" MSAG.DSP_JUN, ") .append(" MSAG.AGNT_CD, ") .append(" ROW_NUMBER() OVER(ORDER BY MSAG.DSP_JUN ASC) AS ROWNUMBER ") .append(" FROM KK_T_MSKM_AGNT MSAG, ") .append(" (SELECT SVAG1.SVC_KEI_NO, ") .append(" SVAG1.MSKM_DTL_NO ") .append(" FROM KK_T_SVC_KEI SVAG1 ") .append(" WHERE (SVAG1.SVC_KEI_NO,SVAG1.RSV_APLY_YMD || SVAG1.GENE_ADD_DTM) = (SELECT KK0081_GENE.SVC_KEI_NO, ") .append(" MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = SVAG1.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(" ) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND SVAG1.SVC_KEI_NO = ? "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" ) SVAG, ") .append(" (SELECT MSDL1.MSKM_DTL_NO, ") .append(" MSDL1.MSKM_NO ") .append(" FROM KK_T_MSKM_DTL MSDL1 ") .append(" WHERE (MSDL1.MSKM_DTL_NO,MSDL1.GENE_ADD_DTM) = (SELECT KK0021_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = MSDL1.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO ") .append(" ) ") .append(" ) MSDL ") .append(" WHERE SVAG.MSKM_DTL_NO = MSDL.MSKM_DTL_NO ") .append(" AND MSDL.MSKM_NO = MSAG.MSKM_NO ") .append(" AND MSAG.MK_FLG = '0' ") .append(" ) MSAG1 ON (MSAG1.MSKM_NO = MKDT.MSKM_NO ") .append(" AND MSAG1.ROWNUMBER = 1) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT AGNT11.AGNT_CD, ") .append(" AGNT11.AGNT_NM ") .append(" FROM KK_M_AGNT AGNT11 ") .append(" WHERE (AGNT11.AGNT_CD,AGNT11.RSV_APLY_YMD || AGNT11.GENE_ADD_DTM) = (SELECT KK0061_GENE.AGNT_CD, ") .append(" MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_AGNT KK0061_GENE ") .append(" WHERE KK0061_GENE.AGNT_CD = AGNT11.AGNT_CD ") .append(" AND KK0061_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0061_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0061_GENE.AGNT_CD ") .append(" ) ") .append(" ) AGNT1 ON (AGNT1.AGNT_CD = MSAG1.AGNT_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT MSAG.MSKM_NO, ") .append(" MSAG.DSP_JUN, ") .append(" MSAG.AGNT_CD, ") .append(" ROW_NUMBER() OVER(ORDER BY MSAG.DSP_JUN ASC) AS ROWNUMBER ") .append(" FROM KK_T_MSKM_AGNT MSAG, ") .append(" (SELECT SVAG1.SVC_KEI_NO, ") .append(" SVAG1.MSKM_DTL_NO ") .append(" FROM KK_T_SVC_KEI SVAG1 ") .append(" WHERE (SVAG1.SVC_KEI_NO,SVAG1.RSV_APLY_YMD || SVAG1.GENE_ADD_DTM) = (SELECT KK0081_GENE.SVC_KEI_NO, ") .append(" MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = SVAG1.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(" ) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND SVAG1.SVC_KEI_NO = ? "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" ) SVAG, ") .append(" (SELECT MSDL1.MSKM_DTL_NO, ") .append(" MSDL1.MSKM_NO ") .append(" FROM KK_T_MSKM_DTL MSDL1 ") .append(" WHERE (MSDL1.MSKM_DTL_NO,MSDL1.GENE_ADD_DTM) = (SELECT KK0021_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = MSDL1.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO ") .append(" ) ") .append(" ) MSDL ") .append(" WHERE SVAG.MSKM_DTL_NO = MSDL.MSKM_DTL_NO ") .append(" AND MSDL.MSKM_NO = MSAG.MSKM_NO ") .append(" AND MSAG.MK_FLG = '0' ") .append(" ) MSAG2 ON (MSAG2.MSKM_NO = MKDT.MSKM_NO ") .append(" AND MSAG2.ROWNUMBER = 2) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT AGNT21.AGNT_CD, ") .append(" AGNT21.AGNT_NM ") .append(" FROM KK_M_AGNT AGNT21 ") .append(" WHERE (AGNT21.AGNT_CD,AGNT21.RSV_APLY_YMD || AGNT21.GENE_ADD_DTM) = (SELECT KK0061_GENE.AGNT_CD, ") .append(" MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_AGNT KK0061_GENE ") .append(" WHERE KK0061_GENE.AGNT_CD = AGNT21.AGNT_CD ") .append(" AND KK0061_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0061_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0061_GENE.AGNT_CD ") .append(" ) ") .append(" ) AGNT2 ON (AGNT2.AGNT_CD = MSAG2.AGNT_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT MSAG.MSKM_NO, ") .append(" MSAG.DSP_JUN, ") .append(" MSAG.AGNT_CD, ") .append(" ROW_NUMBER() OVER(ORDER BY MSAG.DSP_JUN ASC) AS ROWNUMBER ") .append(" FROM KK_T_MSKM_AGNT MSAG, ") .append(" (SELECT SVAG1.SVC_KEI_NO, ") .append(" SVAG1.MSKM_DTL_NO ") .append(" FROM KK_T_SVC_KEI SVAG1 ") .append(" WHERE (SVAG1.SVC_KEI_NO,SVAG1.RSV_APLY_YMD || SVAG1.GENE_ADD_DTM) = (SELECT KK0081_GENE.SVC_KEI_NO, ") .append(" MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = SVAG1.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(" ) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND SVAG1.SVC_KEI_NO = ? "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" ) SVAG, ") .append(" (SELECT MSDL1.MSKM_DTL_NO, ") .append(" MSDL1.MSKM_NO ") .append(" FROM KK_T_MSKM_DTL MSDL1 ") .append(" WHERE (MSDL1.MSKM_DTL_NO,MSDL1.GENE_ADD_DTM) = (SELECT KK0021_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = MSDL1.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO ") .append(" ) ") .append(" ) MSDL ") .append(" WHERE SVAG.MSKM_DTL_NO = MSDL.MSKM_DTL_NO ") .append(" AND MSDL.MSKM_NO = MSAG.MSKM_NO ") .append(" AND MSAG.MK_FLG = '0' ") .append(" ) MSAG3 ON (MSAG3.MSKM_NO = MKDT.MSKM_NO ") .append(" AND MSAG3.ROWNUMBER = 3) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT AGNT31.AGNT_CD, ") .append(" AGNT31.AGNT_NM ") .append(" FROM KK_M_AGNT AGNT31 ") .append(" WHERE (AGNT31.AGNT_CD,AGNT31.RSV_APLY_YMD || AGNT31.GENE_ADD_DTM) = (SELECT KK0061_GENE.AGNT_CD, ") .append(" MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_AGNT KK0061_GENE ") .append(" WHERE KK0061_GENE.AGNT_CD = AGNT31.AGNT_CD ") .append(" AND KK0061_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0061_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0061_GENE.AGNT_CD ") .append(" ) ") .append(" ) AGNT3 ON (AGNT3.AGNT_CD = MSAG3.AGNT_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT MSAG.MSKM_NO, ") .append(" MSAG.DSP_JUN, ") .append(" MSAG.AGNT_CD, ") .append(" ROW_NUMBER() OVER(ORDER BY MSAG.DSP_JUN ASC) AS ROWNUMBER ") .append(" FROM KK_T_MSKM_AGNT MSAG, ") .append(" (SELECT SVAG1.SVC_KEI_NO, ") .append(" SVAG1.MSKM_DTL_NO ") .append(" FROM KK_T_SVC_KEI SVAG1 ") .append(" WHERE (SVAG1.SVC_KEI_NO,SVAG1.RSV_APLY_YMD || SVAG1.GENE_ADD_DTM) = (SELECT KK0081_GENE.SVC_KEI_NO, ") .append(" MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = SVAG1.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(" ) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND SVAG1.SVC_KEI_NO = ? "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" ) SVAG, ") .append(" (SELECT MSDL1.MSKM_DTL_NO, ") .append(" MSDL1.MSKM_NO ") .append(" FROM KK_T_MSKM_DTL MSDL1 ") .append(" WHERE (MSDL1.MSKM_DTL_NO,MSDL1.GENE_ADD_DTM) = (SELECT KK0021_GENE.MSKM_DTL_NO, ") .append(" MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = MSDL1.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO ") .append(" ) ") .append(" ) MSDL ") .append(" WHERE SVAG.MSKM_DTL_NO = MSDL.MSKM_DTL_NO ") .append(" AND MSDL.MSKM_NO = MSAG.MSKM_NO ") .append(" AND MK_FLG = '0' ") .append(" ) MSAG4 ON (MSAG4.MSKM_NO = MKDT.MSKM_NO ") .append(" AND MSAG4.ROWNUMBER = 4) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT AGNT41.AGNT_CD, ") .append(" AGNT41.AGNT_NM ") .append(" FROM KK_M_AGNT AGNT41 ") .append(" WHERE (AGNT41.AGNT_CD,AGNT41.RSV_APLY_YMD || AGNT41.GENE_ADD_DTM) = (SELECT KK0061_GENE.AGNT_CD, ") .append(" MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_AGNT KK0061_GENE ") .append(" WHERE KK0061_GENE.AGNT_CD = AGNT41.AGNT_CD ") .append(" AND KK0061_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0061_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0061_GENE.AGNT_CD ") .append(" ) ") .append(" ) AGNT4 ON (AGNT4.AGNT_CD = MSAG4.AGNT_CD) "); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); sql_Buff.append(" LEFT OUTER JOIN ( ") .append(" SELECT TMPP1.SVC_KEI_NO, ") .append(" MAX(DECODE(ROWNUMBER, 1, TMPP1.TMP_PAY_PRC_NM)) TMP_PAY_PRC_NM1, ") .append(" MAX(DECODE(ROWNUMBER, 2, TMPP1.TMP_PAY_PRC_NM)) TMP_PAY_PRC_NM2, ") .append(" MAX(DECODE(ROWNUMBER, 3, TMPP1.TMP_PAY_PRC_NM)) TMP_PAY_PRC_NM3, ") .append(" MAX(DECODE(ROWNUMBER, 4, TMPP1.TMP_PAY_PRC_NM)) TMP_PAY_PRC_NM4 ") .append(" FROM (SELECT IKST.TMP_PAY_PRC_NO, ") .append(" IKST.SVC_KEI_NO, ") .append(" TMPP.TMP_PAY_PRC_NM, ") .append(" ROW_NUMBER() OVER(ORDER BY IKST.UPD_DTM DESC) AS ROWNUMBER ") .append(" FROM KK_T_ICJKN_SETTE IKST, ") .append(" (SELECT KK0721.TMP_PAY_PRC_NO, ") .append(" KK0721.TMP_PAY_PRC_NM ") .append(" FROM KK_M_TMP_PAY_PRC KK0721 ") .append(" WHERE KK0721.MK_FLG = '0' ") .append(" ) TMPP ") .append(" WHERE IKST.TMP_PAY_PRC_NO = TMPP.TMP_PAY_PRC_NO ") .append(" AND IKST.TMP_PAY_PRC_NO IN( ") .append(JCRStrConst.TMP_PAY_PRC_NO_SYOKI_HIYO) .append(" )"); if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" AND IKST.SVC_KEI_NO = ? "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); } sql_Buff.append(" AND IKST.MK_FLG = '0' ") .append(" ) TMPP1 ") .append(" GROUP BY TMPP1.SVC_KEI_NO ") .append(" ) TMPP2 ON (TMPP2.SVC_KEI_NO = SVCK.SVC_KEI_NO) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT IKST1.TMP_PAY_PRC_NO, ") .append(" IKST1.SVC_KEI_NO, ") .append(" IKST1.ICJKN_SETTE_CD, ") .append(" IKST1.ICJKN_SHITEI_RT, ") .append(" IKST1.ICJKN_SHITEI_AMNT ") .append(" FROM KK_T_ICJKN_SETTE IKST1 ") .append(" WHERE IKST1.TMP_PAY_PRC_NO IN( ") .append(JCRStrConst.TMP_PAY_PRC_NO_JIM_FEE) .append(" ) ") .append(" AND IKST1.MK_FLG = '0' ") .append(" ) JMRT ON (JMRT.SVC_KEI_NO = SVCK.SVC_KEI_NO) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT KK0721.TMP_PAY_PRC_NO, ") .append(" KK0721.TMP_PAY_PRC_AMNT, ") .append(" KK0721.MANS_PRC_SKBT_CD, ") .append(" KK0721.MANS_ICJKN_TG_FLG ") .append(" FROM KK_M_TMP_PAY_PRC KK0721 ") .append(" WHERE KK0721.MK_FLG = '0' ") .append(" ) JMTM ON (JMRT.TMP_PAY_PRC_NO = JMTM.TMP_PAY_PRC_NO) ") .append(" LEFT OUTER JOIN ( ") .append(" SELECT TKHP.TK_HOSHIKI_KEI_NO, ") .append(" TKHP.MANS_PRC_SKBT_CD, ") /* ++++++++++ v22.00.00 変更開始 ++++++++++ */ // .append(" TKHP.KANYU_KEI_SEIKY_AMNT ") .append(" TKHP.KANYU_KEI_SEIKY_AMNT, ") .append(" TKHP.SEIKY_AMNT_TSTAYMD, ") .append(" TKHP.SEIKY_AMNT_TENDYMD ") /* ++++++++++ v22.00.00 変更終了 ++++++++++ */ .append(" FROM KK_T_TK_HSK_KEI_PRC TKHP ") .append(" WHERE TKHP.MK_FLG = '0' ") .append(" ) TKHP1 ON (TKHP1.TK_HOSHIKI_KEI_NO = SVCK.TK_HOSHIKI_KEI_NO ") /* ++++++++++ v22.00.00 変更開始 ++++++++++ */ // .append(" AND TKHP1.MANS_PRC_SKBT_CD = JMTM.MANS_PRC_SKBT_CD) "); .append(" AND TKHP1.MANS_PRC_SKBT_CD = JMTM.MANS_PRC_SKBT_CD) ") .append(" AND TKHP1.SEIKY_AMNT_TSTAYMD <= MKDT.MSKM_YMD ") .append(" AND TKHP1.SEIKY_AMNT_TENDYMD >= MKDT.MSKM_YMD "); /* ++++++++++ v22.00.00 変更終了 ++++++++++ */ // SQL文_個別部1 if ( JPCModelConstant.FUNC_CD_1.equals( inMsg.getObject(EKK0081B512CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE SVCK.SVC_KEI_NO = ? ") .append(" AND (SVCK.SVC_KEI_NO,SVCK.RSV_APLY_YMD || SVCK.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, ") .append(" MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = SVCK.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(" ) ") .append(" AND SVCK.PRC_GRP_CD <> '99' "); // パラメータ追加(ＫＥＹ＿サービス契約番号を指定) replaceList.add(svcKeiNo); // パラメータ追加(運用日付を指定) replaceList.add(opeDate); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); //-------------------------------------------------------------------------------- 修正 Start --------// // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0 ; idx < replaceList.size() ; idx++) { CAANJDBCUtil.setParam(pstmt, idx + 1, replaceList.get(idx)); } //-------------------------------------------------------------------------------- 修正 End ----------// // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0081B512CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0081B512CBSMsg1List.PRC_GRP_CD , EKK0081B512CBSMsg1List.PRC_GRP_CD_NM , EKK0081B512CBSMsg1List.PCRS_CD , EKK0081B512CBSMsg1List.PCRS_CD_NM , EKK0081B512CBSMsg1List.MSKM_FORM_CD , EKK0081B512CBSMsg1List.MSKM_FORM_CD_NM , EKK0081B512CBSMsg1List.SVC_KEI_STAT , EKK0081B512CBSMsg1List.SVC_KEI_STAT_NM , EKK0081B512CBSMsg1List.IDO_NG_STAT_CD , EKK0081B512CBSMsg1List.IDO_NG_STAT_NM , EKK0081B512CBSMsg1List.TOKUSOKU_STAT , EKK0081B512CBSMsg1List.TOKUSOKU_STAT_NM , EKK0081B512CBSMsg1List.SVC_KEI_ADD_DTM , EKK0081B512CBSMsg1List.MSKM_UK_DTM , EKK0081B512CBSMsg1List.MSKM_YMD , EKK0081B512CBSMsg1List.MSKMSHO_NO , EKK0081B512CBSMsg1List.AGNT_CD1 , EKK0081B512CBSMsg1List.AGNT_CD2 , EKK0081B512CBSMsg1List.AGNT_CD3 , EKK0081B512CBSMsg1List.AGNT_CD4 , EKK0081B512CBSMsg1List.AGNT_NM1 , EKK0081B512CBSMsg1List.AGNT_NM2 , EKK0081B512CBSMsg1List.AGNT_NM3 , EKK0081B512CBSMsg1List.AGNT_NM4 , EKK0081B512CBSMsg1List.KAISEN_PLACE_PCD , EKK0081B512CBSMsg1List.KAISEN_PLACE_STATE_NM , EKK0081B512CBSMsg1List.KAISEN_PLACE_CITY_NM , EKK0081B512CBSMsg1List.KAISEN_PLACE_OAZTSU_NM , EKK0081B512CBSMsg1List.KAISEN_PLACE_AZCHO_NM , EKK0081B512CBSMsg1List.KAISEN_PLACE_BNCHIGO , EKK0081B512CBSMsg1List.KAISEN_PLACE_ADRTTM , EKK0081B512CBSMsg1List.KAISEN_PLACE_ADRRM , EKK0081B512CBSMsg1List.SVC_STATYMD , EKK0081B512CBSMsg1List.SVC_ENDYMD , EKK0081B512CBSMsg1List.SVC_CHRG_STAYMD , EKK0081B512CBSMsg1List.SVC_CHRG_ENDYMD , EKK0081B512CBSMsg1List.OP_SVC_HKTGI_UM , EKK0081B512CBSMsg1List.OP_SVC_HKTGI_UM_NM , EKK0081B512CBSMsg1List.MSKM_UK_TNT_USER_ID , EKK0081B512CBSMsg1List.MSKM_UK_TNT_USER_ID_NM , EKK0081B512CBSMsg1List.SYOKI_HIYOU_NM1 , EKK0081B512CBSMsg1List.SYOKI_HIYOU_NM2 , EKK0081B512CBSMsg1List.SYOKI_HIYOU_NM3 , EKK0081B512CBSMsg1List.SYOKI_HIYOU_NM4 , EKK0081B512CBSMsg1List.ICJKN_SETTE_CD , EKK0081B512CBSMsg1List.ICJKN_SHITEI_RT , EKK0081B512CBSMsg1List.ICJKN_SHITEI_AMNT , EKK0081B512CBSMsg1List.TMP_PAY_PRC_AMNT , EKK0081B512CBSMsg1List.DSP_SVC_KEI_STAT_NM , EKK0081B512CBSMsg1List.SVC_DSL_KISAN_YMD }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B512CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0081B512CBSMsg1List", outMsg1); // 総検索件数をセット inMsg.set(EKK0081B512CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(outMsg1.length)); } catch(SQLException e) { inMsg.set(EKK0081B512CBSMsg.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(EKK0081B512CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }