/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbEDK0011B040TPDA
* ソースファイル名：JSYejbEDK0011B040TPDA.java
* 作成者 ：EK909135
* 日付 ：2011年09月26日
*＜機能概要＞
* SQLFacility
* 出荷状況詳細一覧照会テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* ｖ1.00.00
* ｖ7.00.00 2013/11/19 FJ）山口 ANK-1578-00-00(多機能ルータ対応)
*
********************************************************************************/
package eo.ejb.cbs.sqlf;
import com.fujitsu.futurity.model.base.CAANJDBCUtil;
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.ejb.common.JSYejbConnection;
import com.fujitsu.futurity.model.ejb.common.JSYejbLog;
import com.fujitsu.futurity.model.ejb.common.StatusCodes;
import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler;
import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import eo.common.constant.JPCModelConstant;
import eo.ejb.cbs.cbsmsg.EDK0011B040CBSMsg;
import eo.ejb.cbs.cbsmsg.EDK0011B040CBSMsg1List;
import eo.ejb.cbm.entity.DK0011ETMsg;
import eo.ejb.common.JDKModelCommon.COMMA;
import static eo.ejb.common.JDKModelCommon.*;
import static eo.common.constant.JZM0171Constant.*;
/**
*
* テンプレートDBアクセス部品 EDK0011B040_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbEDK0011B040TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* サービス契約番号の予約適用年月日に当て込む日時
*/
private static final String RSV_DATE = new StringBuffer("(CASE HAISO.HAISO_STAT")
.append("\n WHEN '001' THEN HAISO.HAISO_UK_YMD ")
.append("\n WHEN '002' THEN HAISO.HAISO_SJI_YMD ")
.append("\n WHEN '003' THEN HAISO.HAISO_SJI_YMD ")
.append("\n WHEN '004' THEN HAISO.HAISO_SJI_YMD ")
.append("\n WHEN '005' THEN HAISO.HAISO_UK_YMD ")
.append("\nEND ) ").toString();
/**
* コンストラクタ
*/
public JSYejbEDK0011B040TPDA() {
}
/**
* EDK0011B040_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(DK0011ETMsg.getTableName());
StringBuffer sql_Buff = new StringBuffer();
/* ++++++++++ ｖ1.00.00 変更開始 ++++++++++ */
// SQL文_個別部
if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B040CBSMsg.FUNC_CODE).toString()))
{
sql_Buff.append(" SELECT ")
.append(" TMP.SHKALOTNO AS SHKALOTNO, ")
.append(" TMP.SHKALOTKIKISEQ AS SHKALOTKIKISEQ, ")
.append(" TMP.IRAIYMD AS IRAIYMD, ")
.append(" TMP.STAT AS STAT, ")
.append(" TMP.DENPYONO AS DENPYONO, ")
.append(" KK0081.SVC_KEI_NO AS SVCKEINO, ")
.append(" KK0081.SYSID AS SYSID, ")
.append(" PRCGRP.PRC_GRP_NM AS PRCGRP, ")
.append(" TMP.HOJINPRCGRP AS HOJINPRCGRP, ")
.append(" TMP.KIKISOHUSPCD AS KIKISOHUSPCD, ")
.append(" SHKAKIHONINFO.HISOS_PCD AS HISOSPCD, ")
.append(" TMP.KIKISOHUSSTATE AS KIKISOHUSSTATE, ")
.append(" TMP.KIKISOHUSCITY AS KIKISOHUSCITY, ")
.append(" TMP.KIKISOHUSOAZTSU AS KIKISOHUSOAZTSU, ")
.append(" TMP.KIKISOHUSAZCHO AS KIKISOHUSAZCHO, ")
.append(" TMP.KIKISOHUSBNCHIGO AS KIKISOHUSBNCHIGO, ")
.append(" TMP.KIKISOHUSADRTTM AS KIKISOHUSADRTTM, ")
.append(" TMP.KIKISOHUSADRRM AS KIKISOHUSADRRM, ")
.append(" SHKAKIHONINFO.HISOS_STATE_NM AS HISOSSTATE, ")
.append(" SHKAKIHONINFO.HISOS_CITY_NM AS HISOSCITY, ")
.append(" SHKAKIHONINFO.HISOS_OAZTSU_NM AS HISOSOAZTSU, ")
.append(" SHKAKIHONINFO.HISOS_AZCHO_NM AS HISOSAZCHO, ")
.append(" SHKAKIHONINFO.HISOS_BNCHIGO AS HISOSBNCHIGO, ")
.append(" SHKAKIHONINFO.HISOS_ADRTTM AS HISOSADRTTM, ")
.append(" SHKAKIHONINFO.HISOS_ADRRM AS HISOSADRRM, ")
.append(" TMP.KIKISOHUS AS KIKISOHUS, ")
.append(" SHKAKIHONINFO.HISOS_NM AS HISOS, ")
.append(" TMP.KIKISOHUSTELNO AS KIKISOHUSTELNO, ")
.append(" SHKAKIHONINFO.HISOS_TELNO AS HISOSTELNO, ")
.append(" TMP.SOKONM AS SOKONM, ")
.append(" TMP.SHELFNM AS SHELFNM, ")
.append(" TMP.SOKOTELNO AS SOKOTELNO, ")
.append(" TMP.SOKOFAXNO AS SOKOFAXNO, ")
.append(" TMP.MAKERNAME AS MAKERNAME, ")
.append(" TMP.TAKNKIKIMODELCD AS TAKNKIKIMODELCD, ")
.append(" TMP.HUZOKUHINMODELCD AS HUZOKUHINMODELCD, ")
.append(" TMP.SETHINMODELCD AS SETHINMODELCD, ")
.append(" TMP.GOODSNAME AS GOODSNAME, ")
.append(" TMP.SEIZONO AS SEIZONO, ")
.append(" TMP.CNT AS CNT, ")
.append(" TMP.HAISODIV AS HAISODIV, ")
.append(" TMP.HAISOSTAT AS HAISOSTAT, ")
.append(" TMP.IDOMOTOKRIPLACESKCD AS IDOMOTOKRIPLACESKCD ")
.append(" FROM ")
.append(" ( ")
.append(" SELECT ")
.append(" HAISOBPIN.SHUKKA_LOT_NO AS SHKALOTNO, ")
.append(" HAISOBPIN.SHUKKA_LOT_KIKI_SEQ AS SHKALOTKIKISEQ, ")
.append(" HAISO.HAISO_UK_YMD AS IRAIYMD, ")
.append( cd(CD00009, "HAISO.HAISO_STAT", COMMA.RIGHT, "STAT"))
.append(" SHKAFINDTL.DENPYO_NO AS DENPYONO, ")
.append( svcKeiNoFor2("KK0341", "SVC_KEI_NO", RSV_DATE, COMMA.RIGHT, RSV_DATE))
.append( cd(CD00886, "HAISO.HOJIN_PRC_GRP_CD", COMMA.RIGHT, "HOJINPRCGRP"))
.append(" KK0341.KIKI_SOHUS_PCD AS KIKISOHUSPCD, ")
.append(" KK0341.KIKI_SOHUS_STATE_NM AS KIKISOHUSSTATE, ")
.append(" KK0341.KIKI_SOHUS_CITY_NM AS KIKISOHUSCITY, ")
.append(" KK0341.KIKI_SOHUS_OAZTSU_NM AS KIKISOHUSOAZTSU, ")
.append(" KK0341.KIKI_SOHUS_AZCHO_NM AS KIKISOHUSAZCHO, ")
.append(" KK0341.KIKI_SOHUS_BNCHIGO AS KIKISOHUSBNCHIGO, ")
.append(" KK0341.KIKI_SOHUS_ADRTTM AS KIKISOHUSADRTTM, ")
.append(" KK0341.KIKI_SOHUS_ADRRM AS KIKISOHUSADRRM, ")
.append(" KK0341.KIKI_SOHUS_NM AS KIKISOHUS, ")
.append(" KK0341.KIKI_SOHUS_TELNO AS KIKISOHUSTELNO, ")
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_NM ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_NM ")
.append(" WHEN '3' THEN YTK_SK_OFFC.YOTAKU_SAKI_OFFC_NM ")
.append(" END AS SOKONM, ")
.append( cdNoYmd(
CD00544,
"DECODE(HAISO.IDOMOTO_KRIPLACE_SKCD , '1' , HAISOBPIN.HAISO_MOTO_SKDN_CD , "
+ " '2', HAISOBPIN.IDOMOTO_KOCOMP_SLF_CD , "
+ " '3', HAISOBPIN.IDOMOTO_YTKSKOF_SLF_CD , "
+ " HAISOBPIN.HAISO_MOTO_SKDN_CD)",
COMMA.RIGHT,
"SHELFNM"))
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_TELNO ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_TLN ")
.append(" WHEN '3' THEN YTK_SK_OFFC.OFFC_TLN ")
.append(" END AS SOKOTELNO, ")
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_FAX_NO ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_FAX_NO ")
.append(" WHEN '3' THEN YTK_SK_OFFC.OFFC_FAX_NO ")
.append(" END AS SOKOFAXNO, ")
.append(" MAKER.MAKER_NM_1 AS MAKERNAME, ")
.append(" HAISOBPIN.TAKNKIKI_MODEL_CD AS TAKNKIKIMODELCD, ")
.append(" HAISOBPIN.HUZOKUHIN_MODEL_CD AS HUZOKUHINMODELCD, ")
.append(" HAISOBPIN.TAKNKIKI_SETHIN_MODEL_CD AS SETHINMODELCD, ")
.append(" TAKNKIKI_MODEL.TAKNKIKI_MODEL_NM AS GOODSNAME, ")
.append(" HAISOBPIN.KIKI_SEIZO_NO AS SEIZONO, ")
.append(" '1' AS CNT, ")
.append(" HAISO.HAISO_DIV AS HAISODIV, ")
.append(" HAISO.HAISO_STAT AS HAISOSTAT, ")
.append(" HAISO.IDOMOTO_KRIPLACE_SKCD AS IDOMOTOKRIPLACESKCD ")
.append(" FROM ")
.append(" DK_T_HAISO HAISO ")
.append(" LEFT OUTER JOIN DK_T_HAISO_TG_BPIN HAISOBPIN ")
.append(" ON (HAISOBPIN.HAISO_NO = HAISO.HAISO_NO ")
.append(" AND HAISOBPIN.MK_FLG = '0') ")
.append( joinKKTKSvc("KK0341", "HAISOBPIN", "KKTK_SVC_KEI_NO", "KIKI_CHG_NO", "INNER"))
.append(" LEFT OUTER JOIN DK_T_SHKA_FIN_DTL SHKAFINDTL ")
.append(" ON (SHKAFINDTL.SHUKKA_LOT_NO = HAISOBPIN.SHUKKA_LOT_NO ")
.append(" AND SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ = HAISOBPIN.SHUKKA_LOT_KIKI_SEQ ")
.append(" AND SHKAFINDTL.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_TAKNKIKI_MODEL TAKNKIKI_MODEL ")
.append(" ON (TAKNKIKI_MODEL.TAKNKIKI_MODEL_CD = HAISOBPIN.TAKNKIKI_MODEL_CD ")
.append(" AND TAKNKIKI_MODEL.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_MAKER MAKER ")
.append(" ON (MAKER.MAKER_CD = TAKNKIKI_MODEL.MAKER_CD ")
.append(" AND MAKER.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN DK_M_SOKO SOKO ")
.append(" ON (SOKO.SOKO_CD = HAISO.HAISO_MOTO_SOKO_CD ")
.append(" AND SOKO.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_KOJI_COMP KOJI_COMP ")
.append(" ON KOJI_COMP.KOJI_COMP_CD = HAISO.IDOMOTO_KOCOMP_CD ")
.append(" AND KOJI_COMP.MK_FLG = '0' ")
.append(" LEFT OUTER JOIN ZM_M_YTK_SK_OFFC YTK_SK_OFFC ")
.append(" ON YTK_SK_OFFC.YOTAKU_SAKI_OFFC_CD = HAISO.IDOMOTO_YTKSKOF_CD ")
.append(" AND YTK_SK_OFFC.MK_FLG = '0' ")
.append(" WHERE ")
.append(" HAISO.MK_FLG = '0' ")
.append(" AND HAISOBPIN.HAISO_TAIBPIN_NO = ? ")
.append(" ) TMP ")
.append( innerJoinToGen("KK_T_SVC_KEI", "KK0081", "TMP", "SVC_KEI_NO"))
.append(" LEFT OUTER JOIN ( SELECT PRCGRP_1.PRC_GRP_CD,PRCGRP_1.PRC_GRP_NM ")
.append(" FROM KK_M_PRC_GRP PRCGRP_1 ")
.append(" WHERE PRCGRP_1.GENE_ADD_DTM = (SELECT MAX(PRCGRP_2.GENE_ADD_DTM) ")
.append(" FROM KK_M_PRC_GRP PRCGRP_2 ")
.append(" WHERE PRCGRP_2.PRC_GRP_CD = PRCGRP_1.PRC_GRP_CD ")
.append(" AND PRCGRP_2.PRC_GRP_TSTAYMD <= ? ")
.append(" AND PRCGRP_2.MK_FLG = '0')) PRCGRP ")
.append(" ON (PRCGRP.PRC_GRP_CD = KK0081.PRC_GRP_CD) ");
sql_Buff.append(" LEFT OUTER JOIN DK_T_SHKA_KIHON_INFO SHKAKIHONINFO ")
.append(" ON (SHKAKIHONINFO.SHUKKA_LOT_NO = TMP.SHKALOTNO ")
.append(" AND SHKAKIHONINFO.SVC_KEI_NO = TMP.SVC_KEI_NO ")
.append(" AND SHKAKIHONINFO.MK_FLG = '0') ");
}
if (JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B040CBSMsg.FUNC_CODE).toString()))
{
sql_Buff.append(" SELECT ")
.append(" TMP.SHKALOTNO AS SHKALOTNO, ")
.append(" TMP.SHKALOTKIKISEQ AS SHKALOTKIKISEQ, ")
.append(" TMP.IRAIYMD AS IRAIYMD, ")
.append(" TMP.STAT AS STAT, ")
.append(" TMP.DENPYONO AS DENPYONO, ")
.append(" KK0081.SVC_KEI_NO AS SVCKEINO, ")
.append(" KK0081.SYSID AS SYSID, ")
.append(" PRCGRP.PRC_GRP_NM AS PRCGRP, ")
.append(" TMP.HOJINPRCGRP AS HOJINPRCGRP, ")
.append(" TMP.KIKISOHUSPCD AS KIKISOHUSPCD, ")
.append(" SHKAKIHONINFO.HISOS_PCD AS HISOSPCD, ")
.append(" TMP.KIKISOHUSSTATE AS KIKISOHUSSTATE, ")
.append(" TMP.KIKISOHUSCITY AS KIKISOHUSCITY, ")
.append(" TMP.KIKISOHUSOAZTSU AS KIKISOHUSOAZTSU, ")
.append(" TMP.KIKISOHUSAZCHO AS KIKISOHUSAZCHO, ")
.append(" TMP.KIKISOHUSBNCHIGO AS KIKISOHUSBNCHIGO, ")
.append(" TMP.KIKISOHUSADRTTM AS KIKISOHUSADRTTM, ")
.append(" TMP.KIKISOHUSADRRM AS KIKISOHUSADRRM, ")
.append(" SHKAKIHONINFO.HISOS_STATE_NM AS HISOSSTATE, ")
.append(" SHKAKIHONINFO.HISOS_CITY_NM AS HISOSCITY, ")
.append(" SHKAKIHONINFO.HISOS_OAZTSU_NM AS HISOSOAZTSU, ")
.append(" SHKAKIHONINFO.HISOS_AZCHO_NM AS HISOSAZCHO, ")
.append(" SHKAKIHONINFO.HISOS_BNCHIGO AS HISOSBNCHIGO, ")
.append(" SHKAKIHONINFO.HISOS_ADRTTM AS HISOSADRTTM, ")
.append(" SHKAKIHONINFO.HISOS_ADRRM AS HISOSADRRM, ")
.append(" TMP.KIKISOHUS AS KIKISOHUS, ")
.append(" SHKAKIHONINFO.HISOS_NM AS HISOS, ")
.append(" TMP.KIKISOHUSTELNO AS KIKISOHUSTELNO, ")
.append(" SHKAKIHONINFO.HISOS_TELNO AS HISOSTELNO, ")
.append(" TMP.SOKONM AS SOKONM, ")
.append(" TMP.SHELFNM AS SHELFNM, ")
.append(" TMP.SOKOTELNO AS SOKOTELNO, ")
.append(" TMP.SOKOFAXNO AS SOKOFAXNO, ")
.append(" TMP.MAKERNAME AS MAKERNAME, ")
.append(" TMP.TAKNKIKIMODELCD AS TAKNKIKIMODELCD, ")
.append(" TMP.HUZOKUHINMODELCD AS HUZOKUHINMODELCD, ")
.append(" TMP.SETHINMODELCD AS SETHINMODELCD, ")
.append(" TMP.GOODSNAME AS GOODSNAME, ")
.append(" TMP.SEIZONO AS SEIZONO, ")
.append(" TMP.CNT AS CNT, ")
.append(" TMP.HAISODIV AS HAISODIV, ")
.append(" TMP.HAISOSTAT AS HAISOSTAT, ")
.append(" TMP.IDOMOTOKRIPLACESKCD AS IDOMOTOKRIPLACESKCD ")
.append(" FROM ")
.append(" ( ")
.append(" SELECT ")
.append(" SHKAFINERR.SHUKKA_LOT_NO AS SHKALOTNO, ")
.append(" SHKAFINERR.SHUKKA_LOT_KIKI_SEQ AS SHKALOTKIKISEQ, ")
.append(" HAISO.HAISO_UK_YMD AS IRAIYMD, ")
.append( cd(CD00029, "SHKAFINERR.SHUKKA_ERR_CD", COMMA.RIGHT, "STAT"))
.append(" SHKAFINDTL.DENPYO_NO AS DENPYONO, ")
.append( svcKeiNoFor2("KK0341", "SVC_KEI_NO", RSV_DATE, COMMA.RIGHT, RSV_DATE))
.append( cd(CD00886, "HAISO.HOJIN_PRC_GRP_CD", COMMA.RIGHT, "HOJINPRCGRP"))
.append(" KK0341.KIKI_SOHUS_PCD AS KIKISOHUSPCD, ")
.append(" KK0341.KIKI_SOHUS_STATE_NM AS KIKISOHUSSTATE, ")
.append(" KK0341.KIKI_SOHUS_CITY_NM AS KIKISOHUSCITY, ")
.append(" KK0341.KIKI_SOHUS_OAZTSU_NM AS KIKISOHUSOAZTSU, ")
.append(" KK0341.KIKI_SOHUS_AZCHO_NM AS KIKISOHUSAZCHO, ")
.append(" KK0341.KIKI_SOHUS_BNCHIGO AS KIKISOHUSBNCHIGO, ")
.append(" KK0341.KIKI_SOHUS_ADRTTM AS KIKISOHUSADRTTM, ")
.append(" KK0341.KIKI_SOHUS_ADRRM AS KIKISOHUSADRRM, ")
.append(" KK0341.KIKI_SOHUS_NM AS KIKISOHUS, ")
.append(" KK0341.KIKI_SOHUS_TELNO AS KIKISOHUSTELNO, ")
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_NM ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_NM ")
.append(" WHEN '3' THEN YTK_SK_OFFC.YOTAKU_SAKI_OFFC_NM ")
.append(" END AS SOKONM, ")
.append( cdNoYmd(
CD00544,
"DECODE(HAISO.IDOMOTO_KRIPLACE_SKCD , '1' , HAISOBPIN.HAISO_MOTO_SKDN_CD , "
+ " '2', HAISOBPIN.IDOMOTO_KOCOMP_SLF_CD , "
+ " '3', HAISOBPIN.IDOMOTO_YTKSKOF_SLF_CD , "
+ " HAISOBPIN.HAISO_MOTO_SKDN_CD)",
COMMA.RIGHT,
"SHELFNM"))
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_TELNO ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_TLN ")
.append(" WHEN '3' THEN YTK_SK_OFFC.OFFC_TLN ")
.append(" END AS SOKOTELNO, ")
.append(" CASE HAISO.IDOMOTO_KRIPLACE_SKCD ")
.append(" WHEN '1' THEN SOKO.SOKO_FAX_NO ")
.append(" WHEN '2' THEN KOJI_COMP.KOJI_COMP_FAX_NO ")
.append(" WHEN '3' THEN YTK_SK_OFFC.OFFC_FAX_NO ")
.append(" END AS SOKOFAXNO, ")
.append(" MAKER.MAKER_NM_1 AS MAKERNAME, ")
.append(" SHKAFINERR.TAKNKIKI_MODEL_CD AS TAKNKIKIMODELCD, ")
.append(" '' AS HUZOKUHINMODELCD, ")
.append(" '' AS SETHINMODELCD, ")
.append(" TAKNKIKI_MODEL.TAKNKIKI_MODEL_NM AS GOODSNAME, ")
.append(" SHKAFINERR.KIKI_SEIZO_NO AS SEIZONO, ")
.append(" SHKAFINERR.CNT AS CNT, ")
.append(" HAISO.HAISO_DIV AS HAISODIV, ")
.append(" HAISO.HAISO_STAT AS HAISOSTAT, ")
.append(" HAISO.IDOMOTO_KRIPLACE_SKCD AS IDOMOTOKRIPLACESKCD ")
.append(" FROM ")
.append(" DK_T_HAISO HAISO ")
.append(" LEFT OUTER JOIN DK_T_HAISO_TG_BPIN HAISOBPIN ")
.append(" ON (HAISOBPIN.HAISO_NO = HAISO.HAISO_NO ")
.append(" AND HAISOBPIN.MK_FLG = '0') ")
.append( joinKKTKSvc("KK0341", "HAISOBPIN", "KKTK_SVC_KEI_NO", "KIKI_CHG_NO", "INNER"))
.append(" LEFT OUTER JOIN DK_T_SHKA_FIN_DTL SHKAFINDTL ")
.append(" ON (SHKAFINDTL.SHUKKA_LOT_NO = HAISOBPIN.SHUKKA_LOT_NO ")
.append(" AND SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ = HAISOBPIN.SHUKKA_LOT_KIKI_SEQ ")
.append(" AND SHKAFINDTL.MK_FLG = '0') ")
.append(" INNER JOIN DK_T_SHKA_FIN_ERR SHKAFINERR ")
.append(" ON (SHKAFINERR.SHUKKA_FIN_NO = SHKAFINDTL.SHUKKA_FIN_NO ")
.append(" AND SHKAFINERR.SHUKKA_LOT_NO = SHKAFINDTL.SHUKKA_LOT_NO ")
.append(" AND SHKAFINERR.SHUKKA_LOT_KIKI_SEQ = SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ ")
.append(" AND SHKAFINERR.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_TAKNKIKI_MODEL TAKNKIKI_MODEL ")
.append(" ON (TAKNKIKI_MODEL.TAKNKIKI_MODEL_CD = HAISOBPIN.TAKNKIKI_MODEL_CD ")
.append(" AND TAKNKIKI_MODEL.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_MAKER MAKER ")
.append(" ON (MAKER.MAKER_CD = TAKNKIKI_MODEL.MAKER_CD ")
.append(" AND MAKER.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN DK_M_SOKO SOKO ")
.append(" ON (SOKO.SOKO_CD = HAISO.HAISO_MOTO_SOKO_CD ")
.append(" AND SOKO.MK_FLG = '0') ")
.append(" LEFT OUTER JOIN ZM_M_KOJI_COMP KOJI_COMP ")
.append(" ON KOJI_COMP.KOJI_COMP_CD = HAISO.IDOMOTO_KOCOMP_CD ")
.append(" AND KOJI_COMP.MK_FLG = '0' ")
.append(" LEFT OUTER JOIN ZM_M_YTK_SK_OFFC YTK_SK_OFFC ")
.append(" ON YTK_SK_OFFC.YOTAKU_SAKI_OFFC_CD = HAISO.IDOMOTO_YTKSKOF_CD ")
.append(" AND YTK_SK_OFFC.MK_FLG = '0' ")
.append(" WHERE ")
.append(" HAISO.MK_FLG = '0' ")
.append(" AND HAISOBPIN.HAISO_TAIBPIN_NO = ? ")
.append(" ) TMP ")
.append( innerJoinToGen("KK_T_SVC_KEI", "KK0081", "TMP", "SVC_KEI_NO"))
.append(" LEFT OUTER JOIN ( SELECT PRCGRP_1.PRC_GRP_CD,PRCGRP_1.PRC_GRP_NM ")
.append(" FROM KK_M_PRC_GRP PRCGRP_1 ")
.append(" WHERE PRCGRP_1.GENE_ADD_DTM = (SELECT MAX(PRCGRP_2.GENE_ADD_DTM) ")
.append(" FROM KK_M_PRC_GRP PRCGRP_2 ")
.append(" WHERE PRCGRP_2.PRC_GRP_CD = PRCGRP_1.PRC_GRP_CD ")
.append(" AND PRCGRP_2.PRC_GRP_TSTAYMD <= ? ")
.append(" AND PRCGRP_2.MK_FLG = '0')) PRCGRP ")
.append(" ON (PRCGRP.PRC_GRP_CD = KK0081.PRC_GRP_CD) ");
sql_Buff.append(" LEFT OUTER JOIN DK_T_SHKA_KIHON_INFO SHKAKIHONINFO ")
.append(" ON (SHKAKIHONINFO.SHUKKA_LOT_NO = TMP.SHKALOTNO ")
.append(" AND SHKAKIHONINFO.SVC_KEI_NO = TMP.SVC_KEI_NO ")
.append(" AND SHKAKIHONINFO.MK_FLG = '0') ");
}
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
Object haisoTaibpinNo = inMsg.getObject(EDK0011B040CBSMsg.KEY_HAISO_TAIBPIN_NO);
Object unyoYMD = inMsg.getObject(EDK0011B040CBSMsg.KEY_UNYO_YMD);
int index = 1;
// パラメータの設定(ＫＥＹ＿運用日付を指定)
for (int i = 0; i < 2; i++)
{
CAANJDBCUtil.setParam(pstmt, index++, unyoYMD);
}
// パラメータの設定(ＫＥＹ＿配送対象物品番号を指定)
CAANJDBCUtil.setParam(pstmt, index++, haisoTaibpinNo);
for (int i = 0; i < 2; i++)
{
CAANJDBCUtil.setParam(pstmt, index++, unyoYMD);
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// EDK0011B040CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
EDK0011B040CBSMsg1List.SHKA_LOT_NO
, EDK0011B040CBSMsg1List.SHKA_LOT_KIKI_SEQ
, EDK0011B040CBSMsg1List.HAISO_UK_YMD
, EDK0011B040CBSMsg1List.SHKA_STAT
, EDK0011B040CBSMsg1List.DENPYO_NO
, EDK0011B040CBSMsg1List.SVC_KEI_NO
, EDK0011B040CBSMsg1List.SYSID
, EDK0011B040CBSMsg1List.PRC_GRP_KEI_SVC
, EDK0011B040CBSMsg1List.HOJIN_PRC_GRP_KEI_SVC
, EDK0011B040CBSMsg1List.KIKI_PCD
, EDK0011B040CBSMsg1List.HAISO_PCD
, EDK0011B040CBSMsg1List.KIKI_SOHUS_STATE_NM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_CITY_NM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_OAZTSU_NM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_AZCHO_NM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_BNCHIGO
, EDK0011B040CBSMsg1List.KIKI_SOHUS_ADRTTM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_ADRRM
, EDK0011B040CBSMsg1List.HISOS_STATE_NM
, EDK0011B040CBSMsg1List.HISOS_CITY_NM
, EDK0011B040CBSMsg1List.HISOS_OAZTSU_NM
, EDK0011B040CBSMsg1List.HISOS_AZCHO_NM
, EDK0011B040CBSMsg1List.HISOS_BNCHIGO
, EDK0011B040CBSMsg1List.HISOS_ADRTTM
, EDK0011B040CBSMsg1List.HISOS_ADRRM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_NM
, EDK0011B040CBSMsg1List.HISOS_NM
, EDK0011B040CBSMsg1List.KIKI_SOHUS_TELNO
, EDK0011B040CBSMsg1List.HISOS_TELNO
, EDK0011B040CBSMsg1List.TTM_NM
, EDK0011B040CBSMsg1List.SKDN_NM
, EDK0011B040CBSMsg1List.SOKO_TELNO
, EDK0011B040CBSMsg1List.SOKO_FAX_NO
, EDK0011B040CBSMsg1List.MAKER_NM
, EDK0011B040CBSMsg1List.KIKI_MODEL_NO
, EDK0011B040CBSMsg1List.FZK_MODEL_NO
, EDK0011B040CBSMsg1List.SET_MODEL_NO
, EDK0011B040CBSMsg1List.GOODS_NM
, EDK0011B040CBSMsg1List.SEISO_NO
, EDK0011B040CBSMsg1List.CNT
, EDK0011B040CBSMsg1List.HAISO_DIV
, EDK0011B040CBSMsg1List.HAISO_STAT
, EDK0011B040CBSMsg1List.IDOMOTO_KRIPLACE_SKCD
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0011B040CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("EDK0011B040CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(EDK0011B040CBSMsg.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(EDK0011B040CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}