/*******************************************************************************
*	 All Rights reserved,Copyright (c) K-Opticom 
********************************************************************************
*＜プログラム内容＞
*	システム名		：eo顧客基幹システム
*	モジュール名	：JSYejbEKK0191A010TPDA
*	ソースファイル名：JSYejbEKK0191A010TPDA.java
*	作成者			：EK916944
*	日付			：2014年10月14日
*＜機能概要＞
*	サービス契約内訳<eo光電話>一意照会テンプレートDBアクセス部品です。
*＜修正履歴＞
*	バージョン	修正日		修正者		修正内容
*	ｖ1.00.00
*	ｖ4.00.00   2013/01/25  FJ)坂本     IT1-2013-0000099
*	ｖ4.00.01   2013/01/25  FJ)坂本     ST2-2013-0000450
*	ｖ10.00.00  2014/09/03  FJ)大田     OM-2014-0002880
*   ｖ72.00.00  2024/08/26  FJ)森       ANK-4494-00-00_【eo定期】 双方向番ポ対応
*
********************************************************************************/

package eo.ejb.cbs.sqlf;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import 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.entity.KK0191ETMsg;
import eo.ejb.cbs.cbsmsg.EKK0191A010CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0191A010CBSMsg1List;
import eo.ejb.common.JKKModelCommon;
import eo.ejb.common.db.JKKejbExclusiveProcKK0161;

/**
*
*  テンプレートDBアクセス部品　EKK0191A010_SQLF<br>
*  入力された項目からSQL文を動的に組み立て、<br>
*  検索結果をCBSMsgにセットします。<br>
*/
public class JSYejbEKK0191A010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
	
	/**
	 * コンストラクタ
	 */
	public JSYejbEKK0191A010TPDA()	{
	}
	
	/**
	*   EKK0191A010_SQLF<br>
	*   @param inMsg    入力値の格納されたCBSMsg <br>
	*   @return void
	*/
	public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
		
		// コネクション
		Connection con1 = null;
		
		// プリペアステートメント
		PreparedStatement pstmt = null;
		
		// リザルトセット
		ResultSet rsltQuery = null;
		
		/******************** 
		 * SQL文の作成
		 ********************/

		try{
			
			//コネクション取得
			con1 = JSYejbConnection.getConnection(KK0191ETMsg.getTableName());
			
			
			// SQL文_基本部1
			StringBuffer sql_Buff = new StringBuffer();
			sql_Buff.append(" SELECT ")
					.append("   KK0191.SVC_KEI_UCWK_NO ")
					.append("  ,KK0191.GENE_ADD_DTM ")
					.append("  ,KK0191.TELNO ")
					.append("  ,KK0191.DAIHYO_TELNO ")
					.append("  ,KK0191.TELNO_USE_PLACE_NO ")
					.append("  ,KK0191.EMG_AD_CD ")
					.append("  ,KK0191.EMG_HOSEI_PCD ")
					.append("  ,KK0191.EMG_BNCHIGO ")
					.append("  ,KK0191.EMG_ADRTTM ")
					.append("  ,KK0191.EMG_ADRRM ")
					.append("  ,KK0191.EMG_KSH_KSITEI_FLG ")
					.append("  ,KK0191.EMG_KSH_NM ")
					.append("  ,KK0191.EMG_KSH_KANA ")
					.append("  ,KK0191.SIP_USER_ID ")
					.append("  ,KK0191.SIP_USER_ID_PWD ")
					.append("  ,KK0191.SIP_USER_ID_DEL_ZUMI_FLG ")
					.append("  ,KK0191.FIRST_CDR_HASSEI_YMD ")
					.append("  ,KK0191.TELNO_JUN ")
					.append("  ,KK0191.NTT_NO_GUIDE_UM ")
					.append("  ,(SELECT ZM0171_02.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_02 ")
					.append("         WHERE  ZM0171_02.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_02.CD_DIV = KK0191.NTT_NO_GUIDE_UM ")
					.append("         AND    ZM0171_02.MK_FLG = '0')  NTT_NO_GUIDE_UM_NM ")
					.append("  ,KK0191.HELLOPAGE_KEISAI_UM ")
					.append("  ,(SELECT ZM0171_03.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_03 ")
					.append("         WHERE  ZM0171_03.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_03.CD_DIV = KK0191.HELLOPAGE_KEISAI_UM ")
					.append("         AND    ZM0171_03.MK_FLG = '0')  HELLOPAGE_KEISAI_UM_NM ")
					.append("  ,KK0191.TUWA_DTL_KOKAI_YH ")
					.append("  ,(SELECT ZM0171_04.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_04 ")
					.append("         WHERE  ZM0171_04.CD_SBT_CD = 'CD00001' ")
					.append("         AND    ZM0171_04.CD_DIV = KK0191.TUWA_DTL_KOKAI_YH ")
					.append("         AND    ZM0171_04.MK_FLG = '0')  TUWA_DTL_KOKAI_YH_NM ")
					.append("  ,KK0191.TEL_NO_PRE_TCHI_CD ")
					.append("  ,KK0191.NTT_KEISHA_AD_CD ")
					.append("  ,KK0191.NTT_KEISHA_PCD ")
					.append("  ,KK0191.NTT_KEISHA_STATE_NM ")
					.append("  ,KK0191.NTT_KEISHA_CITY_NM ")
					.append("  ,KK0191.NTT_KEISHA_OAZTSU_NM ")
					.append("  ,KK0191.NTT_KEISHA_AZCHO_NM ")
					.append("  ,KK0191.NTT_KEISHA_BNCHIGO ")
					.append("  ,KK0191.NTT_KEISHA_ADRTTM ")
					.append("  ,KK0191.NTT_KEISHA_ADRRM ")
					.append("  ,KK0191.NTT_KSH_KANA ")
					.append("  ,KK0191.NTT_KEISHA_NM ")
					.append("  ,KK0191.NTT_KSH_AD_KSH_AD_SAI_FLG ")
					.append("  ,KK0191.TSJGS_CD ")
					.append("  ,(SELECT TU0051.TSJGS_NM ")
					.append("         FROM   TU_M_TUSHIN_JGYOSHA TU0051 ")
					.append("         WHERE  TU0051.TSJGS_CD = KK0191.TSJGS_CD ")
					.append("         AND    TU0051.MK_FLG= '0') AS TSJGS_NM ")
					.append("  ,KK0191.ITNM_TSJGS_KTN_CD ")
					.append("  ,(SELECT TU0131.TSJGS_KTN_NM ")
					.append("         FROM   TU_M_TUSHIN_JGS_KTN TU0131 ")
					.append("         WHERE  TU0131.TSJGS_KTN_CD = KK0191.ITNM_TSJGS_KTN_CD ")
					.append("         AND    TU0131.MK_FLG= '0') AS TSJGS_KTN_NM ")
					.append("  ,KK0191.DOJI_OPEN_KIBO_CD ")
					.append("  ,KK0191.TEL_TTDKI_MSKM_CD ")
					.append("  ,KK0191.MAN_SWITCH_RSV_DTM ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_KIBO_UM ")
					.append("  ,(SELECT ZM0171_05.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_05 ")
					.append("         WHERE  ZM0171_05.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_05.CD_DIV = KK0191.DSL_PAUSE_TOKI_KIBO_UM ")
					.append("         AND    ZM0171_05.MK_FLG = '0')  DSL_PAUSE_TOKI_KIBO_UM_NM ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_SBT_CD ")
					.append("  ,(SELECT ZM0171_06.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_06 ")
					.append("         WHERE  ZM0171_06.CD_SBT_CD = 'CD00324' ")
					.append("         AND    ZM0171_06.CD_DIV = KK0191.DSL_PAUSE_TOKI_SBT_CD ")
					.append("         AND    ZM0171_06.MK_FLG = '0') DSL_PAUSE_TOKI_SBT_CD_NM ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_TSS_TLN ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_STA_YMD ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_END_RSYMD ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_END_YMD ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_ADD_CD ")
					.append("  ,(SELECT ZM0171_07.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_07 ")
					.append("         WHERE  ZM0171_07.CD_SBT_CD = 'CD00325' ")
					.append("         AND    ZM0171_07.CD_DIV = KK0191.DSL_PAUSE_TOKI_ADD_CD ")
					.append("         AND    ZM0171_07.MK_FLG = '0') DSL_PAUSE_TOKI_ADD_CD_NM ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_STA_RSYMD ")
					.append("  ,KK0191.TOWNPAGE_KEISAI_KANA ")
					.append("  ,KK0191.TOWNPAGE_KEISAI_NM ")
					.append("  ,KK0191.BMP_UM ")
					.append("  ,(SELECT ZM0171_08.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_08 ")
					.append("         WHERE  ZM0171_08.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_08.CD_DIV = KK0191.BMP_UM ")
					.append("         AND    ZM0171_08.MK_FLG = '0') BMP_UM_NM ")
					.append("  ,KK0191.BMP_DOJI_KOJI_KIBO_UM ")
					.append("  ,(SELECT ZM0171_09.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_09 ")
					.append("         WHERE  ZM0171_09.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_09.CD_DIV = KK0191.BMP_DOJI_KOJI_KIBO_UM ")
					.append("         AND    ZM0171_09.MK_FLG = '0') BMP_DOJI_KOJI_KIBO_UM_NM ")
					.append("  ,KK0191.BMP_DOJI_OPEN_YMD ")
					.append("  ,KK0191.VA_TAKNKIKI_MODEL_CD ")
					.append("  ,KK0191.VA_KIKI_SEIZO_NO ")
					.append("  ,KK0191.VA_PORT_NO ")
					.append("  ,KK0191.RSV_APLY_CD ")
					.append("  ,(SELECT ZM0171_10.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_10 ")
					.append("         WHERE  ZM0171_10.CD_SBT_CD = 'CD00343' ")
					.append("         AND    ZM0171_10.CD_DIV = KK0191.RSV_APLY_CD ")
					.append("         AND    ZM0171_10.MK_FLG = '0')  RSV_APLY_CD_NM ")
					.append("  ,KK0191.ADD_DTM ")
					.append("  ,KK0191.ADD_OPEACNT ")
					.append("  ,KK0191.UPD_DTM ")
					.append("  ,KK0191.UPD_OPEACNT ")
					.append("  ,KK0191.DEL_DTM ")
					.append("  ,KK0191.DEL_OPEACNT ")
					.append("  ,KK0191.MK_FLG ")
					.append("  ,(SELECT ZM0171_11.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_11 ")
					.append("         WHERE  ZM0171_11.CD_SBT_CD = 'CD00016' ")
					.append("         AND    ZM0171_11.CD_DIV = KK0191.MK_FLG ")
					.append("         AND    ZM0171_11.MK_FLG = '0') MK_FLG_NM ")
					.append("  ,NULL AS LAST_UPD_DTM ")
					.append("  ,KK0191.EMG_STATE_NM ")
					.append("  ,KK0191.EMG_CITY_NM ")
					.append("  ,KK0191.EMG_OAZTSU_NM ")
					.append("  ,KK0191.EMG_AZCHO_NM ")
					.append("  ,KK0191.SHK_TEL_OP_SETTE_RMTS_PWD ")
					.append("  ,KK0191.TEL_OP_SETTE_RMTS_PWD ")
					.append("  ,KK0191.DSL_PAUSE_TOKI_TSS_SVKUWNO ")
					.append("  ,KK0191.BMP_AF_USE_PLACE_SBT_CD ")
					.append("  ,(SELECT ZM0171_12.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_12 ")
					.append("         WHERE  ZM0171_12.CD_SBT_CD = 'CD01301' ")
					.append("         AND    ZM0171_12.CD_DIV = BMP_AF_USE_PLACE_SBT_CD ")
					.append("         AND    ZM0171_12.MK_FLG = '0') BMP_AF_USE_PLACE_SBT_CD_NM ")
					.append("  ,KK0191.PAUSE_TOKI_END_SBT_CD ")
					.append("  ,(SELECT ZM0171_13.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_13 ")
					.append("         WHERE  ZM0171_13.CD_SBT_CD = 'CD01353' ")
					.append("         AND    ZM0171_13.CD_DIV = KK0191.PAUSE_TOKI_END_SBT_CD ")
					.append("         AND    ZM0171_13.MK_FLG = '0') PAUSE_TOKI_END_SBT_CD_NM ")
					.append("  ,KK0191.NKYS_AD_MAN_INPUT_FLG ")
					.append("  ,(SELECT ZM0171_14.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_14 ")
					.append("         WHERE  ZM0171_14.CD_SBT_CD = 'CD01239' ")
					.append("         AND    ZM0171_14.CD_DIV = KK0191.NKYS_AD_MAN_INPUT_FLG ")
					.append("         AND    ZM0171_14.MK_FLG = '0') NKYS_AD_MAN_INPUT_FLG_NM ")
					.append("  ,KK0191.DOBANITEN_MSKM_UM ")
					.append("  ,(SELECT ZM0171_15.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_15 ")
					.append("         WHERE  ZM0171_15.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_15.CD_DIV = KK0191.DOBANITEN_MSKM_UM ")
					.append("         AND    ZM0171_15.MK_FLG = '0') AS DOBANITEN_MSKM_UM_NM ")
					.append("  ,KK0191.DOBANITEN_REQ_CTRL_CD ")
					.append("  ,(SELECT ZM0171_16.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_16 ")
					.append("         WHERE  ZM0171_16.CD_SBT_CD = 'CD01425' ")
					.append("         AND    ZM0171_16.CD_DIV = KK0191.DOBANITEN_REQ_CTRL_CD ")
					.append("         AND    ZM0171_16.MK_FLG = '0') AS DOBANITEN_REQ_CTRL_CD_NM ")
					.append("  ,KK0191.DOBANITEN_JDG_CD ")
					.append("  ,(SELECT ZM0171_17.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_17 ")
					.append("         WHERE  ZM0171_17.CD_SBT_CD = 'CD01281' ")
					.append("         AND    ZM0171_17.CD_DIV = KK0191.DOBANITEN_JDG_CD ")
					.append("         AND    ZM0171_17.MK_FLG = '0') DOBANITEN_JDG_CD_NM ")
					.append("  ,KK0191.BMP_KOJI_KIBO_YMD ")
					.append("  ,KK0191.BMPKJ_KIBO_TIME_CD ")
					.append("  ,(SELECT ZM0171_18.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_18 ")
					.append("         WHERE  ZM0171_18.CD_SBT_CD = 'CD01234' ")
					.append("         AND    ZM0171_18.CD_DIV = KK0191.BMPKJ_KIBO_TIME_CD ")
					.append("         AND    ZM0171_18.MK_FLG = '0') BMPKJ_KIBO_TIME_CD_NM ")
					.append("  ,KK0191.DBANITEN_MSKMJIG_KIJIRAN_1 ")
					.append("  ,KK0191.DBANITEN_MSKMJIG_KIJIRAN_2 ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_ADD_CD ")
					.append("  ,(SELECT ZM0171_20.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_20 ")
					.append("         WHERE  ZM0171_20.CD_SBT_CD = 'CD00325' ")
					.append("         AND    ZM0171_20.CD_DIV = KK0191.ITENS_OPAF_TOKI_ADD_CD ")
					.append("         AND    ZM0171_20.MK_FLG = '0') AS ITENS_OPAF_TOKI_ADD_CD_NM ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_SBT_CD ")
					.append("  ,(SELECT ZM0171_21.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_21 ")
					.append("         WHERE  ZM0171_21.CD_SBT_CD = 'CD00324' ")
					.append("         AND    ZM0171_21.CD_DIV = KK0191.ITENS_OPAF_TOKI_SBT_CD ")
					.append("         AND    ZM0171_21.MK_FLG = '0') AS ITENS_OPAF_TOKI_SBT_CD_NM ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_KIBO_UM ")
					.append("  ,(SELECT ZM0171_22.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_22 ")
					.append("         WHERE  ZM0171_22.CD_SBT_CD = 'CD00002' ")
					.append("         AND    ZM0171_22.CD_DIV = KK0191.ITENS_OPAF_TOKI_KIBO_UM ")
					.append("         AND    ZM0171_22.MK_FLG = '0') AS ITENS_OPAF_TOKI_KIBO_UM_NM ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_STA_RSYMD ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_STA_YMD ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_END_RSYMD ")
					.append("  ,KK0191.ITENS_OPAF_TOKI_END_YMD ")
					.append("  ,KK0191.ITNS_OPAF_TOKI_TSS_SVKUWNO ")
					.append("  ,KK0191.DOBANITEN_NTT_KEISHA_NM ")
					.append("  ,KK0191.DOBANITEN_NTT_KSH_KANA ")
					.append("  ,KK0191.VA_KIKI_CHG_NO ")
					.append("  ,KK0191.DOBANITEN_TTDK_JSSIS_SKCD ")
					.append("  ,(SELECT ZM0171_23.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_23 ")
					.append("         WHERE  ZM0171_23.CD_SBT_CD = 'CD01658' ")
					.append("         AND    ZM0171_23.CD_DIV = KK0191.DOBANITEN_TTDK_JSSIS_SKCD ")
					.append("         AND    ZM0171_23.MK_FLG = '0') AS DOBANITEN_TTDK_JSSIS_SKCD_NM ")
					.append("  ,KK0191.DBNITN_KOPT_NAI_SKEKKA_CD ")
					.append("  ,(SELECT ZM0171_24.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_24 ")
					.append("         WHERE  ZM0171_24.CD_SBT_CD = 'CD01659' ")
					.append("         AND    ZM0171_24.CD_DIV = KK0191.DBNITN_KOPT_NAI_SKEKKA_CD ")
					.append("         AND    ZM0171_24.MK_FLG = '0') AS DBNITN_KOPT_NAI_SKEKKA_CD_NM ")
					.append("  ,KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG ")
					.append("  ,(SELECT ZM0171_25.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_25 ")
					.append("         WHERE  ZM0171_25.CD_SBT_CD = 'CD00581' ")
					.append("         AND    ZM0171_25.CD_DIV = KK0191.DBNITN_AT_RNKKH_CST_ZM_FLG ")
					.append("         AND    ZM0171_25.MK_FLG = '0') AS DBNITN_AT_RNKKH_CST_ZM_FLG_NM ")
					// v72.00.00 ANK-4994-00-00 ADD START
					.append(" ,KK0191.KOJI_JSSI_WAY ")
					.append(" ,(SELECT ZM0171_26.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_26 ")
					.append("         WHERE  ZM0171_26.CD_SBT_CD = 'CD01832' ")
					.append("         AND    ZM0171_26.CD_DIV = KK0191.KOJI_JSSI_WAY ")
					.append("         AND    ZM0171_26.MK_FLG = '0') AS KOJI_JSSI_WAY_NM ")
					.append(" ,KK0191.SOTEI_SWITCH_TIMEZ ")
					.append(" ,(SELECT ZM0171_27.CD_DIV_NM ")
					.append("         FROM   ZM_M_CD_NM_KANRI ZM0171_27 ")
					.append("         WHERE  ZM0171_27.CD_SBT_CD IN ('CD01833','CD01837') ")
					.append("         AND    ZM0171_27.CD_DIV = KK0191.SOTEI_SWITCH_TIMEZ ")
					.append("         AND    ZM0171_27.MK_FLG = '0') AS SOTEI_SWITCH_TIMEZ_NM ")
					// v72.00.00 ANK-4994-00-00 ADD END
					.append(" FROM ")
					.append("  KK_T_SVKEIUW_EOH_TEL KK0191 ");
			// SQL文_個別部1
			if ( "1".equals( inMsg.getObject(EKK0191A010CBSMsg.FUNC_CODE).toString() ) ) {
 				sql_Buff.append("  WHERE  ")
						.append("   KK0191.SVC_KEI_UCWK_NO = ? AND  ")
						.append("   KK0191.GENE_ADD_DTM = ? AND  ")
						.append("   KK0191.MK_FLG = '0' ");
			}
			if ( "2".equals( inMsg.getObject(EKK0191A010CBSMsg.FUNC_CODE).toString() ) ) {
 				sql_Buff.append(" WHERE    KK0191.SVC_KEI_UCWK_NO = ? ")
						.append(" AND      (KK0191.SVC_KEI_UCWK_NO, KK0191.GENE_ADD_DTM) = ")
						.append("          (SELECT KK0191_GENE.SVC_KEI_UCWK_NO, MAX(KK0191_GENE.GENE_ADD_DTM) AS KK0191_MAX  ")
						.append("           FROM   KK_T_SVKEIUW_EOH_TEL  KK0191_GENE ")
						.append("           WHERE  KK0191_GENE.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
						.append("           AND    KK0191_GENE.MK_FLG = '0' ")
						.append("           AND    KK0191_GENE.RSV_APLY_CD = '2' ")
						.append("           GROUP BY KK0191_GENE.SVC_KEI_UCWK_NO )  ");
			}
			if ( "3".equals( inMsg.getObject(EKK0191A010CBSMsg.FUNC_CODE).toString() ) ) {
 				sql_Buff.append(" INNER JOIN KK_T_SVC_KEI_UCWK  KK0161  ")
						.append("    ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO  ")
						.append("   AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM  ")
						.append(" WHERE KK0191.SVC_KEI_UCWK_NO = ?  ")
						.append("   AND KK0191.MK_FLG = '0'  ")
						.append("   AND (KK0161.SVC_KEI_UCWK_NO, KK0161.GENE_ADD_DTM) =  ")
						.append("          (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX   ")
						.append("             FROM KK_T_SVC_KEI_UCWK  KK0161_GENE  ")
						.append("            WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO  ")
						.append("              AND KK0161_GENE.MK_FLG = '0'  ")
						.append("              AND KK0161_GENE.RSV_APLY_YMD <= ?  ")
						.append("              AND KK0161_GENE.RSV_APLY_CD = '2'  ")
						.append("            GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO )  ");
			}
			// OM-2014-0002880 予約適用日を考慮する 2014/09/03 大田 ADD START
			if ( "4".equals( inMsg.getObject(EKK0191A010CBSMsg.FUNC_CODE).toString() ) ) {
 				sql_Buff.append(" INNER JOIN KK_T_SVC_KEI_UCWK  KK0161  ")
						.append("    ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO  ")
						.append("   AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM  ")
						.append(" WHERE KK0191.SVC_KEI_UCWK_NO = ?  ")
						.append("   AND KK0191.MK_FLG = '0'  ")
						.append("   AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) =  ")
						.append("          (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX   ")
						.append("             FROM KK_T_SVC_KEI_UCWK  KK0161_GENE  ")
						.append("            WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO  ")
						.append("              AND KK0161_GENE.MK_FLG = '0'  ")
						.append("              AND KK0161_GENE.RSV_APLY_YMD <= ?  ")
						.append("              AND KK0161_GENE.RSV_APLY_CD = '2'  ")
						.append("            GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO )  ");
			}
			// OM-2014-0002880 予約適用日を考慮する 2014/09/03 大田 ADD END
			
			//prepareStatementにSQL文をセット
			pstmt = con1.prepareStatement(sql_Buff.toString());
			
			//ログ出力(SQL文の出力)
			JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
			
			// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
			if ( "1".equals( inMsg.getString(EKK0191A010CBSMsg.FUNC_CODE) ) ) {
				// パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定)
				CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0191A010CBSMsg.KEY_SVC_KEI_UCWK_NO));
				// パラメータの設定(ＫＥＹ＿世代登録年月日時分秒を指定)
				CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(EKK0191A010CBSMsg.KEY_GENE_ADD_DTM));
			}
			if ( "2".equals( inMsg.getString(EKK0191A010CBSMsg.FUNC_CODE) ) ) {
				// パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定)
				CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0191A010CBSMsg.KEY_SVC_KEI_UCWK_NO));
			}
			if ( "3".equals( inMsg.getString(EKK0191A010CBSMsg.FUNC_CODE) ) ) {
				// パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定)
				CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0191A010CBSMsg.KEY_SVC_KEI_UCWK_NO));
				// パラメータの設定(運用日付を指定)
				CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg));
			}
			
			// OM-2014-0002880 予約適用日を考慮する 2014/09/03 大田 ADD START
			if ( "4".equals( inMsg.getString(EKK0191A010CBSMsg.FUNC_CODE) ) ) {
				// パラメータの設定(ＫＥＹ＿サービス契約内訳番号を指定)
				CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(EKK0191A010CBSMsg.KEY_SVC_KEI_UCWK_NO));
				// パラメータの設定(運用日付を指定)
				CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg));
			}
			// OM-2014-0002880 予約適用日を考慮する 2014/09/03 大田 ADD END
			
			// ResultSetの取得
			rsltQuery = pstmt.executeQuery();
			
			// EKK0191A010CBSMsgの明細にセットする項目
			String[] msgKeyList1 = {
					  EKK0191A010CBSMsg1List.SVC_KEI_UCWK_NO
					, EKK0191A010CBSMsg1List.GENE_ADD_DTM
					, EKK0191A010CBSMsg1List.TELNO
					, EKK0191A010CBSMsg1List.DAIHYO_TELNO
					, EKK0191A010CBSMsg1List.TELNO_USE_PLACE_NO
					, EKK0191A010CBSMsg1List.EMG_AD_CD
					, EKK0191A010CBSMsg1List.EMG_HOSEI_PCD
					, EKK0191A010CBSMsg1List.EMG_BNCHIGO
					, EKK0191A010CBSMsg1List.EMG_ADRTTM
					, EKK0191A010CBSMsg1List.EMG_ADRRM
					, EKK0191A010CBSMsg1List.EMG_KSH_KSITEI_FLG
					, EKK0191A010CBSMsg1List.EMG_KSH_NM
					, EKK0191A010CBSMsg1List.EMG_KSH_KANA
					, EKK0191A010CBSMsg1List.SIP_USER_ID
					, EKK0191A010CBSMsg1List.SIP_USER_ID_PWD
					, EKK0191A010CBSMsg1List.SIP_USER_ID_DEL_ZUMI_FLG
					, EKK0191A010CBSMsg1List.FIRST_CDR_HASSEI_YMD
					, EKK0191A010CBSMsg1List.TELNO_JUN
					, EKK0191A010CBSMsg1List.NTT_NO_GUIDE_UM
					, EKK0191A010CBSMsg1List.NTT_NO_GUIDE_UM_NM
					, EKK0191A010CBSMsg1List.HELLOPAGE_KEISAI_UM
					, EKK0191A010CBSMsg1List.HELLOPAGE_KEISAI_UM_NM
					, EKK0191A010CBSMsg1List.TUWA_DTL_KOKAI_YH
					, EKK0191A010CBSMsg1List.TUWA_DTL_KOKAI_YH_NM
					, EKK0191A010CBSMsg1List.TEL_NO_PRE_TCHI_CD
					, EKK0191A010CBSMsg1List.NTT_KEISHA_AD_CD
					, EKK0191A010CBSMsg1List.NTT_KEISHA_PCD
					, EKK0191A010CBSMsg1List.NTT_KEISHA_STATE_NM
					, EKK0191A010CBSMsg1List.NTT_KEISHA_CITY_NM
					, EKK0191A010CBSMsg1List.NTT_KEISHA_OAZTSU_NM
					, EKK0191A010CBSMsg1List.NTT_KEISHA_AZCHO_NM
					, EKK0191A010CBSMsg1List.NTT_KEISHA_BNCHIGO
					, EKK0191A010CBSMsg1List.NTT_KEISHA_ADRTTM
					, EKK0191A010CBSMsg1List.NTT_KEISHA_ADRRM
					, EKK0191A010CBSMsg1List.NTT_KSH_KANA
					, EKK0191A010CBSMsg1List.NTT_KEISHA_NM
					, EKK0191A010CBSMsg1List.NTT_KSH_AD_KSH_AD_SAI_FLG
					, EKK0191A010CBSMsg1List.TSJGS_CD
					, EKK0191A010CBSMsg1List.TSJGS_CD_NM
					, EKK0191A010CBSMsg1List.ITNM_TSJGS_KTN_CD
					, EKK0191A010CBSMsg1List.ITNM_TSJGS_KTN_CD_NM
					, EKK0191A010CBSMsg1List.DOJI_OPEN_KIBO_CD
					, EKK0191A010CBSMsg1List.TEL_TTDKI_MSKM_CD
					, EKK0191A010CBSMsg1List.MAN_SWITCH_RSV_DTM
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_KIBO_UM
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_KIBO_UM_NM
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_SBT_CD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_SBT_CD_NM
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_TSS_TLN
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_STA_YMD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_END_RSYMD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_END_YMD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_ADD_CD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_ADD_CD_NM
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_STA_RSYMD
					, EKK0191A010CBSMsg1List.TOWNPAGE_KEISAI_KANA
					, EKK0191A010CBSMsg1List.TOWNPAGE_KEISAI_NM
					, EKK0191A010CBSMsg1List.BMP_UM
					, EKK0191A010CBSMsg1List.BMP_UM_NM
					, EKK0191A010CBSMsg1List.BMP_DOJI_KOJI_KIBO_UM
					, EKK0191A010CBSMsg1List.BMP_DOJI_KOJI_KIBO_UM_NM
					, EKK0191A010CBSMsg1List.BMP_DOJI_OPEN_YMD
					, EKK0191A010CBSMsg1List.VA_TAKNKIKI_MODEL_CD
					, EKK0191A010CBSMsg1List.VA_KIKI_SEIZO_NO
					, EKK0191A010CBSMsg1List.VA_PORT_NO
					, EKK0191A010CBSMsg1List.RSV_APLY_CD
					, EKK0191A010CBSMsg1List.RSV_APLY_CD_NM
					, EKK0191A010CBSMsg1List.ADD_DTM
					, EKK0191A010CBSMsg1List.ADD_OPEACNT
					, EKK0191A010CBSMsg1List.UPD_DTM
					, EKK0191A010CBSMsg1List.UPD_OPEACNT
					, EKK0191A010CBSMsg1List.DEL_DTM
					, EKK0191A010CBSMsg1List.DEL_OPEACNT
					, EKK0191A010CBSMsg1List.MK_FLG
					, EKK0191A010CBSMsg1List.MK_FLG_NM
					, EKK0191A010CBSMsg1List.LAST_UPD_DTM
					, EKK0191A010CBSMsg1List.EMG_STATE_NM
					, EKK0191A010CBSMsg1List.EMG_CITY_NM
					, EKK0191A010CBSMsg1List.EMG_OAZTSU_NM
					, EKK0191A010CBSMsg1List.EMG_AZCHO_NM
					, EKK0191A010CBSMsg1List.SHK_TEL_OP_SETTE_RMTS_PWD
					, EKK0191A010CBSMsg1List.TEL_OP_SETTE_RMTS_PWD
					, EKK0191A010CBSMsg1List.DSL_PAUSE_TOKI_TSS_SVKUWNO
					, EKK0191A010CBSMsg1List.BMP_AF_USE_PLACE_SBT_CD
					, EKK0191A010CBSMsg1List.BMP_AF_USE_PLACE_SBT_CD_NM
					, EKK0191A010CBSMsg1List.PAUSE_TOKI_END_SBT_CD
					, EKK0191A010CBSMsg1List.PAUSE_TOKI_END_SBT_CD_NM
					, EKK0191A010CBSMsg1List.NKYS_AD_MAN_INPUT_FLG
					, EKK0191A010CBSMsg1List.NKYS_AD_MAN_INPUT_FLG_NM
					, EKK0191A010CBSMsg1List.DOBANITEN_MSKM_UM
					, EKK0191A010CBSMsg1List.DOBANITEN_MSKM_UM_NM
					, EKK0191A010CBSMsg1List.DOBANITEN_REQ_CTRL_CD
					, EKK0191A010CBSMsg1List.DOBANITEN_REQ_CTRL_CD_NM
					, EKK0191A010CBSMsg1List.DOBANITEN_JDG_CD
					, EKK0191A010CBSMsg1List.DOBANITEN_JDG_CD_NM
					, EKK0191A010CBSMsg1List.BMP_KOJI_KIBO_YMD
					, EKK0191A010CBSMsg1List.BMPKJ_KIBO_TIME_CD
					, EKK0191A010CBSMsg1List.BMPKJ_KIBO_TIME_CD_NM
					, EKK0191A010CBSMsg1List.DBANITEN_MSKMJIG_KIJIRAN_1
					, EKK0191A010CBSMsg1List.DBANITEN_MSKMJIG_KIJIRAN_2
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_ADD_CD
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_ADD_CD_NM
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_SBT_CD
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_SBT_CD_NM
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_KIBO_UM
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_KIBO_UM_NM
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_STA_RSYMD
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_STA_YMD
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_END_RSYMD
					, EKK0191A010CBSMsg1List.ITENS_OPAF_TOKI_END_YMD
					, EKK0191A010CBSMsg1List.ITNS_OPAF_TOKI_TSS_SVKUWNO
					, EKK0191A010CBSMsg1List.DOBANITEN_NTT_KEISHA_NM
					, EKK0191A010CBSMsg1List.DOBANITEN_NTT_KSH_KANA
					, EKK0191A010CBSMsg1List.VA_KIKI_CHG_NO
					, EKK0191A010CBSMsg1List.DOBANITEN_TTDK_JSSIS_SKCD
					, EKK0191A010CBSMsg1List.DOBANITEN_TTDK_JSSIS_SKCD_NM
					, EKK0191A010CBSMsg1List.DBNITN_KOPT_NAI_SKEKKA_CD
					, EKK0191A010CBSMsg1List.DBNITN_KOPT_NAI_SKEKKA_CD_NM
					, EKK0191A010CBSMsg1List.DBNITN_AT_RNKKH_CST_ZM_FLG
					, EKK0191A010CBSMsg1List.DBNITN_AT_RNKKH_CST_ZM_FLG_NM
					// v72.00.00 ANK-4994-00-00 ADD START
					, EKK0191A010CBSMsg1List.KOJI_JSSI_WAY
					, EKK0191A010CBSMsg1List.KOJI_JSSI_WAY_NM
					, EKK0191A010CBSMsg1List.SOTEI_SWITCH_TIMEZ
					, EKK0191A010CBSMsg1List.SOTEI_SWITCH_TIMEZ_NM
					// v72.00.00 ANK-4994-00-00 ADD END
			};
			
			// 明細に処理結果(ResultSet)をマッピング
			CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0191A010CBSMsg1List",0);

			// 明細が取得できなかった場合、最終更新年月日時分秒を取得しない
			if (outMsg1.length > 0)
			{
				String lastUpdDtm = null;
				// 検索結果に"サービス契約内訳番号"が設定されていた場合
				if (!outMsg1[0].isNull(EKK0191A010CBSMsg1List.SVC_KEI_UCWK_NO))
				{
					lastUpdDtm = new JKKejbExclusiveProcKK0161().getLastUpdDtmSvcKeiUcwkNo(inMsg, outMsg1[0]
							.getString(EKK0191A010CBSMsg1List.SVC_KEI_UCWK_NO));
				}

				// 取得した最大最終更新年月日時分秒を設定
				outMsg1[0].set(EKK0191A010CBSMsg1List.LAST_UPD_DTM, lastUpdDtm);
			}

			// inMsg(CBSMsg)に明細をセット
			inMsg.set("EKK0191A010CBSMsg1List", outMsg1);
			

		} catch(SQLException e) {
			inMsg.set(EKK0191A010CBSMsg.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(EKK0191A010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
				throw new CAANRuntimeException(e);
			}
		}
	}
}