/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名 ：eo顧客基幹システム * モジュール名 ：JSYejbECH0011B010TPDA * ソースファイル名：JSYejbECH0011B010TPDA.java * 作成者 ：EK908604 * 日付 ：2012年12月07日 *＜機能概要＞ * 債権一覧照会テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン 修正日 修正者 修正内容 * ｖ1.00.00 * ｖ5.00.00 2013/06/08 FJ) 林 【IT2-2013-0000506】機能コード7：充当データが存在しない場合抽出対象外とする。 * ｖ5.00.01 2013/07/26 FJ) 文元【LT-2013-0000589】 取得項目に前受金充当額を追加 * ｖ5.00.02 2013/07/28 FJ) 林 【LT-2013-0000600】 機能コード2：債権額の取得内容変更 * ・変更前債権額(00900910630)が存在すれば 変更前債権額(00900910630) * ・変更前債権額(00900910630)が存在しなければ 債権額(00900910601) * ｖ5.00.03 2013/08/24 FJ) 小野【OM-2013-0000460】 性能改善 * ｖ5.00.04 2013/08/26 FJ) 早崎【IT1-2013-0001612】取得項目「債権請求額」「債権うち消費税額」追加 * ｖ5.00.05 2013/10/22 FJ) 北端【OM-2013-0002945】「債権一覧情報」の検索条件を修正 * ｖ5.00.06 2013/11/13 FJ) 田内【OM-2013-0004176】 機能コード3：リアル収納のレコードを抽出対象外とする。 * ｖ6.00.00 2013/12/26 FJ) 垣内【OM-2013-0003035】 性能改善 * ｖ8.00.00 2014/01/31 FJ) 江森【ANK-1589-00-00】未収金額、充当額不正対応 * ｖ8.00.01 2014/02/24 FJ) 小野【ANK-1589-00-00】消費税に伴う料金施策により、債権額うち消費税額をサマリして取得するように変更。 * ｖ8.00.02 2014/03/24 FJ) 小野【OM-2014-0001004】性能改善により、出力項目から最新請求支払期限年月日、合算フラグを削除。 * ｖ9.00.00 2014/04/27 FJ) 江森【OM-2014-0001637】貸倒後入金額をサマリして取得するように修正。 * ｖ9.00.01 2014/06/09 FJ) 小野【OM-2014-0001004（再）】性能改善のため、カウント取得のSQL呼び出しを削除する。 * ｖ9.00.02 2014/06/02 FJ) 小野【OM-2014-0005201】入金登録、入金取消、請求書再発行画面での債権一覧ソート順を修正。 * ｖ10.00.00 2014/08/20 FJ) 鈴木【OM-2014-0002708】入金取消した際の取消された充当の考慮がされておらず、取消された充当から入金を取得し、領収日を設定しているのを修正。 * v52.00.00 2021/01/08 FJ)三原 【IT2-2021-0000002】請求債権関連スキーマが登録されない ********************************************************************************/ package eo.ejb.cbs.sqlf; 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 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.common.constant.JACStrConst; import eo.common.constant.JPCModelConstant; import eo.common.util.JCRUtilCommon; import eo.ejb.cbm.entity.CH0011ETMsg; import eo.ejb.cbm.entity.ZM0321ETMsg; import eo.ejb.cbm.entity.ZM0321LE; import eo.ejb.cbs.cbsmsg.ECH0011B010CBSMsg; import eo.ejb.cbs.cbsmsg.ECH0011B010CBSMsg1List; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　ECH0011B010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbECH0011B010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbECH0011B010TPDA() { } /** * ECH0011B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void */ public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){ // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // パラメータ設定リスト List replaceListCnt = new ArrayList(); /******************** * SQL文の作成 ********************/ try{ String funcCode = inMsg.getString(ECH0011B010CBSMsg.FUNC_CODE); String seikyKeiNo = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_KEI_NO); String seikyNo = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_NO); // 業務機能識別コード取得 String workParam[] = getWorkParam(); // 運用日付 String opeDate = (String)JPCModelCommon.getOpeDate(inMsg, inContext, null); //コネクション取得 con1 = JSYejbConnection.getConnection(CH0011ETMsg.getTableName()); // -------------------------------------------------- // // データ取得 // -------------------------------------------------- // // SQL文_基本部1 StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append(" SELECT ") .append(" CH0011.SAIKEN_NO ") .append(" ,CH0011.SAIKEN_YM AS SAIKEN_FIX_YM ") .append(" ,CH0011.SAIKEN_STAT ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00158' ") .append(" AND ZM0171.CD_DIV = CH0011.SAIKEN_STAT ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SAIKEN_STAT_NM ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_ALI ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00158' ") .append(" AND ZM0171.CD_DIV = CH0011.SAIKEN_STAT ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SAIKEN_STAT_ALI ") .append(" ,CH0011.SEIKY_WAY_CD ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00166' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_WAY_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKY_WAY_CD_NM ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_ALI ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00166' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_WAY_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKY_WAY_CD_ALI ") .append(" ,CH0011.PAY_KIGEN_YMD ") .append(" ,CH0011.CHG_PAY_KIGEN_YMD ") .append(" ,CH0011.SEIKY_SBT_CD ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00164' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKY_SBT_CD_NM ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_ALI ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00164' ") .append(" AND ZM0171.CD_DIV = CH0011.SEIKY_SBT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS SEIKY_SBT_CD_ALI ") .append(" ,CH0011.RCNT_SAIKEN_JUTO_YMD AS SAIKEN_JUTO_YMD ") .append(" ,CH0011.SAIKEN_ADJ_YMD ") .append(" ,CH0011.SAIKEN_CHG_YMD ") .append(" ,CH0011.JUTO_FIN_YMD ") .append(" ,( ") .append(" SELECT ") .append(" CH0111.RECEIPT_YMD ") .append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" CH0201_1.SAIKEN_NO ") .append(" ,MAX(CH0111.RECEIPT_YMD) AS RECEIPT_YMD ") .append(" FROM ") .append(" CH_T_JUTO CH0201_1 ") .append(" INNER JOIN ") .append(" CH_T_NYUKIN CH0111 ") .append(" ON CH0201_1.NYUKIN_NO = CH0111.NYUKIN_NO ") .append(" AND CH0111.MK_FLG = '0'") .append(" WHERE ") .append(" CH0201_1.JUTO_NO IN ( ") .append(" SELECT ") .append(" CH0201_2.JUTO_NO") .append(" FROM ") .append(" CH_T_JUTO CH0201_2 ") .append(" INNER JOIN ") .append(" CH_T_SAIKEN CH0011 ") .append(" ON CH0011.SAIKEN_NO = CH0201_2.SAIKEN_NO "); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); if (JPCModelConstant.FUNC_CD_2.equals(funcCode) || JPCModelConstant.FUNC_CD_6.equals(funcCode)) { // 機能コード"2"または"6"の場合 sql_Buff.append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0041.SEIKY_NO ") .append(" ,CH0041.SAIKEN_NO ") .append(" FROM ") .append(" CH_T_SEIKY_SKN_KNRN CH0041 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_NO = CH0041.SEIKY_NO ") .append(" AND CH0051.SEIKY_NO = ? ") .append(" AND CH0051.MK_FLG = '0' ") .append(" WHERE ") .append(" CH0041.MK_FLG = '0' ") .append(" ) CH0041 ") .append(" ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO "); replaceListCnt.add(seikyNo); } else { sql_Buff.append(" AND CH0011.SEIKY_KEI_NO = ? "); replaceListCnt.add(seikyKeiNo); } sql_Buff.append(" WHERE ") .append(" CH0201_2.MK_FLG = '0' ") // OM-2014-0002708 2014.08.20 add start .append(" AND CH0201_2.CL_YMD IS NULL ") // OM-2014-0002708 2014.08.20 add end .append(" ) ") .append(" GROUP BY ") .append(" CH0201_1.SAIKEN_NO ") .append(" ) CH0111 ") .append(" WHERE ") .append(" CH0011.SAIKEN_NO = CH0111.SAIKEN_NO ") .append(" ) RECEIPT_YMD "); if (JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" ,CASE WHEN ") .append(" ( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) ") .append(" IS NOT NULL THEN ") // 変更前債権額: CHGE_SAIK(00900910630)が存在すれば、変更前債権額: CHGE_SAIK(00900910630) .append(" ( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) ") .append(" ELSE") // 変更前債権額: CHGE_SAIK(00900910630)が存在しなければ 債権額: SAIK_AMNT(00900910601) .append(" ( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) ") .append(" END SAIKEN_AMNT "); } else { sql_Buff.append(" ,( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) SAIKEN_AMNT "); // 債権額 : SAIK_AMNT(00900910601) } sql_Buff.append(" ,( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) MINOU_AMNT ") // 未収金額 : MISH_AMNT(00900950601) .append(" ,( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) JYTO_AMNT ") // 充当額＿合計 : JUTO_GAKU(00900960601) .append(" ,( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) MAEU_JYTO_AMNT ") // 前受金充当額 : SAIKE_MAU(00900930601) .append(" ,( ") .append(" SELECT ") .append(" CH0021.AMNT ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" AND ROWNUM = 1 ") .append(" ) SEIKY_AMNT ") // 請求額 : SEIKY_AMNT(00900940601) .append(" ,( ") .append(" SELECT ") .append(" SUM(CH0021.AMNT) ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" ) SAIKEN_STAX ") // うち消費税額 : SAIKE_STX(00900910201, 10900910201) .append(" ,( ") .append(" SELECT ") .append(" SUM(CH0021.AMNT) ") .append(" FROM ") .append(" CH_T_SAIKEN_UCWK CH0021 ") .append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0401.PRC_GRP_CD, ") .append(" CH0401.PCRS_CD, ") .append(" CH0401.PRC_SVC_CD, ") .append(" CH0401.PRC_KMK_CD ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" CH0401.SYS_CD = 'CH' ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = '1' ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.MK_FLG = '0' ") .append(" ) CH0401 ") .append(" ON CH0401.PRC_GRP_CD = CH0021.PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = CH0021.PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = CH0021.PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = CH0021.PRC_KMK_CD ") .append(" WHERE ") .append(" CH0021.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0021.SAIKEN_FIX_YM = CH0011.SAIKEN_YM ") .append(" AND CH0021.MK_FLG = '0' ") .append(" ) KSD_AF_NK_AMNT ") // 貸倒後入金額 : KSD_AF_NK(00900985601) .append(" ,CH0041.KHRI_RSLT_CD ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_NM ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00902' ") .append(" AND ZM0171.CD_DIV = CH0041.KHRI_RSLT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS KHRI_RSLT_CD_NM ") .append(" ,( ") .append(" SELECT ") .append(" ZM0171.CD_DIV_ALI ") .append(" FROM ") .append(" ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ") .append(" ZM0171.CD_SBT_CD = 'CD00902' ") .append(" AND ZM0171.CD_DIV = CH0041.KHRI_RSLT_CD ") .append(" AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS KHRI_RSLT_CD_ALI ") .append(" ,( ") .append(" SELECT ") .append(" SEIKY_RE_HAKKO_CNT ") .append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" CH0051_A.SAIKEN_NO ") .append(" ,COUNT(CH0051_A.SEIKY_NO) SEIKY_RE_HAKKO_CNT ") .append(" FROM ") .append(" ( ") .append(" SELECT ") .append(" CH0041_A.SEIKY_NO AS SEIKY_NO ") .append(" ,CH0041_A.SAIKEN_NO AS SAIKEN_NO ") .append(" FROM ") .append(" CH_T_SEIKY_SKN_KNRN CH0041_A ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051_A ") .append(" ON CH0051_A.SEIKY_NO = CH0041_A.SEIKY_NO ") .append(" WHERE ") .append(" CH0051_A.SEIKY_SBT_CD = '3' ") .append(" AND CH0051_A.MK_FLG = '0' ") .append(" AND CH0041_A.MK_FLG = '0' ") .append(" ) CH0051_A ") .append(" GROUP BY ") .append(" CH0051_A.SAIKEN_NO ") .append(" ) CH0051_A ") .append(" WHERE ") .append(" CH0051_A.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" ) SEIKY_RE_HAKKO_CNT ") .append(" ,CH0011.UPD_DTM AS SAIKEN_UPD_DTM_AF ") .append(" ,CH0041.SEIKY_NO ") .append(" FROM ") .append(" CH_T_SAIKEN CH0011 "); if (JPCModelConstant.FUNC_CD_2.equals(funcCode)) { replaceListCnt.add(workParam[3]); // 変更前債権額 : CHGE_SAIK(00900910630) replaceListCnt.add(opeDate); replaceListCnt.add(workParam[3]); // 変更前債権額 : CHGE_SAIK(00900910630) replaceListCnt.add(opeDate); replaceListCnt.add(workParam[0]); // 債権額 : SAIK_AMNT(00900910601) replaceListCnt.add(opeDate); } else { replaceListCnt.add(workParam[0]); // 債権額 : SAIK_AMNT(00900910601) replaceListCnt.add(opeDate); } replaceListCnt.add(workParam[1]); // 未収金額 : MISH_AMNT(00900950601) replaceListCnt.add(opeDate); replaceListCnt.add(workParam[2]); // 充当額＿合計 : JUTO_GAKU(00900960601) replaceListCnt.add(opeDate); replaceListCnt.add(JACStrConst.WKS_CH_SAIKE_MAU); // 前受金充当額 : SAIKE_MAU(00900930601) replaceListCnt.add(opeDate); replaceListCnt.add(JACStrConst.WKS_CH_SAIKE_AMT); // 請求額 : SAIKE_AMT(00900940601) replaceListCnt.add(opeDate); replaceListCnt.add(JACStrConst.WKS_CH_SAIKE_STX); // うち消費税額 : SAIKE_STX(00900910201, 10900910201) replaceListCnt.add(opeDate); replaceListCnt.add(JACStrConst.KSD_AF_NK); // 貸倒後入金額 : KSD_AF_NK(00900985601) replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); replaceListCnt.add(opeDate); // 条件1 追加 sql_Buff.append(getCondition1(inMsg, replaceListCnt)); // 条件2 追加 sql_Buff.append(getCondition2(inMsg, replaceListCnt)); // ソート条件 追加 sql_Buff.append(getSortCondition(inMsg)); //prepareStatementにSQL文をセット pstmt = 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(pstmt, idx+1, replaceListCnt.get(idx)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // ECH0011B010CBSMsgの明細にセットする項目 String[] msgKeyList2 = { ECH0011B010CBSMsg1List.SAIKEN_NO , ECH0011B010CBSMsg1List.SAIKEN_FIX_YM , ECH0011B010CBSMsg1List.SAIKEN_STAT , ECH0011B010CBSMsg1List.SAIKEN_STAT_NM , ECH0011B010CBSMsg1List.SAIKEN_STAT_ALI , ECH0011B010CBSMsg1List.SEIKY_WAY_CD , ECH0011B010CBSMsg1List.SEIKY_WAY_CD_NM , ECH0011B010CBSMsg1List.SEIKY_WAY_CD_ALI , ECH0011B010CBSMsg1List.PAY_KIGEN_YMD , ECH0011B010CBSMsg1List.CHG_PAY_KIGEN_YMD , ECH0011B010CBSMsg1List.SEIKY_SBT_CD , ECH0011B010CBSMsg1List.SEIKY_SBT_CD_NM , ECH0011B010CBSMsg1List.SEIKY_SBT_CD_ALI , ECH0011B010CBSMsg1List.SAIKEN_JUTO_YMD , ECH0011B010CBSMsg1List.SAIKEN_CYOSEI_YMD , ECH0011B010CBSMsg1List.SAIKEN_HENKO_YMD , ECH0011B010CBSMsg1List.JUTO_FIN_YMD , ECH0011B010CBSMsg1List.RECEIPT_YMD , ECH0011B010CBSMsg1List.SAIKEN_AMNT , ECH0011B010CBSMsg1List.MINOU_AMNT , ECH0011B010CBSMsg1List.JYTO_AMNT , ECH0011B010CBSMsg1List.MAEU_JYTO_AMNT , ECH0011B010CBSMsg1List.SEIKY_AMNT , ECH0011B010CBSMsg1List.SAIKEN_STAX , ECH0011B010CBSMsg1List.KSD_AF_NK_AMNT , ECH0011B010CBSMsg1List.KHRI_RSLT_CD , ECH0011B010CBSMsg1List.KHRI_RSLT_CD_NM , ECH0011B010CBSMsg1List.KHRI_RSLT_CD_ALI , ECH0011B010CBSMsg1List.SEIKY_RE_HAKKO_CNT , ECH0011B010CBSMsg1List.SAIKEN_UPD_DTM_AF , ECH0011B010CBSMsg1List.SEIKY_NO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList2,"eo.ejb.cbs.cbsmsg.ECH0011B010CBSMsg1List",0); // inMsg(CBSMsg)に取得した明細をセット inMsg.set("ECH0011B010CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(ECH0011B010CBSMsg.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(ECH0011B010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** * ECH0011B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @param replaceListCnt パラメータ設定リスト
* @return StringBuilder */ private StringBuilder getCondition1(CAANMsg inMsg, List replaceListCnt){ String funcCode = inMsg.getString(ECH0011B010CBSMsg.FUNC_CODE); String seikyKeiNo = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_KEI_NO); String seikyNo = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_NO); String seikyYm = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_YM); StringBuilder sql_Buff = new StringBuilder(); // 機能コード"1"または"5"または"7"または"8"または"9"の場合 if (JPCModelConstant.FUNC_CD_1.equals(funcCode) || JPCModelConstant.FUNC_CD_5.equals(funcCode) || JPCModelConstant.FUNC_CD_7.equals(funcCode) || JPCModelConstant.FUNC_CD_8.equals(funcCode) || JPCModelConstant.FUNC_CD_9.equals(funcCode)) { sql_Buff.append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0041.SEIKY_NO ") .append(" ,CH0041.SAIKEN_NO ") .append(" ,CH0051.KHRI_RSLT_CD ") .append(" ,CH0051.SEIKY_YM ") .append(" FROM ") .append(" CH_T_SEIKY_SKN_KNRN CH0041 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_NO = CH0041.SEIKY_NO ") .append(" WHERE 1=1 "); if (!JPCModelConstant.FUNC_CD_5.equals(funcCode) && !JPCModelConstant.FUNC_CD_7.equals(funcCode) && !JCRUtilCommon.isNull(seikyYm)) { sql_Buff.append(" AND CH0051.SEIKY_YM = ? "); replaceListCnt.add(seikyYm); } if ((JPCModelConstant.FUNC_CD_8.equals(funcCode) || JPCModelConstant.FUNC_CD_9.equals(funcCode)) && !JCRUtilCommon.isNull(seikyNo)) { sql_Buff.append(" AND CH0051.SEIKY_NO = ? "); replaceListCnt.add(seikyNo); } sql_Buff.append(" AND CH0051.SEIKY_KEI_NO = ? ") .append(" AND CH0051.MK_FLG = '0' ") .append(" AND CH0041.KKSHI_CNT = 0 ") .append(" AND CH0041.MK_FLG = '0' ") .append(" AND CH0051.SEIKY_SBT_CD <> '3' ") .append(" ) CH0041 ") .append(" ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0041.SEIKY_YM = CH0011.SAIKEN_YM "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_2.equals(funcCode) || JPCModelConstant.FUNC_CD_6.equals(funcCode)) { // 機能コード"2"または"6"の場合 sql_Buff.append(" INNER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0041.SEIKY_NO ") .append(" ,CH0041.SAIKEN_NO ") .append(" ,CH0051.KHRI_RSLT_CD ") .append(" ,CH0051.SEIKY_YM ") .append(" FROM ") .append(" CH_T_SEIKY_SKN_KNRN CH0041 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_NO = CH0041.SEIKY_NO ") .append(" AND CH0051.SEIKY_NO = ? ") .append(" AND CH0051.MK_FLG = '0'") .append(" WHERE") .append(" CH0051.MK_FLG = '0' ") .append(" AND CH0041.MK_FLG = '0'") .append(" ) CH0041 ") .append(" ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO "); replaceListCnt.add(seikyNo); } else if (JPCModelConstant.FUNC_CD_3.equals(funcCode) || JPCModelConstant.FUNC_CD_4.equals(funcCode)) { // 機能コード"3"または"4"の場合 sql_Buff.append(" LEFT OUTER JOIN ") .append(" ( ") .append(" SELECT ") .append(" CH0041.SEIKY_NO ") .append(" ,CH0041.SAIKEN_NO ") .append(" ,CH0051.KHRI_RSLT_CD ") .append(" ,CH0051.SEIKY_YM ") .append(" FROM ") .append(" CH_T_SEIKY_SKN_KNRN CH0041 ") .append(" INNER JOIN ") .append(" CH_T_SEIKY CH0051 ") .append(" ON CH0051.SEIKY_NO = CH0041.SEIKY_NO ") .append(" WHERE ") .append(" CH0051.SEIKY_KEI_NO = ? ") .append(" AND CH0051.MK_FLG = '0' ") .append(" AND CH0041.KKSHI_CNT = 0 ") .append(" AND CH0041.MK_FLG = '0' ") .append(" AND CH0051.SEIKY_SBT_CD <> '3' ") .append(" ) CH0041 ") .append(" ON CH0041.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0041.SEIKY_YM = CH0011.SAIKEN_YM "); replaceListCnt.add(seikyKeiNo); } return sql_Buff; } /** * ECH0011B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @param replaceListCnt パラメータ設定リスト
* @return StringBuilder */ private StringBuilder getCondition2(CAANMsg inMsg, List replaceListCnt){ String funcCode = inMsg.getString(ECH0011B010CBSMsg.FUNC_CODE); String seikyKeiNo = inMsg.getString(ECH0011B010CBSMsg.KEY_SEIKY_KEI_NO); StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append(" WHERE "); if (JPCModelConstant.FUNC_CD_1.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN ('020', '030', '040', '050', '060', '080', '090') ") .append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" CH0011.MK_FLG = '0' "); } else if (JPCModelConstant.FUNC_CD_3.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN ('020', '060') ") .append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_4.equals(funcCode)) { String printYsk = inMsg.getObject(ECH0011B010CBSMsg.KEY_PRINT_YOSHIKI).toString(); sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? "); // ＫＥＹ＿印刷様式が窓口の場合 // IT2-2021-0000002 MOD START //if (JACStrConst.PRINT_YSK_CD_MADO.equals(printYsk)) { if (JACStrConst.PRINT_YSK_CD_MADO.equals(printYsk) || JACStrConst.PRINT_YSK_CD_MADO_CUST.equals(printYsk)) { // IT2-2021-0000002 MOD END sql_Buff.append(" AND CH0011.SAIKEN_STAT IN ('020', '030', '040', '060') "); // ＫＥＹ＿印刷様式が口座振替の場合 } else if (JACStrConst.PRINT_YSK_CD_KOZA.equals(printYsk)) { sql_Buff.append(" AND CH0011.SEIKY_WAY_CD IN ('2', '3') "); // ＫＥＹ＿印刷様式がクレジットの場合 } else if (JACStrConst.PRINT_YSK_CD_CRDT.equals(printYsk)) { sql_Buff.append(" AND CH0011.SEIKY_WAY_CD = '4' "); // ＫＥＹ＿印刷様式が口振領収書のみの場合 } else if (JACStrConst.PRINT_YSK_CD_RCPT.equals(printYsk)) { sql_Buff.append(" AND CH0011.SAIKEN_STAT = '050' ") .append(" AND CH0011.SEIKY_WAY_CD IN ('2', '3') "); } sql_Buff.append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_5.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN ('020', '060', '080') ") .append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_6.equals(funcCode)) { sql_Buff.append(" CH0011.MK_FLG = '0' "); } else if (JPCModelConstant.FUNC_CD_7.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN (?, ?, ?, ?) ") .append(" AND CH0011.MK_FLG = '0' ") .append(" AND EXISTS ") .append(" ( ") .append(" SELECT 1 ") .append(" FROM ") .append(" CH_T_JUTO CH0201 ") .append(" WHERE ") .append(" CH0201.SAIKEN_NO = CH0011.SAIKEN_NO ") .append(" AND CH0201.CL_YMD IS NULL ") .append(" AND CH0201.MK_FLG = '0' ") .append(" ) "); replaceListCnt.add(seikyKeiNo); replaceListCnt.add(JACStrConst.SAIKEN_STAT_REAL_KAKNO); replaceListCnt.add(JACStrConst.SAIKEN_STAT_KARI_KAKNO); replaceListCnt.add(JACStrConst.SAIKEN_STAT_KAKNO_ZUMI); replaceListCnt.add(JACStrConst.SAIKEN_STAT_ICHIBU_KAKNO); } else if (JPCModelConstant.FUNC_CD_8.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN ('000', '010') ") .append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } else if (JPCModelConstant.FUNC_CD_9.equals(funcCode)) { sql_Buff.append(" CH0011.SEIKY_KEI_NO = ? ") .append(" AND CH0011.SAIKEN_STAT IN ('020', '030', '040', '050', '060', '080', '090') ") .append(" AND CH0011.MK_FLG = '0' "); replaceListCnt.add(seikyKeiNo); } return sql_Buff; } /** * ECH0011B010_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return StringBuilder */ private StringBuilder getSortCondition(CAANMsg inMsg){ String funcCode = inMsg.getString(ECH0011B010CBSMsg.FUNC_CODE); StringBuilder sql_Buff = new StringBuilder(); sql_Buff.append(" ORDER BY "); if (JPCModelConstant.FUNC_CD_1.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC "); } else if (JPCModelConstant.FUNC_CD_2.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC "); } else if (JPCModelConstant.FUNC_CD_3.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC ") .append(" ,CH0011.SAIKEN_NO ASC "); } else if (JPCModelConstant.FUNC_CD_4.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC ") .append(" ,CH0011.SAIKEN_NO ASC "); } else if (JPCModelConstant.FUNC_CD_5.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC "); } else if (JPCModelConstant.FUNC_CD_6.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC "); } else if (JPCModelConstant.FUNC_CD_7.equals(funcCode)) { sql_Buff.append(" CH0011.SAIKEN_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC ") .append(" ,CH0011.SAIKEN_NO ASC "); } else if (JPCModelConstant.FUNC_CD_8.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC, ") .append(" CH0011.SEIKY_SBT_CD ASC "); } else if (JPCModelConstant.FUNC_CD_9.equals(funcCode)) { sql_Buff.append(" SAIKEN_FIX_YM DESC ") .append(" ,CH0011.SEIKY_SBT_CD ASC "); } return sql_Buff; } /** * ECH0011B010_SQLF
* @return String[] */ private String[] getWorkParam(){ String[] workParam = new String[4]; // 業務パラメータ管理より業務機能識別コード：料金項目コード（債権額）を取得する CAANMsg msgZM0321 = new CAANMsg(ZM0321ETMsg.class.getName()); ZM0321LE le = new ZM0321LE(); msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_SAIKEN_AMNT); // SAIK_AMNT(00900910601) msgZM0321.set(ZM0321ETMsg.MK_FLG, JACStrConst.MK_FLG_YK); CAANMsg retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[0] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（未収金額））取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPARA_CH_WKS_MISYU_KINGAKU); // MISH_AMNT(00900950601) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[1] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（充当額＿合計）を取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_JUTO_GOKEI); // JUTO_GAKU(00900960601) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[2] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); // 業務パラメータ管理より業務機能識別コード：料金項目コード（変更前債権額）を取得する msgZM0321.set(ZM0321ETMsg.WORK_PARAM_ID, JACStrConst.WKPRA_CH_WKS_CHGE_SAIKEN); // CHGE_SAIK(00900910630) retZM0321 = le.findByPrimaryKey(msgZM0321); workParam[3] = retZM0321.getString(ZM0321ETMsg.WORK_PARAM_SETTE_VALUE); return workParam; } }