/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0341B017TPDA * ソースファイル名：JSYejbEKK0341B017TPDA.java * 作成者：EK912022 * 日付：2013年01月21日 *＜機能概要＞ * 機器提供サービス契約一覧（工事機器）テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * v4.00.00 2013/01/21 FJ)坂本 ST1-2012-0000846 * ｖ4.00.01 2013/02/01 FJ）沖田 IT1-2013-0000186 * v10.02.00 2014/08/15 FJ）伊藤 ANK-2198-00-00 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.fujitsu.futurity.model.base.CAANJDBCUtil; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANSQLFacility; 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.AgentDispatchContext; import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler; import eo.ejb.cbm.entity.KK0341ETMsg; import eo.ejb.cbs.cbsmsg.EKK0341B017CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0341B017CBSMsg1List; import eo.ejb.common.JKKModelCommon; /** * * テンプレートDBアクセス部品　EKK0341B017_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0341B017TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0341B017TPDA() { } /** * EKK0341B017_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // パラメータのカウント int iPCnt = 0; // 運用日の取得 String opeDate = JKKModelCommon.getOpeDate(inMsg); /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(KK0341ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT KK0341_CUR.KKTK_SVC_KEI_NO AS CUR_KKTK_SVC_KEI_NO ") .append(" ,KK0341_CUR.GENE_ADD_DTM AS CUR_KK0341_GENE_ADD_DTM ") .append(" ,KK0341_CUR.UPD_DTM AS CUR_KK0341_UPD_DTM ") .append(" ,KK0341_CUR.TAKNKIKI_MODEL_CD AS CUR_KK0341_TAKNKIKI_MODEL_CD ") .append(" ,KK0341_CUR.KIKI_SEIZO_NO AS CUR_KIKI_SEIZO_NO ") .append(" ,KK0791_CUR.STB_SBT_CD AS CUR_STB_SBT_CD ") .append(" ,( SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00561' ") .append(" AND ZM0171_01.CD_DIV = KK0791_CUR.STB_SBT_CD ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS CUR_STB_SBT_CD_NM ") .append(" ,KK0341_CUR.TAKNKIKI_IDO_CD AS CUR_TAKNKIKI_IDO_CD ") .append(" ,( SELECT ZM0171_02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00562' ") .append(" AND ZM0171_02.CD_DIV = KK0341_CUR.TAKNKIKI_IDO_CD ") .append(" AND ZM0171_02.MK_FLG = '0' ") .append(" ) AS CUR_TAKNKIKI_IDO_CD_NM ") .append(" ,KK0341_CUR.HDD_CAPA_CD AS CUR_KK0341_HDD_CAPA_CD ") .append(" ,( SELECT ZM0171_03.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ") .append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00557' ") .append(" AND ZM0171_03.CD_DIV = KK0341_CUR.HDD_CAPA_CD ") .append(" AND ZM0171_03.MK_FLG = '0' ") .append(" ) AS CUR_HDD_CAPA_CD_NM ") .append(" ,KK0341_CUR.TAKNKIKI_SBT_CD AS CUR_TAKNKIKI_SBT_CD ") .append(" ,KK0341_CUR.KKTK_SVC_CD AS CUR_KKTK_SVC_CD ") .append(" ,KK0341_CUR.HAMBAI_SBT_CD AS CUR_HAMBAI_SBT_CD ") .append(" ,KK0341_CUR.KKTK_SBT_CD AS CUR_KKTK_SBT_CD ") .append(" ,KK0341_CUR.PCRS_CD AS CUR_PCRS_CD ") .append(" ,KK0161_CUR.SVC_KEI_UCWK_NO AS CUR_SVC_KEI_UCWK_NO ") .append(" ,KK0161_CUR.GENE_ADD_DTM AS CUR_KK0161_GENE_ADD_DTM ") .append(" ,KK0161_CUR.UPD_DTM AS CUR_KK0161_UPD_DTM ") .append(" ,KK0161_CUR.PCRS_CD AS CUR_KK0161_PCRS_CD ") .append(" ,(SELECT KK0791_01.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_01 ") .append(" WHERE KK0791_01.PCRS_CD = KK0161_CUR.PCRS_CD ") .append(" AND (KK0791_01.PCRS_CD, KK0791_01.RSV_APLY_YMD || KK0791_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_01GENE.PCRS_CD,MAX(KK0791_01GENE.RSV_APLY_YMD || KK0791_01GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PCRS KK0791_01GENE ") .append(" WHERE KK0791_01GENE.PCRS_CD = KK0791_01.PCRS_CD ") .append(" AND KK0791_01GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_01GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_01GENE.PCRS_CD) ") .append(" ) AS CUR_PCRS_CD_NM "); // 20140815 ANK-2198-00-00 Add Start sql_Buff.append(" ,CASE WHEN KK0771.KKTK_SVC_CD IS NOT NULL THEN ") .append(" TO_CHAR(ADD_MONTHS(TO_DATE(KK0341_CUR.SVC_STA_YMD, 'YYYYMMDD'), KK0771.PNLTY_HASSEI_MIN_USE_PRD) - 1, 'YYYYMMDD') ") .append(" END AS CUR_KIKI_MIN_USE_PRD "); // 20140815 ANK-2198-00-00 Add End // ＫＥＹ＿工事案件番号が指定されている場合 if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KOJIAK_NO)) { sql_Buff.append(" ,KU0141.KOJIAK_TRGT_TAKNKIKI_NO ") .append(" ,KU0141.KOJIAK_TRGT_TAKNKIKI_STAT ") .append(" ,KU0141.KOJIAK_TRGT_KIKI_GRP_NO ") .append(" ,KU0141.STC_TEKKYO_FLG ") .append(" ,KU0141.TAKNKIKI_SBT_CD ") .append(" ,KU0141.TAKNKIKI_IDO_CD ") .append(" ,KU0141.RSV_TAKNKIKI_MODEL_CD ") .append(" ,KU0141.RSV_KIKI_SEIZO_NO ") .append(" ,KU0141.RSV_MAKER_CD ") .append(" ,KU0141.RSV_MACAD ") .append(" ,KU0141.RSV_STB_ID ") .append(" ,KU0141.RSV_HDD_CAPA_CD ") .append(" ,KU0141.RSV_VONU_BSPT_KH ") .append(" ,KU0141.RSV_STB_KEI_TV_COURSE_CD ") .append(" ,KU0141.RSV_ROUTER_KIND_CD ") .append(" ,KU0141.RSV_ROUTER_RENT_CD ") .append(" ,KU0141.CHGE_TAKNKIKI_MODEL_CD ") .append(" ,KU0141.CHGE_KIKI_SEIZO_NO ") .append(" ,KU0141.CHGE_MAKER_CD ") .append(" ,KU0141.CHGE_MACAD ") .append(" ,KU0141.CHGE_STB_ID ") .append(" ,KU0141.CHGE_HDD_CAPA_CD ") .append(" ,KU0141.CHGE_VONU_BSPT_KH ") .append(" ,KU0141.CHGE_STB_KEI_TV_COURSE_CD ") .append(" ,KU0141.CHGE_ROUTER_KIND_CD ") .append(" ,KU0141.CHGE_ROUTER_RENT_CD "); } else{ // 未設定の場合は "NULL" を設定する sql_Buff.append(" ,NULL AS KOJIAK_TRGT_TAKNKIKI_NO ") .append(" ,NULL AS KOJIAK_TRGT_TAKNKIKI_STAT ") .append(" ,NULL AS KOJIAK_TRGT_KIKI_GRP_NO ") .append(" ,NULL AS STC_TEKKYO_FLG ") .append(" ,NULL AS TAKNKIKI_SBT_CD ") .append(" ,NULL AS TAKNKIKI_IDO_CD ") .append(" ,NULL AS RSV_TAKNKIKI_MODEL_CD ") .append(" ,NULL AS RSV_KIKI_SEIZO_NO ") .append(" ,NULL AS RSV_MAKER_CD ") .append(" ,NULL AS RSV_MACAD ") .append(" ,NULL AS RSV_STB_ID ") .append(" ,NULL AS RSV_HDD_CAPA_CD ") .append(" ,NULL AS RSV_VONU_BSPT_KH ") .append(" ,NULL AS RSV_STB_KEI_TV_COURSE_CD ") .append(" ,NULL AS RSV_ROUTER_KIND_CD ") .append(" ,NULL AS RSV_ROUTER_RENT_CD ") .append(" ,NULL AS CHGE_TAKNKIKI_MODEL_CD ") .append(" ,NULL AS CHGE_KIKI_SEIZO_NO ") .append(" ,NULL AS CHGE_MAKER_CD ") .append(" ,NULL AS CHGE_MACAD ") .append(" ,NULL AS CHGE_STB_ID ") .append(" ,NULL AS CHGE_HDD_CAPA_CD ") .append(" ,NULL AS CHGE_VONU_BSPT_KH ") .append(" ,NULL AS CHGE_STB_KEI_TV_COURSE_CD ") .append(" ,NULL AS CHGE_ROUTER_KIND_CD ") .append(" ,NULL AS CHGE_ROUTER_RENT_CD "); } sql_Buff.append(" ,KK0341_RSV.GENE_ADD_DTM AS RSV_KK0341_GENE_ADD_DTM ") .append(" ,KK0341_RSV.UPD_DTM AS RSV_KK0341_UPD_DTM ") .append(" ,KK0341_RSV.TAKNKIKI_MODEL_CD AS RSV_KK0341_TAKNKIKI_MODEL_CD ") .append(" ,KK0341_RSV.KIKI_SEIZO_NO AS RSV_KIKI_SEIZO_NO ") .append(" ,KK0791_RSV.STB_SBT_CD AS RSV_STB_SBT_CD ") .append(" ,( SELECT ZM0171_04.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_04 ") .append(" WHERE ZM0171_04.CD_SBT_CD = 'CD00561' ") .append(" AND ZM0171_04.CD_DIV = KK0791_RSV.STB_SBT_CD ") .append(" AND ZM0171_04.MK_FLG = '0' ") .append(" ) AS RSV_STB_SBT_CD_NM ") .append(" ,KK0341_RSV.TAKNKIKI_IDO_CD AS RSV_TAKNKIKI_IDO_CD ") .append(" ,( SELECT ZM0171_05.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_05 ") .append(" WHERE ZM0171_05.CD_SBT_CD = 'CD00562' ") .append(" AND ZM0171_05.CD_DIV = KK0341_RSV.TAKNKIKI_IDO_CD ") .append(" AND ZM0171_05.MK_FLG = '0' ") .append(" ) AS RSV_TAKNKIKI_IDO_CD_NM ") .append(" ,KK0341_RSV.HDD_CAPA_CD AS RSV_KK0341_HDD_CAPA_CD ") .append(" ,( SELECT ZM0171_06.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_06 ") .append(" WHERE ZM0171_06.CD_SBT_CD = 'CD00557' ") .append(" AND ZM0171_06.CD_DIV = KK0341_RSV.HDD_CAPA_CD ") .append(" AND ZM0171_06.MK_FLG = '0' ") .append(" ) AS RSV_HDD_CAPA_CD_NM ") .append(" ,KK0341_RSV.TAKNKIKI_SBT_CD AS RSV_TAKNKIKI_SBT_CD ") .append(" ,KK0341_RSV.KKTK_SVC_CD AS RSV_KKTK_SVC_CD ") .append(" ,KK0341_RSV.HAMBAI_SBT_CD AS RSV_HAMBAI_SBT_CD ") .append(" ,KK0341_RSV.KKTK_SBT_CD AS RSV_KKTK_SBT_CD ") .append(" ,KK0341_RSV.PCRS_CD AS RSV_KK0341_PCRS_CD ") .append(" ,KK0161_RSV.GENE_ADD_DTM AS RSV_KK0161_GENE_ADD_DTM ") .append(" ,KK0161_RSV.UPD_DTM AS RSV_KK0161_UPD_DTM ") .append(" ,KK0161_RSV.PCRS_CD AS RSV_KK0161_PCRS_CD ") .append(" ,(SELECT KK0791_02.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791_02 ") .append(" WHERE KK0791_02.PCRS_CD = KK0161_RSV.PCRS_CD ") .append(" AND (KK0791_02.PCRS_CD, KK0791_02.RSV_APLY_YMD || KK0791_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_02GENE.PCRS_CD,MAX(KK0791_02GENE.RSV_APLY_YMD || KK0791_02GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PCRS KK0791_02GENE ") .append(" WHERE KK0791_02GENE.PCRS_CD = KK0791_02.PCRS_CD ") .append(" AND KK0791_02GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_02GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_02GENE.PCRS_CD) ") .append(" ) AS RSV_PCRS_CD_NM ") .append(" FROM ( ") .append(" SELECT * ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01 ") .append(" WHERE KK0341_01.KKTK_SVC_KEI_STAT NOT IN ('910','920') "); // ＫＥＹ＿機器提供サービスコードが指定されている場合 if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)) { sql_Buff.append(" AND KK0341_01.KKTK_SVC_CD = ? "); } sql_Buff.append(" AND (KK0341_01.KKTK_SVC_KEI_NO, KK0341_01.RSV_APLY_YMD || KK0341_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_01GENE.KKTK_SVC_KEI_NO,MAX(KK0341_01GENE.RSV_APLY_YMD || KK0341_01GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01GENE ") .append(" WHERE KK0341_01GENE.KKTK_SVC_KEI_NO = KK0341_01.KKTK_SVC_KEI_NO ") .append(" AND KK0341_01GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0341_01GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_01GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_01GENE.KKTK_SVC_KEI_NO) ") .append(" AND KK0341_01.SVC_KEI_NO = ? ") .append(" UNION ") .append(" SELECT * ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01 ") .append(" WHERE KK0341_01.KKTK_SVC_KEI_STAT NOT IN ('910','920') "); // ＫＥＹ＿機器提供サービスコードが指定されている場合 if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)) { sql_Buff.append(" AND KK0341_01.KKTK_SVC_CD = ? "); } sql_Buff.append(" AND (KK0341_01.KKTK_SVC_KEI_NO, KK0341_01.RSV_APLY_YMD || KK0341_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_01GENE.KKTK_SVC_KEI_NO,MAX(KK0341_01GENE.RSV_APLY_YMD || KK0341_01GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_01GENE ") .append(" WHERE KK0341_01GENE.KKTK_SVC_KEI_NO = KK0341_01.KKTK_SVC_KEI_NO ") .append(" AND KK0341_01GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0341_01GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_01GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_01GENE.KKTK_SVC_KEI_NO) ") .append(" AND EXISTS ( ") .append(" SELECT * ") .append(" FROM ( ") .append(" SELECT KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241 ") .append(" INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251 ") .append(" ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" WHERE KK0241.SVC_KEI_NO = ? ") .append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = ") .append(" (SELECT KK0241_01.SVC_KEI_NO, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD ") .append(" FROM KK_T_KAISEN_TG_SVKEI KK0241_01 ") .append(" WHERE KK0241_01.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" AND KK0241_01.KAISEN_UCWK_USE_STAYMD <= ? ") .append(" AND KK0241_01.MK_FLG = '0' ") .append(" GROUP BY KK0241_01.SVC_KEI_NO) ") .append(" AND KK0241.MK_FLG = '0' ") .append(" AND (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) = ") .append(" (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO,MAX(KK0251_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE ") .append(" WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0251_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO) ") .append(" ) KK0251 ") .append(" WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0341_01.SVC_KEI_KAISEN_UCWK_NO ") .append(" ) ") .append(" ) KK0341_CUR ") .append(" LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02 ") .append(" WHERE (KK0341_02.KKTK_SVC_KEI_NO, NVL(KK0341_02.RSV_APLY_YMD,'20991231') || KK0341_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0341_02GENE.KKTK_SVC_KEI_NO,MAX(NVL(KK0341_02GENE.RSV_APLY_YMD,'20991231') || KK0341_02GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_KKTK_SVC_KEI KK0341_02GENE ") .append(" WHERE KK0341_02GENE.KKTK_SVC_KEI_NO = KK0341_02.KKTK_SVC_KEI_NO ") .append(" AND KK0341_02GENE.RSV_APLY_CD = '1' ") .append(" AND KK0341_02GENE.MK_FLG = '0' ") .append(" GROUP BY KK0341_02GENE.KKTK_SVC_KEI_NO) ") .append(" ) KK0341_RSV ") .append(" ON KK0341_RSV.KKTK_SVC_KEI_NO = KK0341_CUR.KKTK_SVC_KEI_NO "); // ＫＥＹ＿工事案件番号が指定されている場合 if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KOJIAK_NO)) { sql_Buff.append(" LEFT JOIN KU_T_KJAK_TG_TKKIKI KU0141 ") .append(" ON KU0141.KKTK_SVC_KEI_NO = KK0341_CUR.KKTK_SVC_KEI_NO ") .append(" AND KU0141.KOJIAK_NO = ? ") .append(" AND KU0141.KOJIAK_TRGT_TAKNKIKI_STAT IN ('120','130','140','150') "); } sql_Buff.append(" LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_01 ") .append(" WHERE KK0161_01.SVC_KEI_UCWK_STAT NOT IN ('910','920') ") .append(" AND (KK0161_01.SVC_KEI_UCWK_NO, KK0161_01.RSV_APLY_YMD || KK0161_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_01GENE.SVC_KEI_UCWK_NO,MAX(KK0161_01GENE.RSV_APLY_YMD || KK0161_01GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_01GENE ") .append(" WHERE KK0161_01GENE.SVC_KEI_UCWK_NO = KK0161_01.SVC_KEI_UCWK_NO ") .append(" AND KK0161_01GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0161_01GENE.RSV_APLY_CD = '2' ") .append(" AND KK0161_01GENE.MK_FLG = '0' ") .append(" GROUP BY KK0161_01GENE.SVC_KEI_UCWK_NO) ") .append(" ) KK0161_CUR ") .append(" ON KK0161_CUR.SVC_KEI_UCWK_NO = KK0341_CUR.SVC_KEI_UCWK_NO ") .append(" LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_02 ") .append(" WHERE KK0161_02.SVC_KEI_UCWK_STAT NOT IN ('910','920') ") .append(" AND (KK0161_02.SVC_KEI_UCWK_NO, NVL(KK0161_02.RSV_APLY_YMD,'20991231') || KK0161_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0161_02GENE.SVC_KEI_UCWK_NO,MAX(NVL(KK0161_02GENE.RSV_APLY_YMD,'20991231') || KK0161_02GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_SVC_KEI_UCWK KK0161_02GENE ") .append(" WHERE KK0161_02GENE.SVC_KEI_UCWK_NO = KK0161_02.SVC_KEI_UCWK_NO ") .append(" AND KK0161_02GENE.RSV_APLY_CD = '1' ") .append(" AND KK0161_02GENE.MK_FLG = '0' ") .append(" GROUP BY KK0161_02GENE.SVC_KEI_UCWK_NO) ") .append(" ) KK0161_RSV ") .append(" ON KK0161_RSV.SVC_KEI_UCWK_NO = KK0341_RSV.SVC_KEI_UCWK_NO ") .append(" LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_M_PCRS KK0791_01 ") .append(" WHERE (KK0791_01.PCRS_CD, KK0791_01.RSV_APLY_YMD || KK0791_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_01GENE.PCRS_CD,MAX(KK0791_01GENE.RSV_APLY_YMD || KK0791_01GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PCRS KK0791_01GENE ") .append(" WHERE KK0791_01GENE.PCRS_CD = KK0791_01.PCRS_CD ") .append(" AND KK0791_01GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_01GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_01GENE.PCRS_CD) ") .append(" ) KK0791_CUR ") .append(" ON KK0791_CUR.PCRS_CD = KK0341_CUR.PCRS_CD ") .append(" LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_M_PCRS KK0791_02 ") .append(" WHERE (KK0791_02.PCRS_CD, KK0791_02.RSV_APLY_YMD || KK0791_02.GENE_ADD_DTM) = ") .append(" (SELECT KK0791_02GENE.PCRS_CD,MAX(KK0791_02GENE.RSV_APLY_YMD || KK0791_02GENE.GENE_ADD_DTM) ") .append(" FROM KK_M_PCRS KK0791_02GENE ") .append(" WHERE KK0791_02GENE.PCRS_CD = KK0791_02.PCRS_CD ") .append(" AND KK0791_02GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0791_02GENE.MK_FLG = '0' ") .append(" GROUP BY KK0791_02GENE.PCRS_CD) ") .append(" ) KK0791_RSV ") .append(" ON KK0791_RSV.PCRS_CD = KK0341_RSV.PCRS_CD "); // 20140815 ANK-2198-00-00 Add Start sql_Buff.append("LEFT JOIN ( ") .append(" SELECT * ") .append(" FROM KK_M_KKTK_SVC KK0771 ") .append(" WHERE KK0771.MK_FLG = '0' ") .append(") KK0771 ") .append("ON KK0771.KKTK_SVC_CD = KK0341_CUR.KKTK_SVC_CD "); // 20140815 ANK-2198-00-00 Add End // SQL文_個別部1 if ("1".equals(inMsg.getObject(EKK0341B017CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" ORDER BY KK0341_CUR.KKTK_SVC_CD ASC ") .append(" ,KK0341_CUR.SVC_KEI_UCWK_NO ASC ") .append(" ,KK0341_CUR.SVC_KEI_NO ASC "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ("1".equals(inMsg.getString(EKK0341B017CBSMsg.FUNC_CODE))) { // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(ＫＥＹ＿機器提供サービスコードを指定) if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)) { CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)); } // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B017CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(ＫＥＹ＿機器提供サービスコードを指定) if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)) { CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B017CBSMsg.KEY_KKTK_SVC_CD)); } // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B017CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // ＫＥＹ＿工事案件番号が指定されている場合 if (!inMsg.isNull(EKK0341B017CBSMsg.KEY_KOJIAK_NO)) { // パラメータの設定(ＫＥＹ＿工事案件番号を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, inMsg.getObject(EKK0341B017CBSMsg.KEY_KOJIAK_NO)); } // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, ++iPCnt, opeDate); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0341B017CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0341B017CBSMsg1List.CUR_KKTK_SVC_KEI_NO , EKK0341B017CBSMsg1List.CUR_KK0341_GENE_ADD_DTM , EKK0341B017CBSMsg1List.CUR_KK0341_UPD_DTM , EKK0341B017CBSMsg1List.CUR_KK0341_TAKNKIKI_MODEL_CD , EKK0341B017CBSMsg1List.CUR_KIKI_SEIZO_NO , EKK0341B017CBSMsg1List.CUR_STB_SBT_CD , EKK0341B017CBSMsg1List.CUR_STB_SBT_CD_NM , EKK0341B017CBSMsg1List.CUR_TAKNKIKI_IDO_CD , EKK0341B017CBSMsg1List.CUR_TAKNKIKI_IDO_CD_NM , EKK0341B017CBSMsg1List.CUR_KK0341_HDD_CAPA_CD , EKK0341B017CBSMsg1List.CUR_HDD_CAPA_CD_NM , EKK0341B017CBSMsg1List.CUR_TAKNKIKI_SBT_CD , EKK0341B017CBSMsg1List.CUR_KKTK_SVC_CD , EKK0341B017CBSMsg1List.CUR_HAMBAI_SBT_CD , EKK0341B017CBSMsg1List.CUR_KKTK_SBT_CD , EKK0341B017CBSMsg1List.CUR_KK0341_PCRS_CD , EKK0341B017CBSMsg1List.CUR_SVC_KEI_UCWK_NO , EKK0341B017CBSMsg1List.CUR_KK0161_GENE_ADD_DTM , EKK0341B017CBSMsg1List.CUR_KK0161_UPD_DTM , EKK0341B017CBSMsg1List.CUR_KK0161_PCRS_CD , EKK0341B017CBSMsg1List.CUR_PCRS_CD_NM // 20140815 ANK-2198-00-00 Add Start , EKK0341B017CBSMsg1List.CUR_KIKI_MIN_USE_PRD // 20140815 ANK-2198-00-00 Add End , EKK0341B017CBSMsg1List.KOJIAK_TRGT_TAKNKIKI_NO , EKK0341B017CBSMsg1List.KOJIAK_TRGT_TAKNKIKI_STAT , EKK0341B017CBSMsg1List.KOJIAK_TRGT_KIKI_GRP_NO , EKK0341B017CBSMsg1List.STC_TEKKYO_FLG , EKK0341B017CBSMsg1List.TAKNKIKI_SBT_CD , EKK0341B017CBSMsg1List.TAKNKIKI_IDO_CD , EKK0341B017CBSMsg1List.RSV_TAKNKIKI_MODEL_CD , EKK0341B017CBSMsg1List.RSV_KIKI_SEIZO_NO , EKK0341B017CBSMsg1List.RSV_MAKER_CD , EKK0341B017CBSMsg1List.RSV_MACAD , EKK0341B017CBSMsg1List.RSV_STB_ID , EKK0341B017CBSMsg1List.RSV_HDD_CAPA_CD , EKK0341B017CBSMsg1List.RSV_VONU_BSPT_KH , EKK0341B017CBSMsg1List.RSV_STB_KEI_TV_COURSE_CD , EKK0341B017CBSMsg1List.RSV_ROUTER_KIND_CD , EKK0341B017CBSMsg1List.RSV_ROUTER_RENT_CD , EKK0341B017CBSMsg1List.CHGE_TAKNKIKI_MODEL_CD , EKK0341B017CBSMsg1List.CHGE_KIKI_SEIZO_NO , EKK0341B017CBSMsg1List.CHGE_MAKER_CD , EKK0341B017CBSMsg1List.CHGE_MACAD , EKK0341B017CBSMsg1List.CHGE_STB_ID , EKK0341B017CBSMsg1List.CHGE_HDD_CAPA_CD , EKK0341B017CBSMsg1List.CHGE_VONU_BSPT_KH , EKK0341B017CBSMsg1List.CHGE_STB_KEI_TV_COURSE_CD , EKK0341B017CBSMsg1List.CHGE_ROUTER_KIND_CD , EKK0341B017CBSMsg1List.CHGE_ROUTER_RENT_CD , EKK0341B017CBSMsg1List.RSV_KK0341_GENE_ADD_DTM , EKK0341B017CBSMsg1List.RSV_KK0341_UPD_DTM , EKK0341B017CBSMsg1List.RSV_KK0341_TAKNKIKI_MODEL_CD , EKK0341B017CBSMsg1List.RSV_RSV_KIKI_SEIZO_NO , EKK0341B017CBSMsg1List.RSV_STB_SBT_CD , EKK0341B017CBSMsg1List.RSV_STB_SBT_CD_NM , EKK0341B017CBSMsg1List.RSV_TAKNKIKI_IDO_CD , EKK0341B017CBSMsg1List.RSV_TAKNKIKI_IDO_CD_NM , EKK0341B017CBSMsg1List.RSV_KK0341_HDD_CAPA_CD , EKK0341B017CBSMsg1List.RSV_HDD_CAPA_CD_NM , EKK0341B017CBSMsg1List.RSV_TAKNKIKI_SBT_CD , EKK0341B017CBSMsg1List.RSV_KKTK_SVC_CD , EKK0341B017CBSMsg1List.RSV_HAMBAI_SBT_CD , EKK0341B017CBSMsg1List.RSV_KKTK_SBT_CD , EKK0341B017CBSMsg1List.RSV_KK0341_PCRS_CD , EKK0341B017CBSMsg1List.RSV_KK0161_GENE_ADD_DTM , EKK0341B017CBSMsg1List.RSV_KK0161_UPD_DTM , EKK0341B017CBSMsg1List.RSV_KK0161_PCRS_CD , EKK0341B017CBSMsg1List.RSV_PCRS_CD_NM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0341B017CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0341B017CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0341B017CBSMsg.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(EKK0341B017CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }