/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEKK0081B508TPDA * ソースファイル名：JSYejbEKK0081B508TPDA.java * 作成者：EK908996 * 日付：2012年09月10日 *＜機能概要＞ * eo光電話サービス契約情報一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * ｖ3.00 2012/05/24 FU)南岡カレント取得SQL修正 * ｖ3.01　　　2012/06/01 FU)南岡【UI残(No.170)】 * 電話帳記載名文言修正対応 * v4.00 2012/07/06 FJ)中谷【ANK-1163-00-00】変更系申込が可能となるタイミングについて * v4.00 2012/09/10 FJ)知識「案件番号： ANK-0522-00-00」番ポ申込時における法人のNTT電話番号の確認対応について * v4.00 2012/10/11 FJ)美濃原【IT1-2012-0001273】システム日付をオンライン運用日付に修正 * v4.00 2012/11/30 FJ)中谷【IT2-2012-0001914】サービス契約回線内訳.使用開始年月日の条件を削除 * v4.00 2012/12/12 FJ)美濃原【TAI-2012-0000136】品質強化サブタイプのカレント取得を修正 * v5.00 2013/02/08 FJ)西村【ST2-2013-0000691】予約適用コード(EKK0081B508)追加対応 * ｖ10.00 2014/08/02 FJ)西川【OM-2014-0002441】水平展開　適用開始日を参照するように修正 * ｖ10.00 2014/08/30 FJ)西川【OM-2014-0002778】電話セレクトパック課金チェックエラー * ｖ14.00 2015/06/10 FJ)中谷【ST4-2014-0000082】性能障害対応 * ｖ14.00 2015/07/03 FJ)西川【OM-2015-0001474】電話帳申込.電話帳記載コードからサービス契約内訳＜eo光電話＞.ハローページ記載有無に変更 * ｖ33.00.00 2017/10/11 FJ)河邊【OM-2017-0001002】フロント転送電話設定 * v50.00.00 2020/06/30 FJ)張本【ANK-3862-00-00】【CMP】WEB電話オプション外出し対応 * ********************************************************************************/ 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.EKK0081B508CBSMsg; import eo.ejb.cbs.cbsmsg.EKK0081B508CBSMsg1List; import eo.ejb.cbm.entity.KK0081ETMsg; import eo.ejb.common.JCCModelCommon; /** * * テンプレートDBアクセス部品　EKK0081B508_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEKK0081B508TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEKK0081B508TPDA() { } /** * EKK0081B508_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(KK0081ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" KEI.SYSID, ") .append(" KEI.SVC_KEI_NO, ") .append(" KEI.SVC_KEI_STAT, ") .append(" KEI.PCRS_CD AS KK0081_PCRS_CD, ") .append(" KEI.PPLAN_CD AS KK0081_PPLAN_CD, ") .append(" KEI.SHK_DFLT_PWD, ") .append(" KEI.CUST_ID_PWD, ") .append(" UCWK.SVC_KEI_UCWK_NO, ") .append(" UCWK.SVC_KEI_UCWK_STAT, ") .append(" UCWK.WEB_OP_ADD_FAIL_FLG, ") .append(" UCWK.TELNO, ") .append(" UCWK.NTT_NO_GUIDE_UM, ") .append(" UCWK.DNWACHO_KISAI_CD AS DNWACHO_KISAI_CD, ") .append(" ( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_DIV = UCWK.DNWACHO_KISAI_CD ") .append(" AND ZM0171.CD_SBT_CD = 'CD00438' ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") // ★1 .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS DNWACHO_KISAI_CD_NM, ") .append(" UCWK.VA_PORT_NO, ") .append(" KAISEN.SVC_KEI_KAISEN_UCWK_NO, ") .append(" KAISEN.SVC_KEI_KAISEN_UCWK_STAT, ") .append(" KAISEN.KAISEN_PLACE_AD_CD, ") .append(" KAISEN.KAISEN_PLACE_PCD, ") .append(" KAISEN.KAISEN_PLACE_STATE_NM, ") .append(" KAISEN.KAISEN_PLACE_CITY_NM, ") .append(" KAISEN.KAISEN_PLACE_OAZTSU_NM, ") .append(" KAISEN.KAISEN_PLACE_AZCHO_NM, ") .append(" KAISEN.KAISEN_PLACE_BNCHIGO, ") .append(" KAISEN.KAISEN_PLACE_ADRTTM, ") .append(" KAISEN.KAISEN_PLACE_ADRRM, ") .append(" KAISEN.KAISEN_PLACE_NO, ") .append(" OP.OP_SVC_KEI_NO, ") .append(" OP.OP_SVC_KEI_STAT, ") .append(" OP.OP_SVC_CD, ") .append(" OP.N_050_OP_TELNO, ") .append(" OP.AGING_NO, ") .append(" SBOP.SBOP_SVC_KEI_NO, ") .append(" SBOP.SBOP_SVC_KEI_STAT, ") .append(" SBOP.SBOP_SVC_CD, ") .append(" ( ") .append(" SELECT ") .append(" KK0821.KAIHK_PSB_PRD ") .append(" FROM ") .append(" KK_M_OP_SVC KK0821 ") .append(" WHERE ") .append(" KK0821.OP_SVC_CD = OP.OP_SVC_CD ") .append(" AND (KK0821.OP_SVC_CD, KK0821.RSV_APLY_YMD || KK0821.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0821G.OP_SVC_CD,MAX(KK0821G.RSV_APLY_YMD || KK0821G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_M_OP_SVC KK0821G ") .append(" WHERE ") .append(" KK0821G.OP_SVC_CD = KK0821.OP_SVC_CD ") .append(" AND KK0821G.RSV_APLY_YMD <= ? ") // ★2 .append(" AND KK0821G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0821G.OP_SVC_CD ") .append(" ) ") .append(" AND KK0821.OP_SVC_TSTAYMD <= ? ") // ★3 .append(" AND KK0821.OP_SVC_CD IN ('B022', 'B023', 'B024', 'B025', 'B026', 'B027','B028', 'B029', 'B071', 'B072', 'B135') ") .append(" ) AS KAIHK_PSB_PRD, ") .append(" KEI.RSV_APLY_YMD AS KK0081_RSV_APLY_YMD, ") .append(" UCWK.KK0161_RSV_APLY_YMD, ") .append(" UCWK.KK0161_RSV_APLY_CD, ") .append(" OP.KK0351_RSV_APLY_YMD, ") .append(" SBOP.KK0401_RSV_APLY_YMD, ") .append(" ( ") .append(" SELECT ") .append(" MAX(KK1681.IDO_RSV_DTL_CD) ") .append(" FROM ") .append(" KK_T_IDO_RSV KK1681 ") .append(" WHERE ") .append(" KK1681.SVC_KEI_NO = KEI.SVC_KEI_NO ") .append(" AND KK1681.IDO_RSV_DTL_CD = '003' ") .append(" AND KK1681.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" AND KK1681.MK_FLG = '0' ") .append(" ) AS KK0081_IDO_RSV_DTL_CD, ") .append(" OP.KK0351_IDO_RSV_DTL_CD, ") .append(" SBOP.KK0401_IDO_RSV_DTL_CD, ") .append(" KEI.GENE_ADD_DTM AS KK0081_GENE_ADD_DTM, ") .append(" UCWK.KK0161_GENE_ADD_DTM, ") .append(" OP.KK0351_GENE_ADD_DTM, ") .append(" SBOP.KK0401_GENE_ADD_DTM, ") .append(" OP.ZM0111_UPD_DTM_BF, ") .append(" UCWK.SHK_TEL_OP_SETTE_RMTS_PWD, ") .append(" UCWK.TEL_OP_SETTE_RMTS_PWD, ") .append(" OP.TWRYO_STIAM_TTU_TCHI_STIAM ") .append(" FROM ") .append(" /*EKK0081B508*/ ") .append(" KK_T_SVC_KEI KEI ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" U.SVC_KEI_UCWK_NO, ") .append(" U.KK0161_GENE_ADD_DTM, ") .append(" U.SVC_KEI_UCWK_STAT, ") .append(" U.KK0161_SVC_KEI_NO, ") .append(" U.WEB_OP_ADD_FAIL_FLG, ") .append(" U.KK0161_RSV_APLY_YMD, ") .append(" U.KK0161_RSV_APLY_CD, ") .append(" U.KK0191_SVC_KEI_UCWK_NO, ") .append(" U.TELNO, ") .append(" U.SHK_TEL_OP_SETTE_RMTS_PWD, ") .append(" U.TEL_OP_SETTE_RMTS_PWD, ") .append(" U.NTT_NO_GUIDE_UM, ") .append(" U.VA_PORT_NO, ") .append(" U.HELLOPAGE_KEISAI_UM AS DNWACHO_KISAI_CD ") .append(" FROM ( ") .append(" SELECT ") .append(" KK0161.SVC_KEI_UCWK_NO, ") .append(" KK0161.GENE_ADD_DTM AS KK0161_GENE_ADD_DTM, ") .append(" KK0161.SVC_KEI_UCWK_STAT, ") .append(" KK0161.SVC_KEI_NO AS KK0161_SVC_KEI_NO, ") .append(" KK0161.WEB_OP_ADD_FAIL_FLG, ") .append(" KK0161.RSV_APLY_YMD AS KK0161_RSV_APLY_YMD, ") .append(" KK0161.RSV_APLY_CD AS KK0161_RSV_APLY_CD, ") .append(" KK0191.SVC_KEI_UCWK_NO AS KK0191_SVC_KEI_UCWK_NO, ") .append(" KK0191.TELNO, ") .append(" KK0191.SHK_TEL_OP_SETTE_RMTS_PWD, ") .append(" KK0191.TEL_OP_SETTE_RMTS_PWD, ") .append(" KK0191.NTT_NO_GUIDE_UM, ") .append(" KK0191.VA_PORT_NO, ") .append(" KK0191.HELLOPAGE_KEISAI_UM, ") .append(" MAX(KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) OVER (PARTITION BY KK0191.TELNO_JUN) AS KK0161_MAX_TELNO_GENE_ADD_DTM") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161 ") .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ") .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ") .append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ") .append(" AND KK0191.RSV_APLY_CD = '2' ") .append(" AND KK0191.MK_FLG = '0' ") .append(" WHERE ") .append(" KK0161.SVC_KEI_NO = ? ") // ★4 .append(" AND KK0161.SVC_KEI_UCWK_STAT in ('010', '020', '030', '100') ") .append(" AND KK0161.KEIZK_AF_KEI_CHGECHU_FLG = '0' ") .append(" AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0161G.SVC_KEI_UCWK_NO,MAX(KK0161G.RSV_APLY_YMD || KK0161G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_SVC_KEI_UCWK KK0161G ") .append(" WHERE ") .append(" KK0161G.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ") .append(" AND KK0161G.RSV_APLY_YMD <= ? ") // ★5 .append(" AND KK0161G.RSV_APLY_CD = '2' ") .append(" AND KK0161G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0161G.SVC_KEI_UCWK_NO ") .append(" ) ") .append(" ) U ") .append(" WHERE ") // OM-2017-0001002 2017/10/11 MOD START // .append(" U.KK0161_RSV_APLY_YMD || U.KK0161_GENE_ADD_DTM = U.KK0161_MAX_TELNO_GENE_ADD_DTM ") .append(" U.SVC_KEI_UCWK_STAT NOT IN ('910', '920') ") // OM-2017-0001002 2017/10/11 MOD END .append(" )UCWK ") .append(" ON KEI.SVC_KEI_NO = UCWK.KK0161_SVC_KEI_NO ") .append(" INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241 ") .append(" ON KK0241.SVC_KEI_NO = KEI.SVC_KEI_NO ") .append(" AND KK0241.MK_FLG = '0' ") .append(" AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? ") // ★6 .append(" AND KK0241.KAISEN_UCWK_USE_ENDYMD = ") .append(" ( ") .append(" SELECT ") .append(" MAX(KK0241_MAX.KAISEN_UCWK_USE_ENDYMD) ") .append(" FROM ") .append(" KK_T_KAISEN_TG_SVKEI KK0241_MAX ") .append(" WHERE ") .append(" KK0241_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO ") .append(" AND KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ? ") // ★7 .append(" AND KK0241_MAX.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0241_MAX.SVC_KEI_NO ") .append(" ) ") .append(" INNER JOIN KK_T_SVKEI_KAISEN_UW KAISEN ") .append(" ON KAISEN.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND (KAISEN.SVC_KEI_KAISEN_UCWK_NO, KAISEN.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0251G.SVC_KEI_KAISEN_UCWK_NO,MAX(KK0251G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_SVKEI_KAISEN_UW KK0251G ") .append(" WHERE ") .append(" KK0251G.SVC_KEI_KAISEN_UCWK_NO = KAISEN.SVC_KEI_KAISEN_UCWK_NO ") .append(" AND KK0251G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0251G.SVC_KEI_KAISEN_UCWK_NO ") .append(" ) ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" O.OP_SVC_KEI_NO, ") .append(" O.KK0351_GENE_ADD_DTM, ") .append(" O.OP_SVC_KEI_STAT, ") .append(" O.OP_SVC_CD, ") .append(" O.KK0351_SVC_KEI_NO, ") .append(" O.KK0351_SVC_KEI_UCWK_NO, ") .append(" O.KK0351_RSV_APLY_YMD, ") .append(" O.KK0371_OP_SVC_KEI_NO, ") .append(" O.N_050_OP_TELNO, ") .append(" O.TWRYO_STIAM_TTU_TCHI_STIAM, ") .append(" OIR.KK0351_IDO_RSV_DTL_CD, ") .append(" OIR.KK1681_OP_SVC_KEI_NO, ") .append(" AG.AGING_NO, ") .append(" AG.AGING_TG_VALUE, ") .append(" AG.ZM0111_UPD_DTM_BF ") .append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" KK0351.OP_SVC_KEI_NO, ") .append(" KK0351.GENE_ADD_DTM AS KK0351_GENE_ADD_DTM, ") .append(" KK0351.OP_SVC_KEI_STAT, ") .append(" KK0351.OP_SVC_CD, ") .append(" KK0351.SVC_KEI_NO AS KK0351_SVC_KEI_NO, ") .append(" KK0351.SVC_KEI_UCWK_NO AS KK0351_SVC_KEI_UCWK_NO, ") .append(" KK0351.RSV_APLY_YMD AS KK0351_RSV_APLY_YMD, ") .append(" KK0371.OP_SVC_KEI_NO AS KK0371_OP_SVC_KEI_NO, ") .append(" KK0371.GENE_ADD_DTM AS KK0371_GENE_ADD_DTM, ") .append(" KK0371.N_050_OP_TELNO, ") .append(" KK0371.TWRYO_STIAM_TTU_TCHI_STIAM, ") .append(" MAX(KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) OVER (PARTITION BY KK0351.SVC_KEI_UCWK_NO,KK0351.OP_SVC_CD) KK0351_MAX_OPCD_GENE_ADD_DTM ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351 ") .append(" INNER JOIN KK_T_OPSVKEI_TEL KK0371 ") .append(" ON KK0351.OP_SVC_KEI_NO = KK0371.OP_SVC_KEI_NO ") .append(" AND KK0351.GENE_ADD_DTM = KK0371.GENE_ADD_DTM ") .append(" AND KK0371.RSV_APLY_CD = '2' ") .append(" AND KK0371.MK_FLG = '0' ") .append(" WHERE ") .append(" (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0351G.OP_SVC_KEI_NO,MAX(KK0351G.RSV_APLY_YMD || KK0351G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_OP_SVC_KEI KK0351G ") .append(" WHERE ") .append(" KK0351G.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO ") .append(" AND KK0351G.RSV_APLY_YMD <= ? ") // ★8 .append(" AND KK0351G.RSV_APLY_CD = '2' ") .append(" AND KK0351G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0351G.OP_SVC_KEI_NO ") .append(" ) ") .append(" AND KK0351.OP_SVC_CD IN ('B022', 'B023', 'B024', 'B025', 'B026', 'B027','B028', 'B029', 'B071', 'B072', 'B135') ") .append(" AND KK0351.PCRS_CD IN ('B26', 'B27', 'B28', 'B29', 'B30', 'B31', 'B32','B33', 'B75', 'B76', 'BE6') ") .append(" AND KK0351.PPLAN_CD IN ('PB2601', 'PB2701', 'PB2801', 'PB2901', 'PB3001','PB3101', 'PB3201', 'PB3301', 'PB7501', 'PB7601', 'PBE601') ") .append(" AND KK0351.OYA_KEI_SKBT_CD = '03' ") .append(" AND KK0351.SYSID = ? ") // ★9 .append(" ) O ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK1681.IDO_RSV_DTL_CD AS KK0351_IDO_RSV_DTL_CD, ") .append(" KK1681.OP_SVC_KEI_NO AS KK1681_OP_SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_IDO_RSV KK1681 ") .append(" WHERE ") .append(" KK1681.IDO_RSV_DTL_CD IN ('014', '015', '016') ") .append(" AND KK1681.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" AND KK1681.MK_FLG = '0' ") .append(" ) OIR ") .append(" ON O.OP_SVC_KEI_NO = OIR.KK1681_OP_SVC_KEI_NO ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" ZM0111.AGING_NO, ") .append(" ZM0111.AGING_TG_VALUE, ") .append(" ( ") .append(" SELECT ") .append(" MAX(ZM0111M.UPD_DTM) ") .append(" FROM ") .append(" ZM_T_AGING ZM0111M ") .append(" WHERE ") .append(" ZM0111.AGING_NO = ZM0111M.AGING_NO ") .append(" ) AS ZM0111_UPD_DTM_BF ") .append(" FROM ") .append(" ZM_T_AGING ZM0111 ") .append(" WHERE ") .append(" ZM0111.AGING_STAT IN ('100', '200') ") .append(" AND ZM0111.AGING_SBT_CD = '001' ") .append(" AND ZM0111.USE_STA_DTM <= ? ") // ★10 .append(" AND ZM0111.MK_FLG = '0' ") .append(" ) AG ") .append(" ON O.N_050_OP_TELNO = AG.AGING_TG_VALUE ") .append(" WHERE ") // OM-2017-0001002 2017/10/11 MOD START // .append(" O.KK0351_RSV_APLY_YMD || O.KK0351_GENE_ADD_DTM = O.KK0351_MAX_OPCD_GENE_ADD_DTM ") .append(" O.OP_SVC_KEI_STAT NOT IN ('910', '920') ") // OM-2017-0001002 2017/10/11 MOD END .append(" ) OP ") .append(" ON UCWK.SVC_KEI_UCWK_NO = OP.KK0351_SVC_KEI_UCWK_NO ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" S.KK0401_OP_SVC_KEI_NO, ") .append(" S.SBOP_SVC_KEI_NO, ") .append(" S.KK0401_GENE_ADD_DTM, ") .append(" S.SBOP_SVC_KEI_STAT, ") .append(" S.SBOP_SVC_CD, ") .append(" S.KK0401_RSV_APLY_YMD, ") .append(" S.KK0431_OP_SVC_KEI_NO, ") .append(" S.KK0431_SBOP_SVC_KEI_NO, ") .append(" SIR.KK0401_IDO_RSV_DTL_CD, ") .append(" SIR.KK1681_SBOP_SVC_KEI_NO ") .append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" KK0401.OP_SVC_KEI_NO AS KK0401_OP_SVC_KEI_NO, ") .append(" KK0401.SBOP_SVC_KEI_NO, ") .append(" KK0401.GENE_ADD_DTM AS KK0401_GENE_ADD_DTM, ") .append(" KK0401.SBOP_SVC_KEI_STAT, ") .append(" KK0401.SBOP_SVC_CD, ") .append(" KK0401.RSV_APLY_YMD AS KK0401_RSV_APLY_YMD, ") .append(" KK0431.OP_SVC_KEI_NO AS KK0431_OP_SVC_KEI_NO, ") .append(" KK0431.GENE_ADD_DTM AS KK0431_GENE_ADD_DTM, ") .append(" KK0431.SBOP_SVC_KEI_NO AS KK0431_SBOP_SVC_KEI_NO, ") .append(" MAX(KK0401.RSV_APLY_YMD || KK0401.GENE_ADD_DTM) OVER (PARTITION BY KK0401.OP_SVC_KEI_NO) ") .append(" KK0401_MAX_SBOPCD_GENE_ADD_DTM ") .append(" FROM ") .append(" KK_T_SBOP_SVC_KEI KK0401 ") .append(" INNER JOIN KK_T_SBOPSVKEI_TEL KK0431 ") .append(" ON KK0401.OP_SVC_KEI_NO = KK0431.OP_SVC_KEI_NO ") .append(" AND KK0401.SBOP_SVC_KEI_NO = KK0431.SBOP_SVC_KEI_NO ") .append(" AND KK0401.GENE_ADD_DTM = KK0431.GENE_ADD_DTM ") .append(" AND KK0431.RSV_APLY_CD = '2' ") .append(" AND KK0431.MK_FLG = '0' ") .append(" WHERE ") .append(" (KK0401.OP_SVC_KEI_NO, KK0401.SBOP_SVC_KEI_NO, KK0401.RSV_APLY_YMD || KK0401.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0401G.OP_SVC_KEI_NO,KK0401G.SBOP_SVC_KEI_NO,MAX(KK0401G.RSV_APLY_YMD || KK0401G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_SBOP_SVC_KEI KK0401G ") .append(" WHERE ") .append(" KK0401G.OP_SVC_KEI_NO = KK0401.OP_SVC_KEI_NO ") .append(" AND KK0401G.SBOP_SVC_KEI_NO = KK0401.SBOP_SVC_KEI_NO ") .append(" AND KK0401G.RSV_APLY_YMD <= ? ") // ★11 .append(" AND KK0401G.RSV_APLY_CD = '2' ") .append(" AND KK0401G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0401G.OP_SVC_KEI_NO, KK0401G.SBOP_SVC_KEI_NO ") .append(" ) ") .append(" AND KK0401.SBOP_SVC_CD IN ('D07', 'D08') ") .append(" AND KK0401.PCRS_CD IN ('D07', 'D08') ") .append(" AND KK0401.PPLAN_CD IN ('PD0701', 'PD0801') ") .append(" ) S ") .append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" KK1681.IDO_RSV_DTL_CD AS KK0401_IDO_RSV_DTL_CD, ") .append(" KK1681.SBOP_SVC_KEI_NO AS KK1681_SBOP_SVC_KEI_NO ") .append(" FROM ") .append(" KK_T_IDO_RSV KK1681 ") .append(" WHERE ") .append(" KK1681.IDO_RSV_DTL_CD IN ('017', '018') ") .append(" AND KK1681.IDO_RSV_STAT_CD IN ('00', '03') ") .append(" AND KK1681.MK_FLG = '0' ") .append(" ) SIR ") .append(" ON S.SBOP_SVC_KEI_NO = SIR.KK1681_SBOP_SVC_KEI_NO ") .append(" WHERE ") // OM-2017-0001002 2017/10/11 MOD START // .append(" S.KK0401_RSV_APLY_YMD || S.KK0401_GENE_ADD_DTM = S.KK0401_MAX_SBOPCD_GENE_ADD_DTM ") .append(" S.SBOP_SVC_KEI_STAT NOT IN ('910', '920') ") // OM-2017-0001002 2017/10/11 MOD END .append(" ) SBOP ") .append(" ON OP.OP_SVC_KEI_NO = SBOP.KK0401_OP_SVC_KEI_NO "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EKK0081B508CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" KEI.SYSID = ? ") // ★12 .append(" AND KEI.SVC_KEI_NO = ? ") // ★13 .append(" AND (KEI.SVC_KEI_NO, KEI.RSV_APLY_YMD || KEI.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0081G.SVC_KEI_NO,MAX(KK0081G.RSV_APLY_YMD || KK0081G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081G ") .append(" WHERE ") .append(" KK0081G.SVC_KEI_NO = KEI.SVC_KEI_NO ") .append(" AND KK0081G.RSV_APLY_YMD <= ? ") // ★14 .append(" AND KK0081G.RSV_APLY_CD = '2' ") .append(" AND KK0081G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0081G.SVC_KEI_NO ") .append(" ) "); } if ( "2".equals( inMsg.getObject(EKK0081B508CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE ") .append(" KEI.SYSID = ? ") // ★12 .append(" AND KEI.SVC_KEI_NO = ? ") // ★13 .append(" AND UCWK.TELNO = ? ") // ★14 .append(" AND (KEI.SVC_KEI_NO, KEI.RSV_APLY_YMD || KEI.GENE_ADD_DTM) = ") .append(" ( ") .append(" SELECT ") .append(" KK0081G.SVC_KEI_NO,MAX(KK0081G.RSV_APLY_YMD || KK0081G.GENE_ADD_DTM) ") .append(" FROM ") .append(" KK_T_SVC_KEI KK0081G ") .append(" WHERE ") .append(" KK0081G.SVC_KEI_NO = KEI.SVC_KEI_NO ") .append(" AND KK0081G.RSV_APLY_YMD <= ? ") // ★15 .append(" AND KK0081G.RSV_APLY_CD = '2' ") .append(" AND KK0081G.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0081G.SVC_KEI_NO ") .append(" ) "); } sql_Buff.append(" ORDER BY ") .append(" KEI.GENE_ADD_DTM, ") .append(" UCWK.SVC_KEI_UCWK_NO DESC, ") .append(" UCWK.KK0161_GENE_ADD_DTM, ") .append(" OP.OP_SVC_CD, ") .append(" OP.KK0351_GENE_ADD_DTM DESC, ") .append(" SBOP.SBOP_SVC_CD, ") .append(" SBOP.KK0401_GENE_ADD_DTM DESC "); //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); /* ++++++++++ v3.00 修正開始 ++++++++++ */ String operateDateTime = JCCModelCommon.getOpeDateTimeStamp(inMsg, null); String operateDate = operateDateTime.substring(0,8); // パラメータのカウント int iPCnt = 1; // PreparedStatementの指定されたパラメータカラムに指定された値を設定 // ■SQL基本部 // 1:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 2:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 3:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 4:パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SVC_KEI_NO)); // 5:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 6:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 7:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 8:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // 9:パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SYSID)); // 10:パラメータの設定(オンライン運用日付を指定：YYYYMMDDHH24MISSsss) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDateTime); // 11:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); // ■SQL個別部：機能コード1 if ("1".equals(inMsg.getString(EKK0081B508CBSMsg.FUNC_CODE))) { // 12:パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SYSID)); // 13:パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SVC_KEI_NO)); // 14:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); } // ■SQL個別部：機能コード2 if ("2".equals(inMsg.getString(EKK0081B508CBSMsg.FUNC_CODE))) { // 12:パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SYSID)); // 13:パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_SVC_KEI_NO)); // 14:パラメータの設定(ＫＥＹ＿電話番号を指定) CAANJDBCUtil.setParam(pstmt, iPCnt++, inMsg.getObject(EKK0081B508CBSMsg.KEY_TELNO)); // 15:パラメータの設定(オンライン運用日付を指定：YYYYMMDD) CAANJDBCUtil.setParam(pstmt, iPCnt++, operateDate); } /* ++++++++++ v3.00 修正終了 ++++++++++ */ // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK0081B508CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK0081B508CBSMsg1List.SYSID , EKK0081B508CBSMsg1List.SVC_KEI_NO , EKK0081B508CBSMsg1List.SVC_KEI_STAT , EKK0081B508CBSMsg1List.KK0081_PCRS_CD , EKK0081B508CBSMsg1List.KK0081_PPLAN_CD , EKK0081B508CBSMsg1List.SHK_DFLT_PWD , EKK0081B508CBSMsg1List.CUST_ID_PWD , EKK0081B508CBSMsg1List.SVC_KEI_UCWK_NO , EKK0081B508CBSMsg1List.SVC_KEI_UCWK_STAT , EKK0081B508CBSMsg1List.WEB_OP_ADD_FAIL_FLG , EKK0081B508CBSMsg1List.TELNO , EKK0081B508CBSMsg1List.NTT_NO_GUIDE_UM , EKK0081B508CBSMsg1List.DNWACHO_KISAI_CD , EKK0081B508CBSMsg1List.DNWACHO_KISAI_CD_NM , EKK0081B508CBSMsg1List.VA_PORT_NO , EKK0081B508CBSMsg1List.SVC_KEI_KAISEN_UCWK_NO , EKK0081B508CBSMsg1List.SVC_KEI_KAISEN_UCWK_STAT , EKK0081B508CBSMsg1List.KAISEN_PLACE_AD_CD , EKK0081B508CBSMsg1List.KAISEN_PLACE_PCD , EKK0081B508CBSMsg1List.KAISEN_PLACE_STATE_NM , EKK0081B508CBSMsg1List.KAISEN_PLACE_CITY_NM , EKK0081B508CBSMsg1List.KAISEN_PLACE_OAZTSU_NM , EKK0081B508CBSMsg1List.KAISEN_PLACE_AZCHO_NM , EKK0081B508CBSMsg1List.KAISEN_PLACE_BNCHIGO , EKK0081B508CBSMsg1List.KAISEN_PLACE_ADRTTM , EKK0081B508CBSMsg1List.KAISEN_PLACE_ADRRM , EKK0081B508CBSMsg1List.KAISEN_PLACE_NO , EKK0081B508CBSMsg1List.OP_SVC_KEI_NO , EKK0081B508CBSMsg1List.OP_SVC_KEI_STAT , EKK0081B508CBSMsg1List.OP_SVC_CD , EKK0081B508CBSMsg1List.N_050_OP_TELNO , EKK0081B508CBSMsg1List.AGING_NO , EKK0081B508CBSMsg1List.SBOP_SVC_KEI_NO , EKK0081B508CBSMsg1List.SBOP_SVC_KEI_STAT , EKK0081B508CBSMsg1List.SBOP_SVC_CD , EKK0081B508CBSMsg1List.KAIHK_PSB_PRD , EKK0081B508CBSMsg1List.KK0081_RSV_APLY_YMD , EKK0081B508CBSMsg1List.KK0161_RSV_APLY_YMD , EKK0081B508CBSMsg1List.KK0161_RSV_APLY_CD , EKK0081B508CBSMsg1List.KK0351_RSV_APLY_YMD , EKK0081B508CBSMsg1List.KK0401_RSV_APLY_YMD , EKK0081B508CBSMsg1List.KK0081_IDO_RSV_DTL_CD , EKK0081B508CBSMsg1List.KK0351_IDO_RSV_DTL_CD , EKK0081B508CBSMsg1List.KK0401_IDO_RSV_DTL_CD , EKK0081B508CBSMsg1List.KK0081_GENE_ADD_DTM , EKK0081B508CBSMsg1List.KK0161_GENE_ADD_DTM , EKK0081B508CBSMsg1List.KK0351_GENE_ADD_DTM , EKK0081B508CBSMsg1List.KK0401_GENE_ADD_DTM , EKK0081B508CBSMsg1List.ZM0111_UPD_DTM_BF , EKK0081B508CBSMsg1List.SHK_TEL_OP_SETTE_RMTS_PWD , EKK0081B508CBSMsg1List.TEL_OP_SETTE_RMTS_PWD , EKK0081B508CBSMsg1List.TWRYO_STIAM_TTU_TCHI_STIAM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EKK0081B508CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EKK0081B508CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EKK0081B508CBSMsg.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(EKK0081B508CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }