/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbECK0011A010TPDA
* ソースファイル名：JSYejbECK0011A010TPDA.java
* 作成者 ：EK911775
* 日付 ：2012年07月03日
*＜機能概要＞
* お客様一意照会テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* v4.00.00 2013/03/17 FJ)沖田 OT-2013-0000308対応
* v8.00.00 2014/03/04 FJ)小島 ANK-1962-00-00
* v8.00.01 2014/03/07 FJ)松枝 ANK-1962-00-00
* v20.00.00 2015/10/05 FJ)清原 ANK-2553-00-00
* v33.00.00 2017/05/31 FJ)クウン ANK-3179-00-00
* v37.00.00 2018/02/16 FJ)柳川 【ANK-3366-00-00】eo光設備卸対応
* v37.00.01 2018/02/27 FJ)三原 ANK-3366-00-00_eo光設備卸対応
* v37.00.02 2018/04/16 FJ)永井 【ANK-3375-00-00】【CMP】クレカ有効期限切れに対する対応施策
* v57.00.00 2022/01/13 FJ)平野 【ANK-4075-00-00】マンション卸回線(WI-FIスキーム)の不備対応
********************************************************************************/
package eo.ejb.cbs.sqlf;
import com.fujitsu.futurity.model.base.CAANMsg;
import com.fujitsu.futurity.model.base.CAANSQLFacility;
import com.fujitsu.futurity.model.base.CAANRuntimeException;
import com.fujitsu.futurity.model.base.CAANJDBCUtil;
import com.fujitsu.futurity.model.ejb.common.JSYejbConnection;
import com.fujitsu.futurity.model.ejb.common.JSYejbLog;
import com.fujitsu.futurity.model.ejb.common.StatusCodes;
import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler;
import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import eo.ejb.cbs.cbsmsg.ECK0011A010CBSMsg;
import eo.ejb.cbs.cbsmsg.ECK0011A010CBSMsg1List;
import eo.ejb.cbm.entity.CK0011ETMsg;
import eo.ejb.common.JCKModelCommon;
import eo.ejb.common.db.JCKejbExclusiveProcCK0011;
/**
*
* テンプレートDBアクセス部品 ECK0011A010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbECK0011A010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbECK0011A010TPDA() {
}
/**
* ECK0011A010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
// ANK-3366-00-00 ADD START
// パラメータのカウント
int iPCnt = 0;
// ANK-3366-00-00 ADD END
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(CK0011ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT CK0011.SYSID ")
.append(" ,CK0011.GENE_ADD_DTM ")
.append(" ,CK0011.CUST_STAT ")
.append(" ,(SELECT ZM0171_01.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ")
.append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00038' ")
.append(" AND ZM0171_01.CD_DIV = CK0011.CUST_STAT ")
.append(" AND ZM0171_01.MK_FLG = '0' ")
.append(" ) AS CUST_STAT_NM ")
.append(" ,CK0011.KEISHA_TYPE_CD ")
.append(" ,(SELECT ZM0171_02.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ")
.append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00036' ")
.append(" AND ZM0171_02.CD_DIV = CK0011.KEISHA_TYPE_CD ")
.append(" AND ZM0171_02.MK_FLG = '0' ")
.append(" ) AS KEISHA_TYPE_CD_NM ")
.append(" ,CK0011.CUST_NM ")
.append(" ,CK0011.CUST_KANA ")
.append(" ,CK0011.CUST_SEX_CD ")
.append(" ,(SELECT ZM0171_03.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ")
.append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00245' ")
.append(" AND ZM0171_03.CD_DIV = CK0011.CUST_SEX_CD ")
.append(" AND ZM0171_03.MK_FLG = '0' ")
.append(" ) AS CUST_SEX_CD_NM ")
.append(" ,CK0011.CUST_BIRTHD ")
.append(" ,CK0011.NM_KISHO ")
.append(" ,CK0011.CUST_CFM_BIKO ")
.append(" ,CK0011.KEISHA_AD_CD ")
.append(" ,CK0011.KEISHA_PCD ")
.append(" ,CK0011.KEISHA_STATE_NM ")
.append(" ,CK0011.KEISHA_CITY_NM ")
.append(" ,CK0011.KEISHA_OAZTSU_NM ")
.append(" ,CK0011.KEISHA_AZCHO_NM ")
.append(" ,CK0011.KEISHA_BNCHIGO ")
.append(" ,CK0011.KEISHA_ADRTTM ")
.append(" ,CK0011.KEISHA_ADRRM ")
.append(" ,CK0011.KEISHA_TELNO ")
.append(" ,CK0011.CUST_RANK_CD ")
.append(" ,(SELECT ZM0171_04.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_04 ")
.append(" WHERE ZM0171_04.CD_SBT_CD = 'CD00267' ")
.append(" AND ZM0171_04.CD_DIV = CK0011.CUST_RANK_CD ")
.append(" AND ZM0171_04.MK_FLG = '0' ")
.append(" ) AS CUST_RANK_CD_NM ")
.append(" ,CK0011.RRK_WAY_CD ")
.append(" ,(SELECT ZM0171_05.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_05 ")
.append(" WHERE ZM0171_05.CD_SBT_CD = 'CD00251' ")
.append(" AND ZM0171_05.CD_DIV = CK0011.RRK_WAY_CD ")
.append(" AND ZM0171_05.MK_FLG = '0' ")
.append(" ) AS RRK_WAY_CD_NM ")
.append(" ,CK0011.RRK_WAY_HOKI ")
.append(" ,CK0011.EOID ")
.append(" ,CK0011.EOID_CHGE_YMD ")
.append(" ,CK0011.MEMBER_SBT_CD ")
// ANK-3366-00-00 MOD START
//.append(" ,(SELECT ZM0171_06.CD_DIV_NM ")
//.append(" FROM ZM_M_CD_NM_KANRI ZM0171_06 ")
//.append(" WHERE ZM0171_06.CD_SBT_CD = 'CD00039' ")
//.append(" AND ZM0171_06.CD_DIV = CK0011.MEMBER_SBT_CD ")
//.append(" AND ZM0171_06.MK_FLG = '0' ")
.append(" ,(CASE ")
.append(" WHEN CK0011.MEMBER_SBT_CD = '10' AND CK0011.ORSJGS_CD IS NOT NULL ")
.append(" THEN (SELECT ZM0321.WORK_PARAM_SETTE_VALUE ")
.append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ")
.append(" WHERE ZM0321.WORK_PARAM_ID = 'CK_DISP_MEM_SBT_ORS' ")
.append(" AND ZM0321.WORK_PARAM_TSTAYMD <= ? ")
.append(" AND ZM0321.WORK_PARAM_TENDYMD >= ? ")
.append(" AND ZM0321.MK_FLG = '0') ")
.append(" ELSE (SELECT ZM0171_06.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_06 ")
.append(" WHERE ZM0171_06.CD_SBT_CD = 'CD00039' ")
.append(" AND ZM0171_06.CD_DIV = CK0011.MEMBER_SBT_CD ")
.append(" AND ZM0171_06.MK_FLG = '0') ")
.append(" END ")
// ANK-3366-00-00 MOD END
.append(" ) AS MEMBER_SBT_CD_NM ")
.append(" ,CK0011.KARI_ADD_YMD ")
.append(" ,CK0011.JOIN_YMD ")
.append(" ,CK0011.RSV_APLY_YMD ")
.append(" ,CK0011.RSV_CL_YMD ")
.append(" ,CK0011.RSV_APLY_CD ")
.append(" ,(SELECT ZM0171_07.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_07 ")
.append(" WHERE ZM0171_07.CD_SBT_CD = 'CD00343' ")
.append(" AND ZM0171_07.CD_DIV = CK0011.RSV_APLY_CD ")
.append(" AND ZM0171_07.MK_FLG = '0' ")
.append(" ) AS RSV_APLY_CD_NM ")
.append(" ,CK0011.PWD_REMINDER_QS_CD ")
.append(" ,(SELECT ZM0171_08.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_08 ")
.append(" WHERE ZM0171_08.CD_SBT_CD = 'CD00264' ")
.append(" AND ZM0171_08.CD_DIV = CK0011.PWD_REMINDER_QS_CD ")
.append(" AND ZM0171_08.MK_FLG = '0' ")
.append(" ) AS PWD_REMINDER_QS_CD_NM ")
.append(" ,CK0011.PWD_REMINDER_QS_NAIYO ")
.append(" ,CK0011.PWD_REMINDER_ANS ")
.append(" ,CK0011.NKNAME ")
.append(" ,CK0011.NKNAME_CHGE_YMD ")
.append(" ,CK0011.NAIHAN_GAIHAN_CD ")
.append(" ,CK0011.NAIHAN_COMP_SKBT_CD ")
.append(" ,CK0011.NECESSARY_HOJO_CD ")
.append(" ,CK0011.EONETID_MI_IKO_FLG ")
.append(" ,(SELECT ZM0171_09.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_09 ")
.append(" WHERE ZM0171_09.CD_SBT_CD = 'CD00602' ")
.append(" AND ZM0171_09.CD_DIV = CK0011.EONETID_MI_IKO_FLG ")
.append(" AND ZM0171_09.MK_FLG = '0' ")
.append(" ) AS EONETID_MI_IKO_FLG_NM ")
.append(" ,CK0011.LOGIN_NINSHO_KH ")
.append(" ,(SELECT ZM0171_10.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_10 ")
.append(" WHERE ZM0171_10.CD_SBT_CD = 'CD00003' ")
.append(" AND ZM0171_10.CD_DIV = CK0011.LOGIN_NINSHO_KH ")
.append(" AND ZM0171_10.MK_FLG = '0' ")
.append(" ) AS LOGIN_NINSHO_KH_NM ")
.append(" ,CK0011.CUST_TKJK_CD ")
.append(" ,CK0011.CUST_NTAIKAI_CD ")
.append(" ,(SELECT ZM0171_11.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_11 ")
.append(" WHERE ZM0171_11.CD_SBT_CD = 'CD00260' ")
.append(" AND ZM0171_11.CD_DIV = CK0011.CUST_NTAIKAI_CD ")
.append(" AND ZM0171_11.MK_FLG = '0' ")
.append(" ) AS CUST_NTAIKAI_CD_NM ")
.append(" ,CK0011.CUST_TAIKAI_YMD ")
.append(" ,CK0011.CUST_TAIKAI_RSN_CD ")
.append(" ,(SELECT ZM0171_12.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_12 ")
.append(" WHERE ZM0171_12.CD_SBT_CD = 'CD00261' ")
.append(" AND ZM0171_12.CD_DIV = CK0011.CUST_TAIKAI_RSN_CD ")
.append(" AND ZM0171_12.MK_FLG = '0' ")
.append(" ) AS CUST_TAIKAI_RSN_CD_NM ")
.append(" ,CK0011.CUST_KAIHK_YMD ")
.append(" ,CK0011.LOGIN_NINSHO_STP_YMD ")
.append(" ,CK0011.SHK_EOID ")
.append(" ,CK0011.SHK_EOID_PWD ")
.append(" ,CK0011.MMB_SBT_TOKUT_FLG ")
.append(" ,(SELECT ZM0171_13.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_13 ")
.append(" WHERE ZM0171_13.CD_SBT_CD = 'CD00603' ")
.append(" AND ZM0171_13.CD_DIV = CK0011.MMB_SBT_TOKUT_FLG ")
.append(" AND ZM0171_13.MK_FLG = '0' ")
.append(" ) AS MMB_SBT_TOKUT_FLG_NM ")
.append(" ,CK0011.NAYOSE_SAKI_SYSID ")
.append(" ,(SELECT CK0011_01.CUST_NM ")
.append(" FROM CK_T_CUST CK0011_01 ")
.append(" WHERE CK0011_01.SYSID = CK0011.NAYOSE_SAKI_SYSID ")
.append(" AND (CK0011_01.SYSID, CK0011_01.RSV_APLY_YMD || CK0011_01.GENE_ADD_DTM) = ")
.append(" (SELECT CK0011_GENE.SYSID, MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX ")
.append(" FROM CK_T_CUST CK0011_GENE ")
.append(" WHERE CK0011_GENE.SYSID = CK0011_01.SYSID ")
.append(" AND CK0011_GENE.RSV_APLY_YMD <= ? ")
.append(" AND CK0011_GENE.RSV_APLY_CD = '2' ")
.append(" AND CK0011_GENE.MK_FLG = '0' ")
.append(" GROUP BY CK0011_GENE.SYSID ")
.append(" )) AS NAYOSE_SAKI_SYSID_NM ")
.append(" ,CK0011.NAYOSE_YMD ")
.append(" ,CK0011.EOID_TCHISHO_HAKKOJSKI_CD ")
.append(" ,(SELECT ZM0171_14.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_14 ")
.append(" WHERE ZM0171_14.CD_SBT_CD = 'CD00266' ")
.append(" AND ZM0171_14.CD_DIV = CK0011.EOID_TCHISHO_HAKKOJSKI_CD ")
.append(" AND ZM0171_14.MK_FLG = '0' ")
.append(" ) AS EOID_TCHISHO_HAKKOJSKI_CD_NM ")
.append(" ,CK0011.CUST_MK_YMD ")
.append(" ,CK0011.DEL_TRGT_GAI_FLG ")
.append(" ,(SELECT ZM0171_15.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_15 ")
.append(" WHERE ZM0171_15.CD_SBT_CD = 'CD00582' ")
.append(" AND ZM0171_15.CD_DIV = CK0011.DEL_TRGT_GAI_FLG ")
.append(" AND ZM0171_15.MK_FLG = '0' ")
.append(" ) AS DEL_TRGT_GAI_FLG_NM ")
.append(" ,CK0011.ADD_DTM ")
.append(" ,CK0011.ADD_OPEACNT ")
.append(" ,CK0011.UPD_DTM ")
.append(" ,CK0011.UPD_OPEACNT ")
.append(" ,CK0011.DEL_DTM ")
.append(" ,CK0011.DEL_OPEACNT ")
.append(" ,CK0011.MK_FLG ")
.append(" ,(SELECT ZM0171_16.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_16 ")
.append(" WHERE ZM0171_16.CD_SBT_CD = 'CD00016' ")
.append(" AND ZM0171_16.CD_DIV = CK0011.MK_FLG ")
.append(" AND ZM0171_16.MK_FLG = '0' ")
.append(" ) AS MK_FLG_NM ")
.append(" ,NULL AS LAST_UPD_DTM ")
.append(" ,CK0011.CONT_CUST_NM ")
.append(" ,CK0011.CONT_CUST_KANA ")
.append(" ,CK0011.CONT_CUST_SEX_CD ")
.append(" ,(SELECT ZM0171_17.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_17 ")
.append(" WHERE ZM0171_17.CD_SBT_CD = 'CD00245' ")
.append(" AND ZM0171_17.CD_DIV = CK0011.CONT_CUST_SEX_CD ")
.append(" AND ZM0171_17.MK_FLG = '0' ")
.append(" ) AS CONT_CUST_SEX_CD_NM ")
.append(" ,CK0011.CONT_CUST_BIRTHD ")
.append(" ,CK0011.CONT_AD_CD ")
.append(" ,CK0011.CONT_PCD ")
.append(" ,CK0011.CONT_STATE_NM ")
.append(" ,CK0011.CONT_CITY_NM ")
.append(" ,CK0011.CONT_OAZTSU_NM ")
.append(" ,CK0011.CONT_AZCHO_NM ")
.append(" ,CK0011.CONT_BNCHIGO ")
.append(" ,CK0011.CONT_ADRTTM ")
.append(" ,CK0011.CONT_ADRRM ")
.append(" ,CK0011.CONT_ISP_USE_FAIL_FLG ")
.append(" ,(SELECT ZM0171_18.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_18 ")
.append(" WHERE ZM0171_18.CD_SBT_CD = 'CD00601' ")
.append(" AND ZM0171_18.CD_DIV = CK0011.CONT_ISP_USE_FAIL_FLG ")
.append(" AND ZM0171_18.MK_FLG = '0' ")
.append(" ) AS CONT_ISP_USE_FAIL_FLG_NM ")
.append(" ,CK0011.CONT_ISP_USE_STP_YMD ")
.append(" ,CK0011.OLD_EONETMB_NINSHO_ID ")
.append(" ,CK0011.MMB_ISP_KSSAI_STAT_CD ")
.append(" ,(SELECT ZM0171_19.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_19 ")
.append(" WHERE ZM0171_19.CD_SBT_CD = 'CD00870' ")
.append(" AND ZM0171_19.CD_DIV = CK0011.MMB_ISP_KSSAI_STAT_CD ")
.append(" AND ZM0171_19.MK_FLG = '0' ")
.append(" ) AS MMB_ISP_KSSAI_STAT_CD_NM ")
.append(" ,CK0011.KYUMIN_FLG ")
.append(" ,CK0011.KYUMIN_YMD ")
.append(" ,CK0011.CONT_AD_BNKT_STA_PST ")
.append(" ,CK0011.SVCTK_BUT_DEL_TRN_JSSI_DTM ")
.append(" ,CK0011.KEISHA_AD_MAN_INPUT_FLG ")
.append(" ,(SELECT ZM0171_20.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_20 ")
.append(" WHERE ZM0171_20.CD_SBT_CD = 'CD01239' ")
.append(" AND ZM0171_20.CD_DIV = CK0011.KEISHA_AD_MAN_INPUT_FLG ")
.append(" AND ZM0171_20.MK_FLG = '0' ")
.append(" ) AS KEISHA_AD_MAN_INPUT_FLG_NM ")
.append(" ,CK0011.CSSTI_SHS_AD_CD ")
.append(" ,CK0011.CSSTI_SHS_PCD ")
.append(" ,CK0011.CSSTI_SHS_STATE_NM ")
.append(" ,CK0011.CSSTI_SHS_CITY_NM ")
.append(" ,CK0011.CSSTI_SHS_OAZTSU_NM ")
.append(" ,CK0011.CSSTI_SHS_AZCHO_NM ")
.append(" ,CK0011.CSSTI_SHS_BNCHIGO ")
.append(" ,CK0011.CSSTI_SHS_ADRTTM ")
.append(" ,CK0011.CSSTI_SHS_ADRRM ")
.append(" ,CK0011.CSSTI_SHS_AD_MAN_INPUT_FLG ")
.append(" ,(SELECT ZM0171_21.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_21 ")
.append(" WHERE ZM0171_21.CD_SBT_CD = 'CD01239' ")
.append(" AND ZM0171_21.CD_DIV = CK0011.CSSTI_SHS_AD_MAN_INPUT_FLG ")
.append(" AND ZM0171_21.MK_FLG = '0' ")
.append(" ) AS CSSTI_SHS_AD_MAN_INPUT_FLG_NM ")
.append(" ,CK0011.EOID_KNRN_INF_CHG_SKCD ")
.append(" ,CK0011.EOID_KNRN_INF_CHG_DTM ")
.append(" ,CK0011.KEPG_KJNIF_TRAT_DOI_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 = CK0011.KEPG_KJNIF_TRAT_DOI_UM ")
.append(" AND ZM0171_22.MK_FLG = '0' ")
.append(" ) AS KEPG_KJNIF_TRAT_DOI_UM_NM ")
.append(" ,CK0011.KEPG_KJNIF_TRAT_DOI_YMD ")
.append(" ,CK0011.KEPG_KJNIFT_FFG ")
.append(" ,(SELECT ZM0171_23.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_23 ")
.append(" WHERE ZM0171_23.CD_SBT_CD = 'CD01629' ")
.append(" AND ZM0171_23.CD_DIV = CK0011.KEPG_KJNIFT_FFG ")
.append(" AND ZM0171_23.MK_FLG = '0' ")
.append(" ) AS KEPG_KJNIFT_FFG_NM ")
.append(" ,CK0011.KEPG_KJNIFT_FFG_CHG_YMD ")
.append(" ,CK0011.KEPG_KJNIFT_FFG_CHG_UWY_CD ")
.append(" ,(SELECT ZM0171_24.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_24 ")
.append(" WHERE ZM0171_24.CD_SBT_CD = 'CD01626' ")
.append(" AND ZM0171_24.CD_DIV = CK0011.KEPG_KJNIFT_FFG_CHG_UWY_CD ")
.append(" AND ZM0171_24.MK_FLG = '0' ")
.append(" ) AS KEPG_KJNIFT_FFG_CHG_UWY_CD_NM ")
.append(" ,CK0011.KEPG_KJNIFT_JOKYO_CD ")
.append(" ,(SELECT ZM0171_25.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_25 ")
.append(" WHERE ZM0171_25.CD_SBT_CD = 'CD01627' ")
.append(" AND ZM0171_25.CD_DIV = CK0011.KEPG_KJNIFT_JOKYO_CD ")
.append(" AND ZM0171_25.MK_FLG = '0' ")
.append(" ) AS KEPG_KJNIFT_JOKYO_CD_NM ")
.append(" ,CK0011.KEPG_KJNIFT_STAYMD ")
.append(" ,CK0011.KEPG_KJNIFT_RSCD ")
.append(" ,(SELECT ZM0171_26.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_26 ")
.append(" WHERE ZM0171_26.CD_SBT_CD = 'CD01628' ")
.append(" AND ZM0171_26.CD_DIV = CK0011.KEPG_KJNIFT_RSCD ")
.append(" AND ZM0171_26.MK_FLG = '0' ")
.append(" ) AS KEPG_KJNIFT_RSCD_NM ")
.append(" ,CK0011.KEPG_KJNIFT_RSCD_CHG_YMD ")
//v20.00.00 Add Start
.append(" ,CK0011.MMB_ISP_KSSAI_USE_JDG_CD ")
.append(" ,(SELECT ZM0171_27.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_27 ")
.append(" WHERE ZM0171_27.CD_SBT_CD = 'CD01678' ")
.append(" AND ZM0171_27.CD_DIV = CK0011.MMB_ISP_KSSAI_USE_JDG_CD ")
.append(" AND ZM0171_27.MK_FLG = '0' ")
.append(" ) AS MMB_ISP_KSSAI_USE_JDG_CD_NM ")
//v20.00.00 Add End
//---------v33.00.00 ANK-3179-00-00 ADD START---------//
.append(" ,CK0011.DSL_AF_SKSSNS_KH ")
//---------v33.00.00 ANK-3179-00-00 ADD END-----------//
// ANK-3366-00-00 ADD START
.append(" ,CK0011.ORSJGS_CD ")
// ANK-3366-00-00 ADD END
// ANK-3366-00-00 ADD START
.append(" ,(SELECT CH0911.ORSJGS_NM ")
.append(" FROM CH_M_ORSJGS CH0911 ")
.append(" WHERE CH0911.ORSJGS_CD = CK0011.ORSJGS_CD ")
.append(" AND CH0911.GENE_ADD_DTM = ")
.append(" (SELECT MAX(CH0911_GENE.GENE_ADD_DTM) AS CH0911_MAX ")
.append(" FROM CH_M_ORSJGS CH0911_GENE ")
.append(" WHERE CH0911_GENE.ORSJGS_CD = CH0911.ORSJGS_CD ")
.append(" AND CH0911_GENE.ORSJGS_TSTAYMD <= ? ")
.append(" AND CH0911_GENE.MK_FLG = '0') ")
.append(" ) AS ORSJGS_NM ")
// ANK-3366-00-00 ADD END
// ANK-3375-00-00 ADD START
.append(" ,CK0011.PAYWAY_CHG_TS_MSG_YOKSI_UM ")
.append(" ,(SELECT ZM0171_28.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_28 ")
.append(" WHERE ZM0171_28.CD_SBT_CD = 'CD00002' ")
.append(" AND ZM0171_28.CD_DIV = CK0011.PAYWAY_CHG_TS_MSG_YOKSI_UM ")
.append(" AND ZM0171_28.MK_FLG = '0' ")
.append(" ) AS PAYWAY_CHG_TS_MSG_YOKSI_UM_NM ")
// ANK-3375-00-00 ADD END
// ANK-4075-00-00 ADD START
.append(" ,CK0011.SCM_TYPE_CD ")
.append(" ,(SELECT ZM0171_29.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_29 ")
.append(" WHERE ZM0171_29.CD_SBT_CD = 'CD01784' ")
.append(" AND ZM0171_29.CD_DIV = CK0011.SCM_TYPE_CD ")
.append(" AND ZM0171_29.MK_FLG = '0' ")
.append(" ) AS SCM_TYPE_CD_NM ")
.append(" ,CK0011.SCM_JGS_CD ")
.append(" ,(SELECT ZM0171_30.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_30 ")
.append(" WHERE ZM0171_30.CD_SBT_CD = 'CD01785' ")
.append(" AND ZM0171_30.CD_DIV = CK0011.SCM_JGS_CD ")
.append(" AND ZM0171_30.MK_FLG = '0' ")
.append(" ) AS SCM_JGS_CD_NM ")
// ANK-4075-00-00 ADD END
.append(" FROM CK_T_CUST CK0011 ");
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(ECK0011A010CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" WHERE CK0011.SYSID = ? ")
.append(" AND CK0011.GENE_ADD_DTM = ? ")
.append(" AND CK0011.MK_FLG = '0' ");
}
if ( "2".equals( inMsg.getObject(ECK0011A010CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" WHERE CK0011.SYSID = ? ")
.append(" AND (CK0011.SYSID, CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM) = ")
.append(" (SELECT CK0011_GENE.SYSID, MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX ")
.append(" FROM CK_T_CUST CK0011_GENE ")
.append(" WHERE CK0011_GENE.SYSID = CK0011.SYSID ")
.append(" AND CK0011_GENE.RSV_APLY_YMD <= ? ")
.append(" AND CK0011_GENE.RSV_APLY_CD = '2' ")
.append(" AND CK0011_GENE.MK_FLG = '0' ")
.append(" GROUP BY CK0011_GENE.SYSID ) ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
// ANK-3366-00-00 MOD START
//if ( "1".equals( inMsg.getString(ECK0011A010CBSMsg.FUNC_CODE) ) ) {
// // パラメータの設定(運用日付を指定)
// CAANJDBCUtil.setParam(pstmt, 1, JCKModelCommon.getOpeDate(inMsg));
// // パラメータの設定(ＫＥＹ＿SYSIDを指定)
// CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(ECK0011A010CBSMsg.KEY_SYSID));
// // パラメータの設定(ＫＥＹ＿世代登録年月日時分秒を指定)
// CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(ECK0011A010CBSMsg.KEY_GENE_ADD_DTM));
//}
//if ( "2".equals( inMsg.getString(ECK0011A010CBSMsg.FUNC_CODE) ) ) {
// // パラメータの設定(ＫＥＹ＿予約適用年月日を指定)
// CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(ECK0011A010CBSMsg.KEY_RSV_APLY_YMD));
// // パラメータの設定(ＫＥＹ＿SYSIDを指定)
// CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(ECK0011A010CBSMsg.KEY_SYSID));
// // パラメータの設定(ＫＥＹ＿予約適用年月日を指定)
// CAANJDBCUtil.setParam(pstmt, 3, inMsg.getObject(ECK0011A010CBSMsg.KEY_RSV_APLY_YMD));
//}
if ( "1".equals( inMsg.getString(ECK0011A010CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(ＫＥＹ＿SYSIDを指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(ECK0011A010CBSMsg.KEY_SYSID));
// パラメータの設定(ＫＥＹ＿世代登録年月日時分秒を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(ECK0011A010CBSMsg.KEY_GENE_ADD_DTM));
}
if ( "2".equals( inMsg.getString(ECK0011A010CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(ＫＥＹ＿予約適用年月日を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(ECK0011A010CBSMsg.KEY_RSV_APLY_YMD));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JCKModelCommon.getOpeDate(inMsg));
// パラメータの設定(ＫＥＹ＿SYSIDを指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(ECK0011A010CBSMsg.KEY_SYSID));
// パラメータの設定(ＫＥＹ＿予約適用年月日を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(ECK0011A010CBSMsg.KEY_RSV_APLY_YMD));
}
// ANK-3366-00-00 MOD END
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// ECK0011A010CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
ECK0011A010CBSMsg1List.SYSID
, ECK0011A010CBSMsg1List.GENE_ADD_DTM
, ECK0011A010CBSMsg1List.CUST_STAT
, ECK0011A010CBSMsg1List.CUST_STAT_NM
, ECK0011A010CBSMsg1List.KEISHA_TYPE_CD
, ECK0011A010CBSMsg1List.KEISHA_TYPE_CD_NM
, ECK0011A010CBSMsg1List.CUST_NM
, ECK0011A010CBSMsg1List.CUST_KANA
, ECK0011A010CBSMsg1List.CUST_SEX_CD
, ECK0011A010CBSMsg1List.CUST_SEX_CD_NM
, ECK0011A010CBSMsg1List.CUST_BIRTHD
, ECK0011A010CBSMsg1List.NM_KISHO
, ECK0011A010CBSMsg1List.CUST_CFM_BIKO
, ECK0011A010CBSMsg1List.KEISHA_AD_CD
, ECK0011A010CBSMsg1List.KEISHA_PCD
, ECK0011A010CBSMsg1List.KEISHA_STATE_NM
, ECK0011A010CBSMsg1List.KEISHA_CITY_NM
, ECK0011A010CBSMsg1List.KEISHA_OAZTSU_NM
, ECK0011A010CBSMsg1List.KEISHA_AZCHO_NM
, ECK0011A010CBSMsg1List.KEISHA_BNCHIGO
, ECK0011A010CBSMsg1List.KEISHA_ADRTTM
, ECK0011A010CBSMsg1List.KEISHA_ADRRM
, ECK0011A010CBSMsg1List.KEISHA_TELNO
, ECK0011A010CBSMsg1List.CUST_RANK_CD
, ECK0011A010CBSMsg1List.CUST_RANK_CD_NM
, ECK0011A010CBSMsg1List.RRK_WAY_CD
, ECK0011A010CBSMsg1List.RRK_WAY_CD_NM
, ECK0011A010CBSMsg1List.RRK_WAY_HOKI
, ECK0011A010CBSMsg1List.EOID
, ECK0011A010CBSMsg1List.EOID_CHGE_YMD
, ECK0011A010CBSMsg1List.MEMBER_SBT_CD
, ECK0011A010CBSMsg1List.MEMBER_SBT_CD_NM
, ECK0011A010CBSMsg1List.KARI_ADD_YMD
, ECK0011A010CBSMsg1List.JOIN_YMD
, ECK0011A010CBSMsg1List.RSV_APLY_YMD
, ECK0011A010CBSMsg1List.RSV_CL_YMD
, ECK0011A010CBSMsg1List.RSV_APLY_CD
, ECK0011A010CBSMsg1List.RSV_APLY_CD_NM
, ECK0011A010CBSMsg1List.PWD_REMINDER_QS_CD
, ECK0011A010CBSMsg1List.PWD_REMINDER_QS_CD_NM
, ECK0011A010CBSMsg1List.PWD_REMINDER_QS_NAIYO
, ECK0011A010CBSMsg1List.PWD_REMINDER_ANS
, ECK0011A010CBSMsg1List.NKNAME
, ECK0011A010CBSMsg1List.NKNAME_CHGE_YMD
, ECK0011A010CBSMsg1List.NAIHAN_GAIHAN_CD
, ECK0011A010CBSMsg1List.NAIHAN_COMP_SKBT_CD
, ECK0011A010CBSMsg1List.NECESSARY_HOJO_CD
, ECK0011A010CBSMsg1List.EONETID_MI_IKO_FLG
, ECK0011A010CBSMsg1List.EONETID_MI_IKO_FLG_NM
, ECK0011A010CBSMsg1List.LOGIN_NINSHO_KH
, ECK0011A010CBSMsg1List.LOGIN_NINSHO_KH_NM
, ECK0011A010CBSMsg1List.CUST_TKJK_CD
, ECK0011A010CBSMsg1List.CUST_NTAIKAI_CD
, ECK0011A010CBSMsg1List.CUST_NTAIKAI_CD_NM
, ECK0011A010CBSMsg1List.CUST_TAIKAI_YMD
, ECK0011A010CBSMsg1List.CUST_TAIKAI_RSN_CD
, ECK0011A010CBSMsg1List.CUST_TAIKAI_RSN_CD_NM
, ECK0011A010CBSMsg1List.CUST_KAIHK_YMD
, ECK0011A010CBSMsg1List.LOGIN_NINSHO_STP_YMD
, ECK0011A010CBSMsg1List.SHK_EOID
, ECK0011A010CBSMsg1List.SHK_EOID_PWD
, ECK0011A010CBSMsg1List.MMB_SBT_TOKUT_FLG
, ECK0011A010CBSMsg1List.MMB_SBT_TOKUT_FLG_NM
, ECK0011A010CBSMsg1List.NAYOSE_SAKI_SYSID
, ECK0011A010CBSMsg1List.NAYOSE_SAKI_SYSID_NM
, ECK0011A010CBSMsg1List.NAYOSE_YMD
, ECK0011A010CBSMsg1List.EOID_TCHISHO_HAKKOJSKI_CD
, ECK0011A010CBSMsg1List.EOID_TCHISHO_HAKKOJSKI_CD_NM
, ECK0011A010CBSMsg1List.CUST_MK_YMD
, ECK0011A010CBSMsg1List.DEL_TRGT_GAI_FLG
, ECK0011A010CBSMsg1List.DEL_TRGT_GAI_FLG_NM
, ECK0011A010CBSMsg1List.ADD_DTM
, ECK0011A010CBSMsg1List.ADD_OPEACNT
, ECK0011A010CBSMsg1List.UPD_DTM
, ECK0011A010CBSMsg1List.UPD_OPEACNT
, ECK0011A010CBSMsg1List.DEL_DTM
, ECK0011A010CBSMsg1List.DEL_OPEACNT
, ECK0011A010CBSMsg1List.MK_FLG
, ECK0011A010CBSMsg1List.MK_FLG_NM
, ECK0011A010CBSMsg1List.LAST_UPD_DTM
, ECK0011A010CBSMsg1List.CONT_CUST_NM
, ECK0011A010CBSMsg1List.CONT_CUST_KANA
, ECK0011A010CBSMsg1List.CONT_CUST_SEX_CD
, ECK0011A010CBSMsg1List.CONT_CUST_SEX_CD_NM
, ECK0011A010CBSMsg1List.CONT_CUST_BIRTHD
, ECK0011A010CBSMsg1List.CONT_AD_CD
, ECK0011A010CBSMsg1List.CONT_PCD
, ECK0011A010CBSMsg1List.CONT_STATE_NM
, ECK0011A010CBSMsg1List.CONT_CITY_NM
, ECK0011A010CBSMsg1List.CONT_OAZTSU_NM
, ECK0011A010CBSMsg1List.CONT_AZCHO_NM
, ECK0011A010CBSMsg1List.CONT_BNCHIGO
, ECK0011A010CBSMsg1List.CONT_ADRTTM
, ECK0011A010CBSMsg1List.CONT_ADRRM
, ECK0011A010CBSMsg1List.CONT_ISP_USE_FAIL_FLG
, ECK0011A010CBSMsg1List.CONT_ISP_USE_FAIL_FLG_NM
, ECK0011A010CBSMsg1List.CONT_ISP_USE_STP_YMD
, ECK0011A010CBSMsg1List.OLD_EONETMB_NINSHO_ID
, ECK0011A010CBSMsg1List.MMB_ISP_KSSAI_STAT_CD
, ECK0011A010CBSMsg1List.MMB_ISP_KSSAI_STAT_CD_NM
, ECK0011A010CBSMsg1List.KYUMIN_FLG
, ECK0011A010CBSMsg1List.KYUMIN_YMD
, ECK0011A010CBSMsg1List.CONT_AD_BNKT_STA_PST
, ECK0011A010CBSMsg1List.SVCTK_BUT_DEL_TRN_JSSI_DTM
, ECK0011A010CBSMsg1List.KEISHA_AD_MAN_INPUT_FLG
, ECK0011A010CBSMsg1List.KEISHA_AD_MAN_INPUT_FLG_NM
, ECK0011A010CBSMsg1List.CSSTI_SHS_AD_CD
, ECK0011A010CBSMsg1List.CSSTI_SHS_PCD
, ECK0011A010CBSMsg1List.CSSTI_SHS_STATE_NM
, ECK0011A010CBSMsg1List.CSSTI_SHS_CITY_NM
, ECK0011A010CBSMsg1List.CSSTI_SHS_OAZTSU_NM
, ECK0011A010CBSMsg1List.CSSTI_SHS_AZCHO_NM
, ECK0011A010CBSMsg1List.CSSTI_SHS_BNCHIGO
, ECK0011A010CBSMsg1List.CSSTI_SHS_ADRTTM
, ECK0011A010CBSMsg1List.CSSTI_SHS_ADRRM
, ECK0011A010CBSMsg1List.CSSTI_SHS_AD_MAN_INPUT_FLG
, ECK0011A010CBSMsg1List.CSSTI_SHS_AD_MAN_INPUT_FLG_NM
, ECK0011A010CBSMsg1List.EOID_KNRN_INF_CHG_SKCD
, ECK0011A010CBSMsg1List.EOID_KNRN_INF_CHG_DTM
, ECK0011A010CBSMsg1List.KEPG_KJNIF_TRAT_DOI_UM
, ECK0011A010CBSMsg1List.KEPG_KJNIF_TRAT_DOI_UM_NM
, ECK0011A010CBSMsg1List.KEPG_KJNIF_TRAT_DOI_YMD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_FFG
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_FFG_NM
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_FFG_CHG_YMD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_FFG_CHG_UWY_CD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_FFG_CHG_UWY_CD_NM
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_JOKYO_CD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_JOKYO_CD_NM
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_STAYMD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_RSCD
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_RSCD_NM
, ECK0011A010CBSMsg1List.KEPG_KJNIFT_RSCD_CHG_YMD
//v20.00.00 Add Start
, ECK0011A010CBSMsg1List.MMB_ISP_KSSAI_USE_JDG_CD
, ECK0011A010CBSMsg1List.MMB_ISP_KSSAI_USE_JDG_CD_NM
//v20.00.00 Add End
//---------v33.00.00 ANK-3179-00-00 ADD START---------//
, ECK0011A010CBSMsg1List.DSL_AF_SKSSNS_KH
//---------v33.00.00 ANK-3179-00-00 ADD END-----------//
// ANK-3366-00-00 ADD START
, ECK0011A010CBSMsg1List.ORSJGS_CD
, ECK0011A010CBSMsg1List.ORSJGS_NM
// ANK-3366-00-00 ADD END
// ANK-3375-00-00 ADD START
, ECK0011A010CBSMsg1List.PAYWAY_CHG_TS_MSG_YOKSI_UM
, ECK0011A010CBSMsg1List.PAYWAY_CHG_TS_MSG_YOKSI_UM_NM
// ANK-3375-00-00 ADD END
// ANK-4075-00-00 ADD START
, ECK0011A010CBSMsg1List.SCM_TYPE_CD
, ECK0011A010CBSMsg1List.SCM_TYPE_CD_NM
, ECK0011A010CBSMsg1List.SCM_JGS_CD
, ECK0011A010CBSMsg1List.SCM_JGS_CD_NM
// ANK-4075-00-00 ADD END
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECK0011A010CBSMsg1List",0);
// 明細が取得できなかった場合、最終更新年月日時分秒を取得しない
if (outMsg1.length > 0)
{
String lastUpdDtm = null;
// 検索結果に"SYSID"が設定されていた場合
if (!outMsg1[0].isNull(ECK0011A010CBSMsg1List.SYSID))
{
lastUpdDtm = new JCKejbExclusiveProcCK0011().getLastUpdDtmSysId(inMsg, outMsg1[0].getString(ECK0011A010CBSMsg1List.SYSID));
}
// 取得した最大最終更新年月日時分秒を設定
outMsg1[0].set(ECK0011A010CBSMsg1List.LAST_UPD_DTM, lastUpdDtm);
}
// inMsg(CBSMsg)に明細をセット
inMsg.set("ECK0011A010CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(ECK0011A010CBSMsg.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(ECK0011A010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}