/**
 * @(#)file    BidPlanSignDriver.java
 * @(#)author  Son hyungmin
 * @(#)version 1.0
 * @(#)date    2014-09-29
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.dcchem.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of dcchem, Inc.
 */
package kr.co.udapsoft.common.commonSign.driver;

import java.sql.Connection;

import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.commonSign.CommonSignInterface;
import kr.co.udapsoft.common.sender.MessageSend;
import oracle.toplink.internal.ox.StrBuffer;

/**
 * 
 * @version 1.0
 * @author Beomgeun Lee
 */
public class OChgPoSignDriver implements CommonSignInterface {

    /**
     * 테슽 파일
     * 
     */
    public OChgPoSignDriver() {
        super();
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCallSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCallSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doCallSign");
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doStartSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doStartSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doStartSign");
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFirstFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstFinishSign(Connection connection, ValueObject signDoc) throws Exception {
		System.out.println("!!doFirstFinishSign");
    	System.out.println("### 결재  Strart");
    	updateMaster(connection, signDoc);
    	String maxSubContid = getMaxSubCntId();
		addChangeContProc(connection,signDoc, maxSubContid);	//변경 계약 축
    	System.out.println("### 결재  End");
    }
    
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFinishSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doFinishSign");
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!반려");
    	updateMaster(connection, signDoc);
    }
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doRejectSign");
    	
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCancelSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCancelSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("!!doCancelSign");
    }
    
    
    /**
     * SAC01001 상태값을 업데이트해준다.
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void updateMaster(Connection connection, ValueObject vo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StringBuffer sqlstr = null;
        
        try {
        	sqlstr = new StringBuffer();
        	String status = "";
        	if(vo.get("SIGN_TYPE").equals("1")){
        		if(vo.get("SIGN_DECI_CLASS").equals("14")){
        			status = "50";//결재완료
        		}else {
        			status = "80";//반려 2018.07.11 90->80으로 변경 
        		}
        	}
   			sqlstr.append(" UPDATE  SUB3000T  					\n");
    		sqlstr.append("      SET STATUS_CD = ?    						\n");
    		sqlstr.append(" WHERE SUB_CONT_REQ_ID	= ?    							\n");
    		persistent.setStatement(sqlstr.toString());
            persistent.addParameter(status);
            persistent.addParameter(vo.get("KEY_COL2"));
            persistent.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updateSac01001() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	private String getMaxSubCntId() {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr 	= 	"SELECT SUB2000T_S.NEXTVAL AS MAX_SUB_CONT_MOD_ID \n"
            		+	"  FROM DUAL\n";

            search.setStatement(sqlstr);
            return search.execute().getRow(0).get("MAX_SUB_CONT_MOD_ID");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getNextPgmGrSeq() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }
	
	/**
     * 변경 계약 정보 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void addChangeContProc(Connection connection, ValueObject valueObject, String maxSubContid) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {

        	addChangeContSub(connection, valueObject, maxSubContid);	//변경 계약 축
        	
			addChangeContSupport(connection, valueObject, maxSubContid);	//변경 계약 축
			
//			orgContChgStatus(super.conn.getConnection(), params);	//원계약정보 상태 변경
			Send_ToeTEC(valueObject);
			
			//2017.2.7 외주예산관리 추가
			addBcsCont(connection, valueObject, maxSubContid);
			//2018.10.31 외주내역 추가
			addContItem(connection, valueObject, maxSubContid);
			
        }
        catch(Exception e) {
        	  WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
              WAFLogger.error("SQL : " + e.getMessage());
              WAFLogger.error(e);
              throw e;
        }
            
    }
	
	public boolean addChangeContSub(Connection connection, ValueObject valueObject, String maxSubContid) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append("INSERT INTO SUB2000T 		               \n");
			sqlstr.append("       ( 	SUB_CONT_MOD_ID		               \n");
			sqlstr.append("         ,	CON_DOC_NO		                   \n");
			sqlstr.append("         ,	MOD_NO		                       \n");
			sqlstr.append("         ,	SUB_CONT_ID		                   \n");
			sqlstr.append("         ,	MOD_GB		                       \n");
			sqlstr.append("         ,	MOD_CON_RSN		                   \n");
			sqlstr.append("         ,	MOD_REQ_DATE		               \n");
			sqlstr.append("         ,	MOD_CORP_RSN		               \n");
			sqlstr.append("         ,	DEPT_CD		                       \n");
			sqlstr.append("         ,	DEPT_NAME		                   \n");
			sqlstr.append("         ,	CON_GB		                       \n");
			sqlstr.append("         ,	DEALER_NM		                   \n");
			sqlstr.append("         ,	BUILD_NAME		                   \n");
			sqlstr.append("         ,	UND_CON_NAME		               \n");
			sqlstr.append("         ,	SUB_UND_CON_NAME		           \n");
			sqlstr.append("         ,	ELEC_CON_YN		                   \n");
			sqlstr.append("         ,	REQ_DEPT_CD		                   \n");
			sqlstr.append("         ,	REQ_DATE		                   \n");
			sqlstr.append("         ,	REQ_USERID		                   \n");
			sqlstr.append("         ,	REQ_USERNM		                   \n");
			sqlstr.append("         ,	REQ_DOC_NO		                   \n");
			sqlstr.append("         ,	CST_DATE		                   \n");
			sqlstr.append("         ,	CON_IN_DATE		                   \n");
			sqlstr.append("         ,	CON_DATE		                   \n");
			sqlstr.append("         ,	PAY_COND_GB		                   \n");
			sqlstr.append("         ,	PAY_CONTENT		                   \n");
			sqlstr.append("         ,	START_WORK_DATE		               \n");
			sqlstr.append("         ,	END_WORK_DATE		               \n");
			sqlstr.append("         ,	WORK_PLACE		                   \n");
			sqlstr.append("         ,	PAY_WAY_CASH		               \n");
			sqlstr.append("         ,	PAY_WAY_BILL		               \n");
			sqlstr.append("         ,	CON_AMT		                       \n");
			sqlstr.append("         ,	CON_SUPPLY_AMT		               \n");
			sqlstr.append("         ,	CON_SUPPLY_TAMT		               \n");
			sqlstr.append("         ,	CON_SUPPLY_NAMT		               \n");
			sqlstr.append("         ,	CON_SURTAX_AMT		               \n");
			sqlstr.append("         ,	LABOR_AMT		                   \n");
			sqlstr.append("         ,	PREPAY_RATE		                   \n");
			sqlstr.append("         ,	PREPAY_AMT		                   \n");
			sqlstr.append("         ,	PREPAY_GUAR_AMT_RATE	           \n");
			sqlstr.append("         ,	PREPAY_GUAR_AMT		               \n");
			sqlstr.append("         ,	PREPAY_SUPPLY_AMT		           \n");
			sqlstr.append("         ,	PREPAY_SUPPLY_TAMT		           \n");
			sqlstr.append("         ,	PREPAY_SUPPLY_NAMT		           \n");
			sqlstr.append("         ,	PREPAY_SURTAX_AMT		           \n");
			sqlstr.append("         ,	CON_EST_GUAR_RATE		           \n");
			sqlstr.append("         ,	CON_EST_GUAR_AMT		           \n");
			sqlstr.append("         ,	CON_GUAR_TERM		               \n");
			sqlstr.append("         ,	DEF_EST_GUAR_RATE		           \n");
			sqlstr.append("         ,	DEF_GUAR_AMT		               \n");
			sqlstr.append("         ,	DEF_EST_TERM		               \n");
			sqlstr.append("         ,	DELAY_RATE		                   \n");
			sqlstr.append("         ,	LABOR_GUAR_JOIN_AMT_GB	           \n");
			sqlstr.append("         ,	CON_SPE_TXT		                   \n");
			sqlstr.append("         ,	ADD_FILE_KIND		               \n");
			sqlstr.append("         ,	CON_CHARGE_ID		               \n");
			sqlstr.append("         ,	CON_CHARGE_NAME		               \n");
			sqlstr.append("         ,	CORP_SIGN_DATE		               \n");
			sqlstr.append("         ,	COMPANY_SIGN_DATE		           \n");
			sqlstr.append("         ,	PAY_GROUP		                   \n");
			sqlstr.append("         ,	PAYMENT_TERMS	 	               \n");
			sqlstr.append("         ,	ACCT_GB		                       \n");
			sqlstr.append("         ,	PROOF_CD		                   \n");
			sqlstr.append("         ,	CON_CUR_CD		                   \n");
			sqlstr.append("         ,	CON_EXCHANGE_DATE		           \n");
			sqlstr.append("         ,	CON_WON_RATE		               \n");
			sqlstr.append("         ,	PAY_ITEM		                   \n");
			sqlstr.append("         ,	COST_CODE		                   \n");
			sqlstr.append("         ,	COST_TYPE		                   \n");
			sqlstr.append("         ,	WORK_KIND_CD		               \n");
			sqlstr.append("         ,	CONTRACTID		                   \n");
			sqlstr.append("         ,	G_ID		                       \n");
			sqlstr.append("         ,	P_ID		                       \n");
			sqlstr.append("         ,	CON_CLS_YN		                   \n");
			sqlstr.append("         ,	TEMPLATE_CD		                   \n");
			sqlstr.append("         ,	CONT_HASH		                   \n");
			sqlstr.append("         ,	TRUE_RANDOM		                   \n");
			sqlstr.append("         ,	STATUS_CD		                   \n");
			sqlstr.append("         ,	BID_NO		                       \n");
			sqlstr.append("         ,	BID_DEG		                       \n");
			sqlstr.append("         ,	MONTH_ACMP_CNT		               \n");
			sqlstr.append("         ,	OBJ_RCPT_DAY		               \n");
			sqlstr.append("         ,	MODI_REQ_REASON		               \n");
			sqlstr.append("         ,	MODI_REQ_DT		                   \n");
			sqlstr.append("         ,	FRST_CON_AMT		               \n");
			sqlstr.append("         ,	FRST_EXE_AMT		               \n");
			sqlstr.append("         ,	CHG_CON_AMT		                   \n");
			sqlstr.append("         ,	CHG_EXE_AMT		                   \n");
			sqlstr.append("         ,	IN_SPE_INFO		                   \n");
			sqlstr.append("         ,   IN_CHG_INFO 		               \n");
			sqlstr.append("         ,	OBC_MID_REQ_MONTH		           \n");
			sqlstr.append("         ,   OBC_MID_BILL_DATE		           \n");
			sqlstr.append("         ,   SUPP_DIRECT_YN			           \n");	//2021.04.01(하도급직불여부 추가)
			sqlstr.append("         ,   LABOR_USE_YN			           \n");	//2022.03.21(노무비닷컴 사용여부 추가)
			sqlstr.append("         ,	CRE_BY		                       \n");
			sqlstr.append("         ,	CRE_DATE) 				           \n");
			sqlstr.append("SELECT ?	                                       \n");
			sqlstr.append("         ,	A.CON_DOC_NO		               \n");
			sqlstr.append("         ,	A.MOD_NO + 1		               \n");
			sqlstr.append("         ,	A.SUB_CONT_ID		               \n");
			sqlstr.append("         ,   B.MOD_GB   		                   \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	A.DEPT_CD		                   \n");
			sqlstr.append("         ,	A.DEPT_NAME		                   \n");
			sqlstr.append("         ,	A.CON_GB		                   \n");
			sqlstr.append("         ,	A.DEALER_NM		                   \n");
			sqlstr.append("         ,	A.BUILD_NAME		               \n");
			sqlstr.append("         ,	A.UND_CON_NAME		               \n");
			sqlstr.append("         ,	A.SUB_UND_CON_NAME		           \n");
			sqlstr.append("         ,	A.ELEC_CON_YN		               \n");
			sqlstr.append("         ,	A.REQ_DEPT_CD		               \n");
			sqlstr.append("         ,	A.REQ_DATE		                   \n");
			sqlstr.append("         ,	A.REQ_USERID		               \n");
			sqlstr.append("         ,	A.REQ_USERNM		               \n");
			sqlstr.append("         ,	A.REQ_DOC_NO		               \n");
			sqlstr.append("         ,	A.CST_DATE		                   \n");
			sqlstr.append("         ,	A.CON_IN_DATE		               \n");
			sqlstr.append("         ,	A.CON_DATE  		               \n");
			sqlstr.append("         ,	A.PAY_COND_GB		               \n");
			sqlstr.append("         ,	A.PAY_CONTENT		               \n");
			sqlstr.append("         ,	A.START_WORK_DATE		           \n");
			sqlstr.append("         ,	B.AFT_CONT_DATE		               \n");
			sqlstr.append("         ,	A.WORK_PLACE		               \n");
			sqlstr.append("         ,	A.PAY_WAY_CASH		               \n");
			sqlstr.append("         ,	A.PAY_WAY_BILL		               \n");//2017.07.19 
			sqlstr.append("         ,	B.AFT_SUPP_AMT + ROUND(DECODE(NVL(A.CON_SURTAX_AMT,0), 0, 0,ROUND(B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT)*0.1,0)),0)	\n");//A.CON_AMT
			sqlstr.append("         ,	B.AFT_SUPP_AMT                                                                                                           	        \n");//A.CON_SUPPLY_AMT	
//			sqlstr.append("         ,	ROUND(B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT),0)                  	                                                \n");//A.CON_SUPPLY_TAMT
//			sqlstr.append("         ,	B.AFT_SUPP_AMT - ROUND( B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT),0) 	                                                \n");//A.CON_SUPPLY_NAMT
			sqlstr.append("         ,   ROUND (DECODE(B.AFT_SUPP_AMT,0,0, B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT / A.CON_SUPPLY_AMT)), 0)                                      \n"); //A.CON_SUPPLY_TAMT
			sqlstr.append("         ,   B.AFT_SUPP_AMT - ROUND (DECODE(B.AFT_SUPP_AMT,0,0,B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT / A.CON_SUPPLY_AMT)), 0)                      \n"); //A.CON_SUPPLY_NAMT
			sqlstr.append("         ,	ROUND(DECODE(NVL(A.CON_SURTAX_AMT,0), 0, 0,B.AFT_SUPP_AMT * (A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT)*0.1),0)                            \n");//A.CON_SURTAX_AMT
			sqlstr.append("         ,	A.LABOR_AMT		                   \n");
			sqlstr.append("         ,	A.PREPAY_RATE		               \n");//2017.07.25
			sqlstr.append("         ,	A.PREPAY_AMT		               \n");
			sqlstr.append("         ,	A.PREPAY_GUAR_AMT_RATE	           \n");
			sqlstr.append("         ,	A.PREPAY_GUAR_AMT	               \n");	
			sqlstr.append("         ,	A.PREPAY_SUPPLY_AMT	               \n");
			sqlstr.append("         ,	A.PREPAY_SUPPLY_TAMT               \n");
			sqlstr.append("         ,	A.PREPAY_SUPPLY_NAMT               \n");
			sqlstr.append("         ,	A.PREPAY_SURTAX_AMT                \n");
			sqlstr.append("         ,	A.CON_EST_GUAR_RATE		           \n");
			sqlstr.append("         ,	A.CON_EST_GUAR_AMT                 \n");
			sqlstr.append("         ,	A.CON_GUAR_TERM		               \n");
			sqlstr.append("         ,	A.DEF_EST_GUAR_RATE		           \n");
			sqlstr.append("         ,	A.DEF_GUAR_AMT                     \n");
			sqlstr.append("         ,	A.DEF_EST_TERM		               \n");
			sqlstr.append("         ,	A.DELAY_RATE		               \n");
			sqlstr.append("         ,	A.LABOR_GUAR_JOIN_AMT_GB	       \n");
			sqlstr.append("         ,	A.CON_SPE_TXT		               \n");
			sqlstr.append("         ,	A.ADD_FILE_KIND		               \n");
			sqlstr.append("         ,	A.CON_CHARGE_ID		               \n");
			sqlstr.append("         ,	A.CON_CHARGE_NAME		           \n");
			sqlstr.append("         ,	A.CORP_SIGN_DATE		           \n");
			sqlstr.append("         ,	A.COMPANY_SIGN_DATE		           \n");
			sqlstr.append("         ,	A.PAY_GROUP		                   \n");
			sqlstr.append("         ,	A.PAYMENT_TERMS		               \n");
			sqlstr.append("         ,	A.ACCT_GB		                   \n");
			sqlstr.append("         ,	A.PROOF_CD		                   \n");
			sqlstr.append("         ,	A.CON_CUR_CD		               \n");
			sqlstr.append("         ,	A.CON_EXCHANGE_DATE		           \n");
			sqlstr.append("         ,	A.CON_WON_RATE		               \n");
			sqlstr.append("         ,	A.PAY_ITEM		                   \n");
			sqlstr.append("         ,	A.COST_CODE		                   \n");
			sqlstr.append("         ,	A.COST_TYPE		                   \n");
			sqlstr.append("         ,	A.WORK_KIND_CD		               \n");
			sqlstr.append("         ,	A.CONTRACTID		               \n");
			sqlstr.append("         ,	A.G_ID		                       \n");
			sqlstr.append("         ,	A.P_ID		                       \n");
			sqlstr.append("         ,	1		                           \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	NULL		                       \n");
			sqlstr.append("         ,	'00'		                       \n");
			sqlstr.append("         ,	BID_NO		                       \n");
			sqlstr.append("         ,	BID_DEG		                       \n");
			sqlstr.append("         ,	MONTH_ACMP_CNT		               \n");
			sqlstr.append("         ,	OBJ_RCPT_DAY		               \n");
			sqlstr.append("         ,	MODI_REQ_REASON		               \n");
			sqlstr.append("         ,	MODI_REQ_DT		                   \n");
			sqlstr.append("         ,   B.BEF_CONT_AMT 	                   \n");
			sqlstr.append("         ,   B.BEF_BUG_AMT		               \n");
			sqlstr.append("         ,   B.AFT_CONT_AMT		               \n");
			sqlstr.append("         ,   B.AFT_BUG_AMT  	                   \n");
			sqlstr.append("         ,   B.SP_REMARK		                   \n");
			sqlstr.append("         ,   B.REMARK		                   \n");
			sqlstr.append("         ,   A.OBC_MID_REQ_MONTH		           \n");
			sqlstr.append("         ,   A.OBC_MID_BILL_DATE		           \n");
			sqlstr.append("         ,   NVL(A.SUPP_DIRECT_YN,'N') AS SUPP_DIRECT_YN		           \n");	//2021.04.01(하도급직불여부 추가)
			sqlstr.append("         ,   NVL(A.LABOR_USE_YN,'N') AS LABOR_USE_YN			           \n");	//2022.03.21(노무비닷컴 사용여부 추가)
			sqlstr.append("         ,	?		                           \n");
			sqlstr.append("         ,	SYSDATE				               \n");
			sqlstr.append("  FROM SUB2000T A				           \n");
			sqlstr.append("     , SUB3000T B   			           \n");
			sqlstr.append("  WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID   \n");
			sqlstr.append("  AND B.SUB_CONT_REQ_ID = ?			           \n");
			sqlstr.append("   AND A.SUB_CONT_MOD_ID= ?   		           \n");
					
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(maxSubContid);
			persistent.addParameter("99990");
			persistent.addParameter(valueObject.get("KEY_COL2"));
			persistent.addParameter(valueObject.get("KEY_COL1"));
			persistent.execute();
			
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }        
    }
	
	/**
     * 변경 계약 업체 정보 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean addChangeContSupport(Connection connection, ValueObject valueObject, String maxSubContid) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append("INSERT INTO CONT_OUT_SUPP 							\n");
			sqlstr.append("       ( 	SUB_CONT_MOD_ID		\n");
			sqlstr.append("         ,	CORP_ID		\n");
			sqlstr.append("         ,	SIGN_SEQ		\n");
			sqlstr.append("         ,	VENDCD		\n");
			sqlstr.append("         ,	MEMBER_NAME		\n");
			sqlstr.append("         ,	BOSS_NAME		\n");
			sqlstr.append("         ,	POST_CODE		\n");
			sqlstr.append("         ,	ADDRESS		\n");
			sqlstr.append("         ,	TEL_NUM		\n");
			sqlstr.append("         ,	MEMBER_SLNO		\n");
			sqlstr.append("         ,	USER_NAME		\n");
			sqlstr.append("         ,	DIVISION		\n");
			sqlstr.append("         ,	POSITION		\n");
			sqlstr.append("         ,	HP		\n");
			sqlstr.append("         ,	EMAIL		\n");
			sqlstr.append("         ,	CRE_BY		\n");
			sqlstr.append("         ,	CRE_DATE	)	\n");
			sqlstr.append("SELECT ?	\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(D.AFT_CORP_ID, A.CORP_ID), A.CORP_ID)	\n");
			sqlstr.append("         ,	A.SIGN_SEQ		\n");
			sqlstr.append("         ,	NVL(E.CORP_REG_NO, A.VENDCD) VENDCD		\n"); 
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.CORP_KOR, B.CORP_KOR), A.MEMBER_NAME), C.CORP_KOR) AS MEMBER_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.REP_NAME, B.REP_NAME), A.BOSS_NAME), C.REP_NAME) AS BOSS_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.POST_NO, B.POST_NO), A.POST_CODE), C.POST_NO) AS POST_CODE		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.ADDRESS, B.ADDRESS), A.ADDRESS), C.ADDRESS) AS ADDRESS		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.CORP_TEL, B.CORP_TEL), A.TEL_NUM), A.TEL_NUM) AS TEL_NUM		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(NVL(E.COPR_NO, B.COPR_NO), A.MEMBER_SLNO), C.COPR_NO) AS MEMBER_SLNO		\n");
			sqlstr.append("         ,	A.USER_NAME		\n");
			sqlstr.append("         ,	A.DIVISION		\n");
			sqlstr.append("         ,	A.POSITION		\n");
			sqlstr.append("         ,	A.HP		\n");
			sqlstr.append("         ,	A.EMAIL		\n");
			sqlstr.append("         ,	?		\n");
			sqlstr.append("         ,	SYSDATE				\n");
			sqlstr.append("  FROM CONT_OUT_SUPP A, SUPP_INFO B  ,HEAD_OFFICE_INFO C, SUB3000T D,  SUPP_INFO E 				\n");
			sqlstr.append(" WHERE  A.CORP_ID = B.CORP_ID(+)						\n");
			sqlstr.append(" AND A.SUB_CONT_MOD_ID = D.SUB_CONT_MOD_ID			\n");
			sqlstr.append(" AND D.AFT_CORP_ID = E.CORP_ID(+)         			\n");
			sqlstr.append(" AND D.SUB_CONT_REQ_ID = ?							\n");
			sqlstr.append(" AND A.SUB_CONT_MOD_ID = ?							\n");			
						
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(maxSubContid);
			persistent.addParameter("99990");
			persistent.addParameter(valueObject.get("KEY_COL2"));
			persistent.addParameter(valueObject.get("KEY_COL1"));
			persistent.execute();
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }        
    }
	
	//2016.6.16 변경시행결의 결재완료알림 
	 public void Send_ToeTEC(ValueObject params) throws Exception {
			HandlerStorage Stroage = new HandlerStorage();
			MessageSend messageSend = new MessageSend(Stroage);
	     	ValueObject bid_Master = cont_info(params);
	     			
	        StringBuffer mailtitle = new StringBuffer();
	        StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();
	       
	        mailtitle.append("* [알림]변경시행결의 승인");
	        mailtitlein.append("* 변경시행결의 승인");
	        mailContent.append("아래와 같이 변경시행결의가 완료되었음을 알려드립니다.<br>");
	        mailContent.append("<style type='text/css'>");
	        mailContent.append("td {font-size:12px;}");
	        mailContent.append("</style>");
	        mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        mailContent.append("<br>");
	        mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NAME") +  "<br>");
	        mailContent.append("2.계약번호 : " + bid_Master.get("CON_DOC_NO") + "<br>");
	        mailContent.append("3.계약명     : " + bid_Master.get("BUILD_NAME") + "<br>");
	        mailContent.append("4.외주담당자 : " + bid_Master.get("CON_CHARGE_NAME") + "<br>" );
	        mailContent.append("5.계약업체 : " + bid_Master.get("MEMBER_NAME") + "<br>" );
	        mailContent.append("6.변경금액 " );
	        mailContent.append("<table width='700' cellspacing='0' cellpadding='0' bordercolordark='#FFFFFF' bordercolorlight='#AEAFAF' border='1'>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>&nbsp;</td><td align='Center' bgcolor='rgb(208,216,232)'>원계약</td><td align='Center' bgcolor='rgb(208,216,232)'>변경계약</td><td align='Center' bgcolor='rgb(208,216,232)'>증감(▲▼)</td></tr>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>도급금액</td><td align='Right'>&nbsp;" + bid_Master.get("BEF_CONT_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("AFT_CONT_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("CHG_CONT_AMT") + "</td></tr>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>예산금액</td><td align='Right'>&nbsp;" + bid_Master.get("BEF_BUG_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("AFT_BUG_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("CHG_BUG_AMT") + "</td></tr>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>계약금액</td><td align='Right'>&nbsp;" + bid_Master.get("BEF_SUPP_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("AFT_SUPP_AMT") + "</td><td align='Right'>&nbsp;" + bid_Master.get("CHG_SUPP_AMT") + "</td></tr>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>기타</td><td align='Right'>&nbsp;" + bid_Master.get("BEF_ETC") + "</td><td align='Right'>&nbsp;" + bid_Master.get("AFT_ETC") + "</td><td align='Right'>&nbsp;" + bid_Master.get("CHG_ETC") + "</td></tr></table>" );
	        mailContent.append("7.계약기간 : " +bid_Master.get("WORK_FROM_DT")+" ~ "+ bid_Master.get("WORK_END_DT")+ "<br>");
	        mailContent.append("8.변경사유 <br>");
	        mailContent.append("&nbsp;" + bid_Master.get("REMARK") + "<br>");
	        mailContent.append("9.특기사항 <br>");
	        mailContent.append("&nbsp;" + bid_Master.get("SP_REMARK") + "<br>");
	        mailContent.append("10.비고 <br>");
	        mailContent.append("&nbsp;1)계약시 추가 반영할 사항은 외주 담당자에게 연락하시기 바랍니다.   <br>");
	        mailContent.append("&nbsp;2)변경계약서는 변경시행결의 승인 후 1~2일 내 업체에 전달됩니다.   <br>");
	        mailContent.append("&nbsp;3)계약 완료 후 계약서 및 관련 서류는 전자조달시스템의 외주-계약결과에서 조회가능합니다.  <br>");
	        mailContent.append("</font><br>");
	        //test
	        messageSend.sendEmail(bid_Master.get("O_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1");
	        //변경계약요청자 
	        if(!bid_Master.get("E_MAIL").isEmpty()){//
	    	    messageSend.sendEmail(bid_Master.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1");
	        }	     	
		}
	 
	//2017.2.7 외주예산관리 추가
	 public boolean addBcsCont(Connection connection, ValueObject valueObject, String maxSubContid) throws Exception {
			//WAFLogger.debug("valueObject : " + valueObject);
	        Persistent persistent = new PersistentImpl(connection);
	        StrBuffer sqlstr = new StrBuffer();
	        try {
				sqlstr.append("INSERT INTO BCS_MR 			                                                               \n");
				sqlstr.append("       (  DEPT_CD					                                                               \n");
				sqlstr.append("         ,MR_ID					                                                                   \n");
				sqlstr.append("         ,KIND_GB   				                                                                   \n");
	            sqlstr.append("         ,MR_NO					                                                                   \n");
	            sqlstr.append("         ,TITLE					                                                                   \n");
	            sqlstr.append("         ,CONAMT					                                                                   \n");
	            sqlstr.append("         ,CONDATE					                                                               \n");
	            sqlstr.append("         ,CONT_ID					                                                               \n");
	            sqlstr.append("         ,CONT_MOD_ID				                                                               \n");
	            sqlstr.append("         ,VENDOR_NM					                                                               \n");
	            sqlstr.append("         ,CRE_DATE					                                                               \n");
	            sqlstr.append("         ,CRE_BY					                                                                   \n");
	            sqlstr.append("         ,CURRENCY_GB )				                                                               \n");
	            sqlstr.append("   SELECT A.DEPT_CD	                                                                               \n");//DEPT_CD		
				sqlstr.append("         ,	(SELECT DECODE(COUNT(*), 0, 1, MAX(MR_ID)+1) FROM BCS_MR WHERE DEPT_CD = A.DEPT_CD  )  \n");//MR_ID			
				sqlstr.append("         ,	'O'		                                                                               \n");//DEMANDED_KIND	
				sqlstr.append("         ,	 SUBSTR(A.REQ_DOC_NO, 11, LENGTH(A.REQ_DOC_NO)-10)		                               \n");//MR_NO			
				sqlstr.append("         ,	 A.BUILD_NAME		                                                                   \n");//TITLE			
				sqlstr.append("         ,	B.AFT_SUPP_AMT                                                                         \n");//CONAMT			
				sqlstr.append("         ,	SYSDATE		                                                                           \n");//CONDATE		
				sqlstr.append("         ,	A.SUB_CONT_ID		                                                                   \n");//CONT_ID		
				sqlstr.append("         ,	? 	                                                                                   \n");//CONT_MOD_ID	
				sqlstr.append("         ,   NVL(D.CORP_KOR, C.MEMBER_NAME) MEMBER_NAME                                             \n");//VENDOR_NM		
				sqlstr.append("         ,	SYSDATE		                                                                           \n");//CRE_DATE		
				sqlstr.append("         ,	999	               	                                                                   \n");//CRE_BY			
				sqlstr.append("         ,	A.CON_CUR_CD		                                                                   \n");//CURRENCY_GB	
				sqlstr.append("   FROM SUB2000T A	                                  		                               \n");
				sqlstr.append("      , SUB3000T B   			                               	                           \n");
				sqlstr.append("      , CONT_OUT_SUPP C   			                       	                               \n");
				sqlstr.append("      , SUPP_INFO D      			                       	                               \n");
				sqlstr.append("  WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID						                               \n");
				sqlstr.append("    AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID						                               \n");
				sqlstr.append("    AND C.SIGN_SEQ = 2				                                                               \n");
				sqlstr.append("    AND B.AFT_CORP_ID = D.CORP_ID(+)                                                                \n");
				sqlstr.append("    AND B.SUB_CONT_REQ_ID = ?				                                                       \n");
				sqlstr.append("    AND A.SUB_CONT_MOD_ID= ?   				                                                       \n");
						
				persistent.setStatement(sqlstr.toString());
				persistent.addParameter(maxSubContid);
				persistent.addParameter(valueObject.get("KEY_COL2"));
				persistent.addParameter(valueObject.get("KEY_COL1"));
				persistent.execute();
				
	            return true;
	        }
	        catch(Exception e) {
	            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
	            WAFLogger.error("SQL : " + sqlstr);
	            WAFLogger.error(e);
	            //WAFLogger.debug("Exception : " + e.getMessage());
	            throw e;
	        }        
	    }
	 
	 //2018.10.31 외주내역추가
	 public boolean addContItem(Connection connection, ValueObject valueObject, String maxSubContid) throws Exception {
			//WAFLogger.debug("valueObject : " + valueObject);
	        Persistent persistent = new PersistentImpl(connection);
	        StrBuffer sqlstr = new StrBuffer();
	        try {
	        	sqlstr.append("INSERT INTO CONT_OUT_ITEM (															\n");
	        	sqlstr.append("        SUB_CONT_MOD_ID															\n");
	        	sqlstr.append("        ,SQ_CORD															\n");
	        	sqlstr.append("        ,NO_CORD															\n");
	        	sqlstr.append("        ,SUB_CONT_ID															\n");
	        	sqlstr.append("        ,MATERIAL_CD															\n");
	        	sqlstr.append("        ,ITEM_NAME															\n");
	        	sqlstr.append("        ,SUPPLY_TAMT															\n");
	        	sqlstr.append("        ,SUPPLY_NAMT															\n");
	        	sqlstr.append("        ,SUPPLY_AMT															\n");
	        	sqlstr.append("        ,CD_CORD											\n");
	            sqlstr.append("        ,SEQ_BUDGET											\n");
	            sqlstr.append("        ,SQ_BUDGET											\n");
	            sqlstr.append("        ,CD_CSTR											\n");
	            sqlstr.append("        ,DL_CSTR											\n");
	            sqlstr.append("        ,CD_ACT											\n");
	            sqlstr.append("        ,NM_CSTR											\n");
	            sqlstr.append("        ,CD_SIZE											\n");
	            sqlstr.append("        ,SIZE_CSTR											\n");
	            sqlstr.append("        ,UNIT_CSTR											\n");
	            sqlstr.append("        ,QTY_ORD											\n");
	            sqlstr.append("        ,UNT_ORD											\n");
	            sqlstr.append("        ,AMT_ORD											\n");
	            sqlstr.append("        ,QTY_ACT											\n");
	            sqlstr.append("        ,UNT_ACT											\n");
	            sqlstr.append("        ,AMT_ACT											\n");
	            sqlstr.append("        ,QTY_TRNS_ORD											\n");
	            sqlstr.append("        ,UNT_TRNS_ORD											\n");
	            sqlstr.append("        ,AMT_TRNS_ORD											\n");
	            sqlstr.append("        ,QTY_TRNS_ACT											\n");
	            sqlstr.append("        ,UNT_TRNS_ACT											\n");
	            sqlstr.append("        ,AMT_TRNS_ACT											\n");
	            sqlstr.append("        ,QTY_CORD											\n");
	        	sqlstr.append("        ,CRE_BY															\n");
	        	sqlstr.append("        ,CRE_DATE															\n");
	        	sqlstr.append(")															\n");
	        	sqlstr.append("SELECT  ? AS SUB_CONT_MOD_ID															\n");
	        	sqlstr.append("        ,A.SQ_CORD																	\n");
	        	sqlstr.append("        ,A.NO_CORD																	\n");
	        	sqlstr.append("        ,(SELECT SUB_CONT_ID FROM SUB2000T WHERE SUB_CONT_MOD_ID = ?) AS SUB_CONT_ID	\n");
	        	sqlstr.append("        ,'0101'||A.CD_CSTR||A.DL_CSTR AS MATERAIL_CD											\n");
	        	sqlstr.append("        ,A.NM_CSTR AS ITEM_NAME														\n");
	        	sqlstr.append("        ,NVL(B.SUPPLY_TAMT,0) AS SUPPLY_TAMT											\n");
	        	sqlstr.append("        ,NVL(B.SUPPLY_NAMT,0) AS SUPPLY_NAMT											\n");
	        	sqlstr.append("        ,NVL(B.SUPPLY_AMT,0) AS SUPPLY_AMT											\n");
	        	sqlstr.append("        ,A.CD_CORD											\n");
	            sqlstr.append("        ,A.SEQ_BUDGET											\n");
	            sqlstr.append("        ,A.SQ_BUDGET											\n");
	            sqlstr.append("        ,A.CD_CSTR											\n");
	            sqlstr.append("        ,A.DL_CSTR											\n");
	            sqlstr.append("        ,A.CD_ACT											\n");
	            sqlstr.append("        ,A.NM_CSTR											\n");
	            sqlstr.append("        ,A.CD_SIZE											\n");
	            sqlstr.append("        ,A.SIZE_CSTR											\n");
	            sqlstr.append("        ,A.UNIT_CSTR											\n");
	            sqlstr.append("        ,A.QTY_ORD											\n");
	            sqlstr.append("        ,A.UNT_ORD											\n");
	            sqlstr.append("        ,A.AMT_ORD											\n");
	            sqlstr.append("        ,A.QTY_ACT											\n");
	            sqlstr.append("        ,A.UNT_ACT											\n");
	            sqlstr.append("        ,A.AMT_ACT											\n");
	            sqlstr.append("        ,A.QTY_TRNS_ORD											\n");
	            sqlstr.append("        ,A.UNT_TRNS_ORD											\n");
	            sqlstr.append("        ,A.AMT_TRNS_ORD											\n");
	            sqlstr.append("        ,A.QTY_TRNS_ACT											\n");
	            sqlstr.append("        ,A.UNT_TRNS_ACT											\n");
	            sqlstr.append("        ,A.AMT_TRNS_ACT											\n");
	            sqlstr.append("        ,A.QTY_CORD											\n");
	        	sqlstr.append("        ,A.CRE_BY																	\n");
	        	sqlstr.append("        ,SYSDATE																		\n");
	        	sqlstr.append("FROM    SUB3000T_DETAIL A															\n");
	        	sqlstr.append("        ,(																			\n");
	        	sqlstr.append("        SELECT  *																	\n");
	        	sqlstr.append("        FROM    CONT_OUT_ITEM														\n");
	        	sqlstr.append("        WHERE   SUB_CONT_MOD_ID = ?													\n");
	        	sqlstr.append("        ) B																			\n");
	        	sqlstr.append("WHERE   A.CD_CSTR = B.CD_CSTR(+)														\n");
	        	sqlstr.append("AND     A.DL_CSTR = B.DL_CSTR(+)														\n");
	        	sqlstr.append("AND     A.SUB_CONT_REQ_ID = ?														\n");
	        	sqlstr.append("ORDER BY A.SQ_CORD, A.NO_CORD														\n");
				 
				persistent.setStatement(sqlstr.toString());
				persistent.addParameter(maxSubContid);
				persistent.addParameter(valueObject.get("KEY_COL1")); 
				persistent.addParameter(valueObject.get("KEY_COL1")); 
				persistent.addParameter(valueObject.get("KEY_COL2")); 
				persistent.execute();
				
	            return true;
	        }
	        catch(Exception e) {
	            WAFLogger.error(this.getClass().getName() + " --> addContItem() : " + e.getMessage());
	            WAFLogger.error("SQL : " + sqlstr);
	            WAFLogger.error(e);
	            //WAFLogger.debug("Exception : " + e.getMessage());
	            throw e;
	        }        
	    }
	 
	 public ValueObject cont_info(ValueObject param) {

			Search search = new SearchImpl();
			 ValueObject valueobject = null;
			RowSet rowSet = new RowSetImpl();
	        
	        StrBuffer sqlstr = new StrBuffer();
	        try{ 
	        	sqlstr.append(" SELECT A.DEPT_NAME															  	    \n");
	        	sqlstr.append("      , A.CON_DOC_NO																    \n");
	        	sqlstr.append("      , A.BUILD_NAME 																\n");
	        	sqlstr.append("      , A.CON_CHARGE_NAME															\n");
	        	sqlstr.append("      , NVL(F.CORP_KOR, C.MEMBER_NAME) MEMBER_NAME									\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.BEF_CONT_AMT, 0) BEF_CONT_AMT    	                    \n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.AFT_CONT_AMT, 0) AFT_CONT_AMT 						\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.CHG_CONT_AMT, 0) CHG_CONT_AMT 						\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.BEF_BUG_AMT, 0) BEF_BUG_AMT 							\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.AFT_BUG_AMT, 0) AFT_BUG_AMT 							\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.CHG_BUG_AMT, 0) CHG_BUG_AMT 							\n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.BEF_SUPP_AMT,F_GET_CURRENCY_DIGIT(A.CON_CUR_CD))	BEF_SUPP_AMT	 \n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.AFT_SUPP_AMT,F_GET_CURRENCY_DIGIT(A.CON_CUR_CD))	AFT_SUPP_AMT	 \n");
	        	sqlstr.append("      , F_GET_D_CURRENCY(B.CHG_SUPP_AMT,F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) 	CHG_SUPP_AMT	 \n");
	        	sqlstr.append("      , A.START_WORK_DATE 															\n");
	        	sqlstr.append("      , TO_CHAR(A.START_WORK_DATE, 'YYYY-MM-DD') WORK_FROM_DT  						\n");
	        	sqlstr.append("      , TO_CHAR(TO_DATE(B.AFT_CONT_DATE), 'YYYY-MM-DD') WORK_END_DT  			    \n");
	        	sqlstr.append("      , B.BEF_ETC																    \n");
	        	sqlstr.append("      , B.AFT_ETC																    \n");
	        	sqlstr.append("      , B.CHG_ETC																    \n");
	        	sqlstr.append("      , B.REMARK																        \n");
	        	sqlstr.append("      , B.SP_REMARK																    \n");
	        	sqlstr.append("      , D.E_MAIL 																    \n");
	        	sqlstr.append("      , E.EMAIL O_EMAIL															    \n");
	        	sqlstr.append("  FROM SUB2000T A															    \n");
	        	sqlstr.append("      ,SUB3000T B															    \n");
	        	sqlstr.append("      ,CONT_OUT_SUPP C														\n");
	        	sqlstr.append("      ,PUB0120T D  	   															    \n");
	        	sqlstr.append("      ,CONT_OUT_SUPP E  													    \n");
	        	sqlstr.append("      ,SUPP_INFO F   													    \n");
	        	sqlstr.append(" WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID 										\n");
	        	sqlstr.append("   AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID											\n");
	        	sqlstr.append("   AND C.SIGN_SEQ = 2																\n");
	        	sqlstr.append("   AND A.SUB_CONT_MOD_ID = E.SUB_CONT_MOD_ID											\n");
	        	sqlstr.append("   AND E.SIGN_SEQ = 1																\n");
	        	sqlstr.append("   AND B.CRE_BY = TO_CHAR(D.ACCT_ID)			          								\n");
	        	sqlstr.append("   AND B.AFT_CORP_ID = F.CORP_ID(+)			          								\n");
	        	sqlstr.append("   AND B.SUB_CONT_MOD_ID = ?															\n"); 
	        	sqlstr.append("   AND B.SUB_CONT_REQ_ID = ?															\n"); 
	        	 
	        	search.setStatement(sqlstr.toString());
				search.addParameter(param.get("KEY_COL1"));
				search.addParameter(param.get("KEY_COL2"));
				search.setStatement(sqlstr.toString());
	            rowSet = search.execute();
	            if( rowSet != null && rowSet.size() > 0 ){
	            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
	            }
	        }
	        catch (WAFSQLException se) {
	            Logger.err.println("SQL : \n" + se.getStatement());
	            Logger.err.println("PARAM : \n" + se.getParameter());
	        }
	        return valueobject;
		}
}