/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK3051B001TPDA * ソースファイル名：JSYejbEKK3051B001TPDA.java * 作成者：EK918345 * 日付：2016年07月04日 *＜機能概要＞ * チケット利用申請対象施策一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * v26.01.00 2016/07/25 FJ）笠川【IT2-2016-0000040】多機能ルータの異動判定キーを配送番号から機器提供サービス契約と変更番号にする * v26.02.00 2016/07/25 FJ）笠川【IT2-2016-0000041】STBとC-CASの2重出力抑止 * v26.03.00 2016/08/30 FJ）阮【ST-2016-0000030 】『施策申請条件個別指定コード』項目取得の追加 * v28.00.00 2016/12/14 FJ）舘山【OM-2016-0002509】多機能ルータのキャンセルで明細が２件返される * v33.00.00 2017/07/28 FJ）平野【ANK-3217-00-00】GHサービス開始対応 * v33.01.00 2017/08/21 FJ）平野【IT1-2017-0000102】機器追加でＧＨ機器登録時重複して施策が返却される * v33.02.00 2017/08/25 FJ）平野【ANK-3236-00-00】チケットによる割引CPの重複適用防止のための画面制御 * v33.03.00 2017/10/23 FJ) 平野【ANK-3288-00-00】【eoプレミアムクラブ】TVガイド誌無料×１年間 * v35.00.00 2017/12/04 FJ) 河邊【OM-2017-0001213】オープンカーソルエラー * v34.00.00 2017/12/07 FJ) 平野【ANK-3303-00-00】【eoプレミアムクラブ】セキュリティパック・多機能無線ルータ無料×１年間 * v41.00.00 2019/02/07 FJ) 謝【ANK-3529-00-00】無線LAN中継器レンタルサービス開始対応 * v41.00.01 2019/04/11 FJ) 永井【OM-2019-0000407】 * v45.00.00 2019/07/24 FJ)吉田【ANK-3636-00-00】消費増税対応（8%⇒10%） * v50.00.00 2020/06/18 FJ)西窪【ANK-3825-00-00】メッシュWiFi導入対応 * v50.01.00 2020/06/01 FJ)中原【ANK-3754-00-00】トビラフォン対応 * v51.00.00 2020/10/28 FJ)荒木【ANK-3918-00-00】プレクラ施策追加「インターネットサギウォール」「おうちの機器補償」 * v51.00.01 2020/11/02 FJ)西窪【ANK-3949-00-00】Netflix導入対応（STEP1） * v52.00.00 2021/01/25 FJ)吉村【ANK-3987-00-00】Netflix導入対応（STEP2） * v52.00.01 2021/02/03 FJ)吉村【IT1-2021-0000010】ストリーミングメディアプレーヤー料金プランコード変更対応 * v54.00.00 2021/07/27 FJ)秋末【ANK-4094-00-00】ストリーミングメディアプレーヤー価格改定対応 * v63.00.00 2022/12/07 FJ)佐々木【OM-2021-0000558】 * v66.00.00 2023/02/27 FJ)松永【ANK-4307-00-00】【eo定期】プレミアムクラブリニューアル対応 * v61.00.00 2023/04/10 FJ)井手【ANK-4315-00-00】【eo定期】eoホームゲートウェイ導入対応 * v61.00.01 2023/08/31 FJ)新井【ANK-4315-00-00】【eo定期】eoホームゲートウェイ導入対応 * v68.00.00 2023/12/13 FJ)新井【#83584】【eo顧客】KKIFE336：多機能からHGWへ交換中にKKIFE336を実行するとシステムエラー * v75.00.00 2025/02/19 FJ)張【ANK-4640-00-00】NTT卸対応：STEP1.5 ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; 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.ejb.cbm.cbmmsg.ZM0321CBMMsg; import eo.ejb.cbm.entity.KK3051ETMsg; import eo.ejb.cbs.cbsmsg.EKK3051B001CBSMsg; import eo.ejb.cbs.cbsmsg.EKK3051B001CBSMsg1List; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK3051B001_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK3051B001TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK3051B001TPDA() { } /** * EKK3051B001_SQLF
* @param inMsg 入力値の格納されたCBSMsg
*/ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; //ANK-3217-00-00 ADD START // 業務パラメータ管理用 Connection conZM0321 = null; PreparedStatement pstmtZM0321 = null; ResultSet rsZM0321 = null; StringBuffer ghTakCodeInStmt = new StringBuffer(); //ANK-3236-00-00 ADD START StringBuffer ghSisakCpCaseStmt = new StringBuffer(); StringBuffer ghSisakModelCaseStmt = new StringBuffer(); //ANK-3236-00-00 ADD END // ANK-3288-00-00 ADD START // 施策-キャンペーンcase文組み立て用（登録時取得分） StringBuffer sskCpCaseStmt = new StringBuffer(); // ANK-3288-00-00 ADD END // SQL文_GH宅内機器型式コード取得 StringBuffer sql_Buff_GH = new StringBuffer(); sql_Buff_GH.append("SELECT /*EKK3051B001*/ ") .append(" ZM0321.WORK_PARAM_SETTE_VALUE ") .append("FROM ") .append(" ZM_M_WORK_PARAM_KNRI ZM0321 ") .append("WHERE ") .append(" WORK_PARAM_ID = 'KK_GH_TKKKMDL' ") .append("AND WORK_PARAM_TSTAYMD <= ? ") .append("AND WORK_PARAM_TENDYMD >= ? ") .append("AND MK_FLG = '0'"); //ANK-3236-00-00 ADD START // SQL文_GH施策-キャンペーン-型式情報取得 StringBuffer sql_Buff_GH_2 = new StringBuffer(); sql_Buff_GH_2.append("SELECT /*EKK3051B001*/ ") .append(" ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ") .append(" ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ") .append(" WORK_PARAM_ID LIKE 'KK_GH_SSK_CP_MDL%' ") .append(" AND WORK_PARAM_TSTAYMD <= ? ") .append(" AND WORK_PARAM_TENDYMD >= ? ") .append(" AND MK_FLG = '0' "); //ANK-3236-00-00 ADD END // ANK-3288-00-00 ADD START // SQL文_施策-キャンペーン情報取得 StringBuffer sql_Buff_SSK_CP = new StringBuffer(); sql_Buff_SSK_CP.append(" SELECT /*EKK3051B001*/ ") .append(" ZM0321.WORK_PARAM_SETTE_VALUE ") .append(" FROM ") .append(" ZM_M_WORK_PARAM_KNRI ZM0321 ") .append(" WHERE ") .append(" WORK_PARAM_ID LIKE 'KK_SSK_CP%' ") .append(" AND WORK_PARAM_TSTAYMD <= ? ") .append(" AND WORK_PARAM_TENDYMD >= ? ") .append(" AND MK_FLG = '0' "); // ANK-3288-00-00 ADD END try { //コネクション取得 conZM0321 = JSYejbConnection.getConnection(KK3051ETMsg.getTableName()); //prepareStatementにSQL文をセット pstmtZM0321 = conZM0321.prepareStatement(sql_Buff_GH.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff_GH); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals(inMsg.getString(EKK3051B001CBSMsg.FUNC_CODE) ) ) { // 運用日付を取得 String opeDate = JKKModelCommon.getOpeDate(inMsg); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 1, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 2, opeDate); } // ResultSetの取得 rsZM0321 = pstmtZM0321.executeQuery(); // GH宅内機器型式コード String tmpGhTakCode = null; String[] ghTakCode = null; // 結果の取得 while(rsZM0321.next()) { tmpGhTakCode = rsZM0321.getString(ZM0321CBMMsg.WORK_PARAM_SETTE_VALUE); ghTakCode = tmpGhTakCode.split(","); } if (ghTakCode != null) { ghTakCodeInStmt.append("("); // GH宅内機器型式コードを展開 for (String code : ghTakCode) { ghTakCodeInStmt.append("'" + code + "',"); } // 最後のカンマを除去 ghTakCodeInStmt.deleteCharAt(ghTakCodeInStmt.lastIndexOf(",")); ghTakCodeInStmt.append(")"); } //ANK-3236-00-00 ADD START // OM-2017-0001213 2017/12/04 ADD START if (pstmtZM0321 != null) { pstmtZM0321.close(); } // OM-2017-0001213 2017/12/04 ADD END //prepareStatementにSQL文_GH施策-キャンペーン-型式情報取得をセット pstmtZM0321 = conZM0321.prepareStatement(sql_Buff_GH_2.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff_GH_2); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals(inMsg.getString(EKK3051B001CBSMsg.FUNC_CODE) ) ) { // 運用日付を取得 String opeDate = JKKModelCommon.getOpeDate(inMsg); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 1, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 2, opeDate); } // OM-2017-0001213 2017/12/04 ADD START if (rsZM0321 != null) { rsZM0321.close(); } // OM-2017-0001213 2017/12/04 ADD END // ResultSetの取得 rsZM0321 = pstmtZM0321.executeQuery(); String tmpWorkParamSetteValue; String[] tmpWorkParamSetteValueArray = null; // 結果の取得 while(rsZM0321.next()) { tmpWorkParamSetteValue = rsZM0321.getString(ZM0321CBMMsg.WORK_PARAM_SETTE_VALUE); tmpWorkParamSetteValueArray = tmpWorkParamSetteValue.split(","); // 施策-キャンペーンコードのCASE式を組み立てる ghSisakCpCaseStmt.append(" WHEN '" + tmpWorkParamSetteValueArray[0] + "' ") .append(" THEN '" + tmpWorkParamSetteValueArray[1] + "' "); // 施策-宅内機器型式コードのCASE式を組み立てる ghSisakModelCaseStmt.append(" WHEN '" + tmpWorkParamSetteValueArray[0] + "' ") .append(" THEN '"+ tmpWorkParamSetteValueArray[2] + "' "); } //ANK-3236-00-00 ADD END // ANK-3288-00-00 ADD START // OM-2017-0001213 2017/12/04 ADD START if (pstmtZM0321 != null) { pstmtZM0321.close(); } // OM-2017-0001213 2017/12/04 ADD END //prepareStatementにSQL文_施策-キャンペーン情報取得をセット pstmtZM0321 = conZM0321.prepareStatement(sql_Buff_SSK_CP.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff_SSK_CP); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals(inMsg.getString(EKK3051B001CBSMsg.FUNC_CODE) ) ) { // 運用日付を取得 String opeDate = JKKModelCommon.getOpeDate(inMsg); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 1, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmtZM0321, 2, opeDate); } // OM-2017-0001213 2017/12/04 ADD START if (rsZM0321 != null) { rsZM0321.close(); } // OM-2017-0001213 2017/12/04 ADD END // ResultSetの取得 rsZM0321 = pstmtZM0321.executeQuery(); // 結果の取得 while(rsZM0321.next()) { tmpWorkParamSetteValue = rsZM0321.getString(ZM0321CBMMsg.WORK_PARAM_SETTE_VALUE); tmpWorkParamSetteValueArray = tmpWorkParamSetteValue.split(","); // 施策-キャンペーンコードのCASE式を組み立てる sskCpCaseStmt.append(" WHEN '" + tmpWorkParamSetteValueArray[0] + "' ") .append(" THEN '" + tmpWorkParamSetteValueArray[1] + "' "); } // ANK-3288-00-00 ADD END } catch(SQLException e) { inMsg.set(EKK3051B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { // 資源の解放 try { if(rsZM0321 != null) { rsZM0321.close(); } if(pstmtZM0321 != null) { pstmtZM0321.close(); } // ANK-3288-00-00 ADD START if(conZM0321 != null) { closeConnection(conZM0321); } // ANK-3288-00-00 ADD END }catch(SQLException e) { inMsg.set(EKK3051B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } //ANK-3217-00-00 ADD END /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK3051ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT /*EKK3051B001*/ KK3051.EOPRMCL_SISAK_CD ") .append(" , KK3051.CMP_ADD_OPTNTY_CD ") // ST-2016-0000030 ADD START .append(" , KK3061_00.SSKSSJK_KSITEI_CD ") // ST-2016-0000030 ADD END .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_1 ") // 2016/07/25 IT2-2016-0000040 MOD START // .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_2 ") // .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") // .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") // .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_5 ") // 2016/07/25 IT2-2016-0000040 MOD END .append(" FROM ( ") .append(" SELECT KK3061.EOPRMCL_SISAK_CD ") // ST-2016-0000030 ADD START .append(" , KK3061.SSKSSJK_KSITEI_CD ") // ST-2016-0000030 ADD END .append(" , KU0011.KOJIAK_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") // 2016/07/25 IT2-2016-0000040 ADD START .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") // 2016/07/25 IT2-2016-0000040 ADD END .append(" FROM ( ") .append(" SELECT DISTINCT ") .append(" KU0011_01.KOJIAK_NO ") .append(" , KU0011_01.KOJIAK_SBT_CD ") .append(" FROM KU_T_SVKEI_KOJIAK KU0081_01 ") .append(" INNER JOIN KU_T_KOJIAK KU0011_01 ") .append(" ON KU0011_01.KOJIAK_NO = KU0081_01.KOJIAK_NO ") .append(" WHERE KU0081_01.SVC_KEI_NO = ? ") .append(" AND KU0081_01.SVKEI_KOJIAK_TSTAYMD <= ? ") .append(" AND KU0081_01.SVKEI_KOJIAK_TENDYMD >= ? ") .append(" AND KU0081_01.MK_FLG = '0' ") .append(" AND KU0011_01.KOJIAK_STAT IN ('130', '140', '150', '160', '170', '180', '190') ") .append(" AND KU0011_01.MK_FLG = '0' ") .append(" ) KU0011 ") .append(" INNER JOIN KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" ON KK3061.EOPRMCL_SSKSSJK_SBT_CD = '01' ") .append(" AND KK3061.KOJIAK_SBT_CD = KU0011.KOJIAK_SBT_CD ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_1 IS NULL OR KK3061.TAKNKIKI_SBT_CD_1 IN ") .append(" (SELECT KU0141_01.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_01 ") .append(" WHERE KU0141_01.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_01.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_1 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_1 = KU0141_01.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_1 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_1 = KU0141_01.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_01.MK_FLG = '0') ") .append(" ) ") // IT1-2017-0000102 ADD START .append(" AND KK3061.TAKNKIKI_SBT_CD_1 <> 'P0' ") // IT1-2017-0000102 ADD END .append(" AND (KK3061.TAKNKIKI_SBT_CD_2 IS NULL OR KK3061.TAKNKIKI_SBT_CD_2 IN ") .append(" (SELECT KU0141_02.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_02 ") .append(" WHERE KU0141_02.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_02.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_2 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_2 = KU0141_02.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_2 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_2 = KU0141_02.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_02.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_3 IS NULL OR KK3061.TAKNKIKI_SBT_CD_3 IN ") .append(" (SELECT KU0141_03.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_03 ") .append(" WHERE KU0141_03.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_03.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_3 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_3 = KU0141_03.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_3 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_3 = KU0141_03.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_03.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_4 IS NULL OR KK3061.TAKNKIKI_SBT_CD_4 IN ") .append(" (SELECT KU0141_04.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_04 ") .append(" WHERE KU0141_04.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_04.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_4 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_4 = KU0141_04.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_4 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_4 = KU0141_04.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_04.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_5 IS NULL OR KK3061.TAKNKIKI_SBT_CD_5 IN ") .append(" (SELECT KU0141_05.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_05 ") .append(" WHERE KU0141_05.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_05.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_5 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_5 = KU0141_05.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_5 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_5 = KU0141_05.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_05.MK_FLG = '0') ") .append(" ) ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TSTAYMD <= ? ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TENDYMD >= ? ") .append(" AND KK3061.MK_FLG = '0' ") .append(" UNION ALL ") .append(" SELECT KK3061.EOPRMCL_SISAK_CD ") // 2016/07/25 IT2-2016-0000040 MOD START // .append(" , DK0011.HAISO_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") // ST-2016-0000030 ADD START .append(" , KK3061.SSKSSJK_KSITEI_CD ") // ST-2016-0000030 ADD END .append(" , DK0011.KKTK_SVC_KEI_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") .append(" , DK0011.KIKI_CHG_NO AS CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") // 2016/07/25 IT2-2016-0000040 MOD END .append(" FROM ( ") .append(" SELECT DISTINCT ") .append(" DK0011_01.HAISO_NO ") // 2016/07/25 IT2-2016-0000040 ADD START .append(" , DK0021_01.KKTK_SVC_KEI_NO") .append(" , DK0021_01.KIKI_CHG_NO ") // 2016/07/25 IT2-2016-0000040 ADD END .append(" FROM DK_T_HAISO_TG_BPIN DK0021_01 ") .append(" INNER JOIN DK_T_HAISO DK0011_01 ") .append(" ON DK0011_01.HAISO_NO = DK0021_01.HAISO_NO ") .append(" WHERE (DK0021_01.KKTK_SVC_KEI_NO, DK0021_01.KIKI_CHG_NO) IN ( ") .append(" SELECT KK0341_01.KKTK_SVC_KEI_NO ") .append(" , KK0341_01.KIKI_CHG_NO ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01 ") .append(" WHERE KK0341_01.OYA_KEI_SKBT_CD IN ('01', '03', '04') ") .append(" AND KK0341_01.SVC_KEI_NO = ? ") .append(" AND KK0341_01.KIKI_SORYO_UM = '1' ") // OM-2016-0002509 2016-12-15 ADD START .append(" AND KK0341_01.KKTK_SVC_KEI_STAT NOT IN ('910', '920') ") // OM-2016-0002509 2016-12-15 ADD END .append(" AND (KK0341_01.KKTK_SVC_KEI_NO, KK0341_01.KIKI_CHG_NO, NVL(KK0341_01.RSV_APLY_YMD, '20991231') || KK0341_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_01_GENE.KKTK_SVC_KEI_NO, KK0341_01_GENE.KIKI_CHG_NO ") .append(" , MAX(NVL(KK0341_01_GENE.RSV_APLY_YMD, '20991231') || KK0341_01_GENE.GENE_ADD_DTM) AS KK0341_01_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01_GENE ") .append(" WHERE KK0341_01_GENE.KKTK_SVC_KEI_NO = KK0341_01.KKTK_SVC_KEI_NO ") .append(" AND KK0341_01_GENE.KIKI_CHG_NO = KK0341_01.KIKI_CHG_NO ") .append(" AND KK0341_01_GENE.RSV_APLY_CD IN ('1', '2') ") .append(" AND KK0341_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_01_GENE.KKTK_SVC_KEI_NO, KK0341_01_GENE.KIKI_CHG_NO) ") .append(" UNION ") .append(" SELECT KK0341_02.KKTK_SVC_KEI_NO ") .append(" , KK0341_02.KIKI_CHG_NO ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02 ") .append(" WHERE KK0341_02.OYA_KEI_SKBT_CD = '02' ") .append(" AND KK0341_02.SVC_KEI_KAISEN_UCWK_NO IN ( ") .append(" SELECT KK0241_02.SVC_KEI_KAISEN_UCWK_NO ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_02 ") .append(" WHERE KK0241_02.SVC_KEI_NO = ? ") .append(" AND KK0241_02.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_02.KAISEN_UCWK_USE_ENDYMD >= ? ") .append(" AND KK0241_02.MK_FLG = '0' ") .append(" ) ") .append(" AND KK0341_02.KIKI_SORYO_UM = '1' ") // OM-2016-0002509 2016-12-15 ADD START .append(" AND KK0341_02.KKTK_SVC_KEI_STAT NOT IN ('910', '920') ") // OM-2016-0002509 2016-12-15 ADD END .append(" AND (KK0341_02.KKTK_SVC_KEI_NO, KK0341_02.KIKI_CHG_NO, NVL(KK0341_02.RSV_APLY_YMD, '20991231') || KK0341_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_02_GENE.KKTK_SVC_KEI_NO, KK0341_02_GENE.KIKI_CHG_NO ") .append(" , MAX(NVL(KK0341_02_GENE.RSV_APLY_YMD, '20991231') || KK0341_02_GENE.GENE_ADD_DTM) AS KK0341_02_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02_GENE ") .append(" WHERE KK0341_02_GENE.KKTK_SVC_KEI_NO = KK0341_02.KKTK_SVC_KEI_NO ") .append(" AND KK0341_02_GENE.KIKI_CHG_NO = KK0341_02.KIKI_CHG_NO ") .append(" AND KK0341_02_GENE.RSV_APLY_CD IN ('1', '2') ") .append(" AND KK0341_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_02_GENE.KKTK_SVC_KEI_NO, KK0341_02_GENE.KIKI_CHG_NO) ") .append(" ) ") .append(" AND DK0021_01.MK_FLG = '0' ") .append(" AND DK0011_01.HISOS_KOCOMP_CD IS NULL ") .append(" AND DK0011_01.HAISO_STAT IN ('001', '002', '003', '004') ") .append(" AND NVL(DK0011_01.HAISO_FIN_YMD, '20991231') > ? ") .append(" AND DK0011_01.MK_FLG = '0' ") .append(" ) DK0011 ") .append(" INNER JOIN KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" ON KK3061.EOPRMCL_SSKSSJK_SBT_CD = '02' ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_1 IS NULL OR KK3061.TAKNKIKI_SBT_CD_1 IN ") .append(" (SELECT DK0021_01.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_01 ") .append(" WHERE DK0021_01.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_01.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_01.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_01.MK_FLG = '0') ") .append(" ) ") // IT1-2017-0000102 ADD START .append(" AND KK3061.TAKNKIKI_SBT_CD_1 <> 'P0' ") // IT1-2017-0000102 ADD END .append(" AND (KK3061.TAKNKIKI_SBT_CD_2 IS NULL OR KK3061.TAKNKIKI_SBT_CD_2 IN ") .append(" (SELECT DK0021_02.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_02 ") .append(" WHERE DK0021_02.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_02.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_02.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_02.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_3 IS NULL OR KK3061.TAKNKIKI_SBT_CD_3 IN ") .append(" (SELECT DK0021_03.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_03 ") .append(" WHERE DK0021_03.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_03.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_03.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_03.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_4 IS NULL OR KK3061.TAKNKIKI_SBT_CD_4 IN ") .append(" (SELECT DK0021_04.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_04 ") .append(" WHERE DK0021_04.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_04.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_04.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_04.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_5 IS NULL OR KK3061.TAKNKIKI_SBT_CD_5 IN ") .append(" (SELECT DK0021_05.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_05 ") .append(" WHERE DK0021_05.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_05.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_05.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_05.MK_FLG = '0') ") .append(" ) ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TSTAYMD <= ? ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TENDYMD >= ? ") .append(" AND KK3061.MK_FLG = '0' ") //ANK-3217-00-00 ADD START // 工事案件情報取得（GHプレミアムサポート施策対象） .append(" UNION ALL ") .append(" SELECT KK3061.EOPRMCL_SISAK_CD ") .append(" , KK3061.SSKSSJK_KSITEI_CD ") .append(" , KU0011.KOJIAK_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") .append(" FROM ( ") .append(" SELECT DISTINCT ") .append(" KU0011_01.KOJIAK_NO ") .append(" , KU0011_01.KOJIAK_SBT_CD ") .append(" FROM KU_T_SVKEI_KOJIAK KU0081_01 ") .append(" INNER JOIN KU_T_KOJIAK KU0011_01 ") .append(" ON KU0011_01.KOJIAK_NO = KU0081_01.KOJIAK_NO ") .append(" WHERE KU0081_01.SVC_KEI_NO = ? ") .append(" AND KU0081_01.SVKEI_KOJIAK_TSTAYMD <= ? ") .append(" AND KU0081_01.SVKEI_KOJIAK_TENDYMD >= ? ") .append(" AND KU0081_01.MK_FLG = '0' ") .append(" AND KU0011_01.KOJIAK_STAT IN ('130', '140', '150', '160', '170', '180', '190') ") .append(" AND KU0011_01.MK_FLG = '0' ") .append(" ) KU0011 ") .append(" INNER JOIN KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" ON KK3061.EOPRMCL_SSKSSJK_SBT_CD = '01' ") .append(" AND (KK3061.KOJIAK_SBT_CD IS NULL OR KK3061.KOJIAK_SBT_CD = KU0011.KOJIAK_SBT_CD) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_1 IS NULL OR KK3061.TAKNKIKI_SBT_CD_1 IN ") .append(" (SELECT KU0141_01.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_01 ") .append(" WHERE KU0141_01.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_01.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_1 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_1 = KU0141_01.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_1 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_1 = KU0141_01.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_01.MK_FLG = '0' ") .append(" AND KU0141_01.RSV_TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt).append(") ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_2 IS NULL OR KK3061.TAKNKIKI_SBT_CD_2 IN ") .append(" (SELECT KU0141_02.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_02 ") .append(" WHERE KU0141_02.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_02.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_2 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_2 = KU0141_02.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_2 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_2 = KU0141_02.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_02.MK_FLG = '0' ") .append(" AND KU0141_02.RSV_TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt).append(") ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_3 IS NULL OR KK3061.TAKNKIKI_SBT_CD_3 IN ") .append(" (SELECT KU0141_03.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_03 ") .append(" WHERE KU0141_03.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_03.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_3 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_3 = KU0141_03.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_3 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_3 = KU0141_03.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_03.MK_FLG = '0' ") .append(" AND KU0141_03.RSV_TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt).append(") ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_4 IS NULL OR KK3061.TAKNKIKI_SBT_CD_4 IN ") .append(" (SELECT KU0141_04.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_04 ") .append(" WHERE KU0141_04.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_04.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_4 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_4 = KU0141_04.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_4 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_4 = KU0141_04.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_04.MK_FLG = '0' ") .append(" AND KU0141_04.RSV_TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt).append(") ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_5 IS NULL OR KK3061.TAKNKIKI_SBT_CD_5 IN ") .append(" (SELECT KU0141_05.TAKNKIKI_SBT_CD ") .append(" FROM KU_T_KJAK_TG_TKKIKI KU0141_05 ") .append(" WHERE KU0141_05.KOJIAK_NO = KU0011.KOJIAK_NO ") .append(" AND KU0141_05.KOJIAK_TRGT_TAKNKIKI_STAT IN ('130', '140', '150') ") .append(" AND (KK3061.STC_TEKKYO_FLG_5 IS NULL ") .append(" OR KK3061.STC_TEKKYO_FLG_5 = KU0141_05.STC_TEKKYO_FLG) ") .append(" AND (KK3061.TAKNKIKI_IDO_CD_5 IS NULL ") .append(" OR KK3061.TAKNKIKI_IDO_CD_5 = KU0141_05.TAKNKIKI_IDO_CD) ") .append(" AND KU0141_05.MK_FLG = '0' ") .append(" AND KU0141_05.RSV_TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt).append(") ") .append(" ) ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TSTAYMD <= ? ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TENDYMD >= ? ") .append(" AND KK3061.MK_FLG = '0' ") //ANK-3236-00-00 ADD START .append(" WHERE ") .append(" NOT EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0481.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.SVC_KEI_NO = ? ") .append(" AND KK0451.WRIB_SVC_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'X' THEN 'X' ") .append(ghSisakCpCaseStmt.toString()) .append(" ELSE NULL ") .append(" END ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO, MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, ") .append(" KK0481_GENE.WRIB_SVC_TRGT_KEI_NO ") .append(" ) ") .append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRIB_SVC_KEI KK0451_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO ") .append(" ) ") .append(" ) AND ") .append(" EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341_03 ") .append(" INNER JOIN ") .append(" KK_T_KAP_KEI KK2541 ") .append(" ON ") .append(" KK0341_03.KKTK_SVC_KEI_NO = KK2541.KKTK_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0341_03.OYA_KEI_SKBT_CD IN ('01', '03', '04') AND ") .append(" KK0341_03.SVC_KEI_NO = ? AND ") .append(" KK0341_03.KKTK_SVC_KEI_STAT NOT IN ('910', '920') AND ") .append(" KK0341_03.TAKNKIKI_MODEL_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'X' THEN 'X' ") .append(ghSisakModelCaseStmt.toString()) .append(" ELSE NULL ") .append(" END AND ") // ANK-3636-00-00 MOD START // .append(" KK2541.KAP_PLAN_CD = ") .append(" (KK2541.KAP_PLAN_CD = ") // ANK-3636-00-00 MOD END .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B003' THEN 'KP0035' ") .append(" WHEN 'B005' THEN 'KP0034' ") .append(" WHEN 'B007' THEN 'KP0036' ") .append(" WHEN 'B009' THEN 'KP0037' ") .append(" ELSE NULL ") // ANK-3636-00-00 ADD START .append(" END OR ") .append(" KK2541.KAP_PLAN_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B003' THEN 'KP0135' ") .append(" WHEN 'B005' THEN 'KP0134' ") .append(" WHEN 'B007' THEN 'KP0136' ") .append(" WHEN 'B009' THEN 'KP0137' ") .append(" ELSE NULL ") .append(" END OR ") .append(" KK2541.KAP_PLAN_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B003' THEN 'KP1135' ") .append(" WHEN 'B005' THEN 'KP1134' ") .append(" WHEN 'B007' THEN 'KP1136' ") .append(" WHEN 'B009' THEN 'KP1137' ") .append(" ELSE NULL ") // ANK-3636-00-00 ADD END // ANK-3636-00-00 MOD START // .append(" END AND ") .append(" END) AND ") // ANK-3636-00-00 MOD END .append(" (KK0341_03.KKTK_SVC_KEI_NO, KK0341_03.KIKI_CHG_NO, NVL(KK0341_03.RSV_APLY_YMD, '20991231') || KK0341_03.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO, ") .append(" KK0341_03_GENE.KIKI_CHG_NO , ") .append(" MAX(NVL(KK0341_03_GENE.RSV_APLY_YMD, '20991231') || KK0341_03_GENE.GENE_ADD_DTM) AS KK0341_03_GENE_MAX ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341_03_GENE ") .append(" WHERE ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO = KK0341_03.KKTK_SVC_KEI_NO AND ") .append(" KK0341_03_GENE.KIKI_CHG_NO = KK0341_03.KIKI_CHG_NO AND ") .append(" KK0341_03_GENE.RSV_APLY_CD IN ('1', '2') AND ") .append(" KK0341_03_GENE.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO, ") .append(" KK0341_03_GENE.KIKI_CHG_NO ") .append(" ) AND ") .append(" (KK2541.KAP_KEI_NO, KK2541.RSV_APLY_YMD || KK2541.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK2541_GENE.KAP_KEI_NO, ") .append(" MAX(KK2541_GENE.RSV_APLY_YMD || KK2541_GENE.GENE_ADD_DTM) AS KK2541_GENE_MAX ") .append(" FROM ") .append(" KK_T_KAP_KEI KK2541_GENE ") .append(" WHERE ") .append(" KK2541_GENE.KAP_KEI_NO = KK2541.KAP_KEI_NO AND ") .append(" KK2541_GENE.RSV_APLY_YMD <= ? AND ") .append(" KK2541_GENE.RSV_APLY_CD = '2' AND ") .append(" KK2541_GENE.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK2541_GENE.KAP_KEI_NO ") .append(" ) ") .append(" ) ") //ANK-3236-00-00 ADD END // 配送情報取得（GHプレミアムサポート施策対象） .append(" UNION ALL ") .append(" SELECT KK3061.EOPRMCL_SISAK_CD ") .append(" , KK3061.SSKSSJK_KSITEI_CD ") //ANK-3236-00-00 MOD START // .append(" , DK0011.KKTK_SVC_KEI_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") // .append(" , DK0011.KIKI_CHG_NO AS CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , MIN(DK0011.KKTK_SVC_KEI_NO) AS CMP_ADD_OPTNTY_TG_KEY_1 ") .append(" , MIN(DK0011.KIKI_CHG_NO) AS CMP_ADD_OPTNTY_TG_KEY_2 ") //ANK-3236-00-00 MOD END .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") .append(" FROM ( ") .append(" SELECT DISTINCT ") .append(" DK0011_01.HAISO_NO ") .append(" , DK0021_01.KKTK_SVC_KEI_NO") .append(" , DK0021_01.KIKI_CHG_NO ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_01 ") .append(" INNER JOIN DK_T_HAISO DK0011_01 ") .append(" ON DK0011_01.HAISO_NO = DK0021_01.HAISO_NO ") .append(" WHERE (DK0021_01.KKTK_SVC_KEI_NO, DK0021_01.KIKI_CHG_NO) IN ( ") .append(" SELECT KK0341_01.KKTK_SVC_KEI_NO ") .append(" , KK0341_01.KIKI_CHG_NO ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01 ") .append(" WHERE KK0341_01.OYA_KEI_SKBT_CD IN ('01', '03', '04') ") .append(" AND KK0341_01.SVC_KEI_NO = ? ") .append(" AND KK0341_01.KKTK_SVC_KEI_STAT NOT IN ('910', '920') ") .append(" AND KK0341_01.TAKNKIKI_MODEL_CD IN ").append(ghTakCodeInStmt) .append(" AND (KK0341_01.KKTK_SVC_KEI_NO, KK0341_01.KIKI_CHG_NO, ") .append(" NVL(KK0341_01.RSV_APLY_YMD, '20991231') || KK0341_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_01_GENE.KKTK_SVC_KEI_NO, KK0341_01_GENE.KIKI_CHG_NO ") .append(" , MAX(NVL(KK0341_01_GENE.RSV_APLY_YMD, '20991231') ") .append(" || KK0341_01_GENE.GENE_ADD_DTM) AS KK0341_01_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01_GENE ") .append(" WHERE KK0341_01_GENE.KKTK_SVC_KEI_NO = KK0341_01.KKTK_SVC_KEI_NO ") .append(" AND KK0341_01_GENE.KIKI_CHG_NO = KK0341_01.KIKI_CHG_NO ") .append(" AND KK0341_01_GENE.RSV_APLY_CD IN ('1', '2') ") .append(" AND KK0341_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_01_GENE.KKTK_SVC_KEI_NO, KK0341_01_GENE.KIKI_CHG_NO) ") .append(" ) ") .append(" AND DK0021_01.MK_FLG = '0' ") .append(" AND DK0011_01.HISOS_KOCOMP_CD IS NULL ") .append(" AND DK0011_01.HAISO_STAT IN ('001', '002', '003', '004') ") .append(" AND NVL(DK0011_01.HAISO_FIN_YMD, '20991231') > ? ") .append(" AND DK0011_01.MK_FLG = '0' ") .append(" ) DK0011 ") .append(" INNER JOIN KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" ON KK3061.EOPRMCL_SSKSSJK_SBT_CD = '02' ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_1 IS NULL OR KK3061.TAKNKIKI_SBT_CD_1 IN ") .append(" (SELECT DK0021_01.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_01 ") .append(" WHERE DK0021_01.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_01.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_01.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_01.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_2 IS NULL OR KK3061.TAKNKIKI_SBT_CD_2 IN ") .append(" (SELECT DK0021_02.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_02 ") .append(" WHERE DK0021_02.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_02.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_02.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_02.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_3 IS NULL OR KK3061.TAKNKIKI_SBT_CD_3 IN ") .append(" (SELECT DK0021_03.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_03 ") .append(" WHERE DK0021_03.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_03.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_03.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_03.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_4 IS NULL OR KK3061.TAKNKIKI_SBT_CD_4 IN ") .append(" (SELECT DK0021_04.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_04 ") .append(" WHERE DK0021_04.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_04.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_04.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_04.MK_FLG = '0') ") .append(" ) ") .append(" AND (KK3061.TAKNKIKI_SBT_CD_5 IS NULL OR KK3061.TAKNKIKI_SBT_CD_5 IN ") .append(" (SELECT DK0021_05.TAKNKIKI_SBT_CD ") .append(" FROM DK_T_HAISO_TG_BPIN DK0021_05 ") .append(" WHERE DK0021_05.HAISO_NO = DK0011.HAISO_NO ") // OM-2021-0000558 ADD STRAT .append(" AND DK0021_05.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO ") .append(" AND DK0021_05.KIKI_CHG_NO = DK0011.KIKI_CHG_NO ") // OM-2021-0000558 ADD END .append(" AND DK0021_05.MK_FLG = '0') ") .append(" ) ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TSTAYMD <= ? ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TENDYMD >= ? ") .append(" AND KK3061.MK_FLG = '0' ") //ANK-3217-00-00 ADD END //ANK-3236-00-00 ADD START .append(" WHERE ") .append(" NOT EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0481.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.SVC_KEI_NO = ? ") .append(" AND KK0451.WRIB_SVC_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'X' THEN 'X' ") .append(ghSisakCpCaseStmt.toString()) .append(" ELSE NULL ") .append(" END ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO, MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, ") .append(" KK0481_GENE.WRIB_SVC_TRGT_KEI_NO ") .append(" ) ") .append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRIB_SVC_KEI KK0451_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO ") .append(" ) ") .append(" ) AND ") .append(" EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341_03 ") .append(" INNER JOIN ") .append(" KK_T_KAP_KEI KK2541 ") .append(" ON ") .append(" KK0341_03.KKTK_SVC_KEI_NO = KK2541.KKTK_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0341_03.OYA_KEI_SKBT_CD IN ('01', '03', '04') AND ") .append(" KK0341_03.SVC_KEI_NO = ? AND ") .append(" KK0341_03.KKTK_SVC_KEI_NO = DK0011.KKTK_SVC_KEI_NO AND ") .append(" KK0341_03.KIKI_CHG_NO = DK0011.KIKI_CHG_NO AND ") .append(" KK0341_03.KKTK_SVC_KEI_STAT NOT IN ('910', '920') AND ") .append(" KK0341_03.TAKNKIKI_MODEL_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'X' THEN 'X' ") .append(ghSisakModelCaseStmt.toString()) .append(" ELSE NULL ") .append(" END AND ") // ANK-3636-00-00 MOD START // .append(" KK2541.KAP_PLAN_CD = ") .append(" (KK2541.KAP_PLAN_CD = ") // ANK-3636-00-00 MOD END .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B002' THEN 'KP0035' ") .append(" WHEN 'B004' THEN 'KP0034' ") .append(" WHEN 'B006' THEN 'KP0036' ") .append(" WHEN 'B008' THEN 'KP0037' ") .append(" ELSE NULL ") // ANK-3636-00-00 ADD START .append(" END OR ") .append(" KK2541.KAP_PLAN_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B002' THEN 'KP0135' ") .append(" WHEN 'B004' THEN 'KP0134' ") .append(" WHEN 'B006' THEN 'KP0136' ") .append(" WHEN 'B008' THEN 'KP0137' ") .append(" ELSE NULL ") .append(" END OR ") .append(" KK2541.KAP_PLAN_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD WHEN 'B002' THEN 'KP1135' ") .append(" WHEN 'B004' THEN 'KP1134' ") .append(" WHEN 'B006' THEN 'KP1136' ") .append(" WHEN 'B008' THEN 'KP1137' ") .append(" ELSE NULL ") // ANK-3636-00-00 ADD END // ANK-3636-00-00 MOD START // .append(" END AND ") .append(" END) AND ") // ANK-3636-00-00 MOD END .append(" (KK0341_03.KKTK_SVC_KEI_NO, KK0341_03.KIKI_CHG_NO, NVL(KK0341_03.RSV_APLY_YMD, '20991231') || KK0341_03.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO, ") .append(" KK0341_03_GENE.KIKI_CHG_NO , ") .append(" MAX(NVL(KK0341_03_GENE.RSV_APLY_YMD, '20991231') || KK0341_03_GENE.GENE_ADD_DTM) AS KK0341_03_GENE_MAX ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341_03_GENE ") .append(" WHERE ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO = KK0341_03.KKTK_SVC_KEI_NO AND ") .append(" KK0341_03_GENE.KIKI_CHG_NO = KK0341_03.KIKI_CHG_NO AND ") .append(" KK0341_03_GENE.RSV_APLY_CD IN ('1', '2') AND ") .append(" KK0341_03_GENE.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0341_03_GENE.KKTK_SVC_KEI_NO, ") .append(" KK0341_03_GENE.KIKI_CHG_NO ") .append(" ) AND ") .append(" (KK2541.KAP_KEI_NO, KK2541.RSV_APLY_YMD || KK2541.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK2541_GENE.KAP_KEI_NO, ") .append(" MAX(KK2541_GENE.RSV_APLY_YMD || KK2541_GENE.GENE_ADD_DTM) AS KK2541_GENE_MAX ") .append(" FROM ") .append(" KK_T_KAP_KEI KK2541_GENE ") .append(" WHERE ") .append(" KK2541_GENE.KAP_KEI_NO = KK2541.KAP_KEI_NO AND ") .append(" KK2541_GENE.RSV_APLY_YMD <= ? AND ") .append(" KK2541_GENE.RSV_APLY_CD = '2' AND ") .append(" KK2541_GENE.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK2541_GENE.KAP_KEI_NO ") .append(" ) ") .append(" ) ") .append(" GROUP BY ") .append(" KK3061.EOPRMCL_SISAK_CD, ") .append(" KK3061.SSKSSJK_KSITEI_CD ") //ANK-3236-00-00 ADD END .append(" UNION ALL ") .append(" SELECT KK3061.EOPRMCL_SISAK_CD ") // ST-2016-0000030 ADD START .append(" , KK3061.SSKSSJK_KSITEI_CD ") // ST-2016-0000030 ADD END .append(" , KK1681.IDO_RSV_NO AS CMP_ADD_OPTNTY_TG_KEY_1 ") // 2016/07/25 IT2-2016-0000040 ADD START .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_2 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_3 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_4 ") .append(" , NULL AS CMP_ADD_OPTNTY_TG_KEY_5 ") // 2016/07/25 IT2-2016-0000040 ADD END .append(" FROM ( ") .append(" SELECT KK1681_01.IDO_RSV_NO ") .append(" , KK1681_01.IDO_DIV ") .append(" , KK1681_01.IDO_RSV_DTL_CD ") .append(" , KK1681_01.NEW_PRC_GRP_CD ") .append(" , KK1681_01.NEW_PCRS_CD ") .append(" , KK1681_01.NEW_PPLAN_CD ") .append(" , KK1681_01.OLD_PRC_GRP_CD ") .append(" , KK1681_01.OLD_PCRS_CD ") .append(" , KK1681_01.OLD_PPLAN_CD ") // 2016/07/25 IT2-2016-0000041 ADD START .append(" , NULL AS TAKNKIKI_SBT_CD ") // 2016/07/25 IT2-2016-0000041 ADD END .append(" FROM KK_T_IDO_RSV KK1681_01 ") .append(" WHERE KK1681_01.SVC_KEI_NO = ? ") .append(" AND KK1681_01.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" AND KK1681_01.MK_FLG = '0' ") .append(" UNION ALL ") .append(" SELECT KK1681_02.IDO_RSV_NO ") .append(" , KK1681_02.IDO_DIV ") .append(" , KK1681_02.IDO_RSV_DTL_CD ") .append(" , KK1681_02.NEW_PRC_GRP_CD ") .append(" , KK1681_02.NEW_PCRS_CD ") .append(" , KK1681_02.NEW_PPLAN_CD ") .append(" , KK1681_02.OLD_PRC_GRP_CD ") .append(" , KK1681_02.OLD_PCRS_CD ") .append(" , KK1681_02.OLD_PPLAN_CD ") // 2016/07/25 IT2-2016-0000041 ADD START .append(" , KK0341.TAKNKIKI_SBT_CD ") // 2016/07/25 IT2-2016-0000041 ADD END .append(" FROM KK_T_IDO_RSV KK1681_02 ") // 2016/07/25 IT2-2016-0000041 MOD START // .append(" WHERE KK1681_02.KKTK_SVC_KEI_NO IN ( ") .append(" INNER JOIN ( ") // 2016/07/25 IT2-2016-0000041 MOD END .append(" SELECT KK0341_01.KKTK_SVC_KEI_NO ") // 2016/07/25 IT2-2016-0000041 ADD START .append(" , KK0341_01.TAKNKIKI_SBT_CD ") // 2016/07/25 IT2-2016-0000041 ADD END .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01 ") .append(" WHERE KK0341_01.OYA_KEI_SKBT_CD IN ('01', '03', '04') ") .append(" AND KK0341_01.SVC_KEI_NO = ? ") .append(" AND (KK0341_01.KKTK_SVC_KEI_NO, KK0341_01.RSV_APLY_YMD || KK0341_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_01_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_01_GENE.RSV_APLY_YMD || KK0341_01_GENE.GENE_ADD_DTM) AS KK0341_01_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01_GENE ") .append(" WHERE KK0341_01_GENE.KKTK_SVC_KEI_NO = KK0341_01.KKTK_SVC_KEI_NO ") .append(" AND KK0341_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0341_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_01_GENE.KKTK_SVC_KEI_NO) ") .append(" UNION ") .append(" SELECT KK0341_02.KKTK_SVC_KEI_NO ") // 2016/07/25 IT2-2016-0000041 ADD START .append(" , KK0341_02.TAKNKIKI_SBT_CD ") // 2016/07/25 IT2-2016-0000041 ADD END .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02 ") .append(" WHERE KK0341_02.OYA_KEI_SKBT_CD = '02' ") .append(" AND KK0341_02.SVC_KEI_KAISEN_UCWK_NO IN ") .append(" (SELECT KK0241_02.SVC_KEI_KAISEN_UCWK_NO ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_02 ") .append(" WHERE KK0241_02.SVC_KEI_NO = ? ") .append(" AND KK0241_02.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_02.KAISEN_UCWK_USE_ENDYMD >= ? ") .append(" AND KK0241_02.MK_FLG = '0') ") .append(" AND (KK0341_02.KKTK_SVC_KEI_NO, KK0341_02.RSV_APLY_YMD || KK0341_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_02_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_02_GENE.RSV_APLY_YMD || KK0341_02_GENE.GENE_ADD_DTM) AS KK0341_02_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02_GENE ") .append(" WHERE KK0341_02_GENE.KKTK_SVC_KEI_NO = KK0341_02.KKTK_SVC_KEI_NO ") .append(" AND KK0341_02_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0341_02_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_02_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_02_GENE.KKTK_SVC_KEI_NO) ") // 2016/07/25 IT2-2016-0000041 MOD START // .append(" ) ") // .append(" AND KK1681_02.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" ) KK0341 ON ") .append(" KK0341.KKTK_SVC_KEI_NO = KK1681_02.KKTK_SVC_KEI_NO ") .append(" WHERE ") .append(" KK1681_02.IDO_RSV_STAT_CD IN ('00', '03') ") // 2016/07/25 IT2-2016-0000041 MOD END .append(" AND KK1681_02.MK_FLG = '0' ") .append(" ) KK1681 ") .append(" INNER JOIN KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" ON KK3061.EOPRMCL_SSKSSJK_SBT_CD = '03' ") .append(" AND KK3061.IDO_DIV = KK1681.IDO_DIV ") .append(" AND KK3061.IDO_RSV_DTL_CD = KK1681.IDO_RSV_DTL_CD ") .append(" AND (KK3061.NEW_PRC_GRP_CD IS NULL OR KK3061.NEW_PRC_GRP_CD = KK1681.NEW_PRC_GRP_CD) ") .append(" AND (KK3061.NEW_PCRS_CD IS NULL OR KK3061.NEW_PCRS_CD = KK1681.NEW_PCRS_CD) ") .append(" AND (KK3061.NEW_PPLAN_CD IS NULL OR KK3061.NEW_PPLAN_CD = KK1681.NEW_PPLAN_CD) ") .append(" AND (KK3061.OLD_PRC_GRP_CD IS NULL OR KK3061.OLD_PRC_GRP_CD = KK1681.OLD_PRC_GRP_CD) ") .append(" AND (KK3061.OLD_PCRS_CD IS NULL OR KK3061.OLD_PCRS_CD = KK1681.OLD_PCRS_CD) ") .append(" AND (KK3061.OLD_PPLAN_CD IS NULL OR KK3061.OLD_PPLAN_CD = KK1681.OLD_PPLAN_CD) ") // 2016/07/25 IT2-2016-0000041 ADD START .append(" AND (KK3061.TAKNKIKI_SBT_CD_1 IS NULL OR KK3061.TAKNKIKI_SBT_CD_1 = KK1681.TAKNKIKI_SBT_CD) ") // 2016/07/25 IT2-2016-0000041 ADD END .append(" AND KK3061.EOPRMCL_SSKSSJK_TSTAYMD <= ? ") .append(" AND KK3061.EOPRMCL_SSKSSJK_TENDYMD >= ? ") .append(" AND KK3061.MK_FLG = '0' ") // ANK-3288-00-00 ADD START // 登録時対象取得 .append(" UNION ALL ") .append(" SELECT ") .append(" KK3061.EOPRMCL_SISAK_CD ") .append(" ,KK3061.SSKSSJK_KSITEI_CD ") .append(" ,NULL ") .append(" ,NULL ") .append(" ,NULL ") .append(" ,NULL ") .append(" ,NULL ") .append(" FROM ") .append(" KK_M_EOPRMCL_SSKSSJK KK3061 ") .append(" WHERE ") .append(" KK3061.EOPRMCL_SSKSSJK_SBT_CD = '00' ") .append(" AND KK3061.EOPRMCL_SSKSSJK_NO IN ( ") .append(" (SELECT ") .append(" '0000000043' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '03' ") .append(" AND EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" WHERE ") .append(" KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0351.OP_SVC_CD = 'B068' ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE ") .append(" KK0351_GENE.OP_SVC_KEI_NO = KK0351.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 ") .append(" KK0351_GENE.OP_SVC_KEI_NO) ") .append(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE ") .append(" KK0081_GENE.SVC_KEI_NO = KK0081.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 ") .append(" KK0081_GENE.SVC_KEI_NO)) ") //ANK-3303-00-00 ADD START .append(" ,(SELECT ") .append(" '0000000044' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" ON ") .append(" KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND KK0451.WRIB_SVC_CD = 'W00000010' ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (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_GENE_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_GENE_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(" ) ") //#83584 MOD START //.append(" ,(SELECT ") .append(" ,(SELECT DISTINCT ") //#83584 MOD END .append(" '0000000045' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_KAISEN_TG_SVKEI KK0241 ") .append(" ON ") .append(" KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" ON ") .append(" KK0341.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ") .append(" INNER JOIN ") .append(" KK_T_KKOP_SVC_KEI KK2811 ") .append(" ON ") .append(" KK2811.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = ") .append(" ( SELECT KK0241_GENE.SVC_KEI_NO, MAX(KK0241_GENE.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_GENE ") .append(" WHERE KK0241_GENE.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" AND KK0241_GENE.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0241_GENE.SVC_KEI_NO ") .append(" ) ") .append(" AND KK0081.SVC_CD = '01' ") //ANK-4315-00-00 MOD START //.append(" AND KK0341.TAKNKIKI_SBT_CD IN = 'R0' ") .append(" AND KK0341.TAKNKIKI_SBT_CD IN ('R0', 'S0') ") //ANK-4315-00-00 MOD END .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK2811.PCRS_CD = 'G02' ") .append(" AND KK2811.KKOP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND NOT EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0451.WRIB_SVC_CD = 'W00000006' ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (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_GENE_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_GENE_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(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" AND (KK2811.KKOP_SVC_KEI_NO, KK2811.RSV_APLY_YMD || KK2811.GENE_ADD_DTM) = ") .append(" (SELECT KK2811_GENE.KKOP_SVC_KEI_NO, MAX(KK2811_GENE.RSV_APLY_YMD || KK2811_GENE.GENE_ADD_DTM) AS KK2811_GENE_MAX ") .append(" FROM KK_T_KKOP_SVC_KEI KK2811_GENE ") .append(" WHERE KK2811_GENE.KKOP_SVC_KEI_NO = KK2811.KKOP_SVC_KEI_NO ") .append(" AND KK2811_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK2811_GENE.RSV_APLY_CD = '2' ") .append(" AND KK2811_GENE.MK_FLG = '0' ") .append(" GROUP BY KK2811_GENE.KKOP_SVC_KEI_NO) ") .append(" ) ") //ANK-3303-00-00 ADD END // ANK-3754-00-00 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000077' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_SVC_KEI_UCWK KK0161 ") .append(" ON ") .append(" KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" ON ") .append(" KK0481.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '02' ") .append(" AND KK0451.WRIB_SVC_CD = 'W00000023' ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" 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_GENE_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(" AND (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_GENE_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_GENE_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(" ) ") // ANK-3754-00-00 ADD END // ANK-3529-00-00 ADD START // OM-2019-0000407 MOD START // .append(" ,(SELECT ") .append(" ,(SELECT DISTINCT ") // OM-2019-0000407 MOD END .append(" '0000000046' ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO = ? ") .append(" AND KK0341.PPLAN_CD = 'PCA101' ") .append(" AND KK0341.TAKNKIKI_SBT_CD = 'G0' ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" ) ") // ANK-3529-00-00 ADD END // ANK-3825-00-00 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000078' ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO = ? ") .append(" AND KK0341.PPLAN_CD = 'PCA201' ") .append(" AND KK0341.TAKNKIKI_SBT_CD = 'G0' ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" ) ") // ANK-3825-00-00 ADD END // ANK-3918-00-00 ADD START .append(" ,(SELECT ") .append(" '0000000079' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND EXISTS ( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" WHERE ") .append(" KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0351.OP_SVC_CD = 'B133' ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0351_GENE.OP_SVC_KEI_NO, MAX( KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE ") .append(" KK0351_GENE.OP_SVC_KEI_NO = KK0351.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 ") .append(" KK0351_GENE.OP_SVC_KEI_NO) ") .append(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0081_GENE.SVC_KEI_NO ") .append(" , MAX( ") .append(" KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM ") .append(" ) AS KK0081_GENE_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE ") .append(" KK0081_GENE.SVC_KEI_NO = KK0081.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 ") .append(" KK0081_GENE.SVC_KEI_NO) ") .append(" ) ") .append(" ,(SELECT ") .append(" '0000000080' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND EXISTS ( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" WHERE ") .append(" KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0351.OP_SVC_CD = 'B134' ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0351_GENE.OP_SVC_KEI_NO, MAX( KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE ") .append(" KK0351_GENE.OP_SVC_KEI_NO = KK0351.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 ") .append(" KK0351_GENE.OP_SVC_KEI_NO) ") .append(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0081_GENE.SVC_KEI_NO ") .append(" , MAX( ") .append(" KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM ") .append(" ) AS KK0081_GENE_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE ") .append(" KK0081_GENE.SVC_KEI_NO = KK0081.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 ") .append(" KK0081_GENE.SVC_KEI_NO) ") .append(" ) ") .append(" ,(SELECT ") .append(" '0000000081' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND EXISTS ( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" WHERE ") .append(" KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0351.OP_SVC_CD = 'B131' ") .append(" AND KK0351.SVC_KEI_NO = ? ") .append(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0351_GENE.OP_SVC_KEI_NO, MAX( KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE ") .append(" KK0351_GENE.OP_SVC_KEI_NO = KK0351.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 ") .append(" KK0351_GENE.OP_SVC_KEI_NO) ") .append(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0081_GENE.SVC_KEI_NO ") .append(" , MAX( ") .append(" KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM ") .append(" ) AS KK0081_GENE_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE ") .append(" KK0081_GENE.SVC_KEI_NO = KK0081.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 ") .append(" KK0081_GENE.SVC_KEI_NO) ") .append(" ) ") // ANK-3918-00-00 ADD END // ANK-3949-00-00 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000082' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" LEFT OUTER JOIN ") .append(" KK_T_IDO_RSV KK1681 ") .append(" ON ") .append(" KK0081.SVC_KEI_NO = KK1681.SVC_KEI_NO ") // ANK-3987-00-00 MOD START // .append(" AND KK1681.IDO_DIV = '00009' ") .append(" AND KK1681.IDO_DIV IN ( '00009','00019' ) ") // ANK-3987-00-00 MOD END .append(" AND KK1681.IDO_RSV_DTL_CD = '003' ") .append(" AND KK1681.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" AND KK1681.NEW_PCRS_CD IN (SELECT KK0791.PCRS_CD ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE TAJGS_BUNDLE_DIV = '001' ") .append(" AND PCRS_TSTAYMD <= ? ") .append(" AND PCRS_TENDYMD >= ? ") .append(" AND(KK0791.PCRS_CD, KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_GENE.PCRS_CD, MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.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(" AND KK1681.OLD_PCRS_CD NOT IN (SELECT KK0791.PCRS_CD ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE TAJGS_BUNDLE_DIV = '001' ") .append(" AND PCRS_TSTAYMD <= ? ") .append(" AND PCRS_TENDYMD >= ? ") .append(" AND(KK0791.PCRS_CD, KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_GENE.PCRS_CD, MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.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(" AND KK1681.MK_FLG = '0' ") .append(" WHERE KK0081.SVC_CD = '01' ") .append(" AND KK0081.SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK0081.SVC_KEI_NO = ? ") .append(" AND ( ") .append(" KK0081.PCRS_CD IN (SELECT KK0791.PCRS_CD ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE TAJGS_BUNDLE_DIV = '001' ") .append(" AND PCRS_TSTAYMD <= ? ") .append(" AND PCRS_TENDYMD >= ? ") .append(" AND(KK0791.PCRS_CD, KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_GENE.PCRS_CD, MAX(KK0791_GENE.RSV_APLY_YMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.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(" OR ") .append(" KK1681.IDO_RSV_NO IS NOT NULL ") .append(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE ") .append(" KK0081_GENE.SVC_KEI_NO = KK0081.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 ") .append(" KK0081_GENE.SVC_KEI_NO) ") .append(" ) ") // ANK-3949-00-00 ADD END // ANK-3987-00-00 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000143' ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO = ? ") // IT1-2021-0000010 MOD START // .append(" AND KK0341.PPLAN_CD = 'PC9001' ") .append(" AND KK0341.PPLAN_CD = 'PCA301' ") // IT1-2021-0000010 MOD END .append(" AND KK0341.TAKNKIKI_SBT_CD = 'Q0' ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND ( KK0341.SVC_CHRG_STAYMD >= ? OR KK0341.SVC_CHRG_STAYMD IS NULL ) ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" ) ") .append(" ,(SELECT DISTINCT ") .append(" '0000000144' ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO = ? ") // IT1-2021-0000010 MOD START // .append(" AND KK0341.PPLAN_CD = 'PC9001' ") .append(" AND KK0341.PPLAN_CD = 'PCA301' ") // IT1-2021-0000010 MOD END .append(" AND KK0341.TAKNKIKI_SBT_CD = 'Q0' ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND ( KK0341.SVC_CHRG_STAYMD >= ? OR KK0341.SVC_CHRG_STAYMD IS NULL ) ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" ) ") // ANK-3987-00-00 ADD END //ANK-4094-00-00 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000145' ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.SVC_KEI_NO = ? ") .append(" AND KK0341.PPLAN_CD = 'PCA301' ") .append(" AND KK0341.TAKNKIKI_SBT_CD = 'Q0' ") .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND ( KK0341.SVC_CHRG_STAYMD >= ? OR KK0341.SVC_CHRG_STAYMD IS NULL ) ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" ) ") // ANK-4094-00-00 ADD END // ANK-4640-00-00 20250219 張 ADD START .append(" ,(SELECT DISTINCT ") .append(" '0000000182' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" ON ") .append(" KK0351.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_SBOP_SVC_KEI KK0401 ") .append(" ON ") .append(" KK0401.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND KK0351.OP_SVC_CD = 'B138' ") .append(" AND KK0401.SBOP_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND KK0401.SBOP_SVC_CD = 'D14' ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351.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(" AND (KK0401.OP_SVC_KEI_NO, KK0401.SBOP_SVC_KEI_NO, KK0401.RSV_APLY_YMD || KK0401.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_GENE_MAX ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_GENE ") .append(" WHERE KK0401_GENE.OP_SVC_KEI_NO = KK0401.OP_SVC_KEI_NO ") .append(" AND KK0401_GENE.SBOP_SVC_KEI_NO = KK0401.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(" ) ") .append(" ,(SELECT DISTINCT ") .append(" '0000000183' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" ON ") .append(" KK0351.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_SBOP_SVC_KEI KK0401 ") .append(" ON ") .append(" KK0401.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND KK0351.OP_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND KK0351.OP_SVC_CD = 'B138' ") .append(" AND KK0401.SBOP_SVC_KEI_STAT IN ('010', '020', '030', '100', '210') ") .append(" AND KK0401.SBOP_SVC_CD = 'D15' ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX ") .append(" FROM KK_T_OP_SVC_KEI KK0351_GENE ") .append(" WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351.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(" AND (KK0401.OP_SVC_KEI_NO, KK0401.SBOP_SVC_KEI_NO, KK0401.RSV_APLY_YMD || KK0401.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_GENE_MAX ") .append(" FROM KK_T_SBOP_SVC_KEI KK0401_GENE ") .append(" WHERE KK0401_GENE.OP_SVC_KEI_NO = KK0401.OP_SVC_KEI_NO ") .append(" AND KK0401_GENE.SBOP_SVC_KEI_NO = KK0401.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(" ) ") // ANK-4640-00-00 20250219 張 ADD END // ANK-4307-00-00 ADD START //#83584 MOD START //.append(" ,(SELECT ") .append(" ,(SELECT DISTINCT ") //#83584 MOD END .append(" '0000000146' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_KAISEN_TG_SVKEI KK0241 ") .append(" ON ") .append(" KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" ON ") .append(" KK0341.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ") .append(" INNER JOIN ") .append(" KK_T_KKOP_SVC_KEI KK2811 ") .append(" ON ") .append(" KK2811.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = ") .append(" ( SELECT KK0241_GENE.SVC_KEI_NO, MAX(KK0241_GENE.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_GENE ") .append(" WHERE KK0241_GENE.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" AND KK0241_GENE.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0241_GENE.SVC_KEI_NO ") .append(" ) ") .append(" AND KK0081.SVC_CD = '01' ") //ANK-4315-00-00 MOD START //.append(" AND KK0341.TAKNKIKI_SBT_CD = 'R0' ") .append(" AND KK0341.TAKNKIKI_SBT_CD IN ('R0', 'S0') ") //ANK-4315-00-00 MOD END .append(" AND KK0341.KKTK_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND KK2811.PCRS_CD = 'G02' ") .append(" AND KK2811.KKOP_SVC_KEI_STAT IN ('010', '020', '030', '100') ") .append(" AND NOT EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" AND KK0451.WRIB_SVC_CD = 'W00000006' ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (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_GENE_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_GENE_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(" ) ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_GENE_MAX ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.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(" AND (KK2811.KKOP_SVC_KEI_NO, KK2811.RSV_APLY_YMD || KK2811.GENE_ADD_DTM) = ") .append(" (SELECT KK2811_GENE.KKOP_SVC_KEI_NO, MAX(KK2811_GENE.RSV_APLY_YMD || KK2811_GENE.GENE_ADD_DTM) AS KK2811_GENE_MAX ") .append(" FROM KK_T_KKOP_SVC_KEI KK2811_GENE ") .append(" WHERE KK2811_GENE.KKOP_SVC_KEI_NO = KK2811.KKOP_SVC_KEI_NO ") .append(" AND KK2811_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK2811_GENE.RSV_APLY_CD = '2' ") .append(" AND KK2811_GENE.MK_FLG = '0' ") .append(" GROUP BY KK2811_GENE.KKOP_SVC_KEI_NO) ") .append(" ) ") .append(" ,(SELECT ") .append(" '0000000147' ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081 ") .append(" INNER JOIN ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" ON ") .append(" KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0081.SVC_KEI_NO = ? ") .append(" AND KK0081.SVC_CD = '01' ") .append(" AND KK0451.WRIB_SVC_CD = 'W00000010' ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") .append(" AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_GENE_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_GENE ") .append(" WHERE KK0081_GENE.SVC_KEI_NO = KK0081.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(" AND (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_GENE_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_GENE_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(" ) ") // ANK-4307-00-00 ADD END .append(" ) ") .append(" AND NOT EXISTS( ") .append(" SELECT ") .append(" 1 ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481 ") .append(" INNER JOIN ") .append(" KK_T_WRIB_SVC_KEI KK0451 ") .append(" ON ") .append(" KK0481.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO ") .append(" WHERE ") .append(" KK0481.SVC_KEI_NO = ? ") .append(" AND KK0451.WRIB_SVC_KEI_STAT IN ('010', '100') ") //ANK-3987-00-00 MOD START // .append(" AND KK0451.WRIB_SVC_CD = ") .append(" AND ( KK0451.WRIB_SVC_CD = ") //ANK-3987-00-00 MOD END .append(" CASE KK3061.EOPRMCL_SISAK_CD ") .append(" WHEN 'X' THEN 'X' ") .append(sskCpCaseStmt.toString()) .append(" ELSE NULL ") .append(" END ") //ANK-3987-00-00 ADD START .append(" OR KK0451.WRIB_SVC_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD ") .append(" WHEN 'B010' THEN 'W10000191' ") .append(" WHEN 'B011' THEN 'W10000192' ") //ANK-4094-00-00 ADD START .append(" WHEN 'B012' THEN 'W10000191' ") //ANK-4094-00-00 ADD END .append(" ELSE NULL ") //ANK-4094-00-00 MOD START //.append(" END ) ") .append(" END ") //ANK-4094-00-00 MOD END //ANK-3987-00-00 ADD END //ANK-4094-00-00 ADD START .append(" OR KK0451.WRIB_SVC_CD = ") .append(" CASE KK3061.EOPRMCL_SISAK_CD ") .append(" WHEN 'B010' THEN 'W10000194' ") .append(" WHEN 'B011' THEN 'W10000194' ") .append(" WHEN 'B012' THEN 'W10000192' ") .append(" ELSE NULL ") .append(" END ) ") //ANK-4094-00-00 ADD END .append(" AND (KK0451.WRIB_SVC_KEI_NO, KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO, MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRIB_SVC_KEI KK0451_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0451_GENE.WRIB_SVC_KEI_NO) ") .append(" AND (KK0481.WRIB_SVC_KEI_NO, KK0481.WRIB_SVC_TRGT_KEI_NO, KK0481.GENE_ADD_DTM) = ") .append(" (SELECT ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO, MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_GENE_MAX ") .append(" FROM ") .append(" KK_T_WRISVC_TG_KEI KK0481_GENE ") .append(" WHERE ") .append(" 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 ") .append(" KK0481_GENE.WRIB_SVC_KEI_NO, KK0481_GENE.WRIB_SVC_TRGT_KEI_NO) ") .append(" ) ") // ANK-3288-00-00 ADD END .append(" ) KK3061_00 ") .append(" INNER JOIN KK_M_EOPRMCL_SISAK KK3051 ") .append(" ON KK3051.EOPRMCL_SISAK_CD = KK3061_00.EOPRMCL_SISAK_CD ") .append(" AND KK3051.EOPRMCL_SISAK_TSTAYMD <= ? ") .append(" AND KK3051.EOPRMCL_SISAK_TENDYMD >= ? ") .append(" AND (KK3051.EOPRMCL_SISAK_CD, KK3051.RSV_APLY_YMD || KK3051.GENE_ADD_DTM) = ") .append(" (SELECT KK3051_GENE.EOPRMCL_SISAK_CD, MAX(KK3051_GENE.RSV_APLY_YMD || KK3051_GENE.GENE_ADD_DTM) AS KK3051_GENE_MAX ") .append(" FROM KK_M_EOPRMCL_SISAK KK3051_GENE ") .append(" WHERE KK3051_GENE.EOPRMCL_SISAK_CD = KK3051.EOPRMCL_SISAK_CD ") .append(" AND KK3051_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK3051_GENE.MK_FLG = '0' ") .append(" GROUP BY KK3051_GENE.EOPRMCL_SISAK_CD) "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK3051B001CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" ORDER BY KK3051.EOPRMCL_SISAK_CD ASC ") .append(" , KK3051.CMP_ADD_OPTNTY_CD ASC ") .append(" , KK3061_00.CMP_ADD_OPTNTY_TG_KEY_1 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(EKK3051B001CBSMsg.FUNC_CODE) ) ) { // 運用日付を取得 String opeDate = JKKModelCommon.getOpeDate(inMsg); //ANK-3987-00-00 ADD START //運用日の月初を取得 String OpeYm = opeDate.substring(0, 6); String OpeYmdSta = OpeYm + "01"; //ANK-3987-00-00 ADD END // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 2, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 3, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 4, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 5, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 6, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 7, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 8, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 9, opeDate); // パラメータの設定(運用日付－10日を指定) CAANJDBCUtil.setParam(pstmt, 10, JKKModelCommon.addDay(inMsg, inContext, opeDate, "-10")); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 11, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 12, opeDate); //ANK-3217-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 13, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 14, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 15, opeDate); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 16, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 17, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 18, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 19, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 20, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 21, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 22, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 23, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 24, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 13, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 14, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 15, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 16, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 17, opeDate); //ANK-3236-00-00 ADD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 18, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 19, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 20, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 21, opeDate); //ANK-3236-00-00 ADD END //ANK-3236-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 18, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付－10日を指定) // CAANJDBCUtil.setParam(pstmt, 19, JKKModelCommon.addDay(inMsg, inContext, opeDate, "-10")); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 20, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 21, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 22, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付－10日を指定) CAANJDBCUtil.setParam(pstmt, 23, JKKModelCommon.addDay(inMsg, inContext, opeDate, "-10")); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 24, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 25, opeDate); //ANK-3236-00-00 MOD END //ANK-3236-00-00 ADD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 26, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 27, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 28, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 29, opeDate); //ANK-3236-00-00 ADD END //ANK-3236-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 22, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 23, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 24, opeDate); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 25, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 26, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 27, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 28, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 29, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 30, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 31, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 32, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 33, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 30, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 31, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 32, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 33, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 34, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 35, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 36, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 37, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 38, opeDate); // ANK-3288-00-00 MOD START // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 39, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 40, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 41, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 39, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 40, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 41, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 42, opeDate); //ANK-3303-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 43, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 44, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 45, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 46, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 47, opeDate); //// ANK-3288-00-00 MOD END // パラメータの設定(ＫＥＹ＿サービス契約番号を設定) CAANJDBCUtil.setParam(pstmt, 43, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 44, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 45, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を設定) CAANJDBCUtil.setParam(pstmt, 46, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 47, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 48, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 49, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 50, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 51, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 52, opeDate); // ANK-3529-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 53, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 54, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 55, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 56, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 57, opeDate); ////ANK-3303-00-00 MOD END // ANK-3754-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 53, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 54, opeDate); // ANK-3825-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 55, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 56, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 57, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 58, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 59, opeDate); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 55, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 56, opeDate); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 57, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 58, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 59, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 60, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 61, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 53, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 54, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 55, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 56, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 57, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 58, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 59, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 60, opeDate); // ANK-3918-00-00 ADD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 61, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 62, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 63, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 64, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 65, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 66, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 67, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 68, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 69, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 70, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 71, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 72, opeDate); // ANK-3918-00-00 ADD END // ANK-3949-00-00 ADD START // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 73, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 74, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 75, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 76, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 77, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 78, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 79, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 80, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 81, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 82, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 83, opeDate); // ANK-3949-00-00 ADD END // ANK-3987-00-00 ADD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 84, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 85, OpeYmdSta); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 86, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 87, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 88, OpeYmdSta); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 89, opeDate); // ANK-3987-00-00 ADD END // ANK-4094-00-00 ADD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 90, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 91, OpeYmdSta); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 92, opeDate); // ANK-4094-00-00 ADD END // ANK-4640-00-00 20250219 張 ADD START // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 93, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 94, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 95, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 96, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 97, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 98, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 99, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 100, opeDate); // ANK-4640-00-00 20250219 張 ADD END // ANK-4640-00-00 20250219 張 MOD START //// ANK-4307-00-00 ADD START // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 93, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 94, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 95, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 96, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 97, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 98, opeDate); // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 99, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 100, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 101, opeDate); // // パラメータの設定(運用日付を設定) // CAANJDBCUtil.setParam(pstmt, 102, opeDate); //// ANK-4307-00-00 ADD END // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 101, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 102, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 103, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 104, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 105, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 106, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 107, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 108, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 109, opeDate); // パラメータの設定(運用日付を設定) CAANJDBCUtil.setParam(pstmt, 110, opeDate); // ANK-4640-00-00 20250219 張 MOD END // ANK-3918-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 61, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 62, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 63, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 64, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 65, opeDate); // ANK-3949-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 73, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 74, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 75, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 76, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 77, opeDate); // ANK-3987-00-00 MOD START // // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) // CAANJDBCUtil.setParam(pstmt, 84, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 85, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 86, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 87, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 88, opeDate); // ANK-4094-00-00 MOD START // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) //CAANJDBCUtil.setParam(pstmt, 90, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) //CAANJDBCUtil.setParam(pstmt, 91, opeDate); // パラメータの設定(運用日付を指定) //CAANJDBCUtil.setParam(pstmt, 92, opeDate); // パラメータの設定(運用日付を指定) //CAANJDBCUtil.setParam(pstmt, 93, opeDate); // パラメータの設定(運用日付を指定) //CAANJDBCUtil.setParam(pstmt, 94, opeDate); // ANK-4307-00-00 MOD START // CAANJDBCUtil.setParam(pstmt, 93, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 94, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 95, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 96, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 97, opeDate); // ANK-4640-00-00 20250219 張 MOD START // CAANJDBCUtil.setParam(pstmt, 103, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 104, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 105, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 106, opeDate); // // パラメータの設定(運用日付を指定) // CAANJDBCUtil.setParam(pstmt, 107, opeDate); CAANJDBCUtil.setParam(pstmt, 111, inMsg.getObject(EKK3051B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 112, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 113, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 114, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 115, opeDate); // ANK-4640-00-00 20250219 張 MOD END // ANK-4307-00-00 MOD END // ANK-4094-00-00 MOD END // ANK-3987-00-00 MOD END // ANK-3949-00-00 MOD END // ANK-3918-00-00 MOD END // ANK-3825-00-00 MOD END // ANK-3754-00-00 MOD END // ANK-3529-00-00 MOD END } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK3051B001CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK3051B001CBSMsg1List.EOPRMCL_SISAK_CD , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_CD // ST-2016-0000030 ADD START , EKK3051B001CBSMsg1List.SSKSSJK_KSITEI_CD // ST-2016-0000030 ADD END , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_TG_KEY_1 , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_TG_KEY_2 , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_TG_KEY_3 , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_TG_KEY_4 , EKK3051B001CBSMsg1List.CMP_ADD_OPTNTY_TG_KEY_5 }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK3051B001CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK3051B001CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK3051B001CBSMsg.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(EKK3051B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }