/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEKK0351B014TPDA
* ソースファイル名：JSYejbEKK0351B014TPDA.java
* 作成者 ：EK953649
* 日付 ：2019年10月01日
*＜機能概要＞
* オプションサービス契約一覧照会（オペレーター支援）テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* v47.00.00 2019/11/01 FJ)後藤 【ANK-3766-00-00】営業チャネルでの既契約者対応
* v49.00.00 2020/04/21 FJ)坂本 【ANK-3831-00-00】オペレータ支援向けAPI・画面機能変更(2020年5月下旬リリース)
* v50.00.00 2020/06/08 FJ)大島 【ANK-3862-02-00】【WEB電話オプション外出し対応】050番号返却対応
* v50.01.00 2020/06/01 FJ)中原 【ANK-3754-00-00】トビラフォン対応
* v51.00.00 2020/10/28 FJ)謝 【ANK-3927-00-00】オペレータ支援向けAPI機能変更(2020年12月中旬リリース)
* v53.00.00 2021/05/14 GDC)ミコ 【ANK-4030-00-00】オペレータ支援向けAPI機能変更（2021年6月）
* v73.00.00 2024/08/09 GDC)v.auditor 【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.EKK0351B014CBSMsg;
import eo.ejb.cbs.cbsmsg.EKK0351B014CBSMsg1List;
import eo.ejb.cbm.entity.KK0351ETMsg;
import eo.ejb.common.JKKModelCommon;
/**
*
* テンプレートDBアクセス部品 EKK0351B014_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEKK0351B014TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbEKK0351B014TPDA() {
}
/**
* EKK0351B014_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(KK0351ETMsg.getTableName());
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT ")
.append(" KK0351.OP_SVC_KEI_NO ")
.append(" , KK0351.OP_SVC_KEI_STAT ")
.append(" , KK0351.OP_SVC_CD ")
.append(" , KK0351.SVC_DSL_YMD ")
.append(" , KK0351.SVC_DLRE_CD ")
.append(" , ( ")
.append(" SELECT ")
.append(" KK0821.OP_SVC_NM ")
.append(" FROM ")
.append(" KK_M_OP_SVC KK0821 ")
.append(" WHERE ")
.append(" KK0821.OP_SVC_CD = KK0351.OP_SVC_CD ")
.append(" AND ( ")
.append(" KK0821.OP_SVC_CD ")
.append(" , KK0821.RSV_APLY_YMD || KK0821.GENE_ADD_DTM ")
.append(" ) = ( ")
.append(" SELECT ")
.append(" KK0821_GENE.OP_SVC_CD ")
.append(" , MAX( ")
.append(" KK0821_GENE.RSV_APLY_YMD || KK0821_GENE.GENE_ADD_DTM ")
.append(" ) AS KK0821_MAX ")
.append(" FROM ")
.append(" KK_M_OP_SVC KK0821_GENE ")
.append(" WHERE ")
.append(" KK0821_GENE.OP_SVC_CD = KK0821.OP_SVC_CD ")
.append(" AND KK0821_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0821_GENE.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0821_GENE.OP_SVC_CD ")
.append(" ) ")
.append(" ) AS OP_SVC_NM ")
.append(" , KK0401.SBOP_SVC_KEI_NO ")
.append(" , KK0401.SBOP_SVC_CD ")
.append(" , KK0401.SBOP_SVC_NM ")
.append(" , KK0401.SBOP_SVC_KEI_STAT ")
.append(" , KK0401.SVC_DSL_YMD ")
.append(" , KK0401.SVC_DLRE_CD ")
.append(" , KK0411.ADD_CAPA ")
// ANK-3766-00-00 ADD START
.append(" , KK0351.SVC_KEI_UCWK_NO ")
.append(" , KK0361.MLAD ")
.append(" , KK0371.TWRYO_STIAM_TTU_TCHI_STIAM ")
// ANK-3766-00-00 ADD END
// ANK-3831-00-00 ADD START
.append(" , KK0361.SHK_POP_ID_PWD ")
.append(" , KK0411.ALIAS AS ML_ALIAS ")
// ANK-3831-00-00 ADD END
// ANK-3862-02-00 ADD START
.append(" , KK0371.N_050_OP_TELNO ")
// ANK-3862-02-00 ADD START
// ANK-3754-00-00 ADD START
.append(" , KK0371.TOBILAPH_ID ")
// ANK-3754-00-00 ADD END
// ANK-3927-00-00 ADD START
.append(" , KK0351.SVC_STAYMD AS OP_SVC_STAYMD ")
.append(" , KK0351.SVC_ENDYMD AS OP_SVC_ENDYMD ")
.append(" , KK0351.SVC_CHRG_STAYMD AS OP_SVC_CHRG_STAYMD ")
.append(" , KK0351.SVC_CHRG_ENDYMD AS OP_SVC_CHRG_ENDYMD ")
.append(" , KK0351.PPLAN_CD AS OP_PPLAN_CD ")
.append(" ,(SELECT KK0591.PPLAN_NM ")
.append(" FROM KK_M_PPLAN KK0591 ")
.append(" WHERE KK0591.PCRS_CD = KK0351.PCRS_CD ")
.append(" AND KK0591.PPLAN_CD = KK0351.PPLAN_CD ")
.append(" AND KK0591.MK_FLG = '0' ")
.append(" ) AS OP_PPLAN_NM ")
.append(" , KK0351.PLAN_STAYMD AS OP_PLAN_STAYMD ")
.append(" , KK0351.PLAN_ENDYMD AS OP_PLAN_ENDYMD ")
.append(" , KK0351.PLAN_CHRG_STAYMD AS OP_PLAN_CHRG_STAYMD ")
.append(" , KK0351.PLAN_CHRG_ENDYMD AS OP_PLAN_CHRG_ENDYMD ")
.append(" , KK0351.PNLTY_HASSEI_CD AS OP_PNLTY_HASSEI_CD ")
.append(" , KK0351.KAIHK_YMD AS OP_KAIHK_YMD ")
// ANK-4030-00-00 ADD START
.append(" , CASE WHEN KK0351.PCRS_CD IN ('B39','B72','B87','BA9','BB5','F05','F06') AND KK0351.OP_SVC_KEI_STAT < '100' THEN KK0351.SVC_USE_STA_KIBO_YMD ")
.append(" WHEN KK0351.PCRS_CD IN ('B39','B72','B87','BA9','BB5','F05','F06') AND KK0351.OP_SVC_KEI_STAT >= '100' THEN KK0351.SVC_STAYMD ")
.append(" ELSE KK0351.DSP_SVCTK_STAYMD END AS OP_SVC_USE_STA_YMD ")
.append(" , KK1681_OP.RSV_APLY_YMD AS OP_USE_END_RSYMD ")
.append(" , KK0381.CABLE_GUIDE_MSKM_CNT ")
// ANK-4030-00-00 ADD END
// ANK-4427-00-00 ADD START
.append(" , KK0361.TYPEN_TELNO ")
// ANK-4427-00-00 ADD END
.append(" , KK0401.SVC_STAYMD AS SBOP_SVC_STAYMD ")
.append(" , KK0401.SVC_ENDYMD AS SBOP_SVC_ENDYMD ")
.append(" , KK0401.SVC_CHRG_STAYMD AS SBOP_SVC_CHRG_STAYMD ")
.append(" , KK0401.SVC_CHRG_ENDYMD AS SBOP_SVC_CHRG_ENDYMD ")
.append(" , KK0401.PPLAN_CD AS SBOP_PPLAN_CD ")
.append(" ,(SELECT KK0591.PPLAN_NM ")
.append(" FROM KK_M_PPLAN KK0591 ")
.append(" WHERE KK0591.PCRS_CD = KK0401.PCRS_CD ")
.append(" AND KK0591.PPLAN_CD = KK0401.PPLAN_CD ")
.append(" AND KK0591.MK_FLG = '0' ")
.append(" ) AS SBOP_PPLAN_NM ")
.append(" , KK0401.PLAN_STAYMD AS SBOP_PLAN_STAYMD ")
.append(" , KK0401.PLAN_ENDYMD AS SBOP_PLAN_ENDYMD ")
.append(" , KK0401.PLAN_CHRG_STAYMD AS SBOP_PLAN_CHRG_STAYMD ")
.append(" , KK0401.PLAN_CHRG_ENDYMD AS SBOP_PLAN_CHRG_ENDYMD ")
.append(" , KK0401.PNLTY_HASSEI_CD AS SBOP_PNLTY_HASSEI_CD ")
.append(" , KK0401.KAIHK_YMD AS SBOP_KAIHK_YMD ")
// ANK-4030-00-00 ADD START
.append(" , KK0401.DSP_SVCTK_STAYMD AS SBOP_SVC_USE_STA_YMD ")
.append(" , KK1681_SBOP.RSV_APLY_YMD AS SBOP_USE_END_RSYMD ")
// ANK-4030-00-00 ADD END
// ANK-3927-00-00 ADD END
.append(" FROM ")
.append(" ( ")
.append(" SELECT ")
.append(" OP.OP_SVC_KEI_NO ")
.append(" , OP.OP_SVC_KEI_STAT ")
.append(" , OP.OP_SVC_CD ")
.append(" , OP.SVC_KEI_UCWK_NO ")
.append(" , OP.SVC_KEI_NO ")
.append(" , OP.SVC_DSL_YMD ")
.append(" , OP.SVC_DLRE_CD ")
// ANK-3766-00-00 ADD START
.append(" , OP.GENE_ADD_DTM ")
// ANK-3766-00-00 ADD END
// ANK-3927-00-00 ADD START
.append(" , OP.PPLAN_CD ")
.append(" , OP.PCRS_CD ")
.append(" , OP.SVC_STAYMD ")
.append(" , OP.SVC_ENDYMD ")
.append(" , OP.SVC_CHRG_STAYMD ")
.append(" , OP.SVC_CHRG_ENDYMD ")
.append(" , OP.PNLTY_HASSEI_CD ")
.append(" , OP.KAIHK_YMD ")
.append(" , OP.PLAN_STAYMD ")
.append(" , OP.PLAN_ENDYMD ")
.append(" , OP.PLAN_CHRG_STAYMD ")
.append(" , OP.PLAN_CHRG_ENDYMD ")
// ANK-4030-00-00 ADD START
.append(" , OP.SVC_USE_STA_KIBO_YMD ")
.append(" , OP.DSP_SVCTK_STAYMD ")
// ANK-4030-00-00 ADD END
// ANK-3927-00-00 ADD END
.append(" FROM ")
.append(" KK_T_OP_SVC_KEI OP ")
.append(" WHERE ")
.append(" OP.RSV_APLY_YMD || OP.GENE_ADD_DTM = ( ")
.append(" SELECT ")
.append(" MAX( ")
.append(" KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM ")
.append(" ) AS MAX_GENE_DTM ")
.append(" FROM ")
.append(" KK_T_OP_SVC_KEI KK0351_GENE ")
.append(" WHERE ")
.append(" KK0351_GENE.OP_SVC_KEI_NO = OP.OP_SVC_KEI_NO ")
.append(" AND KK0351_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0351_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0351_GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" UNION ")
.append(" SELECT ")
.append(" KK0351_02.OP_SVC_KEI_NO ")
.append(" , KK0351_02.OP_SVC_KEI_STAT ")
.append(" , KK0351_02.OP_SVC_CD ")
.append(" , KK0351_02.SVC_KEI_UCWK_NO ")
.append(" , KK0161.SVC_KEI_NO ")
.append(" , KK0351_02.SVC_DSL_YMD ")
.append(" , KK0351_02.SVC_DLRE_CD ")
// ANK-3766-00-00 ADD START
.append(" , KK0351_02.GENE_ADD_DTM ")
// ANK-3766-00-00 ADD END
// ANK-3927-00-00 ADD START
.append(" , KK0351_02.PPLAN_CD ")
.append(" , KK0351_02.PCRS_CD ")
.append(" , KK0351_02.SVC_STAYMD ")
.append(" , KK0351_02.SVC_ENDYMD ")
.append(" , KK0351_02.SVC_CHRG_STAYMD ")
.append(" , KK0351_02.SVC_CHRG_ENDYMD ")
.append(" , KK0351_02.PNLTY_HASSEI_CD ")
.append(" , KK0351_02.KAIHK_YMD ")
.append(" , KK0351_02.PLAN_STAYMD ")
.append(" , KK0351_02.PLAN_ENDYMD ")
.append(" , KK0351_02.PLAN_CHRG_STAYMD ")
.append(" , KK0351_02.PLAN_CHRG_ENDYMD ")
// ANK-4030-00-00 ADD START
.append(" , KK0351_02.SVC_USE_STA_KIBO_YMD ")
.append(" , KK0351_02.DSP_SVCTK_STAYMD ")
// ANK-4030-00-00 ADD END
// ANK-3927-00-00 ADD END
.append(" FROM ")
.append(" KK_T_OP_SVC_KEI KK0351_02 ")
.append(" INNER JOIN KK_T_SVC_KEI_UCWK KK0161 ")
.append(" ON KK0161.SVC_KEI_UCWK_NO = KK0351_02.SVC_KEI_UCWK_NO ")
.append(" WHERE ")
.append(" ( ")
.append(" KK0161.SVC_KEI_UCWK_NO ")
.append(" , KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM ")
.append(" ) = ( ")
.append(" SELECT ")
.append(" KK0161_GENE.SVC_KEI_UCWK_NO ")
.append(" , MAX( ")
.append(" KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM ")
.append(" ) ")
.append(" FROM ")
.append(" KK_T_SVC_KEI_UCWK KK0161_GENE ")
.append(" WHERE ")
.append(" KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
.append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0161_GENE.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0161_GENE.SVC_KEI_UCWK_NO ")
.append(" ) ")
.append(" AND ( ")
.append(" KK0351_02.OP_SVC_KEI_NO ")
.append(" , KK0351_02.RSV_APLY_YMD || KK0351_02.GENE_ADD_DTM ")
.append(" ) = ( ")
.append(" SELECT ")
.append(" KK0351_02_GENE.OP_SVC_KEI_NO ")
.append(" , MAX( ")
.append(" KK0351_02_GENE.RSV_APLY_YMD || KK0351_02_GENE.GENE_ADD_DTM ")
.append(" ) ")
.append(" FROM ")
.append(" KK_T_OP_SVC_KEI KK0351_02_GENE ")
.append(" WHERE ")
.append(" KK0351_02_GENE.OP_SVC_KEI_NO = KK0351_02.OP_SVC_KEI_NO ")
.append(" AND KK0351_02_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0351_02_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0351_02_GENE.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0351_02_GENE.OP_SVC_KEI_NO ")
.append(" ) ")
.append(" ) KK0351 ")
// ANK-3766-00-00 ADD START
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" OP_ISP.OP_SVC_KEI_NO ")
.append(" , OP_ISP.MLAD ")
.append(" , OP_ISP.GENE_ADD_DTM ")
// ANK-3831-00-00 ADD START
.append(" , OP_ISP.SHK_POP_ID_PWD ")
// ANK-3831-00-00 ADD END
// ANK-4427-00-00 ADD START
.append(" , OP_ISP.TYPEN_TELNO ")
// ANK-4427-00-00 ADD END
.append(" FROM KK_T_OPSVKEI_ISP OP_ISP ")
.append(" WHERE ")
.append(" OP_ISP.MK_FLG = '0' ")
.append(" ) KK0361 ")
.append(" ON KK0351.OP_SVC_KEI_NO = KK0361.OP_SVC_KEI_NO ")
.append(" AND KK0351.GENE_ADD_DTM = KK0361.GENE_ADD_DTM ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" OP_TEL.OP_SVC_KEI_NO ")
.append(" , OP_TEL.TWRYO_STIAM_TTU_TCHI_STIAM ")
.append(" , OP_TEL.GENE_ADD_DTM ")
// ANK-3862-02-00 ADD START
.append(" , OP_TEL.N_050_OP_TELNO ")
// ANK-3862-02-00 ADD END
// ANK-3754-00-00 ADD START
.append(" , OP_TEL.TOBILAPH_ID ")
// ANK-3754-00-00 ADD END
.append(" FROM KK_T_OPSVKEI_TEL OP_TEL ")
.append(" WHERE ")
.append(" OP_TEL.MK_FLG = '0' ")
.append(" ) KK0371 ")
.append(" ON KK0351.OP_SVC_KEI_NO = KK0371.OP_SVC_KEI_NO ")
.append(" AND KK0351.GENE_ADD_DTM = KK0371.GENE_ADD_DTM ")
// ANK-3766-00-00 ADD END
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" SBOP.OP_SVC_KEI_NO ")
.append(" , SBOP.SBOP_SVC_KEI_NO ")
.append(" , SBOP.SBOP_SVC_KEI_STAT ")
.append(" , SBOP.SBOP_SVC_CD ")
.append(" , SBOP.SVC_DSL_YMD ")
.append(" , SBOP.SVC_DLRE_CD ")
.append(" , ( ")
.append(" SELECT ")
.append(" KK0831.SBOP_SVC_NM ")
.append(" FROM ")
.append(" KK_M_SBOP_SVC KK0831 ")
.append(" WHERE ")
.append(" KK0831.SBOP_SVC_CD = SBOP.SBOP_SVC_CD ")
.append(" AND (KK0831.SBOP_SVC_CD, KK0831.GENE_ADD_DTM) = ( ")
.append(" SELECT ")
.append(" KK0831_GENE.SBOP_SVC_CD ")
.append(" , MAX(KK0831_GENE.GENE_ADD_DTM) AS KK0831_MAX ")
.append(" FROM ")
.append(" KK_M_SBOP_SVC KK0831_GENE ")
.append(" WHERE ")
.append(" KK0831_GENE.SBOP_SVC_CD = KK0831.SBOP_SVC_CD ")
.append(" AND KK0831_GENE.MK_FLG = '0' ")
.append(" GROUP BY ")
.append(" KK0831_GENE.SBOP_SVC_CD ")
.append(" ) ")
.append(" ) AS SBOP_SVC_NM ")
// ANK-3927-00-00 ADD START
.append(" , SBOP.PPLAN_CD ")
.append(" , SBOP.PCRS_CD ")
.append(" , SBOP.SVC_STAYMD ")
.append(" , SBOP.SVC_ENDYMD ")
.append(" , SBOP.SVC_CHRG_STAYMD ")
.append(" , SBOP.SVC_CHRG_ENDYMD ")
.append(" , SBOP.PNLTY_HASSEI_CD ")
.append(" , SBOP.KAIHK_YMD ")
.append(" , SBOP.PLAN_STAYMD ")
.append(" , SBOP.PLAN_ENDYMD ")
.append(" , SBOP.PLAN_CHRG_STAYMD ")
.append(" , SBOP.PLAN_CHRG_ENDYMD ")
// ANK-3927-00-00 ADD END
// ANK-4030-00-00 ADD END
.append(" , SBOP.DSP_SVCTK_STAYMD ")
// ANK-4030-00-00 ADD END
.append(" FROM ")
.append(" KK_T_SBOP_SVC_KEI SBOP ")
.append(" WHERE ")
.append(" SBOP.GENE_ADD_DTM = ( ")
.append(" SELECT ")
.append(" MAX( ")
.append(" KK0401_GENE.GENE_ADD_DTM ")
.append(" ) AS MAX_GENE_DTM ")
.append(" FROM ")
.append(" KK_T_SBOP_SVC_KEI KK0401_GENE ")
.append(" WHERE ")
.append(" KK0401_GENE.OP_SVC_KEI_NO = SBOP.OP_SVC_KEI_NO ")
.append(" AND KK0401_GENE.SBOP_SVC_KEI_NO = SBOP.SBOP_SVC_KEI_NO ")
.append(" AND KK0401_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0401_GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" ) KK0401 ")
.append(" ON KK0401.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ")
.append(" LEFT OUTER JOIN ( ")
.append(" SELECT ")
.append(" SBOP_ISP.ADD_CAPA ")
.append(" , SBOP_ISP.OP_SVC_KEI_NO ")
.append(" , SBOP_ISP.SBOP_SVC_KEI_NO ")
// ANK-3831-00-00 ADD START
.append(" , SBOP_ISP.ALIAS ")
// ANK-3831-00-00 ADD END
.append(" FROM ")
.append(" KK_T_SBOPSVKEI_ISP SBOP_ISP ")
.append(" where ")
.append(" SBOP_ISP.GENE_ADD_DTM = ( ")
.append(" SELECT ")
.append(" MAX(KK0411_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM ")
.append(" FROM ")
.append(" KK_T_SBOP_SVC_KEI KK0411_GENE ")
.append(" WHERE ")
.append(" KK0411_GENE.OP_SVC_KEI_NO = SBOP_ISP.OP_SVC_KEI_NO ")
.append(" AND KK0411_GENE.SBOP_SVC_KEI_NO = SBOP_ISP.SBOP_SVC_KEI_NO ")
.append(" AND KK0411_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0411_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0411_GENE.MK_FLG = '0' ")
.append(" ) ")
.append(" ) KK0411 ")
.append(" ON KK0411.OP_SVC_KEI_NO = KK0401.OP_SVC_KEI_NO ")
// ANK-4030-00-00 MOD START
// .append(" AND KK0411.SBOP_SVC_KEI_NO = KK0401.SBOP_SVC_KEI_NO ");
.append(" AND KK0411.SBOP_SVC_KEI_NO = KK0401.SBOP_SVC_KEI_NO ")
.append(" LEFT JOIN ( ")
.append(" SELECT ")
.append(" OPKT.OP_SVC_KEI_NO ")
.append(" ,OPKT.GENE_ADD_DTM ")
.append(" ,OPKT.CABLE_GUIDE_MSKM_CNT ")
.append(" FROM KK_T_OPSVKEI_TV OPKT ")
.append(" ) KK0381 ")
.append(" ON KK0381.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ")
.append(" AND KK0381.GENE_ADD_DTM = KK0351.GENE_ADD_DTM ")
.append(" LEFT JOIN( ")
.append(" SELECT ")
.append(" IDR_OP.OP_SVC_KEI_NO ")
.append(" ,MAX(IDR_OP.RSV_APLY_YMD) AS RSV_APLY_YMD ")
.append(" FROM KK_T_IDO_RSV IDR_OP ")
.append(" WHERE IDR_OP.OP_SVC_KEI_NO IS NOT NULL ")
.append(" AND IDR_OP.IDO_RSV_STAT_CD = '00' ")
.append(" AND IDO_RSV_DTL_CD='016'")
.append(" AND IDR_OP.MK_FLG = '0' ")
.append(" GROUP BY IDR_OP.OP_SVC_KEI_NO ")
.append(" ) KK1681_OP ")
.append(" ON KK1681_OP.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ")
.append(" LEFT JOIN( ")
.append(" SELECT ")
.append(" IDR_SBOP.SBOP_SVC_KEI_NO ")
.append(" ,MAX(IDR_SBOP.RSV_APLY_YMD) AS RSV_APLY_YMD ")
.append(" FROM KK_T_IDO_RSV IDR_SBOP ")
.append(" WHERE IDR_SBOP.SBOP_SVC_KEI_NO IS NOT NULL ")
.append(" AND IDR_SBOP.IDO_RSV_STAT_CD = '00' ")
.append(" AND IDO_RSV_DTL_CD='018'")
.append(" AND IDR_SBOP.MK_FLG = '0' ")
.append(" GROUP BY IDR_SBOP.SBOP_SVC_KEI_NO ")
.append(" ) KK1681_SBOP ")
.append(" ON KK1681_SBOP.SBOP_SVC_KEI_NO = KK0401.SBOP_SVC_KEI_NO ");
// ANK-4030-00-00 MOD END
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(EKK0351B014CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" WHERE ")
.append(" KK0351.SVC_KEI_NO = ? ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
if ( "1".equals( inMsg.getObject(EKK0351B014CBSMsg.FUNC_CODE).toString() ) ) {
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 1, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 2, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 3, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 4, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(運用日付を指定)
CAANJDBCUtil.setParam(pstmt, 5, JKKModelCommon.getOpeDate(inMsg));
// パラメータの設定(サービス契約番号を指定)
CAANJDBCUtil.setParam(pstmt, 6, inMsg.getObject(EKK0351B014CBSMsg.KEY_SVC_KEI_NO));
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EKK0351B014CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EKK0351B014CBSMsg1List.OP_SVC_KEI_NO
, EKK0351B014CBSMsg1List.OP_SVC_KEI_STAT
, EKK0351B014CBSMsg1List.OP_SVC_CD
, EKK0351B014CBSMsg1List.SVC_DSL_YMD
, EKK0351B014CBSMsg1List.SVC_DLRE_CD
, EKK0351B014CBSMsg1List.OP_SVC_NM
, EKK0351B014CBSMsg1List.SBOP_SVC_KEI_NO
, EKK0351B014CBSMsg1List.SBOP_SVC_CD
, EKK0351B014CBSMsg1List.SBOP_SVC_NM
, EKK0351B014CBSMsg1List.SBOP_SVC_KEI_STAT
, EKK0351B014CBSMsg1List.SBOP_SVC_DSL_YMD
, EKK0351B014CBSMsg1List.SBOP_SVC_DLRE_CD
, EKK0351B014CBSMsg1List.ADD_CAPA
// ANK-3766-00-00 ADD START
, EKK0351B014CBSMsg1List.SVC_KEI_UCWK_NO
, EKK0351B014CBSMsg1List.MLAD
, EKK0351B014CBSMsg1List.TWRYO_STIAM_TTU_TCHI_STIAM
// ANK-3766-00-00 ADD END
// ANK-3831-00-00 ADD START
, EKK0351B014CBSMsg1List.SHK_POP_ID_PWD
, EKK0351B014CBSMsg1List.ML_ALIAS
// ANK-3831-00-00 ADD END
// ANK-3862-02-00 ADD START
, EKK0351B014CBSMsg1List.N_050_OP_TELNO
// ANK-3862-02-00 ADD END
// ANK-3754-00-00 ADD START
, EKK0351B014CBSMsg1List.TOBILAPH_ID
// ANK-3754-00-00 ADD END
// ANK-3927-00-00 ADD START
, EKK0351B014CBSMsg1List.OP_SVC_STAYMD
, EKK0351B014CBSMsg1List.OP_SVC_ENDYMD
, EKK0351B014CBSMsg1List.OP_SVC_CHRG_STAYMD
, EKK0351B014CBSMsg1List.OP_SVC_CHRG_ENDYMD
, EKK0351B014CBSMsg1List.OP_PPLAN_CD
, EKK0351B014CBSMsg1List.OP_PPLAN_NM
, EKK0351B014CBSMsg1List.OP_PLAN_STAYMD
, EKK0351B014CBSMsg1List.OP_PLAN_ENDYMD
, EKK0351B014CBSMsg1List.OP_PLAN_CHRG_STAYMD
, EKK0351B014CBSMsg1List.OP_PLAN_CHRG_ENDYMD
, EKK0351B014CBSMsg1List.OP_PNLTY_HASSEI_CD
, EKK0351B014CBSMsg1List.OP_KAIHK_YMD
// ANK-4030-00-00 ADD START
, EKK0351B014CBSMsg1List.OP_SVC_USE_STA_YMD
, EKK0351B014CBSMsg1List.OP_USE_END_RSYMD
, EKK0351B014CBSMsg1List.CABLE_GUIDE_MSKM_CNT
// ANK-4030-00-00 ADD START
// ANK-4427-00-00 ADD START
, EKK0351B014CBSMsg1List.TYPEN_TELNO
// ANK-4427-00-00 ADD END
, EKK0351B014CBSMsg1List.SBOP_SVC_STAYMD
, EKK0351B014CBSMsg1List.SBOP_SVC_ENDYMD
, EKK0351B014CBSMsg1List.SBOP_SVC_CHRG_STAYMD
, EKK0351B014CBSMsg1List.SBOP_SVC_CHRG_ENDYMD
, EKK0351B014CBSMsg1List.SBOP_PPLAN_CD
, EKK0351B014CBSMsg1List.SBOP_PPLAN_NM
, EKK0351B014CBSMsg1List.SBOP_PLAN_STAYMD
, EKK0351B014CBSMsg1List.SBOP_PLAN_ENDYMD
, EKK0351B014CBSMsg1List.SBOP_PLAN_CHRG_STAYMD
, EKK0351B014CBSMsg1List.SBOP_PLAN_CHRG_ENDYMD
, EKK0351B014CBSMsg1List.SBOP_PNLTY_HASSEI_CD
, EKK0351B014CBSMsg1List.SBOP_KAIHK_YMD
// ANK-3927-00-00 ADD END
// ANK-4030-00-00 ADD START
, EKK0351B014CBSMsg1List.SBOP_SVC_USE_STA_YMD
, EKK0351B014CBSMsg1List.SBOP_USE_END_RSYMD
// ANK-4030-00-00 ADD START
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0351B014CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EKK0351B014CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EKK0351B014CBSMsg.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(EKK0351B014CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}