/**
 * @(#)file    SSRDetailBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.13
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.process.process.biz;

import java.sql.Connection;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class SSRDetailBiz extends Biz {

	public SSRDetailBiz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		ValueObject vo_file = new ValueObject();
		
		if( storage.compareEvent("REG_SSR_DETAIL") ){
//			ValueObject vo = getShippingId();	//2021.12.01 협력사 상관없이 SSR 작성 가능하도록 수정
			ValueObject vo = new ValueObject();
			vo.set("VENDOR_CD", "");        		
			vo.set("VENDOR_NM", "");	
			vo.set("status", "1");
			vo.set("shipping_no", "");
			
			vo.set("ship_no", getShipNo(params)); 
			storage.addValueObject("ssrDetail", vo);
			RowSet purchaseFileRow = getfileListRowSet("SSR_DETAIL_", "");
	        storage.addRowSet("purchaseFileRow", purchaseFileRow);	  			
		} 
		else if ( storage.compareEvent("GET_SSR_DETAIL") ) {
			System.out.println("****************GET_SSR_DETAIL");
			//1.SSR내역
			ValueObject vo = getSSRDetail(params);
	        storage.addValueObject("ssrDetail", vo);
	        
	        //2.첨부파일
	        RowSet rowSet = FileManager.getFileList("SSR_DETAIL_" + params.get("shipping_id"), "");
	        RowSet purchaseFileRow = getfileListRowSet("SSR_DETAIL_"+params.get("shipping_id"), "");
	        storage.addRowSet("purchaseFileRow", purchaseFileRow);	        
	    	
	        String str="";
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        }	        
	        vo_file.set("SSR_DETAIL", str);
		}else if( storage.compareEvent("GET_SSR_DETAIL_SHEET") ) {
			params.set("sheet1", getSsrDetailSheet(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if( storage.compareEvent("GET_SSR_INV_SHEET") ) {
			params.set("sheet2", getSsrInvSheet(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if ( storage.compareEvent("GET_SHIPPING_TARGET1") ) {
			params.set("sheet1", getShippingTarget1(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} 
		else if ( storage.compareEvent("GET_SHIPPING_TARGET2") ) {
			params.set("sheet2", getShippingTarget2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} 
		storage.addValueObject("SSR_DETAIL", vo_file);	
	}

	
	private ValueObject getSSRDetail(ValueObject params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT                                         \n");
        	sql.append("       a.shipping_id       	                    \n");
        	sql.append("     , a.dept_cd       	                        \n");
        	sql.append("     , GET_DEPT_NM(a.dept_cd) dept_nm       	\n");
        	sql.append("     , A.SHIP_NO          	                    \n");
        	sql.append("     , a.ship_mode        	                    \n");
        	sql.append("     , a.esti_departure         	            \n");
        	sql.append("     , a.departure         	                    \n");
        	sql.append("     , a.esti_arrival        	                \n");
        	sql.append("     , a.arrival          	                    \n");
        	sql.append("     , A.STATUS  	                            \n");
        	sql.append("     , F_GET_CODE_NAME('SSR_STATUS', A.STATUS) STATUS_NM  \n");
        	sql.append("     , a.pol         	                        \n");
        	sql.append("     , a.pod           	                        \n");
        	sql.append("     , nvl(a.cntr_20dc,0) twenty_dc       	    \n");
        	sql.append("     , nvl(a.cntr_20dt,0) twenty_dt       	    \n");
        	sql.append("     , nvl(a.cntr_20fr,0) twenty_fr       	    \n");
        	sql.append("     , nvl(a.cntr_40dc,0) fourty_dc       	    \n");
        	sql.append("     , nvl(a.cntr_40dt,0) fourty_dt			    \n");
        	sql.append("     , nvl(a.cntr_40fr,0) fourty_fr        	    \n");
        	sql.append("     , nvl(a.cntr_45ft,0) fourty_fft        	\n");
        	sql.append("     , nvl(a.cntr_20dc,0) + nvl(a.cntr_20dt,0) + nvl(a.cntr_20fr,0) + nvl(a.cntr_40dc,0) + nvl(a.cntr_40dt,0) + nvl(a.cntr_40fr,0) + nvl(a.cntr_45ft,0)  total       	\n");
        	sql.append("     , a.package_qty         	                \n");
        	sql.append("     , a.pkg_wgt         	                    \n");
        	sql.append("     , a.vessel_name                            \n");
        	sql.append("     , a.bl_no           	                    \n");
        	sql.append("     , a.clear_custom                           \n");
        	sql.append("     , a.onsite         	                    \n");
        	sql.append("     , a.remarks       	                        \n");
        	sql.append("     , a.origin       	                        \n");
        	sql.append("     , A.CONFIRM_DATE	                        \n");
        	sql.append("     , a.pkg_cbm         	                    \n");
        	sql.append("     , A.VENDOR_CD         	                    \n");
        	sql.append("     , GET_CORP_NM_EBID(B.CORP_ID) VENDOR_NM    \n");
        	sql.append("     , A.DEPTEMPNO   	                        \n");
        	sql.append("     , GET_USER_NM(A.DEPTEMPNO) DEPT_EMP_NAME	\n");
        	sql.append("     , A.DEPTEMP_EMAIL AS  			            \n");
        	sql.append("     , A.DEPTEMP_HP	AS  	                    \n");
        	sql.append("     , A.PUR_EMP_NO		                        \n");
        	sql.append("     , GET_USER_NM(A.PUR_EMP_NO) PUR_EMP_NAME	\n");
        	sql.append("     , A.PUREMP_EMAIL AS  		                \n");
        	sql.append("     , A.PUREMP_HP AS  		                    \n");
        	sql.append("     , A.DATA1		                            \n");
        	sql.append("     , A.DATA2		                            \n");
        	sql.append("     , A.DATA3		                            \n");
        	sql.append("     , A.DATA4		                            \n");
        	sql.append("     , A.DATA5		                            \n");
        	sql.append("     , a.shipping_seq                           \n");
        	sql.append("     , B.corp_id       	                        \n");
        	sql.append(" FROM PSR_SHIPPING A						    \n");
        	sql.append("    , SUPP_INFO B	                            \n");
        	sql.append(" WHERE A.VENDOR_CD = B.CORP_ID(+) 	            \n");
        	sql.append(" AND A.SHIPPING_ID = '"+params.get("shipping_id")+"'	\n");

        	
        	search.setStatement(sql);
        	//System.out.println(sql.toString());
            rowSet = search.execute();
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            return vo;
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
	}
	
	public RowSet getSsrDetailSheet(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	SELECT                                                              	        \n");
			sql.append("		  A.MATERIAL_CD                                                    	        \n");
			sql.append("		, A.PARTIAL_YN                                                  	        \n");
			sql.append("		, F_GET_MATERIAL_NAME(A.MATERIAL_CD) MATERIAL_NAME               	        \n");
			sql.append("		, F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) MATERIAL_SPEC              	        \n");
			sql.append("		, F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) ITEM_UNIT                		        \n");
			sql.append("		, C.ORD_DOC_NO                                                   	        \n");
			sql.append("		, C.CST_DOC_NAME                                                 	        \n");
			sql.append("		, NVL(a.exc_pri, 0) shp_unit_cost                                           \n"); 
			sql.append("		, NVL(a.exc_amt,0) shp_amt                                     	            \n");
			sql.append("		, NVL(a.ship_qty,0) ship_qty                                     	        \n");
			sql.append("		, A.SHIPPING_DETAIL_ID                                           	        \n");
			sql.append("		, A.PUR_CONT_ID                                                  	        \n");
			sql.append("		, A.SHIPPING_ID                                                  	        \n");
			sql.append("		, C.CURRENCY_GB                                                  	        \n");
			sql.append("		, NVL(B.ITEM_QTY,0) ITEM_QTY		                                        \n");
			sql.append("		, GET_CORP_NM_EBID(C.CORP_ID) AS VENDOR_NM		                            \n");
			sql.append("		, CASE WHEN C.PAY_GROUP = '73' THEN 'T/T' 	                                \n");
			sql.append("		 	   WHEN C.PAY_GROUP LIKE '4%' THEN '기성'                                \n");
			sql.append("		 	   WHEN C.PAY_GROUP LIKE '7%' THEN 'L/C'  	                            \n");
			sql.append("			   ELSE C.PAY_GROUP                                                     \n");
			sql.append("	 	  END PAYMENT_NM                                                            \n");
			sql.append("		, F_GET_CODE_NAME('PUR_LEAD_GB', C.LEAD_COND_GB)       	AS CONDITION_NM	    \n");
			sql.append("	FROM PSR_SHIPPING_DETAIL A, CONT_PUR_ITEM B, PUR2000T C,           	            \n");
			sql.append("	(                                                                  	            \n");
			sql.append("		SELECT                                                           	        \n");
			sql.append("			PUR_CONT_ID                                                    	        \n");
			sql.append("			,MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                          	        \n");
			sql.append("		FROM PUR2000T                                                    	        \n");
			sql.append("		WHERE CON_CLS_YN = '9'                                                    	\n");
			sql.append("		GROUP BY PUR_CONT_ID                                                        \n");
			sql.append("	) D                                                               	            \n");
			sql.append("	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                	            \n");
			sql.append("	AND A.MATERIAL_CD = B.MATERIAL_CD                                  	            \n");
			sql.append("	AND A.PUR_CONT_ID = C.PUR_CONT_ID                                  	            \n");
			sql.append("	AND B.PUR_CONT_ID = C.PUR_CONT_ID                                  	            \n");
			sql.append("	AND B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                          	            \n");
			sql.append("	AND C.PUR_CONT_ID = D.PUR_CONT_ID                          	                    \n");
			sql.append("	AND C.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                          	            \n");
			sql.append("	AND A.SHIPPING_ID = ?                                              	            \n");
			search.addParameter(params.get("shipping_id"));
			sql.append("	ORDER BY A.PUR_CONT_ID,A.MATERIAL_CD                               	            \n");
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getSsrDetailSheet() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getSsrInvSheet(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	SELECT                                   \n");
			sql.append("		  A.SHIPPING_ID                      \n");
			sql.append("		, A.SHIPPING_DETAIL_ID               \n");
			sql.append("		, A.INV_NO              	         \n");
			sql.append("		, A.INV_VALUE              	         \n");
			sql.append("		, A.INV_CUR              	         \n");
			sql.append("		, A.ORD                  	         \n");
			sql.append("		, A.INV_DESC              	         \n");
			sql.append("	FROM PSR_SHIPPING_INV A            	     \n");
			sql.append("	WHERE  A.SHIPPING_ID = ?                 \n");
			search.addParameter(params.get("shipping_id"));
			sql.append("	ORDER BY A.ORD                        \n");
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getSsrInvSheet() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getfileListRowSet(String payreqId, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        try {

    		sql.append("	SELECT distinct A.DETAILCD,						\n");
    		sql.append(" 		   A.DETAILNM,								\n");
    		sql.append(" 		   B.ID AS DOC_CD							\n");
    		sql.append("	FROM EBID_COM911T A, PUB0510T B				    \n");
    		sql.append("	WHERE A.BASECD = 'SSR_FILE'					    \n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		sql.append("	AND B.FILE_GB(+) = '"+payreqId+"'				\n");  //ssr_file_id
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
    		System.out.println(sql);
        	
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sql.toString());
            
           
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }
	
	private ValueObject getShippingId() throws Exception {
		Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        
        ValueObject vo = new ValueObject();
    	ValueObject row = null;
        try {

    		sql.append("   SELECT  NVL(B.VENDOR_CD, A.VENDOR_CD ) VENDOR_CD 	\n");
    		sql.append("	      ,NVL(D.CORP_KOR, C.CORP_KOR) VENDOR_NM	    \n");
    		sql.append("	 FROM (SELECT A.CORP_ID VENDOR_CD	                \n");
    		sql.append("	         FROM PUR1000T A	                        \n");
    		sql.append("	             ,SUPP_PUR_ITEM B 	                    \n");
    		sql.append("	        WHERE A.CORP_ID = B.CORP_ID 	            \n");
    		sql.append("	          AND B.KIND_SMALL = '811'	                \n");
    		sql.append("	          AND B.REPRE_YN = 'Y'	                    \n");
    		sql.append("	          AND DEPT_CD = ? ) A	                    \n");
    		sql.append("	     ,(SELECT NVL(MAX(VENDOR_CD), '') VENDOR_CD	    \n");
    		sql.append("	         FROM PSR_SHIPPING 	                        \n");
    		sql.append("	        WHERE DEPT_CD = ?  ) B  	                \n"); 
    		sql.append("	     ,SUPP_INFO C	                                \n");
    		sql.append("	     ,SUPP_INFO D	                                \n");
    		sql.append("	WHERE A.VENDOR_CD = C.CORP_ID(+)	                \n");
    		sql.append("	  AND B.VENDOR_CD = D.CORP_ID(+)	                \n"); 
        	
            search.setStatement(sql.toString());
            search.addParameter(params.get("dept_cd"));
            search.addParameter(params.get("dept_cd"));
            listRowSet = search.execute();
            row = RowSetUtility.getValueObject(listRowSet.getRow(0));
    		vo.set("VENDOR_CD", row.get("VENDOR_CD"));
    		vo.set("VENDOR_NM", row.get("VENDOR_NM"));
    		vo.set("status", "1");
    		vo.set("shipping_no", "");
            WAFLogger.debug(sql.toString()); 
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
        	if(vo == null) vo = new ValueObject();
           
        }        
        return vo; 		
	}
	
	public String getShipNo(ValueObject params) throws Exception {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StringBuffer sql = new StringBuffer();
		String returnValue = "";

		try {

			sql.append("  SELECT REPLACE(DECODE(NVL(MAX(SHIP_NO), 'NONE') , 'NONE','SRN-' || ? || '-001'                                                           \n"); 
			sql.append(" 		  , SUBSTR(MAX(SHIP_NO), 0, LENGTH(MAX(SHIP_NO)) - 3) )  ||TO_CHAR(SUBSTR(MAX(SHIP_NO), LENGTH(MAX(SHIP_NO)) - 2) +1, '000')       \n"); 
			sql.append(" 		  , ' ' )   ship_no                                                                                                                \n"); 
			sql.append("    FROM PSR_SHIPPING                                                                                                                      \n");   
			sql.append("   WHERE DEPT_CD = ?                                                                                                                       \n");    

			search.setStatement(sql.toString());
			search.addParameter(params.get("dept_cd"));
			search.addParameter(params.get("dept_cd"));
			rowSet = search.execute();
			if (rowSet.size() > 0) {
				returnValue = rowSet.getRow(0).get("SHIP_NO");
			}
			return returnValue;
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getShipNo() : \n"
					+ se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			return returnValue;
		}
	}

	
	public RowSet getShippingTarget1(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	        SELECT A.PUR_CONT_ID                                                                                    \n");
			sql.append("	             , A.ORD_DOC_NO                                                                                     \n");
			sql.append("	             , A.CST_DOC_NAME                                                                                   \n");
			sql.append("	             , A.OVERSEA_GB                                                                                     \n");
			sql.append("	             , GET_CORP_NM_EBID(A.CORP_ID) VENDOR_NAME                                                               \n");
			sql.append("	             , A.CORP_ID                                                                                        \n");
			sql.append("	             , A.DEPT_NAME                                                                                      \n");
			sql.append("	             , A.DEPT_CD                                                                                        \n");
			sql.append("	        FROM PUR1000T A                                                                                         \n");
			sql.append("	         WHERE A.DEPT_CD = ?                                                                                      \n");
			search.addParameter(params.get("dept_cd"));
			if(!"".equals(params.get("vendor_name"))) {
				sql.append("		AND UPPER(GET_CORP_NM_EBID(A.CORP_ID)) LIKE '%' || ? || '%'												\n");
				search.addParameter(params.get("vendor_name").toUpperCase()); 
			}
			sql.append("	         ORDER BY PUR_CONT_ID    																				\n");
		
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getShippingTarget1() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getShippingTarget2(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	SELECT                                                           		\n");
			sql.append("		A.MATERIAL_CD                                                  		\n");
			sql.append("		, F_GET_MATERIAL_NAME(A.MATERIAL_CD) MATERIAL_NAME             		\n");
			sql.append("		, F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) MATERIAL_SPEC            		\n");
			sql.append("		, F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) ITEM_UNIT                		\n");
			sql.append("		, NVL(A.ITEM_QTY,0) ship_qty                                   		\n");
			sql.append("		, A.ITEM_PRICE shp_unit_cost                                        \n");
			sql.append("		, NVL(A.ITEM_QTY,0)*A.ITEM_PRICE shp_amt                 		    \n");
			sql.append("		, B.PUR_CONT_ID                                                		\n");
			sql.append("		, B.ORD_DOC_NO                                                 		\n");
			sql.append("		, B.CST_DOC_NAME                                               		\n");
			sql.append("		, B.CURRENCY_GB                                                		\n");
			sql.append("		, E.MATERIAL_GB                                                		\n");
			sql.append("	FROM CONT_PUR_ITEM A, PUR2000T B,                                		\n");
			sql.append("	(                                                                		\n");
			sql.append("		SELECT                                                         		\n");
			sql.append("			PUR_CONT_ID                                                  		\n");
			sql.append("			,MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                        		\n");
			sql.append("		FROM PUR2000T                                                  		\n");
			sql.append("		WHERE DEPT_CD = ?		\n");
			search.addParameter(params.get("dept_cd"));
			sql.append("		AND PUR_CONT_ID = ?		\n");
			search.addParameter(params.get("pur_cont_id"));
			sql.append("		AND CON_CLS_YN = '9'                                           		\n");
			sql.append("		GROUP BY PUR_CONT_ID                                           		\n");
			sql.append("	) C,                                                             		\n");
			sql.append("	(                                                               		\n");
			sql.append("		SELECT A.MATERIAL_GB, B.MATERIAL_CD									\n");
			sql.append("		FROM PMM_MATERIALGROUP A, PMM_COMM_MATERIAL_MASTER B				\n");
			sql.append("		WHERE A.MATERIALGROUP = B.KIND_BIG									\n"); //2018.05.09 13->14로 수정 
			sql.append("		 AND decode(substr(B.material_cd, 0, 1), 'S', A.MATERIALLEVEL, '1') = decode(substr(B.material_cd, 0, 1), 'S', CASE WHEN LENGTH(B.MATERIAL_CD) <= 14 THEN 1 ELSE 2 END   , '1')       									\n");
			sql.append("	)E                                                               		\n");
			sql.append("	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                              		\n");
			sql.append("	AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID		\n");
			sql.append("	AND A.PUR_CONT_ID = C.PUR_CONT_ID		\n");
			sql.append("	AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                        		\n");
			sql.append("	AND A.MATERIAL_CD = E.MATERIAL_CD                                		\n");
			sql.append("	AND DEPT_CD = ?                                                  		\n");
			search.addParameter(params.get("dept_cd"));
			sql.append("	AND A.PUR_CONT_ID = ?                                            		\n");
			search.addParameter(params.get("pur_cont_id"));
			sql.append("	ORDER BY MATERIAL_CD                                             		\n");
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getShippingTarget2() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
}
	
	 