/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKK0081B536TPDA
* ソースファイル名：JSYejbEKK0081B536TPDA.java
* 作成者 ：EK918887
* 日付 ：2015年06月15日
*＜機能概要＞
* サービス契約一覧照会(CIS)テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* ｖ16.01.00 2015/08/17 FJ)野々下 IT1-2015-0000081対応
* ｖ16.02.00 2015/09/03 FJ)熊谷 IT1-2015-0000092対応
* ｖ16.03.00 2016/01/28 FJ)真崎 ST-2016-0000018対応
* ｖ29.00.00 2016/10/04 FJ)藤本 【ANK-2990-00-00】【eo電気】同時申込・量販訪販対応（STEP2-2）
* ｖ33.00.00 2017/08/01 FJ)河邊 【OM-2017-0000787】電力API受付番号返却不備
* ｖ34.00.00 2017/08/10 FJ)山地 【ANK-3210-00-00】【eo電気】電気単独対応（STEP3）
* ｖ71.00.00 2024/03/01 FJ) 永瀬 ANK-4543-00-00 NTT卸対応：Step0
* ｖ73.00.00 2024/07/22 GDC)ml.alcantara 【ANK-4427-00-00】NTT卸対応：Step1
*
********************************************************************************/
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.EKK0081B536CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0081B536CBSMsg1List;
import eo.ejb.cbm.entity.KK0081ETMsg;
import eo.ejb.common.JPCModelCommon;
/**
*
* テンプレートDBアクセス部品 EKK0081B536_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKK0081B536TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKK0081B536TPDA() {
}
/**
* EKK0081B536_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
// PreparedStatementのパラメータカウント
int iPCnt = 0;
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(KK0081ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" KK0081.SVC_KEI_NO ")
.append(" , KK0081.SVC_KEI_STAT ")
.append(" , (SELECT ZM0171_01.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ")
.append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00037' ")
.append(" AND ZM0171_01.CD_DIV = KK0081.SVC_KEI_STAT ")
.append(" AND ZM0171_01.MK_FLG = '0') AS SVC_KEI_STAT_NM ")
// ANK-3210-00-00 ADD START
.append(" , KK0081.SHOSA_DSL_FIN_CD ")
.append(" , (SELECT ZM0171_01.CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ")
.append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00469' ")
.append(" AND ZM0171_01.CD_DIV = KK0081.SHOSA_DSL_FIN_CD ")
.append(" AND ZM0171_01.MK_FLG = '0') AS SHOSA_DSL_FIN_CD_NM ")
// ANK-3210-00-00 ADD END
.append(" , KK0081.SVC_CD ")
.append(" , (SELECT KK0751.SVC_NM ")
.append(" FROM KK_M_SVC KK0751 ")
.append(" WHERE KK0751.SVC_CD = KK0081.SVC_CD ")
.append(" AND KK0751.RSV_APLY_YMD || KK0751.GENE_ADD_DTM = ")
.append(" (SELECT MAX(KK0751_GENE.RSV_APLY_YMD || KK0751_GENE.GENE_ADD_DTM) AS KK0751_MAX ")
.append(" FROM KK_M_SVC KK0751_GENE ")
.append(" WHERE KK0751_GENE.SVC_CD = KK0751.SVC_CD ")
.append(" AND KK0751_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0751_GENE.MK_FLG= '0')) AS SVC_NM ")
// IT1-2015-0000081 MOD START
// .append(" , KK0081.PCRS_CD ")
// .append(" , (SELECT KK0791.PCRS_NM ")
// .append(" FROM KK_M_PCRS KK0791 ")
// .append(" WHERE KK0791.PCRS_CD = KK0081.PCRS_CD ")
// .append(" AND KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM = ")
// .append(" (SELECT 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')) AS PCRS_NM ")
.append(" , KK0081.PRC_GRP_CD ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
//ANK-4427-00-00 MOD START
//.append(" , CASE WHEN KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO THEN KK3521_2.PRC_GRP_NM")
.append(" , CASE ")
.append(" WHEN (SELECT KK0791.NTTORS_DIV FROM KK_M_PCRS KK0791 WHERE KK0791.PCRS_CD = KK0081.PCRS_CD) IS NOT NULL THEN ")
.append(" (SELECT ZM0321.WORK_PARAM_SETTE_VALUE FROM ZM_M_WORK_PARAM_KNRI ZM0321 ")
.append(" WHERE ZM0321.WORK_PARAM_ID = 'KK_NTTORS_C_GRPNM_01') ")
.append(" ELSE CASE ")
.append(" WHEN KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO THEN KK3521_2.PRC_GRP_NM ")
//ANK-4427-00-00 MOD END
.append(" ELSE")
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
.append(" (SELECT KK0781.PRC_GRP_NM ")
.append(" FROM KK_M_PRC_GRP KK0781 ")
.append(" WHERE KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD ")
.append(" AND KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM = ")
.append(" (SELECT MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ")
.append(" FROM KK_M_PRC_GRP KK0781_GENE ")
.append(" WHERE KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ")
.append(" AND KK0781_GENE.RSV_APLY_YMD <= ? ")
//ANK-4543-00-00 NTT卸対応：Step0 MOD START
// .append(" AND KK0781_GENE.MK_FLG= '0')) AS PRC_GRP_NM ")
//ANK-4427-00-00 MOD START
//.append(" AND KK0781_GENE.MK_FLG= '0')) END AS PRC_GRP_NM ")
.append(" AND KK0781_GENE.MK_FLG= '0')) END ")
.append(" END AS PRC_GRP_NM ")
//ANK-4427-00-00 MOD END
//ANK-4543-00-00 NTT卸対応：Step0 MOD END
.append(" , KK0081.PCRS_CD ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
.append(" , CASE WHEN KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO THEN KK3521_2.PCRS_NM")
.append(" ELSE")
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
.append(" (SELECT KK0791.PCRS_NM ")
.append(" FROM KK_M_PCRS KK0791 ")
.append(" WHERE KK0791.PCRS_CD = KK0081.PCRS_CD ")
.append(" AND KK0791.RSV_APLY_YMD || KK0791.GENE_ADD_DTM = ")
.append(" (SELECT 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 <= ? ")
//ANK-4543-00-00 NTT卸対応：Step0 MOD START
// .append(" AND KK0791_GENE.MK_FLG= '0')) AS PCRS_NM ")
.append(" AND KK0791_GENE.MK_FLG= '0')) END AS PCRS_NM ")
//ANK-4543-00-00 NTT卸対応：Step0 MOD END
// IT1-2015-0000081 MOD END
.append(" , KK0081.PPLAN_CD ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
.append(" , CASE WHEN KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO THEN KK3521_2.PPLAN_NM")
.append(" ELSE")
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
.append(" KK0591.PPLAN_NM ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
.append(" END AS PPLAN_NM")
.append(" , CASE")
.append(" WHEN KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO THEN KK3521_2.ID_TCHISHO_PRT_YO_PPLAN_NM")
.append(" ELSE")
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
.append(" KK0591.ID_TCHISHO_PRT_YO_PPLAN_NM ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
.append(" END AS ID_TCHISHO_PRT_YO_PPLAN_NM")
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
.append(" , KK0081.SEIRI_NO ")
// IT1-2015-0000081 MOD START
// .append(" ")
.append(" , KK0011MIN.MSKM_YMD ")
// IT1-2015-0000081 MOD END
// ANK-2990-00-00 ADD START
.append(" , KK0021MIN.MSKMSHO_NO")
// ANK-2990-00-00 ADD END
.append(" , KK0251.svc_kei_kaisen_ucwk_no ")
.append(" , KK0251.kaisen_plc_kana ")
.append(" , KK0251.kaisen_place_ad_cd ")
.append(" , KK0251.kaisen_place_pcd ")
.append(" , KK0251.kaisen_place_state_nm ")
.append(" , KK0251.kaisen_place_city_nm ")
.append(" , KK0251.kaisen_place_oaztsu_nm ")
.append(" , KK0251.kaisen_place_azcho_nm ")
.append(" , KK0251.kaisen_place_bnchigo ")
.append(" , KK0251.kaisen_place_adrttm ")
.append(" , KK0251.kaisen_place_adrrm ")
.append(" , KK0251.kaisen_place_telno ")
.append(" , KK0251.kaisen_place_no ")
.append(" , KK0251.xzahyo ")
.append(" , KK0251.yzahyo ")
.append(" , KK0251.lgtd ")
.append(" , KK0251.lttd ")
.append(" , KK0251.zahyo_hosei_um ")
// IT1-2015-0000081 DEL START
// .append(" ")
// .append(" , KK0011MIN.MSKM_YMD ")
// .append(" ")
// IT1-2015-0000081 DEL END
.append(" , KK0251ITENS.svc_kei_kaisen_ucwk_no AS itns_svkei_kisn_ucwk_no ")
.append(" , KK0251ITENS.kaisen_plc_kana AS itns_kisn_plc_kana ")
.append(" , KK0251ITENS.kaisen_place_ad_cd AS itns_kisn_plc_ad_cd ")
.append(" , KK0251ITENS.kaisen_place_pcd AS itns_kisn_plc_pcd ")
.append(" , KK0251ITENS.kaisen_place_state_nm AS itns_kisn_plc_state_nm ")
.append(" , KK0251ITENS.kaisen_place_city_nm AS itns_kisn_plc_city_nm ")
.append(" , KK0251ITENS.kaisen_place_oaztsu_nm AS itns_kisn_plc_oaztsu_nm ")
.append(" , KK0251ITENS.kaisen_place_azcho_nm AS itns_kisn_plc_azcho_nm ")
.append(" , KK0251ITENS.kaisen_place_bnchigo AS itns_kisn_plc_bnchigo ")
.append(" , KK0251ITENS.kaisen_place_adrttm AS itns_kisn_plc_adrttm ")
.append(" , KK0251ITENS.kaisen_place_adrrm AS itns_kisn_plc_adrrm ")
.append(" , KK0251ITENS.kaisen_place_telno AS itns_kisn_plc_telno ")
.append(" , KK0251ITENS.kaisen_place_no AS itns_kisn_plc_no ")
.append(" , KK0251ITENS.xzahyo AS itns_xzahyo ")
.append(" , KK0251ITENS.yzahyo AS itns_yzahyo ")
.append(" , KK0251ITENS.lgtd AS itns_lgtd ")
.append(" , KK0251ITENS.lttd AS itns_lttd ")
.append(" , KK0251ITENS.zahyo_hosei_um AS itns_zahyo_hosei_um ")
.append(" ")
.append(" , KK0491.seiky_kei_no ")
.append(" , KK0491.seiky_way_cd ")
.append(" , KK0491.seiky_kei_kana ")
.append(" , (SELECT KK0531.crecard_comp_nm ")
.append(" FROM KK_M_CRECARD_COMP KK0531 ")
.append(" WHERE KK0531.CRECARD_COMP_CD = KK0521.CRECARD_COMP_CD ")
// ST-2016-0000018 ADD START
.append(" AND KK0531.CRECARD_JIGYO_CD = KK0521.CRECARD_JIGYO_CD ")
// ST-2016-0000018 ADD END
.append(" AND KK0531.RSV_APLY_YMD || KK0531.GENE_ADD_DTM = ")
.append(" (SELECT MAX(KK0531_GENE.RSV_APLY_YMD || KK0531_GENE.GENE_ADD_DTM) AS KK0531_MAX ")
.append(" FROM KK_M_CRECARD_COMP KK0531_GENE ")
.append(" WHERE KK0531_GENE.CRECARD_COMP_CD = KK0531.CRECARD_COMP_CD ")
// ST-2016-0000018 ADD START
.append(" AND KK0531_GENE.CRECARD_JIGYO_CD = KK0531.CRECARD_JIGYO_CD ")
// ST-2016-0000018 ADD END
.append(" AND KK0531_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0531_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0531_GENE.CRECARD_COMP_CD )) AS crecard_comp_nm ")
.append(" , (SELECT KK0511.bank_nm ")
.append(" FROM KK_M_BANK KK0511 ")
.append(" WHERE KK0511.BANK_CD = KK0501.BANK_CD ")
// ST-2016-0000018 ADD START
.append(" AND KK0511.BANK_SHITEN_CD = KK0501.BANK_SHITEN_CD ")
// ST-2016-0000018 ADD END
.append(" AND KK0511.RSV_APLY_YMD || KK0511.GENE_ADD_DTM = ")
.append(" (SELECT MAX(KK0511_GENE.RSV_APLY_YMD || KK0511_GENE.GENE_ADD_DTM) AS KK0511_MAX ")
.append(" FROM KK_M_BANK KK0511_GENE ")
.append(" WHERE KK0511_GENE.BANK_CD = KK0511.BANK_CD ")
// ST-2016-0000018 ADD START
.append(" AND KK0511_GENE.BANK_SHITEN_CD = KK0511.BANK_SHITEN_CD ")
// ST-2016-0000018 ADD END
.append(" AND KK0511_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0511_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0511_GENE.BANK_CD )) AS bank_nm ")
.append(" ")
.append(" , KK0501.new_cd ")
.append(" , CASE WHEN KK0081.PRC_GRP_CD = '17' ")
.append(" THEN (SELECT KK2961.SVKEI_GRP_SKBT_NO ")
.append(" FROM KK_T_SVKEI_GRP_SETE KK2961 ")
.append(" WHERE KK0081.SVC_KEI_NO = KK2961.SVC_KEI_NO ")
.append(" AND KK2961.SVKEI_GRP_SBT_CD = '01' ")
.append(" AND KK2961.MK_FLG = '0' ) ")
.append(" ELSE NULL ")
.append(" END AS same_usplc_svkei_kisuw_no ")
.append(" FROM ")
.append(" KK_T_SVC_KEI KK0081 ")
.append(" INNER JOIN KK_M_PPLAN KK0591 ")
.append(" ON KK0081.PPLAN_CD = KK0591.PPLAN_CD ")
.append(" AND KK0591.PPLAN_TSTAYMD <= ? ")
.append(" AND KK0591.PPLAN_TENDYMD >= ? ")
.append(" AND KK0591.MK_FLG= '0' ")
.append(" INNER JOIN ( ")
.append(" SELECT KK0081_01.* ")
.append(" FROM KK_T_SVC_KEI KK0081_01 ")
.append(" WHERE (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0081_02.SVC_KEI_NO, MIN(KK0081_02.RSV_APLY_YMD || KK0081_02.GENE_ADD_DTM) AS KK0081_MIN ")
.append(" FROM KK_T_SVC_KEI KK0081_02 ")
.append(" WHERE KK0081_02.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ")
.append(" AND KK0081_02.RSV_APLY_YMD <= ? ")
.append(" AND KK0081_02.RSV_APLY_CD = '2' ")
.append(" AND KK0081_02.MK_FLG = '0' ")
.append(" GROUP BY KK0081_02.SVC_KEI_NO) ")
.append(" ) KK0081MIN ")
.append(" ON KK0081MIN.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" ")
.append(" INNER JOIN ( ")
.append(" SELECT KK0021_01.* ")
.append(" FROM KK_T_MSKM_DTL KK0021_01 ")
.append(" WHERE (KK0021_01.MSKM_DTL_NO, KK0021_01.GENE_ADD_DTM) = ")
// OM-2017-0000787 2017/08/01 MOD START
// .append(" (SELECT KK0021_02.MSKM_DTL_NO, MIN(KK0021_02.GENE_ADD_DTM) AS KK0021_MIN ")
.append(" (SELECT KK0021_02.MSKM_DTL_NO, MAX(KK0021_02.GENE_ADD_DTM) AS KK0021_MIN ")
// OM-2017-0000787 2017/08/01 MOD END
.append(" FROM KK_T_MSKM_DTL KK0021_02 ")
.append(" WHERE KK0021_02.MSKM_DTL_NO = KK0021_01.MSKM_DTL_NO ")
.append(" AND KK0021_02.MK_FLG = '0' ")
.append(" GROUP BY KK0021_02.MSKM_DTL_NO) ")
.append(" ) KK0021MIN ")
.append(" ON KK0021MIN.MSKM_DTL_NO = KK0081MIN.MSKM_DTL_NO ")
.append(" ")
.append(" INNER JOIN ( ")
.append(" SELECT KK0011_01.* ")
.append(" FROM KK_T_MSKM KK0011_01 ")
.append(" WHERE (KK0011_01.MSKM_NO, KK0011_01.GENE_ADD_DTM) = ")
// OM-2017-0000787 2017/08/01 MOD START
// .append(" (SELECT KK0011_02.MSKM_NO, MIN(KK0011_02.GENE_ADD_DTM) AS KK0011_MIN ")
.append(" (SELECT KK0011_02.MSKM_NO, MAX(KK0011_02.GENE_ADD_DTM) AS KK0011_MIN ")
// OM-2017-0000787 2017/08/01 MOD END
.append(" FROM KK_T_MSKM KK0011_02 ")
.append(" WHERE KK0011_02.MSKM_NO = KK0011_01.MSKM_NO ")
.append(" AND KK0011_02.MK_FLG = '0' ")
.append(" GROUP BY KK0011_02.MSKM_NO) ")
.append(" ) KK0011MIN ")
.append(" ON KK0011MIN.MSKM_NO = KK0021MIN.MSKM_NO ")
.append(" INNER JOIN ( ")
.append(" SELECT KK0321_01.* ")
.append(" FROM KK_T_KAKINS KK0321_01 ")
.append(" WHERE KK0321_01.KAKINS_TSTAYMD <= ? ")
.append(" AND KK0321_01.KAKINS_TENDYMD >= ? ")
.append(" AND KK0321_01.MK_FLG = '0' ")
.append(" ) KK0321 ")
.append(" ON KK0321.SVC_KEI_NO = KK0081.SVC_KEI_NO ")
.append(" ")
.append(" INNER JOIN ( ")
.append(" SELECT KK0491_01.* ")
.append(" FROM KK_T_SEIKY_KEI KK0491_01 ")
.append(" WHERE (KK0491_01.SEIKY_KEI_NO, KK0491_01.RSV_APLY_YMD || KK0491_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0491_GENE.SEIKY_KEI_NO, MAX(KK0491_GENE.RSV_APLY_YMD || KK0491_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_SEIKY_KEI KK0491_GENE ")
.append(" WHERE KK0491_GENE.SEIKY_KEI_NO = KK0491_01.SEIKY_KEI_NO ")
.append(" AND KK0491_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0491_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0491_GENE.MK_FLG = '0' ")
.append(" GROUP BY KK0491_GENE.SEIKY_KEI_NO) ")
.append(" ) KK0491 ")
.append(" ON KK0491.SEIKY_KEI_NO = KK0321.SEIKY_KEI_NO ")
.append(" LEFT JOIN ( ")
.append(" SELECT KK0501_01.* ")
.append(" FROM KK_T_KOZA KK0501_01 ")
.append(" WHERE (KK0501_01.SEIKY_WAY_NO_KOZA, KK0501_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0501_GENEE.SEIKY_WAY_NO_KOZA, MAX(KK0501_GENEE.GENE_ADD_DTM) AS KK0501_01_MAX ")
.append(" FROM KK_T_KOZA KK0501_GENEE ")
.append(" WHERE KK0501_GENEE.SEIKY_WAY_NO_KOZA = KK0501_01.SEIKY_WAY_NO_KOZA ")
.append(" AND KK0501_GENEE.MK_FLG = '0' ")
.append(" GROUP BY KK0501_GENEE.SEIKY_WAY_NO_KOZA ) ")
.append(" ) KK0501 ")
.append(" ON KK0501.SEIKY_WAY_NO_KOZA = KK0491.SEIKY_WAY_NO_KOZA ")
.append(" LEFT JOIN ( ")
.append(" SELECT KK0521_01.* ")
.append(" FROM KK_T_CRECARD KK0521_01 ")
.append(" WHERE (KK0521_01.SEIKY_WAY_NO_CRECARD, KK0521_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0521_GENEE.SEIKY_WAY_NO_CRECARD, MAX(KK0521_GENEE.GENE_ADD_DTM) AS KK0521_01_MAX ")
.append(" FROM KK_T_CRECARD KK0521_GENEE ")
.append(" WHERE KK0521_GENEE.SEIKY_WAY_NO_CRECARD = KK0521_01.SEIKY_WAY_NO_CRECARD ")
.append(" AND KK0521_GENEE.MK_FLG = '0' ")
.append(" GROUP BY KK0521_GENEE.SEIKY_WAY_NO_CRECARD ) ")
.append(" ) KK0521 ")
.append(" ON KK0521.SEIKY_WAY_NO_CRECARD = KK0491.SEIKY_WAY_NO_CRECARD ")
// IT1-2015-0000092 MOD START
// .append(" LEFT JOIN ( ")
// .append(" SELECT KK0241_01.* ")
// .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ")
// .append(" WHERE (KK0241_01.SVC_KEI_KAISEN_UCWK_NO, KK0241_01.KAISEN_UCWK_USE_STAYMD) = ")
// .append(" (SELECT KK0241_02.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0241_02.KAISEN_UCWK_USE_STAYMD) ")
// .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_02 ")
// .append(" WHERE KK0241_02.SVC_KEI_KAISEN_UCWK_NO = KK0241_01.SVC_KEI_KAISEN_UCWK_NO ")
// .append(" AND KK0241_02.MK_FLG = '0' ")
// .append(" GROUP BY KK0241_02.SVC_KEI_KAISEN_UCWK_NO) ")
// .append(" ) KK0241 ")
// .append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ")
.append(" LEFT JOIN (")
.append(" SELECT KK0241_01.*")
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01")
.append(" WHERE")
.append(" KK0241_01.KAISEN_UCWK_USE_STAYMD <= ?")
.append(" AND (KK0241_01.SVC_KEI_NO,KAISEN_UCWK_USE_ENDYMD) = ")
.append(" (SELECT KK0241_02.SVC_KEI_NO, MAX(KK0241_02.KAISEN_UCWK_USE_ENDYMD) ")
.append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_02 ")
.append(" WHERE KK0241_02.SVC_KEI_NO = KK0241_01.SVC_KEI_NO")
.append(" AND KK0241_02.KAISEN_UCWK_USE_STAYMD <= ?")
.append(" AND KK0241_02.MK_FLG = '0'")
.append(" GROUP BY KK0241_02.SVC_KEI_NO)")
.append(" AND KK0241_01.MK_FLG = '0'")
.append(" ) KK0241 ")
.append(" ON KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO ")
// IT1-2015-0000092 MOD END
.append(" ")
.append(" LEFT JOIN ( ")
.append(" SELECT KK0251_01.* ")
.append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_01 ")
.append(" WHERE (KK0251_01.SVC_KEI_KAISEN_UCWK_NO, KK0251_01.GENE_ADD_DTM) = ")
.append(" (SELECT KK0251_02.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_02.GENE_ADD_DTM) AS KK0251_MAX ")
.append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_02 ")
.append(" WHERE KK0251_02.SVC_KEI_KAISEN_UCWK_NO = KK0251_01.SVC_KEI_KAISEN_UCWK_NO ")
.append(" AND KK0251_02.MK_FLG = '0' ")
.append(" GROUP BY KK0251_02.SVC_KEI_KAISEN_UCWK_NO) ")
.append(" ) KK0251 ")
.append(" ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT JOIN KK_T_ADCHG KK2091 ")
.append(" ON KK0251.SVC_KEI_KAISEN_UCWK_NO = KK2091.ITNM_SVKEI_KISUW_NO ")
.append(" AND KK2091.ITENS_SVKEI_KISUW_NO IS NOT NULL ")
.append(" AND KK2091.ADCHG_STAT = '001' ")
.append(" AND KK2091.MK_FLG = '0' ")
.append(" AND EXISTS ( ")
.append(" SELECT 1 ")
.append(" FROM KK_T_ADCHG_DTL KK2101 ")
.append(" WHERE KK2101.ADCHG_NO = KK2091.ADCHG_NO ")
.append(" AND KK2101.ADCHG_DTL_SBT_CD = '01' ")
.append(" AND KK2101.CHBF_SKBT_NO = KK0081.SVC_KEI_NO ")
.append(" AND KK2101.CHAF_SKBT_NO = KK0081.SVC_KEI_NO ")
.append(" AND KK2101.MK_FLG = '0' ")
.append(" ) ")
.append(" ")
.append(" LEFT JOIN ( ")
.append(" SELECT KK0251_03.* ")
.append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_03 ")
.append(" WHERE (KK0251_03.SVC_KEI_KAISEN_UCWK_NO, KK0251_03.GENE_ADD_DTM) = ")
.append(" (SELECT KK0251_04.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_04.GENE_ADD_DTM) AS KK0251_MAX ")
.append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_04 ")
.append(" WHERE KK0251_04.SVC_KEI_KAISEN_UCWK_NO = KK0251_03.SVC_KEI_KAISEN_UCWK_NO ")
.append(" AND KK0251_04.MK_FLG = '0' ")
.append(" GROUP BY KK0251_04.SVC_KEI_KAISEN_UCWK_NO) ")
.append(" ) KK0251ITENS ")
//ANK-4543-00-00 NTT卸対応：Step0 ADD START
.append(" ON KK2091.ITENS_SVKEI_KISUW_NO = KK0251ITENS.SVC_KEI_KAISEN_UCWK_NO ")
.append(" LEFT OUTER JOIN (")
.append(" SELECT KK3511.SVC_KEI_NO,KK3521.PRC_GRP_NM,KK3521.PPLAN_NM,KK3521.PCRS_NM, KK3521.ID_TCHISHO_PRT_YO_PPLAN_NM ")
.append(" FROM")
.append(" KK_T_NTTORS_KEI KK3511")
.append(" INNER JOIN KK_M_NTTORS_PCRS KK3521 ON")
.append(" KK3511.NTTORS_SVC_SBT = KK3521.NTTORS_SVC_SBT")
.append(" WHERE")
.append(" KK3511.GENE_ADD_DTM=(SELECT MAX(GENE_ADD_DTM) ")
.append(" FROM KK_T_NTTORS_KEI KK3511_GENE")
.append(" WHERE")
.append(" KK3511.SVC_KEI_NO=KK3511_GENE.SVC_KEI_NO AND")
.append(" KK3511.MK_FLG=0")
.append(" group by SVC_KEI_NO")
.append(")")
.append(") KK3521_2 ON (KK3521_2.SVC_KEI_NO=KK0081.SVC_KEI_NO)");
//ANK-4543-00-00 NTT卸対応：Step0 ADD END
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(EKK0081B536CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" WHERE ")
.append(" KK0081.SYSID = ? ");
if(!inMsg.isNull(EKK0081B536CBSMsg.KEY_SVC_CD_REQ)){
sql_Buff.append(" AND KK0081.SVC_CD = ? ");
}
sql_Buff.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_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) ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(EKK0081B536CBSMsg.FUNC_CODE) ) ) {
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// IT1-2015-0000092 ADD START
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
// IT1-2015-0000092 ADD END
// パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0081B536CBSMsg.KEY_SYSID));
if(!inMsg.isNull(EKK0081B536CBSMsg.KEY_SVC_CD_REQ)){
// パラメータの設定(ＫＥＹ＿サービスコードを指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0081B536CBSMsg.KEY_SVC_CD_REQ));
}
// パラメータの設定(共通部：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, ++iPCnt, JPCModelCommon.getOpeDate(inMsg, inContext, null));
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKK0081B536CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKK0081B536CBSMsg1List.SVC_KEI_NO
, EKK0081B536CBSMsg1List.SVC_KEI_STAT
, EKK0081B536CBSMsg1List.SVC_KEI_STAT_NM
// ANK-3210-00-00 ADD START
, EKK0081B536CBSMsg1List.SHOSA_DSL_FIN_CD
, EKK0081B536CBSMsg1List.SHOSA_DSL_FIN_CD_NM
// ANK-3210-00-00 ADD END
, EKK0081B536CBSMsg1List.SVC_CD
, EKK0081B536CBSMsg1List.SVC_NM
, EKK0081B536CBSMsg1List.PRC_GRP_CD
, EKK0081B536CBSMsg1List.PRC_GRP_NM
, EKK0081B536CBSMsg1List.PCRS_CD
, EKK0081B536CBSMsg1List.PCRS_NM
, EKK0081B536CBSMsg1List.PPLAN_CD
, EKK0081B536CBSMsg1List.PPLAN_NM
, EKK0081B536CBSMsg1List.ID_TCHISHO_PRT_YO_PPLAN_NM
, EKK0081B536CBSMsg1List.SEIRI_NO
, EKK0081B536CBSMsg1List.MSKM_YMD
// ANK-2990-00-00 ADD START
, EKK0081B536CBSMsg1List.MSKMSHO_NO
// ANK-2990-00-00 ADD END
, EKK0081B536CBSMsg1List.SVC_KEI_KAISEN_UCWK_NO
, EKK0081B536CBSMsg1List.KAISEN_PLC_KANA
, EKK0081B536CBSMsg1List.KAISEN_PLACE_AD_CD
, EKK0081B536CBSMsg1List.KAISEN_PLACE_PCD
, EKK0081B536CBSMsg1List.KAISEN_PLACE_STATE_NM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_CITY_NM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_OAZTSU_NM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_AZCHO_NM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_BNCHIGO
, EKK0081B536CBSMsg1List.KAISEN_PLACE_ADRTTM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_ADRRM
, EKK0081B536CBSMsg1List.KAISEN_PLACE_TELNO
, EKK0081B536CBSMsg1List.KAISEN_PLACE_NO
, EKK0081B536CBSMsg1List.XZAHYO
, EKK0081B536CBSMsg1List.YZAHYO
, EKK0081B536CBSMsg1List.LGTD
, EKK0081B536CBSMsg1List.LTTD
, EKK0081B536CBSMsg1List.ZAHYO_HOSEI_UM
, EKK0081B536CBSMsg1List.ITNS_SVKEI_KISN_UCWK_NO
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_KANA
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_AD_CD
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_PCD
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_STATE_NM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_CITY_NM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_OAZTSU_NM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_AZCHO_NM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_BNCHIGO
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_ADRTTM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_ADRRM
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_TELNO
, EKK0081B536CBSMsg1List.ITNS_KISN_PLC_NO
, EKK0081B536CBSMsg1List.ITNS_XZAHYO
, EKK0081B536CBSMsg1List.ITNS_YZAHYO
, EKK0081B536CBSMsg1List.ITNS_LGTD
, EKK0081B536CBSMsg1List.ITNS_LTTD
, EKK0081B536CBSMsg1List.ITNS_ZAHYO_HOSEI_UM
, EKK0081B536CBSMsg1List.SEIKY_KEI_NO
, EKK0081B536CBSMsg1List.SEIKY_WAY_CD
, EKK0081B536CBSMsg1List.SEIKY_KEI_KANA
, EKK0081B536CBSMsg1List.CRECARD_COMP_NM
, EKK0081B536CBSMsg1List.BANK_NM
, EKK0081B536CBSMsg1List.NEW_CD
, EKK0081B536CBSMsg1List.SAME_USPLC_SVKEI_KISUW_NO
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B536CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKK0081B536CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EKK0081B536CBSMsg.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(EKK0081B536CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}