/**
 * @(#)file    ContIntBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-07-04
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;

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

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.config.WAFConfig;
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.WAFConnectionImpl;
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.common.sender.MessageSend;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

   
public class ContIngBiz extends Biz {

	public ContIngBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        //WAFLogger.debug("BidIngListBiz :  ~" + storage.getUser());

		boolean success = false;
		
		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getContList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SHEET_SELECT")){
			
			params.set("sheet2", getStampList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_PROMISE_SELECT")){
			
			params.set("sheet3", getPromiseList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_PRICESYS_SELECT")){
			
			params.set("sheet4", getPricesysList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SELECT")){
            user = getContRequestValueObject(params.get("PUR_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);

            RowSet itemList = getContItemList(params);
            //WAFLogger.debug("ITE LIST : ++++++++++++++++++++++++++++++++++++++++++ " + itemList.size());
            storage.addRowSet("itemList", itemList);            
            
			//저장된 HTML 정보
            RowSet template = getTemplateInfo(params.get("PUR_CONT_MOD_ID"));
            storage.addRowSet("template", template);

			//자동 저장 파일
			RowSet rowSet = getTemplateFile(params.get("PUR_CONT_MOD_ID"));
			storage.addRowSet("AUTOFILE_LIST", rowSet);            

			//인지세구분
			RowSet stampRowSet = getStampIssue();
			storage.addRowSet("stampRowSet", stampRowSet);  
			
			//보증 정보 가져오기
			params.set("MOD_NO", user.get("MOD_NO"));
			
	        String wonCont = "K";
	        
	        if(!params.get("MOD_NO").equals("0")){
	        	wonCont = getWarrGubun(params.get("PUR_CONT_MOD_ID") );
	        }
	        
	        params.set("WON_WARR", wonCont);
	        
			RowSet warrList = getWarrList(params);
			storage.addRowSet("warrList", warrList);    
			
            //변경계약일경우 변경계약내역서
            if(params.get("ChgCont").equals("Y")){
            	//변경계약내역서 추가 2019.08.06
            	params.set("pDemID", user.get("DEM_ID"));
            	params.set("PUR_CONT_ID", user.get("PUR_CONT_ID"));
            	RowSet chgItemList = getChgItemList(params);
            	storage.addRowSet("chgItemList", chgItemList);
            }			
		}else if(storage.compareEvent("PARTNER_EMP_CHK")){
        	ValueObject empChk = getEmpChk(params.get("PUR_CONT_MOD_ID"), params.get("C_HP"));
	        storage.addValueObject("VALUEOBJECT", empChk);
        }else if(storage.compareEvent("FORM_SAVE") || storage.compareEvent("FORM_DECI_SAVE") || storage.compareEvent("FORM_RESOLVE_SAVE")){
			//계약 정보 저장
			success = saveContProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("STAMP_DUTY_GB_SAVE")){ //인지세 발급구분 업데이트 
           
			success = updateStampDutyGb(super.conn.getConnection(), params);
			super.storage.setSuccess(success);

		}else if(storage.compareEvent("SIGN_CALL")){

			params.set("pStatusCD", "20");
			params.set("pConClsYn", "1");
			ContStatusUp(super.conn.getConnection(), params);	//서명 요청

			Send(params, storage.getEvent());		//서명요청 메일과 SMS 전송

			super.storage.setSuccess(true);
		}else if(storage.compareEvent("SIGN_OK")){
			//ERP 등록 업체 확인
			Integer sCount = getErpSuppCount(params.get("C_CORP_ID"));
			
			String orgCdCoop = getOrgCdCoop(params.get("PUR_CONT_MOD_ID"));
			if( !orgCdCoop.equals("") ) {
				sCount = 1;
			}
			
			if(sCount>0) {
				String sMsg="";
				String signedData="";
				
				Crosscert crossErt = new Crosscert();
				if( ("localhost").equals(storage.getRequest().getServerName()) ) {
					sMsg = "SIGN_SUCCESS";
				}else{
					signedData = crossErt.signCrosscert(params.get("CONT_HASH"));
					sMsg = crossErt.chkSignVerify(signedData);
				}
				//WAFLogger.debug("sMsg ======= : " + crossErt.getDn());
				
				if(sMsg.equals("SIGN_SUCCESS")){
					params.set("CONT_SIGNDN", crossErt.getDn());
					params.set("CONT_SIGNDATA", signedData);				
					success = endContProc(super.conn.getConnection(), params, true);
	
					super.storage.setSuccess(success);
				}else{
					super.storage.setSuccess(false);	
					super.storage.setMessage("인증서 검증에 실패 하였습니다.");
				}
			}else{
				
				int aCount = 0;
				ValueObject suppVo = getSuppInfo(params.get("PUR_CONT_MOD_ID"));
				aCount = getErpSuppCountAll(suppVo.get("CORP_REG_NO"));
				
				if(aCount == 0) {
					
					/*
					 * 2019.02.15
					 * 이미경차장님요청
					 * 해외업체일경우 ERP 거래처등록 안함 
					 */
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						throw new Exception("ERP에 등록되지 않은 해외 업체입니다.");
					}
					
					suppVo.set("CD_PARTNER", InterfacePurERPiU.getCdPartner());
					
					//국외업체일경우 SUPP_INFO.CORP_REG_NO 에 CD_PARTNER 값을 넣어준다.
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						updateCdPartner(suppVo.get("CORP_ID"), suppVo.get("CD_PARTNER"));
					}
					
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();
						InterfacePurERPiU.insertErpSupp(erpConn.getConnection(), suppVo);
						
						//구매담당자 ERP거래처 등록요청 메일발송
						sendSuppInfoMail(suppVo.get("TO_MAIL"), suppVo.get("CORP_REG_NO"), suppVo.get("LN_PARTNER"), suppVo.get("CORP_ID"));
						
					}catch(Exception e) {
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
					
				}
				
				super.storage.setSuccess(false);	
				super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
			}
		}else if(storage.compareEvent("END_CONT")){		
			//ERP 등록 업체 확인
			Integer sCount = 0;
			//2016.11.8 onshore, offshore구분
			sCount = getErpSuppCount(params.get("C_CORP_ID"));
			
			String orgCdCoop = getOrgCdCoop(params.get("PUR_CONT_MOD_ID"));
			if( !orgCdCoop.equals("") ) {
				sCount = 1;
			}
			
			if(params.get("ERP_CHECK_YN").equals("N") ){
				sCount = 1;
			}
			if(sCount>0) {
				success = endContProc(super.conn.getConnection(), params, false);
				super.storage.setSuccess(success);
			}else{
				
				int aCount = 0;
				ValueObject suppVo = getSuppInfo(params.get("PUR_CONT_MOD_ID"));
				aCount = getErpSuppCountAll(suppVo.get("CORP_REG_NO")); 
				
				if(aCount == 0) {
					
					/*
					 * 2019.02.15
					 * 이미경차장님요청
					 * 해외업체일경우 ERP 거래처등록 안함 
					 */
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						super.storage.setSuccess(false);	
						super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
						throw new Exception("ERP에 등록되지 않은 해외 업체입니다.");
					}
					
					suppVo.set("CD_PARTNER", InterfacePurERPiU.getCdPartner());
					
					//국외업체일경우 SUPP_INFO.CORP_REG_NO 에 CD_PARTNER 값을 넣어준다.
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						updateCdPartner(suppVo.get("CORP_ID"), suppVo.get("CD_PARTNER"));
					}
					
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();
						InterfacePurERPiU.insertErpSupp(erpConn.getConnection(), suppVo);
						
						//구매담당자 ERP거래처 등록요청 메일발송
						sendSuppInfoMail(suppVo.get("TO_MAIL"), suppVo.get("CORP_REG_NO"), suppVo.get("LN_PARTNER"), suppVo.get("CORP_ID"));
						
					}catch(Exception e) {
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
					
				}
				
				super.storage.setSuccess(false);	
				super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
			}			
		}else if(storage.compareEvent("FORM_DEL")){
			//계약 정보 삭제
			success = deleteContProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}/*else if(storage.compareEvent("SAVE_STAMP")){
			//인지세 저장
			success = saveStampDuty(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}*/else if(storage.compareEvent("FORM_MOVE")){
			success = updateMoveStatus(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("FORM_PDF_SAVE")){
			ArrayList pdfIfno = makeContPdf(params);		// pdf파일 생성
			System.out.println("pdfIfno"+pdfIfno);
		}
	}

	private ValueObject getfilelist(String filegb,String id,String returnid){
		ValueObject vo = new ValueObject();
		RowSet rowSet = FileManager.getFileList(filegb, id);
		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.set(returnid, str);
		return vo;
	}
	
	private String getfileHashlist(String filegb,String id){
		String fileHash="";
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
		
		if( ("localhost").equals(storage.getRequest().getServerName()) ) {
			return fileHash;
		}
		
		if (rowSet.size()>0){
			PDFMaker pdfMaker = new PDFMaker();

			for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);

	        	str = pdfMaker.getHash(WAFConfig.get("waf.repository.path") + row.get("FILE_PATH") +"/"+ row.get("REAL_FILE_NAME"));
	        	fileHash = fileHash + str +"|" ;
	        }
		}
		return fileHash;
	}
	

	
	
	 /**
     * 저장된 계약서식 조회 
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getTemplateInfo(String pur_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT            							\n"
					+ "	  SEQ,                                      \n"
					+ "	  TEMPLATE_NAME,                            \n"
					+ "	  TEMPLATE_HTML,                            \n"
					+ "	  TEMPLATE_STYLE,                           \n"
					+ "	  CHECK_YN,                              \n"
					+ "	  OPTION_YN	                                \n"
                    + " FROM CONT_PUR_HTML      					\n"
                    + " WHERE PUR_CONT_MOD_ID =  '"+pur_cont_cd+"'		\n"
		            + " ORDER BY SEQ ASC      				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
	 /**
     * 계약서식 조회 combo 조회
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getTemplateFile(String pur_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT,          \n"   
            		+ "		TRIM(          \n"              
            		+ "        CASE                         \n"
				    + "       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'          \n"
				    + "       ELSE          \n"
				    + "            CASE                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'          \n"
				    + "             ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'          \n"
				    + "            END                           \n"                              
				    + "      END ) AS FILE_SIZE, FILE_NAME         \n"
                    + " FROM CONT_PUR_FILE      					\n"
                    + " WHERE PUR_CONT_MOD_ID =  '"+pur_cont_cd+"'		\n"
		            + " ORDER BY FILE_ID ASC      				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}

	/**
	 * 인지세구분 조회
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getStampIssue(){
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		String sqlstr = "";
		
		try {
			sqlstr += " SELECT	DETAILCD, DETAILNM				\n"
					+ "	FROM	EBID_COM911T					\n"
					+ "	WHERE	BASECD = 'STAMP_DUTY_ISSUE_GB'	\n";
			
			search.setStatement(sqlstr);
			listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
			if( listRowSet == null ){
				listRowSet = new RowSetImpl();
			}
			
			return listRowSet;
		}
	}
	
	/**
	 * 계약 대상리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getContList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" 	SELECT A.PUR_CONT_MOD_ID	\n");
        	sqlstr.append(" 	     ,A.ORD_DOC_NO	\n");
        	sqlstr.append(" 	     ,A.MOD_NO	\n");
        	sqlstr.append(" 	     ,A.DEPT_NAME	\n");
        	sqlstr.append(" 	     ,A.CST_DOC_NAME	\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') AS ORD_CON_DATE	\n");
        	sqlstr.append(" 	     ,F.MEMBER_NAME	\n");
        	sqlstr.append(" 	     ,A.ORD_AMT	\n");
        	sqlstr.append(" 	     ,F_GET_D_CURRENCY(A.ORD_SUPPLY_AMT, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) AS ORD_SUPPLY_AMT	\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.ORD_START_DATE, 'YYYY-MM-DD') || '~' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS WORK_START_END_DATE	\n");
        	sqlstr.append(" 	     ,A.ORD_START_DATE	\n");
        	sqlstr.append(" 	     ,A.ORD_END_DATE	\n");
        	sqlstr.append(" 	     ,A.CON_GB 	\n");
        	sqlstr.append(" 	     ,A.STATUS_CD 	\n");
        	sqlstr.append(" 	     ,F_GET_CODE_NAME('CONT_STATUS_CD', A.STATUS_CD) AS STATUS_CD_NM	\n");
        	sqlstr.append(" 	     ,A.TEMPLATE_CD \n");
        	sqlstr.append(" 	     ,B.TEMPLATE_NAME \n");
        	sqlstr.append(" 	     ,C.CHG_CONT_YN \n");
        	sqlstr.append(" 	     ,A.CURRENCY_GB 	\n");
        	sqlstr.append(" 	     ,NVL(A.ELEC_CON_YN, 'N') 	 AS ELEC_CON_YN  \n");
        	sqlstr.append(" 	     ,DECODE(A.PAY_DIV, '1', '납품정산', '계획분할') PAY_DIV 	\n");
        	sqlstr.append(" 	  FROM PUR2000T A, CONT_PUR_HTML B, CONT_TEMPLATE C, CONT_PUR_SUPP F	\n");
        	sqlstr.append("   WHERE 1=1					\n");
        	sqlstr.append("   	AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID					\n");
        	sqlstr.append("   	AND A.TEMPLATE_CD = C.TEMPLATE_CD					\n");
        	sqlstr.append("   	AND B.SEQ=0					\n");
        	sqlstr.append("   	AND C.SEQ=0					\n");
    		sqlstr.append("   	AND A.PUR_CONT_MOD_ID = F.PUR_CONT_MOD_ID				\n");
    		sqlstr.append("   	AND F.SIGN_SEQ = 2				\n");
    		if(param.get("user_check").equals("false")){
        		sqlstr.append("  					 AND A.CON_CHARGE_ID =  '"+storage.getUser().get("ACCT_ID")+"'																\n");
        	}
        	// 현장
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND A.DEPT_CD = ?																											 \n");
    			search.addParameter(params.get("P_DEPT_CD"));
        	}
//        		sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'				\n");
        	if(!param.get("P_BUILD_NM").isEmpty())
        		sqlstr.append("   AND UPPER(A.CST_DOC_NAME) LIKE UPPER('%"+param.get("P_BUILD_NM")+"%')				\n");
        	if(!param.get("ContFromDt").isEmpty())		//계약기간
        		sqlstr.append("   AND TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') >= '"+param.get("ContFromDt")+"'				\n");
        	if(!param.get("ContToDt").isEmpty())
        		sqlstr.append("   AND TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') <= '"+param.get("ContToDt")+"'				\n");
        	if(!param.get("p_dealer_nm").isEmpty())
        		sqlstr.append("   AND UPPER(F.MEMBER_NAME) LIKE '%'|| UPPER('"+param.get("p_dealer_nm")+"') ||'%'																						\n");
        	if(!param.get("P_CONT_STATUS").isEmpty())
        		sqlstr.append("   AND A.STATUS_CD IN ("+param.get("P_CONT_STATUS")+")																						\n");

        	sqlstr.append("   	ORDER BY A.ORD_CON_DATE DESC			\n");
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	

	
	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getContRequestValueObject(String PUR_CONT_MOD_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
	        	sqlstr.append(" 	SELECT A.PUR_CONT_MOD_ID	\n");
	        	sqlstr.append(" 	     , A.ORD_DOC_NO	\n");
	        	sqlstr.append(" 	     , A.MOD_NO	\n");	
	        	sqlstr.append(" 	     , DECODE(A.MOD_GB, NULL, '00', A.MOD_GB) AS MOD_GB	\n");	
	        	sqlstr.append(" 	     , A.DEPT_NAME	\n");	
	        	sqlstr.append(" 	     , A.CST_DOC_NAME	\n");	
	        	sqlstr.append(" 	     , A.ORD_CON_DATE	\n");	
	        	sqlstr.append(" 	     , A.PUR_CONT_ID	\n");	
	        	sqlstr.append(" 	     , A.ORD_AMT	\n");	
	        	sqlstr.append(" 	     , A.ORD_START_DATE	\n");	
	        	sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'YYYY') AS ORD_START_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'MM') AS ORD_START_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'DD') AS ORD_START_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.ORD_END_DATE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'YYYY') AS ORD_END_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'MM') AS ORD_END_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'DD') AS ORD_END_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.CON_GB 	\n");	
	        	sqlstr.append(" 	     , A.CURRENCY_GB 	\n");														//추가 (2014-09-19)
	        	sqlstr.append(" 	     , (SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CURRENCY_GB) AS SOSUNUM 	\n");
	        	sqlstr.append(" 	     , (SELECT CHG_CODE02 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CURRENCY_GB) AS CURR_STD 	\n");
	        	sqlstr.append(" 	     , (SELECT CHG_CODE06 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CURRENCY_GB) AS CURR_STD_NM 	\n");
	        	sqlstr.append(" 	     , A.LEAD_PLACE 	\n");	
	        	sqlstr.append("       	 , A.LEAD_COND_GB		 	\n");
	        	sqlstr.append("       	 , F_GET_CODE_NAME('PUR_LEAD_GB', A.LEAD_COND_GB) LEAD_COND_GB_NM	 	\n");	 //추가 (2014-09-25)
	        	sqlstr.append("       	 , A.ORD_SUPPLY_AMT			\n");
	        	sqlstr.append("       	 , A.ORD_SURTAX_AMT			\n");
	        	sqlstr.append("      	 , A.PREPAY_AMT_RATE			\n");
	        	sqlstr.append("       	 , A.PREPAY_AMT			\n");
	        	sqlstr.append("       	 , A.MID_AMT_RATE			\n");
	        	sqlstr.append("       	 , A.MID_AMT			\n");
	        	sqlstr.append("       	 , A.RMDR_AMT_RATE			\n");
	        	sqlstr.append("       	 , A.RMDR_AMT			\n");
	        	sqlstr.append("       	 , A.CON_GUAR_AMT_RATE			\n");
	        	sqlstr.append("       	 , A.CON_GUAR_TERM			\n");
	        	sqlstr.append("       	 , A.CON_GUAR_AMT			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_AMT_RATE			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_AMT			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_TERMS			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_TERMS_TXT			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_TERMS2			\n");
	        	sqlstr.append("       	 , A.DEF_GUAR_TERMS_TXT2			\n");
	        	sqlstr.append("       	 , A.DEL_RATE_GB			\n");
	        	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'YYYY') AS ORD_CON_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'MM') AS ORD_CON_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'DD') AS ORD_CON_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.STATUS_CD 	\n");	
	        	sqlstr.append(" 	     , F_GET_CODE_NAME('CONT_STATUS_CD',A.STATUS_CD) AS STATUS_NM 	\n");	
	        	sqlstr.append(" 	     , A.TEMPLATE_CD 	\n");	
	        	sqlstr.append(" 	     , A.TRUE_RANDOM 	\n");
	        	sqlstr.append(" 	     , A.CONT_HASH 	\n");	
	        	sqlstr.append(" 	     , A.MODI_REQ_REASON 	\n");	
	        	sqlstr.append(" 	     , A.MODI_REQ_DT 	\n");	
	        	sqlstr.append("          , A.PAY_DIV		\n");									//추가 (2014-09-22) 	
	        	sqlstr.append("          , A.DIV_PO_YN		\n");									//추가 (2014-09-22) 	

	        	/*sqlstr.append(" 	     , B.DEMANDED_ID	\n");	
	        	sqlstr.append(" 	     , B.DEMANDED_NO	\n");	
	        	sqlstr.append(" 	     , B.DEMANDED_TITLE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(TO_DATE(B.DEMANDED_DATE),'YYYY-MM-DD') AS DEMANDED_DATE	\n");	
	        	sqlstr.append(" 	     , B.REQ_DEPT_NM	\n");	
	        	sqlstr.append(" 	     , B.EMP_NO	\n");	
	        	sqlstr.append(" 	     , B.EMP_NM	\n");*/
	        	
	        	sqlstr.append(" 	     , NVL(B.DEMANDED_ID, R.DEMANDED_ID) AS DEMANDED_ID	\n");	
	        	sqlstr.append(" 	     , NVL(B.DEMANDED_NO, R.DEMANDED_NO) AS DEMANDED_NO	\n");	
	        	sqlstr.append(" 	     , NVL(B.DEMANDED_TITLE, R.DEMANDED_TITLE) AS DEMANDED_TITLE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(TO_DATE(NVL(B.DEMANDED_DATE, R.DEMANDED_DATE)),'YYYY-MM-DD') AS DEMANDED_DATE	\n");	
	        	sqlstr.append(" 	     , NVL(B.REQ_DEPT_NM, (SELECT DEPART_DEPT_NM FROM PUB0120T WHERE USER_SABUN = R.EMP_NO)) AS REQ_DEPT_NM	\n");	
	        	sqlstr.append(" 	     , NVL(B.EMP_NO, R.EMP_NO) AS EMP_NO	\n");	
	        	sqlstr.append(" 	     , NVL(B.EMP_NM, (SELECT USER_NAME FROM PUB0120T WHERE USER_SABUN = R.EMP_NO)) AS EMP_NM	\n");
	        	
	        	/*분개 유형*/
	        	sqlstr.append(" 	     , A.ACCT_GB 	\n");	
	        	sqlstr.append(" 	     , A.PAY_GROUP 	\n");	
	        	sqlstr.append(" 	     , A.GUA_GB 	\n");	
	        	sqlstr.append(" 	     , A.PAYMENT_TERMS 	\n");	
	        	sqlstr.append(" 	     , A.PAY_ITEM 	\n");	
	        	sqlstr.append(" 	     , A.COST_CODE 	\n");	
	        	sqlstr.append(" 	     , A.COST_TYPE 	\n");	
	        	sqlstr.append(" 	     , A.DEPT_CD 	\n");		        	
	        	sqlstr.append(" 	     , F_GET_PAY_ITEM_EST_DESC(A.PAY_ITEM,A.COST_CODE,A.COST_TYPE,A.DEPT_CD) AS PAY_ITEM_NM	\n");
	        	sqlstr.append(" 	     , (SELECT AC_UNIT_CD FROM ACZ10100V Z WHERE  Z.DEPT_CD = A.DEPT_CD) AS  AC_UNIT_CD	\n");		        	

	        	sqlstr.append(" 	     , A.STAMP_DUTY_GB	\n");				
	        	
	        	//승인자 추가
	        	sqlstr.append(" 	     , A.CON_CONFIRM_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CONFIRM_NAME 	\n");	

				sqlstr.append("          , A.PREPAY_PAYMENT_NM		\n");
				sqlstr.append("          , A.MID_PAYMENT_NM		\n");
				sqlstr.append("          , A.RMDR_PAYMENT_NM		\n");	        	
	        	
	        	sqlstr.append(" 	     , A.DEM_ID 	\n");				//변경 발주 번호 : 2014-10-30 김명호 추가
	        	
	        	sqlstr.append(" 	     , A.CON_CHARGE_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CHARGE_NAME 	\n");

	        	//결재정보 추가
	        	sqlstr.append(" 	     , A.IN_SPE_INFO 	\n");				//내부특기사항 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.IN_CHG_INFO 	\n");				//내부변경사유 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.FRST_CON_AMT	\n");				//원_도급금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.FRST_EXE_AMT	\n");				//원_실행금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.CHG_CON_AMT	\n");				//현_도급금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.CHG_EXE_AMT	\n");				//현_실행금액 : 2014-11-26 천호준 추가
	        	
	        	sqlstr.append(" 	     , DECODE(D.CORP_ID, '', 0, D.CORP_ID)  AS O_CORP_ID	\n");	
	        	sqlstr.append(" 	     , DECODE(D.MEMBER_NAME, '', C.CORP_KOR, D.MEMBER_NAME)  AS O_MEMBER_NAME	\n");	
	        	sqlstr.append(" 	     , DECODE(D.ADDRESS, '', C.ADDRESS, D.ADDRESS) AS O_ADDRESS	\n");	
	        	sqlstr.append(" 	     , DECODE(D.BOSS_NAME, '', C.REP_NAME, D.BOSS_NAME) AS O_BOSS_NAME	\n");
	        	sqlstr.append(" 	     , DECODE(D.VENDCD, '', C.CORP_REG_NO, D.VENDCD) AS O_REG_NO	\n");
	        	sqlstr.append(" 	     , D.USER_NAME AS O_USER_NAME	\n");	
	        	sqlstr.append(" 	     , D.TEL_NUM AS O_TEL_NUM	\n");	
	        	sqlstr.append(" 	     , D.DIVISION AS O_DIVISION	\n");	
	        	sqlstr.append(" 	     , D.POSITION AS O_POSITION	\n");	
	        	sqlstr.append(" 	     , D.HP AS O_HP	\n");	
	        	sqlstr.append(" 	     , D.EMAIL AS O_EMAIL	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(D.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n");	
	        	sqlstr.append(" 	     , D.VENDCD AS O_VENDCD	\n");		        	
	        	sqlstr.append(" 	     , E.CORP_ID  AS C_CORP_ID	\n");	
	        	sqlstr.append(" 	     , E.MEMBER_NAME AS C_MEMBER_NAME	\n");	
	        	sqlstr.append(" 	     , E.ADDRESS AS C_ADDRESS	\n");	
	        	sqlstr.append(" 	     , E.BOSS_NAME AS C_BOSS_NAME	\n");	
	        	sqlstr.append(" 	     , E.USER_NAME AS C_USER_NAME	\n");	
	        	sqlstr.append(" 	     , E.TEL_NUM AS C_TEL_NUM	\n");	
	        	sqlstr.append(" 	     , E.DIVISION AS C_DIVISION	\n");	
	        	sqlstr.append(" 	     , E.POSITION AS C_POSITION	\n");	
	        	sqlstr.append(" 	     , E.HP AS C_HP	\n");		        	
	        	sqlstr.append(" 	     , E.VENDCD AS C_VENDCD	\n");		        	
	        	sqlstr.append(" 	     , E.EMAIL AS C_EMAIL	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(E.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n");	
	        	sqlstr.append(" 	     , NVL(A.ERP_CHECK_YN,'Y') AS ERP_CHECK_YN	\n");	//2016.11.8 onshre,offhore구분 
	        	sqlstr.append(" 	     , (SELECT IN_OUT_DIV FROM SUPP_INFO WHERE CORP_ID = E.CORP_ID) AS IN_OUT_DIV	\n");
	        	sqlstr.append(" 	     , (SELECT SUM(ITEM_AMT) FROM CONT_PUR_ITEM WHERE PUR_CONT_MOD_ID ='"+PUR_CONT_MOD_ID+"' ) AS ITEM_AMT_SUM 	\n");	
	        	sqlstr.append("          ,(SELECT NVL(CHG_CODE10, 'INPUT_VAT')                                                                      \n");															
				sqlstr.append("              FROM EBID_COM911T     									                                               	\n");																	
				sqlstr.append("             WHERE BASECD = 'PROOF_CD'   							                                               	\n");																	
				sqlstr.append("               AND DETAILCD = NVL(A.GUA_GB, '21')) INPUT_VAT		                                                   	\n");//2019.05.13 추가	
				sqlstr.append("  FROM PUR2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_PUR_SUPP D, CONT_PUR_SUPP E, PMM_DEMANDED_REQUEST R	    \n");
				sqlstr.append("  WHERE A.BID_NO = B.BID_NO(+) 	\n");
				sqlstr.append("  AND A.BID_DEG = B.BID_DEG(+)	\n");
				sqlstr.append("  AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+)	\n");
				sqlstr.append("  AND A.DEM_ID = R.DEMANDED_ID(+)	\n");
				sqlstr.append("  AND D.SIGN_SEQ(+) = 1 --원사업자	\n");
				sqlstr.append("  AND A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID(+)	\n");
				sqlstr.append("  AND E.SIGN_SEQ(+) = 2 --계약업체정보  	\n");
				sqlstr.append("  AND A.PUR_CONT_MOD_ID = '"+PUR_CONT_MOD_ID+"'							\n");

	            search.setStatement(sqlstr.toString());
	            rowSet = search.execute();

            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.error(sqlstr.toString());
            //WAFLogger.error("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	
	/**
	 * 품목 리스트
	 * @param Params
	 * @return
	 * 
	 */
	private RowSet getContItemList(ValueObject ValueObject) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();

        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT MATERIAL_CD, ITEM_NAME, ITEM_SPEC, NVL(ITEM_QTY, 0) as ITEM_QTY, ITEM_UNIT, NVL(F_GET_CODE_NAME('UNIT_KIND_GB',ITEM_UNIT),ITEM_UNIT) AS ITEM_UNIT_NM, TRUNC(nvl(ITEM_PRICE, 0)) AS ITEM_PRICE, TRUNC(nvl(ITEM_AMT, 0)) AS ITEM_AMT, (nvl(ITEM_QTY,0) * TRUNC(nvl(ITEM_PRICE,0))) AS SUM_AMT	\n");
        	sqlstr.append("   FROM CONT_PUR_ITEM										\n");
        	sqlstr.append(" WHERE PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'	\n");
			
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("SQL : " + sqlstr);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        
        return rowSet;
	}

	/**
     * 계약 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        try {
        	conn.begin();

			ArrayList pdfIfno = new ArrayList();
			
			if( !("localhost").equals(storage.getRequest().getServerName()) ) {
				pdfIfno = makeContPdf(params);		// pdf파일 생성
				if (pdfIfno.isEmpty()){
					storage.setMessage("PDF 생성에 실패 하였습니다.");    
					storage.setDetailMessage("PDF 생성에 실패 하였습니다.");    
					return false;
				}			
			}
			/*
			System.out.println("pdfIfno"+pdfIfno);
			if (pdfIfno.isEmpty()){
				super.storage.setMessage("PDF 생성에 실패 하였습니다.");    
				return false;
			}
			*/
			
			margeOrigCorpData(super.conn.getConnection(), params);		//원사업자 업체정보 저장
			margeContCorpData(super.conn.getConnection(), params);		//계약업체정보 저장

			CONT_PUR_HTML_del(super.conn.getConnection(), params);	//ContHTML 삭제
			CONT_PUR_HTML_Save(super.conn.getConnection(), params);	//ContHTML 등록
			//WAFLogger.debug("ContHTML 등록");

			CONT_PUR_FILE_del(super.conn.getConnection(), params);	//자동생성파일 삭제
			//WAFLogger.debug("자동생성파일 삭제");
			
			String file_hash="";
			String CONT_HASH="";
			if (!((params.get("TEMPLATE_CD").equals("9")) || (params.get("TEMPLATE_CD").equals("10")))) {
				for(int i=0; i<pdfIfno.size(); i++){
					file_hash = CONT_PUR_FILE_Save(super.conn.getConnection(), (ValueObject) pdfIfno.get(i));	//자동생성파일 등록
					CONT_HASH = CONT_HASH + file_hash +"|" ;
				}
			}
			//첨부파일 hash값 가져오기
			String addFileHash = getfileHashlist("PUR_CONT_MOD_ID", params.get("PUR_CONT_MOD_ID"));
			CONT_HASH = CONT_HASH + addFileHash;
			
			if (CONT_HASH.length()>0){
				params.set("CONT_HASH", CONT_HASH.subSequence(0, CONT_HASH.length()-1));
			}else{
				params.set("CONT_HASH", CONT_HASH.subSequence(0, CONT_HASH.length()));				
			}
			saveContInfo(super.conn.getConnection(), params);		//계약 정보 저장

			//2015.01.08 이인오 인지세 추가
			/*if( !params.get("stamp_duty_issue_gb").equals("00") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}*/

			return true;
        }
        catch(Exception e) {
        	//WAFLogger.debug("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }		

	/**
     * 원사업자 업체 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean margeOrigCorpData(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO CONT_PUR_SUPP T1 USING (									\n");
        	sqlstr.append("          SELECT ? AS	PUR_CONT_MOD_ID	\n");
        	sqlstr.append("                ,? AS	CORP_ID	\n");
        	sqlstr.append("                ,'' AS	VENDCD	\n");
        	sqlstr.append("                ,? AS	MEMBER_NAME	\n");
        	sqlstr.append("                ,? AS	BOSS_NAME	\n");
        	sqlstr.append("                ,'' AS	POST_CODE	\n");
        	sqlstr.append("                ,? AS	ADDRESS	\n");
        	sqlstr.append("                ,? AS	TEL_NUM	\n");
        	sqlstr.append("                ,'' AS	MEMBER_SLNO	\n");
        	sqlstr.append("                ,? AS	USER_NAME	\n");
        	sqlstr.append("                ,? AS	DIVISION	\n");
        	sqlstr.append("                ,? AS	POSITION	\n");
        	sqlstr.append("                ,? AS	HP	\n");
        	sqlstr.append("                ,? AS	EMAIL	\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.PUR_CONT_MOD_ID = T1.PUR_CONT_MOD_ID AND T.CORP_ID = T1.CORP_ID)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET 				\n");
//        	sqlstr.append("  				, T1.VENDCD	 =	, T.VENDCD	\n");
        	sqlstr.append("  				 T1.MEMBER_NAME	 =	 T.MEMBER_NAME	\n");
        	sqlstr.append("  				, T1.BOSS_NAME	 =	 T.BOSS_NAME	\n");
//        	sqlstr.append("  				, T1.POST_CODE	 =	 T.POST_CODE	\n");
        	sqlstr.append("  				, T1.ADDRESS	 =	 T.ADDRESS	\n");
        	sqlstr.append("  				, T1.TEL_NUM	 =	 T.TEL_NUM	\n");
//        	sqlstr.append("  				, T1.MEMBER_SLNO	 =	 T.MEMBER_SLNO	\n");
        	sqlstr.append("  				, T1.USER_NAME	 =	 T.USER_NAME	\n");
        	sqlstr.append("  				, T1.DIVISION	 =	 T.DIVISION	\n");
        	sqlstr.append("  				, T1.POSITION	 =	 T.POSITION	\n");
        	sqlstr.append("  				, T1.HP	 =	 T.HP	\n");
        	sqlstr.append("  				, T1.EMAIL	 =	 T.EMAIL	\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (T1.PUR_CONT_MOD_ID, T1.CORP_ID, T1.SIGN_SEQ, T1.VENDCD, T1.MEMBER_NAME, T1.BOSS_NAME, T1.POST_CODE, T1.ADDRESS, T1.TEL_NUM, T1.MEMBER_SLNO, T1.USER_NAME, T1.DIVISION, T1.POSITION, T1.HP, T1.EMAIL)									\n");
        	sqlstr.append("			VALUES (T.PUR_CONT_MOD_ID, T.CORP_ID, 1, T.VENDCD, T.MEMBER_NAME, T.BOSS_NAME, T.POST_CODE, T.ADDRESS, T.TEL_NUM, T.MEMBER_SLNO, T.USER_NAME, T.DIVISION, T.POSITION, T.HP, T.EMAIL)											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.addParameter(valueObject.get("O_CORP_ID"));
            persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
            persistent.addParameter(valueObject.get("O_BOSS_NAME"));
            persistent.addParameter(valueObject.get("O_ADDRESS"));
            persistent.addParameter(valueObject.get("O_TEL_NUM"));
            persistent.addParameter(valueObject.get("O_USER_NAME"));
            persistent.addParameter(valueObject.get("O_DIVISION"));
            persistent.addParameter(valueObject.get("O_POSITION"));
            persistent.addParameter(valueObject.get("O_HP"));
            persistent.addParameter(valueObject.get("O_EMAIL"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> margeOrigCorpData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	
	/**
     * 협력사 담당자 정보 조회
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private ValueObject getEmpChk(String pur_cont_mod_id, String c_hp) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		ValueObject user = storage.getUser();
		StrBuffer sqlstr = new StrBuffer();
		try{
			
			sqlstr.append(" select NVL(COUNT(*),0) EMP_CHK_CNT                  \n");
			sqlstr.append("   from CONT_PUR_SUPP A, SUPP_CHARGE B               \n");
			sqlstr.append("  where A.pur_cont_mod_id = '"+pur_cont_mod_id+"'    \n");
			sqlstr.append("    and A.sign_seq = '2'                             \n");
			sqlstr.append("    AND A.CORP_ID = B.CORP_ID                        \n");
			sqlstr.append("    AND b.hp1 || b.hp2 || b.hp3 = replace('"+c_hp+"','-','')                      \n");
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}
		catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			//WAFLogger.debug(sqlstr.toString());
			//WAFLogger.debug("WAFSQLException : " + se.getMessage());
		}
		finally {
			
			if( valueobject == null ) valueobject = new ValueObject();
			//WAFLogger.debug("### valueobject size : " + valueobject.size());
			return valueobject;
		}
	}
	/**
     * 원사업자 업체 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean margeContCorpData(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO CONT_PUR_SUPP T1 USING (									\n");
        	sqlstr.append("          SELECT ? AS	PUR_CONT_MOD_ID	\n");
        	sqlstr.append("                ,? AS	CORP_ID	\n");
        	sqlstr.append("                ,'' AS	VENDCD	\n");
        	sqlstr.append("                ,? AS	MEMBER_NAME	\n");
        	sqlstr.append("                ,? AS	BOSS_NAME	\n");
        	sqlstr.append("                ,'' AS	POST_CODE	\n");
        	sqlstr.append("                ,? AS	ADDRESS	\n");
        	sqlstr.append("                ,? AS	TEL_NUM	\n");
        	sqlstr.append("                ,'' AS	MEMBER_SLNO	\n");
        	sqlstr.append("                ,? AS	USER_NAME	\n");
        	sqlstr.append("                ,? AS	DIVISION	\n");
        	sqlstr.append("                ,? AS	POSITION	\n");
        	sqlstr.append("                ,? AS	HP	\n");
        	sqlstr.append("                ,? AS	EMAIL	\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.PUR_CONT_MOD_ID = T1.PUR_CONT_MOD_ID AND T.CORP_ID = T1.CORP_ID)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET 				\n");
//        	sqlstr.append("  				, T1.VENDCD	 =	, T.VENDCD	\n");
        	sqlstr.append("  				 T1.MEMBER_NAME	 =	 T.MEMBER_NAME	\n");
        	sqlstr.append("  				, T1.BOSS_NAME	 =	 T.BOSS_NAME	\n");
//        	sqlstr.append("  				, T1.POST_CODE	 =	 T.POST_CODE	\n");
        	sqlstr.append("  				, T1.ADDRESS	 =	 T.ADDRESS	\n");
        	sqlstr.append("  				, T1.TEL_NUM	 =	 T.TEL_NUM	\n");
//        	sqlstr.append("  				, T1.MEMBER_SLNO	 =	 T.MEMBER_SLNO	\n");
        	sqlstr.append("  				, T1.USER_NAME	 =	 T.USER_NAME	\n");
        	sqlstr.append("  				, T1.DIVISION	 =	 T.DIVISION	\n");
        	sqlstr.append("  				, T1.POSITION	 =	 T.POSITION	\n");
        	sqlstr.append("  				, T1.HP	 =	 T.HP	\n");
        	sqlstr.append("  				, T1.EMAIL	 =	 T.EMAIL	\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (T1.PUR_CONT_MOD_ID, T1.CORP_ID, T1.SIGN_SEQ, T1.VENDCD, T1.MEMBER_NAME, T1.BOSS_NAME, T1.POST_CODE, T1.ADDRESS, T1.TEL_NUM, T1.MEMBER_SLNO, T1.USER_NAME, T1.DIVISION, T1.POSITION, T1.HP, T1.EMAIL)									\n");
        	sqlstr.append("			VALUES (T.PUR_CONT_MOD_ID, T.CORP_ID, 2, T.VENDCD, T.MEMBER_NAME, T.BOSS_NAME, T.POST_CODE, T.ADDRESS, T.TEL_NUM, T.MEMBER_SLNO, T.USER_NAME, T.DIVISION, T.POSITION, T.HP, T.EMAIL)											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.addParameter(valueObject.get("C_CORP_ID"));
            persistent.addParameter(valueObject.get("C_MEMBER_NAME"));
            persistent.addParameter(valueObject.get("C_BOSS_NAME"));
            persistent.addParameter(valueObject.get("C_ADDRESS"));
            persistent.addParameter(valueObject.get("C_TEL_NUM"));
            persistent.addParameter(valueObject.get("C_USER_NAME"));
            persistent.addParameter(valueObject.get("C_DIVISION"));
            persistent.addParameter(valueObject.get("C_POSITION"));
            persistent.addParameter(valueObject.get("C_HP"));
            persistent.addParameter(valueObject.get("C_EMAIL"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> margeContCorpData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
     * 계약정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContInfo(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {

        	
        	
        	sqlstr.append("		UPDATE PUR2000T SET 				\n");
        //	sqlstr.append("       ORD_DOC_NO  =  '"+ valueObject.get("con_doc_no") +"' \n");		// 계약번호
        //   	sqlstr.append("      , DEPT_NAME  =  '"+ valueObject.get("dept_name") +"' \n");		//현장명
        //   	sqlstr.append("      , DEALER_NM  =  '"+ valueObject.get("dealer_nm") +"' \n");		//발주처
        	sqlstr.append("       MOD_GB  =  '"+ valueObject.get("mod_gb") +"' \n");		//계약명
        //	sqlstr.append("       ORD_DOC_NO  =  '"+ valueObject.get("con_doc_no") +"' \n");		// 계약번호
        //  sqlstr.append("      , DEPT_NAME  =  '"+ valueObject.get("dept_name") +"' \n");		//현장명
        //  sqlstr.append("      , DEALER_NM  =  '"+ valueObject.get("dealer_nm") +"' \n");		//발주처
        if(!valueObject.get("ord_con_date_year").isEmpty()){
        	sqlstr.append("      , ORD_CON_DATE  =  '"+ valueObject.get("ord_con_date_year") +"-"+ valueObject.get("ord_con_date_month") +"-"+ valueObject.get("ord_con_date_day") +"' \n");		//계약일
        }
        if(!valueObject.get("ord_start_date_year").isEmpty()){
        	sqlstr.append("      , ORD_START_DATE  =  '"+ valueObject.get("ord_start_date_year") +"-"+ valueObject.get("ord_start_date_month") +"-"+ valueObject.get("ord_start_date_day") +"' \n");		//공사기간 시작일
        }
        if(!valueObject.get("ord_end_date_year").isEmpty()){
        	sqlstr.append("      , ORD_END_DATE  =  '"+ valueObject.get("ord_end_date_year") +"-"+ valueObject.get("ord_end_date_month") +"-"+ valueObject.get("ord_end_date_day") +"' \n");		//공사기간 종료일
        }        	
        	sqlstr.append("      , CST_DOC_NAME  =  ? \n");		//계약명
        	sqlstr.append("      , LEAD_PLACE  =  ? \n");		//공사장소
        	
        	sqlstr.append("      , LEAD_COND_GB  =  ? \n");		//원도급공사명
        	sqlstr.append("      , ORD_AMT  =  ? \n");		// 계약금액
        	sqlstr.append("      , ORD_SUPPLY_AMT  =  ? \n");		//하도급공사명
        	sqlstr.append("      , ORD_SURTAX_AMT  =  ? \n");		//지급방법 현금
        	sqlstr.append("      , PREPAY_AMT_RATE  =  ? \n");		// 지금방법 어음
        	sqlstr.append("      , PREPAY_AMT  =  ? \n");		// 공급가액
        	sqlstr.append("      , MID_AMT_RATE  =  ? \n");		// 공급가액 과세
        	sqlstr.append("      , MID_AMT  =  ? \n");		// 공급가액 비과세
        	sqlstr.append("      , RMDR_AMT_RATE  =  ? \n");		// 계약부과세
        	sqlstr.append("      , RMDR_AMT  =  ? \n");		// 노무비
        	sqlstr.append("      , CON_GUAR_AMT_RATE  =  ? \n");		// 선급금율
        	sqlstr.append("      , CON_GUAR_TERM  =  ? \n");		// 선급금액
        	sqlstr.append("      , CON_GUAR_AMT  =  ? \n");		// 선급금 공급가액
        	sqlstr.append("      , DEF_GUAR_AMT_RATE  =  ? \n");		// 선급금 과세
        	sqlstr.append("      , DEF_GUAR_AMT  =  ? \n");		// 선급금 면세
        	sqlstr.append("      , DEF_GUAR_TERMS  =  ? \n");		// 선급금 부과세
        	sqlstr.append("      , DEF_GUAR_TERMS_TXT  =  ? \n");		// 선급금 부과세
        	sqlstr.append("      , DEF_GUAR_TERMS2  =  ? \n");		// 선급금 부과세
        	sqlstr.append("      , DEF_GUAR_TERMS_TXT2  =  ? \n");		// 선급금 부과세
        	sqlstr.append("      , DEL_RATE_GB  =  ? \n");		// 계약이행 보증금율

        	sqlstr.append("      , TEMPLATE_CD  =  ? \n");		// 계약서류코드
        	sqlstr.append("      , CONT_HASH  =  ? \n");		// 지체상급율
        	sqlstr.append("      , CURRENCY_GB  =  ? \n");		// 통화구분 저장
        	        	 	
        	sqlstr.append("      , ELEC_CON_YN  =  ? \n");		// 전자계약여부        	
        	sqlstr.append("      , STATUS_CD  =  '10' \n");		// 계약진행상태
        	/*분개 유형*/
        	sqlstr.append(" 	 , ACCT_GB  =  ? 	\n");	
        	sqlstr.append(" 	 , PAY_GROUP  =  ? 	\n");	
        	sqlstr.append(" 	 , GUA_GB  =  ? 	\n");	
        	sqlstr.append(" 	 , PAYMENT_TERMS  =  ? 	\n");	
        	sqlstr.append(" 	 , PAY_ITEM  =  ? 	\n");	
        	sqlstr.append(" 	 , COST_CODE  =  ? 	\n");	
        	sqlstr.append(" 	 , COST_TYPE  =  ? 	\n");	

        	sqlstr.append(" 	 , CON_DISPLAY_TXT  =  ? 	\n");	
        	sqlstr.append(" 	 , CON_IN_DATE  =  SYSDATE  	\n");	

        	sqlstr.append("      , PREPAY_GUAR_AMT	 =  ?	\n");									//추가 (2014-09-24) 	
        	sqlstr.append("      , PREPAY_GUAR_AMT_RATE	 =  ?	\n");									//추가 (2014-09-24) 	
        	
        	sqlstr.append("      , STAMP_DUTY_GB	 =  ?	\n");
        	
        	//승인자 추가
        	sqlstr.append("      , CON_CONFIRM_ID	 =  ?	\n");									//추가 (2014-11-05) 	        	
        	sqlstr.append("      , CON_CONFIRM_NAME	 =  ?	\n");									//추가 (2014-11-05) 	        	
        	
        	
			sqlstr.append("      , IN_SPE_INFO 		  =  ? 		\n");						//추가(2014-11-26)
			sqlstr.append("      , IN_CHG_INFO		  =  ? 	\n");							//추가(2014-11-26)
			sqlstr.append("      , FRST_CON_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , FRST_EXE_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , CHG_CON_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , CHG_EXE_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			
        	sqlstr.append(" 	 , UPD_BY  =  ?  	\n");	
        	sqlstr.append(" 	 , UPD_DATE  =  SYSDATE  	\n");	
        	
			sqlstr.append("      , PREPAY_PAYMENT_NM  =  ? 		\n");
			sqlstr.append("      , MID_PAYMENT_NM	  =  ? 	\n");
			sqlstr.append("      , RMDR_PAYMENT_NM  =  ? 		\n");	        	        	
			
			sqlstr.append("      , CON_CHARGE_ID  =  ? 		\n");	        	        	
			sqlstr.append("      , CON_CHARGE_NAME  =  ? 		\n");	
			sqlstr.append("      , ERP_CHECK_YN  =  ? 		\n");      //2016.11.8 
         	sqlstr.append("		WHERE  PUR_CONT_MOD_ID = "+ valueObject.get("PUR_CONT_MOD_ID") +"	\n");
         	
            persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(valueObject.get("cst_doc_name"));		//계약명
        	persistent.addParameter(valueObject.get("lead_place"));		//공사장소
        	
        	persistent.addParameter(valueObject.get("lead_cond_gb"));		//원도급공사명
        	persistent.addParameter(valueObject.get("ord_amt").replaceAll(",",""));
            // 계약금액 2019.05.14 0원계약(감액)일때 undefine오류발생 
        	persistent.addParameter(valueObject.get("ord_supply_amt").replaceAll(",",""));		//하도급공사명
        	persistent.addParameter(valueObject.get("ord_surtax_amt").replaceAll(",",""));		//지급방법 현금
        	persistent.addParameter(valueObject.get("prepay_amt_rate").replaceAll(",",""));		// 지금방법 어음
        	persistent.addParameter(valueObject.get("prepay_amt").replaceAll(",",""));		// 공급가액
        	persistent.addParameter(valueObject.get("mid_amt_rate").replaceAll(",",""));		// 공급가액 과세
        	persistent.addParameter(valueObject.get("mid_amt").replaceAll(",",""));		// 공급가액 비과세
        	persistent.addParameter(valueObject.get("rmdr_amt_rate").replaceAll(",",""));		// 계약부과세
        	persistent.addParameter(valueObject.get("rmdr_amt").replaceAll(",",""));		// 노무비
        	persistent.addParameter(valueObject.get("con_guar_amt_rate").replaceAll(",",""));		// 선급금율
        	persistent.addParameter(valueObject.get("con_guar_term"));		// 선급금액
        	persistent.addParameter(valueObject.get("con_guar_amt").replaceAll(",",""));		// 선급금 공급가액
        	persistent.addParameter(valueObject.get("def_guar_amt_rate").replaceAll(",",""));		// 선급금 과세
        	persistent.addParameter(valueObject.get("def_guar_amt").replaceAll(",",""));		// 선급금 면세
        	persistent.addParameter(valueObject.get("def_guar_terms"));		// 선급금 부과세
        	persistent.addParameter(valueObject.get("def_guar_terms_txt"));		// 선급금 부과세
        	persistent.addParameter(valueObject.get("def_guar_terms2"));		// 선급금 부과세
        	persistent.addParameter(valueObject.get("def_guar_terms_txt2"));		// 선급금 부과세
        	persistent.addParameter(valueObject.get("del_rate_gb"));		// 계약이행 보증금율

        	persistent.addParameter(valueObject.get("TEMPLATE_CD"));		// 계약서류코드
        	persistent.addParameter(valueObject.get("CONT_HASH"));		// 지체상급율
        	persistent.addParameter(valueObject.get("CURRENCY_GB"));		// 통화구분 저장
        	persistent.addParameter(valueObject.get("ELEC_CON_YN"));		// 전자계약여부        	

        	/*분개 유형*/
        	persistent.addParameter(valueObject.get("ACCT_GB"));	
        	persistent.addParameter(valueObject.get("PAY_GROUP"));	
        	persistent.addParameter(valueObject.get("GUA_GB"));	
        	persistent.addParameter(valueObject.get("PAYMENT_TERMS"));	
        	persistent.addParameter(valueObject.get("PAY_ITEM"));	
        	persistent.addParameter(valueObject.get("COST_CODE"));	
        	persistent.addParameter(valueObject.get("COST_TYPE"));	

        	persistent.addParameter(valueObject.get("CON_DISPLAY_TXT"));	

        	persistent.addParameter(valueObject.get("PREPAY_GUAR_AMT").replaceAll(",",""));									//추가 (2014-09-24) 	
        	persistent.addParameter(valueObject.get("PREPAY_GUAR_AMT_RATE"));									//추가 (2014-09-24) 	
        	
        	persistent.addParameter(valueObject.get("p_issue_gb"));
        	
        	//승인자 추가
        	persistent.addParameter(valueObject.get("CON_CONFIRM_ID"));									//추가 (2014-11-05) 	        	
        	persistent.addParameter(valueObject.get("CON_CONFIRM_NAME"));									//추가 (2014-11-05) 	        	
        	
        	persistent.addParameter(valueObject.get("IN_SPE_INFO"));													//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("IN_CHG_INFO"));													//추가 (2014-11-26)  	        	
        	persistent.addParameter(valueObject.get("FRST_CON_AMT").replaceAll(",",""));								//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("FRST_EXE_AMT").replaceAll(",",""));								//추가 (2014-11-26)  	        	
        	persistent.addParameter(valueObject.get("CHG_CON_AMT").replaceAll(",",""));									//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("CHG_EXE_AMT").replaceAll(",",""));									//추가 (2014-11-26) 	  
        	
        	persistent.addParameter(storage.getUser().get("acct_id"));	            

        	persistent.addParameter(valueObject.get("PREPAY_PAYMENT_NM"));									//추가 (2014-11-14) 	        	
        	persistent.addParameter(valueObject.get("MID_PAYMENT_NM"));									//추가 (2014-11-14) 	        	
        	persistent.addParameter(valueObject.get("RMDR_PAYMENT_NM"));									//추가 (2014-11-14) 	   
        	
        	persistent.addParameter(valueObject.get("O_ACCT_ID"));
        	persistent.addParameter(valueObject.get("O_USER_NAME"));	
        	
        	persistent.addParameter(valueObject.get("ERP_CHECK_YN"));	
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveContInfo() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error("Parameter : " + persistent.getParameters());
            WAFLogger.error(e);
            throw e;
        }
    }	
	
	/**
     * 인지세 List
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private RowSet getStampList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        
        try{
			
        	sqlstr.append("    SELECT                                                                                                      \n");  
			sqlstr.append("            STAMP_DUTY_ID                                                                                       \n");
			sqlstr.append("           ,STAMP_DUTY_ISSUE_GB                                                                                 \n");    
			sqlstr.append("           ,STAMP_DUTY_NO                                                                                       \n");    
			sqlstr.append("           ,STAMP_DUTY_TAX                                                                                      \n");    
			sqlstr.append("           ,STAMP_DUTY_DATE                                                                                     \n");    
			sqlstr.append("           ,REMARK                                                                                              \n");
			sqlstr.append("           ,(SELECT DECODE(MAX(AA.FILE_ID), NULL, '파일첨부', '1') FROM PUB0510T AA, PUB0500T BB                \n");
			sqlstr.append("        		   WHERE AA.FILE_ID = BB.FILE_ID AND FILE_GB = 'PUR_STAMP_DUTY'                                    \n");
			sqlstr.append("        		   AND ID = B.STAMP_DUTY_ID) AS FILE_YN                                                            \n");
			sqlstr.append("           ,(SELECT MAX(AA.FILE_ID) FROM PUB0510T AA, PUB0500T BB                                               \n");
			sqlstr.append("        		   WHERE AA.FILE_ID = BB.FILE_ID AND FILE_GB = 'PUR_STAMP_DUTY'                                    \n");
			sqlstr.append("        		   AND ID = B.STAMP_DUTY_ID) AS FILE_ID                                                            \n");                                  
			sqlstr.append("    FROM PUR2000T A,                                                                                            \n"); 
			sqlstr.append("         STAMP_DUTY B                                                                                           \n"); 
			sqlstr.append("    WHERE A.PUR_CONT_MOD_ID = B.CONT_MOD_ID                                                                     \n");
			sqlstr.append("    AND B.STAMP_DUTY_TYPE(+) = '10'                                                                             \n");
			sqlstr.append("    AND A.PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'                                                 \n");
			sqlstr.append("    ORDER BY STAMP_DUTY_ID DESC								                                 				   \n");
			
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	/**
     * 서약서 List
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private RowSet getPromiseList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        
        try{
			
        	sqlstr.append("  SELECT PROMISE_DOC_ID,                                                 \n");
        	sqlstr.append("         PROMISE_DOC_TYPE_GB,                                            \n");
        	sqlstr.append("         REMARK,                                                         \n");
        	sqlstr.append("         (SELECT DECODE (MAX (AA.FILE_ID), NULL, '파일첨부', '1')        	\n");
        	sqlstr.append("            FROM PUB0510T AA, PUB0500T BB                                \n");
        	sqlstr.append("           WHERE     AA.FILE_ID = BB.FILE_ID                             \n");
        	sqlstr.append("                 AND FILE_GB = 'PUR_PROMISE_DOC'                         \n");
        	sqlstr.append("                 AND ID = B.PROMISE_DOC_ID)    AS FILE_YN,               \n");
        	sqlstr.append("         (SELECT MAX (AA.FILE_ID)                                        \n");
        	sqlstr.append("            FROM PUB0510T AA, PUB0500T BB                                \n");
        	sqlstr.append("           WHERE     AA.FILE_ID = BB.FILE_ID                             \n");
        	sqlstr.append("                 AND FILE_GB = 'PUR_PROMISE_DOC'                         \n");
        	sqlstr.append("                 AND ID = B.PROMISE_DOC_ID)    AS FILE_ID                \n");
        	sqlstr.append("    FROM PUR2000T A, PROMISE_DOC_TYPE B                                  \n");
        	sqlstr.append("   WHERE     A.PUR_CONT_MOD_ID = B.CONT_MOD_ID                           \n");
        	sqlstr.append("         AND B.PROMISE_DOC_TYPE(+) = '10'                                \n");
        	sqlstr.append("         AND A.PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'     \n");
        	sqlstr.append("ORDER BY PROMISE_DOC_ID DESC                                             \n");
			
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	/**
	 * 하도급대금 연동제  List
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private RowSet getPricesysList(ValueObject param) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		
		StrBuffer sqlstr = new StrBuffer();
		
		try{
			sqlstr.append("  SELECT PRICE_SYS_ID,                                                 	\n");
			sqlstr.append("         PRICE_SYS_YN,                                            		\n");
			sqlstr.append("         OBJECT_NM,                                                      \n");
			sqlstr.append("         ITEM_NM,                                                        \n");
			sqlstr.append("         DECODE(PRICE_SYS_YN, '01', DECODE(SYS_REASON, '00', REMARK, ETEC_EBID.F_GET_CODE_NAME('PUR_PRICESYS_RS', B.SYS_REASON)), SYS_REASON) AS SYS_REASON,  \n");
			sqlstr.append("         CONT_MOD_ID,                                                    \n");
			sqlstr.append("         CORP_REG_NO,                                                    \n");
			sqlstr.append("         CORP_NAME,                                                     	\n");
			sqlstr.append("         DECODE(PRICE_SYS_YN, '01', '1', '') AS FILE_YN  		        \n");
			sqlstr.append("    FROM PUR2000T A, PRICE_SYS B                                  		\n");
			sqlstr.append("   WHERE     A.PUR_CONT_MOD_ID = B.CONT_MOD_ID                           \n");
			sqlstr.append("         AND A.PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'     \n");
			
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
		}
		catch (WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);  
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			WAFLogger.error("### WAFSQLException : " + se.getMessage());
		}
		finally {    
			if(rowSet == null){
				rowSet = new RowSetImpl();
			}
		}
		return rowSet;
	}

	/**
     * 인지세 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
/*	public boolean mergeStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {

			sqlstr.append(" MERGE	INTO STAMP_DUTY A													\n");
			sqlstr.append("     USING 	(																\n");
			sqlstr.append("             SELECT	PUR_CONT_MOD_ID	AS CONT_MOD_ID							\n");
			sqlstr.append("             		,ORD_DOC_NO	AS CONT_NO									\n");
			sqlstr.append("                     ,MOD_NO	AS MOD_NO										\n");
			sqlstr.append("                     ,CST_DOC_NAME AS CONT_NAME								\n");
			sqlstr.append("                     ,ORD_AMT AS CONT_AMT									\n");
			sqlstr.append("             FROM	PUR2000T												\n");
			sqlstr.append("             WHERE	PUR_CONT_MOD_ID = ?										\n");
			sqlstr.append("             ) B																\n");
			sqlstr.append("     ON		(A.CONT_MOD_ID = B.CONT_MOD_ID AND A.STAMP_DUTY_TYPE = '10')	\n");
			sqlstr.append(" WHEN MATCHED THEN															\n");
			sqlstr.append(" 	UPDATE	SET																\n");
			sqlstr.append("             STAMP_DUTY_ISSUE_GB = ?											\n");
			sqlstr.append("             ,CORP_REG_NO = ?												\n");
			sqlstr.append("             ,CORP_NAME = ?													\n");
			sqlstr.append("             ,CONT_NO = B.CONT_NO											\n");
			sqlstr.append("             ,MOD_NO = B.MOD_NO												\n");
			sqlstr.append("             ,STAMP_DUTY_NO = ?												\n");
			sqlstr.append("             ,CONT_NAME = B.CONT_NAME										\n");
			sqlstr.append("             ,CONT_AMT = B.CONT_AMT											\n");
			sqlstr.append("             ,STAMP_DUTY_TAX = ?												\n");
			sqlstr.append("             ,STAMP_DUTY_DATE = ?											\n");
			sqlstr.append("             ,REMARK = ?														\n");
			sqlstr.append(" WHEN NOT MATCHED THEN														\n");
			sqlstr.append(" 	INSERT	(																\n");
			sqlstr.append("             STAMP_DUTY_ID													\n");
			sqlstr.append("             ,STAMP_DUTY_TYPE												\n");
			sqlstr.append("             ,STAMP_DUTY_ISSUE_GB											\n");
			sqlstr.append("             ,CONT_MOD_ID													\n");
			sqlstr.append("             ,CORP_REG_NO													\n");
			sqlstr.append("             ,CORP_NAME														\n");
			sqlstr.append("             ,CONT_NO														\n");
			sqlstr.append("             ,MOD_NO															\n");
			sqlstr.append("             ,STAMP_DUTY_NO													\n");
			sqlstr.append("             ,CONT_NAME														\n");
			sqlstr.append("             ,CONT_AMT														\n");
			sqlstr.append("             ,STAMP_DUTY_TAX													\n");
			sqlstr.append("             ,STAMP_DUTY_DATE												\n");
			sqlstr.append("             ,REMARK															\n");
			sqlstr.append(" 			)																\n");
			sqlstr.append("     VALUES	(																\n");
			sqlstr.append("     		STAMP_DUTY_S.NEXTVAL											\n");
			sqlstr.append("             ,'10'															\n");
			sqlstr.append("             ,? --STAMP_DUTY_ISSUE_GB										\n");
			sqlstr.append("             ,? --CONT_MOD_ID												\n");
			sqlstr.append("             ,? --CORP_REG_NO												\n");
			sqlstr.append("             ,? --CORP_NAME													\n");
			sqlstr.append("             ,B.CONT_NO --CONT_NO											\n");
			sqlstr.append("             ,B.MOD_NO --MOD_NO												\n");
			sqlstr.append("             ,? --STAMP_DUTY_NO												\n");
			sqlstr.append("             ,B.CONT_NAME --CONT_NAME										\n");
			sqlstr.append("             ,B.CONT_AMT --CONT_AMT											\n");
			sqlstr.append("             ,? --STAMP_DUTY_TAX												\n");
			sqlstr.append("             ,? --STAMP_DUTY_DATE											\n");
			sqlstr.append("             ,? --REMARK														\n");
			sqlstr.append("     		)																\n");
			    		
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("stamp_duty_issue_gb"));
			persistent.addParameter(valueObject.get("O_REG_NO"));
			persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
			persistent.addParameter(valueObject.get("stamp_duty_no"));
			persistent.addParameter(valueObject.get("stamp_duty_tax").replaceAll(",", ""));
			persistent.addParameter(valueObject.get("stamp_duty_date").replaceAll("-", ""));
			persistent.addParameter(valueObject.get("remark"));
			persistent.addParameter(valueObject.get("stamp_duty_issue_gb"));
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("O_REG_NO"));
			persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
			persistent.addParameter(valueObject.get("stamp_duty_no"));
			persistent.addParameter(valueObject.get("stamp_duty_tax").replaceAll(",", ""));
			persistent.addParameter(valueObject.get("stamp_duty_date").replaceAll("-", ""));
			persistent.addParameter(valueObject.get("remark"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> mergeStampDuty() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}*/
	
	/**
	 * 인지세 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
/*	public boolean deleteStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			
			sqlstr.append(" BEGIN														\n");
			sqlstr.append(" 	DELETE	FROM PUB0500T									\n");
			sqlstr.append(" 	WHERE	FILE_ID IN 	(									\n");
			sqlstr.append(" 	                    SELECT	FILE_ID						\n");
			sqlstr.append(" 	                    FROM	PUB0510T					\n");
			sqlstr.append(" 	                    WHERE	FILE_GB = 'PUR_STAMP_DUTY'	\n");
			sqlstr.append(" 	                    AND		ID = ?						\n");
			sqlstr.append(" 	                    );									\n");
			sqlstr.append(" 	DELETE	FROM PUB0510T									\n");
			sqlstr.append(" 	WHERE	FILE_GB = 'PUR_STAMP_DUTY'						\n");
			sqlstr.append(" 	AND		ID = ?;											\n");
			sqlstr.append(" 	DELETE	FROM STAMP_DUTY									\n");
			sqlstr.append(" 	WHERE	CONT_MOD_ID	= ?									\n");
			sqlstr.append(" 	AND		STAMP_DUTY_TYPE = '10';							\n");
			sqlstr.append(" END;														\n");
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> deleteStampDuty() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}	
*/	
	private ArrayList makeContPdf(ValueObject parms) {
		ArrayList<ValueObject> pdfList = new ArrayList();
		if(params.get("PDF_FILE_CNT").equals("")){
			return null;
		}
		String[] template_seq = null;
		String[] template_name = null;
		String[] contHtml = null;
		String[] contHtmlRm = null;
		if(params.getInt("PDF_FILE_CNT")>1){
			template_seq = (String[])params.getObject("template_seq");
			template_name = (String[])params.getObject("template_name");
			contHtml = (String[])params.getObject("cont_html");
			contHtmlRm = (String[])params.getObject("cont_html_rm");
		}else{
			template_seq = new String[]{params.get("template_seq")};
			template_name = new String[]{params.get("template_name")};
			contHtml = new String[]{params.get("cont_html")};
			contHtmlRm = new String[] {params.get("cont_html_rm")};
		}
        try {
			for(int i = 0 ; i < params.getInt("PDF_FILE_CNT"); i++){
				String check_yn = params.get("check_yn_"+template_seq[i]);
				if(check_yn.equals("A")||check_yn.equals("Y")){
					ValueObject vo = new ValueObject();
					vo.set("cont_no", params.get("PUR_CONT_MOD_ID"));
					vo.set("cont_chasu", params.get("MOD_NO"));
					vo.set("true_random", params.get("TRUE_RANDOM"));
					vo.set("file_seq", i);
					vo.set("folder", "purchase");
					vo.set("cont_userno", params.get("ORD_DOC_NO"));
					vo.set("html", contHtmlRm[i]);
					PDFMaker pdfMaker = new PDFMaker();
					ValueObject pdfInfo = pdfMaker.makeContPdf(vo);
					
					if (!pdfInfo.isNull(0)){
						pdfInfo.set("cont_no", params.get("PUR_CONT_MOD_ID"));
						pdfInfo.set("template_name", template_name[i]);
						pdfInfo.set("file_seq", i);
						pdfList.add(pdfInfo);
					}else{
						return null;
					}					
				}
			}
		
			return pdfList;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> makeContPdf() : " + e.getMessage());
            WAFLogger.error(e);
            return null;
        }	
	}
	
	/**
     * 계약 htm 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void CONT_PUR_HTML_del(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_HTML						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> CONT_PUR_HTML_del() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 htm 등록
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean CONT_PUR_HTML_Save(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
    		String[] template_seq = null;
    		String[] contHtml = null;
    		String[] template_name = null;
    		String[] template_style = null;
    		String[] option_yn = null;
    		if(valueObject.getInt("PDF_FILE_CNT")>1){
    			template_seq = (String[])valueObject.getObject("template_seq");
       			template_name = (String[])valueObject.getObject("template_name");
       			template_style = (String[])valueObject.getObject("template_style");
       			option_yn = (String[])valueObject.getObject("option_yn");
    			contHtml = (String[])valueObject.getObject("cont_html");
    		}else{
    			template_seq = new String[]{valueObject.get("template_seq")};
    			template_name = new String[]{valueObject.get("template_name")};
    			template_style = new String[]{valueObject.get("template_style")};
    			option_yn = new String[]{valueObject.get("option_yn")};
    			contHtml = new String[]{valueObject.get("cont_html")};
    		}   

			for(int i = 0 ; i < valueObject.getInt("PDF_FILE_CNT"); i++){
				sqlstr.append("INSERT INTO CONT_PUR_HTML (PUR_CONT_MOD_ID, SEQ, TEMPLATE_NAME, TEMPLATE_HTML, TEMPLATE_STYLE, CHECK_YN, OPTION_YN)						\n");
				sqlstr.append("VALUES(?, ?, ?, ?, ?, ?, ?)					\n");
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
	            persistent.addParameter(template_seq[i]);
	            persistent.addParameter(template_name[i]);
	            persistent.addParameter(contHtml[i]);
	            persistent.addParameter(template_style[i]);
	            persistent.addParameter(valueObject.get("check_yn_"+template_seq[i]));
	            persistent.addParameter(option_yn[i]);
	            persistent.execute();  
	            sqlstr.reset();
	            persistent.clearParameters();
			}
      	
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> CONT_PUR_HTML_Save() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 파일 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void CONT_PUR_FILE_del(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_FILE						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> CONT_PUR_FILE_del() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 파일 등록
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public String CONT_PUR_FILE_Save(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {

        	sqlstr.append("INSERT INTO CONT_PUR_FILE (PUR_CONT_MOD_ID, FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT, FILE_SIZE, FILE_NAME, AUTO_YN, CRE_BY, CRE_DATE)						\n");
        	sqlstr.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("cont_no"));
            persistent.addParameter(valueObject.get("file_seq"));
            persistent.addParameter(valueObject.get("file_name"));
            persistent.addParameter(valueObject.get("file_path"));
            persistent.addParameter(valueObject.get("file_ext"));
            persistent.addParameter(valueObject.get("file_size"));
            persistent.addParameter(valueObject.get("template_name"));
            persistent.addParameter("Y");
            persistent.addParameter("");
            persistent.execute();
            persistent.clearParameters();
            
            return valueObject.get("file_hash");
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> CONT_PUR_FILE_Save() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }		
	/**
     * 을사에 싸인 요청
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean ContStatusUp(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	//WAFLogger.debug("Exception : " + valueObject.get("pConClsYn"));
	        	sqlstr.append("UPDATE PUR2000T SET  	\n");
	        	sqlstr.append(" STATUS_CD = ?					\n");
	        	if (!valueObject.get("pConClsYn").equals(null)){
	        		sqlstr.append(", CON_CLS_YN = '"+ valueObject.get("pConClsYn") +"'					\n");
	        	}    	        	
	        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(valueObject.get("pStatusCD"));                  
	            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));

            persistent.execute();


            //권준호 부장님 프로시저 콜
      		CallableStatement cs 	= null;
    		String pssql			= "";
    		
        	pssql = "{call SP_REG_PSR_STATUS('','25','3','',?,?,'',?,SYSDATE,'',?,?)}";
        	cs = super.conn.getConnection().prepareCall(pssql);
        	cs.setString(1, params.get("DEMANDED_ID"));
        	cs.setString(2, params.get("PUR_CONT_ID"));
        	cs.setString(3, storage.getUser().get("acct_id"));
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.registerOutParameter(5, java.sql.Types.VARCHAR);
            cs.execute();
            
            //WAFLogger.debug("VO_SUCCESS_CD : " + cs.getString(4));

            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> ContStatusUp() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	

	/**
	 * 보증 대상리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getWarrList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        
     //   String sqlstr = "";
        /*
		보증서 종류
		  002 : 계약이행
		  003 : 하자
		  004 : 선금
		  005 : 근로자재해
		  39 : 하도급대금지급         * 
         * */
        StrBuffer sqlstr = new StrBuffer();
        try{
        	if(param.get("MOD_NO").equals("0")){
	        	sqlstr.append("	SELECT  WARR_GUBUN	\n");
	        	sqlstr.append("	    , WARR_GUBUN_NM	\n");
	        	sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
	        	sqlstr.append("	    , MAX(WARR_SEQ) AS WARR_SEQ	\n");
	        	sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
	        	sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
	        	sqlstr.append("	    , MAX(CAUSECODE) AS CAUSECODE	\n");
	        	sqlstr.append("	    , MAX(CAUSECODE_NM) AS CAUSECODE_NM	\n");
	        	sqlstr.append("	    , MAX(GUAR_ORGAN_CODE) AS GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	    , MAX(WARR_GOFFICE_ID) AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	    , MAX(WARR_GOFFICE) AS WARR_GOFFICE	\n");
	        	sqlstr.append("	    , MAX(GUAR_STATUS) AS GUAR_STATUS	\n");
	        	sqlstr.append("	    , MAX(GUAR_STATUS_NM) AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	    , MAX(PAPERDATE) AS PAPERDATE	\n");
	        	sqlstr.append("	    , MAX(WARR_WGNO) AS WARR_WGNO	\n");
	        	sqlstr.append("	    , MAX(WARR_SDATE) AS WARR_SDATE	\n");
	        	sqlstr.append("	    , MAX(WARR_EDATE) AS WARR_EDATE	\n");
	        	sqlstr.append("	    , MAX(GUAR_ONOFF_NM) AS GUAR_ONOFF_NM	\n");
	        	sqlstr.append("	    , MAX(GUAR_ONOFF_YN) AS GUAR_ONOFF_YN	\n");
	        	sqlstr.append("	    , MAX(GRTREQNO) AS GRTREQNO	\n");
	        	sqlstr.append("	     FROM (	\n");
	        	sqlstr.append("	              SELECT WARR_GUBUN	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\n");
	        	sqlstr.append("	                   , MOD_NO	\n");
	        	sqlstr.append("	                   , WARR_SEQ	\n");
	        	sqlstr.append("	                   , WARR_WAMT	\n");
	        	sqlstr.append("	                   , VIEW_DIV	\n");
	        	sqlstr.append("	                   , CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',CAUSECODE) AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('GUARORGAN',GUAR_ORGAN_CODE) AS WARR_GOFFICE	\n");
	        	sqlstr.append("	                   , GUAR_STATUS	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('GUAR_STATUS',GUAR_STATUS) AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	                   , PAPERDATE	\n");
	        	sqlstr.append("	                   , WARR_WGNO	\n");
	        	sqlstr.append("	                   , WARR_SDATE	\n");
	        	sqlstr.append("	                   , WARR_EDATE 	\n");
	        	sqlstr.append("	                   , DECODE(GUAR_ONOFF_YN,'Y','온라인','N','오프라인') AS GUAR_ONOFF_NM	\n");
	        	sqlstr.append("	                   , GUAR_ONOFF_YN 	\n");
	        	sqlstr.append("	                   , GRTREQNO 	\n");
	        	sqlstr.append("	                FROM CONT_PUR_WARR 	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              UNION ALL	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
	        	sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
	        	sqlstr.append("	                   , B.MOD_NO	\n");
	        	sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
	        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
	        	sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
	        	sqlstr.append("	                   , '01' AS CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','01') AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
	        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
	        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");
	        	sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
	        	sqlstr.append("	                	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              UNION ALL	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
	        	sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
	        	sqlstr.append("	                   , B.MOD_NO	\n");
	        	sqlstr.append("	                   , 2 AS WARR_SEQ	\n");
	        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
	        	sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
	        	sqlstr.append("	                   , '01' AS CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','01') AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
	        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
	        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");
	        	sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.CON_GB = '15'	\n"); //표준하도급계약서인 경우 
	        	sqlstr.append("	                	\n");	        	
	        	sqlstr.append("	     )	\n");
	        	sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM	\n");
	        	sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
	        	sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\n");
        	}else{	//변경 계약일경우 
        		
        		if(!param.get("WON_WARR").equals("S")){
        			sqlstr.append("	SELECT  WARR_GUBUN	\n");
        			sqlstr.append("	    , WARR_GUBUN_NM	\n");
        			sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
        			sqlstr.append("	    , WARR_SEQ	\n");
        			sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
        			sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
        			sqlstr.append("	    , CAUSECODE AS CAUSECODE	\n");
        			sqlstr.append("	    , CAUSECODE_NM AS CAUSECODE_NM	\n");
        			sqlstr.append("	    , MAX(GUAR_ORGAN_CODE) AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	    , MAX(WARR_GOFFICE_ID) AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	    , MAX(WARR_GOFFICE) AS WARR_GOFFICE	\n");
        			sqlstr.append("	    , MAX(GUAR_STATUS) AS GUAR_STATUS	\n");
        			sqlstr.append("	    , MAX(GUAR_STATUS_NM) AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	    , MAX(PAPERDATE) AS PAPERDATE	\n");
        			sqlstr.append("	    , MAX(WARR_WGNO) AS WARR_WGNO	\n");
        			sqlstr.append("	    , MAX(WARR_SDATE) AS WARR_SDATE	\n");
        			sqlstr.append("	    , MAX(WARR_EDATE) AS WARR_EDATE	\n");
        			sqlstr.append("	    , MAX(GUAR_ONOFF_NM) AS GUAR_ONOFF_NM	\n");
        			sqlstr.append("	    , MAX(GUAR_ONOFF_YN) AS GUAR_ONOFF_YN	\n");
        			sqlstr.append("	    , MAX(GRTREQNO) AS GRTREQNO	\n");
        			sqlstr.append("	     FROM (	\n");
        			sqlstr.append("	              SELECT WARR_GUBUN	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , WARR_SEQ	\n");
        			sqlstr.append("	                   , WARR_WAMT	\n");
        			sqlstr.append("	                   , VIEW_DIV	\n");
        			sqlstr.append("	                   , CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',CAUSECODE) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUARORGAN',GUAR_ORGAN_CODE) AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , GUAR_STATUS	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUAR_STATUS',GUAR_STATUS) AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , PAPERDATE	\n");
        			sqlstr.append("	                   , WARR_WGNO	\n");
        			sqlstr.append("	                   , WARR_SDATE	\n");
        			sqlstr.append("	                   , WARR_EDATE 	\n");
        			sqlstr.append("	                   , DECODE(GUAR_ONOFF_YN,'Y','온라인','N','오프라인') AS GUAR_ONOFF_NM	\n");
        			sqlstr.append("	                   , GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , GRTREQNO 	\n");
        			sqlstr.append("	                FROM CONT_PUR_WARR 	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , B.MOD_NO	\n");
        			sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(SUBSTR(MOD_GB,1,1),1,'02',2,'08') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(SUBSTR(MOD_GB,1,1),1,'02',2,'08')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND SUBSTR(MOD_GB,1,1) !=0	\n");
        			sqlstr.append("	    \n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , B.MOD_NO	\n");
        			sqlstr.append("	                   , 2 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(SUBSTR(MOD_GB,2,1),1,'03',2,'09') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(SUBSTR(MOD_GB,2,1),1,'03',2,'09')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND SUBSTR(MOD_GB,2,1) !=0	\n");
        			sqlstr.append("	    \n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , B.MOD_NO	\n");
        			sqlstr.append("	                   , 3 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND B.CON_GB = '15'	\n");
        			sqlstr.append("	     )	\n");
        			sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM, WARR_SEQ, CAUSECODE, CAUSECODE_NM	\n");
        			sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
        			sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\n");        		
        			
        		}else{
        			
        			sqlstr.append("	SELECT  WARR_GUBUN	\n");
        			sqlstr.append("	    , WARR_GUBUN_NM	\n");
        			sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
        			sqlstr.append("	    , WARR_SEQ	\n");
        			sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
        			sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
        			sqlstr.append("	    , CAUSECODE AS CAUSECODE	\n");
        			sqlstr.append("	    , CAUSECODE_NM AS CAUSECODE_NM	\n");
        			sqlstr.append("	    , MAX(GUAR_ORGAN_CODE) AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	    , MAX(WARR_GOFFICE_ID) AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	    , MAX(WARR_GOFFICE) AS WARR_GOFFICE	\n");
        			sqlstr.append("	    , MAX(GUAR_STATUS) AS GUAR_STATUS	\n");
        			sqlstr.append("	    , MAX(GUAR_STATUS_NM) AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	    , MAX(PAPERDATE) AS PAPERDATE	\n");
        			sqlstr.append("	    , MAX(WARR_WGNO) AS WARR_WGNO	\n");
        			sqlstr.append("	    , MAX(WARR_SDATE) AS WARR_SDATE	\n");
        			sqlstr.append("	    , MAX(WARR_EDATE) AS WARR_EDATE	\n");
        			sqlstr.append("	    , MAX(GUAR_ONOFF_NM) AS GUAR_ONOFF_NM	\n");
        			sqlstr.append("	    , MAX(GUAR_ONOFF_YN) AS GUAR_ONOFF_YN	\n");
        			sqlstr.append("	    , MAX(GRTREQNO) AS GRTREQNO	\n");
        			sqlstr.append("	     FROM (	\n");
        			sqlstr.append("	              SELECT WARR_GUBUN	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , WARR_SEQ	\n");
        			sqlstr.append("	                   , WARR_WAMT	\n");
        			sqlstr.append("	                   , VIEW_DIV	\n");
        			sqlstr.append("	                   , CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',CAUSECODE) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUARORGAN',GUAR_ORGAN_CODE) AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , GUAR_STATUS	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('GUAR_STATUS',GUAR_STATUS) AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , PAPERDATE	\n");
        			sqlstr.append("	                   , WARR_WGNO	\n");
        			sqlstr.append("	                   , WARR_SDATE	\n");
        			sqlstr.append("	                   , WARR_EDATE 	\n");
        			sqlstr.append("	                   , DECODE(GUAR_ONOFF_YN,'Y','온라인','N','오프라인') AS GUAR_ONOFF_NM	\n");
        			sqlstr.append("	                   , GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , GRTREQNO 	\n");
        			sqlstr.append("	                FROM CONT_PUR_WARR 	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , B.MOD_NO	\n");
        			sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB) AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB)) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '00', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , B.MOD_NO	\n");
        			sqlstr.append("	                   , 3 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB) AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB)) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')   \n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND B.CON_GB = '15'	\n");
        			sqlstr.append("	     )	\n");
        			sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM, WARR_SEQ, CAUSECODE, CAUSECODE_NM	\n");
        			sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
        			sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\n");        		
        		}
        	}
        	//WAFLogger.debug("### MOD_NO : " + sqlstr);
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	
	/**
     * 계약 정보 완료 프로세스
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean endContProc(Connection connection, ValueObject valueObject, boolean signChk) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();
        	boolean bSuccess = false;
        	
        	//오프라인 계약 완료 일경우 서명 저장을 안함
        	if(signChk){
        		CONT_SignOk(super.conn.getConnection(), params);	//서명저장
        	}
        	
			params.set("pStatusCD", "50");
			params.set("pConClsYn", "9");
			ContStatusUp(super.conn.getConnection(), params);	//서명 완료

			//계약 완료 데이터 이동
			bSuccess = endMoveData(super.conn.getConnection(), params);	//보증 데이터 생성
			
			if( bSuccess ){				
				ValueObject contVo = new ValueObject();
								
				//2019.10.18 변경구분 및 사업자번호 변경여부 조회
				ValueObject contChgVo = getContChgInfo(params.get("PUR_CONT_MOD_ID"));

				//계약정보 조회
				//2019.10.18 타절계약 사업자번호 변경된 경우 BID_SUPP에서 EXCHANGE 값을 가져올수 없기 때문에 최초 계약(MOD_NO=0) 기준으로 가져오도록 수정
				if(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y")){
					contVo = getConfinfoChg(params.get("PUR_CONT_MOD_ID"));
					
					if(contVo.get("ORG_ERP_CD_PARTNER").equals("")){
						super.storage.setSuccess(false);	
						throw new Exception("변경전 거래처코드(ORG_ERP_CD_PARTNER) 등록이 안되어 있습니다.");
					} else if(contVo.get("ERP_CD_PARTNER").equals("")){
						super.storage.setSuccess(false);	
						throw new Exception("변경후 거래처코드(ERP_CD_PARTNER) 등록이 안되어 있습니다.");
					}
				} else {
					contVo = getConfinfo(params.get("PUR_CONT_MOD_ID"));
				}
				
				if( !contVo.get("ORG_CD_COOP").equals("") && !(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y"))) { //2019.10.18 타절계약 사업자번호 변경 안된 경우 추가
					contVo.set("CD_COOP",contVo.get("ORG_CD_COOP"));
				}else {
					updatePUR1000T(super.conn.getConnection(), contVo);
				}
				
				
				if( contVo.get("CD_COOP").equals("") ) {
					super.storage.setSuccess(false);	
					throw new Exception("ERP 공사거래처(CD_COOP) 등록이 안되어 있습니다.");
				}
				
				//ERP 전송여부 Y 일때만 전송 NVL(SUB2000T.ERP_CHECK_YN,'Y')  2019.3.31 N일때도 전송 
				//if( contVo.get("ERP_CHECK_YN").equals("Y") ) {
					//ERP연계 키값 없을때 조회하여 UPDATE (NO_CONT)
					if( "".equals(contVo.get("NO_ECUT")) ) {  //2019.4.26 신규일때만 생성. 변경계약일때는 기존값 가져감. 신용길부장님 요청 
						String no_ecut = getErpNoCont(contVo.get("YM_ECUT"));
						contVo.set("NO_ECUT", no_ecut);
						updatePUR2000T(super.conn.getConnection(), contVo);
					}
					
					RowSet conDetail = new RowSetImpl();
					
					//플랜트는 일괄SUM하여 데이터 전송
					if( contVo.get("DEMANDED_KIND").equals("B") ) {
						conDetail = getConfDetailEPMCS(contVo);
					}else {
						conDetail = getConfDetail(contVo);
					}
					
					//2019.04.26 내역없으면 오류 
					if( conDetail == null || conDetail.size() == 0) {
						super.storage.setSuccess(false);	
						throw new Exception("계약내역이 없습니다");
					}
					
							
					
					/*
					 * 분산트렌젝션문제로 새로운 커넥션으로 ERP insert
					 */
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();
						
						
						//최초계약시 INSERT
						if(contVo.get("MOD_NO").equals("0")) {
							InterfacePurERPiU.insertErpContMst(erpConn.getConnection(), contVo);
							if( conDetail != null && conDetail.size() > 0) {
								for( int i = 0; i < conDetail.size(); i++ ) {
									ValueObject detailVo = RowSetUtility.getValueObject(conDetail.getRow(i)); 
									InterfacePurERPiU.insertErpContDet(erpConn.getConnection(), detailVo, contVo);
									InterfacePurERPiU.insertErpContDetConst(erpConn.getConnection(), detailVo, contVo);
								}
							}
							
						//변경계약시 UPDATE
						}else {
							InterfacePurERPiU.updateErpContMst(erpConn.getConnection(), contVo);
							InterfacePurERPiU.deleteErpContDet(erpConn.getConnection(), contVo);
							InterfacePurERPiU.deleteErpContDetConst(erpConn.getConnection(), contVo);
							if( conDetail != null && conDetail.size() > 0) {
								for( int i = 0; i < conDetail.size(); i++ ) {
									ValueObject detailVo = RowSetUtility.getValueObject(conDetail.getRow(i));
									detailVo.set("YM_ECUT", contVo.get("YM_ECUT_ORD"));
									detailVo.set("NO_ECUT", contVo.get("NO_ECUT_ORD"));
									InterfacePurERPiU.insertErpContDet(erpConn.getConnection(), detailVo, contVo);
									InterfacePurERPiU.insertErpContDetConst(erpConn.getConnection(), detailVo, contVo);
								}
							}							
						}
						
						//이력 INSERT
						InterfacePurERPiU.insertErpContMstRev(erpConn.getConnection(), contVo);
						if( conDetail != null && conDetail.size() > 0) {
							for( int i = 0; i < conDetail.size(); i++ ) {
								ValueObject detailVo = RowSetUtility.getValueObject(conDetail.getRow(i)); 
								InterfacePurERPiU.insertErpContDetRev(erpConn.getConnection(), detailVo, contVo);
								InterfacePurERPiU.insertErpContDetConstRev(erpConn.getConnection(), detailVo, contVo);
							}
						}
						
						InterfacePurERPiU.updateWM_G_REQUEST_H(erpConn.getConnection(), contVo);
						InterfacePurERPiU.updateWM_Z_ETEC_REQUEST_H(erpConn.getConnection(), contVo);
						
						//2019.10.18 타절계약 사업자번호 변경시 추가
						if(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y")){
							InterfacePurERPiU.execCoopChg(erpConn.getConnection(), contVo);
						}
					}catch(Exception e) {
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
				}
				
			//}  ERP_CHECK_YN='N'일때도 전송 
			
			
			//WAFLogger.debug("SUCCESS STATUS ======= : " + bSuccess);

			//2015.01.08 이인오 인지세 추가
			/*if( params.get("stamp_duty_issue_gb").equals("10") || params.get("stamp_duty_issue_gb").equals("20") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else if( params.get("stamp_duty_issue_gb").equals("00") ) {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}*/

			boolean successPms = false;
			//계약 완료 후 다른 데이터 베이스 프로시저 호출 : 2014-09-25 김명호 추가
			successPms = endMoveFyByPmsData(super.conn.getConnection(), params);	//보증 데이터 생성

			return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> endContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }		
	/**
     * 갑) 서명 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean CONT_SignOk(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE CONT_PUR_SUPP SET  	\n");
        	sqlstr.append(" SIGN_DATE = SYSDATE					\n");
        	sqlstr.append(" ,SIGN_DN = ?					\n");
        	sqlstr.append(" ,SIGN_DATA = ?					\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
        	sqlstr.append(" 	AND SIGN_SEQ=1						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("CONT_SIGNDN"));
            persistent.addParameter(valueObject.get("CONT_SIGNDATA"));
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> CONT_SignOk() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }		
	
	/**
     * 계약 완료 데이터 이동 처리
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean endMoveData(Connection connection, ValueObject valueObject) throws Exception {

        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        String sqlStr = "";
        try {
        	sqlStr = "{call SP_PUR1000T_OTEHR_SET_ERP(?, ?, ?)}";
        	
        	//WAFLogger.debug("SQL : " + sqlStr); 
        	
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, valueObject.get("PUR_CONT_MOD_ID"));
        	//WAFLogger.debug("1 : " + valueObject.get("PUR_CONT_MOD_ID")); 

            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));            
            
            return true;
        }
        catch(Exception e) {
            super.doException(e);
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("errMsg",cs.getString(3));
            super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return false;
        }
    }			
	/**
     * 계약 완료 데이터 이동 처리
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean endMoveFyByPmsData(Connection connection, ValueObject valueObject) throws Exception {

        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        String sqlStr = "";
        try {
        	sqlStr = "{call ETECEPC.PO_CONFIRM_NOTTFY_BY_PMS(?, ?, ?)}";
        	
        	//WAFLogger.debug("SQL : " + sqlStr); 
        	
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, valueObject.get("ORD_DOC_NO"));
            cs.setString(2, valueObject.get("MOD_NO"));
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(3));   
            
            //WAFLogger.debug("1 : " + cs.getString(3)); 
            
            return true;
        }
        catch(Exception e) {
            super.doException(e);
//            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
//            rtnVo.set("errMsg",cs.getString(3));
            super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return false;
        }
    }			
	public void Send(ValueObject params, String event) throws Exception {
		MessageSend messageSend = new MessageSend(storage);
	
		ValueObject contSendInfo = new ValueObject();
		contSendInfo = getContRequestValueObject(params.get("PUR_CONT_MOD_ID"));

		if(!contSendInfo.get("C_EMAIL").isEmpty()){
			boolean email = false;
	 		StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();
	        if("SIGN_CALL".equals(event)){//견적의뢰
	        	mailtitle.append("* 계약서 서명요청 메일입니다.");
	            mailtitlein.append("* 계약서 서명요청 메일입니다.");
	         	mailContent.append(contSendInfo.get("C_MEMBER_NAME")+"님께 계약서 서명 요청드립니다.<br>보증서 발급 후 법인공인인증서로 전자서명 부탁드립니다.<br>");
	         	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	         	mailContent.append("<br>");
	         	mailContent.append("1.프로젝트 : " + contSendInfo.get("DEPT_NAME") + "<br>");
	         	mailContent.append("2.계약번호 : " + contSendInfo.get("ORD_DOC_NO") + "<br>");
	         	mailContent.append("3.계약명 : " + contSendInfo.get("CST_DOC_NAME") + "<br>");
	         	mailContent.append("4.계약담당자 : " +contSendInfo.get("O_DIVISION")+" / "+ contSendInfo.get("O_USER_NAME") +" (연락처 : " + contSendInfo.get("O_TEL_NUM") + ")<br>");
	         	mailContent.append("</font>");
	        	email = true;	    
	 		}
	        if(email){
	        	messageSend.sendEmail(contSendInfo.get("C_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent);
	        }
		}
 		//WAFLogger.debug("담당자 HP : " + contSendInfo.get("O_HP").replace("-", ""));
		if(!contSendInfo.get("C_HP").isEmpty()){
	 		//WAFLogger.debug("C_HP : "+contSendInfo.get("C_HP"));
	 		String smsContent = "";
	 		if("SIGN_CALL".equals(event)){//서명요청
	 			smsContent = WAFMessages.getMessage("Contract.Signing.Portalrequest");
	 		}

	 		//WAFLogger.debug("담당자 HP : " + contSendInfo.get("O_HP").replace("-", ""));
	        
	        //WAFLogger.debug("받는사람 HP : " + contSendInfo.get("C_HP").replace("-", ""));
	        
	        String se_phon_no = contSendInfo.get("O_TEL_NUM").replace("-", ""); //보내는사람 2015.9.21변경 O_HP
	        String re_phon_no = contSendInfo.get("C_HP").replace("-", ""); //받는사람
	        if(!smsContent.isEmpty()){
	        	messageSend.sendSMS(re_phon_no, se_phon_no, storage.getUser().get("ACCT_ID"), smsContent); //2015.9.21 계약자정보로 변경 
	        }
		}
	}	
	
	/**
	 * ERP 업체 등록 확인
	 * @param Params
	 * @return
	 */
	private Integer getErpSuppCount(String corpid) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        Integer wCount = 0;
        try{
        	sqlstr.append("SELECT	COUNT(*) AS CNT												\n");
        	sqlstr.append("FROM		SUPP_INFO A													\n");
        	sqlstr.append("			,ETEC_ERPIF.ACZ10800 B										\n");
        	sqlstr.append("WHERE	REPLACE(A.CORP_REG_NO,'-','') = REPLACE(B.DCERTI_NO,'-','')	\n");
        	sqlstr.append("AND		B.ADDITEM01 = 'A'											\n");
        	sqlstr.append("AND      B.ADDITEM20 = 'P'               											\n"); //2019.02.26
        	sqlstr.append("AND		A.CORP_ID = ?												\n");
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(corpid);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
                wCount = valueobject.getInt(0);
            }
            
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
        }
        return wCount;
	}
	
	private String getOrgCdCoop(String purContModId) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		StrBuffer sqlstr = new StrBuffer();
		String cdCoop = "";
		try{
			sqlstr.append("SELECT  ERP_CD_COOP						\n");
			sqlstr.append("FROM    PUR1000T A						\n");
			sqlstr.append("        ,PUR2000T B						\n");
			sqlstr.append("WHERE   A.PUR_CONT_ID = B.PUR_CONT_ID	\n");
			sqlstr.append("AND     B.PUR_CONT_MOD_ID = ?			\n");
			
			search.setStatement(sqlstr.toString());
			search.addParameter(purContModId);
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
				cdCoop = valueobject.get("ERP_CD_COOP");
			}
			
		}
		catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}
		finally {
			
			if( valueobject == null ) valueobject = new ValueObject();
		}
		return cdCoop;
	}
	
	/**
     * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean deleteContProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

			CONT_PUR_HTML_del(super.conn.getConnection(), params);	//ContHTML 삭제
			//WAFLogger.debug("ContHTML 삭제");

			CONT_PUR_FILE_del(super.conn.getConnection(), params);	//자동생성파일 삭제
			//WAFLogger.debug("자동생성파일 삭제");

			delContCorpData(super.conn.getConnection(), params);		//계약업체정보 저장
			//WAFLogger.debug("계약업체정보 삭제");

			delContItem(super.conn.getConnection(), params);		//계약상품 정보 저장
			//WAFLogger.debug("계약상품 정보 삭제");
			
			//발주 의뢰번호가 있을경우 타 테이블 업데이트 처리
			if(!valueObject.get("pDemID").isEmpty()){
				//2019.04.15 단가계약일경우 제외
				Search search = new SearchImpl();
		        RowSet listRowSet = null;
		        StrBuffer sqlstr = new StrBuffer();
		        ValueObject rowObject = null;
		        String DIV_PO_YN = "";
		        try {
		        	sqlstr.append(" 	SELECT NVL(DIV_PO_YN,'N') DIV_PO_YN	                                    \n");
		        	sqlstr.append(" 	  FROM PUR2000T A	                                                    \n");
		        	sqlstr.append("   WHERE 1=1								                                    \n");
		    		sqlstr.append("   	AND A.PUR_CONT_MOD_ID= '"+ params.get("PUR_CONT_MOD_ID") +"'			\n"); 
		             
		    		search.setStatement(sqlstr.toString());
		            listRowSet = search.execute();
		            if(listRowSet.size() != 0){
		            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
		            	DIV_PO_YN = rowObject.get("DIV_PO_YN");
		            }
		        }catch(Exception e){
					e.printStackTrace();
		        }
		            
		        if (DIV_PO_YN.equals("N")) {
					upDemandidProc(super.conn.getConnection(), params);  //단가계약 아닐때만 삭제 
				}				
			}
			
			//2016.4.28 대금계획 삭제
			delContPayplan(super.conn.getConnection(), params);	

			delContInfo(super.conn.getConnection(), params);		//계약 정보 저장
			//WAFLogger.debug("계약정보 삭제");

			return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> deleteContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	
	/**
     * 계약 업체 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContCorpData(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_SUPP						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> delContCorpData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContInfo(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  PUR2000T						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> delContInfo() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	
	public void delContPayplan(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_PAYPLAN						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
        	persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> delContPayplan() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 정보 내역 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContItem(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_ITEM						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> delContItem() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }		
	
	/**
     * demanded_id가 있을경우(변경 발주) 타 테이블 업데이트 처리
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean upDemandidProc(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	// 2019.11.28 전자조달에서 변경계약 생성 후 변경계약 삭제 시 원발주내역이 삭제되는 문제가 발생하여 우선 백업테이블로 이관 추가 Start
        	sqlstr.append("INSERT INTO PMM_DEMANDED_REQUEST_DEL \n");
        	sqlstr.append("   (DEMANDED_ID, DEMANDED_NO, REV, DEMANDED_DATE, DEMANDED_TITLE,  \n");
        	sqlstr.append("    DEMANDED_KIND, DEPT_CD, CURRENCY_GB, EMP_NO, DELIVERY_CONDITION,  \n");
        	sqlstr.append("    APP_DATE, APP_NO, PAY_ITEM, COST_CODE, COST_TYPE,  \n");
        	sqlstr.append("    RECEIPT_DATE, SITEDESC_FLAG, SITEDESCCHRGPRSNID, SITEDESCTELNO, SITEDESCDATE,  \n");
        	sqlstr.append("    SITEDESCPLACE, SITEDESCEMAIL, PUREMPNO, INNER_REMARKS, TBE_FLAG,  \n");
        	sqlstr.append("    TBE_DATE, OUTER_REMARKS, REQUEST_DATE, DOC_MR_IN, DOC_FIELD_MANUAL_IN,  \n");
        	sqlstr.append("    DOC_ETC_IN, DOC_MR_OUT, DOC_FIELD_MANUAL_OUT, DOC_ETC_OUT, FILE_GB_IN,  \n");
        	sqlstr.append("    FILE_GB_OUT, CONT_AMT, ACT_AMT, ACT_RATE, ONBID_YN,  \n");
        	sqlstr.append("    DELI_START_DT, DELI_END_DT, DELI_END_PLACE, RTN_REASON, RTN_DATE,  \n");
        	sqlstr.append("    RTN_EMPNO, RTN_EMPNM, RECEIPT_NO, OUT_ITEM_CD, DEMANDED_STATUS,  \n");
        	sqlstr.append("    EBID_STATUS_CD, PR_TYPE, PAY_DIV, CRE_BY, CRE_DATE,  \n");
        	sqlstr.append("    UPD_BY, UPD_DATE, ERP_NO_CORD, ERP_SIGN_DOC_ID, ERP_SIGN_YEAR,  \n");
        	sqlstr.append("    ERP_YM_REQU, ERP_NO_REQU, MOD_GB, BEF_BUG_AMT, AFT_BUG_AMT,  \n");
        	sqlstr.append("    BEF_CONT_DATE, AFT_CONT_DATE, BEF_ETC, AFT_ETC, P_REMARK,  \n");
        	sqlstr.append("    S_REMARK, PUR_CONT_MOD_ID) \n");
        	sqlstr.append("SELECT DEMANDED_ID, DEMANDED_NO, REV, DEMANDED_DATE, DEMANDED_TITLE,  \n");
        	sqlstr.append("    DEMANDED_KIND, DEPT_CD, CURRENCY_GB, EMP_NO, DELIVERY_CONDITION,  \n");
        	sqlstr.append("    APP_DATE, APP_NO, PAY_ITEM, COST_CODE, COST_TYPE,  \n");
        	sqlstr.append("    RECEIPT_DATE, SITEDESC_FLAG, SITEDESCCHRGPRSNID, SITEDESCTELNO, SITEDESCDATE,  \n");
        	sqlstr.append("    SITEDESCPLACE, SITEDESCEMAIL, PUREMPNO, INNER_REMARKS, TBE_FLAG,  \n");
        	sqlstr.append("    TBE_DATE, OUTER_REMARKS, REQUEST_DATE, DOC_MR_IN, DOC_FIELD_MANUAL_IN,  \n");
        	sqlstr.append("    DOC_ETC_IN, DOC_MR_OUT, DOC_FIELD_MANUAL_OUT, DOC_ETC_OUT, FILE_GB_IN,  \n");
        	sqlstr.append("    FILE_GB_OUT, CONT_AMT, ACT_AMT, ACT_RATE, ONBID_YN,  \n");
        	sqlstr.append("    DELI_START_DT, DELI_END_DT, DELI_END_PLACE, RTN_REASON, RTN_DATE,  \n");
        	sqlstr.append("    RTN_EMPNO, RTN_EMPNM, RECEIPT_NO, OUT_ITEM_CD, DEMANDED_STATUS,  \n");
        	sqlstr.append("    EBID_STATUS_CD, PR_TYPE, PAY_DIV, CRE_BY, CRE_DATE,  \n");
        	sqlstr.append("    UPD_BY, UPD_DATE, ERP_NO_CORD, ERP_SIGN_DOC_ID, ERP_SIGN_YEAR,  \n");
        	sqlstr.append("    ERP_YM_REQU, ERP_NO_REQU, MOD_GB, BEF_BUG_AMT, AFT_BUG_AMT,  \n");
        	sqlstr.append("    BEF_CONT_DATE, AFT_CONT_DATE, BEF_ETC, AFT_ETC, P_REMARK,  \n");
        	sqlstr.append("    S_REMARK, PUR_CONT_MOD_ID \n");
        	sqlstr.append("  FROM PMM_DEMANDED_REQUEST \n");
        	sqlstr.append(" WHERE DEMANDED_ID = ? \n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();

            sqlstr.append("INSERT INTO PMM_DEMANDED_ITEM_DEL \n");
            sqlstr.append("   (ITEM_SEQ, DEMANDED_ID, MATERIAL_CD, REQUEST_DATE, DEMANDED_QTY,  \n");
            sqlstr.append("    CONT_PRI, DEMANDED_PRI, DEMANDED_AMT, SHIPPING_QTY, FINISH_FLAG,  \n");
            sqlstr.append("    PO_NO, YM_REQU, NO_REQU, SQ_REQU) \n");
            sqlstr.append("SELECT ITEM_SEQ, DEMANDED_ID, MATERIAL_CD, REQUEST_DATE, DEMANDED_QTY,  \n");
            sqlstr.append("    CONT_PRI, DEMANDED_PRI, DEMANDED_AMT, SHIPPING_QTY, FINISH_FLAG,  \n");
            sqlstr.append("    PO_NO, YM_REQU, NO_REQU, SQ_REQU \n");
            sqlstr.append("  FROM PMM_DEMANDED_ITEM \n");
            sqlstr.append(" WHERE DEMANDED_ID = ?   \n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();

            sqlstr.append("INSERT INTO PMM_DEMANDED_ITEM_CONST_DEL \n");
            sqlstr.append("   (ITEM_SEQ, DEMANDED_ID, MATERIAL_CD, SQ_CSTR, CD_CSTR,  \n");
            sqlstr.append("    DL_CSTR, NM_CSTR, SIZE_CSTR, UNIT_CSTR, CRE_BY,  \n");
            sqlstr.append("    CRE_DATE) \n");
            sqlstr.append("SELECT ITEM_SEQ, DEMANDED_ID, MATERIAL_CD, SQ_CSTR, CD_CSTR,  \n");
            sqlstr.append("    DL_CSTR, NM_CSTR, SIZE_CSTR, UNIT_CSTR, CRE_BY,  \n");
            sqlstr.append("    CRE_DATE \n");
            sqlstr.append("  FROM PMM_DEMANDED_ITEM_CONST \n");
            sqlstr.append(" WHERE DEMANDED_ID = ? \n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();

            sqlstr.append("INSERT INTO PMM_COPR_DEL \n");
            sqlstr.append("   (DEMANDED_ID, VENDOR_SEQ, VENDOR_CD, VENDOR_NAME, PRE_NM,  \n");
            sqlstr.append("    RECOMMEND_REASON, CHARGE_NAME, CHARGE_TEL, CHARGE_EMAIL, VENDOR_ID,  \n");
            sqlstr.append("    CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, ERP_CD_PARTNER) \n");
            sqlstr.append(" SELECT DEMANDED_ID, VENDOR_SEQ, VENDOR_CD, VENDOR_NAME, PRE_NM,  \n");
            sqlstr.append("    RECOMMEND_REASON, CHARGE_NAME, CHARGE_TEL, CHARGE_EMAIL, VENDOR_ID,  \n");
            sqlstr.append("    CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, ERP_CD_PARTNER \n");
            sqlstr.append("   FROM PMM_COPR \n");
            sqlstr.append("  WHERE DEMANDED_ID = ? \n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            // 2019.11.28 전자조달에서 변경계약 생성 후 변경계약 삭제 시 원발주내역이 삭제되는 문제가 발생하여 우선 백업테이블로 이관 추가 End
            
        	sqlstr.append("DELETE FROM PMM_DEMANDED_REQUEST  	\n");
        	sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM PMM_DEMANDED_ITEM  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM PMM_DEMANDED_ITEM_CONST  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM PMM_COPR  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM ETEC_ERPIF.PMM_DEMANDED_REQUEST  	\n");
        	sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM ETEC_ERPIF.PMM_DEMANDED_ITEM  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
            sqlstr.append("DELETE FROM ETEC_ERPIF.PMM_DEMANDED_ITEM_CONST  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();

            sqlstr.append("DELETE FROM ETEC_ERPIF.PMM_COPR  	\n");
            sqlstr.append("WHERE DEMANDED_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            sqlstr.reset();
            
        	sqlstr.append("UPDATE ETECEPC.MR_REQ_PO SET  	\n");
        	sqlstr.append(" EBID_STATUS = NULL					\n");
        	sqlstr.append("WHERE BUYREQNO = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("pDemID"));
            persistent.execute();
            persistent.clearParameters();
            

            InterfacePurERPiU.updateChangeContReject(valueObject.get("pDemID"), storage.getUser().get("USER_SABUN"), storage.getUser().get("USER_NAME"));
            
            return true;
            
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> upDemandidProc() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }

	/**
     * 인지세 발급 구분 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean updateStampDutyGb(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
	        	sqlstr.append("UPDATE PUR2000T SET  	\n");
	        	sqlstr.append(" STAMP_DUTY_GB = ?					\n");
	        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
	           
	        	persistent.setStatement(sqlstr.toString());
	            
	        	persistent.addParameter(valueObject.get("p_issue_gb"));                  
	            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));

            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updateStampDutyGb() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	

	
	/**
     * 인지세 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	/*public boolean saveStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

			//2015.01.08 이인오 인지세 추가
			if( !params.get("stamp_duty_issue_gb").equals("00") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}

			return true;
        }
        catch(Exception e) {
        	//WAFLogger.debug("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> saveStampDuty() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }*/
	
	
	public boolean updateMoveStatus(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE PUR2000T SET 				\n");
        	//sqlstr.append("       STATUS_CD  =  '00' 				\n");		// 계약대상
        	sqlstr.append("       STATUS_CD  =  DECODE(?, 0, '00', '02') 	\n");		// 2020.03.18 변경계약인 경우 변경계약대상이 아닌 변경계약접수로 이동되는 문제로 수정	
        	sqlstr.append("       WHERE  PUR_CONT_MOD_ID =  ?				\n");		// 계약대상
        	
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(params.get("MOD_NO"));
            persistent.addParameter(params.get("PUR_CONT_MOD_ID"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updateMoveStatus() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	
	/*
	 * 원계약건이 전문건설인지 서울보증인지 확인 
	 * */
	public String getWarrGubun(String purContModId) {
	 	
	 	Search search = new SearchImpl(); 
	 	RowSet rowSet = new RowSetImpl();
	 	StringBuffer sqlstr = new StringBuffer();
	 	String rtnStr = "K";
	 	try {
	 		
	 		sqlstr.append("				SELECT DECODE(GUAR_ORGAN_CODE , '2', 'S','K') AS WARRGUBUN																			\n");
	 		sqlstr.append("				     , WARR_GOFFICE                 																								\n");
	 		sqlstr.append("				FROM CONT_PUR_WARR                        																							\n");
	 		sqlstr.append("				WHERE PUR_CONT_MOD_ID = (                 																							\n");
	 		sqlstr.append("											SELECT PUR_CONT_MOD_ID                    																\n");
	 		sqlstr.append("											FROM PUR2000T                             																\n");
	 		sqlstr.append("											WHERE MOD_NO = 0                          																\n");
	 		sqlstr.append("											AND PUR_CONT_ID = (SELECT MIN(PUR_CONT_ID) FROM PUR2000T WHERE PUR_CONT_MOD_ID = '"+purContModId+"')    \n");
	 		sqlstr.append("										)																											\n");
	 		sqlstr.append("				AND WARR_GUBUN = '1'                      																							\n");
	 		sqlstr.append("				AND VIEW_DIV = 'CONT'                     																							\n");
	 		
	 		search.setStatement(sqlstr);
	 		rowSet = search.execute();
	 		if(rowSet != null && rowSet.size() > 0 ) {
	 			rtnStr = rowSet.getRow(0).get("WARRGUBUN");
	 		}
	 	}
	 	catch (Exception e) {
	         super.doException(e);
	         this.storage.setSuccess(false);
	         this.storage.setMessage(e.getMessage());
	         storage.setDetailMessage(e);
	 	}
	 	return rtnStr;
	 }
	
	private ValueObject getConfinfo(String pur_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	
        	sqlstr.append("SELECT  '01' AS WM_CM00301                                                                                       \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMM') AS YM_ECUT                                                                 \n");
        	sqlstr.append("        ,A.ERP_NO_ECUT AS NO_ECUT                                                                                \n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY                                                  \n");
        	sqlstr.append("        ,NVL(B.ERP_SITE_CD, A.DEPT_CD) AS CD_SITE                                                                \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_ECUT                                                               \n");
        	sqlstr.append("        ,TO_CHAR(A.CRE_DATE,'YYYYMMDD') AS DT_MK_ECUT                                                            \n");
        	sqlstr.append("        ,C.USER_NAME AS NM_MK_ECUT                                                                               \n");
        	sqlstr.append("        ,SUBSTRB(A.CST_DOC_NAME,1, 49) AS NM_ECUT                                                                   \n");
        	sqlstr.append("        ,'01' AS WM_CM00302                                                                                      \n");
        	sqlstr.append("        ,DECODE(A.DIV_PO_YN,'Y','02','01') AS WM_CM00303                                                         \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_START_DATE,'YYYYMMDD') AS DT_DELV                                                         \n");
        	sqlstr.append("        ,SUBSTRB(A.LEAD_PLACE, 1,45) AS LOCATE_DELV                                                              \n");
        	sqlstr.append("        ,A.PAYMENT_TERMS AS WM_CM00206                                                                           \n");
        	sqlstr.append("        ,F_GET_CODE_NAME('SUB_ACMP_PAY_TYPE', A.PAY_COND_GB) AS DESC_CM00206                                     \n");
        	sqlstr.append("        ,DECODE(D.VAT_GB,'INPUT_VAT','Y','N') AS TAG_VAT                                                         \n");
        	sqlstr.append("        ,F.ERP_CD_COOP AS CD_COOP                                                                                \n");
        	sqlstr.append("        ,REPLACE(F.DCERTI_NO,'-','') AS NO_COMPANY                                                               \n");
        	sqlstr.append("        ,F.DEALR_NM AS NM_COOP                                                                                   \n");
        	sqlstr.append("        ,NVL(A.ORD_SUPPLY_AMT * NVL(K.EXCHANGE,1),0) AS AMT_ECUT                                                 \n");
        	sqlstr.append("        ,NVL(A.ORD_SURTAX_AMT * NVL(K.EXCHANGE,1),0) AS VAT_ECUT                                                 \n");
        	sqlstr.append("        ,NVL(A.CON_GUAR_AMT * NVL(K.EXCHANGE,1),0) AS AMT_CONT_ASSURANCE                                         \n");
        	sqlstr.append("        ,NVL(A.CON_GUAR_AMT_RATE,0) AS RT_CONT_ASSURANCE                                                         \n");
        	sqlstr.append("        ,NVL(A.PREPAY_GUAR_AMT * NVL(K.EXCHANGE,1),0) AS AMT_PREPAY_ASSURANCE                                    \n");
        	sqlstr.append("        ,NVL(A.PREPAY_AMT_RATE,0) AS RT_PREPAY_ASSURANCE                                                         \n");
        	sqlstr.append("        ,NVL(A.DEF_GUAR_AMT * NVL(K.EXCHANGE,1),0) AS AMT_WARRANTY                                               \n");
        	sqlstr.append("        ,NVL(A.DEF_GUAR_AMT_RATE,0) AS RT_WARRANTY                                                               \n");
        	sqlstr.append("        ,F_GET_CODE_NAME('DELRATE_GB', A.DEL_RATE_GB) AS DESC_AMT_DELAY                                          \n");
        	sqlstr.append("        ,SUBSTRB(A.CON_DISPLAY_TXT,1,240) AS DESC_ECUT                                                           \n");
        	sqlstr.append("        ,C.USER_SABUN AS ID_USER                                                                                 \n");
        	sqlstr.append("        ,NVL2(J.NO_ECUT_O,'01','') AS WM_CM00301_O                                                               \n");
        	sqlstr.append("        ,NVL2(J.NO_ECUT_O,J.YM_ECUT_O,'') AS YM_ECUT_O                                                           \n");
        	sqlstr.append("        ,J.NO_ECUT_O AS NO_ECUT_O                                                                                \n");
        	sqlstr.append("        ,C.USER_SABUN AS ID_INSERT                                                                               \n");
        	sqlstr.append("        ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                                    	\n");
        	sqlstr.append("        ,'' AS ID_UPDATE                                                                               			\n");
        	sqlstr.append("        ,'' AS DTS_UPDATE                                                    									\n");
        	sqlstr.append("        ,NVL(H.ERP_CODE,'000') AS CURR_SOUR                                                                      \n");
        	sqlstr.append("        ,NVL(K.EXCHANGE,1) AS UNT_CURR                                                                           \n");
        	sqlstr.append("        ,NVL(A.ORD_SUPPLY_AMT,0) AS SAMT_CURR          											                \n");
        	sqlstr.append("        ,NVL(A.ORD_SURTAX_AMT,0) AS VAMT_CURR          											                \n");
        	sqlstr.append("        ,DECODE(NVL(A.CURRENCY_GB,'KRW'),'KRW','001','002') AS WM_CM00011                                        \n");
        	sqlstr.append("        ,C.USER_SABUN AS NO_EMP                                                                                  \n");
        	sqlstr.append("        ,I.USER_SABUN AS NO_EMP_A                                                                                \n");
        	sqlstr.append("        ,I.DEPART_DEPT_CD AS CD_DEPT_A                                                                           \n");
        	sqlstr.append("        ,I.USER_NAME AS NM_KOR_A                                                                                 \n");
        	sqlstr.append("        ,I.USER_SABUN AS ID_USER_A                                                                               \n");
        	sqlstr.append("        ,C.DEPART_DEPT_CD AS CD_DEPT                                                                             \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_AGREE                                                              \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_CONT                                                               \n");
        	sqlstr.append("        ,A.MOD_NO AS NO_SEQ                                                                                      \n");
        	sqlstr.append("        ,A.PUR_CONT_MOD_ID                                                                                       \n");
        	sqlstr.append("        ,A.PUR_CONT_ID                                                                                           \n");
        	sqlstr.append("        ,A.MOD_NO																		                        \n");
        	sqlstr.append("        ,A.MOD_GB																		                        \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_START_DATE,'YYYYMMDD') AS ORD_START_DATE							                        \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_END_DATE,'YYYYMMDD') AS ORD_END_DATE								                        \n");
        	sqlstr.append("        ,A.PAY_DIV																		                        \n");
        	sqlstr.append("        ,A.DEM_ID																		                        \n");
        	sqlstr.append("        ,A.ORD_DOC_NO																	                        \n");
        	sqlstr.append("        ,A.REQ_DOC_NO																	                        \n");
        	sqlstr.append("        ,L.YM_ECUT_ORD																	                        \n");
        	sqlstr.append("        ,L.NO_ECUT_ORD																	                        \n");
        	sqlstr.append("        ,'N' AS TAG_UNITY																						\n");
        	sqlstr.append("        ,A.LEAD_COND_GB AS WM_CM00205																			\n");
        	sqlstr.append("        ,'N' AS PROC_COM																							\n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY_A												\n");
        	sqlstr.append("        ,'N' AS YN_CERT																							\n");
        	sqlstr.append("        ,(SELECT DEMANDED_KIND FROM PMM_DEMANDED_REQUEST WHERE DEMANDED_ID = A.DEM_ID) AS DEMANDED_KIND			\n");
        	sqlstr.append("        ,DECODE(A.PAY_DIV,'1','100','110') AS WM_CM00317									                        \n");
        	sqlstr.append("        ,NVL(A.ERP_CHECK_YN,'Y') AS ERP_CHECK_YN									                                \n");
        	sqlstr.append("        ,REPLACE(A.GUA_GB,'Z99','') AS CD_PROOF									                                \n");
        	sqlstr.append("        ,(SELECT ERP_CD_COOP FROM PUR1000T WHERE PUR_CONT_ID = A.PUR_CONT_ID) AS ORG_CD_COOP					    \n");
        	sqlstr.append("        ,NVL(A.CHG_EXE_AMT * NVL(K.EXCHANGE,1),0) AS AMT_ACT_ETEC                                                \n"); //2019.07.16
        	sqlstr.append("FROM    PUR2000T A                                                                                               \n");
        	sqlstr.append("        ,ETEC_ERPIF.SITE_MAPPING B                                                                               \n");
        	sqlstr.append("        ,PUB0120T C                                                                                              \n");
        	sqlstr.append("        ,(                                                                                                       \n");
        	sqlstr.append("        SELECT  DETAILCD, CHG_CODE10 AS VAT_GB                                                                   \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                     \n");
        	sqlstr.append("        WHERE   BASECD = 'PROOF_CD'                                                                              \n");
        	sqlstr.append("        ) D                                                                                                      \n");
        	sqlstr.append("        ,CONT_PUR_SUPP E                                                                                         \n");
        	sqlstr.append("        ,ETEC_ERPIF.ACZ10800 F                                                                                   \n");
        	sqlstr.append("        ,PUB0120T G                                                                                              \n");
        	sqlstr.append("        ,(                                                                                                       \n");
        	sqlstr.append("        SELECT  DETAILCD, NVL(CHG_CODE10,DETAILCD) AS ERP_CODE                                                   \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                     \n");
        	sqlstr.append("        WHERE   BASECD = '911'                                                                                   \n");
        	sqlstr.append("        ) H                                                                                                      \n");
        	sqlstr.append("        ,PUB0120T I                                                                                              \n");
        	sqlstr.append("        ,(																				                        \n");
        	sqlstr.append("        SELECT  B.PUR_CONT_ID															                        \n");
        	sqlstr.append("                ,TO_CHAR(B.CST_DATE,'YYYYMM') AS YM_ECUT_O								                        \n");
        	sqlstr.append("                ,B.ERP_NO_ECUT AS NO_ECUT_O												                        \n");
        	sqlstr.append("        FROM    PUR2000T A																                        \n");
        	sqlstr.append("                ,PUR2000T B																                        \n");
        	sqlstr.append("        WHERE   A.PUR_CONT_MOD_ID = ?													                        \n");
        	sqlstr.append("        AND     A.PUR_CONT_ID = B.PUR_CONT_ID											                        \n");
        	sqlstr.append("        AND     A.MOD_NO - 1 = B.MOD_NO													                        \n");
        	sqlstr.append("        ) J																				                        \n");        	
        	sqlstr.append("		   ,(																				                        \n");
        	sqlstr.append("        SELECT  B.PUR_CONT_MOD_ID														                        \n");
        	sqlstr.append("                ,A.VENDOR_CD																                        \n");
        	sqlstr.append("                ,A.EXCHANGE																                        \n");
        	sqlstr.append("        FROM    BID_SUPP A																                        \n");
        	sqlstr.append("                ,PUR2000T B																                        \n");
        	sqlstr.append("        WHERE   A.BID_NO = B.BID_NO														                        \n");
        	sqlstr.append("        AND     A.BID_DEG = B.BID_DEG													                        \n");
        	sqlstr.append("        ) K																				                        \n");
        	sqlstr.append("        ,(																				                        \n");
        	sqlstr.append("        SELECT  PUR_CONT_ID																                        \n");
        	sqlstr.append("                ,TO_CHAR(CST_DATE,'YYYYMM') AS YM_ECUT_ORD								                        \n");
        	sqlstr.append("                ,ERP_NO_ECUT AS NO_ECUT_ORD												                        \n");
        	sqlstr.append("        FROM    PUR2000T A																                        \n");
        	sqlstr.append("        WHERE   MOD_NO = 0																                        \n");
        	sqlstr.append("        ) L																				                        \n");        	
        	sqlstr.append("        ,SUPP_INFO M																		                        \n");        	
        	sqlstr.append("WHERE   A.DEPT_CD = B.EBID_SITE_CD(+)                                                                            \n");
        	sqlstr.append("AND     A.CRE_BY = C.ACCT_ID(+)                                                                                  \n");
        	sqlstr.append("AND     A.GUA_GB = D.DETAILCD(+)                                                                                 \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID                                                                    \n");
        	sqlstr.append("AND     E.CORP_ID != 0                                                                                           \n");
        	sqlstr.append("AND     E.CORP_ID = M.CORP_ID                                                                                    \n");
        	sqlstr.append("AND     REPLACE(M.CORP_REG_NO,'-','') = REPLACE(F.DCERTI_NO,'-','')						                        \n");
        	sqlstr.append("AND     F.ADDITEM20 = 'P'               											                                \n"); //2019.02.26
        	sqlstr.append("AND     A.UPD_BY = G.ACCT_ID(+)                                                                                  \n");
        	sqlstr.append("AND     A.CURRENCY_GB = H.DETAILCD(+)                                                                            \n");
        	sqlstr.append("AND     A.CON_CONFIRM_ID = I.ACCT_ID(+)                                                                          \n");
        	sqlstr.append("AND     A.PUR_CONT_ID = J.PUR_CONT_ID(+)                                                                         \n");
        	sqlstr.append("AND     A.PUR_CONT_ID = L.PUR_CONT_ID(+)                                                                         \n");
        	sqlstr.append("AND     E.PUR_CONT_MOD_ID = K.PUR_CONT_MOD_ID(+)											                        \n");
        	sqlstr.append("AND     TO_CHAR(E.CORP_ID) = K.VENDOR_CD(+)                                                                      \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = ?                                                                                    \n");
 
			search.setStatement(sqlstr.toString());
			search.addParameter(pur_cont_mod_id);
			search.addParameter(pur_cont_mod_id);
			System.out.println(sqlstr.toString());
			System.out.println(pur_cont_mod_id);
			
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            throw new Exception(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	private String getErpNoCont(String YM_ECUT) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject vo = new ValueObject();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			sqlstr.append("SELECT  TO_CHAR(NVL(MAX(TO_NUMBER(NO_ECUT)),0) + 1,'FM00000') AS NO_ECUT	\n");
			sqlstr.append("FROM    WM_Z_ETEC_EXECUTE_H@ERPIU										\n");
			sqlstr.append("WHERE   WM_CM00301 = '01'												\n");
			sqlstr.append("AND     YM_ECUT = '"+YM_ECUT+"'											\n");
			
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				vo = RowSetUtility.getValueObject(rowSet.getRow(0));
				rtnStr = vo.get("NO_ECUT");
			}
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}finally {
			return rtnStr;
		}
	}

	public boolean updatePUR2000T(Connection connection, ValueObject vo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" UPDATE  PUR2000T			\n");
        	sqlstr.append(" SET     ERP_NO_ECUT = ?		\n");
        	sqlstr.append(" WHERE   PUR_CONT_MOD_ID = ?	\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(vo.get("NO_ECUT"));
            persistent.addParameter(vo.get("PUR_CONT_MOD_ID"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updatePUR2000T() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }
	
	public boolean updatePUR1000T(Connection connection, ValueObject vo) throws Exception {
		
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			sqlstr.append(" UPDATE  PUR1000T			\n");
			sqlstr.append(" SET     ERP_CD_COOP = ?		\n");
			sqlstr.append(" WHERE   PUR_CONT_ID = ?		\n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(vo.get("CD_COOP"));
			persistent.addParameter(vo.get("PUR_CONT_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updatePUR1000T() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			//WAFLogger.debug("Exception : " + e.getMessage());
			throw e;
		}
	}

	public RowSet getConfDetail(ValueObject vo){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	
        	sqlstr.append("SELECT  '01' AS WM_CM00301                                                                                                             \n");
        	sqlstr.append("        ,'"+vo.get("YM_ECUT")+"' AS YM_ECUT                                                                                            \n");
        	sqlstr.append("        ,'"+vo.get("NO_ECUT")+"' AS NO_ECUT                                                                                            \n");
        	sqlstr.append("        ,TO_CHAR(ROW_NUMBER() OVER(ORDER BY A.MATERIAL_CD),'FM000') AS SQ_ECUT                                                         \n");
        	sqlstr.append("        ,DECODE(LENGTH(A.MATERIAL_CD), 13, SUBSTR(A.MATERIAL_CD, 0 ,6)||SUBSTR(A.MATERIAL_CD, 10, 4), 14, SUBSTR(A.MATERIAL_CD, 0 ,6)||SUBSTR(A.MATERIAL_CD, 11, 3), A.MATERIAL_CD) AS CD_MAT         \n");
        	sqlstr.append("        ,SUBSTRB(B.MATERIAL_NAME,1,100) AS NM_MAT                                                                                                     \n");
        	sqlstr.append("        ,B.KIND_SMALL AS CD_SORT                                                                                                       \n");
        	sqlstr.append("        ,SUBSTRB(C.MATERIALGROUPNAME,1,49) AS NM_SORT                                                                                  \n");
        	sqlstr.append("        ,D.ERP_CODE AS CD_SIZE                                                                                                         \n");
        	sqlstr.append("        ,A.ITEM_SPEC AS SIZE_MAT                                                                                                       \n");
        	sqlstr.append("        ,A.ITEM_UNIT AS UNIT_MAT                                                                                                       \n");
        	sqlstr.append("        ,A.ITEM_QTY AS QTY_ECUT                                                                                                        \n");
        	sqlstr.append("        ,A.ITEM_QTY AS QTY_REMAIN                                                                                                      \n");
        	sqlstr.append("        ,A.ITEM_PRICE * "+vo.get("UNT_CURR")+" AS UNT_ECUT                                                                             \n");
        	sqlstr.append("        ,A.ITEM_AMT * "+vo.get("UNT_CURR")+" AS AMT_ECUT                                                                               \n");
        	sqlstr.append("        ,DECODE(F.VAT_GB,'INPUT_VAT',ROUND(A.ITEM_AMT * "+vo.get("UNT_CURR")+" * 0.1,-1), 0) AS VAT_ECUT                        		  \n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY                                                                        \n");
        	sqlstr.append("        ,'"+vo.get("CD_SITE")+"' AS CD_SITE                                                                                            \n");
        	sqlstr.append("        ,H.YM_REQU AS YM_REQU                                                                                                          \n");
        	sqlstr.append("        ,H.NO_REQU AS NO_REQU                                                                                                          \n");
        	sqlstr.append("        ,H.SQ_REQU AS SQ_REQU                                                                                                          \n");
        	sqlstr.append("        ,I.USER_SABUN AS ID_INSERT                                                                                                     \n");
        	sqlstr.append("        ,TO_CHAR(A.CRE_DATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                                                          \n");
        	sqlstr.append("        ,J.USER_SABUN AS ID_UPDATE                                                                                                     \n");
        	sqlstr.append("        ,TO_CHAR(A.UPD_DATE,'YYYYMMDDHH24MISS') AS DTS_UPDATE                                                                          \n");
        	sqlstr.append("        ,'"+vo.get("UNT_CURR")+"' AS UNT_CURR                                                                                          \n");
        	sqlstr.append("        ,A.ITEM_PRICE AS UNT_E_CURR                                                                                                    \n");
        	sqlstr.append("        ,A.ITEM_AMT AS SAMT_CURR                                                                                                       \n");
        	sqlstr.append("        ,DECODE(F.VAT_GB,'INPUT_VAT',ROUND(A.ITEM_AMT * 0.1,-1), 0) AS VAMT_CURR                                                       \n");
        	sqlstr.append("        ,TO_CHAR(E.ORD_START_DATE,'YYYYMMDD') AS DT_DELV                                                                               \n");
        	sqlstr.append("        ,"+vo.get("PUR_CONT_MOD_ID")+" AS PUR_CONT_MOD_ID                                                               				  \n");
        	sqlstr.append("        ,'001' AS SQ_CSTR                                                                                                                     \n");
        	sqlstr.append("        ,K.CD_CSTR                                                                                                                     \n");
        	sqlstr.append("        ,K.DL_CSTR                                                                                                                     \n");
        	sqlstr.append("        ,K.NM_CSTR                                                                                                                     \n");
        	sqlstr.append("        ,K.SIZE_CSTR                                                                                                                   \n");
        	sqlstr.append("        ,K.UNIT_CSTR                                                                                                                   \n");
        	sqlstr.append("        ,'01' AS WM_CM00304																	                                          \n");
			sqlstr.append("        ,'01' AS WM_CM00007																	                                          \n");
			sqlstr.append("        ,NVL(L.QTY_ACT,0) AS QTY_ACT																	                                  \n");
			sqlstr.append("        ,NVL(L.UNT_ACT,0) AS UNT_ACT																	                                  \n");
			sqlstr.append("        ,NVL(L.AMT_ACT,0) AS AMT_ACT																	                                  \n");
			sqlstr.append("        ,NVL(L.UNT_ACT_CURR,0) AS UNT_ACT_CURR																	                      \n");
			sqlstr.append("        ,NVL(L.AMT_ACT_CURR,0) AS AMT_ACT_CURR																	                      \n");
			sqlstr.append("        ,'000' AS NO_REV																	                                              \n");
        	sqlstr.append("FROM    CONT_PUR_ITEM A                                                                                                                \n");
        	sqlstr.append("        ,PMM_COMM_MATERIAL_MASTER B                                                                                                    \n");
        	sqlstr.append("        ,PMM_MATERIALGROUP C                                                                                                           \n");
        	sqlstr.append("        ,(                                                                                                                             \n");
        	sqlstr.append("        SELECT  DETAILCD                                                                                                               \n");
        	sqlstr.append("                ,DETAILNM                                                                                                              \n");
        	sqlstr.append("                ,CHG_CODE10 AS ERP_CODE                                                                                                \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                                           \n");
        	sqlstr.append("        WHERE   BASECD = 'UNIT_KIND_GB'                                                                                                \n");
        	sqlstr.append("        ) D                                                                                                                            \n");
        	sqlstr.append("        ,PUR2000T E                                                                                                                    \n");
        	sqlstr.append("        ,(                                                                                                                             \n");
        	sqlstr.append("        SELECT  DETAILCD                                                                                                               \n");
        	sqlstr.append("                ,DETAILNM                                                                                                              \n");
        	sqlstr.append("                ,CHG_CODE10 AS VAT_GB                                                                                                  \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                                           \n");
        	sqlstr.append("        WHERE   BASECD = 'PROOF_CD'                                                                                                         \n");
        	sqlstr.append("        ) F                                                                                                                            \n");
        	//sqlstr.append("        ,BID_MASTER G           2019.04.30 삭제 .변경계약일때 적용안됨                                                                                                      \n");
        	sqlstr.append("        ,PMM_DEMANDED_ITEM H                                                                                                           \n");
        	sqlstr.append("        ,PUB0120T I                                                                                                                    \n");
        	sqlstr.append("        ,PUB0120T J                                                                                                                    \n");
        	sqlstr.append("        ,PMM_DEMANDED_ITEM_CONST K                                                                                                     \n");
        	sqlstr.append("    		,(																															  \n");
        	sqlstr.append("    		SELECT  SQ_CSTR, QTY_ACT, UNT_ACT, AMT_ACT, UNT_ACT_CURR, AMT_ACT_CURR														  \n");
        	sqlstr.append("    		FROM    ETEC_ERPIF.BUDGET_ITEM																								  \n");
        	sqlstr.append("    		WHERE   (CD_COMPANY, CD_SITE, SEQ_BUDGET) IN 																				  \n");
        	sqlstr.append("    		        (																													  \n");
        	sqlstr.append("    		        SELECT  CD_COMPANY, CD_SITE, MAX(SEQ_BUDGET) AS SEQ_BUDGET															  \n");
        	sqlstr.append("    		        FROM    ETEC_ERPIF.BUDGET_MST A 																					  \n");
        	sqlstr.append("    		               ,ETEC_ERPIF.SITE_MAPPING B																					  \n");
        	//sqlstr.append("   2019.04.26   WHERE   CD_COMPANY = 'TEST'																						  \n");
        	//sqlstr.append("    		       AND     CD_SITE = 'TEST01'																							  \n");
        	sqlstr.append("                WHERE     A.CD_SITE = B.EBID_SITE_CD(+) 		                              			                             	  \n");
        	sqlstr.append("                 AND     CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'			                              				  \n");
			sqlstr.append("                 AND     NVL(B.ERP_SITE_CD, A.CD_SITE) = '"+vo.get("CD_SITE")+"'												          \n");
        	sqlstr.append("    		        AND     A.DT_AGREE IS NOT NULL																						  \n");
        	sqlstr.append("    		        GROUP BY A.CD_COMPANY, A.CD_SITE																					  \n");
        	sqlstr.append("    		        )																													  \n");
        	sqlstr.append("    		) L        																													  \n");
        	sqlstr.append("WHERE   A.MATERIAL_CD = B.MATERIAL_CD                                                                                                  \n");
        	sqlstr.append("AND     B.KIND_SMALL = C.MATERIALGROUP                                                                                                 \n");
        	sqlstr.append("AND     B.UNIT_KIND = D.DETAILCD(+)                                                                                                    \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID                                                                                          \n");
        	sqlstr.append("AND     E.GUA_GB = F.DETAILCD(+)                                                                                                       \n");
        	//sqlstr.append("AND     E.BID_NO = G.BID_NO                                                                                                            \n");
        	//sqlstr.append("AND     E.BID_DEG = G.BID_DEG                                                                                                          \n");
        	//sqlstr.append("AND     G.DEMANDED_ID = H.DEMANDED_ID                                                                                                  \n");
        	sqlstr.append("AND     E.DEM_ID = H.DEMANDED_ID                                                                                                  \n");
        	sqlstr.append("AND     DECODE(LENGTH(A.MATERIAL_CD), 13, SUBSTR(A.MATERIAL_CD, 0 ,6)||SUBSTR(A.MATERIAL_CD, 10, 4), A.MATERIAL_CD) = H.MATERIAL_CD(+) \n");
        	sqlstr.append("AND     A.CRE_BY = I.ACCT_ID(+)                                                                                                        \n");
        	sqlstr.append("AND     A.UPD_BY = J.ACCT_ID(+)                                                                                                        \n");
        	sqlstr.append("AND     H.ITEM_SEQ = K.ITEM_SEQ(+)                                                                                                     \n");
        	sqlstr.append("AND     K.SQ_CSTR = L.SQ_CSTR(+)                                                                                                       \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = ?                                                                                                          \n");
        	
            search.setStatement(sqlstr.toString());
            search.addParameter(vo.get("PUR_CONT_MOD_ID"));
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
	public RowSet getConfDetailEPMCS(ValueObject vo){
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StrBuffer sqlstr = new StrBuffer();
		
		try {

			sqlstr.append("SELECT  '01' AS WM_CM00301																	\n");
			sqlstr.append("        ,'"+vo.get("YM_ECUT")+"' AS YM_ECUT													\n");
			sqlstr.append("        ,'"+vo.get("NO_ECUT")+"' AS NO_ECUT													\n");
			sqlstr.append("        ,'001' AS SQ_ECUT																	\n");
			sqlstr.append("        ,B.MATERIAL_CD AS CD_MAT																\n");
			sqlstr.append("        ,B.MATERIAL_NAME AS NM_MAT															\n");
			sqlstr.append("        ,B.KIND_SMALL AS CD_SORT																\n");
			sqlstr.append("        ,C.MATERIALGROUPNAME AS NM_SORT														\n");
			sqlstr.append("        ,D.ERP_CODE AS CD_SIZE																\n");
			sqlstr.append("        ,B.MATERIAL_SPEC AS SIZE_MAT															\n");
			sqlstr.append("        ,D.ERP_CODE AS UNIT_MAT																\n");
			sqlstr.append("        ,1 AS QTY_ECUT																		\n");
			sqlstr.append("        ,1 AS QTY_REMAIN																		\n");
			sqlstr.append("        ,SUM(A.ITEM_AMT) * "+vo.get("UNT_CURR")+" AS UNT_ECUT													\n");
			sqlstr.append("        ,SUM(A.ITEM_AMT) * "+vo.get("UNT_CURR")+" AS AMT_ECUT													\n");
			sqlstr.append("        ,DECODE(F.VAT_GB,'INPUT_VAT',ROUND(SUM(A.ITEM_AMT) * "+vo.get("UNT_CURR")+" * 0.1,-1), 0) AS VAT_ECUT	\n");
			sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY								\n");
			sqlstr.append("        ,'"+vo.get("CD_SITE")+"' AS CD_SITE													\n");
			sqlstr.append("        ,'' AS YM_REQU																		\n");
			sqlstr.append("        ,'' AS NO_REQU																		\n");
			sqlstr.append("        ,'001' AS SQ_REQU																	\n");
			sqlstr.append("        ,I.USER_SABUN AS ID_INSERT															\n");
			sqlstr.append("        ,TO_CHAR(E.CRE_DATE,'YYYYMMDDHH24MISS') AS DTS_INSERT								\n");
			sqlstr.append("        ,J.USER_SABUN AS ID_UPDATE															\n");
			sqlstr.append("        ,TO_CHAR(E.UPD_DATE,'YYYYMMDDHH24MISS') AS DTS_UPDATE								\n");
			sqlstr.append("        ,'"+vo.get("UNT_CURR")+"' AS UNT_CURR												\n");
			sqlstr.append("        ,SUM(A.ITEM_AMT) AS UNT_E_CURR														\n");
			sqlstr.append("        ,SUM(A.ITEM_AMT) AS SAMT_CURR														\n");
			sqlstr.append("        ,DECODE(F.VAT_GB,'INPUT_VAT',ROUND(SUM(A.ITEM_AMT) * 0.1,-1), 0) AS VAMT_CURR		\n");
			sqlstr.append("        ,TO_CHAR(E.ORD_START_DATE,'YYYYMMDD') AS DT_DELV										\n");
			sqlstr.append("        ,'"+vo.get("PUR_CONT_MOD_ID")+"' AS PUR_CONT_MOD_ID									\n");
			sqlstr.append("        ,'001' AS SQ_CSTR																	\n");
			sqlstr.append("        ,MAX(K.CD_CSTR) CD_CSTR																\n"); //2019.02.15 MK수정 NOT A GROUP BY EXPRESSION
			sqlstr.append("        ,MAX(K.DL_CSTR) DL_CST		 														\n");
			sqlstr.append("        ,MAX(K.NM_CSTR)	NM_CSTR																\n");
			sqlstr.append("        ,MAX(K.SIZE_CSTR) SIZE_CSTR															\n");
			sqlstr.append("        ,MAX(K.UNIT_CSTR) UNIT_CSTR															\n");
			sqlstr.append("        ,'01' AS WM_CM00304																	\n");
			sqlstr.append("        ,'01' AS WM_CM00007																	\n");
			sqlstr.append("        ,MAX(NVL(K.QTY_ACT,0)) AS QTY_ACT													\n");
			sqlstr.append("        ,MAX(NVL(K.UNT_ACT,0)) AS UNT_ACT													\n");
			sqlstr.append("        ,MAX(NVL(K.AMT_ACT,0)) AS AMT_ACT													\n");
			sqlstr.append("        ,MAX(NVL(K.UNT_ACT_CURR,0)) AS UNT_ACT_CURR											\n");
			sqlstr.append("        ,MAX(NVL(K.AMT_ACT_CURR,0)) AS AMT_ACT_CURR											\n");
			sqlstr.append("        ,'000' AS NO_REV																	    \n");
			sqlstr.append("FROM    CONT_PUR_ITEM A																		\n");
			sqlstr.append("        ,PMM_COMM_MATERIAL_MASTER B															\n");
			sqlstr.append("        ,PMM_MATERIALGROUP C																	\n");
			sqlstr.append("        ,(																					\n");
			sqlstr.append("        SELECT  DETAILCD																		\n");
			sqlstr.append("                ,DETAILNM																	\n");
			sqlstr.append("                ,CHG_CODE10 AS ERP_CODE														\n");
			sqlstr.append("        FROM    EBID_COM911T																	\n");
			sqlstr.append("        WHERE   BASECD = 'UNIT_KIND_GB'														\n");
			sqlstr.append("        ) D																					\n");
			sqlstr.append("        ,PUR2000T E																			\n");
			sqlstr.append("        ,(																					\n");
			sqlstr.append("        SELECT  DETAILCD																		\n");
			sqlstr.append("                ,DETAILNM																	\n");
			sqlstr.append("                ,CHG_CODE10 AS VAT_GB														\n");
			sqlstr.append("        FROM    EBID_COM911T																	\n");
			sqlstr.append("        WHERE   BASECD = 'PROOF_CD'															\n");
			sqlstr.append("        ) F																					\n");
			sqlstr.append("        ,BID_MASTER G																		\n");
			sqlstr.append("        ,ETECEPC.MR_REQ_PO H																	\n");
			sqlstr.append("        ,PUB0120T I																			\n");
			sqlstr.append("        ,PUB0120T J																			\n");
			sqlstr.append("        ,ETEC_ERPIF.BUDGET_ITEM K															\n");
			sqlstr.append("WHERE   B.MATERIAL_CD = 'A02001001'															\n");
			sqlstr.append("AND     B.KIND_SMALL = C.MATERIALGROUP														\n");
			sqlstr.append("AND     B.UNIT_KIND = D.DETAILCD(+)															\n");
			sqlstr.append("AND     A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID												\n");
			sqlstr.append("AND     E.GUA_GB = F.DETAILCD(+)																\n");
			sqlstr.append("AND     E.BID_NO = G.BID_NO																	\n");
			sqlstr.append("AND     E.BID_DEG = G.BID_DEG																\n");
			sqlstr.append("AND     E.DEM_ID = H.BUYREQNO																\n");
			sqlstr.append("AND     E.CRE_BY = I.ACCT_ID(+)																\n");
			sqlstr.append("AND     E.UPD_BY = J.ACCT_ID(+)																\n");
			sqlstr.append("AND     K.CD_COMPANY(+) = '"+WAFConfig.get("waf.erp.cdCompany")+"'							\n");
			sqlstr.append("AND     K.CD_SITE(+) = '"+vo.get("CD_SITE")+"'												\n");
			sqlstr.append("AND     H.PAY_ITEM = K.SEQ_BUDGET(+)															\n");
			sqlstr.append("AND     H.COST_CODE = K.SQ_CSTR(+)															\n");
			sqlstr.append("AND     A.PUR_CONT_MOD_ID = ?																\n");
			sqlstr.append("GROUP BY B.MATERIAL_CD 																		\n");
			sqlstr.append("        ,B.MATERIAL_NAME																		\n");
			sqlstr.append("        ,B.KIND_SMALL																		\n");
			sqlstr.append("        ,C.MATERIALGROUPNAME																	\n");
			sqlstr.append("        ,D.ERP_CODE																			\n");
			sqlstr.append("        ,B.MATERIAL_SPEC																		\n");
			sqlstr.append("        ,D.ERP_CODE																			\n");
			sqlstr.append("        ,F.VAT_GB																			\n");
			sqlstr.append("        ,I.USER_SABUN																		\n");
			sqlstr.append("        ,E.CRE_DATE																			\n");
			sqlstr.append("        ,J.USER_SABUN																		\n");
			sqlstr.append("        ,E.UPD_DATE																			\n");
			sqlstr.append("        ,E.ORD_START_DATE																	\n");
			
			search.setStatement(sqlstr.toString());
			search.addParameter(vo.get("PUR_CONT_MOD_ID"));
			listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
			if( listRowSet == null ){
				listRowSet = new RowSetImpl();
			}
			
			return listRowSet;
		}
	}
	
	private Integer getErpSuppCountAll(String corpRegNo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		StrBuffer sqlstr = new StrBuffer();
		Integer wCount = 0;
		
		try{
			sqlstr.append("	SELECT  COUNT(*) AS CNT									\n");
			sqlstr.append("	FROM    MA_PARTNER@ERPIU								\n");
			sqlstr.append("	WHERE   REPLACE(NO_COMPANY,'-','') = REPLACE(?,'-','')	\n");
			sqlstr.append("	OR   	CD_PARTNER = ?									\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(corpRegNo);
			search.addParameter(corpRegNo);
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
				wCount = valueobject.getInt(0);
			}
			
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			throw se;
		}
		
		return wCount;
	}

	private ValueObject getSuppInfo(String pur_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT  '"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY    /*회사코드*/														\n");
        	sqlstr.append("         ,A.CORP_KOR AS LN_PARTNER    /*거래처명(한)*/												\n");
        	sqlstr.append("         ,A.CORP_KOR AS SN_PARTNER    /*거래처명(약어)*/												\n");
        	sqlstr.append("         ,A.CORP_ENG AS EN_PARTNER    /*거래처명(영)*/												\n");
        	sqlstr.append("         ,'EXT' AS TP_PARTNER    /*거래처타입*/														\n");
        	sqlstr.append("         ,'001' AS FG_PARTNER    /*거래처구분*/														\n");
        	sqlstr.append("         ,B.BOSS_NAME AS NM_CEO    /*대표자명*/														\n");
        	sqlstr.append("         ,DECODE(A.IN_OUT_DIV,'O','9999999999',REPLACE(B.VENDCD,'-','')) AS NO_COMPANY    /*사업자등록번호*/	\n");
        	sqlstr.append("         ,A.CORP_STS AS TP_JOB    /*업태*/															\n");
        	sqlstr.append("         ,A.BIZ_FORM AS CLS_JOB    /*종목*/														\n");
        	sqlstr.append("         ,'102' AS CLS_PARTNER    /*거래처분류*/													\n");
        	sqlstr.append("         ,NVL(D.CHG_CODE10,'000') AS CD_NATION    /*국가코드*/										\n");
        	sqlstr.append("         ,B.EMAIL AS E_MAIL    /*담당자 E-MAIL*/													\n");
        	sqlstr.append("         ,B.TEL_NUM AS NO_TEL    /*담당자전화번호*/													\n");
        	sqlstr.append("         ,A.CORP_TEL AS NO_TEL1    /*본사전화번호*/													\n");
        	sqlstr.append("         ,A.CORP_FAX AS NO_FAX1    /*본사FAX*/														\n");
        	sqlstr.append("         ,B.USER_NAME AS CD_EMP_PARTNER    /*거래처담당자*/											\n");
        	sqlstr.append("         ,B.HP AS NO_HPEMP_PARTNER    /*핸드폰번호*/												\n");
        	sqlstr.append("         ,'N' AS USE_YN    /*사용유무*/																\n");
        	sqlstr.append("         ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT    /*추가자*/	\n");
        	sqlstr.append("         ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT    /*등록일*/							\n");
        	sqlstr.append("         ,A.CORP_ID																								\n");
        	sqlstr.append("         ,E.CHG_CODE02 AS TO_MAIL																				\n");
        	sqlstr.append("         ,CASE																									\n");
        	sqlstr.append("         	WHEN 	LENGTH(A.CORP_REG_NO) = 10 																	\n");
        	sqlstr.append("         	THEN	SUBSTR(A.CORP_REG_NO,1,3)||'-'||SUBSTR(A.CORP_REG_NO,4,2)||'-'||SUBSTR(A.CORP_REG_NO,6) 	\n");
        	sqlstr.append("         	ELSE	A.CORP_REG_NO																				\n");
        	sqlstr.append("         END AS CORP_REG_NO																						\n");
        	sqlstr.append("         ,A.IN_OUT_DIV																							\n");
        	sqlstr.append(" FROM    SUPP_INFO A																				\n");
        	sqlstr.append("         ,CONT_PUR_SUPP B																		\n");
        	sqlstr.append("         ,PUR2000T C																				\n");
        	sqlstr.append("         ,(SELECT    DETAILCD, CHG_CODE10														\n");
        	sqlstr.append("         FROM    EBID_COM911T																	\n");
        	sqlstr.append("         WHERE   BASECD = '911') D																\n");
        	sqlstr.append("         ,(SELECT    DETAILCD, CHG_CODE02														\n");
        	sqlstr.append("         FROM    EBID_COM911T																	\n");
        	sqlstr.append("         WHERE   BASECD = 'JOB_DIV') E															\n");
        	sqlstr.append(" WHERE   A.CORP_ID = B.CORP_ID																	\n");
        	sqlstr.append(" AND     B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID													\n");
        	sqlstr.append(" AND     B.SIGN_SEQ = 2																			\n");
        	sqlstr.append(" AND     A.NATION_CD = D.DETAILCD(+)																\n");
        	sqlstr.append(" AND     A.JOB_DIV = E.DETAILCD																	\n");
        	sqlstr.append(" AND     B.PUR_CONT_MOD_ID = ?																	\n");
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(pur_cont_mod_id);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	/**
	 * 협력업체 등록요청 메일발송
	 * @param toMail
	 * @param corpRegNo
	 * @param corpName
	 * @param corpId
	 * @return
	 * @throws Exception
	 */
	public boolean sendSuppInfoMail(String toMail, String corpRegNo, String corpName, String corpId) throws Exception {
		try {
			MessageSend mailSend = new MessageSend(storage);
			StringBuffer mailContent = new StringBuffer();
			String title = "ERP 협력업체 등록요청";
			
			String fileLink = "http://ebid.sgcenc.co.kr/common/file/FileListNew.screen?file_gb=TERMS_AGREE_INFO&id="+corpId+"&is_modify=N";
	      		
      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
      		mailContent.append("</BR>");
      		mailContent.append("전자조달 계약을 위한 ERP 협력업체 등록을 진행해 주십시요.</BR>");
      		mailContent.append("</BR>");
      		mailContent.append("사업자번호: "+corpRegNo+"</BR>");
      		mailContent.append("업체명 : "+corpName+"</BR>");
      		mailContent.append("첨부파일 : <a href='"+fileLink+"' target='_blank'>파일확인</a></BR>");
      		mailContent.append("</font>");
      		
      		mailSend.sendEmail(toMail, "partner@sgc.co.kr", title, title, mailContent, "1");
      		
		    return true;
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> sendSuppInfoMail() : " + e.getMessage());
            WAFLogger.error(e);
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }      
    }
	
	public void updateCdPartner(String corpId, String cdPartner) throws Exception {
        try {
        	conn.begin();
			Persistent pstmt = new PersistentImpl(conn.getConnection());
        	StrBuffer sqlstr = new StrBuffer();
        	sqlstr.append("UPDATE	SUPP_INFO			\n");
        	sqlstr.append("SET		CORP_REG_NO = ?		\n");
        	sqlstr.append("			,UPD_BY = ?			\n");
        	sqlstr.append("			,UPD_DATE = SYSDATE	\n");
        	sqlstr.append("WHERE	CORP_ID = ?			\n");
        			
	       	pstmt.setStatement(sqlstr.toString());
	       	pstmt.addParameter(cdPartner);
	       	pstmt.addParameter(storage.getUser().get("ACCT_ID"));
			pstmt.addParameter(corpId);
			pstmt.execute();
        }catch (Exception e) {
        	WAFLogger.error(this.getClass().getName() + " --> updateCdPartner() : " + e.getMessage());
			WAFLogger.error(e);
			storage.setSuccess(false);
			storage.setDetailMessage(e);
			conn.setRollback();
			throw e;
        }finally {
        	conn.end();
        }
	}

	/**
	 * 계약 변경 내역 목록
	 * @param Params
	 * @return
	 */
	private RowSet getChgItemList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sql = new StrBuffer();
        try{
        	sql.append(" 	SELECT MATERIAL_CD -- 자재코드 		\n");
        	sql.append(" 	     , ITEM_NAME -- 품명 		\n");
        	sql.append(" 	     , ITEM_SPEC -- 규격 		\n");
        	sql.append(" 	     , ITEM_UNIT -- 단위 		\n");
        	sql.append(" 	     , ITEM_QTY -- 수량(변경전 계약) 		\n");
        	sql.append(" 	     , ITEM_PRICE -- 단가(변경전 계약) 		\n");
        	sql.append(" 	     , ITEM_AMT -- 금액(변경전 계약) 		\n");
        	sql.append(" 	     , CONT_QTY -- 수량(변경후 계약)  		\n");
        	sql.append(" 	     , CONT_PRICE -- 단가(변경후 계약)  		\n");
        	sql.append(" 	     , DECODE(CONT_AMT,0,CONT_QTY*CONT_PRICE,CONT_AMT) CONT_AMT -- 금액(변경후 계약)  		\n");
        	sql.append(" 	     , DEMANDED_QTY --  발주의뢰 수량 		\n");
        	sql.append(" 	     , DEMANDED_PRI -- 발주의뢰 단가 		\n");
        	sql.append(" 	     , DECODE(DEMANDED_AMT,0,DEMANDED_QTY*DEMANDED_PRI,DEMANDED_AMT) DEMANDED_AMT -- 발주의뢰 금액 		\n");
        	sql.append(" 	     , STOCK_QTY		\n");
        	sql.append(" 	     , CHG_MOD_CD 		\n");
        	sql.append(" 	  FROM 		\n");
        	sql.append(" 	       (SELECT NVL (B1.MATERIAL_CD, A1.MATERIAL_CD) MATERIAL_CD -- 자재코드 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_NAME, (SELECT MATERIAL_NAME FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_NAME -- 품명 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_SPEC, (SELECT MATERIAL_SPEC FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_SPEC -- 규격 		\n");
        	sql.append(" 	            , NVL (F_GET_CODE_NAME('UNIT_KIND',B1.ITEM_UNIT), (SELECT UNIT_KIND FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_UNIT -- 단위 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_QTY, 0) ITEM_QTY -- 수량(변경전 계약) 		\n");
        	sql.append(" 	            , B1.ITEM_PRICE -- 단가(변경전 계약) 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_QTY, 0)*B1.ITEM_PRICE ITEM_AMT -- 금액(변경전 계약) 		\n");
        	
        	sql.append(" 	            , NVL (D1.ITEM_QTY, 0) CONT_QTY -- 수량(변경후 계약) 		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN D1.ITEM_PRICE = NULL 		\n");
        	sql.append(" 	                  THEN B1.ITEM_PRICE 		\n");
        	sql.append(" 	                  ELSE D1.ITEM_PRICE 		\n");
        	sql.append(" 	             END CONT_PRICE -- 단가(변경후 계약) 		\n");
        	sql.append(" 	            , NVL(D1.ITEM_AMT,0) CONT_AMT -- 발주의뢰 금액 		\n");
     	
        	sql.append(" 	            , NVL (A1.DEMANDED_QTY, 0) DEMANDED_QTY --  발주의뢰 수량 		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN NVL (A1.DEMANDED_PRI, 0)=0 		\n");
        	sql.append(" 	                  THEN B1.ITEM_PRICE 		\n");
        	sql.append(" 	                  ELSE A1.DEMANDED_PRI 		\n");
        	sql.append(" 	              END DEMANDED_PRI -- 발주의뢰 단가 		\n");
        	sql.append(" 	            , NVL(A1.DEMANDED_AMT,0) DEMANDED_AMT -- 발주의뢰 금액 		\n");
        	sql.append(" 	            , NVL (C1.STOCK_QTY, 0) STOCK_QTY		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                  THEN 'MODIFY' 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                  THEN 'NEW' 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NULL 		\n");
        	sql.append(" 	                  THEN 'DELETE' 		\n");
        	sql.append(" 	              END CHG_MOD_CD 		\n");
        	sql.append(" 	         FROM 		\n");
        	sql.append(" 	              (SELECT* 		\n");
        	sql.append(" 	                FROM PMM_DEMANDED_ITEM 		\n");
        	sql.append(" 	               WHERE DEMANDED_ID= "+ params.get("pDemID") +" 		\n");
        	sql.append(" 	                     AND DEMANDED_QTY>0		\n");
        	sql.append(" 	              ) A1 		\n");
        	sql.append(" 	          FULL OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT *  FROM CONT_PUR_ITEM WHERE PUR_CONT_MOD_ID=(SELECT MAX(PUR_CONT_MOD_ID) FROM PUR2000T WHERE PUR_CONT_ID="+ params.get("PUR_CONT_ID") +" AND PUR_CONT_MOD_ID !="+ params.get("PUR_CONT_MOD_ID") +")		\n");
        	sql.append(" 	              ) B1 		\n");
        	sql.append(" 	              ON A1.MATERIAL_CD=B1.MATERIAL_CD 		\n");
        	sql.append(" 	          FULL OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT *  FROM CONT_PUR_ITEM WHERE PUR_CONT_MOD_ID="+ params.get("PUR_CONT_MOD_ID") +"		\n");
        	sql.append(" 	              ) D1 		\n");
        	sql.append(" 	              ON NVL(A1.MATERIAL_CD, B1.MATERIAL_CD)=D1.MATERIAL_CD 		\n");
        	sql.append(" 	          LEFT OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT PUR_CONT_MOD_ID, MATERIAL_CD, CEIL (SUM (STOCK_QTY)) STOCK_QTY 		\n");
        	sql.append(" 	                FROM 		\n");
        	sql.append(" 	                     (SELECT T3.PUR_CONT_MOD_ID, T2.ITEM_NO MATERIAL_CD , T2.SHIP_QTY STOCK_QTY 		\n");
        	sql.append(" 	                          FROM ETECEPC.FM_SHIPPING_LIST T1		\n");
        	sql.append(" 	                             , ETECEPC.FM_SHIPPING_DETAIL T2		\n");
        	sql.append(" 	                             , ETEC_EBID.PUR2000T T3 		\n");
        	sql.append(" 	                          WHERE T1.SHIPPING_LIST_ID= T2.SHIPPING_LIST_ID 		\n");
        	sql.append(" 	                            AND T1.ORDER_NO= T3.ORD_DOC_NO 		\n");
        	sql.append(" 	                     UNION ALL 		\n");
        	sql.append(" 	                     SELECT T3.PUR_CONT_MOD_ID, T1.MATERIAL_CD, T1.ITEM_QTY  		\n"); 
        	sql.append(" 	                          FROM PMM_PAYREQ_ITEM T1		\n");
        	sql.append(" 	                             , PUR1100T T2		\n");
        	sql.append(" 	                             , PUR2000T T3 		\n");
        	sql.append(" 	                          WHERE T1.PAYREQ_ID=T2.PAYREQ_ID 		\n");
        	sql.append(" 	                            AND T2.PUR_CONT_mod_ID= T3.PUR_CONT_mod_ID		\n");
        	sql.append(" 	                     ) 		\n");
        	sql.append(" 	               WHERE PUR_CONT_MOD_ID = "+ params.get("PUR_CONT_MOD_ID") +" 		\n");
        	sql.append(" 	               GROUP BY PUR_CONT_MOD_ID, MATERIAL_CD		\n");
        	sql.append(" 	              ) C1 		\n");
        	sql.append(" 	              ON A1.MATERIAL_CD=C1.MATERIAL_CD		\n");
        	sql.append(" 	       )		\n");

            
            search.setStatement(sql.toString());
            WAFLogger.debug(sql.toString());

            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}	

	/**
	 * 변경구분 및 사업자번호 변경여부 조회
	 * @param Params
	 * @return
	 */
	private ValueObject getContChgInfo(String pur_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{

        	sqlstr.append("SELECT A.MOD_GB                                                                            \n");
        	sqlstr.append("      ,DECODE(A.CORP_ID, NVL(B.BEF_CORP_ID, A.CORP_ID), 'N', 'Y') AS CORP_CHG_YN           \n");
        	sqlstr.append("  FROM PUR2000T A                                                                          \n");
        	sqlstr.append("      ,(                                                                                   \n");
        	sqlstr.append("        SELECT  B.PUR_CONT_ID                                                              \n");
        	sqlstr.append("               ,B.CORP_ID AS BEF_CORP_ID                                                   \n");
        	sqlstr.append("        FROM    PUR2000T A                                                                 \n");
        	sqlstr.append("               ,PUR2000T B                                                                 \n");
        	sqlstr.append("        WHERE   A.PUR_CONT_MOD_ID = ?                                                      \n");
        	sqlstr.append("        AND     A.PUR_CONT_ID = B.PUR_CONT_ID                                              \n");
        	sqlstr.append("        AND     A.MOD_NO - 1 = B.MOD_NO                                                    \n");
        	sqlstr.append("        ) B                                                                                \n");
        	sqlstr.append(" WHERE A.PUR_CONT_ID = B.PUR_CONT_ID(+)                                                    \n");
        	sqlstr.append("   AND A.PUR_CONT_MOD_ID = ?                                                               \n");

			search.setStatement(sqlstr.toString());
			search.addParameter(pur_cont_mod_id);
			search.addParameter(pur_cont_mod_id);

            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            throw new Exception(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	/**
	 * 계약 정보 (사업자번호 변경시)
	 * @param Params
	 * @return
	 */
	private ValueObject getConfinfoChg(String pur_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	
        	sqlstr.append("SELECT  '01' AS WM_CM00301                                                                                       \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMM') AS YM_ECUT                                                                 \n");
        	sqlstr.append("        ,A.ERP_NO_ECUT AS NO_ECUT                                                                                \n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY                                                  \n");
        	sqlstr.append("        ,NVL(B.ERP_SITE_CD, A.DEPT_CD) AS CD_SITE                                                                \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_ECUT                                                               \n");
        	sqlstr.append("        ,TO_CHAR(A.CRE_DATE,'YYYYMMDD') AS DT_MK_ECUT                                                            \n");
        	sqlstr.append("        ,C.USER_NAME AS NM_MK_ECUT                                                                               \n");
        	sqlstr.append("        ,SUBSTRB(A.CST_DOC_NAME,1, 49) AS NM_ECUT                                                                   \n");
        	sqlstr.append("        ,'01' AS WM_CM00302                                                                                      \n");
        	sqlstr.append("        ,DECODE(A.DIV_PO_YN,'Y','02','01') AS WM_CM00303                                                         \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_START_DATE,'YYYYMMDD') AS DT_DELV                                                         \n");
        	sqlstr.append("        ,SUBSTRB(A.LEAD_PLACE, 1,45) AS LOCATE_DELV                                                              \n");
        	sqlstr.append("        ,A.PAYMENT_TERMS AS WM_CM00206                                                                           \n");
        	sqlstr.append("        ,F_GET_CODE_NAME('SUB_ACMP_PAY_TYPE', A.PAY_COND_GB) AS DESC_CM00206                                     \n");
        	sqlstr.append("        ,DECODE(D.VAT_GB,'INPUT_VAT','Y','N') AS TAG_VAT                                                         \n");
        	sqlstr.append("        ,F.ERP_CD_COOP AS CD_COOP                                                                                \n");
        	sqlstr.append("        ,REPLACE(F.DCERTI_NO,'-','') AS NO_COMPANY                                                               \n");
        	sqlstr.append("        ,F.DEALR_NM AS NM_COOP                                                                                   \n");
        	sqlstr.append("        ,NVL(A.ORD_SUPPLY_AMT * NVL(L.EXCHANGE_ORD,1),0) AS AMT_ECUT                                             \n");
        	sqlstr.append("        ,NVL(A.ORD_SURTAX_AMT * NVL(L.EXCHANGE_ORD,1),0) AS VAT_ECUT                                             \n");
        	sqlstr.append("        ,NVL(A.CON_GUAR_AMT * NVL(L.EXCHANGE_ORD,1),0) AS AMT_CONT_ASSURANCE                                     \n");
        	sqlstr.append("        ,NVL(A.CON_GUAR_AMT_RATE,0) AS RT_CONT_ASSURANCE                                                         \n");
        	sqlstr.append("        ,NVL(A.PREPAY_GUAR_AMT * NVL(L.EXCHANGE_ORD,1),0) AS AMT_PREPAY_ASSURANCE                                \n");
        	sqlstr.append("        ,NVL(A.PREPAY_AMT_RATE,0) AS RT_PREPAY_ASSURANCE                                                         \n");
        	sqlstr.append("        ,NVL(A.DEF_GUAR_AMT * NVL(L.EXCHANGE_ORD,1),0) AS AMT_WARRANTY                                           \n");
        	sqlstr.append("        ,NVL(A.DEF_GUAR_AMT_RATE,0) AS RT_WARRANTY                                                               \n");
        	sqlstr.append("        ,F_GET_CODE_NAME('DELRATE_GB', A.DEL_RATE_GB) AS DESC_AMT_DELAY                                          \n");
        	sqlstr.append("        ,SUBSTRB(A.CON_DISPLAY_TXT,1,240) AS DESC_ECUT                                                           \n");
        	sqlstr.append("        ,C.USER_SABUN AS ID_USER                                                                                 \n");
        	sqlstr.append("        ,NVL2(J.NO_ECUT_O,'01','') AS WM_CM00301_O                                                               \n");
        	sqlstr.append("        ,NVL2(J.NO_ECUT_O,J.YM_ECUT_O,'') AS YM_ECUT_O                                                           \n");
        	sqlstr.append("        ,J.NO_ECUT_O AS NO_ECUT_O                                                                                \n");
        	sqlstr.append("        ,C.USER_SABUN AS ID_INSERT                                                                               \n");
        	sqlstr.append("        ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                                    	\n");
        	sqlstr.append("        ,'' AS ID_UPDATE                                                                               			\n");
        	sqlstr.append("        ,'' AS DTS_UPDATE                                                    									\n");
        	sqlstr.append("        ,NVL(H.ERP_CODE,'000') AS CURR_SOUR                                                                      \n");
        	sqlstr.append("        ,NVL(L.EXCHANGE_ORD,1) AS UNT_CURR                                                                       \n");
        	sqlstr.append("        ,NVL(A.ORD_SUPPLY_AMT,0) AS SAMT_CURR          											                \n");
        	sqlstr.append("        ,NVL(A.ORD_SURTAX_AMT,0) AS VAMT_CURR          											                \n");
        	sqlstr.append("        ,DECODE(NVL(A.CURRENCY_GB,'KRW'),'KRW','001','002') AS WM_CM00011                                        \n");
        	sqlstr.append("        ,C.USER_SABUN AS NO_EMP                                                                                  \n");
        	sqlstr.append("        ,I.USER_SABUN AS NO_EMP_A                                                                                \n");
        	sqlstr.append("        ,I.DEPART_DEPT_CD AS CD_DEPT_A                                                                           \n");
        	sqlstr.append("        ,I.USER_NAME AS NM_KOR_A                                                                                 \n");
        	sqlstr.append("        ,I.USER_SABUN AS ID_USER_A                                                                               \n");
        	sqlstr.append("        ,C.DEPART_DEPT_CD AS CD_DEPT                                                                             \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_AGREE                                                              \n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMMDD') AS DT_CONT                                                               \n");
        	sqlstr.append("        ,A.MOD_NO AS NO_SEQ                                                                                      \n");
        	sqlstr.append("        ,A.PUR_CONT_MOD_ID                                                                                       \n");
        	sqlstr.append("        ,A.PUR_CONT_ID                                                                                           \n");
        	sqlstr.append("        ,A.MOD_NO																		                        \n");
        	sqlstr.append("        ,A.MOD_GB																		                        \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_START_DATE,'YYYYMMDD') AS ORD_START_DATE							                        \n");
        	sqlstr.append("        ,TO_CHAR(A.ORD_END_DATE,'YYYYMMDD') AS ORD_END_DATE								                        \n");
        	sqlstr.append("        ,A.PAY_DIV																		                        \n");
        	sqlstr.append("        ,A.DEM_ID																		                        \n");
        	sqlstr.append("        ,A.ORD_DOC_NO																	                        \n");
        	sqlstr.append("        ,A.REQ_DOC_NO																	                        \n");
        	sqlstr.append("        ,L.YM_ECUT_ORD																	                        \n");
        	sqlstr.append("        ,L.NO_ECUT_ORD																	                        \n");
        	sqlstr.append("        ,'N' AS TAG_UNITY																						\n");
        	sqlstr.append("        ,A.LEAD_COND_GB AS WM_CM00205																			\n");
        	sqlstr.append("        ,'N' AS PROC_COM																							\n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY_A												\n");
        	sqlstr.append("        ,'N' AS YN_CERT																							\n");
        	sqlstr.append("        ,(SELECT DEMANDED_KIND FROM PMM_DEMANDED_REQUEST WHERE DEMANDED_ID = A.DEM_ID) AS DEMANDED_KIND			\n");
        	sqlstr.append("        ,DECODE(A.PAY_DIV,'1','100','110') AS WM_CM00317									                        \n");
        	sqlstr.append("        ,NVL(A.ERP_CHECK_YN,'Y') AS ERP_CHECK_YN									                                \n");
        	sqlstr.append("        ,REPLACE(A.GUA_GB,'Z99','') AS CD_PROOF									                                \n");
        	sqlstr.append("        ,(SELECT ERP_CD_COOP FROM PUR1000T WHERE PUR_CONT_ID = A.PUR_CONT_ID) AS ORG_CD_COOP					    \n");
        	sqlstr.append("        ,NVL(A.CHG_EXE_AMT * NVL(L.EXCHANGE_ORD,1),0) AS AMT_ACT_ETEC                                            \n"); //2019.07.16
        	sqlstr.append("        ,F.ERP_CD_PARTNER AS ERP_CD_PARTNER																	    \n");
        	sqlstr.append("        ,J.BEF_ERP_CD_PARTNER AS ORG_ERP_CD_PARTNER															    \n");
        	sqlstr.append("FROM    PUR2000T A                                                                                               \n");
        	sqlstr.append("        ,ETEC_ERPIF.SITE_MAPPING B                                                                               \n");
        	sqlstr.append("        ,PUB0120T C                                                                                              \n");
        	sqlstr.append("        ,(                                                                                                       \n");
        	sqlstr.append("        SELECT  DETAILCD, CHG_CODE10 AS VAT_GB                                                                   \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                     \n");
        	sqlstr.append("        WHERE   BASECD = 'PROOF_CD'                                                                              \n");
        	sqlstr.append("        ) D                                                                                                      \n");
        	sqlstr.append("        ,CONT_PUR_SUPP E                                                                                         \n");
        	sqlstr.append("        ,ETEC_ERPIF.ACZ10800 F                                                                                   \n");
        	sqlstr.append("        ,PUB0120T G                                                                                              \n");
        	sqlstr.append("        ,(                                                                                                       \n");
        	sqlstr.append("        SELECT  DETAILCD, NVL(CHG_CODE10,DETAILCD) AS ERP_CODE                                                   \n");
        	sqlstr.append("        FROM    EBID_COM911T                                                                                     \n");
        	sqlstr.append("        WHERE   BASECD = '911'                                                                                   \n");
        	sqlstr.append("        ) H                                                                                                      \n");
        	sqlstr.append("        ,PUB0120T I                                                                                              \n");
        	sqlstr.append("        ,(																				                        \n");
        	sqlstr.append("        SELECT  B.PUR_CONT_ID															                        \n");
        	sqlstr.append("                ,TO_CHAR(B.CST_DATE,'YYYYMM') AS YM_ECUT_O								                        \n");
        	sqlstr.append("                ,B.ERP_NO_ECUT AS NO_ECUT_O												                        \n");
        	sqlstr.append("                ,E.ERP_CD_PARTNER AS BEF_ERP_CD_PARTNER									                        \n");
        	sqlstr.append("        FROM    PUR2000T A																                        \n");
        	sqlstr.append("                ,PUR2000T B																                        \n");
        	sqlstr.append("                ,CONT_PUR_SUPP C															                        \n");
        	sqlstr.append("                ,SUPP_INFO D																                        \n");
        	sqlstr.append("                ,ETEC_ERPIF.ACZ10800 E													                        \n");
        	sqlstr.append("        WHERE   A.PUR_CONT_MOD_ID = ?													                        \n");
        	sqlstr.append("        AND     A.PUR_CONT_ID = B.PUR_CONT_ID											                        \n");
        	sqlstr.append("        AND     A.MOD_NO - 1 = B.MOD_NO													                        \n");
        	sqlstr.append("        AND     B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID									                        \n");
        	sqlstr.append("        AND     C.CORP_ID != 0															                        \n");
        	sqlstr.append("        AND     C.CORP_ID = D.CORP_ID													                        \n");
        	sqlstr.append("        AND     REPLACE(D.CORP_REG_NO,'-','') = REPLACE(E.DCERTI_NO,'-','')				                        \n");
        	sqlstr.append("        AND     E.ADDITEM20 = 'P'														                        \n");
        	sqlstr.append("        ) J																				                        \n");
        	sqlstr.append("        ,(																				                        \n");
        	sqlstr.append("        SELECT  A.PUR_CONT_ID																                    \n");
        	sqlstr.append("                ,TO_CHAR(A.CST_DATE,'YYYYMM') AS YM_ECUT_ORD								                        \n");
        	sqlstr.append("                ,A.ERP_NO_ECUT AS NO_ECUT_ORD												                    \n");
        	sqlstr.append("                ,B.EXCHANGE AS EXCHANGE_ORD												                        \n");
        	sqlstr.append("        FROM    PUR2000T A, BID_SUPP B													                        \n");
        	sqlstr.append("        WHERE   A.MOD_NO = 0																                        \n");
        	sqlstr.append("        AND     A.BID_NO = B.BID_NO(+)													                        \n");
        	sqlstr.append("        AND     A.BID_DEG = B.BID_DEG(+)													                        \n");
        	sqlstr.append("        AND     A.CORP_ID = B.VENDOR_CD(+)												                        \n");
        	sqlstr.append("        ) L																				                        \n");        	
        	sqlstr.append("        ,SUPP_INFO M																		                        \n");        	
        	sqlstr.append("WHERE   A.DEPT_CD = B.EBID_SITE_CD(+)                                                                            \n");
        	sqlstr.append("AND     A.CRE_BY = C.ACCT_ID(+)                                                                                  \n");
        	sqlstr.append("AND     A.GUA_GB = D.DETAILCD(+)                                                                                 \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID                                                                    \n");
        	sqlstr.append("AND     E.CORP_ID != 0                                                                                           \n");
        	sqlstr.append("AND     E.CORP_ID = M.CORP_ID                                                                                    \n");
        	sqlstr.append("AND     REPLACE(M.CORP_REG_NO,'-','') = REPLACE(F.DCERTI_NO,'-','')						                        \n");
        	sqlstr.append("AND     F.ADDITEM20 = 'P'               											                                \n"); //2019.02.26
        	sqlstr.append("AND     A.UPD_BY = G.ACCT_ID(+)                                                                                  \n");
        	sqlstr.append("AND     A.CURRENCY_GB = H.DETAILCD(+)                                                                            \n");
        	sqlstr.append("AND     A.CON_CONFIRM_ID = I.ACCT_ID(+)                                                                          \n");
        	sqlstr.append("AND     A.PUR_CONT_ID = J.PUR_CONT_ID(+)                                                                         \n");
        	sqlstr.append("AND     A.PUR_CONT_ID = L.PUR_CONT_ID(+)                                                                         \n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = ?                                                                                    \n");
 
			search.setStatement(sqlstr.toString());
			search.addParameter(pur_cont_mod_id);
			search.addParameter(pur_cont_mod_id);
			System.out.println(sqlstr.toString());
			System.out.println(pur_cont_mod_id);
			
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            throw new Exception(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
}