* 申込明細番号以外で進捗にぶつけてるデータを取得する *
* @param inMsg 入力値の格納されたCBSMsg * @param inContext Agentから渡されたAgentDispatchContext * @return 検索結果 */ private CAANMsg[] getEKK1091B001FirstList(CAANMsg inMsg, AgentDispatchContext inContext) { // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; // PreparedStatementのパラメータカウント int iPCnt = 0; /******************** * SQL文の作成 ********************/ try { //コネクション取得 con1 = JSYejbConnection.getConnection(KK1091ETMsg.getTableName()); // SQL文の作成(項目取得・ベース部分) // (1) StringBuffer sql_Buff1 = new StringBuffer(); sql_Buff1.append(" SELECT KK1091.PRG_NO ") .append(" ,KK1091.IDO_DIV ") .append(" ,(SELECT ZM0171_01.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_01 ") .append(" WHERE ZM0171_01.CD_SBT_CD = 'CD00576' ") .append(" AND ZM0171_01.CD_DIV = KK1091.IDO_DIV ") .append(" AND ZM0171_01.MK_FLG = '0' ") .append(" ) AS IDO_DIV_NM ") .append(" ,KK1131_01.IDO_RSN_CD AS IDO_RSN_CD_1 ") .append(" ,(SELECT ZM0171_02.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_02 ") .append(" WHERE ZM0171_02.CD_SBT_CD = 'CD00846' ") .append(" AND ZM0171_02.CD_DIV = KK1131_01.IDO_RSN_CD ") .append(" AND ZM0171_02.MK_FLG = '0' ") .append(" ) AS IDO_RSN_CD_NM_1 ") .append(" ,KK1131_02.IDO_RSN_CD AS IDO_RSN_CD_2 ") .append(" ,(SELECT ZM0171_03.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_03 ") .append(" WHERE ZM0171_03.CD_SBT_CD = 'CD00846' ") .append(" AND ZM0171_03.CD_DIV = KK1131_02.IDO_RSN_CD ") .append(" AND ZM0171_03.MK_FLG = '0' ") .append(" ) AS IDO_RSN_CD_NM_2 ") .append(" ,KK1131_03.IDO_RSN_CD AS IDO_RSN_CD_3 ") .append(" ,(SELECT ZM0171_04.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_04 ") .append(" WHERE ZM0171_04.CD_SBT_CD = 'CD00846' ") .append(" AND ZM0171_04.CD_DIV = KK1131_03.IDO_RSN_CD ") .append(" AND ZM0171_04.MK_FLG = '0' ") .append(" ) AS IDO_RSN_CD_NM_3 ") .append(" ,KK1131_04.IDO_RSN_CD AS IDO_RSN_CD_4 ") .append(" ,(SELECT ZM0171_05.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_05 ") .append(" WHERE ZM0171_05.CD_SBT_CD = 'CD00846' ") .append(" AND ZM0171_05.CD_DIV = KK1131_04.IDO_RSN_CD ") .append(" AND ZM0171_05.MK_FLG = '0' ") .append(" ) AS IDO_RSN_CD_NM_4 ") .append(" ,KK1131_05.IDO_RSN_CD AS IDO_RSN_CD_5 ") .append(" ,(SELECT ZM0171_06.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_06 ") .append(" WHERE ZM0171_06.CD_SBT_CD = 'CD00846' ") .append(" AND ZM0171_06.CD_DIV = KK1131_05.IDO_RSN_CD ") .append(" AND ZM0171_06.MK_FLG = '0' ") .append(" ) AS IDO_RSN_CD_NM_5 ") .append(" ,KK1091.PRG_STAT ") .append(" ,(SELECT ZM0171_07.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_07 ") .append(" WHERE ZM0171_07.CD_SBT_CD = 'CD00647' ") .append(" AND ZM0171_07.CD_DIV = KK1091.PRG_STAT ") .append(" AND ZM0171_07.MK_FLG = '0' ") .append(" ) AS PRG_STAT_NM ") .append(" ,KK1091.PRG_DTM ") .append(" ,(SELECT ZM0061.ORG_NM ") .append(" FROM ZM_M_ORGANIZATION ZM0061 ") .append(" WHERE ZM0061.ORG_CD = ZM0051.SHUMU_SHOZK_ORG_CD ") .append(" AND ZM0061.MK_FLG = '0' ") .append(" AND SUBSTR(KK1091.ADD_DTM, 1, 8) BETWEEN ZM0061.ORG_TSTAYMD AND ZM0061.ORG_TENDYMD ") .append(" ) AS ORG_NM ") .append(" ,ZM0051.USER_NM ") .append(" ,KK1091.PRG_TKJK_1 ") .append(" ,KK1091.PRG_TKJK_2 ") .append(" ,KK1091.PRG_MEMO ") .append(" ,KK1091.UPD_DTM ") .append(" ,KK1091.IDO_DTM ") .append(" ,KK1091.ADD_DTM ") .append(" FROM KK_T_PRG KK1091 ") .append(" LEFT JOIN ZM_M_USER ZM0051 ") .append(" ON ZM0051.USER_ID = KK1091.ADD_OPEACNT ") .append(" AND ZM0051.MK_FLG = '0' ") .append(" AND SUBSTR(KK1091.ADD_DTM, 1, 8) BETWEEN ZM0051.USER_TSTAYMD AND ZM0051.USER_TENDYMD ") .append(" LEFT JOIN (SELECT KK1131_01T.SVC_KEI_NO ") .append(" ,KK1131_01T.IDO_DIV ") .append(" ,KK1131_01T.IDO_DTM ") .append(" ,KK1131_01T.IDO_RSN_CD ") .append(" FROM (SELECT KK1131_01N.SVC_KEI_NO ") .append(" ,KK1131_01N.IDO_DIV ") .append(" ,KK1131_01N.IDO_DTM ") .append(" ,KK1131_01N.IDO_RSN_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK1131_01N.SVC_KEI_NO ") .append(" ,KK1131_01N.IDO_DIV ") .append(" ,KK1131_01N.IDO_DTM ") .append(" ORDER BY KK1131_01N.IDO_RSN_NO ASC) AS LINE ") .append(" FROM KK_T_IDO_RSN KK1131_01N ") .append(" WHERE KK1131_01N.MK_FLG = '0' ") .append(" ) KK1131_01T ") .append(" WHERE KK1131_01T.LINE = 1 ") .append(" ) KK1131_01 ") .append(" ON KK1131_01.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" AND KK1131_01.IDO_DIV = KK1091.IDO_DIV ") .append(" AND KK1131_01.IDO_DTM = KK1091.IDO_DTM ") .append(" LEFT JOIN (SELECT KK1131_02T.SVC_KEI_NO ") .append(" ,KK1131_02T.IDO_DIV ") .append(" ,KK1131_02T.IDO_DTM ") .append(" ,KK1131_02T.IDO_RSN_CD ") .append(" FROM (SELECT KK1131_02N.SVC_KEI_NO ") .append(" ,KK1131_02N.IDO_DIV ") .append(" ,KK1131_02N.IDO_DTM ") .append(" ,KK1131_02N.IDO_RSN_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK1131_02N.SVC_KEI_NO ") .append(" ,KK1131_02N.IDO_DIV ") .append(" ,KK1131_02N.IDO_DTM ") .append(" ORDER BY KK1131_02N.IDO_RSN_NO ASC) AS LINE ") .append(" FROM KK_T_IDO_RSN KK1131_02N ") .append(" WHERE KK1131_02N.MK_FLG = '0' ") .append(" ) KK1131_02T ") .append(" WHERE KK1131_02T.LINE = 2 ") .append(" ) KK1131_02 ") .append(" ON KK1131_02.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" AND KK1131_02.IDO_DIV = KK1091.IDO_DIV ") .append(" AND KK1131_02.IDO_DTM = KK1091.IDO_DTM ") .append(" LEFT JOIN (SELECT KK1131_03T.SVC_KEI_NO ") .append(" ,KK1131_03T.IDO_DIV ") .append(" ,KK1131_03T.IDO_DTM ") .append(" ,KK1131_03T.IDO_RSN_CD ") .append(" FROM (SELECT KK1131_03N.SVC_KEI_NO ") .append(" ,KK1131_03N.IDO_DIV ") .append(" ,KK1131_03N.IDO_DTM ") .append(" ,KK1131_03N.IDO_RSN_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK1131_03N.SVC_KEI_NO ") .append(" ,KK1131_03N.IDO_DIV ") .append(" ,KK1131_03N.IDO_DTM ") .append(" ORDER BY KK1131_03N.IDO_RSN_NO ASC) AS LINE ") .append(" FROM KK_T_IDO_RSN KK1131_03N ") .append(" WHERE KK1131_03N.MK_FLG = '0' ") .append(" ) KK1131_03T ") .append(" WHERE KK1131_03T.LINE = 3 ") .append(" ) KK1131_03 ") .append(" ON KK1131_03.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" AND KK1131_03.IDO_DIV = KK1091.IDO_DIV ") .append(" AND KK1131_03.IDO_DTM = KK1091.IDO_DTM ") .append(" LEFT JOIN (SELECT KK1131_04T.SVC_KEI_NO ") .append(" ,KK1131_04T.IDO_DIV ") .append(" ,KK1131_04T.IDO_DTM ") .append(" ,KK1131_04T.IDO_RSN_CD ") .append(" FROM (SELECT KK1131_04N.SVC_KEI_NO ") .append(" ,KK1131_04N.IDO_DIV ") .append(" ,KK1131_04N.IDO_DTM ") .append(" ,KK1131_04N.IDO_RSN_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK1131_04N.SVC_KEI_NO ") .append(" ,KK1131_04N.IDO_DIV ") .append(" ,KK1131_04N.IDO_DTM ") .append(" ORDER BY KK1131_04N.IDO_RSN_NO ASC) AS LINE ") .append(" FROM KK_T_IDO_RSN KK1131_04N ") .append(" WHERE KK1131_04N.MK_FLG = '0' ") .append(" ) KK1131_04T ") .append(" WHERE KK1131_04T.LINE = 4 ") .append(" ) KK1131_04 ") .append(" ON KK1131_04.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" AND KK1131_04.IDO_DIV = KK1091.IDO_DIV ") .append(" AND KK1131_04.IDO_DTM = KK1091.IDO_DTM ") .append(" LEFT JOIN (SELECT KK1131_05T.SVC_KEI_NO ") .append(" ,KK1131_05T.IDO_DIV ") .append(" ,KK1131_05T.IDO_DTM ") .append(" ,KK1131_05T.IDO_RSN_CD ") .append(" FROM (SELECT KK1131_05N.SVC_KEI_NO ") .append(" ,KK1131_05N.IDO_DIV ") .append(" ,KK1131_05N.IDO_DTM ") .append(" ,KK1131_05N.IDO_RSN_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK1131_05N.SVC_KEI_NO ") .append(" ,KK1131_05N.IDO_DIV ") .append(" ,KK1131_05N.IDO_DTM ") .append(" ORDER BY KK1131_05N.IDO_RSN_NO ASC) AS LINE ") .append(" FROM KK_T_IDO_RSN KK1131_05N ") .append(" WHERE KK1131_05N.MK_FLG = '0' ") .append(" ) KK1131_05T ") .append(" WHERE KK1131_05T.LINE = 5 ") .append(" ) KK1131_05 ") .append(" ON KK1131_05.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" AND KK1131_05.IDO_DIV = KK1091.IDO_DIV ") .append(" AND KK1131_05.IDO_DTM = KK1091.IDO_DTM "); // SQL文の作成(条件部分) // (3) // 子関数で入力サービスＩＦを元に組み立て StringBuffer sql_BuffWhere = this.strBuffWhereBuilder(inMsg); // SQL文の作成(ソート部分） // (4) // 子関数で入力サービスＩＦを元に組み立て StringBuffer sql_BuffSort = this.strBuffOrderByBuilder(inMsg); // SQL文の組立て StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(sql_Buff1).append(sql_BuffWhere).append(sql_BuffSort); // prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); // ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // パラメータカウントの初期化 iPCnt = 0; // PreparedStatementの指定されたパラメータカラムに 指定された値を設定 // Where句の動的部分を指定 iPCnt = this.setParam(inMsg, pstmt, iPCnt); // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK1091B001CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK1091B001CBSMsg1List.PRG_NO , EKK1091B001CBSMsg1List.IDO_DIV , EKK1091B001CBSMsg1List.IDO_DIV_NM , EKK1091B001CBSMsg1List.IDO_RSN_CD_1 , EKK1091B001CBSMsg1List.IDO_RSN_CD_NM_1 , EKK1091B001CBSMsg1List.IDO_RSN_CD_2 , EKK1091B001CBSMsg1List.IDO_RSN_CD_NM_2 , EKK1091B001CBSMsg1List.IDO_RSN_CD_3 , EKK1091B001CBSMsg1List.IDO_RSN_CD_NM_3 , EKK1091B001CBSMsg1List.IDO_RSN_CD_4 , EKK1091B001CBSMsg1List.IDO_RSN_CD_NM_4 , EKK1091B001CBSMsg1List.IDO_RSN_CD_5 , EKK1091B001CBSMsg1List.IDO_RSN_CD_NM_5 , EKK1091B001CBSMsg1List.PRG_STAT , EKK1091B001CBSMsg1List.PRG_STAT_NM , EKK1091B001CBSMsg1List.PRG_DTM , EKK1091B001CBSMsg1List.ORG_NM , EKK1091B001CBSMsg1List.USER_NM , EKK1091B001CBSMsg1List.PRG_TKJK_1 , EKK1091B001CBSMsg1List.PRG_TKJK_2 , EKK1091B001CBSMsg1List.PRG_MEMO , EKK1091B001CBSMsg1List.UPD_DTM , EKK1091B001CBSMsg1List.IDO_DTM , EKK1091B001CBSMsg1List.ADD_DTM }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery, msgKeyList1, "eo.ejb.cbs.cbsmsg.EKK1091B001CBSMsg1List", 0); return outMsg1; } catch (SQLException e) { inMsg.set(EKK1091B001CBSMsg.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(EKK1091B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** ** 申込明細番号で進捗にぶつけてるデータを取得する *
* @param inMsg 入力値の格納されたCBSMsg * @param inContext Agentから渡されたAgentDispatchContext * @param prgNo 進捗番号 * @return 検索結果 */ private CAANMsg[] getEKK1091B001SecondList(CAANMsg inMsg, AgentDispatchContext inContext, String prgNo) { // コネクション Connection con1 = null; // プリペアステートメント PreparedStatement pstmt = null; // リザルトセット ResultSet rsltQuery = null; /******************** * SQL文の作成 ********************/ try { //コネクション取得 con1 = JSYejbConnection.getConnection(KK1091ETMsg.getTableName()); // SQL文の作成 StringBuffer sql_Buff = new StringBuffer(); sql_Buff.append(" SELECT KK1091.PRG_NO ") .append(" ,KK0021.MSKMSHO_NO ") .append(" ,KK0021.MSKM_YMD ") .append(" ,KK0071_1.AGNT_CD AS AGNT_CD_1 ") .append(" ,KK0071_2.AGNT_CD AS AGNT_CD_2 ") .append(" ,KK0071_3.AGNT_CD AS AGNT_CD_3 ") .append(" ,KK0071_4.AGNT_CD AS AGNT_CD_4 ") .append(" ,KK0071_5.AGNT_CD AS AGNT_CD_5 ") .append(" ,KK1021_01.TMP_PAY_PRC_NO AS TMP_PAY_PRC_NO_1 ") .append(" ,KK1021_01.ICJKN_SETTE_CD AS ICJKN_SETTE_CD_1 ") .append(" ,(SELECT ZM0171_08.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_08 ") .append(" WHERE ZM0171_08.CD_SBT_CD = 'CD00346' ") .append(" AND ZM0171_08.CD_DIV = KK1021_01.ICJKN_SETTE_CD ") .append(" AND ZM0171_08.MK_FLG = '0' ") .append(" ) AS ICJKN_SETTE_CD_NM_1 ") .append(" ,KOJIAK.TMP_PAY_PRC_NO_2 ") .append(" ,KOJIAK.ICJKN_SETTE_CD_2 ") .append(" ,KOJIAK.ICJKN_SETTE_CD_NM_2 ") .append(" ,KK0021.MSKM_DTL_NO ") .append(" ,KK0021.MSKMSHO_DTL_NO ") .append(" FROM KK_T_PRG KK1091 ") .append(" LEFT JOIN (SELECT * ") .append(" FROM KK_T_MSKM_DTL KK0021_01 ") .append(" WHERE (KK0021_01.MSKM_DTL_NO, KK0021_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) ") .append(" FROM KK_T_MSKM_DTL KK0021_GENE ") .append(" WHERE KK0021_GENE.MSKM_DTL_NO = KK0021_01.MSKM_DTL_NO ") .append(" AND KK0021_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0021_GENE.MSKM_DTL_NO) ") .append(" ) KK0021 ON KK0021.MSKM_DTL_NO = KK1091.MSKM_DTL_NO ") .append(" LEFT JOIN KK_T_ICJKN_SETTE KK1021_01 ") .append(" ON KK1021_01.MSKM_DTL_NO = KK1091.MSKM_DTL_NO ") .append(" AND KK1021_01.ADD_DTM = KK1091.ADD_DTM ") .append(" AND SUBSTR(KK1021_01.TMP_PAY_PRC_NO, 3, 3) IN ('005', '045') ") .append(" AND SUBSTR(KK1021_01.TMP_PAY_PRC_NO, 3, 6) <> '045002' ") .append(" LEFT JOIN (SELECT ") .append(" KK0071_12.AGNT_CD ") .append(" ,KK0071_12.MSKM_NO ") .append(" FROM ") .append(" (SELECT ") .append(" KK0071_11.MSKM_NO ") .append(" ,KK0071_11.AGNT_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK0071_11.MSKM_NO ORDER BY KK0071_11.DSP_JUN ASC) AS LINE ") .append(" FROM ") .append(" KK_T_MSKM_AGNT KK0071_11 ") .append(" WHERE KK0071_11.MK_FLG = '0' ") .append(" ) KK0071_12 ") .append(" WHERE KK0071_12.LINE = 1 ") .append(" ) KK0071_1 ") .append(" ON KK0071_1.MSKM_NO = KK0021.MSKM_NO ") .append(" LEFT JOIN (SELECT ") .append(" KK0071_22.AGNT_CD ") .append(" ,KK0071_22.MSKM_NO ") .append(" FROM ") .append(" (SELECT ") .append(" KK0071_21.MSKM_NO ") .append(" ,KK0071_21.AGNT_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK0071_21.MSKM_NO ORDER BY KK0071_21.DSP_JUN ASC) AS LINE ") .append(" FROM ") .append(" KK_T_MSKM_AGNT KK0071_21 ") .append(" WHERE KK0071_21.MK_FLG = '0' ") .append(" ) KK0071_22 ") .append(" WHERE KK0071_22.LINE = 2 ") .append(" ) KK0071_2 ") .append(" ON KK0071_2.MSKM_NO = KK0021.MSKM_NO ") .append(" LEFT JOIN (SELECT ") .append(" KK0071_32.AGNT_CD ") .append(" ,KK0071_32.MSKM_NO ") .append(" FROM ") .append(" (SELECT ") .append(" KK0071_31.MSKM_NO ") .append(" ,KK0071_31.AGNT_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK0071_31.MSKM_NO ORDER BY KK0071_31.DSP_JUN ASC) AS LINE ") .append(" FROM ") .append(" KK_T_MSKM_AGNT KK0071_31 ") .append(" WHERE KK0071_31.MK_FLG = '0' ") .append(" ) KK0071_32 ") .append(" WHERE KK0071_32.LINE = 3 ") .append(" ) KK0071_3 ") .append(" ON KK0071_3.MSKM_NO = KK0021.MSKM_NO ") .append(" LEFT JOIN (SELECT ") .append(" KK0071_42.AGNT_CD ") .append(" ,KK0071_42.MSKM_NO ") .append(" FROM ") .append(" (SELECT ") .append(" KK0071_41.MSKM_NO ") .append(" ,KK0071_41.AGNT_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK0071_41.MSKM_NO ORDER BY KK0071_41.DSP_JUN ASC) AS LINE ") .append(" FROM ") .append(" KK_T_MSKM_AGNT KK0071_41 ") .append(" WHERE KK0071_41.MK_FLG = '0' ") .append(" ) KK0071_42 ") .append(" WHERE KK0071_42.LINE = 4 ") .append(" ) KK0071_4 ") .append(" ON KK0071_4.MSKM_NO = KK0021.MSKM_NO ") .append(" LEFT JOIN (SELECT ") .append(" KK0071_52.AGNT_CD ") .append(" ,KK0071_52.MSKM_NO ") .append(" FROM ") .append(" (SELECT ") .append(" KK0071_51.MSKM_NO ") .append(" ,KK0071_51.AGNT_CD ") .append(" ,ROW_NUMBER() OVER (PARTITION BY KK0071_51.MSKM_NO ORDER BY KK0071_51.DSP_JUN ASC) AS LINE ") .append(" FROM ") .append(" KK_T_MSKM_AGNT KK0071_51 ") .append(" WHERE KK0071_51.MK_FLG = '0' ") .append(" ) KK0071_52 ") .append(" WHERE KK0071_52.LINE = 5 ") .append(" ) KK0071_5 ") .append(" ON KK0071_5.MSKM_NO = KK0021.MSKM_NO ") .append(" , (SELECT ") .append(" CASE WHEN KK0081.SVC_KEI_NO IS NOT NULL ") .append(" THEN CASE WHEN KK0081.KOJI_UM = '1' ") .append(" THEN KK1021_01.TMP_PAY_PRC_NO ") .append(" ELSE KK1021_02.TMP_PAY_PRC_NO ") .append(" END ") .append(" ELSE NULL ") .append(" END TMP_PAY_PRC_NO_2 ") .append(" ,CASE WHEN KK0081.SVC_KEI_NO IS NOT NULL ") .append(" THEN CASE WHEN KK0081.KOJI_UM = '1' ") .append(" THEN CASE WHEN KK1021_01.TMP_PAY_PRC_NO IS NOT NULL ") .append(" THEN '01' ") .append(" ELSE '02' ") .append(" END ") .append(" ELSE CASE WHEN KK1021_02.TMP_PAY_PRC_NO IS NOT NULL ") .append(" THEN KK1021_02.ICJKN_SETTE_CD ") .append(" ELSE NULL ") .append(" END ") .append(" END ") .append(" ELSE NULL ") .append(" END ICJKN_SETTE_CD_2 ") .append(" ,CASE WHEN KK0081.SVC_KEI_NO IS NOT NULL ") .append(" THEN CASE WHEN KK0081.KOJI_UM = '1' ") .append(" THEN CASE WHEN KK1021_01.TMP_PAY_PRC_NO IS NOT NULL ") .append(" THEN '無' ") .append(" ELSE '有' ") .append(" END ") .append(" ELSE CASE WHEN KK1021_02.TMP_PAY_PRC_NO IS NOT NULL ") .append(" THEN (SELECT ZM0171_09.CD_DIV_NM ") .append(" FROM ZM_M_CD_NM_KANRI ZM0171_09 ") .append(" WHERE ZM0171_09.CD_SBT_CD = 'CD00346' ") .append(" AND ZM0171_09.CD_DIV = KK1021_02.ICJKN_SETTE_CD ") .append(" AND ZM0171_09.MK_FLG = '0') ") .append(" ELSE NULL ") .append(" END ") .append(" END ") .append(" ELSE NULL ") .append(" END ICJKN_SETTE_CD_NM_2 ") .append(" FROM KK_T_PRG KK1091 ") .append(" LEFT JOIN ( ") .append(" SELECT KK0081_01.SVC_KEI_NO ") .append(" ,CASE WHEN KK1091_01.IDO_DIV = '00009' ") .append(" THEN CASE WHEN KU0011.MANSION_BUKKEN_CD IS NOT NULL ") .append(" THEN '0' ") .append(" ELSE '1' ") .append(" END ") .append(" ELSE CASE WHEN KU0011.MANSION_BUKKEN_CD IS NOT NULL ") .append(" THEN CASE WHEN KK0891.TK_HOSHIKI_KEI_NO IS NOT NULL ") .append(" THEN '0' ") .append(" ELSE '1' ") .append(" END ") .append(" ELSE '1' ") .append(" END ") .append(" END KOJI_UM ") .append(" FROM KK_T_SVC_KEI KK0081_01 ") .append(" INNER JOIN KK_T_PRG KK1091_01 ") .append(" ON KK1091_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ") .append(" LEFT JOIN ( ") .append(" SELECT DISTINCT ") .append(" KU0091_01.MSKM_DTL_NO ") .append(" ,KU0011_01.MANSION_BUKKEN_CD ") .append(" FROM KU_T_KOJIAK KU0011_01 ") .append(" INNER JOIN KU_T_MSKM_DTL_KOJIAK KU0091_01 ") .append(" ON KU0091_01.KOJIAK_NO = KU0011_01.KOJIAK_NO ") .append(" AND KU0011_01.MK_FLG = '0' ") .append(" AND KU0091_01.MK_FLG = '0' ") .append(" ) KU0011 ") .append(" ON KU0011.MSKM_DTL_NO = KK1091_01.MSKM_DTL_NO ") .append(" AND KU0011.MANSION_BUKKEN_CD = '001' ") .append(" AND KK0081_01.PRC_GRP_CD IN('04','10') ") .append(" LEFT JOIN KK_T_TK_HOSHIKI_KEI KK0891 ") .append(" ON KK0891.TK_HOSHIKI_KEI_NO = KK0081_01.TK_HOSHIKI_KEI_NO ") .append(" AND KK0891.TK_HOSHIKI_CD NOT IN('008','009') ") .append(" AND KK0891.MK_FLG = '0' ") .append(" AND KK0081_01.PRC_GRP_CD IN('04','10') ") .append(" WHERE ") .append(" KK1091_01.PRG_NO = ? ") .append(" AND KK0081_01.SVC_KEI_NO = ? ") .append(" AND KK0081_01.PRC_GRP_CD IN('02','03','04','10','11','12','13') ") .append(" AND (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = ") .append(" (SELECT KK0081_01_GENE.SVC_KEI_NO, MAX(KK0081_01_GENE.RSV_APLY_YMD || KK0081_01_GENE.GENE_ADD_DTM) AS KK0081_01_MAX ") .append(" FROM KK_T_SVC_KEI KK0081_01_GENE ") .append(" WHERE KK0081_01_GENE.SVC_KEI_NO = KK0081_01.SVC_KEI_NO ") .append(" AND KK0081_01_GENE.RSV_APLY_YMD <= ? ") .append(" AND KK0081_01_GENE.RSV_APLY_CD = '2' ") .append(" AND KK0081_01_GENE.MK_FLG = '0' ") .append(" GROUP BY KK0081_01_GENE.SVC_KEI_NO) ") .append(" AND EXISTS ") .append(" (SELECT 1 ") .append(" FROM KU_T_MSKM_DTL_KOJIAK KU0091 ") .append(" WHERE KU0091.MSKM_DTL_NO = KK1091_01.MSKM_DTL_NO ") .append(" AND KU0091.MK_FLG = '0') ") .append(" ) KK0081 ") .append(" ON KK0081.SVC_KEI_NO = KK1091.SVC_KEI_NO ") .append(" LEFT JOIN KK_T_ICJKN_SETTE KK1021_01 ") .append(" ON KK1021_01.MSKM_DTL_NO = KK1091.MSKM_DTL_NO ") .append(" AND KK1021_01.ADD_DTM = KK1091.ADD_DTM ") .append(" AND KK1021_01.TMP_PAY_PRC_NO = '999990000001' ") .append(" AND KK1021_01.MK_FLG = '0' ") .append(" LEFT JOIN KK_T_ICJKN_SETTE KK1021_02 ") .append(" ON KK1021_02.MSKM_DTL_NO = KK1091.MSKM_DTL_NO ") .append(" AND KK1021_02.ADD_DTM = KK1091.ADD_DTM ") .append(" AND SUBSTR(KK1021_02.TMP_PAY_PRC_NO, 3, 3) IN ('008', '009', '049') ") .append(" AND KK1021_02.MK_FLG = '0' ") .append(" WHERE ") .append(" KK1091.PRG_NO = ? ") .append(" ) KOJIAK ") .append(" WHERE KK1091.PRG_NO = ? "); // prepareStatementにSQL文をセット pstmt = con1.prepareStatement(sql_Buff.toString()); // ログ出力(SQL文の出力) JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff); // パラメータの設定(進捗番号を指定) CAANJDBCUtil.setParam(pstmt, 1, prgNo); // パラメータの設定(ＫＥＹ＿サービス契約番号を指定) CAANJDBCUtil.setParam(pstmt, 2, inMsg.getObject(EKK1091B001CBSMsg.KEY_SVC_KEI_NO)); // パラメータの設定(運用日付を指定) CAANJDBCUtil.setParam(pstmt, 3, JKKModelCommon.getOpeDate(inMsg)); // パラメータの設定(進捗番号を指定) CAANJDBCUtil.setParam(pstmt, 4, prgNo); // パラメータの設定(進捗番号を指定) CAANJDBCUtil.setParam(pstmt, 5, prgNo); // ResultSetの取得 rsltQuery = pstmt.executeQuery(); // EKK1091B001CBSMsgの明細にセットする項目 String[] msgKeyList1 = { EKK1091B001CBSMsg1List.PRG_NO , EKK1091B001CBSMsg1List.MSKMSHO_NO , EKK1091B001CBSMsg1List.MSKM_YMD , EKK1091B001CBSMsg1List.AGNT_CD_1 , EKK1091B001CBSMsg1List.AGNT_CD_2 , EKK1091B001CBSMsg1List.AGNT_CD_3 , EKK1091B001CBSMsg1List.AGNT_CD_4 , EKK1091B001CBSMsg1List.AGNT_CD_5 , EKK1091B001CBSMsg1List.TMP_PAY_PRC_NO_1 , EKK1091B001CBSMsg1List.ICJKN_SETTE_CD_1 , EKK1091B001CBSMsg1List.ICJKN_SETTE_CD_NM_1 , EKK1091B001CBSMsg1List.TMP_PAY_PRC_NO_2 , EKK1091B001CBSMsg1List.ICJKN_SETTE_CD_2 , EKK1091B001CBSMsg1List.ICJKN_SETTE_CD_NM_2 , EKK1091B001CBSMsg1List.MSKM_DTL_NO , EKK1091B001CBSMsg1List.MSKMSHO_DTL_NO }; // 明細に処理結果(ResultSet)をマッピング CAANMsg[] outMsg1 = mapMessageList(rsltQuery, msgKeyList1, "eo.ejb.cbs.cbsmsg.EKK1091B001CBSMsg1List", 0); return outMsg1; } catch (SQLException e) { inMsg.set(EKK1091B001CBSMsg.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(EKK1091B001CBSMsg.STATUS, StatusCodes.FIND_DB_ERR); throw new CAANRuntimeException(e); } } } /** ** CBSMsgで渡された各入力値を元に、 * SQLの条件部分を組みたてます。 *
* @param inMsg 入力値の格納されたCBSMsg * @return sbf inMsgを元に組み立てられた検索条件 */ private StringBuffer strBuffWhereBuilder(CAANMsg inMsg){ StringBuffer sbf = new StringBuffer(); sbf.append(" WHERE KK1091.MK_FLG = '0' ") .append(" AND KK1091.SVC_KEI_NO = ? "); // ＫＥＹ＿異動区分の条件設定 if (!inMsg.isNull(EKK1091B001CBSMsg.KEY_IDO_DIV)) { sbf.append(" AND KK1091.IDO_DIV = ? "); } return sbf; } /** *
* CBSMsgにあるＫＥＹ＿ソート項目NoとＫＥＹ＿ソート区分を元に、
* SQLのソート条件を組立てます。
*
* サービスIFから渡された値を元に、
* PreparedStatementのパラメータ設定を行います。
*
* 1個目の検索結果と2個目の検索結果を結合します *
* @param firstInfo 一個目の検索結果 * @param secondInfo 二個目の検索結果 * @return 結合結果 */ private CAANMsg execKetugou(CAANMsg firstInfo, CAANMsg secondInfo) { HashMap