/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECH0331B010TPDA * ソースファイル名：JSYejbECH0331B010TPDA.java * 作成者 ：EKek0088 * 日付 ：2011年09月18日 *＜機能概要＞ * SQLFacility * テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ5.00.00 2013/07/012 FJ) 林 【LT-2013-0000378】 請求書引抜き一覧画面にて、抽出対象外のデータが出力される * ｖ5.01.00 2013/07/27 FJ) 早崎 【IT1-2013-0001454】 請求書引抜き一覧画面にて、登録区分項目が空白 * ｖ5.02.00 2013/08/11 YCC)間 請求書引抜テーブルの日付検索条件が'20991231'固定だったため 引抜終了日 <= 運用日付 < 引抜開始日に修正 * スケジュール定義よりイベントコード72の日付を参照していた箇所をコード39に修正 * ｖ5.03.00 2013/10/30 FJ) 伊藤 【OM-2013-0002528,3283】請求書引抜存在チェック用に機能コードを追加。及び、抽出項目に請求書引抜フラグを追加。 * ｖ5.04.00 2013/11/05 FJ) 伊藤 【OM-2013-0004014】引抜開始日、引抜終了日の検索条件の修正 * ｖ8.00.00 2014/03/18 FJ) 伊藤 【IT2-2014-0000163】料金対応記録請求年月がNULLになっていたのを修正 ********************************************************************************/ package eo.ejb.cbs.sqlf; import com.fujitsu.futurity.model.base.CAANConnectionMgr; 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 java.util.ArrayList; import java.util.List; import eo.common.constant.JACStrConst; import eo.ejb.cbs.cbsmsg.ECH0331B010CBSMsg; import eo.ejb.cbs.cbsmsg.ECH0331B010CBSMsg1List; import eo.ejb.cbm.cbmmsg.CH0501CBMMsg; import eo.ejb.cbm.entity.CH0331ETMsg; import eo.ejb.cbm.entity.CH0501ETMsg; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　ECH0331B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECH0331B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * データ抽出タイプ：ＫＥＹ_請求年月指定（最新請求年月） */ private static final String SELEC_TYPE_SEIKY_YM_LATEST = "1"; /** * データ抽出タイプ：ＫＥＹ_請求年月指定 */ private static final String SELEC_TYPE_SEIKY_YM_ = "2"; /** * データ抽出タイプ：ＫＥＹ_請求年月未指定（ＫＥＹ_請求契約番号） */ private static final String SELEC_TYPE_SEIKY_KEI_NO = "3"; /** * コンストラクタ */ public JSYejbECH0331B010TPDA() { } /** * ECH0331B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt1 = null; PreparedStatement pstmt2 = null; // リザルトセット ResultSet rsltQuery1 = null; ResultSet rsltQuery2 = null; // ページング用 int iTotalSearchNum = 0; // 総検索数 int iTotalPageNum = 0; // 総ページ数 int iDisplayNum = 0; // 表示件数 int iMaxSearchNum = 0; // 最大検索件数 int iDisplayPageNum = 0; // 表示ページ番号 int iArrayCopyStart = 0; // 取得範囲先頭 int iArrayCopyCount = 0; // 取得件数 // パラメータ設定リスト List replaceListCnt = new ArrayList(); List replaceListCnt2 = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ //コネクション取得 con1 = JSYejbConnection.getConnection(CH0331ETMsg.getTableName()); if (!"4".equals( inMsg.getObject(ECH0331B010CBSMsg.FUNC_CODE).toString())) { String schdlTeigiYM = "" ; String selecType = "" ; // スケジュール定義より最新の請求年月を取得 schdlTeigiYM = getSchdlTeigiYM(inMsg, inContext); // ＫＥＹ＿請求年月が指定されている場合 if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_SEIKY_YM)) { selecType = SELEC_TYPE_SEIKY_YM_LATEST; if(!inMsg.getString(ECH0331B010CBSMsg.KEY_SEIKY_YM).equals(schdlTeigiYM)) { selecType = SELEC_TYPE_SEIKY_YM_; } } else { selecType = SELEC_TYPE_SEIKY_KEI_NO; } // 総件数取得 // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" COUNT(*) AS CNT "); // from句以降追加 StringBuilder condition = getCondition1(inMsg, inContext, replaceListCnt, selecType, schdlTeigiYM, true); sql_Buff.append(condition); //prepareStatementにSQL文をセット pstmt1 = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceListCnt.size(); idx++) { CAANJDBCUtil.setParam(pstmt1, idx + 1, replaceListCnt.get(idx)); } // ResultSetの取得 rsltQuery1 = pstmt1.executeQuery(); // ECH0331B010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECH0331B010CBSMsg.TOTAL_SEARCH_NUM }; rsltQuery1.next(); mapMessage(rsltQuery1, msgKeyList1, inMsg); // パラメータの設定 // パラメータの最大検索件数がnullでない場合、最大検索件数の設定 if (!inMsg.isNull(ECH0331B010CBSMsg.MAX_SEARCH_NUM)) { iMaxSearchNum = Integer.parseInt(inMsg.getString(ECH0331B010CBSMsg.MAX_SEARCH_NUM)); } // パラメータの表示件数がnullでない場合、表示件数の設定 if (!inMsg.isNull(ECH0331B010CBSMsg.DISPLAY_NUM)) { iDisplayNum = Integer.parseInt(inMsg.getString(ECH0331B010CBSMsg.DISPLAY_NUM)); } // パラメータの表示ページ番号がnullでない場合、表示ページ番号の設定 if (!inMsg.isNull(ECH0331B010CBSMsg.DISPLAY_PAGE_NUM)) { iDisplayPageNum = Integer.parseInt(inMsg.getString(ECH0331B010CBSMsg.DISPLAY_PAGE_NUM)); } // 総検索件数の設定 iTotalSearchNum = Integer.parseInt(inMsg.getString(ECH0331B010CBSMsg.TOTAL_SEARCH_NUM)); // 総ページ数の設定 // 表示件数が0の場合 if (0 == iDisplayNum) { iTotalPageNum = 1; } // 検索件数/表示件数の余りがない場合 else if (0 == iTotalSearchNum % iDisplayNum) { iTotalPageNum = iTotalSearchNum / iDisplayNum; } else { iTotalPageNum = iTotalSearchNum / iDisplayNum + 1; } // 総検索数と総ページ数の設定 inMsg.set(ECH0331B010CBSMsg.TOTAL_SEARCH_NUM, String.valueOf(iTotalSearchNum)); inMsg.set(ECH0331B010CBSMsg.TOTAL_PAGE_NUM, String.valueOf(iTotalPageNum)); // 検索エラーの設定 // 総検索件数が0の場合 if(0 == iTotalSearchNum) { inMsg.set(ECH0331B010CBSMsg.SEARCH_ERR_FLG , "1"); return; } // 総検索件数が最大検索件数より大きい場合(最大検索件数超え) else if(0 != iMaxSearchNum && iTotalSearchNum > iMaxSearchNum) { inMsg.set(ECH0331B010CBSMsg.SEARCH_ERR_FLG , "2"); return; } // 表示ページに該当するデータがない場合 else if(iTotalPageNum < iDisplayPageNum) { inMsg.set(ECH0331B010CBSMsg.SEARCH_ERR_FLG , "3"); return; } // SQL文_基本部1 sql_Buff = new StringBuffer(); // select句 condition = getCondition2(inMsg, inContext, replaceListCnt2, schdlTeigiYM); sql_Buff.append(condition); if (SELEC_TYPE_SEIKY_YM_LATEST.equals(selecType) || SELEC_TYPE_SEIKY_YM_.equals(selecType)) { // from句以降 condition = getCondition1(inMsg, inContext, replaceListCnt2, selecType, schdlTeigiYM, false); sql_Buff.append(condition); } else if (SELEC_TYPE_SEIKY_KEI_NO.equals(selecType)) { condition = getCondition1(inMsg, inContext, replaceListCnt2, SELEC_TYPE_SEIKY_YM_LATEST, schdlTeigiYM, false); sql_Buff.append(condition); sql_Buff.append(" UNION "); // select句 condition = getCondition2(inMsg, inContext, replaceListCnt2, schdlTeigiYM); sql_Buff.append(condition); // from句以降 condition = getCondition1(inMsg, inContext, replaceListCnt2, selecType, schdlTeigiYM, false); sql_Buff.append(condition); } // SQL文_個別部1 if ( "1".equals( inMsg.getObject(ECH0331B010CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" ORDER BY SEIKY_KEI_NO "); } if ( "2".equals( inMsg.getObject(ECH0331B010CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" ORDER BY HIKINUKI_SBT_CD,SEIKY_KEI_NO "); } if ( "3".equals( inMsg.getObject(ECH0331B010CBSMsg.FUNC_CODE).toString())) { sql_Buff.append(" ORDER BY HIKINUKI_SBT_CD DESC,SEIKY_KEI_NO "); } //prepareStatementにSQL文をセット pstmt2 = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceListCnt2.size(); idx++) { CAANJDBCUtil.setParam(pstmt2, idx + 1, replaceListCnt2.get(idx)); } // ResultSetの取得 rsltQuery2 = pstmt2.executeQuery(); // ECH0331B010CBSMsgの明細にセットする項目 String[] msgKeyList2 = { ECH0331B010CBSMsg1List.SKS_HIKINUKI_NO , ECH0331B010CBSMsg1List.SEIKY_KEI_NO , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD_ALI , ECH0331B010CBSMsg1List.SEIKY_YM , ECH0331B010CBSMsg1List.SEIKY_YM_PRC_SCHDL_TEIGI , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD_ALI , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD_ALI , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG_NM , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG_ALI , ECH0331B010CBSMsg1List.UPD_DTM , ECH0331B010CBSMsg1List.HIKINUKI_STAYMD , ECH0331B010CBSMsg1List.HIKINUKI_ENDYMD , ECH0331B010CBSMsg1List.ADD_DIV_NM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery2, msgKeyList2, "eo.ejb.cbs.cbsmsg.ECH0331B010CBSMsg1List", 0); // inMsg(CBSMsg)に取得した明細をセット inMsg.set("ECH0331B010CBSMsg1List", outMsg1); // ページング関連処理 CAANMsg[] outMsg2 = null; // 対象データの取得 if(null == inMsg.getString(ECH0331B010CBSMsg.SEARCH_ERR_FLG)) { // 取得範囲の設定 // 取得範囲先頭の設定 iArrayCopyStart = iDisplayNum * (iDisplayPageNum - 1); // 取得範囲末尾の設定 // 表示件数が指定されていない場合 if (0 == iDisplayNum) { // 取得件数に総検索件数を設定 iArrayCopyCount = iTotalSearchNum; } // 表示ページ数が最終ページかつ総検索件数/表示件数の余りがある場合 else if(iTotalPageNum == iDisplayPageNum && 0 < iTotalSearchNum % iDisplayNum) { // 取得件数に総検索数/表示件数の余りを設定 iArrayCopyCount = iTotalSearchNum % iDisplayNum; } // 表示ページ数が最終ページでないまたは総検索件数/表示件数の余りがない場合 else { // 取得件数に総検索数/表示件数を設定 iArrayCopyCount = iDisplayNum; } // ページング範囲のデータを取得 outMsg2 = new CAANMsg[iArrayCopyCount]; System.arraycopy(outMsg1, iArrayCopyStart, outMsg2, 0, iArrayCopyCount); // inMsg(CBSMsg)に取得した明細をセット inMsg.set("ECH0331B010CBSMsg1List", outMsg2); } } else { StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" CH0331.SKS_HIKINUKI_NO ") .append(" ,CH0331.SEIKY_KEI_NO ") .append(" ,CH0331.HIKINUKI_SBT_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00903' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_SBT_CD_NM ") .append(" ,NULL AS HIKINUKI_SBT_CD_ALI ") .append(" ,NULL AS SEIKY_YM ") .append(" ,(SELECT CH0501.SEIKY_YM ") .append(" FROM CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE CH0501.EVENT_CD = ? ") .append(" AND CH0501.EVENT_YMD = (SELECT MIN(CH0501_MIN.EVENT_YMD) ") .append(" FROM CH_M_PRC_SCHDL_TEIGI CH0501_MIN ") .append(" WHERE CH0501_MIN.EVENT_YMD >= ? ") .append(" AND CH0501_MIN.EVENT_CD = ? ") .append(" AND CH0501_MIN.MK_FLG = '0' ) ") .append(" AND CH0501.MK_FLG = '0'" ) .append(" ) AS SEIKY_YM_PRC_SCHDL_TEIGI ") .append(" ,CH0331.HIKINUKI_RSN_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00909' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_RSN_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_RSN_CD_NM ") .append(" ,NULL AS HIKINUKI_RSN_CD_ALI ") .append(" ,CH0331.HIKINUKI_SEND_CD ") .append(" ,NULL AS HIKINUKI_SEND_CD_NM ") .append(" ,NULL AS HIKINUKI_SEND_CD_ALI ") .append(" ,NULL AS SEIKYUS_HIKINUKI_FLG ") .append(" ,NULL AS HIKINUKI_SEND_CD_NM ") .append(" ,NULL AS HIKINUKI_SEND_CD_ALI ") .append(" ,CH0331.UPD_DTM ") .append(" ,CH0331.HIKINUKI_STAYMD ") .append(" ,CH0331.HIKINUKI_ENDYMD ") .append(" ,NULL AS ADD_DIV_NM ") .append(" FROM ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" WHERE ") .append(" CH0331.SEIKY_KEI_NO = ? ") .append(" AND CH0331.HIKINUKI_SEND_CD <> ? ") .append(" AND CH0331.HIKINUKI_STAYMD = ") .append(" ( ") .append(" SELECT ") .append(" MAX(CH0331.HIKINUKI_STAYMD) ") .append(" FROM ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" WHERE ") .append(" CH0331.SEIKY_KEI_NO = ? ") .append(" AND CH0331.HIKINUKI_SEND_CD <> ? ") .append(" AND CH0331.MK_FLG = '0' ") .append(" ) ") .append(" AND ? BETWEEN CH0331.HIKINUKI_STAYMD AND CH0331.HIKINUKI_ENDYMD ") .append(" AND CH0331.MK_FLG = '0' "); replaceListCnt.add(JPCModelCommon.getOpeDate(inMsg, inContext, null).toString()); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_DATA_SND_YMD_3); replaceListCnt.add(JPCModelCommon.getOpeDate(inMsg, inContext, null).toString()); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_DATA_SND_YMD_3); replaceListCnt.add(JPCModelCommon.getOpeDate(inMsg, inContext, null).toString()); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.PULLOUT_SEND_CD_KAIJO); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.PULLOUT_SEND_CD_KAIJO); replaceListCnt.add(JPCModelCommon.getOpeDate(inMsg, inContext, null).toString()); //prepareStatementにSQL文をセット pstmt1 = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 for (int idx = 0; idx < replaceListCnt.size(); idx++) { CAANJDBCUtil.setParam(pstmt1, idx + 1, replaceListCnt.get(idx)); } // ResultSetの取得 rsltQuery1 = pstmt1.executeQuery(); // ECH0331B010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { ECH0331B010CBSMsg1List.SKS_HIKINUKI_NO , ECH0331B010CBSMsg1List.SEIKY_KEI_NO , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_SBT_CD_ALI , ECH0331B010CBSMsg1List.SEIKY_YM , ECH0331B010CBSMsg1List.SEIKY_YM_PRC_SCHDL_TEIGI , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_RSN_CD_ALI , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD_NM , ECH0331B010CBSMsg1List.HIKINUKI_SEND_CD_ALI , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG_NM , ECH0331B010CBSMsg1List.SEIKYUS_HIKINUKI_FLG_ALI , ECH0331B010CBSMsg1List.UPD_DTM , ECH0331B010CBSMsg1List.HIKINUKI_STAYMD , ECH0331B010CBSMsg1List.HIKINUKI_ENDYMD , ECH0331B010CBSMsg1List.ADD_DIV_NM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery1, msgKeyList1, "eo.ejb.cbs.cbsmsg.ECH0331B010CBSMsg1List", 0); // inMsg(CBSMsg)に取得した明細をセット inMsg.set("ECH0331B010CBSMsg1List", outMsg1); } } catch(SQLException e) { inMsg.set(ECH0331B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { // 資源の解放 try{ if(rsltQuery1 != null){ rsltQuery1.close(); } if(rsltQuery2 != null){ rsltQuery2.close(); } if(pstmt1 != null){ pstmt1.close(); } if(pstmt2 != null){ pstmt2.close(); } if(con1 != null){ closeConnection(con1); } }catch(SQLException e){ inMsg.set(ECH0331B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** * from句以降の生成
* @param inMsg 入力値の格納されたCBSMsg
* @param inContext ディスパッチコンテキスト
* @param replaceListCnt パラメータ設定リスト
* @param selecType 抽出タイプ
* @param schdlTeigiYM スケジュール定義請求年月
* @param schdlTeigiYMD スケジュール定義イベント年月日
* @param count カウント取得フラグ
* @return StringBuilder */ private StringBuilder getCondition1(CAANMsg inMsg, AgentDispatchContext inContext, List replaceListCnt, String selecType, String schdlTeigiYM, Boolean count) { StringBuilder sql_Buff = new StringBuilder(); String ym = "" ; if (SELEC_TYPE_SEIKY_YM_LATEST.equals(selecType) || SELEC_TYPE_SEIKY_KEI_NO.equals(selecType)) { ym = schdlTeigiYM; } else if (SELEC_TYPE_SEIKY_YM_.equals(selecType)) { ym = inMsg.getString(ECH0331B010CBSMsg.KEY_SEIKY_YM); } if (SELEC_TYPE_SEIKY_YM_LATEST.equals(selecType) || SELEC_TYPE_SEIKY_YM_.equals(selecType)) { sql_Buff.append(" FROM ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0051.SEIKY_YM = ? ") .append(" AND CH0051.SEIKY_SBT_CD = ? ") .append(" AND CH0051.MK_FLG = '0' "); replaceListCnt.add(ym); replaceListCnt.add(JACStrConst.SIKYSHBTS_TEIGK_SEIKY); if (SELEC_TYPE_SEIKY_YM_.equals(selecType)) { sql_Buff.append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? "); replaceListCnt.add(JACStrConst.SEIKYUS_PULLOUT_FLG); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)) { sql_Buff.append(" AND CH0051.SEIKY_KEI_NO = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); } sql_Buff.append(" WHERE ") .append(" CH0331.HIKINUKI_STAYMD < ") .append(" (SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM = CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.HIKINUKI_ENDYMD > ") .append(" (SELECT ") .append(" MAX(CH0501.EVENT_YMD) ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM < CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.MK_FLG = '0' "); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_SBT_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_RSN_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)) { if (SELEC_TYPE_SEIKY_YM_LATEST.equals(selecType)) { sql_Buff.append(" AND CH0331.HIKINUKI_SEND_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); } else if (SELEC_TYPE_SEIKY_YM_.equals(selecType)) { sql_Buff.append(" AND ( ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? ") .append(" ) ") .append(" OR ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0331.HIKINUKI_SEND_CD = ? ") .append(" ) ") .append(" ) "); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_KJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_ICJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); } } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)) { sql_Buff.append(" AND CH0331.SEIKY_KEI_NO = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); } } if (SELEC_TYPE_SEIKY_KEI_NO.equals(selecType) && count) { sql_Buff.append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" CH0331.SKS_HIKINUKI_NO ") .append(" ,CH0331.SEIKY_KEI_NO ") .append(" ,CH0331.HIKINUKI_SBT_CD ") .append(" ,CH0051.SEIKY_YM ") .append(" ,CH0331.HIKINUKI_RSN_CD ") .append(" ,CH0331.HIKINUKI_SEND_CD ") .append(" ,CH0331.UPD_DTM ") .append(" ,CH0331.HIKINUKI_STAYMD ") .append(" ,CH0331.HIKINUKI_ENDYMD ") .append(" FROM ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0051.SEIKY_YM = ? ") .append(" AND CH0051.SEIKY_SBT_CD = ? ") .append(" AND CH0051.MK_FLG = '0' "); replaceListCnt.add(ym); replaceListCnt.add(JACStrConst.SIKYSHBTS_TEIGK_SEIKY); if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)) { sql_Buff.append(" AND CH0051.SEIKY_KEI_NO = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); } sql_Buff.append(" WHERE ") .append(" CH0331.HIKINUKI_STAYMD < ") .append(" (SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM = CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.HIKINUKI_ENDYMD > ") .append(" (SELECT ") .append(" MAX(CH0501.EVENT_YMD) ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM < CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.MK_FLG = '0' "); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_SBT_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_RSN_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_SEND_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)) { sql_Buff.append(" AND CH0331.SEIKY_KEI_NO = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); } sql_Buff.append(" UNION ") .append(" SELECT ") .append(" CH0331.SKS_HIKINUKI_NO ") .append(" ,CH0331.SEIKY_KEI_NO ") .append(" ,CH0331.HIKINUKI_SBT_CD ") .append(" ,CH0051.SEIKY_YM ") .append(" ,CH0331.HIKINUKI_RSN_CD ") .append(" ,CH0331.HIKINUKI_SEND_CD ") .append(" ,CH0331.UPD_DTM ") .append(" ,CH0331.HIKINUKI_STAYMD ") .append(" ,CH0331.HIKINUKI_ENDYMD ") .append(" FROM ") .append(" CH_T_SEIKY CH0051 ") .append(" INNER JOIN ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" ON CH0051.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0331.SEIKY_KEI_NO = ? ") .append(" AND CH0331.MK_FLG = '0' ") .append(" AND CH0331.HIKINUKI_STAYMD < ") .append(" (SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM = CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.HIKINUKI_ENDYMD > ") .append(" (SELECT ") .append(" MAX(CH0501.EVENT_YMD) ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM < CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_SBT_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_RSN_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)) { sql_Buff.append(" AND ( ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? ") .append(" ) ") .append(" OR ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0331.HIKINUKI_SEND_CD = ? ") .append(" ) ") .append(" ) "); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_KJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_ICJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); } sql_Buff.append(" WHERE ") .append(" CH0051.SEIKY_KEI_NO = ? ") .append(" AND CH0051.SEIKY_SBT_CD = ? ") .append(" AND CH0051.SEIKY_YM != ? ") .append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? ") .append(" AND CH0051.MK_FLG = '0' ") .append(" ) "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.SIKYSHBTS_TEIGK_SEIKY); replaceListCnt.add(schdlTeigiYM); replaceListCnt.add(JACStrConst.SEIKYUS_PULLOUT_FLG); } else if (SELEC_TYPE_SEIKY_KEI_NO.equals(selecType) && !count) { sql_Buff.append(" FROM ") .append(" CH_T_SEIKY CH0051 ") .append(" INNER JOIN ") .append(" CH_T_SKS_HIKINUKI CH0331 ") .append(" ON CH0051.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0331.SEIKY_KEI_NO = ? ") .append(" AND CH0331.MK_FLG = '0' ") .append(" AND CH0331.HIKINUKI_STAYMD < ") .append(" (SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM = CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') ") .append(" AND CH0331.HIKINUKI_ENDYMD > ") .append(" (SELECT ") .append(" MAX(CH0501.EVENT_YMD) ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.SEIKY_YM < CH0051.SEIKY_YM ") .append(" AND CH0501.MK_FLG = '0') "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); replaceListCnt.add(JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_SBT_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SBT_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)) { sql_Buff.append(" AND CH0331.HIKINUKI_RSN_CD = ? "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_RSN_CD)); } if (!inMsg.isNull(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)) { sql_Buff.append(" AND ( ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? ") .append(" ) ") .append(" OR ") .append(" ( ") .append(" CH0331.HIKINUKI_SBT_CD = ? ") .append(" AND CH0331.HIKINUKI_SEND_CD = ? ") .append(" ) ") .append(" ) "); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_KJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); replaceListCnt.add(JACStrConst.HKNK_SBT_CD_ICJ_HKNK); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_HIKINUKI_SEND_CD)); } sql_Buff.append(" WHERE ") .append(" CH0051.SEIKY_KEI_NO = ? ") .append(" AND CH0051.SEIKY_SBT_CD = ? ") .append(" AND CH0051.SEIKY_YM != ? ") .append(" AND CH0051.SEIKYUS_HIKINUKI_FLG = ? ") .append(" AND CH0051.MK_FLG = '0' "); replaceListCnt.add(inMsg.getObject(ECH0331B010CBSMsg.KEY_SEIKY_KEI_NO)); replaceListCnt.add(JACStrConst.SIKYSHBTS_TEIGK_SEIKY); replaceListCnt.add(schdlTeigiYM); replaceListCnt.add(JACStrConst.SEIKYUS_PULLOUT_FLG); } return sql_Buff; } /** * Select句の生成
* @param inMsg 入力値の格納されたCBSMsg
* @param inContext ディスパッチコンテキスト
* @param replaceListCnt パラメータ設定リスト
* @return StringBuilder */ private StringBuilder getCondition2(CAANMsg inMsg, AgentDispatchContext inContext, List replaceListCnt, String schdlTeigiYM) { StringBuilder sql_Buff = new StringBuilder(); String ymd = JPCModelCommon.getOpeDate(inMsg, inContext, null).toString(); sql_Buff.append(" SELECT ") .append(" CH0331.SKS_HIKINUKI_NO ") .append(" ,CH0331.SEIKY_KEI_NO ") .append(" ,CH0331.HIKINUKI_SBT_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00903' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_SBT_CD_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00903' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_SBT_CD_ALI ") .append(" ,CH0051.SEIKY_YM ") .append(" , ? AS SEIKY_YM_PRC_SCHDL_TEIGI ") .append(" ,CH0331.HIKINUKI_RSN_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00909' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_RSN_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_RSN_CD_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00909' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_RSN_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_RSN_CD_ALI ") .append(" ,CH0331.HIKINUKI_SEND_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00910' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_SEND_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_SEND_CD_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00910' ") .append(" AND ZM0171.CD_DIV = CH0331.HIKINUKI_SEND_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS HIKINUKI_SEND_CD_ALI ") .append(" ,CH0051.SEIKYUS_HIKINUKI_FLG ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00910' ") .append(" AND ZM0171.CD_DIV = CH0051.SEIKYUS_HIKINUKI_FLG ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKYUS_HIKINUKI_FLG_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00910' ") .append(" AND ZM0171.CD_DIV = CH0051.SEIKYUS_HIKINUKI_FLG ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKYUS_HIKINUKI_FLG_ALI ") .append(" ,CH0331.UPD_DTM ") .append(" ,CH0331.HIKINUKI_STAYMD ") .append(" ,CH0331.HIKINUKI_ENDYMD ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD01441' ") .append(" AND ZM0171.CD_DIV = ") .append(" (CASE WHEN ") .append(" (SELECT CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ") .append(" FROM CH_T_PRC_TAIOKRK CH0551 ") .append(" WHERE CH0551.TAIO_DTM = ") .append(" (SELECT MAX(CH0551_GENE.TAIO_DTM) ") .append(" FROM CH_T_PRC_TAIOKRK CH0551_GENE ") .append(" WHERE CH0551_GENE.PRC_TAIO_KIROK_SBT_CD = '102' ") .append(" AND CH0551_GENE.SEIKY_KEI_NO = CH0551.SEIKY_KEI_NO ") .append(" AND CH0551_GENE.KIROK_TG_YM = CH0551.KIROK_TG_YM ") .append(" AND CH0551_GENE.MK_FLG = '0' ") .append(" ) ") .append(" AND CH0551.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0551.KIROK_TG_YM = CH0051.SEIKY_YM ") .append(" ) IN('1901','1902') THEN '1' ") .append(" WHEN ") .append(" (SELECT CH0551.PRC_TAIO_KIROK_SBT_DTL_CD ") .append(" FROM CH_T_PRC_TAIOKRK CH0551 ") .append(" WHERE CH0551.TAIO_DTM = ") .append(" (SELECT MAX(CH0551_GENE.TAIO_DTM) ") .append(" FROM CH_T_PRC_TAIOKRK CH0551_GENE ") .append(" WHERE CH0551_GENE.PRC_TAIO_KIROK_SBT_CD = '102' ") .append(" AND CH0551_GENE.SEIKY_KEI_NO = CH0551.SEIKY_KEI_NO ") .append(" AND CH0551_GENE.KIROK_TG_YM = CH0551.KIROK_TG_YM ") .append(" AND CH0551_GENE.MK_FLG = '0' ") .append(" ) ") .append(" AND CH0551.SEIKY_KEI_NO = CH0331.SEIKY_KEI_NO ") .append(" AND CH0551.KIROK_TG_YM = CH0051.SEIKY_YM ") .append(" ) IN('1903','1904') THEN '2' ") .append(" ELSE '9' END ") .append(" ) ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ADD_DIV_NM "); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(schdlTeigiYM); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(ymd); replaceListCnt.add(ymd); return sql_Buff; } /** * 運用日を基準とし、請求書引抜解除日に該当する最新の請求年月を取得
* @param inMsg 入力値の格納されたCBSMsg
* @param inContext ディスパッチコンテキスト
* @return void */ private String getSchdlTeigiYM(CAANMsg inMsg, AgentDispatchContext inContext) { Connection con = null; PreparedStatement pstmt = null; ResultSet rsltQuery = null; String seikyYm = ""; try { con = JSYejbConnection.getConnection(CH0501ETMsg.getTableName()); StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT ") .append(" CH0501.SEIKY_YM ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = ? ") .append(" AND CH0501.EVENT_YMD = ") .append(" (SELECT ") .append(" MIN(EVENT_YMD) ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" EVENT_YMD >= ? ") .append(" AND EVENT_CD = ? ") .append(" AND MK_FLG = '0' ) ") .append(" AND CH0501.MK_FLG = '0' "); pstmt = con.prepareStatement(sql_Buff.toString()); CAANJDBCUtil.setParam(pstmt, 1, JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); CAANJDBCUtil.setParam(pstmt, 2, JPCModelCommon.getOpeDate(inMsg, inContext, null)); CAANJDBCUtil.setParam(pstmt, 3, JACStrConst.EVENT_CD_SEIKYUS_HIKINUKI_KAIJO_YMD); rsltQuery = pstmt.executeQuery(); if (rsltQuery.next()) { seikyYm = rsltQuery.getString("SEIKY_YM"); } } catch (SQLException e) { inMsg.set(CH0501CBMMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } finally { try { if (rsltQuery != null) { rsltQuery.close(); } if (pstmt != null) { pstmt.close(); } if (con != null) { CAANConnectionMgr.getInstance().close(con); } } catch(SQLException e) { inMsg.set(CH0501ETMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } return seikyYm; } }