/*******************************************************************************
*	 All Rights reserved,Copyright (c) K-Opticom 
********************************************************************************
*＜プログラム内容＞
*	システム名		：eo顧客基幹システム
*	モジュール名	：JSYejbEDK0011B030TPDA
*	ソースファイル名：JSYejbEDK0011B030TPDA.java
*	作成者			：EK909135
*	日付			：2011年09月14日
*＜機能概要＞
*	SQLFacility
*	出荷状況一覧照会テンプレートDBアクセス部品です。
*＜修正履歴＞
*	バージョン	修正日		修正者		修正内容
*	ｖ1.00.00
*	ｖ5.00.00	2013/10/22	FJ）塚田		OM-2013-0003293(性能改善)
*	ｖ5.00.01	2013/10/29	FJ）塚田		OM-2013-0003764(性能改善)
*	ｖ7.00.00	2013/11/07	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.EDK0011B030CBSMsg;
import eo.ejb.cbs.cbsmsg.EDK0011B030CBSMsg1List;
import eo.ejb.cbm.entity.DK0011ETMsg;
import eo.ejb.common.JDKModelCommon;
import eo.ejb.common.JDKModelCommon.COMMA;

import static eo.common.constant.JZM0171Constant.*;
import static eo.ejb.common.JDKModelCommon.*;
import static eo.common.util.JDKCommonUtil.*;

/**
*
*  テンプレートDBアクセス部品　EDK0011B030_SQLF<br>
*  入力された項目からSQL文を動的に組み立て、<br>
*  検索結果をCBSMsgにセットします。<br>
*/
public class JSYejbEDK0011B030TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
	
	/**
	 * 出荷状況リスト種別コード：出荷状況一覧
	 */
	private static final String SBT_CD_JOKYO_LIST = "001";
	
	/**
	 * 出荷状況リスト種別コード：出荷処理待ちリスト
	 */
	private static final String SBT_CD_WAIT_LIST = "002";
	
	/**
	 * 出荷状況リスト種別コード：出荷完了リスト
	 */
	private static final String SBT_CD_FIN_LIST = "003";
	
	/**
	 * 出荷状況リスト種別コード：出荷キャンセル依頼書
	 */
	private static final String SBT_CD_CANCEL = "004";
	
	/**
	 * 出荷状況リスト種別コード：出荷完了エラーリスト
	 */
	private static final String SBT_CD_ERR_LIST = "005";
	
	/**
	 * LIKE検索
	 */
	private static final String SEARCH_FOR_LIKE = "%";
	
	/**
	 *  サービス契約番号の予約適用年月日に当て込む日時
	 */
	private static final String RSV_DATE = new StringBuffer("(CASE HAISO.HAISO_STAT")
												    .append(" WHEN '001' THEN HAISO.HAISO_UK_YMD ")
												    .append(" WHEN '002' THEN HAISO.HAISO_SJI_YMD ")
												    .append(" WHEN '003' THEN HAISO.HAISO_SJI_YMD ")
												    .append(" WHEN '004' THEN HAISO.HAISO_SJI_YMD ")
												    .append(" WHEN '005' THEN HAISO.HAISO_UK_YMD ")
												    .append("END ) ").toString();

	private static final String RSV_DATE2 = new StringBuffer("(CASE TMP_SUB.HAISOSTAT")
												    .append(" WHEN '001' THEN TMP_SUB.IRAIYMD ")
												    .append(" WHEN '002' THEN TMP_SUB.HAISO_SJI_YMD ")
												    .append(" WHEN '003' THEN TMP_SUB.HAISO_SJI_YMD ")
												    .append(" WHEN '004' THEN TMP_SUB.HAISO_SJI_YMD ")
												    .append(" WHEN '005' THEN TMP_SUB.IRAIYMD ")
												    .append("END ) ").toString();
	
	/**
	 * コンストラクタ
	 */
	public JSYejbEDK0011B030TPDA()	{
	}
	
	/**
	*   EDK0011B030_SQLF<br>
	*   @param inMsg    入力値の格納されたCBSMsg <br>
	*   @return void
	*/
	public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
		
		// コネクション
		Connection con1 = null;
		
		// プリペアステートメント
		PreparedStatement pstmtForCount = null;
		PreparedStatement pstmtForFetch = null;
		
		// リザルトセット
		ResultSet rsltQueryForCount = null;
		ResultSet rsltQueryForFetch = null;
		
		/* ++++++++++ ｖ1.00.00 追加開始 ++++++++++ */
		/***************************************************** 
		 * サービスインターフェイス共通部の必須チェック
		 *****************************************************/
		
		// 最大検索件数、表示件数、表示ページ番号が未設定の場合
		// 例外をスローする
		if (inMsg.isNull(EDK0011B030CBSMsg.MAX_SEARCH_NUM)
				|| inMsg.isNull(EDK0011B030CBSMsg.DISPLAY_NUM)
				|| inMsg.isNull(EDK0011B030CBSMsg.DISPLAY_PAGE_NUM))
		{
			throw new IllegalArgumentException("サービスインターフェイス共通部の最大検索件数、表示件数、表示ページ番号は必須項目です。");
		}
		/* ++++++++++ ｖ1.00.00 追加終了 ++++++++++ */
		
		/******************** 
		 * SQL文の作成
		 ********************/

		try{
			
			//コネクション取得
			con1 = JSYejbConnection.getConnection(DK0011ETMsg.getTableName());

			String sql = makeSqlForCount(makeSql(inMsg, inContext, true));
			
			// prepareStatementにSQL文をセット
			pstmtForCount = con1.prepareStatement(sql);
			

			// パラメータの設定
			int i = setParameters(inMsg, pstmtForCount);
			
			// ログ出力(SQL文の出力)
			JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql);
			
			// ResultSetの取得
			rsltQueryForCount = pstmtForCount.executeQuery();
			
			// EDK0011B030CBSMsgの明細にセットする項目
			String[] msgKeyList0 = {
					EDK0011B030CBSMsg.TOTAL_SEARCH_NUM
			};
			
			rsltQueryForCount.next();
			mapMessage(rsltQueryForCount, msgKeyList0, inMsg);
			
			int iTotal = Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.TOTAL_SEARCH_NUM));
			int iDispNum = Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.DISPLAY_NUM));
			int iSearchDivDisp =  iTotal / iDispNum;
			int iRest =  iTotal % iDispNum;
			int iDispPageNum = 0;
			int iStartNum = 0;
			int iEndNum = 0;
			
			if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
					|| JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
			{
				// 総ページ数設定
				// 割り切れない場合は総ページ数を増やす
				if (0 != iRest)
				{
					iSearchDivDisp = iSearchDivDisp + 1;
				}
				inMsg.set(EDK0011B030CBSMsg.TOTAL_PAGE_NUM, Integer.toString(iSearchDivDisp));
				
				// 検索件数が0件の場合、エラーフラグに"1"を設定して処理終了
				if (0 == (Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.TOTAL_SEARCH_NUM))))
				{
					inMsg.set(EDK0011B030CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_ZERO);
					return;
				}
				// 検索件数が最大検索件数を超えた場合、エラーフラグに"2"を設定して処理終了
				if (Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.MAX_SEARCH_NUM))
						< (Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.TOTAL_SEARCH_NUM))))
				{
					inMsg.set(EDK0011B030CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_OVERMAX);
					return;
				}
				// 表示ページに該当するデータがない場合、エラーフラグに"3"を設定して処理終了
				iDispPageNum = Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.DISPLAY_PAGE_NUM));
				iStartNum = iDispNum * (iDispPageNum - 1) + 1;
				iEndNum = iDispNum * iDispPageNum;
				if (iSearchDivDisp < iDispPageNum)
				{
					inMsg.set(EDK0011B030CBSMsg.SEARCH_ERR_FLG, JPCModelConstant.SEARCH_ERR_FLG_NODATA);
					return;
				}
			}
			
			
			String sql2 = makeSql(inMsg, inContext);
			
			// データ取得SQL分の形成
			if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
					|| JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
			{
				pstmtForFetch = con1.prepareStatement(makeSqlForRealm(sql2, "COL", "CNT"));
			}
			else if (JPCModelConstant.FUNC_CD_3.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
					|| JPCModelConstant.FUNC_CD_4.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
			{
				pstmtForFetch = con1.prepareStatement(sql2);
			}

			//prepareStatementにSQL文をセット
			
			//ログ出力(SQL文の出力)
			JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql2);
			
			// パラメータの設定
			i = setParameters(inMsg, pstmtForFetch);
			
			if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
					|| JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
			{
				// パラメータの設定(画面表示データ開始レコード行を指定)
				CAANJDBCUtil.setParam(pstmtForFetch, i++, iStartNum);
				// パラメータの設定(画面表示データ終了レコード行を指定)
				CAANJDBCUtil.setParam(pstmtForFetch, i++, iEndNum);
			}
			/* ++++++++++ ｖ1.00.00 変更終了 ++++++++++ */
			
			// ResultSetの取得
			rsltQueryForFetch = pstmtForFetch.executeQuery();
			
			// EDK0011B030CBSMsgの明細にセットする項目
			String[] msgKeyList1 = {
					  EDK0011B030CBSMsg1List.HAISO_IRAI_YMD
					, EDK0011B030CBSMsg1List.SVKEI_NO
					, EDK0011B030CBSMsg1List.SYSID
					, EDK0011B030CBSMsg1List.PRC_GRP_KEI_SVC
					, EDK0011B030CBSMsg1List.HOJIN_PRC_GRP_KEI_SVC
					, EDK0011B030CBSMsg1List.KIKI_SOHUS_SHUKKAS
					, EDK0011B030CBSMsg1List.HISOS_SHUKKAS
					, EDK0011B030CBSMsg1List.TAKNKIKI_MODEL_NO
					, EDK0011B030CBSMsg1List.HUZOKUHIN_MODEL_NO
					, EDK0011B030CBSMsg1List.TAKNKIKI_SETHIN_MODEL_NO
					, EDK0011B030CBSMsg1List.SEIZO_NO
					, EDK0011B030CBSMsg1List.KIKI_KEI_DIV
					, EDK0011B030CBSMsg1List.CNT
					, EDK0011B030CBSMsg1List.SHKA_JOKYO
					, EDK0011B030CBSMsg1List.SHUKKAM
					, EDK0011B030CBSMsg1List.SHUKKAM_SHELF_NM
					, EDK0011B030CBSMsg1List.SHKA_LOT_NO
					, EDK0011B030CBSMsg1List.SHKA_FIN_YMD
					, EDK0011B030CBSMsg1List.DENPYO_NO
					, EDK0011B030CBSMsg1List.HAISO_TAIBPIN_NO
					, EDK0011B030CBSMsg1List.HAISO_DIV
					, EDK0011B030CBSMsg1List.HAISO_STAT
					, EDK0011B030CBSMsg1List.SV_STA_YMD
					, EDK0011B030CBSMsg1List.CHRG_STA_YMD
					, EDK0011B030CBSMsg1List.KIKI_KEI_YMD
					, EDK0011B030CBSMsg1List.STC_YMD
					, EDK0011B030CBSMsg1List.KOCOMP_CD
					, EDK0011B030CBSMsg1List.IDOMOTO_KRIPLACE_SKCD
			};
			
			// 明細に処理結果(ResultSet)をマッピング
			CAANMsg[] outMsg1 = mapMessageList(rsltQueryForFetch,msgKeyList1,"eo.ejb.cbs.cbsmsg.EDK0011B030CBSMsg1List",0);
			// inMsg(CBSMsg)に明細をセット
			inMsg.set("EDK0011B030CBSMsg1List", outMsg1);
			

		} catch(SQLException e) {
			inMsg.set(EDK0011B030CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
			throw new CAANRuntimeException(e);
		} finally {
			// 資源の解放
			try{
				SQLException e = JDKModelCommon.close(pstmtForCount, pstmtForFetch, rsltQueryForCount, rsltQueryForFetch);
				if(con1 != null){
					closeConnection(con1);
				}
				if (e != null)
				{
					throw e;
				}
			}catch(SQLException e){
				inMsg.set(EDK0011B030CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
				throw new CAANRuntimeException(e);
			}
		}
	}
	
	
	private static int setParameters (CAANMsg inMsg, PreparedStatement pstmt) throws SQLException
	{
		int i = 1;
		if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
				|| JPCModelConstant.FUNC_CD_3.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
		{
			// ＫＥＹ＿サービス契約番号が指定されていない場合
			if (inMsg.isNull(EDK0011B030CBSMsg.KEY_SVKEI_NO)) {
				for (int k = 0; k < 3; k++)
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SK_MT_CD, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SK_MT_CD, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SK_MT_CD, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SK_MT_CD, i, inMsg, pstmt);
				// パラメータの設定(ＫＥＹ＿出荷ロット番号を指定)
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO))
				{
					CAANJDBCUtil.setParam(pstmt, i++, inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO) + SEARCH_FOR_LIKE);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SEIZO_NO, i, inMsg, pstmt);

				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN) && "2".equals(inMsg.getObject(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN).toString()))
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_KEI_SVC, i, inMsg, pstmt);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SYSID, i, inMsg, pstmt);
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN) && "1".equals(inMsg.getObject(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN).toString()))
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_KEI_SVC, i, inMsg, pstmt);
				}

			}
			else{
				for (int k = 0; k < 3; k++)
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SVKEI_NO, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SVKEI_NO, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SK_MT_CD, i, inMsg, pstmt);
				// パラメータの設定(ＫＥＹ＿出荷ロット番号を指定)
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO))
				{
					CAANJDBCUtil.setParam(pstmt, i++, inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO) + SEARCH_FOR_LIKE);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SEIZO_NO, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SYSID, i, inMsg, pstmt);
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN) && "2".equals(inMsg.getObject(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN).toString()))
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_KEI_SVC, i, inMsg, pstmt);
				}
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN) && "1".equals(inMsg.getObject(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN).toString()))
				{
					i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_KEI_SVC, i, inMsg, pstmt);
				}

				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SVKEI_NO, i, inMsg, pstmt);
			}
		}
		else if (JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
				|| JPCModelConstant.FUNC_CD_4.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
		{
			for (int k = 0; k < 3; k++)
			{
				i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
			}
			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SVKEI_NO, i, inMsg, pstmt);
			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_SVKEI_NO, i, inMsg, pstmt);
			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, i, inMsg, pstmt);
			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO, i, inMsg, pstmt);

			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
			i = appendParamIfAnyInput(EDK0011B030CBSMsg.KEY_UNYO_YMD, i, inMsg, pstmt);
		}
		
		return i;
	}
	
	
	/**
	 * SQLを生成する
	 * @param inMsg インプットデータ
	 * @param inContext コンテキスト
	 * @param serchCntFlg 
	 * @return SQL
	 */
	private String makeSql(CAANMsg inMsg, AgentDispatchContext inContext)
	{
		return makeSql(inMsg, inContext, false);
	}
	
	
	
	/**
	 * SQLを生成する
	 * @param inMsg インプットデータ
	 * @param inContext コンテキスト
	 * @param serchCntFlg 
	 * @return SQL
	 */
	private String makeSql(CAANMsg inMsg, AgentDispatchContext inContext, boolean serchCntFlg)
	{
		
		StringBuffer sql_Buff = new StringBuffer();
		//運用日付
		StringBuffer unyoYmd = new StringBuffer();
		unyoYmd.append("'")
				.append(inMsg.getObject(EDK0011B030CBSMsg.KEY_UNYO_YMD).toString())
				.append("'");
		
		// SQL文_個別部
		if (JPCModelConstant.FUNC_CD_1.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
				|| JPCModelConstant.FUNC_CD_3.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
		{
			if (inMsg.isNull(EDK0011B030CBSMsg.KEY_SVKEI_NO)) {
				sql_Buff.append("    SELECT ")
						.append("        IRAIYMD AS COL_IRAIYMD, ")
						.append("        SVC_KEI_NO AS COL_SVCKEINO, ")
						.append("        SYSID AS COL_SYSID, ")
						.append("        ( SELECT PRCGRP.PRC_GRP_NM FROM ( SELECT PRCGRP_1.PRC_GRP_CD,PRCGRP_1.PRC_GRP_NM FROM KK_M_PRC_GRP PRCGRP_1 WHERE PRCGRP_1.GENE_ADD_DTM = (SELECT MAX(PRCGRP_2.GENE_ADD_DTM) FROM KK_M_PRC_GRP PRCGRP_2 WHERE PRCGRP_2.PRC_GRP_CD = PRCGRP_1.PRC_GRP_CD AND PRCGRP_2.PRC_GRP_TSTAYMD <= ")
						.append(           unyoYmd )
						.append("          AND PRCGRP_2.MK_FLG = '0')) PRCGRP WHERE PRCGRP.PRC_GRP_CD = TT.PRC_GRP_CD ) AS COL_PRCGRP, ")
						.append("        HOJINPRCGRP AS COL_HOJINPRCGRP, ")
						.append("        KIKISOHUS AS COL_KIKISOHUS, ")
						.append("        HISOS_NM AS COL_HISOS, ")
						.append("        TAKNKIKIMODELCD AS COL_TAKNKIKIMODELCD, ")
						.append("        HUZOKUHINMODELCD AS COL_HUZOKUHINMODELCD, ")
						.append("        SETHINMODELCD AS COL_SETHINMODELCD, ")
						.append("        SEIZONO AS COL_SEIZONO, ")
						.append("        KIKIKEIKBN AS COL_KIKIKEIKBN, ")
						.append("        CNT AS COL_CNT, ")
						.append("        STAT AS COL_STAT, ")
						.append("        SOKONM AS COL_SOKONM, ")
						.append("        SHELFNM AS COL_SHELFNM, ")
						.append("        SHUKKALOTNO AS COL_SHUKKALOTNO, ")
						.append("        SHUKKAYMD AS COL_SHUKKAYMD, ")
						.append("        DENPYONO AS COL_DENPYONO, ")
						.append("        HAISOTAIBPINNO AS COL_HAISOTAIBPINNO, ")
						.append("        HAISODIV AS COL_HAISODIV, ")
						.append("        HAISOSTAT AS COL_HAISOSTAT, ")
						.append("        KK0081_SVC_STA_YMD AS COL_SVC_STA_YMD, ")
						.append("        SVC_CHRG_STAYMD AS COL_SVC_CHRG_STAYMD, ")
						.append("        SVC_STA_YMD AS COL_KIKI_KEI_YMD, ")
						.append("        KIKI_STC_TEKKYO_YMD AS COL_KIKI_STC_TEKKYO_YMD, ")
						.append("        KOJI_COMP_CD AS COL_KOJI_COMP_CD, ")
						.append("        IDOMOTO_KRIPLACE_SKCD AS COL_IDOMOTO_KRIPLACE_SKCD ")
						.append("    FROM ")

						.append("        ( ")
						.append("            SELECT ")
						.append("                TMP.IRAIYMD, ")
						.append("                TMP.SVC_KEI_NO, ")
						.append("                ( SELECT KK0081.SYSID FROM KK_T_SVC_KEI KK0081 WHERE KK0081.SVC_KEI_NO = TMP.SVC_KEI_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_783.RSV_APLY_YMD || KK_T_SVC_KEI_783.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK_T_SVC_KEI_783 WHERE KK_T_SVC_KEI_783.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK_T_SVC_KEI_783.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_783.RSV_APLY_YMD <= ")
						.append(                   unyoYmd )
						.append("                  AND KK_T_SVC_KEI_783.MK_FLG = '0') ) AS SYSID, ")
						.append("                ( SELECT KK0081.PRC_GRP_CD FROM KK_T_SVC_KEI KK0081 WHERE KK0081.SVC_KEI_NO = TMP.SVC_KEI_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_783.RSV_APLY_YMD || KK_T_SVC_KEI_783.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK_T_SVC_KEI_783 WHERE KK_T_SVC_KEI_783.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK_T_SVC_KEI_783.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_783.RSV_APLY_YMD <= ")
						.append(                   unyoYmd )
						.append("                  AND KK_T_SVC_KEI_783.MK_FLG = '0') ) AS PRC_GRP_CD, ")
						
						.append("                TMP.HOJINPRCGRP, ")
						.append("                TMP.KIKISOHUS, ")
						.append("                ( SELECT SHKAKIHONINFO.HISOS_NM FROM DK_T_SHKA_KIHON_INFO SHKAKIHONINFO WHERE SHKAKIHONINFO.SHUKKA_LOT_NO = TMP.SHUKKALOTNO AND SHKAKIHONINFO.SVC_KEI_NO = TMP.SVC_KEI_NO AND SHKAKIHONINFO.MK_FLG = '0' ) AS HISOS_NM, ")
						.append("                TMP.TAKNKIKIMODELCD, ")
						.append("                TMP.HUZOKUHINMODELCD, ")
						.append("                TMP.SETHINMODELCD, ")
						.append("                TMP.SEIZONO, ")
						.append("                TMP.KIKIKEIKBN, ")
						.append("                TMP.CNT, ")
						.append("                TMP.STAT, ")
						.append("                TMP.SOKONM, ")
						.append("                TMP.SHELFNM, ")
						.append("                TMP.SHUKKALOTNO, ")
						.append("                TMP.SHUKKAYMD, ")
						.append("                TMP.DENPYONO, ")
						.append("                TMP.HAISOTAIBPINNO, ")
						.append("                TMP.HAISODIV, ")
						.append("                TMP.HAISOSTAT, ")
						.append("                ( SELECT KK0081.SVC_STA_YMD FROM KK_T_SVC_KEI KK0081 WHERE KK0081.SVC_KEI_NO = TMP.SVC_KEI_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_783.RSV_APLY_YMD || KK_T_SVC_KEI_783.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK_T_SVC_KEI_783 WHERE KK_T_SVC_KEI_783.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK_T_SVC_KEI_783.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_783.RSV_APLY_YMD <= ")
						.append(                   unyoYmd )
						.append("                  AND KK_T_SVC_KEI_783.MK_FLG = '0') ) AS KK0081_SVC_STA_YMD, ")
						.append("                ( SELECT KK0081.SVC_CHRG_STAYMD FROM KK_T_SVC_KEI KK0081 WHERE KK0081.SVC_KEI_NO = TMP.SVC_KEI_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_783.RSV_APLY_YMD || KK_T_SVC_KEI_783.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK_T_SVC_KEI_783 WHERE KK_T_SVC_KEI_783.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK_T_SVC_KEI_783.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_783.RSV_APLY_YMD <= ")
						.append(                   unyoYmd )
						.append("                  AND KK_T_SVC_KEI_783.MK_FLG = '0') ) AS SVC_CHRG_STAYMD, ")
						.append("                TMP.SVC_STA_YMD, ")
						.append("                TMP.KIKI_STC_TEKKYO_YMD, ")
						.append("                TMP.KOJI_COMP_CD, ")
						.append("                TMP.IDOMOTO_KRIPLACE_SKCD, ")
						.append("                ( SELECT KK0081.SVC_KEI_NO FROM KK_T_SVC_KEI KK0081 WHERE KK0081.SVC_KEI_NO = TMP.SVC_KEI_NO AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (SELECT MAX(KK_T_SVC_KEI_783.RSV_APLY_YMD || KK_T_SVC_KEI_783.GENE_ADD_DTM) FROM KK_T_SVC_KEI KK_T_SVC_KEI_783 WHERE KK_T_SVC_KEI_783.SVC_KEI_NO = KK0081.SVC_KEI_NO AND KK_T_SVC_KEI_783.RSV_APLY_CD = '2' AND KK_T_SVC_KEI_783.RSV_APLY_YMD <= ")
						.append(                   unyoYmd )
						.append("                  AND KK_T_SVC_KEI_783.MK_FLG = '0') ) AS KK0081_SVC_KEI_NO ")
						.append("            FROM ")

						.append("                ( ")
						.append("                    SELECT ")
						.append("                        IRAIYMD, ")
						.append(                         svcKeiNoFor2("TMP_SUB", "SVC_KEI_NO", "TMP_SUB.IRAIYMD", COMMA.RIGHT, RSV_DATE2) )
						.append("                        TMP_SUB.SYSID, ")
						.append("                        TMP_SUB.HOJINPRCGRP, ")
						.append("                        TMP_SUB.KIKISOHUS, ")
						.append("                        TMP_SUB.TAKNKIKIMODELCD, ")
						.append("                        TMP_SUB.HUZOKUHINMODELCD, ")
						.append("                        TMP_SUB.SETHINMODELCD, ")
						.append("                        TMP_SUB.SEIZONO, ")
						.append(                         cd(CD00212, "TMP_SUB.KKTK_SBT_CD", COMMA.RIGHT, "KIKIKEIKBN") )
						.append("                        TMP_SUB.CNT, ")
						.append("                        TMP_SUB.STAT, ")
						.append("                        TMP_SUB.SOKONM, ")
						.append("                        TMP_SUB.SHELFNM, ")
						.append("                        TMP_SUB.SHUKKALOTNO, ")
						.append("                        TMP_SUB.SHUKKAYMD, ")
						.append("                        TMP_SUB.DENPYONO, ")
						.append("                        TMP_SUB.HAISOTAIBPINNO, ")
						.append("                        TMP_SUB.HAISODIV, ")
						.append("                        TMP_SUB.HAISOSTAT, ")
						.append("                        TMP_SUB.SVC_STA_YMD, ")
						.append("                        TMP_SUB.KIKI_STC_TEKKYO_YMD, ")
						.append("                        TMP_SUB.KOJI_COMP_CD, ")
						.append("                        TMP_SUB.IDOMOTO_KRIPLACE_SKCD ")
						.append("                    FROM ")

						.append("                        ( ")
						.append("                            SELECT ")
						.append("                                HAISO.HAISO_UK_YMD AS IRAIYMD, ")
						.append("                                HAISO.HAISO_SJI_YMD, ")
						.append("                                ( SELECT KK0341.SVC_KEI_NO             FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS SVC_KEI_NO, ")
						.append("                                ( SELECT KK0341.OYA_KEI_SKBT_CD        FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS OYA_KEI_SKBT_CD, ")
						.append("                                ( SELECT KK0341.TAKNKIKI_SBT_CD        FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS TAKNKIKI_SBT_CD, ")
						.append("                                ( SELECT KK0341.SVC_KEI_KAISEN_UCWK_NO FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS SVC_KEI_KAISEN_UCWK_NO, ")
						.append("                                ( SELECT KK0341.KKTK_SBT_CD            FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS KKTK_SBT_CD, ")
						.append("                                ( SELECT KK0341.SYSID FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS SYSID, ")
						.append(                                 cd(CD00886, "HAISO.HOJIN_PRC_GRP_CD", COMMA.RIGHT, "HOJINPRCGRP") )
						.append("                                ( SELECT KK0341.KIKI_SOHUS_NM FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS KIKISOHUS, ")
						.append("                                HAISOBPIN.TAKNKIKI_MODEL_CD AS TAKNKIKIMODELCD, ")
						.append("                                HAISOBPIN.HUZOKUHIN_MODEL_CD AS HUZOKUHINMODELCD, ")
						.append("                                HAISOBPIN.TAKNKIKI_SETHIN_MODEL_CD AS SETHINMODELCD, ")
						.append("                                HAISOBPIN.KIKI_SEIZO_NO AS SEIZONO, ")
						.append("                                '1' AS CNT, ")
						.append(                                 cd(CD00009, "HAISO.HAISO_STAT", COMMA.RIGHT, "STAT") )
						.append("                                CASE HAISO.IDOMOTO_KRIPLACE_SKCD WHEN '1' THEN SOKO.SOKO_NM WHEN '2' THEN KOJI_COMP.KOJI_COMP_NM WHEN '3' THEN YTK_SK_OFFC.YOTAKU_SAKI_OFFC_NM 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("                                HAISOBPIN.SHUKKA_LOT_NO AS SHUKKALOTNO, ")
						.append("                                HAISO.SHUKKA_YMD AS SHUKKAYMD, ")
						.append("                                ( SELECT SHKAFINDTL.DENPYO_NO FROM DK_T_SHKA_FIN_DTL SHKAFINDTL WHERE HAISOBPIN.SHUKKA_LOT_NO = SHKAFINDTL.SHUKKA_LOT_NO AND HAISOBPIN.SHUKKA_LOT_KIKI_SEQ = SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ AND SHKAFINDTL.MK_FLG = '0' AND SHKAFINDTL.SHUKKA_FIN_NO = (SELECT MAX(SHKAFINDTL2.SHUKKA_FIN_NO) FROM DK_T_SHKA_FIN_DTL SHKAFINDTL2 WHERE SHKAFINDTL.SHUKKA_LOT_NO = SHKAFINDTL2.SHUKKA_LOT_NO AND SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ = SHKAFINDTL2.SHUKKA_LOT_KIKI_SEQ AND SHKAFINDTL2.MK_FLG = '0')) AS DENPYONO, ")
						.append("                                HAISOBPIN.HAISO_TAIBPIN_NO AS HAISOTAIBPINNO, ")
						.append("                                HAISO.HAISO_DIV AS HAISODIV, ")
						.append("                                HAISO.HAISO_STAT AS HAISOSTAT, ")
						.append("                                ( SELECT KK0341.SVC_STA_YMD FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS SVC_STA_YMD, ")
						.append("                                ( SELECT KOJIAK.KIKI_STC_TEKKYO_YMD FROM KU_T_KOJIAK KOJIAK WHERE KOJIAK.KOJIAK_NO = HAISOBPIN.KOJIAK_NO AND KOJIAK.MK_FLG = '0' ) AS KIKI_STC_TEKKYO_YMD, ")
						.append("                                HAISO.HISOS_KOCOMP_CD AS KOJI_COMP_CD, ")
						.append("                                HAISO.IDOMOTO_KRIPLACE_SKCD AS IDOMOTO_KRIPLACE_SKCD, ")
						.append("                                ( SELECT KK0341.KKTK_SVC_KEI_NO FROM KK_T_KKTK_SVC_KEI KK0341 WHERE KK0341.KKTK_SVC_KEI_NO = HAISOBPIN.KKTK_SVC_KEI_NO AND KK0341.GENE_ADD_DTM = (SELECT MAX(KK0341_425.GENE_ADD_DTM) FROM KK_T_KKTK_SVC_KEI KK0341_425 WHERE KK0341_425.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND KK0341_425.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO AND KK0341_425.RSV_APLY_CD IN ('1', '2') AND KK0341_425.MK_FLG = '0') AND KK0341.KIKI_CHG_NO = HAISOBPIN.KIKI_CHG_NO ) AS KKTK_SVC_KEI_NO ")
						.append("                            FROM ")

						.append("                               ( SELECT * FROM DK_T_HAISO HAISO WHERE IDOMOTO_KOCOMP_CD = ? OR IDOMOTO_YTKSKOF_CD = ? OR HAISO_MOTO_SOKO_CD = ? ) HAISO")
						.append("                                LEFT OUTER JOIN DK_T_HAISO_TG_BPIN HAISOBPIN ON (HAISOBPIN.HAISO_NO = HAISO.HAISO_NO AND HAISOBPIN.MK_FLG = '0') ")
						.append("                                LEFT OUTER JOIN DK_M_SOKO SOKO ON (SOKO.SOKO_CD = HAISO.HAISO_MOTO_SOKO_CD AND SOKO.MK_FLG = '0') ")
						.append("                                LEFT OUTER JOIN ZM_M_KOJI_COMP KOJI_COMP ON KOJI_COMP.KOJI_COMP_CD = HAISO.IDOMOTO_KOCOMP_CD AND KOJI_COMP.MK_FLG = '0' ")
						.append("                                LEFT OUTER JOIN ZM_M_YTK_SK_OFFC YTK_SK_OFFC ON YTK_SK_OFFC.YOTAKU_SAKI_OFFC_CD = HAISO.IDOMOTO_YTKSKOF_CD AND YTK_SK_OFFC.MK_FLG = '0' ")
						.append("                            WHERE ")
						.append("                                 HAISO.MK_FLG = '0' ");

						appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_WAIT_LIST, "HAISO.HAISO_STAT IN ('001', '002')", inMsg, sql_Buff);
						appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_FIN_LIST,  "HAISO.HAISO_STAT IN ('003', '004')", inMsg, sql_Buff);
						appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_CANCEL,    "HAISO.HAISO_STAT = '005'", inMsg, sql_Buff);

						appendSqlIfAnyInput(
								EDK0011B030CBSMsg.KEY_SK_MT_CD,
								"DECODE(HAISO.IDOMOTO_KRIPLACE_SKCD, '1', HAISO.HAISO_MOTO_SOKO_CD, "
										+ " '2', HAISO.IDOMOTO_KOCOMP_CD, "
										+ " '3', HAISO.IDOMOTO_YTKSKOF_CD, "
										+ " HAISO.HAISO_MOTO_SOKO_CD) = ?",
								inMsg,
								sql_Buff);
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO,   "HAISO.SHUKKA_LOT_NO LIKE ?", inMsg, sql_Buff);

						// 指定された帳票によってあてる対象を変える
						if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD))
						{
							String sbtCd = inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD).toString();
							if (SBT_CD_FIN_LIST.equals(sbtCd))
							{
								// 「出荷完了リスト」の場合は、配送完了年月日
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_FIN_YMD >= ?", inMsg, sql_Buff);
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_FIN_YMD <= ?", inMsg, sql_Buff);
							}
							else if(SBT_CD_CANCEL.equals(sbtCd))
							{
								// 「出荷キャンセル依頼書」の場合は、配送中止年月日
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_STP_YMD >= ?", inMsg, sql_Buff);
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_STP_YMD <= ?", inMsg, sql_Buff);
							}
							else
							{
								// 「出荷状況一覧」「出荷処理待ちリスト」の場合は、配送受付年月日
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_UK_YMD >= ?", inMsg, sql_Buff);
								appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_UK_YMD <= ?", inMsg, sql_Buff);
							}
						}

						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_SEIZO_NO,      "HAISOBPIN.KIKI_SEIZO_NO = ?", inMsg, sql_Buff);

						// パラメータの設定(ＫＥＹ＿契約サービスを指定)
						if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC))
						{
							// 法人料金グループコードの値が設定されている場合
							appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN, "2", "HAISO.HOJIN_PRC_GRP_CD = ?", inMsg, sql_Buff);
						}

						sql_Buff.append("                        ) TMP_SUB ");

						// パラメータの設定(ＫＥＹ＿ＳＹＳＩＤを指定)
						if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SYSID)) {
							sql_Buff.append("                   WHERE TMP_SUB.SYSID = ? ");
						}
						sql_Buff.append("                ) TMP ")
								.append("        ) TT ");
						
						if(serchCntFlg)
						{
							sql_Buff.append(" WHERE ROWNUM <= ")
									.append(Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.MAX_SEARCH_NUM)) + 1)
									.append("        AND TT.KK0081_SVC_KEI_NO IS NOT NULL ");
						}
						else
						{
							sql_Buff.append("        WHERE TT.KK0081_SVC_KEI_NO IS NOT NULL ");
						}
						
						if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC))
						{
							// 料金グループコードの値が設定されている場合
							appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN, "1", "TT.PRC_GRP_CD = ?", inMsg, sql_Buff);
						}

			} else {
				sql_Buff.append("   SELECT ")
						.append("      TMP.IRAIYMD AS COL_IRAIYMD, ")
						.append("      TMP.SVC_KEI_NO AS COL_SVCKEINO, ")
						.append("      KK0081.SYSID AS COL_SYSID, ")
						.append("      PRCGRP.PRC_GRP_NM AS COL_PRCGRP, ")
						.append("      TMP.HOJINPRCGRP AS COL_HOJINPRCGRP, ")
						.append("      TMP.KIKISOHUS AS COL_KIKISOHUS, ")
						.append("      SHKAKIHONINFO.HISOS_NM AS COL_HISOS, ")
						.append("      TMP.TAKNKIKIMODELCD AS COL_TAKNKIKIMODELCD, ")
						.append("      TMP.HUZOKUHINMODELCD AS COL_HUZOKUHINMODELCD, ")
						.append("      TMP.SETHINMODELCD AS COL_SETHINMODELCD, ")
						.append("      TMP.SEIZONO AS COL_SEIZONO, ")
						.append("      TMP.KIKIKEIKBN AS COL_KIKIKEIKBN, ")
						.append("      TMP.CNT AS COL_CNT, ")
						.append("      TMP.STAT AS COL_STAT, ")
						.append("      TMP.SOKONM AS COL_SOKONM, ")
						.append("      TMP.SHELFNM AS COL_SHELFNM, ")
						.append("      TMP.SHUKKALOTNO AS COL_SHUKKALOTNO, ")
						.append("      TMP.SHUKKAYMD AS COL_SHUKKAYMD, ")
						.append("      TMP.DENPYONO AS COL_DENPYONO, ")
						.append("      TMP.HAISOTAIBPINNO AS COL_HAISOTAIBPINNO, ")
						.append("      TMP.HAISODIV AS COL_HAISODIV, ")
						.append("      TMP.HAISOSTAT AS COL_HAISOSTAT, ")
						.append("      KK0081.SVC_STA_YMD AS COL_SVC_STA_YMD, ")
						.append("      KK0081.SVC_CHRG_STAYMD AS COL_SVC_CHRG_STAYMD, ")
						.append("      TMP.SVC_STA_YMD AS COL_KIKI_KEI_YMD, ")
						.append("      TMP.KIKI_STC_TEKKYO_YMD AS COL_KIKI_STC_TEKKYO_YMD, ")
						.append("      TMP.KOJI_COMP_CD AS COL_KOJI_COMP_CD, ")
						.append("      TMP.IDOMOTO_KRIPLACE_SKCD AS COL_IDOMOTO_KRIPLACE_SKCD ")
						.append("   FROM ")
						.append("      ( ")
						.append("             SELECT ")
						.append("                HAISO.HAISO_UK_YMD AS IRAIYMD, ")
						.append(                 svcKeiNoFor2("KK0341", "SVC_KEI_NO", "HAISO.HAISO_UK_YMD", COMMA.RIGHT, RSV_DATE))
						.append("                KK0341.SYSID AS SYSID, ")
						.append(                 cd(CD00886, "HAISO.HOJIN_PRC_GRP_CD", COMMA.RIGHT, "HOJINPRCGRP"))
						.append("                KK0341.KIKI_SOHUS_NM AS KIKISOHUS, ")
						.append("                HAISOBPIN.TAKNKIKI_MODEL_CD AS TAKNKIKIMODELCD, ")
						.append("                HAISOBPIN.HUZOKUHIN_MODEL_CD AS HUZOKUHINMODELCD, ")
						.append("                HAISOBPIN.TAKNKIKI_SETHIN_MODEL_CD AS SETHINMODELCD, ")
						.append("                HAISOBPIN.KIKI_SEIZO_NO AS SEIZONO, ")
						.append(                 cd(CD00212, "KK0341.KKTK_SBT_CD", COMMA.RIGHT, "KIKIKEIKBN"))
						.append("                '1' AS CNT, ")
						.append(                 cd(CD00009, "HAISO.HAISO_STAT", COMMA.RIGHT, "STAT"))
						.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("                HAISOBPIN.SHUKKA_LOT_NO AS SHUKKALOTNO, ")
						.append("                HAISO.SHUKKA_YMD AS SHUKKAYMD, ")
						.append("                (SELECT SHKAFINDTL.DENPYO_NO ")
						.append("                 FROM   DK_T_SHKA_FIN_DTL SHKAFINDTL ")
						.append("                 WHERE  HAISOBPIN.SHUKKA_LOT_NO = SHKAFINDTL.SHUKKA_LOT_NO ")
						.append("                 AND    HAISOBPIN.SHUKKA_LOT_KIKI_SEQ = SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ ")
						.append("                 AND    SHKAFINDTL.MK_FLG = '0' ")
						.append("                 AND    SHKAFINDTL.SHUKKA_FIN_NO = (SELECT MAX(SHKAFINDTL2.SHUKKA_FIN_NO)  ")
						.append("                                                    FROM   DK_T_SHKA_FIN_DTL SHKAFINDTL2  ")
						.append("                                                    WHERE  SHKAFINDTL.SHUKKA_LOT_NO = SHKAFINDTL2.SHUKKA_LOT_NO ")
						.append("                                                    AND    SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ = SHKAFINDTL2.SHUKKA_LOT_KIKI_SEQ ")
						.append("                                                    AND    SHKAFINDTL2.MK_FLG = '0')) AS DENPYONO, ")
						.append("                HAISOBPIN.HAISO_TAIBPIN_NO AS HAISOTAIBPINNO, ")
						.append("                HAISO.HAISO_DIV AS HAISODIV, ")
						.append("                HAISO.HAISO_STAT AS HAISOSTAT, ")
						.append("                KK0341.SVC_STA_YMD AS SVC_STA_YMD, ")
						.append("                KOJIAK.KIKI_STC_TEKKYO_YMD AS KIKI_STC_TEKKYO_YMD, ")
						.append("                HAISO.HISOS_KOCOMP_CD AS KOJI_COMP_CD, ")
						.append("                HAISO.IDOMOTO_KRIPLACE_SKCD AS IDOMOTO_KRIPLACE_SKCD ")
						.append("             FROM ")
						.append("            (select dk0021.haiso_no ")
						.append("             from (  ")
						.append("                   SELECT KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO ")
						.append("                     FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
						.append("                     INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ")
						.append("                       ON KK0341.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
						.append("                      AND KK0341.MK_FLG = '0' ")
						.append("                    WHERE KK0241.SVC_KEI_NO = ? ")
						.append("                      AND KK0241.MK_FLG = '0' ")
						.append("                    UNION ALL ")
						.append("                     SELECT KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO ")
						.append("                      FROM KK_T_KKTK_SVC_KEI KK0341 ")
						.append("                     WHERE KK0341.SVC_KEI_NO = ?")
						.append("                       AND KK0341.OYA_KEI_SKBT_CD <> '02' ")
						.append("                       AND KK0341.MK_FLG = '0' ")
						.append("                   ) kk0341_targets")
						.append("             inner join DK_T_HAISO_TG_BPIN dk0021 ")
						.append("                on kk0341_targets.kktk_svc_kei_no = dk0021.kktk_svc_kei_NO ")
						.append("               and kk0341_targets.kiki_chg_no = dk0021.kiki_chg_no ")
						.append("               and dk0021.mk_flg = '0' ")
						.append("              group by dk0021.haiso_no ")
						.append("            ) svc_kei_all_haiso ")
						.append("            INNER JOIN  DK_T_HAISO HAISO  ")
						.append("            ON HAISO.HAISO_NO = svc_kei_all_haiso.haiso_no ")
						.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_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("                LEFT OUTER JOIN KU_T_KOJIAK KOJIAK ")
						.append("                     ON (KOJIAK.KOJIAK_NO = HAISOBPIN.KOJIAK_NO ")
						.append("                     AND KOJIAK.MK_FLG = '0') ")
						.append("             WHERE ")
						.append("                HAISO.MK_FLG = '0' ");

				appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_WAIT_LIST, "HAISO.HAISO_STAT IN ('001', '002')", inMsg, sql_Buff);
				appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_FIN_LIST,  "HAISO.HAISO_STAT IN ('003', '004')", inMsg, sql_Buff);
				appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD, SBT_CD_CANCEL,    "HAISO.HAISO_STAT = '005'", inMsg, sql_Buff);

				appendSqlIfAnyInput(
						EDK0011B030CBSMsg.KEY_SK_MT_CD,
						"DECODE(HAISO.IDOMOTO_KRIPLACE_SKCD, '1', HAISO.HAISO_MOTO_SOKO_CD, "
								+ " '2', HAISO.IDOMOTO_KOCOMP_CD, "
								+ " '3', HAISO.IDOMOTO_YTKSKOF_CD, "
								+ " HAISO.HAISO_MOTO_SOKO_CD) = ?",
						inMsg,
						sql_Buff);
				appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_SHKA_LOT_NO,   "HAISO.SHUKKA_LOT_NO LIKE ?", inMsg, sql_Buff);

				// 指定された帳票によってあてる対象を変える
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD))
				{
					String sbtCd = inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD).toString();
					if (SBT_CD_FIN_LIST.equals(sbtCd))
					{
						// 「出荷完了リスト」の場合は、配送完了年月日
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_FIN_YMD >= ?", inMsg, sql_Buff);
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_FIN_YMD <= ?", inMsg, sql_Buff);
					}
					else if(SBT_CD_CANCEL.equals(sbtCd))
					{
						// 「出荷キャンセル依頼書」の場合は、配送中止年月日
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_STP_YMD >= ?", inMsg, sql_Buff);
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_STP_YMD <= ?", inMsg, sql_Buff);
					}
					else
					{
						// 「出荷状況一覧」「出荷処理待ちリスト」の場合は、配送受付年月日
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_UK_YMD >= ?", inMsg, sql_Buff);
						appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO,   "HAISO.HAISO_UK_YMD <= ?", inMsg, sql_Buff);
					}
				}

				appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_SEIZO_NO,      "HAISOBPIN.KIKI_SEIZO_NO = ?", inMsg, sql_Buff);
				appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_SYSID,         "KK0341.SYSID = ?", inMsg, sql_Buff);

				// パラメータの設定(ＫＥＹ＿契約サービスを指定)
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC))
				{
					// 法人料金グループコードの値が設定されている場合
					appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN, "2", "HAISO.HOJIN_PRC_GRP_CD = ?", inMsg, sql_Buff);
				}

				sql_Buff.append("     ) TMP  ")
				.append(         		 innerJoinToGen("KK_T_SVC_KEI", "KK0081", "TMP", "SVC_KEI_NO"));
				if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_KEI_SVC))
				{
					// 料金グループコードの値が設定されている場合
					appendSqlIfInputEq(EDK0011B030CBSMsg.KEY_KEI_SVC_KBN, "1", "KK0081.PRC_GRP_CD = ?", inMsg, sql_Buff);
				}
				sql_Buff.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.SHUKKALOTNO ")
				.append("                     AND SHKAKIHONINFO.SVC_KEI_NO = TMP.SVC_KEI_NO ")
				.append("                     AND SHKAKIHONINFO.MK_FLG = '0') ");

				sql_Buff.append("                WHERE TMP.SVC_KEI_NO = ? ");
			}

			// 並び順
			if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD))
			{
				String sbtCd = inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD).toString();
				
				if(!serchCntFlg)
				{
					// 出荷状況一覧、出荷処理待ちリスト、出荷キャンセル依頼書が選択された場合
					if (SBT_CD_JOKYO_LIST.equals(sbtCd)
							|| SBT_CD_WAIT_LIST.equals(sbtCd)
							|| SBT_CD_CANCEL.equals(sbtCd))
					{
						sql_Buff.append(" ORDER BY ")
								.append("   IRAIYMD DESC, ")
								.append("   COL_SVCKEINO ASC, ")
								.append("   NVL(TAKNKIKIMODELCD, NVL(HUZOKUHINMODELCD, SETHINMODELCD)) ASC ");
					}
					// 出荷完了リストが選択された場合
					else if (SBT_CD_FIN_LIST.equals(sbtCd))
					{
						sql_Buff.append(" ORDER BY ")
								.append("   SHUKKAYMD DESC, ")
								.append("   COL_SVCKEINO ASC, ")
								.append("   NVL(TAKNKIKIMODELCD, NVL(HUZOKUHINMODELCD, SETHINMODELCD)) ASC ");
					}
				}
			}
		}

		if (JPCModelConstant.FUNC_CD_2.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString())
				|| JPCModelConstant.FUNC_CD_4.equals(inMsg.getObject(EDK0011B030CBSMsg.FUNC_CODE).toString()))
		{
			sql_Buff.append("   SELECT ")
					.append("      TMP.IRAIYMD AS COL_IRAIYMD, ")
					.append("      TMP.SVC_KEI_NO AS COL_SVCKEINO, ")
					.append("      TMP.SYSID AS COL_SYSID, ")
					.append("      PRCGRP.PRC_GRP_NM AS COL_PRCGRP, ")
					.append("      TMP.HOJINPRCGRP AS COL_HOJINPRCGRP, ")
					.append("      TMP.KIKISOHUS AS COL_KIKISOHUS, ")
					.append("      SHKAKIHONINFO.HISOS_NM AS COL_HISOS, ")
					.append("      TMP.TAKNKIKIMODELCD AS COL_TAKNKIKIMODELCD, ")
					.append("      TMP.HUZOKUHINMODELCD AS COL_HUZOKUHINMODELCD, ")
					.append("      TMP.SETHINMODELCD AS COL_SETHINMODELCD, ")
					.append("      TMP.SEIZONO AS COL_SEIZONO, ")
					.append("      TMP.KIKIKEIKBN AS COL_KIKIKEIKBN, ")
					.append("      TMP.CNT AS COL_CNT, ")
					.append("      TMP.STAT AS COL_STAT, ")
					.append("      TMP.SOKONM AS COL_SOKONM, ")
					.append("      TMP.SHELFNM AS COL_SHELFNM, ")
					.append("      TMP.SHUKKALOTNO AS COL_SHUKKALOTNO, ")
					.append("      TMP.SHUKKAYMD AS COL_SHUKKAYMD, ")
					.append("      TMP.DENPYONO AS COL_DENPYONO, ")
					.append("      TMP.HAISOTAIBPINNO AS COL_HAISOTAIBPINNO, ")
					.append("      TMP.HAISODIV AS COL_HAISODIV, ")
					.append("      TMP.HAISOSTAT AS COL_HAISOSTAT, ")
					.append("      KK0081.SVC_STA_YMD AS COL_SVC_STA_YMD, ")
					.append("      KK0081.SVC_CHRG_STAYMD AS COL_SVC_CHRG_STAYMD, ")
					.append("      TMP.SVC_STA_YMD AS COL_KIKI_KEI_YMD, ")
					.append("      TMP.KIKI_STC_TEKKYO_YMD AS COL_KIKI_STC_TEKKYO_YMD, ")
					.append("      TMP.KOJI_COMP_CD AS COL_KOJI_COMP_CD, ")
					.append("      TMP.IDOMOTO_KRIPLACE_SKCD AS COL_IDOMOTO_KRIPLACE_SKCD ")
					.append("   FROM ")
					.append("      ( SELECT ")
					.append("              HAISO.HAISO_UK_YMD AS IRAIYMD, ")
					.append(               svcKeiNoFor2("KK0341", "SVC_KEI_NO", "HAISO.HAISO_UK_YMD", COMMA.RIGHT, RSV_DATE))
					.append("              KK0341.SYSID AS SYSID, ")
					.append(               cd(CD00886, "HAISO.HOJIN_PRC_GRP_CD", COMMA.RIGHT, "HOJINPRCGRP"))
					.append("              KK0341.KIKI_SOHUS_NM AS KIKISOHUS, ")
					.append("              SHKAFINERR.TAKNKIKI_MODEL_CD AS TAKNKIKIMODELCD, ")
					.append("              '' AS HUZOKUHINMODELCD, ")
					.append("              '' AS SETHINMODELCD, ")
					.append("              SHKAFINERR.KIKI_SEIZO_NO AS SEIZONO, ")
					.append(               cd(CD00212, "KK0341.KKTK_SBT_CD", COMMA.RIGHT, "KIKIKEIKBN"))
					.append("              SHKAFINERR.CNT AS CNT, ")
					.append(               cd(CD00009, "HAISO.HAISO_STAT", COMMA.RIGHT, "STAT"))
					.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("              SHKAFINERR.SHUKKA_LOT_NO AS SHUKKALOTNO, ")
					.append("              SHKAFINDTL.SHUKKA_FIN_YMD AS SHUKKAYMD, ")
					.append("              SHKAFINDTL.DENPYO_NO AS DENPYONO, ")
					.append("              HAISOBPIN.HAISO_TAIBPIN_NO AS HAISOTAIBPINNO, ")
					.append("              HAISO.HAISO_DIV AS HAISODIV, ")
					.append("              HAISO.HAISO_STAT AS HAISOSTAT, ")
					.append("              KK0341.SVC_STA_YMD AS SVC_STA_YMD, ")
					.append("              KOJIAK.KIKI_STC_TEKKYO_YMD AS KIKI_STC_TEKKYO_YMD, ")
					.append("              HAISO.HISOS_KOCOMP_CD AS KOJI_COMP_CD, ")
					.append("              HAISO.IDOMOTO_KRIPLACE_SKCD AS IDOMOTO_KRIPLACE_SKCD ")
					.append("           FROM ");
			if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SVKEI_NO)) {
			sql_Buff.append("    	    (select dk0021.haiso_no ")
					.append("    	       from (  ")
					.append("                   SELECT KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO ")
					.append("                     FROM KK_T_KAISEN_TG_SVKEI KK0241 ")
					.append("                     INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ")
					.append("                       ON KK0341.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO ")
					.append("                      AND KK0341.MK_FLG = '0' ")
					.append("                    WHERE KK0241.SVC_KEI_NO = ? ")
					.append("                      AND KK0241.MK_FLG = '0' ")
					.append("                   UNION ALL ")
					.append("                     SELECT KK0341.KKTK_SVC_KEI_NO, KK0341.KIKI_CHG_NO ")
					.append("                      FROM KK_T_KKTK_SVC_KEI KK0341 ")
					.append("                     WHERE KK0341.SVC_KEI_NO = ?")
					.append("                       AND KK0341.OYA_KEI_SKBT_CD <> '02' ")
					.append("                       AND KK0341.MK_FLG = '0' ")
					.append("    	   		   ) kk0341_targets")
					.append("    	      inner join DK_T_HAISO_TG_BPIN dk0021 ")
					.append("    	         on kk0341_targets.kktk_svc_kei_no = dk0021.kktk_svc_kei_NO ")
					.append("    	        and kk0341_targets.kiki_chg_no = dk0021.kiki_chg_no ")
					.append("    	        and dk0021.mk_flg = '0' ")
					.append("    	      group by dk0021.haiso_no ")
					.append("    	    ) svc_kei_all_haiso ")
					.append("            INNER JOIN  DK_T_HAISO HAISO  ")
					.append("            ON HAISO.HAISO_NO = svc_kei_all_haiso.haiso_no ");
			}
			else
			{
			sql_Buff.append("            DK_T_HAISO HAISO ");
			}
			sql_Buff.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 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("              LEFT OUTER JOIN KU_T_KOJIAK KOJIAK ")
					.append("               ON (KOJIAK.KOJIAK_NO = HAISOBPIN.KOJIAK_NO ")
					.append("                   AND KOJIAK.MK_FLG = '0') ")
					.append("           WHERE ")
					.append("              HAISO.MK_FLG = '0' ")
					.append("             AND  SHKAFINDTL.SHUKKA_FIN_NO = (SELECT MAX(SHKAFINERR2.SHUKKA_FIN_NO) ")
					.append("                                              FROM   DK_T_SHKA_FIN_ERR SHKAFINERR2 ")
					.append("                                              WHERE  SHKAFINDTL.SHUKKA_LOT_NO = SHKAFINERR2.SHUKKA_LOT_NO ")
					.append("                                              AND    SHKAFINDTL.SHUKKA_LOT_KIKI_SEQ = SHKAFINERR2.SHUKKA_LOT_KIKI_SEQ ")
					.append("                                              AND    SHKAFINERR2.MK_FLG = '0') ");

			appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_FROM, "HAISO.HAISO_FIN_YMD >= ?", inMsg, sql_Buff);
			appendSqlIfAnyInput(EDK0011B030CBSMsg.KEY_TRGT_YMD_TO, "HAISO.HAISO_FIN_YMD <= ?", inMsg, sql_Buff);

			sql_Buff.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) ")
			
					.append("              LEFT OUTER JOIN DK_T_SHKA_KIHON_INFO SHKAKIHONINFO ")
					.append("                 ON (SHKAKIHONINFO.SHUKKA_LOT_NO = TMP.SHUKKALOTNO ")
					.append("                   AND SHKAKIHONINFO.SVC_KEI_NO = TMP.SVC_KEI_NO ")
					.append("                   AND SHKAKIHONINFO.MK_FLG = '0') ");
			
			if(serchCntFlg)
			{
				String rownumCnt = String.valueOf(Integer.parseInt(inMsg.getString(EDK0011B030CBSMsg.MAX_SEARCH_NUM)) + 1);
				sql_Buff.append("WHERE ROWNUM <= " + rownumCnt);
			}

			if (!inMsg.isNull(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD))
			{
				String sbtCd = inMsg.getObject(EDK0011B030CBSMsg.KEY_SHKA_JOKYO_LIST_SBT_CD).toString();
				// 出荷エラーリストが選択された場合
				if (SBT_CD_ERR_LIST.equals(sbtCd))
				{
					sql_Buff.append(" ORDER BY ")
							.append("   SHUKKAYMD DESC, ")
							.append("   SHUKKALOTNO ASC ");
				}
			}
		}
		
		return sql_Buff.toString();
	}
	
}