/******************************************************************************* * All Rights reserved,Copyright (c) K-Opticom ******************************************************************************** *＜プログラム内容＞ * システム名：eo顧客基幹システム * モジュール名：JSYejbEAC0171A010TPDA * ソースファイル名：JSYejbEAC0171A010TPDA.java * 作成者：EKek0077 * 日付：2011年10月17日 *＜機能概要＞ * SQLFacility * テンプレートDBアクセス部品です。 *＜修正履歴＞ * バージョン修正日修正者修正内容 * ｖ1.00.00 * ｖ5.00.00 2013/07/03 FJ)垣内【LT-2013-0000203】料金サービス管理を世代を見ないよう修正 * ｖ5.00.01 2013/07/26 FJ)河井【TG1-2013-0000662】マスタ参照基準日対応 * ｖ8.00.00 2013/12/25 FJ)江森【ANK-1584-00-00】一時金スキーマに項目「サービス詳細契約番号」追加対応 * ｖ9.00.00 2014/04/16 FJ)小野【ANK-2054-00-00】eoスマートリンク改修(端末補償)により、出力項目追加。 * ｖ16.00.00 2015/06/05 FJ)西面【ANK-2480-00-00】新電力対応 * ********************************************************************************/ package eo.ejb.cbs.sqlf; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.fujitsu.futurity.model.base.CAANJDBCUtil; import com.fujitsu.futurity.model.base.CAANMsg; import com.fujitsu.futurity.model.base.CAANRuntimeException; import com.fujitsu.futurity.model.base.CAANSQLFacility; import com.fujitsu.futurity.model.ejb.common.JSYejbConnection; import com.fujitsu.futurity.model.ejb.common.JSYejbLog; import com.fujitsu.futurity.model.ejb.common.StatusCodes; import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext; import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler; import eo.common.constant.JACStrConst; import eo.ejb.cbm.entity.AC0171ETMsg; import eo.ejb.cbs.cbsmsg.EAC0171A010CBSMsg; import eo.ejb.cbs.cbsmsg.EAC0171A010CBSMsg1List; import eo.ejb.common.JPCModelCommon; /** * * テンプレートDBアクセス部品　EAC0171A010_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/ public class JSYejbEAC0171A010TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{ /** * コンストラクタ */ public JSYejbEAC0171A010TPDA() { } /** * EAC0171A010_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(AC0171ETMsg.getTableName()); // SQL文_基本部1 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT AC0171.ICJKN_ADD_SEQ ") .append(" ,AC0171.ICJKN_UPD_SEQ ") .append(" ,AC0171.KICJKN_NO ") .append(" ,AC0171.SEIKY_KEI_NO ") .append(" ,AC0171.SEIKY_YM ") .append(" ,AC0171.TONYU_DIV ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00972' ") .append(" AND ZM0171.CD_DIV = AC0171.TONYU_DIV ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS TONYU_DIV_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00972' ") .append(" AND ZM0171.CD_DIV = AC0171.TONYU_DIV ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS TONYU_DIV_ALI ") .append(" ,AC0171.SVC_KEI_NO ") .append(" ,AC0171.PRC_GRP_CD ") .append(" ,(SELECT KK0781.PRC_GRP_NM ") .append(" FROM KK_M_PRC_GRP KK0781 ") .append(" WHERE KK0781.PRC_GRP_CD = AC0171.PRC_GRP_CD ") .append(" AND KK0781.PRC_GRP_TSTAYMD || KK0781.GENE_ADD_DTM = ") .append(" ( SELECT MAX(KK0781_GENE.PRC_GRP_TSTAYMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ") .append(" FROM KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.PRC_GRP_TSTAYMD <= ? ") .append(" AND KK0781_GENE.MK_FLG = '0' ) ") .append(" ) AS PRC_GRP_NM ") .append(" ,AC0171.PCRS_CD ") .append(" ,(SELECT KK0791.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE KK0791.PCRS_CD = AC0171.PCRS_CD ") .append(" AND KK0791.PCRS_TSTAYMD || KK0791.GENE_ADD_DTM = ") .append(" ( SELECT MAX(KK0791_GENE.PCRS_TSTAYMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.PCRS_CD ") .append(" AND KK0791_GENE.PCRS_TSTAYMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ) ") .append(" ) AS PCRS_CD_NM ") .append(" ,AC0171.PRC_SVC_CD ") .append(" ,(SELECT AC0181.SCREEN_DSP_NM ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181 ") .append(" WHERE AC0181.SYS_CD = 'AC' ") .append(" AND AC0181.PCRS_CD = AC0171.PCRS_CD ") .append(" AND AC0181.PRC_SVC_CD = AC0171.PRC_SVC_CD ") .append(" AND AC0181.PRC_SVC_KANRI_TSTAYMD = ") .append(" ( SELECT MAX(AC0181_GENE.PRC_SVC_KANRI_TSTAYMD) AS AC0181_MAX ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181_GENE ") .append(" WHERE AC0181_GENE.SYS_CD = AC0181.SYS_CD ") .append(" AND AC0181_GENE.PCRS_CD = AC0181.PCRS_CD ") .append(" AND AC0181_GENE.PRC_SVC_CD = AC0181.PRC_SVC_CD ") .append(" AND AC0181_GENE.PRC_SVC_KANRI_TSTAYMD <= ? ") .append(" AND AC0181_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS PRC_SVC_CD_NM ") .append(" ,AC0171.PRC_KMK_CD ") .append(" ,(SELECT CH0391.PRC_KMK_NM ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391 ") .append(" WHERE CH0391.PRC_KMK_NM_SBT_CD = '1' ") .append(" AND CH0391.PRC_GRP_CD = AC0171.PRC_GRP_CD ") .append(" AND CH0391.PCRS_CD = AC0171.PCRS_CD ") .append(" AND CH0391.PRC_SVC_CD = AC0171.PRC_SVC_CD ") .append(" AND CH0391.PRC_KMK_CD = AC0171.PRC_KMK_CD ") .append(" AND CH0391.PRC_KMK_OPUT_NM_TSTAYMD = ") .append(" ( SELECT MAX(CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD) AS CH0391_MAX ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391_GENE ") .append(" WHERE CH0391_GENE.PRC_KMK_NM_SBT_CD = CH0391.PRC_KMK_NM_SBT_CD ") .append(" AND CH0391_GENE.PRC_GRP_CD = CH0391.PRC_GRP_CD ") .append(" AND CH0391_GENE.PCRS_CD = CH0391.PCRS_CD ") .append(" AND CH0391_GENE.PRC_SVC_CD = CH0391.PRC_SVC_CD ") .append(" AND CH0391_GENE.PRC_KMK_CD = CH0391.PRC_KMK_CD ") .append(" AND CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD <= ? ") .append(" AND CH0391_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS PRC_KMK_CD_NM ") .append(" ,AC0171.TRGT_PRC_GRP_CD ") .append(" ,(SELECT KK0781.PRC_GRP_NM ") .append(" FROM KK_M_PRC_GRP KK0781 ") .append(" WHERE KK0781.PRC_GRP_CD = AC0171.TRGT_PRC_GRP_CD ") .append(" AND KK0781.PRC_GRP_TSTAYMD || KK0781.GENE_ADD_DTM = ") .append(" ( SELECT MAX(KK0781_GENE.PRC_GRP_TSTAYMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX ") .append(" FROM KK_M_PRC_GRP KK0781_GENE ") .append(" WHERE KK0781_GENE.PRC_GRP_CD = KK0781.PRC_GRP_CD ") .append(" AND KK0781_GENE.PRC_GRP_TSTAYMD <= ? ") .append(" AND KK0781_GENE.MK_FLG = '0' ) ") .append(" ) AS TRGT_PRC_GRP_NM ") .append(" ,AC0171.TRGT_PCRS_CD ") .append(" ,(SELECT KK0791.PCRS_NM ") .append(" FROM KK_M_PCRS KK0791 ") .append(" WHERE KK0791.PCRS_CD = AC0171.TRGT_PCRS_CD ") .append(" AND KK0791.PCRS_TSTAYMD || KK0791.GENE_ADD_DTM = ") .append(" ( SELECT MAX(KK0791_GENE.PCRS_TSTAYMD || KK0791_GENE.GENE_ADD_DTM) AS KK0791_MAX ") .append(" FROM KK_M_PCRS KK0791_GENE ") .append(" WHERE KK0791_GENE.PCRS_CD = KK0791.PCRS_CD ") .append(" AND KK0791_GENE.PCRS_TSTAYMD <= ? ") .append(" AND KK0791_GENE.MK_FLG = '0' ) ") .append(" ) AS TRGT_PCRS_CD_NM ") .append(" ,AC0171.TRGT_PRC_SVC_CD ") .append(" ,(SELECT AC0181.SCREEN_DSP_NM ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181 ") .append(" WHERE AC0181.SYS_CD = 'AC' ") .append(" AND AC0181.PCRS_CD = AC0171.TRGT_PCRS_CD ") .append(" AND AC0181.PRC_SVC_CD = AC0171.TRGT_PRC_SVC_CD ") .append(" AND AC0181.PRC_SVC_KANRI_TSTAYMD = ") .append(" ( SELECT MAX(AC0181_GENE.PRC_SVC_KANRI_TSTAYMD) AS AC0181_MAX ") .append(" FROM AC_M_PRC_SVC_KANRI AC0181_GENE ") .append(" WHERE AC0181_GENE.SYS_CD = AC0181.SYS_CD ") .append(" AND AC0181_GENE.PCRS_CD = AC0181.PCRS_CD ") .append(" AND AC0181_GENE.PRC_SVC_CD = AC0181.PRC_SVC_CD ") .append(" AND AC0181_GENE.PRC_SVC_KANRI_TSTAYMD <= ? ") .append(" AND AC0181_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS TRGT_PRC_SVC_CD_NM ") .append(" ,(SELECT ") .append(" KK0711.WRIB_SVC_CD ") .append(" FROM ") .append(" KK_M_WRIB_SVC_TANKA KK0711 ") .append(" ,CH_M_PRC_KMK_CS_CHGE CH0401 ") .append(" WHERE ") .append(" KK0711.WRIB_SVC_TANKA_NO = AC0171.TRGT_PRC_SVC_CD ") .append(" AND CH0401.SYS_CD = ? ") .append(" AND CH0401.WORK_KINO_SKBT_CD = ? ") .append(" AND CH0401.CHSHT_CHG_CD = ? ") .append(" AND CH0401.PRC_GRP_CD = AC0171.TRGT_PRC_GRP_CD ") .append(" AND CH0401.PCRS_CD = AC0171.TRGT_PCRS_CD ") .append(" AND CH0401.PRC_SVC_CD = AC0171.TRGT_PRC_SVC_CD ") .append(" AND CH0401.PRC_KMK_CD = AC0171.TRGT_PRC_KMK_CD ") .append(" AND ? BETWEEN CH0401.PRC_KMK_CS_CHGE_TSTAYMD AND CH0401.PRC_KMK_CS_CHGE_TENDYMD ") .append(" AND CH0401.RSV_APLY_YMD || CH0401.GENE_ADD_DTM = ") .append(" ( SELECT ") .append(" MAX(CH0401_GENE.RSV_APLY_YMD || CH0401_GENE.GENE_ADD_DTM) AS CH0401_MAX ") .append(" FROM ") .append(" CH_M_PRC_KMK_CS_CHGE CH0401_GENE ") .append(" WHERE ") .append(" CH0401_GENE.SYS_CD = CH0401.SYS_CD ") .append(" AND CH0401_GENE.WORK_KINO_SKBT_CD = CH0401.WORK_KINO_SKBT_CD ") .append(" AND CH0401_GENE.CHSHT_CHG_CD = CH0401.CHSHT_CHG_CD ") .append(" AND CH0401_GENE.PRC_GRP_CD = CH0401.PRC_GRP_CD ") .append(" AND CH0401_GENE.PCRS_CD = CH0401.PCRS_CD ") .append(" AND CH0401_GENE.PRC_SVC_CD = CH0401.PRC_SVC_CD ") .append(" AND CH0401_GENE.PRC_KMK_CD = CH0401.PRC_KMK_CD ") .append(" AND CH0401_GENE.RSV_APLY_YMD <= ? ") .append(" AND CH0401_GENE.MK_FLG = '0') ) AS WARIB_SVC_CD ") .append(" ,AC0171.TRGT_PRC_KMK_CD ") .append(" ,(SELECT CH0391.PRC_KMK_NM ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391 ") .append(" WHERE CH0391.PRC_KMK_NM_SBT_CD = '1' ") .append(" AND CH0391.PRC_GRP_CD = AC0171.TRGT_PRC_GRP_CD ") .append(" AND CH0391.PCRS_CD = AC0171.TRGT_PCRS_CD ") .append(" AND CH0391.PRC_SVC_CD = AC0171.TRGT_PRC_SVC_CD ") .append(" AND CH0391.PRC_KMK_CD = AC0171.TRGT_PRC_KMK_CD ") .append(" AND CH0391.PRC_KMK_OPUT_NM_TSTAYMD = ") .append(" ( SELECT MAX(CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD) AS CH0391_MAX ") .append(" FROM CH_M_PRC_KMK_OPUT_NM CH0391_GENE ") .append(" WHERE CH0391_GENE.PRC_KMK_NM_SBT_CD = CH0391.PRC_KMK_NM_SBT_CD ") .append(" AND CH0391_GENE.PRC_GRP_CD = CH0391.PRC_GRP_CD ") .append(" AND CH0391_GENE.PCRS_CD = CH0391.PCRS_CD ") .append(" AND CH0391_GENE.PRC_SVC_CD = CH0391.PRC_SVC_CD ") .append(" AND CH0391_GENE.PRC_KMK_CD = CH0391.PRC_KMK_CD ") .append(" AND CH0391_GENE.PRC_KMK_OPUT_NM_TSTAYMD <= ? ") .append(" AND CH0391_GENE.MK_FLG = '0' ) ") .append(" AND ROWNUM = 1 ") .append(" ) AS TRGT_PRC_KMK_CD_NM ") .append(" ,AC0171.SEIKY_YM_SNST_STDARDYMD ") .append(" ,AC0171.ICJKN_PRC ") .append(" ,AC0171.ICJKN_SBT_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00974' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_SBT_CD ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_SBT_CD_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00974' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_SBT_CD ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_SBT_CD_ALI ") .append(" ,AC0171.ICJKN_TRKM_STAT ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00188' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_TRKM_STAT ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_TRKM_STAT_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00188' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_TRKM_STAT ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_TRKM_STAT_ALI ") .append(" ,AC0171.ICJKN_TRKM_RSLT_CD ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00973' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_TRKM_RSLT_CD ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_TRKM_RSLT_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00973' ") .append(" AND ZM0171.CD_DIV = AC0171.ICJKN_TRKM_RSLT_CD ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS ICJKN_TRKM_RSLT_ALI ") .append(" ,AC0171.TRKM_ZUMI_AMNT ") .append(" ,AC0171.TRKM_ZAN_AMNT ") .append(" ,AC0171.MANSION_ID ") .append(" ,AC0171.TK_HOSHIKI_KEI_NO ") .append(" ,AC0171.KANYU_KEI_PAY_HOSHIKI_CD ") .append(" ,AC0171.SVC_DTL_SKBT_NO ") .append(" ,( ") .append(" SELECT ") .append(" KK0341.KIKI_SEIZO_NO ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341 ") .append(" WHERE ") .append(" KK0341.KKTK_SVC_KEI_NO = AC0171.SVC_DTL_SKBT_NO ") .append(" AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = ( ") .append(" SELECT ") .append(" KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX ") .append(" FROM ") .append(" KK_T_KKTK_SVC_KEI KK0341_GENE ") .append(" WHERE ") .append(" KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO ") .append(" AND KK0341_GENE.RSV_APLY_YMD <= ") .append(" ( ") .append(" CASE WHEN AC0171.SEIKY_YM IS NOT NULL THEN ") .append(" ( ") .append(" SELECT ") .append(" CH0501.EVENT_YMD ") .append(" FROM ") .append(" CH_M_PRC_SCHDL_TEIGI CH0501 ") .append(" WHERE ") .append(" CH0501.EVENT_CD = '02' ") .append(" AND CH0501.SEIKY_YM = AC0171.SEIKY_YM ") .append(" ) ELSE ? END ") .append(" ) ") .append(" AND KK0341_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0341_GENE.MK_FLG = '0' ") .append(" GROUP BY ") .append(" KK0341_GENE.KKTK_SVC_KEI_NO ") .append(" ) ") .append(" AND KK0341.KKTK_SVC_CD = 'C021' ") .append(" ) KIKI_SEIZO_NO ") .append(" ,AC0171.ICJKN_ADD_DTM ") .append(" ,AC0171.ICJKN_ADD_USER_ID ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = AC0171.ICJKN_ADD_USER_ID ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS ICJKN_ADD_USER_ID_NM ") .append(" ,AC0171.ICJKN_UPD_DTM ") .append(" ,AC0171.ICJKN_UPD_USER_ID ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = AC0171.ICJKN_UPD_USER_ID ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS ICJKN_UPD_USER_ID_NM ") .append(" ,AC0171.ADD_DTM ") .append(" ,ADD_OPEACNT ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = AC0171.ADD_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS ADD_OPEACNT_NM ") .append(" ,AC0171.UPD_DTM ") .append(" ,AC0171.UPD_OPEACNT ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = AC0171.UPD_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS UPD_OPEACNT_NM ") .append(" ,AC0171.DEL_DTM ") .append(" ,AC0171.DEL_OPEACNT ") .append(" ,(SELECT ZM0051.USER_NM ") .append(" FROM ZM_M_USER ZM0051 ") .append(" WHERE ZM0051.USER_ID = AC0171.DEL_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" ) AS DEL_OPEACNT_NM ") .append(" ,AC0171.MK_FLG ") .append(" ,(SELECT ZM0171.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00016' ") .append(" AND ZM0171.CD_DIV = AC0171.MK_FLG ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS MK_FLG_NM ") .append(" ,(SELECT ZM0171.CD_DIV_ALI ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171 ") .append(" WHERE ZM0171.CD_SBT_CD = 'CD00016' ") .append(" AND ZM0171.CD_DIV = AC0171.MK_FLG ") .append(" AND ZM0171.CD_TSTAYMD <= ? ") .append(" AND ZM0171.CD_TENDYMD >= ? ") .append(" AND ZM0171.MK_FLG = '0' ") .append(" ) AS MK_FLG_ALI ") //v16.00.00 2015/06/05 Add Start .append(",AC0171.USE_STAYMD") .append(",AC0171.USE_ENDYMD") //v16.00.00 2015/06/05 Add End .append(" FROM AC_T_ICJKN AC0171 "); // SQL文_個別部1 if ( "1".equals( inMsg.getObject(EAC0171A010CBSMsg.FUNC_CODE).toString() ) ) { sql_Buff.append(" WHERE AC0171.ICJKN_ADD_SEQ = ? ") .append(" AND AC0171.ICJKN_UPD_SEQ = ? ") .append(" AND AC0171.MK_FLG = '0' "); } //prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); //ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // 手修正箇所S // パラメータインデックス int i = 1; String opeDate = JPCModelCommon.getOnlineOpeDate(inMsg, null); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); // システムコードの指定 CAANJDBCUtil.setParam(pstmt, i++, JACStrConst.SYS_CD_AC); // 業務機能識別コードの指定 CAANJDBCUtil.setParam(pstmt, i++, JACStrConst.WORK_KINO_SKBT_ICJKN_SCR); // 抽出変換コードの指定 CAANJDBCUtil.setParam(pstmt, i++, JACStrConst.CHSHT_CHG_CD_1); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); // 運用日付の指定 CAANJDBCUtil.setParam(pstmt, i++, opeDate); CAANJDBCUtil.setParam(pstmt, i++, opeDate); // 手修正箇所E // PreparedStatementの指定されたパラメータカラムに指定された値を設定 if ( "1".equals( inMsg.getString(EAC0171A010CBSMsg.FUNC_CODE) ) ) { // パラメータの設定(ＫＥＹ＿一時金登録番号を指定) CAANJDBCUtil.setParam(pstmt, i++, inMsg.getObject(EAC0171A010CBSMsg.KEY_ICJKN_ADD_SEQ)); //手修正箇所S //一時金更新枝番が１桁の時は２桁目をスペースで埋める。 if(inMsg.getString(EAC0171A010CBSMsg.KEY_ICJKN_UPD_SEQ).length() == 1) { inMsg.set(EAC0171A010CBSMsg.KEY_ICJKN_UPD_SEQ,inMsg.getString(EAC0171A010CBSMsg.KEY_ICJKN_UPD_SEQ) + " "); } //手修正箇所E // パラメータの設定(ＫＥＹ＿一時金更新枝番を指定) CAANJDBCUtil.setParam(pstmt, i++, inMsg.getObject(EAC0171A010CBSMsg.KEY_ICJKN_UPD_SEQ)); } // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EAC0171A010CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EAC0171A010CBSMsg1List.ICJKN_ADD_SEQ , EAC0171A010CBSMsg1List.ICJKN_UPD_SEQ , EAC0171A010CBSMsg1List.KICJKN_NO , EAC0171A010CBSMsg1List.SEIKY_KEI_NO , EAC0171A010CBSMsg1List.SEIKY_YM , EAC0171A010CBSMsg1List.TONYU_DIV , EAC0171A010CBSMsg1List.TONYU_DIV_NM , EAC0171A010CBSMsg1List.TONYU_DIV_ALI , EAC0171A010CBSMsg1List.SVC_KEI_NO , EAC0171A010CBSMsg1List.PRC_GRP_CD , EAC0171A010CBSMsg1List.PRC_GRP_CD_NM , EAC0171A010CBSMsg1List.PCRS_CD , EAC0171A010CBSMsg1List.PCRS_CD_NM , EAC0171A010CBSMsg1List.PRC_SVC_CD , EAC0171A010CBSMsg1List.PRC_SVC_CD_NM , EAC0171A010CBSMsg1List.PRC_KMK_CD , EAC0171A010CBSMsg1List.PRC_KMK_CD_NM , EAC0171A010CBSMsg1List.TRGT_PRC_GRP_CD , EAC0171A010CBSMsg1List.TRGT_PRC_GRP_CD_NM , EAC0171A010CBSMsg1List.TRGT_PCRS_CD , EAC0171A010CBSMsg1List.TRGT_PCRS_CD_NM , EAC0171A010CBSMsg1List.TRGT_PRC_SVC_CD , EAC0171A010CBSMsg1List.TRGT_PRC_SVC_CD_NM , EAC0171A010CBSMsg1List.WARIB_SVC_CD , EAC0171A010CBSMsg1List.TRGT_PRC_KMK_CD , EAC0171A010CBSMsg1List.TRGT_PRC_KMK_CD_NM , EAC0171A010CBSMsg1List.USE_STAYMD , EAC0171A010CBSMsg1List.ICJKN_PRC , EAC0171A010CBSMsg1List.ICJKN_SBT_CD , EAC0171A010CBSMsg1List.ICJKN_SBT_CD_NM , EAC0171A010CBSMsg1List.ICJKN_SBT_CD_ALI , EAC0171A010CBSMsg1List.ICJKN_TRKM_STAT , EAC0171A010CBSMsg1List.ICJKN_TRKM_STAT_NM , EAC0171A010CBSMsg1List.ICJKN_TRKM_STAT_ALI , EAC0171A010CBSMsg1List.ICJKN_TRKM_RSLT , EAC0171A010CBSMsg1List.ICJKN_TRKM_RSLT_NM , EAC0171A010CBSMsg1List.ICJKN_TRKM_RSLT_ALI , EAC0171A010CBSMsg1List.TRKM_ZUMI_AMNT , EAC0171A010CBSMsg1List.TRKM_ZAN_AMNT , EAC0171A010CBSMsg1List.MANSION_ID , EAC0171A010CBSMsg1List.TK_HOSHIKI_KEI_NO , EAC0171A010CBSMsg1List.KANYU_KEI_PAY_HOSHIKI_CD , EAC0171A010CBSMsg1List.SVC_DTL_SKBT_NO , EAC0171A010CBSMsg1List.KIKI_SEIZO_NO , EAC0171A010CBSMsg1List.ICJKN_ADD_DTM , EAC0171A010CBSMsg1List.ICJKN_ADD_USER_ID , EAC0171A010CBSMsg1List.ICJKN_ADD_USER_ID_NM , EAC0171A010CBSMsg1List.ICJKN_UPD_DTM , EAC0171A010CBSMsg1List.ICJKN_UPD_USER_ID , EAC0171A010CBSMsg1List.ICJKN_UPD_USER_ID_NM , EAC0171A010CBSMsg1List.ADD_DTM , EAC0171A010CBSMsg1List.ADD_OPEACNT , EAC0171A010CBSMsg1List.ADD_OPEACNT_NM , EAC0171A010CBSMsg1List.UPD_DTM , EAC0171A010CBSMsg1List.UPD_OPEACNT , EAC0171A010CBSMsg1List.UPD_OPEACNT_NM , EAC0171A010CBSMsg1List.DEL_DTM , EAC0171A010CBSMsg1List.DEL_OPEACNT , EAC0171A010CBSMsg1List.DEL_OPEACNT_NM , EAC0171A010CBSMsg1List.MK_FLG , EAC0171A010CBSMsg1List.MK_FLG_NM , EAC0171A010CBSMsg1List.MK_FLG_ALI //v16.00.00 2015/06/05 Add Start , EAC0171A010CBSMsg1List.TOGTU_USE_STAYMD , EAC0171A010CBSMsg1List.TOGTU_USE_ENDYMD //v16.00.00 2015/06/05 Add End }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EAC0171A010CBSMsg1List",0); // inMsg(CBSMsg)に明細をセット inMsg.set("EAC0171A010CBSMsg1List", outMsg1); } catch(SQLException e) { inMsg.set(EAC0171A010CBSMsg.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(EAC0171A010CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } }