/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0081B037TPDA * ソースファイル名：JSYejbEKK0081B037TPDA.java * 作成者：EK911025 * 日付：2014年04月04日 *＜機能概要＞ * サービス契約一覧照会（割引用）テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ9.00.00 2014/04/28 FJ）寺園 OM-2014-0000771 * ｖ25.00.00 2016/06/13 FJ）岡田 OM-2016-0001324 ********************************************************************************/ package eo.ejb.cbs.sqlf; 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 eo.ejb.cbs.cbsmsg.EKK0081B037CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0081B037CBSMsg1List; import eo.ejb.cbm.entity.KK0081ETMsg; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK0081B037_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0081B037TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0081B037TPDA() { } /** * EKK0081B037_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // パラメータのカウント int iPCnt = 1; /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK0081ETMsg.getTableName()); // ＫＥＹ＿サービス契約番号の設定 String[] wkSvcKeiNo = inMsg.getString(EKK0081B037CBSMsg.KEY_SVC_KEI_NO).split(","); StringBuffer sbSvcKeiNo = new StringBuffer(); StringBuffer svcKeiNo = new StringBuffer(); for (int i = 0; i < wkSvcKeiNo.length; i++) { // 入力値をカンマで分割した数分OR条件にて設定した内容を // AND条件にてWHERE句に設定する sbSvcKeiNo.append(" , ? "); } // 最初のカンマを取り除いて設定 svcKeiNo.append(sbSvcKeiNo.toString().substring(2)); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT * ") .append(" FROM ( ") .append(" SELECT ") .append(" '03' AS TG_KEI_SKBT_CD ") .append(" , KK0161.SVC_KEI_NO ") .append(" , KK0161.SVC_KEI_UCWK_NO ") .append(" , NULL AS OP_SVC_KEI_NO ") .append(" , NULL AS SBOP_SVC_KEI_NO ") .append(" , NULL AS KKTK_SVC_KEI_NO ") .append(" , NULL AS SEIOPSVC_KEI_NO ") .append(" , NULL AS SEIKY_KEI_NO ") .append(" , KK0161.GENE_ADD_DTM ") .append(" , KK0161.SVC_KEI_UCWK_STAT ") .append(" , NULL AS OP_SVC_KEI_STAT ") .append(" , NULL AS SBOP_SVC_KEI_STAT ") .append(" , NULL AS KKTK_SVC_KEI_STAT ") .append(" , NULL AS SEIOPSVC_KEI_STAT ") .append(" , KK0161.MSKM_DTL_NO ") .append(" , NULL AS OP_SVC_CD ") .append(" , NULL AS SBOP_SVC_CD ") .append(" , NULL AS KKTK_SVC_CD ") .append(" , NULL AS SEIOPSVC_CD ") .append(" , KK0161.PCRS_CD ") .append(" , KK0161.PPLAN_CD ") .append(" , KK0161.TK_HOSHIKI_KEI_NO ") .append(" , KK0161.PAYWAY_KEIZOKU_FLG ") .append(" , KK0161.WEB_OP_ADD_FAIL_FLG ") .append(" , KK0161.WORK_RRK_BIKO ") .append(" , KK0161.SVC_USE_STA_KIBO_YMD ") .append(" , KK0161.SVC_UEST_KBTMZ_CD ") .append(" , KK0161.SVC_UEST_KBTM_CD ") .append(" , KK0161.RSV_TSTA_KIBO_YMD ") .append(" , KK0161.SKEKKA_SEND_CD ") .append(" , KK0161.SVC_KEI_UCWK_KZKWRK_REQYMD ") .append(" , KK0161.SHOSA_YMD ") .append(" , KK0161.SHOSA_CL_YMD ") .append(" , KK0161.FTRIAL_KANYU_YMD ") .append(" , KK0161.FTRIAL_PRD_ENDYMD ") .append(" , KK0161.HONKANYU_YMD ") .append(" , KK0161.HONKANYU_IKO_KIGEN_YMD ") .append(" , KK0161.KEI_CNC_YMD ") .append(" , KK0161.RSV_APLY_YMD ") .append(" , KK0161.RSV_CL_YMD ") .append(" , KK0161.RSV_APLY_CD ") .append(" , KK0161.PLAN_STAYMD ") .append(" , KK0161.PLAN_ENDYMD ") .append(" , KK0161.PLAN_CHRG_STAYMD ") .append(" , KK0161.PLAN_CHRG_ENDYMD ") .append(" , KK0161.PLAN_END_SBT_CD ") .append(" , KK0161.SVC_CANCEL_YMD ") .append(" , KK0161.SVC_CANCEL_RSN_CD ") .append(" , KK0161.SVC_STA_YMD ") .append(" , KK0161.SVC_CHRG_STAYMD ") .append(" , KK0161.SVC_STP_YMD ") .append(" , KK0161.SVC_STP_RSN_CD ") .append(" , KK0161.SVC_STP_RLS_YMD ") .append(" , KK0161.SVC_STP_RLS_RSN_CD ") .append(" , KK0161.PAUSE_STP_CD ") .append(" , KK0161.SVC_PAUSE_YMD ") .append(" , KK0161.SVC_PAUSE_RSN_CD ") .append(" , KK0161.SVC_PAUSE_RSN_MEMO ") .append(" , KK0161.SVC_PAUSE_RLS_YMD ") .append(" , KK0161.SVC_PAUSE_RLS_RSN_CD ") .append(" , KK0161.SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0161.SVC_ENDYMD ") .append(" , KK0161.SVC_CHRG_ENDYMD ") .append(" , KK0161.SVC_DSL_KISAN_YMD ") .append(" , KK0161.SVC_DSL_YMD ") .append(" , KK0161.SVC_DLRE_CD ") .append(" , KK0161.SVC_DLRE_MEMO ") .append(" , KK0161.SVC_DSL_TTDKI_FIN_FLG ") .append(" , KK0161.KAIHK_YMD ") .append(" , KK0161.SVC_CANCEL_CL_YMD ") .append(" , KK0161.SVC_DSL_CL_YMD ") .append(" , KK0161.SVKEIUW_HKHASYMD ") .append(" , KK0161.CHRG_STA_YMD_HOSEI_UM ") .append(" , KK0161.SVC_PAUSE_CHRG_STA_YMD ") .append(" , KK0161.PNLTY_HASSEI_CD ") .append(" , KK0161.IDO_DIV ") .append(" , KK0161.SHOSA_DSL_FIN_CD ") .append(" , KK0161.KEIZK_MT_SVC_KEI_UCWK_NO ") .append(" , KK0161.KEIZK_AF_KEI_CHGECHU_FLG ") .append(" , NULL AS SBOP_SVC_KEI_KZKWRK_REQYMD ") .append(" , NULL AS SVC_PAUSE_PRC_TSTA_YM ") .append(" , NULL AS KIKI_SEIZO_NO ") .append(" , NULL AS TAKNKIKI_MODEL_CD ") .append(" , NULL AS KIKI_STC_SAKI_STATE_NM ") .append(" , NULL AS KIKI_STC_SAKI_CITY_NM ") .append(" , NULL AS KIKI_STC_SAKI_OAZTSU_NM ") .append(" , NULL AS KIKI_STC_SAKI_AZCHO_NM ") .append(" , NULL AS KIKI_STC_SAKI_BNCHIGO ") .append(" , NULL AS KIKI_STC_SAKI_ADRTTM ") .append(" , NULL AS KIKI_STC_SAKI_ADRRM ") .append(" , NULL AS KIKI_CHG_RSN_CD ") .append(" , NULL AS TAKNKIKI_SETHIN_MODEL_CD ") .append(" , NULL AS KKTK_SBT_CD ") .append(" , NULL AS KIKI_CHG_NO ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161 ") .append(" WHERE ") .append(" KK0161.SVC_KEI_NO IN ( ") .append(svcKeiNo) .append(" ) ") .append(" AND KK0161.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.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.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(" UNION ") .append(" SELECT ") .append(" '04' AS TG_KEI_SKBT_CD ") .append(" , KK0351.SVC_KEI_NO ") .append(" , NULL AS SVC_KEI_UCWK_NO ") .append(" , KK0351.OP_SVC_KEI_NO ") .append(" , NULL AS SBOP_SVC_KEI_NO ") .append(" , NULL AS KKTK_SVC_KEI_NO ") .append(" , NULL AS SEIOPSVC_KEI_NO ") .append(" , NULL AS SEIKY_KEI_NO ") .append(" , NULL AS GENE_ADD_DTM ") .append(" , NULL AS SVC_KEI_UCWK_STAT ") .append(" , KK0351.OP_SVC_KEI_STAT ") .append(" , NULL AS SBOP_SVC_KEI_STAT ") .append(" , NULL AS KKTK_SVC_KEI_STAT ") .append(" , NULL AS SEIOPSVC_KEI_STAT ") .append(" , KK0351.MSKM_DTL_NO ") .append(" , KK0351.OP_SVC_CD ") .append(" , NULL AS SBOP_SVC_CD ") .append(" , NULL AS KKTK_SVC_CD ") .append(" , NULL AS SEIOPSVC_CD ") .append(" , KK0351.PCRS_CD ") .append(" , KK0351.PPLAN_CD ") .append(" , NULL AS TK_HOSHIKI_KEI_NO ") .append(" , NULL AS PAYWAY_KEIZOKU_FLG ") .append(" , NULL AS WEB_OP_ADD_FAIL_FLG ") .append(" , NULL AS WORK_RRK_BIKO ") .append(" , NULL AS SVC_USE_STA_KIBO_YMD ") .append(" , NULL AS SVC_UEST_KBTMZ_CD ") .append(" , NULL AS SVC_UEST_KBTM_CD ") .append(" , NULL AS RSV_TSTA_KIBO_YMD ") .append(" , NULL AS SKEKKA_SEND_CD ") .append(" , NULL AS SVC_KEI_UCWK_KZKWRK_REQYMD ") .append(" , NULL AS SHOSA_YMD ") .append(" , NULL AS SHOSA_CL_YMD ") .append(" , NULL AS FTRIAL_KANYU_YMD ") .append(" , NULL AS FTRIAL_PRD_ENDYMD ") .append(" , NULL AS HONKANYU_YMD ") .append(" , NULL AS HONKANYU_IKO_KIGEN_YMD ") .append(" , NULL AS KEI_CNC_YMD ") .append(" , NULL AS RSV_APLY_YMD ") .append(" , NULL AS RSV_CL_YMD ") .append(" , NULL AS RSV_APLY_CD ") .append(" , NULL AS PLAN_STAYMD ") .append(" , NULL AS PLAN_ENDYMD ") .append(" , NULL AS PLAN_CHRG_STAYMD ") .append(" , NULL AS PLAN_CHRG_ENDYMD ") .append(" , NULL AS PLAN_END_SBT_CD ") .append(" , NULL AS SVC_CANCEL_YMD ") .append(" , NULL AS SVC_CANCEL_RSN_CD ") .append(" , KK0351.SVC_STAYMD AS SVC_STA_YMD ") .append(" , KK0351.SVC_CHRG_STAYMD ") .append(" , NULL AS SVC_STP_YMD ") .append(" , NULL AS SVC_STP_RSN_CD ") .append(" , NULL AS SVC_STP_RLS_YMD ") .append(" , NULL AS SVC_STP_RLS_RSN_CD ") .append(" , NULL AS PAUSE_STP_CD ") .append(" , NULL AS SVC_PAUSE_YMD ") .append(" , NULL AS SVC_PAUSE_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RSN_MEMO ") .append(" , NULL AS SVC_PAUSE_RLS_YMD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0351.SVC_ENDYMD ") .append(" , KK0351.SVC_CHRG_ENDYMD ") .append(" , NULL AS SVC_DSL_KISAN_YMD ") .append(" , NULL AS SVC_DSL_YMD ") .append(" , NULL AS SVC_DLRE_CD ") .append(" , NULL AS SVC_DLRE_MEMO ") .append(" , NULL AS SVC_DSL_TTDKI_FIN_FLG ") .append(" , NULL AS KAIHK_YMD ") .append(" , NULL AS SVC_CANCEL_CL_YMD ") .append(" , NULL AS SVC_DSL_CL_YMD ") .append(" , NULL AS SVKEIUW_HKHASYMD ") .append(" , NULL AS CHRG_STA_YMD_HOSEI_UM ") .append(" , NULL AS SVC_PAUSE_CHRG_STA_YMD ") .append(" , NULL AS PNLTY_HASSEI_CD ") .append(" , NULL AS IDO_DIV ") .append(" , NULL AS SHOSA_DSL_FIN_CD ") .append(" , NULL AS KEIZK_MT_SVC_KEI_UCWK_NO ") .append(" , NULL AS KEIZK_AF_KEI_CHGECHU_FLG ") .append(" , NULL AS SBOP_SVC_KEI_KZKWRK_REQYMD ") .append(" , NULL AS SVC_PAUSE_PRC_TSTA_YM ") .append(" , NULL AS KIKI_SEIZO_NO ") .append(" , NULL AS TAKNKIKI_MODEL_CD ") .append(" , NULL AS KIKI_STC_SAKI_STATE_NM ") .append(" , NULL AS KIKI_STC_SAKI_CITY_NM ") .append(" , NULL AS KIKI_STC_SAKI_OAZTSU_NM ") .append(" , NULL AS KIKI_STC_SAKI_AZCHO_NM ") .append(" , NULL AS KIKI_STC_SAKI_BNCHIGO ") .append(" , NULL AS KIKI_STC_SAKI_ADRTTM ") .append(" , NULL AS KIKI_STC_SAKI_ADRRM ") .append(" , NULL AS KIKI_CHG_RSN_CD ") .append(" , NULL AS TAKNKIKI_SETHIN_MODEL_CD ") .append(" , NULL AS KKTK_SBT_CD ") .append(" , NULL AS KIKI_CHG_NO ") .append(" FROM ") .append(" (SELECT KK0351_01.* ") .append(" ,KK0351_01.SVC_KEI_NO AS KEY_SVC_KEI_NO ") .append(" FROM KK_T_OP_SVC_KEI KK0351_01 ") .append(" WHERE KK0351_01.OYA_KEI_SKBT_CD = '01' ") .append(" AND (KK0351_01.OP_SVC_KEI_NO, KK0351_01.RSV_APLY_YMD || KK0351_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_01_GENE.OP_SVC_KEI_NO, MAX(KK0351_01_GENE.RSV_APLY_YMD || KK0351_01_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_OP_SVC_KEI KK0351_01_GENE ") .append(" WHERE KK0351_01_GENE.OP_SVC_KEI_NO = KK0351_01.OP_SVC_KEI_NO ") .append(" AND KK0351_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0351_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0351_01_GENE.OP_SVC_KEI_NO) ") .append(" UNION ") .append(" SELECT KK0351_02.* ") .append(" ,KK0161.SVC_KEI_NO AS KEY_SVC_KEI_NO ") .append(" FROM KK_T_OP_SVC_KEI KK0351_02 ") .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ") .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0351_02.SVC_KEI_UCWK_NO ") .append(" AND KK0351_02.OYA_KEI_SKBT_CD = '03' ") .append(" WHERE (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ") .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.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(" AND (KK0351_02.OP_SVC_KEI_NO, KK0351_02.RSV_APLY_YMD || KK0351_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_02_GENE.OP_SVC_KEI_NO, MAX(KK0351_02_GENE.RSV_APLY_YMD || KK0351_02_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_OP_SVC_KEI KK0351_02_GENE ") .append(" WHERE KK0351_02_GENE.OP_SVC_KEI_NO = KK0351_02.OP_SVC_KEI_NO ") .append(" AND KK0351_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0351_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0351_02_GENE.OP_SVC_KEI_NO) ") .append(" ) KK0351 ") .append(" WHERE KK0351.KEY_SVC_KEI_NO IN ( ") .append(svcKeiNo) .append(" ) ") .append(" AND (KK0351.SVC_DLRE_CD <> '04' OR KK0351.SVC_DLRE_CD IS NULL) ") .append(" UNION ") .append(" SELECT ") .append(" '05' AS TG_KEI_SKBT_CD ") .append(" , KK0401.KEY_SVC_KEI_NO ") .append(" , NULL AS SVC_KEI_UCWK_NO ") .append(" , KK0401.OP_SVC_KEI_NO ") .append(" , KK0401.SBOP_SVC_KEI_NO ") .append(" , NULL AS KKTK_SVC_KEI_NO ") .append(" , NULL AS SEIOPSVC_KEI_NO ") .append(" , NULL AS SEIKY_KEI_NO ") .append(" , KK0401.GENE_ADD_DTM ") .append(" , NULL AS SVC_KEI_UCWK_STAT ") .append(" , NULL AS OP_SVC_KEI_STAT ") .append(" , KK0401.SBOP_SVC_KEI_STAT ") .append(" , NULL AS KKTK_SVC_KEI_STAT ") .append(" , NULL AS SEIOPSVC_KEI_STAT ") .append(" , KK0401.MSKM_DTL_NO ") .append(" , NULL AS OP_SVC_CD ") .append(" , KK0401.SBOP_SVC_CD ") .append(" , NULL AS KKTK_SVC_CD ") .append(" , NULL AS SEIOPSVC_CD ") .append(" , KK0401.PCRS_CD ") .append(" , KK0401.PPLAN_CD ") .append(" , NULL AS TK_HOSHIKI_KEI_NO ") .append(" , NULL AS PAYWAY_KEIZOKU_FLG ") .append(" , NULL AS WEB_OP_ADD_FAIL_FLG ") .append(" , NULL AS WORK_RRK_BIKO ") .append(" , NULL AS SVC_USE_STA_KIBO_YMD ") .append(" , NULL AS SVC_UEST_KBTMZ_CD ") .append(" , NULL AS SVC_UEST_KBTM_CD ") .append(" , KK0401.RSV_TSTA_KIBO_YMD ") .append(" , KK0401.SKEKKA_SEND_CD ") .append(" , NULL AS SVC_KEI_UCWK_KZKWRK_REQYMD ") .append(" , KK0401.SHOSA_YMD ") .append(" , KK0401.SHOSA_CL_YMD ") .append(" , KK0401.FTRIAL_KANYU_YMD ") .append(" , KK0401.FTRIAL_PRD_ENDYMD ") .append(" , KK0401.HONKANYU_YMD ") .append(" , KK0401.HONKANYU_IKO_KIGEN_YMD ") .append(" , KK0401.KEI_CNC_YMD ") .append(" , KK0401.RSV_APLY_YMD ") .append(" , KK0401.RSV_CL_YMD ") .append(" , KK0401.RSV_APLY_CD ") .append(" , KK0401.PLAN_STAYMD ") .append(" , KK0401.PLAN_ENDYMD ") .append(" , KK0401.PLAN_CHRG_STAYMD ") .append(" , KK0401.PLAN_CHRG_ENDYMD ") .append(" , KK0401.PLAN_END_SBT_CD ") .append(" , KK0401.SVC_CANCEL_YMD ") .append(" , KK0401.SVC_CANCEL_RSN_CD ") .append(" , KK0401.SVC_STAYMD AS SVC_STA_YMD ") .append(" , KK0401.SVC_CHRG_STAYMD ") .append(" , KK0401.SVC_STP_YMD ") .append(" , KK0401.SVC_STP_RSN_CD ") .append(" , KK0401.SVC_STP_RLS_YMD ") .append(" , KK0401.SVC_STP_RLS_RSN_CD ") .append(" , KK0401.PAUSE_STP_CD ") .append(" , KK0401.SVC_PAUSE_YMD ") .append(" , KK0401.SVC_PAUSE_RSN_CD ") .append(" , KK0401.SVC_PAUSE_RSN_MEMO ") .append(" , KK0401.SVC_PAUSE_RLS_YMD ") .append(" , KK0401.SVC_PAUSE_RLS_RSN_CD ") .append(" , KK0401.SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0401.SVC_ENDYMD ") .append(" , KK0401.SVC_CHRG_ENDYMD ") .append(" , NULL AS SVC_DSL_KISAN_YMD ") .append(" , KK0401.SVC_DSL_YMD ") .append(" , KK0401.SVC_DLRE_CD ") .append(" , KK0401.SVC_DLRE_MEMO ") .append(" , KK0401.SVC_DSL_TTDKI_FIN_FLG ") .append(" , KK0401.KAIHK_YMD ") .append(" , KK0401.SVC_CANCEL_CL_YMD ") .append(" , KK0401.SVC_DSL_CL_YMD ") .append(" , NULL AS SVKEIUW_HKHASYMD ") .append(" , KK0401.CHRG_STA_YMD_HOSEI_UM ") .append(" , KK0401.SVC_PAUSE_CHRG_STA_YMD ") .append(" , KK0401.PNLTY_HASSEI_CD ") .append(" , KK0401.IDO_DIV ") .append(" , KK0401.SHOSA_DSL_FIN_CD ") .append(" , NULL AS KEIZK_MT_SVC_KEI_UCWK_NO ") .append(" , NULL AS KEIZK_AF_KEI_CHGECHU_FLG ") .append(" , KK0401.SBOP_SVC_KEI_KZKWRK_REQYMD ") .append(" , NULL AS SVC_PAUSE_PRC_TSTA_YM ") .append(" , NULL AS KIKI_SEIZO_NO ") .append(" , NULL AS TAKNKIKI_MODEL_CD ") .append(" , NULL AS KIKI_STC_SAKI_STATE_NM ") .append(" , NULL AS KIKI_STC_SAKI_CITY_NM ") .append(" , NULL AS KIKI_STC_SAKI_OAZTSU_NM ") .append(" , NULL AS KIKI_STC_SAKI_AZCHO_NM ") .append(" , NULL AS KIKI_STC_SAKI_BNCHIGO ") .append(" , NULL AS KIKI_STC_SAKI_ADRTTM ") .append(" , NULL AS KIKI_STC_SAKI_ADRRM ") .append(" , NULL AS KIKI_CHG_RSN_CD ") .append(" , NULL AS TAKNKIKI_SETHIN_MODEL_CD ") .append(" , NULL AS KKTK_SBT_CD ") .append(" , NULL AS KIKI_CHG_NO ") .append(" FROM ") .append(" (SELECT KK0401_01.* ") .append(" ,KK0351_01.SVC_KEI_NO AS KEY_SVC_KEI_NO ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_01 ") .append(" INNER JOIN KK_T_OP_SVC_KEI KK0351_01 ") .append(" ON KK0351_01.OP_SVC_KEI_NO = KK0401_01.OP_SVC_KEI_NO ") .append(" WHERE KK0351_01.OYA_KEI_SKBT_CD = '01' ") .append(" AND (KK0351_01.OP_SVC_KEI_NO, KK0351_01.RSV_APLY_YMD || KK0351_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_01_GENE.OP_SVC_KEI_NO, MAX(KK0351_01_GENE.RSV_APLY_YMD || KK0351_01_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_OP_SVC_KEI KK0351_01_GENE ") .append(" WHERE KK0351_01_GENE.OP_SVC_KEI_NO = KK0351_01.OP_SVC_KEI_NO ") .append(" AND KK0351_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0351_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0351_01_GENE.OP_SVC_KEI_NO) ") .append(" AND (KK0401_01.OP_SVC_KEI_NO, KK0401_01.SBOP_SVC_KEI_NO, KK0401_01.RSV_APLY_YMD || KK0401_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0401_01_GENE.OP_SVC_KEI_NO, KK0401_01_GENE.SBOP_SVC_KEI_NO, MAX(KK0401_01_GENE.RSV_APLY_YMD || KK0401_01_GENE.GENE_ADD_DTM) AS KK0401_MAX ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_01_GENE ") .append(" WHERE KK0401_01_GENE.OP_SVC_KEI_NO = KK0401_01.OP_SVC_KEI_NO ") .append(" AND KK0401_01_GENE.SBOP_SVC_KEI_NO = KK0401_01.SBOP_SVC_KEI_NO ") .append(" AND KK0401_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0401_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0401_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0401_01_GENE.OP_SVC_KEI_NO, KK0401_01_GENE.SBOP_SVC_KEI_NO ) ") .append(" UNION ") .append(" SELECT KK0401_02.* ") .append(" ,KK0161.SVC_KEI_NO AS KEY_SVC_KEI_NO ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_02 ") .append(" INNER JOIN KK_T_OP_SVC_KEI KK0351_02 ") .append(" ON KK0351_02.OP_SVC_KEI_NO = KK0401_02.OP_SVC_KEI_NO ") .append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ") .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0351_02.SVC_KEI_UCWK_NO ") .append(" AND KK0351_02.OYA_KEI_SKBT_CD = '03' ") .append(" WHERE (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ") .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.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(" AND (KK0351_02.OP_SVC_KEI_NO, KK0351_02.RSV_APLY_YMD || KK0351_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_02_GENE.OP_SVC_KEI_NO, MAX(KK0351_02_GENE.RSV_APLY_YMD || KK0351_02_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_OP_SVC_KEI KK0351_02_GENE ") .append(" WHERE KK0351_02_GENE.OP_SVC_KEI_NO = KK0351_02.OP_SVC_KEI_NO ") .append(" AND KK0351_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0351_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0351_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0351_02_GENE.OP_SVC_KEI_NO) ") .append(" AND (KK0401_02.OP_SVC_KEI_NO, KK0401_02.SBOP_SVC_KEI_NO, KK0401_02.RSV_APLY_YMD || KK0401_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0401_02_GENE.OP_SVC_KEI_NO, KK0401_02_GENE.SBOP_SVC_KEI_NO, MAX(KK0401_02_GENE.RSV_APLY_YMD || KK0401_02_GENE.GENE_ADD_DTM) AS KK0401_MAX ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_02_GENE ") .append(" WHERE KK0401_02_GENE.OP_SVC_KEI_NO = KK0401_02.OP_SVC_KEI_NO ") .append(" AND KK0401_02_GENE.SBOP_SVC_KEI_NO = KK0401_02.SBOP_SVC_KEI_NO ") .append(" AND KK0401_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0401_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0401_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0401_02_GENE.OP_SVC_KEI_NO, KK0401_02_GENE.SBOP_SVC_KEI_NO ) ") .append(" ) KK0401 ") .append(" WHERE KK0401.KEY_SVC_KEI_NO IN ( ") .append(svcKeiNo) .append(" ) ") .append(" UNION ") .append(" SELECT ") .append(" '06' AS TG_KEI_SKBT_CD ") .append(" , KK0341.SVC_KEI_NO ") .append(" , KK0341.SVC_KEI_UCWK_NO ") .append(" , NULL AS OP_SVC_KEI_NO ") .append(" , NULL AS SBOP_SVC_KEI_NO ") .append(" , KK0341.KKTK_SVC_KEI_NO ") .append(" , NULL AS SEIOPSVC_KEI_NO ") .append(" , NULL AS SEIKY_KEI_NO ") .append(" , KK0341.GENE_ADD_DTM ") .append(" , NULL AS SVC_KEI_UCWK_STAT ") .append(" , NULL AS OP_SVC_KEI_STAT ") .append(" , NULL AS SBOP_SVC_KEI_STAT ") .append(" , KK0341.KKTK_SVC_KEI_STAT ") .append(" , NULL AS SEIOPSVC_KEI_STAT ") .append(" , KK0341.MSKM_DTL_NO ") .append(" , NULL AS OP_SVC_CD ") .append(" , NULL AS SBOP_SVC_CD ") .append(" , KK0341.KKTK_SVC_CD ") .append(" , NULL AS SEIOPSVC_CD ") .append(" , KK0341.PCRS_CD ") .append(" , KK0341.PPLAN_CD ") .append(" , NULL AS TK_HOSHIKI_KEI_NO ") .append(" , NULL AS PAYWAY_KEIZOKU_FLG ") .append(" , NULL AS WEB_OP_ADD_FAIL_FLG ") .append(" , NULL AS WORK_RRK_BIKO ") .append(" , NULL AS SVC_USE_STA_KIBO_YMD ") .append(" , NULL AS SVC_UEST_KBTMZ_CD ") .append(" , NULL AS SVC_UEST_KBTM_CD ") .append(" , NULL AS RSV_TSTA_KIBO_YMD ") .append(" , NULL AS SKEKKA_SEND_CD ") .append(" , NULL AS SVC_KEI_UCWK_KZKWRK_REQYMD ") .append(" , NULL AS SHOSA_YMD ") .append(" , NULL AS SHOSA_CL_YMD ") .append(" , NULL AS FTRIAL_KANYU_YMD ") .append(" , NULL AS FTRIAL_PRD_ENDYMD ") .append(" , NULL AS HONKANYU_YMD ") .append(" , NULL AS HONKANYU_IKO_KIGEN_YMD ") .append(" , NULL AS KEI_CNC_YMD ") .append(" , KK0341.RSV_APLY_YMD ") .append(" , KK0341.RSV_CL_YMD ") .append(" , KK0341.RSV_APLY_CD ") .append(" , NULL AS PLAN_STAYMD ") .append(" , NULL AS PLAN_ENDYMD ") .append(" , NULL AS PLAN_CHRG_STAYMD ") .append(" , NULL AS PLAN_CHRG_ENDYMD ") .append(" , NULL AS PLAN_END_SBT_CD ") .append(" , NULL AS SVC_CANCEL_YMD ") .append(" , NULL AS SVC_CANCEL_RSN_CD ") .append(" , KK0341.SVC_STA_YMD ") .append(" , KK0341.SVC_CHRG_STAYMD ") .append(" , NULL AS SVC_STP_YMD ") .append(" , NULL AS SVC_STP_RSN_CD ") .append(" , NULL AS SVC_STP_RLS_YMD ") .append(" , NULL AS SVC_STP_RLS_RSN_CD ") .append(" , NULL AS PAUSE_STP_CD ") .append(" , NULL AS SVC_PAUSE_YMD ") .append(" , NULL AS SVC_PAUSE_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RSN_MEMO ") .append(" , NULL AS SVC_PAUSE_RLS_YMD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0341.SVC_ENDYMD ") .append(" , KK0341.SVC_CHRG_ENDYMD ") .append(" , NULL AS SVC_DSL_KISAN_YMD ") .append(" , NULL AS SVC_DSL_YMD ") .append(" , NULL AS SVC_DLRE_CD ") .append(" , NULL AS SVC_DLRE_MEMO ") .append(" , NULL AS SVC_DSL_TTDKI_FIN_FLG ") .append(" , NULL AS KAIHK_YMD ") .append(" , NULL AS SVC_CANCEL_CL_YMD ") .append(" , NULL AS SVC_DSL_CL_YMD ") .append(" , NULL AS SVKEIUW_HKHASYMD ") .append(" , NULL AS CHRG_STA_YMD_HOSEI_UM ") .append(" , NULL AS SVC_PAUSE_CHRG_STA_YMD ") .append(" , NULL AS PNLTY_HASSEI_CD ") .append(" , NULL AS IDO_DIV ") .append(" , NULL AS SHOSA_DSL_FIN_CD ") .append(" , NULL AS KEIZK_MT_SVC_KEI_UCWK_NO ") .append(" , NULL AS KEIZK_AF_KEI_CHGECHU_FLG ") .append(" , NULL AS SBOP_SVC_KEI_KZKWRK_REQYMD ") .append(" , NULL AS SVC_PAUSE_PRC_TSTA_YM ") .append(" , KK0341.KIKI_SEIZO_NO ") .append(" , KK0341.TAKNKIKI_MODEL_CD ") .append(" , KK0341.KIKI_STC_SAKI_STATE_NM ") .append(" , KK0341.KIKI_STC_SAKI_CITY_NM ") .append(" , KK0341.KIKI_STC_SAKI_OAZTSU_NM ") .append(" , KK0341.KIKI_STC_SAKI_AZCHO_NM ") .append(" , KK0341.KIKI_STC_SAKI_BNCHIGO ") .append(" , KK0341.KIKI_STC_SAKI_ADRTTM ") .append(" , KK0341.KIKI_STC_SAKI_ADRRM ") .append(" , KK0341.KIKI_CHG_RSN_CD ") .append(" , KK0341.TAKNKIKI_SETHIN_MODEL_CD ") .append(" , KK0341.KKTK_SBT_CD ") .append(" , KK0341.KIKI_CHG_NO ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO IN ( ") .append(svcKeiNo) .append(" ) ") // v25.00.00 MOD START // .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210', '220') ") // v25.00.00 MOD END .append(" AND KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM IN ( ") .append(" ( SELECT MAX(KK0341_GENE_01.RSV_APLY_YMD || KK0341_GENE_01.GENE_ADD_DTM) AS KK0341_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE_01 ") .append(" WHERE KK0341_GENE_01.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" AND KK0341_GENE_01.RSV_APLY_CD = '2' ") .append(" AND KK0341_GENE_01.RSV_APLY_YMD <= ? ") .append(" AND KK0341_GENE_01.MK_FLG = '0' ") .append(" ) ") .append(" ,( SELECT MAX(KK0341_GENE_02.RSV_APLY_YMD || KK0341_GENE_02.GENE_ADD_DTM) AS KK0341_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE_02 ") .append(" WHERE KK0341_GENE_02.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" AND KK0341_GENE_02.RSV_APLY_CD IN ('1', '2') ") .append(" AND KK0341_GENE_02.MK_FLG = '0' ") .append(" )) ") .append(" UNION ") .append(" SELECT ") .append(" '07' AS TG_KEI_SKBT_CD ") .append(" , KK0441.SVC_KEI_NO ") .append(" , NULL AS SVC_KEI_UCWK_NO ") .append(" , NULL AS OP_SVC_KEI_NO ") .append(" , NULL AS SBOP_SVC_KEI_NO ") .append(" , NULL AS KKTK_SVC_KEI_NO ") .append(" , KK0441.SEIOPSVC_KEI_NO ") .append(" , KK0441.SEIKY_KEI_NO ") .append(" , KK0441.GENE_ADD_DTM ") .append(" , NULL AS SVC_KEI_UCWK_STAT ") .append(" , NULL AS OP_SVC_KEI_STAT ") .append(" , NULL AS SBOP_SVC_KEI_STAT ") .append(" , NULL AS KKTK_SVC_KEI_STAT ") .append(" , KK0441.SEIOPSVC_KEI_STAT ") .append(" , KK0441.MSKM_DTL_NO ") .append(" , NULL AS OP_SVC_CD ") .append(" , NULL AS SBOP_SVC_CD ") .append(" , NULL AS KKTK_SVC_CD ") .append(" , KK0441.SEIOPSVC_CD ") .append(" , KK0441.PCRS_CD ") .append(" , KK0441.PPLAN_CD ") .append(" , NULL AS TK_HOSHIKI_KEI_NO ") .append(" , NULL AS PAYWAY_KEIZOKU_FLG ") .append(" , NULL AS WEB_OP_ADD_FAIL_FLG ") .append(" , NULL AS WORK_RRK_BIKO ") .append(" , NULL AS SVC_USE_STA_KIBO_YMD ") .append(" , NULL AS SVC_UEST_KBTMZ_CD ") .append(" , NULL AS SVC_UEST_KBTM_CD ") .append(" , NULL AS RSV_TSTA_KIBO_YMD ") .append(" , NULL AS SKEKKA_SEND_CD ") .append(" , NULL AS SVC_KEI_UCWK_KZKWRK_REQYMD ") .append(" , NULL AS SHOSA_YMD ") .append(" , NULL AS SHOSA_CL_YMD ") .append(" , NULL AS FTRIAL_KANYU_YMD ") .append(" , NULL AS FTRIAL_PRD_ENDYMD ") .append(" , NULL AS HONKANYU_YMD ") .append(" , NULL AS HONKANYU_IKO_KIGEN_YMD ") .append(" , NULL AS KEI_CNC_YMD ") .append(" , KK0441.RSV_APLY_YMD ") .append(" , NULL AS RSV_CL_YMD ") .append(" , NULL AS RSV_APLY_CD ") .append(" , NULL AS PLAN_STAYMD ") .append(" , NULL AS PLAN_ENDYMD ") .append(" , NULL AS PLAN_CHRG_STAYMD ") .append(" , NULL AS PLAN_CHRG_ENDYMD ") .append(" , NULL AS PLAN_END_SBT_CD ") .append(" , NULL AS SVC_CANCEL_YMD ") .append(" , NULL AS SVC_CANCEL_RSN_CD ") .append(" , KK0441.SVC_STA_YMD ") .append(" , KK0441.SVC_CHRG_STAYMD ") .append(" , NULL AS SVC_STP_YMD ") .append(" , NULL AS SVC_STP_RSN_CD ") .append(" , NULL AS SVC_STP_RLS_YMD ") .append(" , NULL AS SVC_STP_RLS_RSN_CD ") .append(" , NULL AS PAUSE_STP_CD ") .append(" , NULL AS SVC_PAUSE_YMD ") .append(" , NULL AS SVC_PAUSE_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RSN_MEMO ") .append(" , NULL AS SVC_PAUSE_RLS_YMD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_CD ") .append(" , NULL AS SVC_PAUSE_RLS_RSN_MEMO ") .append(" , KK0441.SVC_ENDYMD ") .append(" , KK0441.SVC_CHRG_ENDYMD ") .append(" , NULL AS SVC_DSL_KISAN_YMD ") .append(" , KK0441.SVC_DSL_YMD ") .append(" , NULL AS SVC_DLRE_CD ") .append(" , NULL AS SVC_DLRE_MEMO ") .append(" , NULL AS SVC_DSL_TTDKI_FIN_FLG ") .append(" , NULL AS KAIHK_YMD ") .append(" , NULL AS SVC_CANCEL_CL_YMD ") .append(" , NULL AS SVC_DSL_CL_YMD ") .append(" , NULL AS SVKEIUW_HKHASYMD ") .append(" , NULL AS CHRG_STA_YMD_HOSEI_UM ") .append(" , NULL AS SVC_PAUSE_CHRG_STA_YMD ") .append(" , NULL AS PNLTY_HASSEI_CD ") .append(" , KK0441.IDO_DIV ") .append(" , NULL AS SHOSA_DSL_FIN_CD ") .append(" , NULL AS KEIZK_MT_SVC_KEI_UCWK_NO ") .append(" , NULL AS KEIZK_AF_KEI_CHGECHU_FLG ") .append(" , NULL AS SBOP_SVC_KEI_KZKWRK_REQYMD ") .append(" , NULL AS SVC_PAUSE_PRC_TSTA_YM ") .append(" , NULL AS KIKI_SEIZO_NO ") .append(" , NULL AS TAKNKIKI_MODEL_CD ") .append(" , NULL AS KIKI_STC_SAKI_STATE_NM ") .append(" , NULL AS KIKI_STC_SAKI_CITY_NM ") .append(" , NULL AS KIKI_STC_SAKI_OAZTSU_NM ") .append(" , NULL AS KIKI_STC_SAKI_AZCHO_NM ") .append(" , NULL AS KIKI_STC_SAKI_BNCHIGO ") .append(" , NULL AS KIKI_STC_SAKI_ADRTTM ") .append(" , NULL AS KIKI_STC_SAKI_ADRRM ") .append(" , NULL AS KIKI_CHG_RSN_CD ") .append(" , NULL AS TAKNKIKI_SETHIN_MODEL_CD ") .append(" , NULL AS KKTK_SBT_CD ") .append(" , NULL AS KIKI_CHG_NO ") .append(" FROM ") .append(" KK_T_SEIOPSVC_KEI KK0441 ") .append(" WHERE ") .append(" (KK0441.SEIOPSVC_KEI_NO, KK0441.RSV_APLY_YMD || KK0441.GENE_ADD_DTM) = ") .append(" (SELECT KK0441_GENE.SEIOPSVC_KEI_NO, MAX(KK0441_GENE.RSV_APLY_YMD || KK0441_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SEIOPSVC_KEI KK0441_GENE ") .append(" WHERE KK0441_GENE.SEIOPSVC_KEI_NO = KK0441.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(" AND KK0441.SVC_KEI_NO IN ( ") .append(svcKeiNo) .append(" ) ") .append(" ) "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK0081B037CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" ORDER BY ") .append(" TG_KEI_SKBT_CD ") .append(" , SVC_KEI_NO ") .append(" , SVC_KEI_UCWK_NO ") .append(" , OP_SVC_KEI_NO ") .append(" , SBOP_SVC_KEI_NO ") .append(" , KKTK_SVC_KEI_NO ") .append(" , SEIOPSVC_KEI_NO "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EKK0081B037CBSMsg.FUNC_CODE) ) ) { // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) for (int i = 0; i < wkSvcKeiNo.length; i++) { CAANJDBCUtil.setParam(pstmt, iPCnt++, wkSvcKeiNo[i]); } // パラメータの設定(運用日付を指定) 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)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) for (int i = 0; i < wkSvcKeiNo.length; i++) { CAANJDBCUtil.setParam(pstmt, iPCnt++, wkSvcKeiNo[i]); } // パラメータの設定(運用日付を指定) 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)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) for (int i = 0; i < wkSvcKeiNo.length; i++) { CAANJDBCUtil.setParam(pstmt, iPCnt++, wkSvcKeiNo[i]); } // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) for (int i = 0; i < wkSvcKeiNo.length; i++) { CAANJDBCUtil.setParam(pstmt, iPCnt++, wkSvcKeiNo[i]); } // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) for (int i = 0; i < wkSvcKeiNo.length; i++) { CAANJDBCUtil.setParam(pstmt, iPCnt++, wkSvcKeiNo[i]); } } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0081B037CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0081B037CBSMsg1List.TG_KEI_SKBT_CD , EKK0081B037CBSMsg1List.SVC_KEI_NO , EKK0081B037CBSMsg1List.SVC_KEI_UCWK_NO , EKK0081B037CBSMsg1List.OP_SVC_KEI_NO , EKK0081B037CBSMsg1List.SBOP_SVC_KEI_NO , EKK0081B037CBSMsg1List.KKTK_SVC_KEI_NO , EKK0081B037CBSMsg1List.SEIOPSVC_KEI_NO , EKK0081B037CBSMsg1List.SEIKY_KEI_NO , EKK0081B037CBSMsg1List.GENE_ADD_DTM , EKK0081B037CBSMsg1List.SVC_KEI_UCWK_STAT , EKK0081B037CBSMsg1List.OP_SVC_KEI_STAT , EKK0081B037CBSMsg1List.SBOP_SVC_KEI_STAT , EKK0081B037CBSMsg1List.KKTK_SVC_KEI_STAT , EKK0081B037CBSMsg1List.SEIOPSVC_KEI_STAT , EKK0081B037CBSMsg1List.MSKM_DTL_NO , EKK0081B037CBSMsg1List.OP_SVC_CD , EKK0081B037CBSMsg1List.SBOP_SVC_CD , EKK0081B037CBSMsg1List.KKTK_SVC_CD , EKK0081B037CBSMsg1List.SEIOPSVC_CD , EKK0081B037CBSMsg1List.PCRS_CD , EKK0081B037CBSMsg1List.PPLAN_CD , EKK0081B037CBSMsg1List.TK_HOSHIKI_KEI_NO , EKK0081B037CBSMsg1List.PAYWAY_KEIZOKU_FLG , EKK0081B037CBSMsg1List.WEB_OP_ADD_FAIL_FLG , EKK0081B037CBSMsg1List.WORK_RRK_BIKO , EKK0081B037CBSMsg1List.SVC_USE_STA_KIBO_YMD , EKK0081B037CBSMsg1List.SVC_UEST_KBTMZ_CD , EKK0081B037CBSMsg1List.SVC_UEST_KBTM_CD , EKK0081B037CBSMsg1List.RSV_TSTA_KIBO_YMD , EKK0081B037CBSMsg1List.SKEKKA_SEND_CD , EKK0081B037CBSMsg1List.SVC_KEI_UCWK_KZKWRK_REQYMD , EKK0081B037CBSMsg1List.SHOSA_YMD , EKK0081B037CBSMsg1List.SHOSA_CL_YMD , EKK0081B037CBSMsg1List.FTRIAL_KANYU_YMD , EKK0081B037CBSMsg1List.FTRIAL_PRD_ENDYMD , EKK0081B037CBSMsg1List.HONKANYU_YMD , EKK0081B037CBSMsg1List.HONKANYU_IKO_KIGEN_YMD , EKK0081B037CBSMsg1List.KEI_CNC_YMD , EKK0081B037CBSMsg1List.RSV_APLY_YMD , EKK0081B037CBSMsg1List.RSV_CL_YMD , EKK0081B037CBSMsg1List.RSV_APLY_CD , EKK0081B037CBSMsg1List.PLAN_STAYMD , EKK0081B037CBSMsg1List.PLAN_ENDYMD , EKK0081B037CBSMsg1List.PLAN_CHRG_STAYMD , EKK0081B037CBSMsg1List.PLAN_CHRG_ENDYMD , EKK0081B037CBSMsg1List.PLAN_END_SBT_CD , EKK0081B037CBSMsg1List.SVC_CANCEL_YMD , EKK0081B037CBSMsg1List.SVC_CANCEL_RSN_CD , EKK0081B037CBSMsg1List.SVC_STA_YMD , EKK0081B037CBSMsg1List.SVC_CHRG_STAYMD , EKK0081B037CBSMsg1List.SVC_STP_YMD , EKK0081B037CBSMsg1List.SVC_STP_RSN_CD , EKK0081B037CBSMsg1List.SVC_STP_RLS_YMD , EKK0081B037CBSMsg1List.SVC_STP_RLS_RSN_CD , EKK0081B037CBSMsg1List.PAUSE_STP_CD , EKK0081B037CBSMsg1List.SVC_PAUSE_YMD , EKK0081B037CBSMsg1List.SVC_PAUSE_RSN_CD , EKK0081B037CBSMsg1List.SVC_PAUSE_RSN_MEMO , EKK0081B037CBSMsg1List.SVC_PAUSE_RLS_YMD , EKK0081B037CBSMsg1List.SVC_PAUSE_RLS_RSN_CD , EKK0081B037CBSMsg1List.SVC_PAUSE_RLS_RSN_MEMO , EKK0081B037CBSMsg1List.SVC_ENDYMD , EKK0081B037CBSMsg1List.SVC_CHRG_ENDYMD , EKK0081B037CBSMsg1List.SVC_DSL_KISAN_YMD , EKK0081B037CBSMsg1List.SVC_DSL_YMD , EKK0081B037CBSMsg1List.SVC_DLRE_CD , EKK0081B037CBSMsg1List.SVC_DLRE_MEMO , EKK0081B037CBSMsg1List.SVC_DSL_TTDKI_FIN_FLG , EKK0081B037CBSMsg1List.KAIHK_YMD , EKK0081B037CBSMsg1List.SVC_CANCEL_CL_YMD , EKK0081B037CBSMsg1List.SVC_DSL_CL_YMD , EKK0081B037CBSMsg1List.SVKEIUW_HKHASYMD , EKK0081B037CBSMsg1List.CHRG_STA_YMD_HOSEI_UM , EKK0081B037CBSMsg1List.SVC_PAUSE_CHRG_STA_YMD , EKK0081B037CBSMsg1List.PNLTY_HASSEI_CD , EKK0081B037CBSMsg1List.IDO_DIV , EKK0081B037CBSMsg1List.SHOSA_DSL_FIN_CD , EKK0081B037CBSMsg1List.KEIZK_MT_SVC_KEI_UCWK_NO , EKK0081B037CBSMsg1List.KEIZK_AF_KEI_CHGECHU_FLG , EKK0081B037CBSMsg1List.SBOP_SVC_KEI_KZKWRK_REQYMD , EKK0081B037CBSMsg1List.SVC_PAUSE_PRC_TSTA_YM , EKK0081B037CBSMsg1List.KIKI_SEIZO_NO , EKK0081B037CBSMsg1List.TAKNKIKI_MODEL_CD , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_STATE_NM , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_CITY_NM , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_OAZTSU_NM , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_AZCHO_NM , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_BNCHIGO , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_ADRTTM , EKK0081B037CBSMsg1List.KIKI_STC_SAKI_ADRRM , EKK0081B037CBSMsg1List.KIKI_CHG_RSN_CD , EKK0081B037CBSMsg1List.TAKNKIKI_SETHIN_MODEL_CD , EKK0081B037CBSMsg1List.KKTK_SBT_CD , EKK0081B037CBSMsg1List.KIKI_CHG_NO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B037CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0081B037CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0081B037CBSMsg.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(EKK0081B037CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }