/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbECK0011B050TPDA
* ソースファイル名：JSYejbECK0011B050TPDA.java
* 作成者 ：EK907778
* 日付 ：2012年03月26日
*＜機能概要＞
* お客様情報照会(PMP連携用)テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* v33.00.00 2017/11/09 FJ)孫 ANK-3327-00-00_解約後のWeb請求明細PDFへのお客さま氏名・住所表示対応
* v53.00.00 2021/04/30 GDC)ジョバニー 【ANK-4009-00-00】CX戦略WG方針対応（異動シェア分・API）
*
********************************************************************************/
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.common.constant.JCKStrConst;
import eo.ejb.cbs.cbsmsg.ECK0011B050CBSMsg;
import eo.ejb.cbs.cbsmsg.ECK0011B050CBSMsg1List;
import eo.ejb.cbm.entity.CK0011ETMsg;
import eo.ejb.common.JCCModelCommon;
/**
*
* テンプレートDBアクセス部品 ECK0011B050_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbECK0011B050TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbECK0011B050TPDA() {
}
/**
* ECK0011B050_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(CK0011ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ");
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(ECK0011B050CBSMsg.FUNC_CODE).toString() )
// ANK-3327-00-00 ADD START
||"3".equals( inMsg.getObject(ECK0011B050CBSMsg.FUNC_CODE).toString() )) {
// ANK-3327-00-00 ADD END
sql_Buff.append(" A.CUST_STAT ")
.append(" ,A.KEISHA_TYPE_CD ")
.append(" ,A.CUST_NM ")
.append(" ,A.CUST_KANA ")
.append(" ,A.CUST_SEX_CD ")
.append(" ,A.CUST_BIRTHD ")
.append(" ,A.NM_KISHO ")
.append(" ,A.CUST_CFM_BIKO ")
.append(" ,A.KEISHA_AD_CD ")
.append(" ,A.KEISHA_PCD ")
.append(" ,A.KEISHA_STATE_NM ")
.append(" ,A.KEISHA_CITY_NM ")
.append(" ,A.KEISHA_OAZTSU_NM ")
.append(" ,A.KEISHA_AZCHO_NM ")
.append(" ,A.KEISHA_BNCHIGO ")
.append(" ,A.KEISHA_ADRTTM ")
.append(" ,A.KEISHA_ADRRM ")
.append(" ,A.KEISHA_TELNO ")
.append(" ,A.CUST_RANK_CD ")
.append(" ,A.RRK_WAY_CD ")
.append(" ,A.RRK_WAY_HOKI ")
.append(" ,A.EOID ")
.append(" ,A.EOID_CHGE_YMD ")
.append(" ,A.CONT_CUST_NM ")
.append(" ,A.CONT_CUST_KANA ")
.append(" ,A.CONT_CUST_SEX_CD ")
.append(" ,A.CONT_CUST_BIRTHD ")
.append(" ,A.CONT_AD_CD ")
.append(" ,A.CONT_PCD ")
.append(" ,A.CONT_STATE_NM ")
.append(" ,A.CONT_CITY_NM ")
.append(" ,A.CONT_OAZTSU_NM ")
.append(" ,A.CONT_AZCHO_NM ")
.append(" ,A.CONT_BNCHIGO ")
.append(" ,A.CONT_ADRTTM ")
.append(" ,A.CONT_ADRRM ")
.append(" ,A.CONT_AD_BNKT_STA_PST ")
.append(" ,A.MEMBER_SBT_CD ")
.append(" ,A.CONT_ISP_USE_FAIL_FLG ")
.append(" ,A.CONT_ISP_USE_STP_YMD ")
.append(" ,A.KARI_ADD_YMD ")
.append(" ,A.JOIN_YMD ")
.append(" ,A.RSV_APLY_YMD ")
.append(" ,A.RSV_CL_YMD ")
.append(" ,A.RSV_APLY_CD ")
.append(" ,A.PWD_REMINDER_QS_CD ")
.append(" ,A.PWD_REMINDER_QS_NAIYO ")
.append(" ,A.PWD_REMINDER_ANS ")
.append(" ,A.NKNAME ")
.append(" ,A.NKNAME_CHGE_YMD ")
.append(" ,A.NAIHAN_GAIHAN_CD ")
.append(" ,A.NAIHAN_COMP_SKBT_CD ")
.append(" ,A.NECESSARY_HOJO_CD ")
.append(" ,A.EONETID_MI_IKO_FLG ")
.append(" ,A.LOGIN_NINSHO_KH ")
.append(" ,A.CUST_TKJK_CD ")
.append(" ,A.CUST_NTAIKAI_CD ")
.append(" ,A.CUST_TAIKAI_YMD ")
.append(" ,A.CUST_TAIKAI_RSN_CD ")
.append(" ,A.CUST_KAIHK_YMD ")
.append(" ,A.LOGIN_NINSHO_STP_YMD ")
.append(" ,A.SHK_EOID ")
.append(" ,A.SHK_EOID_PWD ")
.append(" ,A.MMB_SBT_TOKUT_FLG ")
.append(" ,A.NAYOSE_SAKI_SYSID ")
.append(" ,A.NAYOSE_YMD ")
.append(" ,A.EOID_TCHISHO_HAKKOJSKI_CD ")
.append(" ,A.OLD_EONETMB_NINSHO_ID ")
.append(" ,A.MMB_ISP_KSSAI_STAT_CD ")
.append(" ,A.CUST_MK_YMD ")
.append(" ,A.DEL_TRGT_GAI_FLG ")
.append(" ,A.KYUMIN_FLG ")
.append(" ,A.KYUMIN_YMD ")
.append(" ,A.SVCTK_BUT_DEL_TRN_JSSI_DTM ")
.append(" ,A.UPD_DTM ")
.append(" ,A.KEISHA_AD_MAN_INPUT_FLG ")
.append(" ,A.CSSTI_SHS_AD_CD ")
.append(" ,A.CSSTI_SHS_PCD ")
.append(" ,A.CSSTI_SHS_STATE_NM ")
.append(" ,A.CSSTI_SHS_CITY_NM ")
.append(" ,A.CSSTI_SHS_OAZTSU_NM ")
.append(" ,A.CSSTI_SHS_AZCHO_NM ")
.append(" ,A.CSSTI_SHS_BNCHIGO ")
.append(" ,A.CSSTI_SHS_ADRTTM ")
.append(" ,A.CSSTI_SHS_ADRRM ")
.append(" ,A.CSSTI_SHS_AD_MAN_INPUT_FLG ")
.append(" ,G.SHOKUGYO_CD ")
.append(" ,G.SHOKUGYO_OTHER ")
.append(" ,G.OFFC_NM ")
.append(" ,G.SCHOOL_NM ")
.append(" ,G.OFFC_GSHU_CD ")
.append(" ,G.OFFC_BUSHO_NM ")
.append(" ,G.OFFC_AD_CD ")
.append(" ,G.OFFC_PCD ")
.append(" ,G.OFFC_STATE_NM ")
.append(" ,G.OFFC_CITY_NM ")
.append(" ,G.OFFC_OAZTSU_NM ")
.append(" ,G.OFFC_AZCHO_NM ")
.append(" ,G.OFFC_BNCHIGO ")
.append(" ,G.OFFC_ADRTTM ")
.append(" ,G.OFFC_ADRRM ")
.append(" ,G.OFFC_TELNO ")
.append(" ,G.OFFC_NISNNO ")
.append(" ,G.OFFC_WORKER_CNT_SCALE ")
.append(" ,G.CUST_SALARY ")
.append(" ,G.SKSHA_NM ")
.append(" ,G.SKSHA_KANA ")
.append(" ,G.SKSHA_BIRTHD ")
.append(" ,G.SKSHA_ZOKUGARA_CD ")
.append(" ,G.ZOKUGARA_OTHER ")
.append(" ,G.SKSHA_AD_CD ")
.append(" ,G.SKSHA_PCD ")
.append(" ,G.SKSHA_STATE_NM ")
.append(" ,G.SKSHA_CITY_NM ")
.append(" ,G.SKSHA_OAZTSU_NM ")
.append(" ,G.SKSHA_AZCHO_NM ")
.append(" ,G.SKSHA_AD_BNCHIGO ")
.append(" ,G.SKSHA_ADRTTM ")
.append(" ,G.SKSHA_ADRRM ")
.append(" ,G.SKSHA_AD_MAN_INPUT_FLG ")
.append(" ,G.SKSHA_TELNO ")
.append(" ,G.RSV_APLY_CD ")
.append(" ,I.HOJIN_SBT_CD ")
.append(" ,I.HOJIN_ZENGO_SHITEI_CD ")
.append(" ,I.COMP_NM ")
.append(" ,I.COMP_KANA ")
.append(" ,I.DIHYSHA_NM ")
.append(" ,I.DIHYSHA_KANA ")
.append(" ,I.DIHYSHA_POST_NM ")
.append(" ,I.RSV_APLY_CD ")
.append(" ,I.GSHU_CD ")
.append(" ,C.MLAD ")
.append(" ,C.RRKS_NO ")
.append(" ,C.GENE_ADD_DTM ")
.append(" ,D.MLAD ")
.append(" ,D.RRKS_NO ")
.append(" ,D.GENE_ADD_DTM ")
.append(" ,E.RRKS_NO ")
.append(" ,E.TELNO ")
.append(" ,E.RRKS_SBT_CD ")
.append(" ,E.NISNNO ")
.append(" ,E.FAX_NO ")
.append(" ,E.GENE_ADD_DTM ")
.append(" ,F.SVC_KEI_NO ")
.append(" ,F.UPD_DTM ")
// ANK-4009-00-00 ADD START
.append(" ,CASE ")
.append(" WHEN EXISTS( ")
.append(" SELECT 1 ")
.append(" FROM CK_T_LINE_INFO_KANRI T ")
.append(" WHERE T.SYSID = A.SYSID ")
.append(" AND (T.LINE_ID_NINSHO_RLS_DTM IS NULL OR T.LINE_ID_NINSHO_RLS_DTM < T.LINE_ID_NINSHO_ADD_DTM) ")
.append(" AND T.MK_FLG = '0' ")
.append(" ) THEN '1' ")
.append(" ELSE '0' ")
.append(" END AS LINE_RNKI_UM ")
// ANK-4009-00-00 ADD END
.append(" FROM ")
.append(" CK_T_CUST A ")
.append(" /* メールアドレスデータの取得 */ ")
.append(" LEFT OUTER JOIN ")
.append(" ( ")
.append(" SELECT P.SYSID, P.MLAD, P.RRKS_NO, P.GENE_ADD_DTM, P.UPD_DTM ")
.append(" FROM CK_T_RRKS P ")
.append(" WHERE P.SYSID = ? ")
.append(" AND P.RRK_WAY_CD = '001' ")
.append(" AND P.RRKS_BUNRUI_CD = '1' ")
.append(" AND P.RSV_APLY_YMD || P.GENE_ADD_DTM = ")
.append(" (SELECT MAX(I.RSV_APLY_YMD || I.GENE_ADD_DTM) AS I_MAX ")
.append(" FROM CK_T_RRKS I ")
.append(" WHERE I.RRKS_NO = P.RRKS_NO ")
.append(" AND I.RSV_APLY_CD = '2' ")
.append(" AND I.RSV_APLY_YMD <= ? ")
.append(" AND I.MK_FLG = '0' ")
.append(" ) ")
.append(" ) C ")
.append(" ON A.SYSID = C.SYSID ")
.append(" /* 変更手続き中メールアドレスデータの取得 */ ")
.append(" LEFT OUTER JOIN ")
.append(" ( ")
.append(" SELECT Q.SYSID, Q.MLAD, Q.RRKS_NO, Q.GENE_ADD_DTM, Q.UPD_DTM ")
.append(" FROM CK_T_RRKS Q ")
.append(" WHERE Q.SYSID = ? ")
.append(" AND Q.RRK_WAY_CD = '001' ")
.append(" AND Q.RSV_APLY_CD = '1' ")
.append(" AND Q.RRKS_BUNRUI_CD = '1' ")
.append(" AND Q.GENE_ADD_DTM = ")
.append(" (SELECT MAX(J.GENE_ADD_DTM) AS J_MAX ")
.append(" FROM CK_T_RRKS J ")
.append(" WHERE J.RRKS_NO = Q.RRKS_NO ")
.append(" AND J.MK_FLG = '0' ")
.append(" ) ")
.append(" ) D ")
.append(" ON A.SYSID = D.SYSID ")
.append(" /* 電話番号データの取得 */ ")
.append(" LEFT OUTER JOIN ")
.append(" ( ")
.append(" SELECT R.SYSID, R.TELNO, R.RRKS_NO, R.RRKS_SBT_CD, R.FAX_NO, R.NISNNO, R.GENE_ADD_DTM, R.UPD_DTM ")
.append(" FROM CK_T_RRKS R ")
.append(" WHERE R.SYSID = ? ")
.append(" AND R.RRK_WAY_CD = '003' ")
.append(" AND R.RRKS_BUNRUI_CD = '1' ")
.append(" AND R.RSV_APLY_YMD || R.GENE_ADD_DTM = ")
.append(" (SELECT MAX(K.RSV_APLY_YMD || K.GENE_ADD_DTM) AS K_MAX ")
.append(" FROM CK_T_RRKS K ")
.append(" WHERE K.RRKS_NO = R.RRKS_NO ")
.append(" AND K.RSV_APLY_CD = '2' ")
.append(" AND K.RSV_APLY_YMD <= ? ")
.append(" AND K.MK_FLG = '0' ")
.append(" ) ")
.append(" ) E ")
.append(" ON A.SYSID = E.SYSID ")
.append(" /* 標準コンテンツデータの取得 */ ")
.append(" LEFT OUTER JOIN ")
.append(" ( ")
.append(" SELECT S.SYSID, S.SVC_KEI_NO, (SELECT LAST_UPD_DTM FROM KK_T_SVKEI_EXC_CTRL WHERE SVC_KEI_NO = S.SVC_KEI_NO) AS UPD_DTM ")
.append(" FROM KK_T_SVC_KEI S ")
.append(" WHERE S.SYSID = ? ")
.append(" AND S.SVC_CD = ? /* 標準コンテンツ同意のサービスコード */ ")
.append(" AND S.PRC_GRP_CD = ? /* 標準コンテンツ同意の料金グループコード */ ")
.append(" AND S.PCRS_CD = ? /* 標準コンテンツ同意の料金コースコード */ ")
.append(" AND S.PPLAN_CD = ? /* 標準コンテンツ同意の料金プランコード */ ")
.append(" AND S.SVC_KEI_STAT = '100' ")
.append(" AND S.RSV_APLY_YMD || S.GENE_ADD_DTM = ")
.append(" (SELECT MAX(L.RSV_APLY_YMD || L.GENE_ADD_DTM) AS L_MAX ")
.append(" FROM KK_T_SVC_KEI L ")
.append(" WHERE L.SVC_KEI_NO = S.SVC_KEI_NO ")
.append(" AND L.RSV_APLY_CD = '2' ")
.append(" AND L.RSV_APLY_YMD <= ? ")
.append(" AND L.MK_FLG = '0') ")
.append(" ) F ")
.append(" ON A.SYSID = F.SYSID ")
.append(" /* お客様＜個人＞ */ ")
.append(" LEFT OUTER JOIN ")
.append(" CK_T_CUST_KOJIN G ")
.append(" ON A.SYSID = G.SYSID AND A.GENE_ADD_DTM = G.GENE_ADD_DTM ")
.append(" /* お客様＜法人＞ */ ")
.append(" LEFT OUTER JOIN ")
.append(" CK_T_CUST_HOJIN I ")
.append(" ON A.SYSID = I.SYSID AND A.GENE_ADD_DTM = I.GENE_ADD_DTM ")
.append(" WHERE A.SYSID = ? ");
// ANK-3327-00-00 ADD START
if("1".equals( inMsg.getObject(ECK0011B050CBSMsg.FUNC_CODE).toString()))
{
sql_Buff
// ANK-3327-00-00 ADD END
.append(" AND A.CUST_NTAIKAI_CD = '0' ");
// ANK-3327-00-00 ADD START
}
sql_Buff
// ANK-3327-00-00 ADD END
.append(" AND A.RSV_APLY_YMD || A.GENE_ADD_DTM = ")
.append(" (SELECT MAX(N.RSV_APLY_YMD || N.GENE_ADD_DTM) AS N_MAX ")
.append(" FROM CK_T_CUST N ")
.append(" WHERE A.SYSID = N.SYSID ")
.append(" AND N.RSV_APLY_CD = '2' ")
.append(" AND N.RSV_APLY_YMD <= ? ")
.append(" AND N.MK_FLG = '0') ");
}
if ( "2".equals( inMsg.getObject(ECK0011B050CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" A.CUST_STAT ")
.append(" ,A.KEISHA_TYPE_CD ")
.append(" ,A.CUST_NM ")
.append(" ,A.CUST_KANA ")
.append(" ,A.CUST_SEX_CD ")
.append(" ,A.CUST_BIRTHD ")
.append(" ,A.NM_KISHO ")
.append(" ,A.CUST_CFM_BIKO ")
.append(" ,A.KEISHA_AD_CD ")
.append(" ,A.KEISHA_PCD ")
.append(" ,A.KEISHA_STATE_NM ")
.append(" ,A.KEISHA_CITY_NM ")
.append(" ,A.KEISHA_OAZTSU_NM ")
.append(" ,A.KEISHA_AZCHO_NM ")
.append(" ,A.KEISHA_BNCHIGO ")
.append(" ,A.KEISHA_ADRTTM ")
.append(" ,A.KEISHA_ADRRM ")
.append(" ,A.KEISHA_TELNO ")
.append(" ,A.CUST_RANK_CD ")
.append(" ,A.RRK_WAY_CD ")
.append(" ,A.RRK_WAY_HOKI ")
.append(" ,A.EOID ")
.append(" ,A.EOID_CHGE_YMD ")
.append(" ,A.CONT_CUST_NM ")
.append(" ,A.CONT_CUST_KANA ")
.append(" ,A.CONT_CUST_SEX_CD ")
.append(" ,A.CONT_CUST_BIRTHD ")
.append(" ,A.CONT_AD_CD ")
.append(" ,A.CONT_PCD ")
.append(" ,A.CONT_STATE_NM ")
.append(" ,A.CONT_CITY_NM ")
.append(" ,A.CONT_OAZTSU_NM ")
.append(" ,A.CONT_AZCHO_NM ")
.append(" ,A.CONT_BNCHIGO ")
.append(" ,A.CONT_ADRTTM ")
.append(" ,A.CONT_ADRRM ")
.append(" ,A.CONT_AD_BNKT_STA_PST ")
.append(" ,A.MEMBER_SBT_CD ")
.append(" ,A.CONT_ISP_USE_FAIL_FLG ")
.append(" ,A.CONT_ISP_USE_STP_YMD ")
.append(" ,A.KARI_ADD_YMD ")
.append(" ,A.JOIN_YMD ")
.append(" ,A.RSV_APLY_YMD ")
.append(" ,A.RSV_CL_YMD ")
.append(" ,A.RSV_APLY_CD ")
.append(" ,A.PWD_REMINDER_QS_CD ")
.append(" ,A.PWD_REMINDER_QS_NAIYO ")
.append(" ,A.PWD_REMINDER_ANS ")
.append(" ,A.NKNAME ")
.append(" ,A.NKNAME_CHGE_YMD ")
.append(" ,A.NAIHAN_GAIHAN_CD ")
.append(" ,A.NAIHAN_COMP_SKBT_CD ")
.append(" ,A.NECESSARY_HOJO_CD ")
.append(" ,A.EONETID_MI_IKO_FLG ")
.append(" ,A.LOGIN_NINSHO_KH ")
.append(" ,A.CUST_TKJK_CD ")
.append(" ,A.CUST_NTAIKAI_CD ")
.append(" ,A.CUST_TAIKAI_YMD ")
.append(" ,A.CUST_TAIKAI_RSN_CD ")
.append(" ,A.CUST_KAIHK_YMD ")
.append(" ,A.LOGIN_NINSHO_STP_YMD ")
.append(" ,A.SHK_EOID ")
.append(" ,A.SHK_EOID_PWD ")
.append(" ,A.MMB_SBT_TOKUT_FLG ")
.append(" ,A.NAYOSE_SAKI_SYSID ")
.append(" ,A.NAYOSE_YMD ")
.append(" ,A.EOID_TCHISHO_HAKKOJSKI_CD ")
.append(" ,A.OLD_EONETMB_NINSHO_ID ")
.append(" ,A.MMB_ISP_KSSAI_STAT_CD ")
.append(" ,A.CUST_MK_YMD ")
.append(" ,A.DEL_TRGT_GAI_FLG ")
.append(" ,A.KYUMIN_FLG ")
.append(" ,A.KYUMIN_YMD ")
.append(" ,A.SVCTK_BUT_DEL_TRN_JSSI_DTM ")
.append(" ,A.UPD_DTM ")
.append(" ,A.KEISHA_AD_MAN_INPUT_FLG ")
.append(" ,A.CSSTI_SHS_AD_CD ")
.append(" ,A.CSSTI_SHS_PCD ")
.append(" ,A.CSSTI_SHS_STATE_NM ")
.append(" ,A.CSSTI_SHS_CITY_NM ")
.append(" ,A.CSSTI_SHS_OAZTSU_NM ")
.append(" ,A.CSSTI_SHS_AZCHO_NM ")
.append(" ,A.CSSTI_SHS_BNCHIGO ")
.append(" ,A.CSSTI_SHS_ADRTTM ")
.append(" ,A.CSSTI_SHS_ADRRM ")
.append(" ,A.CSSTI_SHS_AD_MAN_INPUT_FLG ")
.append(" ,NULL SHOKUGYO_CD ")
.append(" ,NULL SHOKUGYO_OTHER ")
.append(" ,NULL OFFC_NM ")
.append(" ,NULL SCHOOL_NM ")
.append(" ,NULL OFFC_GSHU_CD ")
.append(" ,NULL OFFC_BUSHO_NM ")
.append(" ,NULL OFFC_AD_CD ")
.append(" ,NULL OFFC_PCD ")
.append(" ,NULL OFFC_STATE_NM ")
.append(" ,NULL OFFC_CITY_NM ")
.append(" ,NULL OFFC_OAZTSU_NM ")
.append(" ,NULL OFFC_AZCHO_NM ")
.append(" ,NULL OFFC_BNCHIGO ")
.append(" ,NULL OFFC_ADRTTM ")
.append(" ,NULL OFFC_ADRRM ")
.append(" ,NULL OFFC_TELNO ")
.append(" ,NULL OFFC_NISNNO ")
.append(" ,NULL OFFC_WORKER_CNT_SCALE ")
.append(" ,NULL CUST_SALARY ")
.append(" ,NULL SKSHA_NM ")
.append(" ,NULL SKSHA_KANA ")
.append(" ,NULL SKSHA_BIRTHD ")
.append(" ,NULL SKSHA_ZOKUGARA_CD ")
.append(" ,NULL ZOKUGARA_OTHER ")
.append(" ,NULL SKSHA_AD_CD ")
.append(" ,NULL SKSHA_PCD ")
.append(" ,NULL SKSHA_STATE_NM ")
.append(" ,NULL SKSHA_CITY_NM ")
.append(" ,NULL SKSHA_OAZTSU_NM ")
.append(" ,NULL SKSHA_AZCHO_NM ")
.append(" ,NULL SKSHA_AD_BNCHIGO ")
.append(" ,NULL SKSHA_ADRTTM ")
.append(" ,NULL SKSHA_ADRRM ")
.append(" ,NULL SKSHA_AD_MAN_INPUT_FLG ")
.append(" ,NULL SKSHA_TELNO ")
.append(" ,NULL RSV_APLY_CD ")
.append(" ,NULL HOJIN_SBT_CD ")
.append(" ,NULL HOJIN_ZENGO_SHITEI_CD ")
.append(" ,NULL COMP_NM ")
.append(" ,NULL COMP_KANA ")
.append(" ,NULL DIHYSHA_NM ")
.append(" ,NULL DIHYSHA_KANA ")
.append(" ,NULL DIHYSHA_POST_NM ")
.append(" ,NULL RSV_APLY_CD ")
.append(" ,NULL GSHU_CD ")
.append(" ,NULL MLAD ")
.append(" ,NULL RRKS_NO ")
.append(" ,NULL GENE_ADD_DTM ")
.append(" ,NULL MLAD ")
.append(" ,NULL RRKS_NO ")
.append(" ,NULL GENE_ADD_DTM ")
.append(" ,NULL RRKS_NO ")
.append(" ,NULL TELNO ")
.append(" ,NULL RRKS_SBT_CD ")
.append(" ,NULL NISNNO ")
.append(" ,NULL FAX_NO ")
.append(" ,NULL GENE_ADD_DTM ")
.append(" ,NULL SVC_KEI_NO ")
.append(" ,NULL UPD_DTM ")
// ANK-4009-00-00 ADD START
.append(" ,NULL LINE_RNKI_UM ")
// ANK-4009-00-00 ADD END
.append(" FROM ")
.append(" CK_T_CUST A ")
.append(" WHERE A.SYSID = ? ")
.append(" AND A.RSV_APLY_YMD || A.GENE_ADD_DTM = ")
.append(" (SELECT MAX(A_GENE.RSV_APLY_YMD || A_GENE.GENE_ADD_DTM) AS N_MAX ")
.append(" FROM CK_T_CUST A_GENE ")
.append(" WHERE A.SYSID = A_GENE.SYSID ")
.append(" AND A_GENE.RSV_APLY_CD = '2' ")
.append(" AND A_GENE.RSV_APLY_YMD <= ? ")
.append(" AND A_GENE.MK_FLG = '0') ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// 運用日付の取得
String opeDate = JCCModelCommon.getOpeDate(inMsg, null);
String sysid = inMsg.getString(ECK0011B050CBSMsg.KEY_SYSID);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(ECK0011B050CBSMsg.FUNC_CODE) )
// ANK-3327-00-00 ADD START
|| "3".equals( inMsg.getString(ECK0011B050CBSMsg.FUNC_CODE)
// ANK-3327-00-00 ADD END
)) {
CAANJDBCUtil.setParam(pstmt, 1, sysid);
CAANJDBCUtil.setParam(pstmt, 2, opeDate);
CAANJDBCUtil.setParam(pstmt, 3, sysid);
CAANJDBCUtil.setParam(pstmt, 4, sysid);
CAANJDBCUtil.setParam(pstmt, 5, opeDate);
CAANJDBCUtil.setParam(pstmt, 6, sysid);
CAANJDBCUtil.setParam(pstmt, 7, JCKStrConst.SVC_CD_HOSTING);
CAANJDBCUtil.setParam(pstmt, 8, JCKStrConst.PRC_GRP_CD_HOSTING);
CAANJDBCUtil.setParam(pstmt, 9, JCKStrConst.PCRS_CD_STDARD_CONT_DOI);
CAANJDBCUtil.setParam(pstmt, 10, JCKStrConst.PPLAN_CD_STDARD_CONT_DOI);
CAANJDBCUtil.setParam(pstmt, 11, opeDate);
CAANJDBCUtil.setParam(pstmt, 12, sysid);
CAANJDBCUtil.setParam(pstmt, 13, opeDate);
}
if ( "2".equals( inMsg.getString(ECK0011B050CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定)
CAANJDBCUtil.setParam(pstmt, 1, inMsg.getObject(ECK0011B050CBSMsg.KEY_SYSID));
CAANJDBCUtil.setParam(pstmt, 2, opeDate);
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// ECK0011B050CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
ECK0011B050CBSMsg1List.CUST_STAT
, ECK0011B050CBSMsg1List.KEISHA_TYPE_CD
, ECK0011B050CBSMsg1List.CUST_NM
, ECK0011B050CBSMsg1List.CUST_KANA
, ECK0011B050CBSMsg1List.CUST_SEX_CD
, ECK0011B050CBSMsg1List.CUST_BIRTHD
, ECK0011B050CBSMsg1List.NM_KISHO
, ECK0011B050CBSMsg1List.CUST_CFM_BIKO
, ECK0011B050CBSMsg1List.KEISHA_AD_CD
, ECK0011B050CBSMsg1List.KEISHA_PCD
, ECK0011B050CBSMsg1List.KEISHA_STATE_NM
, ECK0011B050CBSMsg1List.KEISHA_CITY_NM
, ECK0011B050CBSMsg1List.KEISHA_OAZTSU_NM
, ECK0011B050CBSMsg1List.KEISHA_AZCHO_NM
, ECK0011B050CBSMsg1List.KEISHA_BNCHIGO
, ECK0011B050CBSMsg1List.KEISHA_ADRTTM
, ECK0011B050CBSMsg1List.KEISHA_ADRRM
, ECK0011B050CBSMsg1List.KEISHA_TELNO
, ECK0011B050CBSMsg1List.CUST_RANK_CD
, ECK0011B050CBSMsg1List.RRK_WAY_CD
, ECK0011B050CBSMsg1List.RRK_WAY_HOKI
, ECK0011B050CBSMsg1List.EOID
, ECK0011B050CBSMsg1List.EOID_CHGE_YMD
, ECK0011B050CBSMsg1List.CONT_CUST_NM
, ECK0011B050CBSMsg1List.CONT_CUST_KANA
, ECK0011B050CBSMsg1List.CONT_CUST_SEX_CD
, ECK0011B050CBSMsg1List.CONT_CUST_BIRTHD
, ECK0011B050CBSMsg1List.CONT_AD_CD
, ECK0011B050CBSMsg1List.CONT_PCD
, ECK0011B050CBSMsg1List.CONT_STATE_NM
, ECK0011B050CBSMsg1List.CONT_CITY_NM
, ECK0011B050CBSMsg1List.CONT_OAZTSU_NM
, ECK0011B050CBSMsg1List.CONT_AZCHO_NM
, ECK0011B050CBSMsg1List.CONT_BNCHIGO
, ECK0011B050CBSMsg1List.CONT_ADRTTM
, ECK0011B050CBSMsg1List.CONT_ADRRM
, ECK0011B050CBSMsg1List.CONT_AD_BNKT_STA_PST
, ECK0011B050CBSMsg1List.MEMBER_SBT_CD
, ECK0011B050CBSMsg1List.CONT_ISP_USE_FAIL_FLG
, ECK0011B050CBSMsg1List.CONT_ISP_USE_STP_YMD
, ECK0011B050CBSMsg1List.KARI_ADD_YMD
, ECK0011B050CBSMsg1List.JOIN_YMD
, ECK0011B050CBSMsg1List.RSV_APLY_YMD
, ECK0011B050CBSMsg1List.RSV_CL_YMD
, ECK0011B050CBSMsg1List.RSV_APLY_CD
, ECK0011B050CBSMsg1List.PWD_REMINDER_QS_CD
, ECK0011B050CBSMsg1List.PWD_REMINDER_QS_NAIYO
, ECK0011B050CBSMsg1List.PWD_REMINDER_ANS
, ECK0011B050CBSMsg1List.NKNAME
, ECK0011B050CBSMsg1List.NKNAME_CHGE_YMD
, ECK0011B050CBSMsg1List.NAIHAN_GAIHAN_CD
, ECK0011B050CBSMsg1List.NAIHAN_COMP_SKBT_CD
, ECK0011B050CBSMsg1List.NECESSARY_HOJO_CD
, ECK0011B050CBSMsg1List.EONETID_MI_IKO_FLG
, ECK0011B050CBSMsg1List.LOGIN_NINSHO_KH
, ECK0011B050CBSMsg1List.CUST_TKJK_CD
, ECK0011B050CBSMsg1List.CUST_NTAIKAI_CD
, ECK0011B050CBSMsg1List.CUST_TAIKAI_YMD
, ECK0011B050CBSMsg1List.CUST_TAIKAI_RSN_CD
, ECK0011B050CBSMsg1List.CUST_KAIHK_YMD
, ECK0011B050CBSMsg1List.LOGIN_NINSHO_STP_YMD
, ECK0011B050CBSMsg1List.SHK_EOID
, ECK0011B050CBSMsg1List.SHK_EOID_PWD
, ECK0011B050CBSMsg1List.MMB_SBT_TOKUT_FLG
, ECK0011B050CBSMsg1List.NAYOSE_SAKI_SYSID
, ECK0011B050CBSMsg1List.NAYOSE_YMD
, ECK0011B050CBSMsg1List.EOID_TCHISHO_HAKKOJSKI_CD
, ECK0011B050CBSMsg1List.OLD_EONETMB_NINSHO_ID
, ECK0011B050CBSMsg1List.MMB_ISP_KSSAI_STAT_CD
, ECK0011B050CBSMsg1List.CUST_MK_YMD
, ECK0011B050CBSMsg1List.DEL_TRGT_GAI_FLG
, ECK0011B050CBSMsg1List.KYUMIN_FLG
, ECK0011B050CBSMsg1List.KYUMIN_YMD
, ECK0011B050CBSMsg1List.SVCTK_BUT_DEL_TRN_JSSI_DTM
, ECK0011B050CBSMsg1List.UPD_DTM_SYSID
, ECK0011B050CBSMsg1List.KEISHA_AD_MAN_INPUT_FLG
, ECK0011B050CBSMsg1List.CSSTI_SHS_AD_CD
, ECK0011B050CBSMsg1List.CSSTI_SHS_PCD
, ECK0011B050CBSMsg1List.CSSTI_SHS_STATE_NM
, ECK0011B050CBSMsg1List.CSSTI_SHS_CITY_NM
, ECK0011B050CBSMsg1List.CSSTI_SHS_OAZTSU_NM
, ECK0011B050CBSMsg1List.CSSTI_SHS_AZCHO_NM
, ECK0011B050CBSMsg1List.CSSTI_SHS_BNCHIGO
, ECK0011B050CBSMsg1List.CSSTI_SHS_ADRTTM
, ECK0011B050CBSMsg1List.CSSTI_SHS_ADRRM
, ECK0011B050CBSMsg1List.CSSTI_SHS_AD_MAN_INPUT_FLG
, ECK0011B050CBSMsg1List.SHOKUGYO_CD
, ECK0011B050CBSMsg1List.SHOKUGYO_OTHER
, ECK0011B050CBSMsg1List.OFFC_NM
, ECK0011B050CBSMsg1List.SCHOOL_NM
, ECK0011B050CBSMsg1List.OFFC_GSHU_CD
, ECK0011B050CBSMsg1List.OFFC_BUSHO_NM
, ECK0011B050CBSMsg1List.OFFC_AD_CD
, ECK0011B050CBSMsg1List.OFFC_PCD
, ECK0011B050CBSMsg1List.OFFC_STATE_NM
, ECK0011B050CBSMsg1List.OFFC_CITY_NM
, ECK0011B050CBSMsg1List.OFFC_OAZTSU_NM
, ECK0011B050CBSMsg1List.OFFC_AZCHO_NM
, ECK0011B050CBSMsg1List.OFFC_BNCHIGO
, ECK0011B050CBSMsg1List.OFFC_ADRTTM
, ECK0011B050CBSMsg1List.OFFC_ADRRM
, ECK0011B050CBSMsg1List.OFFC_TELNO
, ECK0011B050CBSMsg1List.OFFC_NISNNO
, ECK0011B050CBSMsg1List.OFFC_WORKER_CNT_SCALE
, ECK0011B050CBSMsg1List.CUST_SALARY
, ECK0011B050CBSMsg1List.SKSHA_NM
, ECK0011B050CBSMsg1List.SKSHA_KANA
, ECK0011B050CBSMsg1List.SKSHA_BIRTHD
, ECK0011B050CBSMsg1List.SKSHA_ZOKUGARA_CD
, ECK0011B050CBSMsg1List.ZOKUGARA_OTHER
, ECK0011B050CBSMsg1List.SKSHA_AD_CD
, ECK0011B050CBSMsg1List.SKSHA_PCD
, ECK0011B050CBSMsg1List.SKSHA_STATE_NM
, ECK0011B050CBSMsg1List.SKSHA_CITY_NM
, ECK0011B050CBSMsg1List.SKSHA_OAZTSU_NM
, ECK0011B050CBSMsg1List.SKSHA_AZCHO_NM
, ECK0011B050CBSMsg1List.SKSHA_AD_BNCHIGO
, ECK0011B050CBSMsg1List.SKSHA_ADRTTM
, ECK0011B050CBSMsg1List.SKSHA_ADRRM
, ECK0011B050CBSMsg1List.SKSHA_AD_MAN_INPUT_FLG
, ECK0011B050CBSMsg1List.SKSHA_TELNO
, ECK0011B050CBSMsg1List.KOJIN_RSV_APLY_CD
, ECK0011B050CBSMsg1List.HOJIN_SBT_CD
, ECK0011B050CBSMsg1List.HOJIN_ZENGO_SHITEI_CD
, ECK0011B050CBSMsg1List.COMP_NM
, ECK0011B050CBSMsg1List.COMP_KANA
, ECK0011B050CBSMsg1List.DIHYSHA_NM
, ECK0011B050CBSMsg1List.DIHYSHA_KANA
, ECK0011B050CBSMsg1List.DIHYSHA_POST_NM
, ECK0011B050CBSMsg1List.HOJIN_RSV_APLY_CD
, ECK0011B050CBSMsg1List.GSHU_CD
, ECK0011B050CBSMsg1List.MLAD
, ECK0011B050CBSMsg1List.RRKS_NO_MLAD
, ECK0011B050CBSMsg1List.GENE_ADD_DTM_MLAD
, ECK0011B050CBSMsg1List.CHGECHU_MLAD
, ECK0011B050CBSMsg1List.RRKS_NO_CHGECHU_MLAD
, ECK0011B050CBSMsg1List.GENE_ADD_DTM_CHGECHU_MLAD
, ECK0011B050CBSMsg1List.RRKS_NO_CONT_TELNO
, ECK0011B050CBSMsg1List.CONT_TELNO
, ECK0011B050CBSMsg1List.CONT_RRKS_SBT_CD
, ECK0011B050CBSMsg1List.CONT_NISNNO
, ECK0011B050CBSMsg1List.CONT_FAX_NO
, ECK0011B050CBSMsg1List.GENE_ADD_DTM_CONT_TELNO
, ECK0011B050CBSMsg1List.SVC_KEI_NO
, ECK0011B050CBSMsg1List.UPD_DTM_SVC_KEI
// ANK-4009-00-00 ADD START
, ECK0011B050CBSMsg1List.LINE_RNKI_UM
// ANK-4009-00-00 ADD END
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECK0011B050CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("ECK0011B050CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(ECK0011B050CBSMsg.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(ECK0011B050CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}