/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbECN0131B005TPDA
* ソースファイル名：JSYejbECN0131B005TPDA.java
* 作成者 ：EK907777
* 日付 ：2012年02月27日
*＜機能概要＞
* プロバイダ請求利用設定一覧取得(PMP連携用)テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* v1.00.00
* v6.00.00 2013/10/15 FJ）小川 ANK-1587-00-00_ECプラットフォーム
* v8.00.00 2013/12/18 FJ）塚田 ANK-1584-00-00_L2-MVNO(本体)
* v8.00.00 2014/03/06 FJ）窪田 ST-2014-0000069
* v9.00.00 2014/08/05 FJ）矢野 ANK-2224-00-00_ECプラットフォーム/CKIFE020「お客様情報取得」の改修
* v12.00.00 2015/02/16 FJ）阪口 IT1-2015-0000009_対策33:照査OKデータのみを検索対象とする
* v16.00.00 2015/06/03 FJ) 頼経 【ANK-2480-00-00】対応 新電力
********************************************************************************/
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.common.util.JCKLcsCommonUtil;
import eo.ejb.cbs.cbsmsg.ECN0131B005CBSMsg;
import eo.ejb.cbs.cbsmsg.ECN0131B005CBSMsg1List;
import eo.ejb.cbm.entity.CN0131ETMsg;
import eo.ejb.common.JCCModelCommon;
/**
*
* テンプレートDBアクセス部品 ECN0131B005_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbECN0131B005TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbECN0131B005TPDA() {
}
/**
* ECN0131B005_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(CN0131ETMsg.getTableName());
// ANK-2224-00-00 MOD START
// ECPフラグ
boolean ecpFlg = false;
String operaterId = inMsg.getString(ECN0131B005CBSMsg.OPERATORID);
if (operaterId.length() >= 4) {
if( "ECP1".equals(operaterId.substring(operaterId.length()-4)) ||
"ECP2".equals(operaterId.substring(operaterId.length()-4)) ||
"ECP3".equals(operaterId.substring(operaterId.length()-4)) ){
ecpFlg = true;
}
}
JSYejbLog.outlog(inContext, JSYejbLog.DEBUG, this.getClass(), "OPERATORID:" + operaterId);
JSYejbLog.outlog(inContext, JSYejbLog.DEBUG, this.getClass(), "ecpFlg:" + ecpFlg );
// ANK-2224-00-00 MOD END
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ");
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(ECN0131B005CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" /*+ LEADING(CUST) INDEX(SVC KK_T_SVC_KEI_IDX_01) INDEX(KAKINS KK_T_KAKINS_IDX_01) */ ")
.append(" CUST.SYSID /* SYSID */ ")
.append(" ,CUST.CUST_NM /* お客様名 */ ")
.append(" ,CUST.CONT_ISP_USE_FAIL_FLG /* コンテンツISP利用不可フラグ */ ")
.append(" ,CUST.MMB_ISP_KSSAI_STAT_CD /* 会員ISP決済状態コード */ ")
.append(" ,SVC.SVC_KEI_NO /* サービス契約番号 */ ")
.append(" ,(SELECT TOKUSOKU_STAT /* 督促ステータス */ ")
.append(" FROM CH_T_TOKUSOKU ")
.append(" WHERE SEIKY_KEI_NO = KAKINS.SEIKY_KEI_NO ")
.append(" AND TOKUSOKU_STAYMD <= ? ")
.append(" AND TOKUSOKU_ENDYMD >= ? ")
.append(" AND MK_FLG = '0' ")
.append(" AND ROWNUM = 1) AS TOKUSOKU_STAT ")
.append(" ,SVC.SVC_CD /* サービスコード */ ")
.append(" ,SVC.PRC_GRP_CD /* 料金グループコード */ ")
.append(" ,PRCGRP.PRC_GRP_NM /* 料金グループ名 */ ")
.append(" ,(SELECT ISP_KSSAI_SVC_KEI_NO /* ISP決済サービス契約番号 */ ")
.append(" FROM CN_T_PAYWAY_INSUPORT ")
.append(" WHERE SYSID = SVC.SYSID ")
.append(" AND ISP_KSSAI_SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND PAYWAY_INPUT_SHIEN_CD = '2' ")
.append(" AND MK_FLG = '0' ")
.append(" AND ROWNUM = 1) AS ISP_KSSAI_SVC_KEI_NO ")
.append(" ,DECODE(SVC.SVC_CD,'51','04',SVC.SVC_CD) OLD_ICS_CONTRACT_CD /* 契約種別コード（旧ICS）*/ ")
.append(" ,CASE SVC.PRC_GRP_CD ")
.append(" WHEN '" + JCKStrConst.PRC_GRP_CD_EOH_TEL + "' THEN ICS_CD_SVC.OLD_ICS_HUKA_SVC_SBT_CD ")
.append(" ELSE ICS_CD_SVC.OLD_ICS_KEI_SVC_CD ")
.append(" END AS OLD_ICS_SVC_CD /* サービスコード（旧ICS）*/ ")
.append(" ,CASE SVC.PRC_GRP_CD ")
.append(" WHEN '" + JCKStrConst.PRC_GRP_CD_EOH_TEL + "' THEN '" + JCKStrConst.OLD_ICS_KEI_SVC_NM_EOH_TEL + "' ")
.append(" ELSE (SELECT CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ")
.append(" WHERE CD_SBT_CD = 'CD01259' ")
.append(" AND CD_DIV = ICS_CD_SVC.OLD_ICS_KEI_SVC_CD ")
.append(" AND CD_TSTAYMD <= ? ")
.append(" AND CD_TENDYMD >= ?) ")
.append(" END AS OLD_ICS_SVC_NM /* サービス名（旧ICS）*/ ")
.append(" ,KAKINS.SEIKY_KEI_NO /* 請求契約番号 */ ")
.append(" FROM CK_T_CUST CUST ")
.append(" /* サービス契約 */ ")
.append(" INNER JOIN KK_T_SVC_KEI SVC ")
.append(" ON SVC.SYSID = CUST.SYSID ")
// ANK-2224-00-00 MOD START
// .append(" AND SVC.SVC_CD IN ('01', '02', '03', '51') /*サービスコード（ネット、電話、テレビ、MVNO）*/ ")
// .append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_SVC_KEI_STAT_FOR_PMP, "'") + ") ")
// .append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
// ANK-2480-00-00 MOD START
// .append(" AND SVC.SVC_CD IN ('01', '02', '03', '51') /*サービスコード（ネット、電話、テレビ、MVNO）*/ ");
.append(" AND SVC.SVC_CD IN ('01', '02', '03', '05', '51') /*サービスコード（ネット、電話、テレビ、電気、MVNO）*/ ");
// ANK-2480-00-00 MOD END
if( ecpFlg ) {
sql_Buff.append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.SVC_KEI_STAT_FOR_PMP, "'") + ") ");
}else{
sql_Buff.append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_SVC_KEI_STAT_FOR_PMP, "'") + ") ");
}
// IT1-2015-0000009 MOD START
sql_Buff.append(" AND (SVC.SHOSA_DSL_FIN_CD IS NULL OR SVC.SHOSA_DSL_FIN_CD <> '2') ")
.append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
// sql_Buff.append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
// IT1-2015-0000009 MOD END
// ANK-2224-00-00 MOD END
.append(" (SELECT MAX(SVC_GENE.RSV_APLY_YMD || SVC_GENE.GENE_ADD_DTM) AS SVC_MAX ")
.append(" FROM KK_T_SVC_KEI SVC_GENE ")
.append(" WHERE SVC_GENE.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND SVC_GENE.RSV_APLY_YMD <= ? ")
.append(" AND SVC_GENE.RSV_APLY_CD = '2' ")
.append(" AND SVC_GENE.MK_FLG = '0') ")
.append(" /* 課金先 */ ")
.append(" INNER JOIN KK_T_KAKINS KAKINS ")
.append(" ON KAKINS.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND KAKINS.KAKINS_TSTAYMD <= ? ")
.append(" AND KAKINS.KAKINS_TENDYMD >= ? ")
.append(" AND KAKINS.MK_FLG = '0' ")
.append(" /* 料金グループ */ ")
.append(" INNER JOIN KK_M_PRC_GRP PRCGRP ")
.append(" ON PRCGRP.PRC_GRP_CD = SVC.PRC_GRP_CD ")
.append(" AND PRCGRP.RSV_APLY_YMD || PRCGRP.GENE_ADD_DTM = ")
.append(" (SELECT MAX(PRCGRP_GENE.RSV_APLY_YMD || PRCGRP_GENE.GENE_ADD_DTM) AS PRCGRP_MAX ")
.append(" FROM KK_M_PRC_GRP PRCGRP_GENE ")
.append(" WHERE PRCGRP_GENE.PRC_GRP_CD = PRCGRP.PRC_GRP_CD ")
.append(" AND PRCGRP_GENE.RSV_APLY_YMD <= ? ")
.append(" AND PRCGRP_GENE.PRC_GRP_TSTAYMD <= ? ")
.append(" AND PRCGRP_GENE.PRC_GRP_TENDYMD >= ? ")
.append(" AND PRCGRP_GENE.MK_FLG = '0') ")
.append(" /* 料金コース */ ")
.append(" INNER JOIN KK_M_PCRS PCRS ")
.append(" ON PCRS.PCRS_CD = SVC.PCRS_CD ")
.append(" /* 旧ICSコード変換 */ ")
.append(" INNER JOIN KK_M_OLD_ICS_CD_CHG ICS_CD_SVC ")
.append(" ON ICS_CD_SVC.PRC_GRP_CD = SVC.PRC_GRP_CD AND ICS_CD_SVC.PCRS_CD = SVC.PCRS_CD AND ICS_CD_SVC.PPLAN_CD = SVC.PPLAN_CD ")
.append(" WHERE CUST.SYSID = ? ")
.append(" AND CUST.CUST_STAT = '" + JCKStrConst.CUST_STATUS_ADD_ZUMI + "' ")
.append(" AND CUST.CUST_NTAIKAI_CD = '" + JCKStrConst.CUST_NTAIKAI_CD_JOIN + "' ")
.append(" AND CUST.MK_FLG = '0' ")
.append(" AND CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM = ")
.append(" (SELECT MAX(CUST_GENE.RSV_APLY_YMD || CUST_GENE.GENE_ADD_DTM) AS CUST_MAX ")
.append(" FROM CK_T_CUST CUST_GENE ")
.append(" WHERE CUST_GENE.SYSID = CUST.SYSID ")
.append(" AND CUST_GENE.RSV_APLY_YMD <= ? ")
.append(" AND CUST_GENE.RSV_APLY_CD = '2' ")
.append(" AND CUST_GENE.MK_FLG = '0') ")
.append(" AND PCRS.RSV_APLY_YMD || PCRS.GENE_ADD_DTM =")
.append(" (SELECT MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_M_PCRS GENE ")
.append(" WHERE GENE.PCRS_CD = PCRS.PCRS_CD ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
// ANK-2480-00-00 MOD START
//.append(" AND ((PCRS.ID_TCHISHO_HAK_SKBT_CD <> '0' AND SVC.SVC_CD <> '51') ")
//.append(" OR (PCRS.ID_TCHISHO_HAK_SKBT_CD = '0' AND SVC.SVC_CD = '51')) ")
.append(" AND ((PCRS.ID_TCHISHO_HAK_SKBT_CD <> '0' AND SVC.SVC_CD not in ('05', '51')) ")
.append(" OR (PCRS.ID_TCHISHO_HAK_SKBT_CD = '0' AND SVC.SVC_CD in ('05', '51'))) ")
// ANK-2480-00-00 MOD END
.append(" AND NVL(ICS_CD_SVC.SOKU_WARI_UM, '0') = ")
.append(" (SELECT CASE ")
.append(" WHEN EXISTS ")
.append(" (SELECT /*+ LEADING(WRISVC_TG_KEI) */ ")
.append(" 1 ")
.append(" FROM KK_T_WRISVC_TG_KEI WRISVC_TG_KEI ")
.append(" INNER JOIN ")
.append(" KK_T_WRIB_SVC_KEI WRIB_SVC_KEI ON WRISVC_TG_KEI.WRIB_SVC_KEI_NO = WRIB_SVC_KEI.WRIB_SVC_KEI_NO ")
.append(" INNER JOIN ")
.append(" KK_M_WRIB_SVC WRIB_SVC ON WRIB_SVC_KEI.WRIB_SVC_CD = WRIB_SVC.WRIB_SVC_CD ")
.append(" WHERE WRISVC_TG_KEI.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND WRISVC_TG_KEI.GENE_ADD_DTM = ")
.append(" (SELECT /*+ INDEX(GENE KK_T_WRISVC_TG_KEI_PK_01) */ ")
.append(" MAX(GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRISVC_TG_KEI GENE ")
.append(" WHERE GENE.WRIB_SVC_KEI_NO = WRISVC_TG_KEI.WRIB_SVC_KEI_NO ")
.append(" AND GENE.WRIB_SVC_TRGT_KEI_NO = WRISVC_TG_KEI.WRIB_SVC_TRGT_KEI_NO ")
.append(" ) ")
.append(" AND WRISVC_TG_KEI.WRISVC_TG_KEI_TSTAYMD <= ? ")
.append(" AND WRISVC_TG_KEI.WRISVC_TG_KEI_TENDYMD >= ? ")
.append(" AND WRISVC_TG_KEI.MK_FLG = '0' ")
.append(" AND WRIB_SVC_KEI.SYSID = ? ")
.append(" AND WRIB_SVC_KEI.WRIB_SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_WRIB_SVC_KEI_STAT_FOR_PMP, "'") + ") /* サービス提供中 */ ")
.append(" AND WRIB_SVC_KEI.RSV_APLY_YMD || WRIB_SVC_KEI.GENE_ADD_DTM = ")
.append(" (SELECT /*+ INDEX(GENE KK_T_WRIB_SVC_KEI_PK_01) */ ")
.append(" MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRIB_SVC_KEI GENE ")
.append(" WHERE GENE.WRIB_SVC_KEI_NO = WRIB_SVC_KEI.WRIB_SVC_KEI_NO ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" AND WRIB_SVC.WRIB_TYPE_CD = '" + JCKStrConst.WRIB_TYPE_CD_SOKU_WARI + "' /* 即割り */ ")
.append(" AND WRIB_SVC.RSV_APLY_YMD || WRIB_SVC.GENE_ADD_DTM = ")
.append(" (SELECT MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_M_WRIB_SVC GENE ")
.append(" WHERE GENE.WRIB_SVC_CD = WRIB_SVC.WRIB_SVC_CD ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" ) THEN '1' ")
.append(" ELSE '0' ")
.append(" END AS SOKU_WARI_UM ")
.append(" FROM DUAL ")
.append(" ) ")
.append(" AND ICS_CD_SVC.OLD_ICS_CD_CHG_TSTAYMD <= ? ")
.append(" AND ICS_CD_SVC.OLD_ICS_CD_CHG_TENDYMD >= ? ")
.append(" AND ICS_CD_SVC.RNK_SAKI_SYS_SKBT_CD = '" + JCKStrConst.RNK_SAKI_SYS_SKBT_CD_AXM + "' ")
.append(" AND ICS_CD_SVC.MK_FLG = '0' ")
.append(" ORDER BY PRCGRP.DSP_JUN, ICS_CD_SVC.OLD_ICS_KEI_SVC_CD, ICS_CD_SVC.OLD_ICS_PPLAN_CD ");
}
if ( "2".equals( inMsg.getObject(ECN0131B005CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" /*+ LEADING(CUST) INDEX(SVC KK_T_SVC_KEI_IDX_01) INDEX(KAKINS KK_T_KAKINS_IDX_01) */ ")
.append(" CUST.SYSID /* SYSID */ ")
.append(" ,CUST.CUST_NM /* お客様名 */ ")
.append(" ,CUST.CONT_ISP_USE_FAIL_FLG /* コンテンツISP利用不可フラグ */ ")
.append(" ,CUST.MMB_ISP_KSSAI_STAT_CD /* 会員ISP決済状態コード */ ")
.append(" ,SVC.SVC_KEI_NO /* サービス契約番号 */ ")
.append(" ,(SELECT TOKUSOKU_STAT /* 督促ステータス */ ")
.append(" FROM CH_T_TOKUSOKU ")
.append(" WHERE SEIKY_KEI_NO = KAKINS.SEIKY_KEI_NO ")
.append(" AND TOKUSOKU_STAYMD <= ? ")
.append(" AND TOKUSOKU_ENDYMD >= ? ")
.append(" AND MK_FLG = '0' ")
.append(" AND ROWNUM = 1) AS TOKUSOKU_STAT ")
.append(" ,SVC.SVC_CD /* サービスコード */ ")
.append(" ,SVC.PRC_GRP_CD /* 料金グループコード */ ")
.append(" ,PRCGRP.PRC_GRP_NM /* 料金グループ名 */ ")
.append(" ,(SELECT ISP_KSSAI_SVC_KEI_NO /* ISP決済サービス契約番号 */ ")
.append(" FROM CN_T_PAYWAY_INSUPORT ")
.append(" WHERE SYSID = SVC.SYSID ")
.append(" AND ISP_KSSAI_SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND PAYWAY_INPUT_SHIEN_CD = '2' ")
.append(" AND MK_FLG = '0' ")
.append(" AND ROWNUM = 1) AS ISP_KSSAI_SVC_KEI_NO ")
.append(" ,DECODE(SVC.SVC_CD,'51','04',SVC.SVC_CD) OLD_ICS_CONTRACT_CD /* 契約種別コード（旧ICS）*/ ")
.append(" ,CASE SVC.PRC_GRP_CD ")
.append(" WHEN '" + JCKStrConst.PRC_GRP_CD_EOH_TEL + "' THEN ICS_CD_SVC.OLD_ICS_HUKA_SVC_SBT_CD || '000' ")
.append(" ELSE ICS_CD_SVC.OLD_ICS_KEI_SVC_CD || ICS_CD_SVC.OLD_ICS_PPLAN_CD ")
.append(" END AS OLD_ICS_SVC_CD_FULL /* サービスコード（旧ICS）*/")
.append(" ,CASE SVC.PRC_GRP_CD ")
.append(" WHEN '" + JCKStrConst.PRC_GRP_CD_EOH_TEL + "' THEN '" + JCKStrConst.OLD_ICS_KEI_SVC_NM_EOH_TEL + "' ")
.append(" ELSE (SELECT CD_DIV_NM ")
.append(" FROM ZM_M_CD_NM_KANRI ")
.append(" WHERE CD_SBT_CD = 'CD01259' ")
.append(" AND CD_DIV = ICS_CD_SVC.OLD_ICS_KEI_SVC_CD ")
.append(" AND CD_TSTAYMD <= ? ")
.append(" AND CD_TENDYMD >= ?) ")
.append(" END AS OLD_ICS_SVC_NM /* サービス名（旧ICS）*/")
.append(" ,KAKINS.SEIKY_KEI_NO /* 請求契約番号 */ ")
.append(" FROM CK_T_CUST CUST ")
.append(" /* サービス契約 */ ")
.append(" INNER JOIN KK_T_SVC_KEI SVC ")
.append(" ON SVC.SYSID = CUST.SYSID ")
// ANK-2224-00-00 MOD START
// .append(" AND SVC.SVC_CD = ? /*サービスコード（ネット）*/ ")
// .append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_SVC_KEI_STAT_FOR_PMP, "'") + ") ")
// .append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
.append(" AND SVC.SVC_CD = ? /*サービスコード（ネット）*/ ");
if( ecpFlg ) {
sql_Buff.append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.SVC_KEI_STAT_FOR_PMP, "'") + ") ");
}else{
sql_Buff.append(" AND SVC.SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_SVC_KEI_STAT_FOR_PMP, "'") + ") ");
}
// IT1-2015-0000009 MOD START
sql_Buff.append(" AND (SVC.SHOSA_DSL_FIN_CD IS NULL OR SVC.SHOSA_DSL_FIN_CD <> '2') ")
.append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
// sql_Buff.append(" AND SVC.RSV_APLY_YMD || SVC.GENE_ADD_DTM = ")
// IT1-2015-0000009 MOD END
// ANK-2224-00-00 MOD END
.append(" (SELECT MAX(SVC_GENE.RSV_APLY_YMD || SVC_GENE.GENE_ADD_DTM) AS SVC_MAX ")
.append(" FROM KK_T_SVC_KEI SVC_GENE ")
.append(" WHERE SVC_GENE.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND SVC_GENE.RSV_APLY_YMD <= ? ")
.append(" AND SVC_GENE.RSV_APLY_CD = '2' ")
.append(" AND SVC_GENE.MK_FLG = '0') ")
.append(" /* 課金先 */ ")
.append(" INNER JOIN KK_T_KAKINS KAKINS ")
.append(" ON KAKINS.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND KAKINS.KAKINS_TSTAYMD <= ? ")
.append(" AND KAKINS.KAKINS_TENDYMD >= ? ")
.append(" AND KAKINS.MK_FLG = '0' ")
.append(" /* 料金グループ */ ")
.append(" INNER JOIN KK_M_PRC_GRP PRCGRP ")
.append(" ON PRCGRP.PRC_GRP_CD = SVC.PRC_GRP_CD ")
.append(" AND PRCGRP.RSV_APLY_YMD || PRCGRP.GENE_ADD_DTM = ")
.append(" (SELECT MAX(PRCGRP_GENE.RSV_APLY_YMD || PRCGRP_GENE.GENE_ADD_DTM) AS PRCGRP_MAX ")
.append(" FROM KK_M_PRC_GRP PRCGRP_GENE ")
.append(" WHERE PRCGRP_GENE.PRC_GRP_CD = PRCGRP.PRC_GRP_CD ")
.append(" AND PRCGRP_GENE.RSV_APLY_YMD <= ? ")
.append(" AND PRCGRP_GENE.PRC_GRP_TSTAYMD <= ? ")
.append(" AND PRCGRP_GENE.PRC_GRP_TENDYMD >= ? ")
.append(" AND PRCGRP_GENE.MK_FLG = '0') ")
.append(" /* 料金コース */ ")
.append(" INNER JOIN KK_M_PCRS PCRS ")
.append(" ON PCRS.PCRS_CD = SVC.PCRS_CD ")
.append(" /* 旧ICSコード変換 */ ")
.append(" INNER JOIN KK_M_OLD_ICS_CD_CHG ICS_CD_SVC ")
.append(" ON ICS_CD_SVC.PRC_GRP_CD = SVC.PRC_GRP_CD AND ICS_CD_SVC.PCRS_CD = SVC.PCRS_CD AND ICS_CD_SVC.PPLAN_CD = SVC.PPLAN_CD ")
.append(" WHERE CUST.SYSID = ? ")
.append(" AND CUST.CUST_STAT = '" + JCKStrConst.CUST_STATUS_ADD_ZUMI + "' ")
.append(" AND CUST.CUST_NTAIKAI_CD = '" + JCKStrConst.CUST_NTAIKAI_CD_JOIN + "' ")
.append(" AND CUST.MK_FLG = '0' ")
.append(" AND CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM = ")
.append(" (SELECT MAX(CUST_GENE.RSV_APLY_YMD || CUST_GENE.GENE_ADD_DTM) AS CUST_MAX ")
.append(" FROM CK_T_CUST CUST_GENE ")
.append(" WHERE CUST_GENE.SYSID = CUST.SYSID ")
.append(" AND CUST_GENE.RSV_APLY_YMD <= ? ")
.append(" AND CUST_GENE.RSV_APLY_CD = '2' ")
.append(" AND CUST_GENE.MK_FLG = '0') ")
.append(" AND PCRS.RSV_APLY_YMD || PCRS.GENE_ADD_DTM =")
.append(" (SELECT MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_M_PCRS GENE ")
.append(" WHERE GENE.PCRS_CD = PCRS.PCRS_CD ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
// ANK-2480-00-00 MOD START
//.append(" AND ((PCRS.ID_TCHISHO_HAK_SKBT_CD <> '0' AND SVC.SVC_CD <> '51') ")
//.append(" OR (PCRS.ID_TCHISHO_HAK_SKBT_CD = '0' AND SVC.SVC_CD = '51')) ")
.append(" AND PCRS.ID_TCHISHO_HAK_SKBT_CD <> '0' ")
// ANK-2480-00-00 MOD END
.append(" AND NVL(ICS_CD_SVC.SOKU_WARI_UM, '0') = ")
.append(" (SELECT CASE ")
.append(" WHEN EXISTS ")
.append(" (SELECT /*+ LEADING(WRISVC_TG_KEI) */ ")
.append(" 1 ")
.append(" FROM KK_T_WRISVC_TG_KEI WRISVC_TG_KEI ")
.append(" INNER JOIN ")
.append(" KK_T_WRIB_SVC_KEI WRIB_SVC_KEI ON WRISVC_TG_KEI.WRIB_SVC_KEI_NO = WRIB_SVC_KEI.WRIB_SVC_KEI_NO ")
.append(" INNER JOIN ")
.append(" KK_M_WRIB_SVC WRIB_SVC ON WRIB_SVC_KEI.WRIB_SVC_CD = WRIB_SVC.WRIB_SVC_CD ")
.append(" WHERE WRISVC_TG_KEI.SVC_KEI_NO = SVC.SVC_KEI_NO ")
.append(" AND WRISVC_TG_KEI.GENE_ADD_DTM = ")
.append(" (SELECT /*+ INDEX(GENE KK_T_WRISVC_TG_KEI_PK_01) */ ")
.append(" MAX(GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRISVC_TG_KEI GENE ")
.append(" WHERE GENE.WRIB_SVC_KEI_NO = WRISVC_TG_KEI.WRIB_SVC_KEI_NO ")
.append(" AND GENE.WRIB_SVC_TRGT_KEI_NO = WRISVC_TG_KEI.WRIB_SVC_TRGT_KEI_NO ")
.append(" ) ")
.append(" AND WRISVC_TG_KEI.WRISVC_TG_KEI_TSTAYMD <= ? ")
.append(" AND WRISVC_TG_KEI.WRISVC_TG_KEI_TENDYMD >= ? ")
.append(" AND WRISVC_TG_KEI.MK_FLG = '0' ")
.append(" AND WRIB_SVC_KEI.SYSID = ? ")
.append(" AND WRIB_SVC_KEI.WRIB_SVC_KEI_STAT IN (" + JCKLcsCommonUtil.convert2String(JCKStrConst.TKCHU_WRIB_SVC_KEI_STAT_FOR_PMP, "'") + ") /* サービス提供中 */ ")
.append(" AND WRIB_SVC_KEI.RSV_APLY_YMD || WRIB_SVC_KEI.GENE_ADD_DTM = ")
.append(" (SELECT /*+ INDEX(GENE KK_T_WRIB_SVC_KEI_PK_01) */ ")
.append(" MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_WRIB_SVC_KEI GENE ")
.append(" WHERE GENE.WRIB_SVC_KEI_NO = WRIB_SVC_KEI.WRIB_SVC_KEI_NO ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" AND WRIB_SVC.WRIB_TYPE_CD = '" + JCKStrConst.WRIB_TYPE_CD_SOKU_WARI + "' /* 即割り */ ")
.append(" AND WRIB_SVC.RSV_APLY_YMD || WRIB_SVC.GENE_ADD_DTM = ")
.append(" (SELECT MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) ")
.append(" FROM KK_M_WRIB_SVC GENE ")
.append(" WHERE GENE.WRIB_SVC_CD = WRIB_SVC.WRIB_SVC_CD ")
.append(" AND GENE.RSV_APLY_YMD <= ? ")
.append(" AND GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" ) THEN '1' ")
.append(" ELSE '0' ")
.append(" END AS SOKU_WARI_UM ")
.append(" FROM DUAL ")
.append(" ) ")
.append(" AND ICS_CD_SVC.OLD_ICS_CD_CHG_TSTAYMD <= ? ")
.append(" AND ICS_CD_SVC.OLD_ICS_CD_CHG_TENDYMD >= ? ")
.append(" AND ICS_CD_SVC.RNK_SAKI_SYS_SKBT_CD = '" + JCKStrConst.RNK_SAKI_SYS_SKBT_CD_AXM + "' ")
.append(" AND ICS_CD_SVC.MK_FLG = '0' ")
.append(" ORDER BY PRCGRP.DSP_JUN, ICS_CD_SVC.OLD_ICS_KEI_SVC_CD, ICS_CD_SVC.OLD_ICS_PPLAN_CD ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// ##### 修正START #####
// 運用日付の取得
String opeDate = JCCModelCommon.getOpeDate(inMsg, null);
// ##### 修正 END #####
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(ECN0131B005CBSMsg.FUNC_CODE) ) ) {
int paramIdx = 0;
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, inMsg.getObject(ECN0131B005CBSMsg.KEY_SYSID));
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, inMsg.getObject(ECN0131B005CBSMsg.KEY_SYSID));
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
}
if ( "2".equals( inMsg.getString(ECN0131B005CBSMsg.FUNC_CODE) ) ) {
int paramIdx = 0;
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, JCKStrConst.SVC_CD_NET);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, inMsg.getObject(ECN0131B005CBSMsg.KEY_SYSID));
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, inMsg.getObject(ECN0131B005CBSMsg.KEY_SYSID));
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
CAANJDBCUtil.setParam(pstmt, ++paramIdx, opeDate);
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// ECN0131B005CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
ECN0131B005CBSMsg1List.SYSID
, ECN0131B005CBSMsg1List.CUST_NM
, ECN0131B005CBSMsg1List.CONT_ISP_USE_FAIL_FLG
, ECN0131B005CBSMsg1List.MMB_ISP_KSSAI_STAT_CD
, ECN0131B005CBSMsg1List.SVC_KEI_NO
, ECN0131B005CBSMsg1List.TOKUSOKU_STAT
, ECN0131B005CBSMsg1List.SVC_CD
, ECN0131B005CBSMsg1List.PRC_GRP_CD
, ECN0131B005CBSMsg1List.PRC_GRP_NM
, ECN0131B005CBSMsg1List.ISP_KSSAI_SVC_KEI_NO
, ECN0131B005CBSMsg1List.CONTRACT_CD
, ECN0131B005CBSMsg1List.SERVICE_CD
, ECN0131B005CBSMsg1List.SERVICE_NAME
, ECN0131B005CBSMsg1List.SEIKY_KEI_NO
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ECN0131B005CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("ECN0131B005CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(ECN0131B005CBSMsg.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(ECN0131B005CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}