/**
* PURPOSE : Confirmation of Purchase receipt
* AUTHOR :   
*/ 

package ibase.webitm.ejb.dis.adv;

import ibase.utility.EMail;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.sys.*;
import ibase.system.config.*;
import ibase.webitm.utility.ITMException;
import java.text.*;

import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.*;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.ejb.dis.InvAllocTraceBean;	

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import javax.ejb.Stateless; 
import org.w3c.dom.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.parsers.*;
import java.text.SimpleDateFormat;

@Stateless 
public class PoRcpConf extends ActionHandlerEJB implements PoRcpConfLocal, PoRcpConfRemote 
{
    /**
	 * The public method is used for confirming the purchase receipt transaction
	 * Returns confirmation message on successfull confirm otherwise returns error message
	 * @param tranId is the transaction id to be confirmed
	 * @param xtraParams contais additional information such as loginEmpCode,loginCode,chgTerm etc
	 * @param forcedFlag (true or false)
	 */
	String userId = "", termId = "";
	GenericUtility genericUtility = GenericUtility.getInstance();
	CommonConstants commonConstants = new CommonConstants();
	
	// overloaded method added to call the confirm method from postsave component - 25/11/11 - Gulzar
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";
		boolean isConn= false;
		Connection conn = null;

		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag, conn, isConn );

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	//Commented and new parameters(conn, connStatus) to function - 25/11/11 - Gulzar
    /**
	 * The public method is used for confirming the purchase receipt transaction
	 * Returns confirmation message on successfull confirm otherwise returns error message
	 * @param tranId is the transaction id to be confirmed
	 * @param xtraParams contais additional information such as loginEmpCode,loginCode,chgTerm etc
	 * @param forcedFlag (true or false)
	 * @param Connection (true or false) - Connection to database
	 * @param connStatus (true or false) - This status indicate whether to commit the trasaction or not (true - commit, false - no commit)
	 */
	//public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException, ITMException
	public String confirm( String tranId, String xtraParams,String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{
		//Connection conn = null; //Gulzar - 25/11/11
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		
		String retString = "";
		String sql = "";
		String conf = "";
		String siteRcp = "";
		
		try
		{
			if ( conn == null )//changed by Gulzar - 25/11/11
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				connStatus = true;
			}
			
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		    userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			if (userId == null || userId.trim().length() == 0)
			{
				userId = "SYSTEM";
			}
			if (termId == null || termId.trim().length() == 0)
			{
				termId = "SYSTEM";
			}

			sql = "SELECT CONFIRMED, SITE_CODE FROM PORCP WHERE TRAN_ID= ?"; 
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				conf = checkNull(rs.getString("CONFIRMED"));
				siteRcp = checkNull(rs.getString("SITE_CODE"));
			}
			
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( conf.equalsIgnoreCase("Y") )
			{
				retString = itmDBAccessLocal.getErrorString("","VTCONF1","");
				return retString;
			}
			else
			{
				retString = retrieveReceipt(tranId, 1,conn, xtraParams);
				if (retString == null || retString.trim().length() == 0)
				{
					////////////////////// EDI creation
					String ediOption = "";
					String dataStr = "";
					sql = "SELECT EDI_OPTION FROM TRANSETUP WHERE TRAN_WINDOW = 'w_porcp' ";
					pstmtSql = conn.prepareStatement(sql);
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						ediOption = checkNull(rs.getString("EDI_OPTION"));
					}
					rs.close();rs = null;
					pstmtSql.close();pstmtSql = null;

					if ( "1".equals(ediOption.trim()) )
					{
						CreateRCPXML createRCPXML = new CreateRCPXML("w_porcp","tran_id");
						dataStr = createRCPXML.getTranXML( tranId, conn );
						System.out.println( "dataStr =[ "+ dataStr + "]" );
						Document ediDataDom = genericUtility.parseString(dataStr);

						E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
						retString = e12CreateBatchLoad.createBatchLoad( ediDataDom, "w_porcp", "0", xtraParams, conn );
						createRCPXML = null;
						e12CreateBatchLoad = null;

						if( retString != null && "SUCCESS".equals(retString) )
						{
							System.out.println("retString from batchload = ["+retString+"]");
						}
					}
					/////////////////////

					if( connStatus )//Condition added - 25/11/11 - Gulzar as confirm method is called from post save component
					{
						conn.commit();
						retString = itmDBAccessLocal.getErrorString("","VTMCONF2","");
					}
				}
				else
				{
					conn.rollback();
				}
			}
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				//if( conn != null && ! conn.isClosed() ) // Gulzar - 25/11/11
				if( conn != null && !conn.isClosed() && connStatus ) //Gulzar - 25/11/11
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}

	public String retrieveReceipt(String tranId, int a, Connection conn, String xtraParams) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rs = null;
		
		String sql = "";
		String dbName = "";
		String retString = "";
		String purcOrder = "";
		String purcOrderDet = "";
		String porcpTranType = "";
		String confirmed = "";
		String qcReqd = "";
		String jobWorkType = "";
		String subContractType = "";
		String pordType = "";
		String autoBkFlush = "";
		String empCode = "";
		String siteCode = "";
		String tranSer = "";
		String value = "";
		String lineNoOrd = "";
		String itemCode = "";
		String nullPo = "";
		String policyNo = "";
		String pervPolicy = "";
		String ledgPostConf = "";
		String runMode = "i";
		String warning = "";
		String cwipTranType = "";
		String tranType = "";
		String saleOrder = "";

		double qtyRcp = 0.0;
		double ordQty = 0.0;
		double dlvQty = 0.0;
		double totRcp = 0.0;
		double qtyTol = 0.0;

		long detCnt = 0;
		long noStk = 0;
		long cnt = 0;
		long pcnt = 0;
		long qcCnt = 0;

		int updCnt; 

		java.sql.Timestamp today = null;
		java.sql.Timestamp confDate = null;
		java.util.Date date = null;
		
		try
		{
			DistCommon distCommon = new DistCommon();
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			dbName = CommonConstants.DB_NAME;

			if( dbName.equalsIgnoreCase("db2"))
			{
				sql = "SELECT TRAN_ID,(CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END), (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE		   QC_REQD END), PURC_ORDER, TRAN_TYPE FROM PORCP WHERE TRAN_ID = ? FOR UPDATE ";
			}
			else if ( dbName.equalsIgnoreCase("mssql") )
			{
				sql = "SELECT TRAN_ID,(CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END), (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE		   QC_REQD END), PURC_ORDER , TRAN_TYPE FROM PORCP (UPDLOCK) WHERE TRAN_ID = ?"; 
			}
			else
			{
				sql = "SELECT TRAN_ID,(CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END), (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE		   QC_REQD END), PURC_ORDER , TRAN_TYPE FROM PORCP WHERE TRAN_ID = ? FOR UPDATE NOWAIT";
			}
			try
			{
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					tranId = checkNull(rs.getString("TRAN_ID"));
					purcOrder = checkNull(rs.getString("PURC_ORDER"));
					porcpTranType = checkNull(rs.getString("TRAN_TYPE"));
					pstmtSql.close();
					pstmtSql = null;
					rs.close();
					rs = null;
				}
				else
				{
					pstmtSql.close();
					pstmtSql = null;
					rs.close();
					rs = null;
					retString = itmDBAccessLocal.getErrorString("","VTLCKERR","");
					return retString; 
				}
			}
			catch(Exception e)
			{
				retString = itmDBAccessLocal.getErrorString("","VTLCKERR","");
				throw new ITMException(e);
			}
			jobWorkType = distCommon.getDisparams("999999", "JOBWORK_TYPE", conn);
			if ( jobWorkType == null )
			{
				jobWorkType = "";
			}
			subContractType = distCommon.getDisparams("999999", "SUBCONTRACT_TYPE", conn);
			if ( subContractType == null )
			{
				subContractType = "";
			}

			sql = "SELECT PORD_TYPE FROM PORDER WHERE PURC_ORDER = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, purcOrder);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				pordType = checkNull(rs.getString("PORD_TYPE"));
			}
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;
			
			// 26/10/11 manoharan enabled backflush
			if (( pordType.equalsIgnoreCase(jobWorkType.trim()) || pordType.equalsIgnoreCase(subContractType.trim()) ) && purcOrder.length() > 0 )
			{
				autoBkFlush = distCommon.getDisparams("999999","AUTO_RCP_BKFLUSH", conn);
			}
			else
			{
				autoBkFlush = "N";
			}

			sql = "SELECT COUNT(1) AS COUNT FROM PORCPDET WHERE TRAN_ID = ?" ;
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				detCnt = rs.getLong("COUNT");
			}

			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}

			sql = "SELECT COUNT(1) AS COUNT FROM PORCPDET WHERE TRAN_ID = ? AND CASE WHEN EFFECT_STOCK IS NULL THEN 'Y' ELSE			   EFFECT_STOCK END  = 'N'";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				noStk = rs.getLong("COUNT");
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			/*// to be implemented later
			 if ( autoBkFlush.trim().equals("Y") && detCnt > noStk )
			{
				retString = autoBackflush( tranId, conn);
				if ( retString != null ) && ( retString.trim().length() > 0)
				{
					break;
				}
			}*/

			retString = chkUpdAddlCost(tranId, conn);

			if ( retString != null && retString.trim().length() > 0)
			{
				return retString;
			}

			retString = checkReplVal(tranId, conn);

			if ( retString != null && retString.trim().length() > 0)
			{
				return retString;
			}
			
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, userId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				empCode = checkNull(rs.getString("EMP_CODE"));
			}
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;

			sql = "SELECT SITE_CODE, TRAN_SER FROM PORCP WHERE TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				siteCode = checkNull(rs.getString("SITE_CODE"));
				tranSer = checkNull(rs.getString("TRAN_SER"));
			}
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;
			
			value = distCommon.getDisparams("999999", "RCP_UOM_VARIANCE", conn);
			if ( value == null )
			{
				retString = itmDBAccessLocal.getErrorString("","VTUOMVARPARM","");
				return retString;
			}
			if (value == null) 
			{
				value = "0";
			}
			double val = 0.0;
			try
			{
				val = Double.parseDouble(value);
			}
			catch(Exception e){}

			sql = "SELECT COUNT(*) AS COUNT FROM PORCPDET WHERE TRAN_ID = ? AND ABS( ((CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE		   QUANTITY__STDUOM END) * (CASE WHEN RATE__STDUOM IS NULL THEN 0 ELSE RATE__STDUOM END) - (CASE WHEN QUANTITY IS NULL		   THEN 0 ELSE QUANTITY END) * (CASE WHEN RATE IS NULL THEN 0 ELSE RATE END)) ) > ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			pstmtSql.setDouble(2, val);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				cnt = rs.getLong("COUNT");
			}
			if ( cnt > 0 )
			{
				pstmtSql.close();
				pstmtSql = null;					
				rs.close();
				rs = null;
				retString = itmDBAccessLocal.getErrorString("","VTCONV","");
				return retString;
			}
			else
			{
				pstmtSql.close();
				pstmtSql = null;					
				rs.close();
				rs = null;
			}
			sql = "SELECT COUNT(*) AS COUNT FROM PORCPDET A, ITEM B WHERE A.ITEM_CODE = B.ITEM_CODE AND A.TRAN_ID = ? AND (CASE WHEN		   B.QC_REQD IS NULL THEN 'N' ELSE B.QC_REQD END) = 'Y' AND (CASE WHEN B.STK_OPT IS NULL THEN '0' ELSE B.STK_OPT END)		   <> '2' ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				cnt = rs.getLong("COUNT");
			}
			if ( cnt > 0 )
			{
				pstmtSql.close();
				pstmtSql = null;					
				rs.close();
				rs = null;
				retString = itmDBAccessLocal.getErrorString("","VTSTKOPT","");
				return retString;
			}
			else
			{
				pstmtSql.close();
				pstmtSql = null;					
				rs.close();
				rs = null;
			}

			sql = "SELECT PURC_ORDER,LINE_NO__ORD, ITEM_CODE, SUM(QUANTITY) QUANTITY FROM PORCPDET "
				+ " WHERE TRAN_ID = ? GROUP BY PURC_ORDER,LINE_NO__ORD, ITEM_CODE ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			
			nullPo = distCommon.getDisparams("999999","RCP_WO_PO", conn);

			if ( purcOrder != null  && nullPo != "Y") 
			{
				while ( rs.next() )
				{	
					purcOrder = checkNull(rs.getString("PURC_ORDER"));
					lineNoOrd = checkNull(rs.getString("LINE_NO__ORD"));
					itemCode = checkNull(rs.getString("ITEM_CODE"));
					qtyRcp = rs.getDouble("QUANTITY");

					lineNoOrd = "   " + lineNoOrd;
					lineNoOrd = lineNoOrd.substring( lineNoOrd.length()-3 );

					ResultSet rs1 = null;
					sql = "SELECT QUANTITY , (CASE WHEN DLV_QTY IS NULL THEN 0 ELSE DLV_QTY END) DLV_QTY FROM PORDDET WHERE PURC_ORDER = ? AND		   LINE_NO   = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, purcOrder);
					pstmtSql.setString(2, lineNoOrd);
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						ordQty = rs1.getDouble("QUANTITY");
						dlvQty = rs1.getDouble("DLV_QTY");
					}
					pstmtSql.close();
					pstmtSql = null;					
					rs1.close();
					rs1 = null;

					totRcp = qtyRcp + dlvQty;
					
					if ( totRcp > ordQty )
					{   
						sql = "SELECT (CASE WHEN QTY_TOL_PERC IS NULL THEN 0 ELSE QTY_TOL_PERC END) QTY_TOL_PERC FROM ITEM WHERE ITEM_CODE = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, itemCode);
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							qtyTol = rs1.getDouble("QTY_TOL_PERC");
						}
						pstmtSql.close();
						pstmtSql = null;
						rs1.close();
						rs1 = null;

						if ( ( (( totRcp - ordQty) / ordQty) * 100 ) > qtyTol )
						{
							retString = itmDBAccessLocal.getErrorString("","VTPOQTY2","");
						}
					}
				} 
				if( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			
			if ( ( retString != null ) && (retString.trim().length() > 0 ) )
			{
				return retString;
			}

			sql = "SELECT COUNT(DISTINCT PURC_ORDER) AS COUNT FROM PORCPDET WHERE TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				cnt = rs.getLong("COUNT");
			}
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;
			if ( cnt > 1 ) 
			{
				sql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE PURC_ORDER IN (SELECT DISTINCT PURC_ORDER FROM PORCPDET "
					+ " WHERE TRAN_ID = ?) AND POLICY_NO IS NOT NULL ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					pcnt = rs.getLong("COUNT");
				}
				pstmtSql.close();
				pstmtSql = null;					
				rs.close();
				rs = null;

				if ( pcnt > 0 )
				{
					sql = "SELECT DISTINCT PURC_ORDER FROM PORCPDET WHERE TRAN_ID = ? "; 
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);
					rs = pstmtSql.executeQuery();
				
					while ( rs.next() )
					{
						purcOrderDet = rs.getString("PURC_ORDER");

						ResultSet rs1 = null;
						sql = "SELECT POLICY_NO FROM PORDER WHERE PURC_ORDER = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, purcOrderDet);
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							policyNo = checkNull(rs1.getString("POLICY_NO"));
						}
						pstmtSql.close();
						pstmtSql = null;
						rs1.close();
						rs1 = null;
						if ( policyNo == null ) 
						{ 
							policyNo = "";
						}
						if (  pervPolicy == null  || pervPolicy.trim().length() == 0 )
						{
							pervPolicy = policyNo;
						}
						else if ( pervPolicy.trim() != policyNo.trim() ) 
						{
							retString = itmDBAccessLocal.getErrorString("","VTPONOSAME","");
						}
					}
					if( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				if (  retString != null  && retString.trim().length() > 0  )
				{
					return retString;
				}
				
				
			}

			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, userId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				empCode = checkNull(rs.getString("EMP_CODE"));
			}	
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;

			sql = "SELECT (CASE WHEN LEDG_POST_CONF IS NULL THEN 'N' ELSE LEDG_POST_CONF END ) LEDG_POST_CONF FROM TRANSETUP WHERE LOWER(TRAN_WINDOW) = 'W_PORCP' ";
			pstmtSql = conn.prepareStatement(sql);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				ledgPostConf = checkNull(rs.getString("LEDG_POST_CONF"));
			}	
			pstmtSql.close();
			pstmtSql = null;					
			rs.close();
			rs = null;

			if ( ledgPostConf == null )
			{	
				ledgPostConf = "N";
			}
			if ( ledgPostConf.equalsIgnoreCase("Y") && runMode != "B" )
			{
				today = new java.sql.Timestamp(System.currentTimeMillis());
				java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateFormat());
				date = sdf.parse(today.toString());
				today =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
				
				sql = "UPDATE PORCP SET EMP_CODE__APRV = ?, CHG_TERM = ?, TRAN_DATE = ? WHERE TRAN_ID = ?";
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setString(1, empCode);
				pstmtUpd.setString(2, termId);
				pstmtUpd.setTimestamp(3, today);
				pstmtUpd.setString(4, tranId);
				updCnt = pstmtUpd.executeUpdate();
				pstmtUpd.close();
				pstmtUpd = null;					
			}
			else
			{
				sql = "UPDATE PORCP SET EMP_CODE__APRV = ?, CHG_TERM = ? WHERE TRAN_ID = ?";
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setString(1, empCode);
				pstmtUpd.setString(2, termId);
				pstmtUpd.setString(3, tranId);
				updCnt = pstmtUpd.executeUpdate();
				pstmtUpd.close();
				pstmtUpd = null;					
			}

			// 26/10/11 manoharan create QC order
			qcCnt = 0;
			// not required for Taro to be done later
			if ( detCnt > noStk) 
			{
				retString = createQc( tranId, siteCode, conn);
				if ( ( retString != null ) && (retString.trim().length() > 0) )
				{
					//break;
					return retString;
				}
				sql = "SELECT COUNT(1) AS COUNT FROM QC_ORDER WHERE PORCP_NO = ? AND STATUS = 'U' "; 
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					qcCnt = rs.getLong("COUNT");
				}		
				if( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
			} 
			
			// end 26/10/11 manoharan create QC order
			retString = confirm( tranId, 1, warning, conn, xtraParams);

			if ( ( retString != null ) && ( retString.trim().length() > 0))
			{
				conn.rollback();
				return retString;
			}
			/*  // not required for Taro to be done later
			if ( qcCnt == 0 )
			{
				pordType = pordType.trim();
				if ( pordType.equalsIgnoreCase("Q") || pordType.equalsIgnoreCase("H") )
				{
					tranType = distCommon.getDisparams("999999", "ASSET_PORCP_TRAN_TYPE", conn);
					cwipTranType = distCommon.getDisparams("999999","ASSET_PORCP_CWIP_TRAN_TYPE", conn)
					
					if ( cwipTranType == null || cwipTranType.trim().length() == 0 )
					{
						tranType = "";
					}
					if ( tranType == null || tranType.trim().length() == 0 )
					{
						tranType = "";
					}
					
					if ( cwipTranType.trim().length() == 0 && tranType.trim().length() == 0 )
					{
						retString = itmDBAccessLocal.getErrorString("","VMRCPPARM","");
					}
					else
					{
						if ( porcpTranType.trim() != tranType )
						{
							if ( porcpTranType.trim() != cwipTranType )
							{
								retString = itmDBAccessLocal.getErrorString("","VTRCPTYPE1","");
							}
						}
						else
						{
							retString = createAssetInstall( tranId, "");
						}
					}
				}
				if ( retString != null && retString.trim().length() > 0) 
				{
					break;
				}
			} */

			if ( retString == null || retString.trim().length() == 0 ) 
			{
				confDate =  new java.sql.Timestamp(System.currentTimeMillis());
				sql = "UPDATE PORCP SET CONFIRMED = 'Y',CONF_DATE = ? WHERE TRAN_ID = ? ";
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setTimestamp(1, confDate);
				pstmtUpd.setString(2, tranId);
				updCnt = pstmtUpd.executeUpdate();
				pstmtUpd.close();
				pstmtUpd = null;					

				if ( updCnt != 1 )
				{
					retString = itmDBAccessLocal.getErrorString("","VTPORCP2","");	
				}
			}
			if ( retString == null || retString.trim().length() == 0 ) 
			{
				InvAcct invAcct = new InvAcct();
				retString = invAcct.acctPoRcpt( tranId, conn );
				if ( retString != null && "CREATE-VOUCHER".equals(retString.trim()) )
				{
					CreatePoRcpVoucher createVouc = new CreatePoRcpVoucher();
					retString = createVouc.createPoRcpVoucher( tranId, xtraParams, conn );
				}
				else if ( retString != null && retString.trim().length() > 0 )
				{
					retString = itmDBAccessLocal.getErrorString("",retString,"");
				}
			}
			/*// not required for Taro to be done later
			sql = "SELECT SALE_ORDER FROM PORDER A, PORCP B WHERE A.PURC_ORDER = B.PURC_ORDER AND B.TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				saleOrder = checkNull(rs.getString("SALE_ORDER"));
			}			
			if( pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if ( detCnt > noStk)
			{
				if ( saleOrder.trim().length() > 0 && retString.trim().length() == 0 )
				{
					retString = stockAllocatePur( tranId, "P", conn);
				}
			}
			*/
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if(pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}

	private String  confirm( String tranId,int commit, String warning, Connection conn, String xtraParams) throws RemoteException, ITMException
	{
		
		PreparedStatement pstmt = null, pstmt1 = null;;
		PreparedStatement pstmtUpd = null;
		PreparedStatement pstmtItemLotPack = null;
		ResultSet rs = null, rs1 = null;
		ResultSet rsItemLotPack = null;
		String sql = "";
		java.sql.Timestamp tranDate = null;
		String siteRcp = "", tranSer = "",suppCode = "", currCode = "", invacctRate = "", errString = "" ;
		String bondTaxGroup= "", bondTaxArray[],EOU = "",channelPartner = "", disLink = "";

		double exchRate = 0,quantity = 0,discount = 0,rate = 0,netAmount = 0, taxAmount= 0, stdQuantity = 0, batchSize = 0 ;
		double potencyPerc = 0, grossWeight = 0	 , tareWeight = 0, netWeight = 0, convQtyStdUom = 0, exShtQty = 0;
		double noArt = 0, receiptQty = 0, stdRate = 0;

		String lineNo = "0", itemCode = "", purcOrder = "", unit = "",locCode = "",stdUom = "", pordLine = "", cancelBo = "";
		String exShtLoc = "", formNo = "", effectStock = "", mfgItemCode = "", invStat = "", itemSer = "";
		String lotNo = "", lotSl = "", acctCodeCR = "", acctCodeDR = "", cctCodeCR = "", cctCodeDR = "", packCode = "";
		String suppCodeMnfr = "", siteCodeMfg = "",grade = "", batchNo = "", dutyPaid = "", uomRound = "";
		java.sql.Timestamp mfgDate = null, retestDate = null, expiryDate = null;

		double  ordQuantity = 0, dlvQuantity =  0, batchSizeApprv = 0, effRate = 0, grossRate = 0;
		double shipperSize = 0d, grossWt = 0d, netWt = 0d, tareWt = 0d;

		String ordStatus =  "", siteCodeDet =  "", xmlValues = "", keyString = "",remarks = "";
		String cctrCodeInv = "", acctCodeInv = "";
		int lineNoInv = 0;
		java.sql.Timestamp chgDate = null;
		FinCommon finCommon = null;
		HashMap stkUpdMap = null;
		String formStatus = "", bondNo = "";
		java.util.Date date = null;
		//CommonConstants commonConstants = CommonConstants.getInstance();
		int formLineNo = 0, updCnt = 0, count = 0;
		double ct3Quantity = 0,	qtyUsed = 0,bondTaxAmount = 0,bondValue = 0,bankGuarantee = 0,exShtQtystd = 0;

		ArrayList suppLockList = new ArrayList();
		ArrayList itemLockList = null,tempList = null;
		HashMap lockCodeWiseMap =  new HashMap(), tempMap = null;
		String lockGroup = "", lockCode = "", tempLockGroup = "",tempLockCode ="",qcReqd = "N", tempTranId = "";
		
		try
		{
			DistCommon distCommon = new DistCommon();
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			sql = "Select tran_date, site_code,tran_ser,supp_code,post_type	, curr_code , exch_rate "
				+ " From porcp Where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				tranDate = rs.getTimestamp("tran_date");
				siteRcp = rs.getString("site_code");
				tranSer = rs.getString("tran_ser");
				suppCode = rs.getString("supp_code");
				invacctRate = rs.getString("post_type");
				currCode = rs.getString("post_type");
				exchRate = rs.getDouble("exch_rate");
			}	
			pstmt.close();
			pstmt = null;					
			rs.close();
			rs = null;

			sql = "Select (case when eou is null then 'N' else eou end) eou From site Where site_code = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteRcp);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				EOU = rs.getString("eou");
			}	
			pstmt.close();
			pstmt = null;					
			rs.close();
			rs = null;
			
			uomRound = distCommon.getDisparams("999999", "UOM_ROUND", conn);
			if ("NULLFOUND".equals(uomRound))
			{
				errString = itmDBAccessLocal.getErrorString("","VTUOMVARPARM","");
				return errString;
			}
			
			if ( "Y".equals(EOU)) 
			{
				bondTaxGroup = distCommon.getDisparams("999999", "B17_BOND_TAX_GROUP", conn);  
				if (bondTaxGroup != null && bondTaxGroup.trim().length() > 0 )
				{
					bondTaxArray = bondTaxGroup.split(",");
					String orderNoTemp = "";
					bondTaxGroup = "";
					for(int ctr = 0;ctr < bondTaxArray.length;ctr++)
					{
						orderNoTemp = bondTaxArray[ctr];
						bondTaxGroup	= bondTaxGroup + "'".concat(orderNoTemp).concat("',");
					}
				}
					
			}
			sql = "Select channel_partner, dis_link From site_supplier "
				+ " Where  site_code = ? " 
				+ " And supp_code = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteRcp);
			pstmt.setString(2, suppCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				channelPartner = rs.getString("channel_partner");
				disLink = rs.getString("dis_link");
				pstmt.close();
				pstmt = null;					
				rs.close();
				rs = null;
			}	
			else
			{
				pstmt.close();
				pstmt = null;					
				rs.close();
				rs = null;
				sql = "Select channel_partner, dis_link From supplier "
					+ " Where  supp_code = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, suppCode);
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					channelPartner = rs.getString("channel_partner");
					disLink = rs.getString("dis_link");
				}			
				pstmt.close();
				pstmt = null;					
				rs.close();
				rs = null;
			}

			// 1. get the lock group of supplier
			// 2. get the lock_code under all the lock_group and store them in a list
			// 3. At the detail level check supplieritem for lock_group if specified
			//    add the item to all the lock_code in the lock_group
			// 4. If the above is not is not specified add the item to all lock_code for supplier
			/////////////////////////////////////////////////
			
			// populate the supplier lock list
			sql = "select lock_group From supplier "
				+ " where  supp_code = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, suppCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				lockGroup = rs.getString("lock_group");
			}			
			pstmt.close();
			pstmt = null;					
			rs.close();
			rs = null;
			if (lockGroup != null && lockGroup.trim().length() > 0 )
			{
				sql = "select lock_code from lock_group "
					+ " where  lock_group = ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, lockGroup);
				rs = pstmt.executeQuery();
				while ( rs.next() )
				{
					lockCode = rs.getString("lock_code");
					suppLockList.add(lockCode);
				}			
				pstmt.close();
				pstmt = null;					
				rs.close();
				rs = null;
			}
			
			/////////////////////////////////////////////////
			sql  = "SELECT SHIPPER_SIZE, GROSS_WEIGHT, NET_WEIGHT FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ? AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ? ";
			pstmtItemLotPack = conn.prepareStatement(sql);

			sql = "select line_no, item_code, purc_order, quantity, " 
				 + " unit, rate__stduom, discount, tax_amt, net_amt, "
				 + " loc_code, line_no__ord, canc_bo, lot_no, lot_sl, "
				 + " unit__std, quantity__stduom, acct_code__cr, acct_code__dr, cctr_code__cr, cctr_code__dr, " 
				 + " mfg_date, pack_code, potency_perc, expiry_date, "
				 + " gross_weight, tare_weight, net_weight, supp_code__mnfr, "
				 + " site_code__mfg, grade, conv__qty_stduom, "
				 + " (case when excess_short_qty is null then 0 else excess_short_qty end) as excess_short_qty, "
				 + " loc_code__excess_short	, (case when realised_qty is null then 0 else realised_qty end) as excess_short_qty, "
				 + " batch_no, no_art, item_code__mfg, std_rate, effect_stock, form_no, retest_date, "
				 + " duty_paid, batch_size "
				 + " from porcpdet Where tran_id = ? " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while ( rs.next() )
			{
				lineNo = rs.getString("line_no");
				itemCode = rs.getString("item_code");
				purcOrder = rs.getString("purc_order");
				quantity = rs.getDouble("quantity");
				unit = rs.getString("unit");
				rate = rs.getDouble("rate__stduom");
				discount = rs.getDouble("discount");
				taxAmount = rs.getDouble("tax_amt");
				netAmount = rs.getDouble("net_amt");
				locCode = rs.getString("loc_code");
				pordLine = rs.getString("line_no__ord");
				cancelBo = rs.getString("canc_bo");
				lotNo = rs.getString("lot_no");
				lotSl = rs.getString("lot_sl");
				stdUom = rs.getString("unit__std");
				stdQuantity = rs.getDouble("quantity__stduom");
				acctCodeCR = rs.getString("acct_code__cr");
				acctCodeDR = rs.getString("acct_code__dr");
				cctCodeCR = rs.getString("cctr_code__cr");
				cctCodeDR = rs.getString("cctr_code__dr");
				mfgDate = rs.getTimestamp("mfg_date");
				packCode = rs.getString("pack_code");
				potencyPerc = rs.getDouble("potency_perc");
				expiryDate = rs.getTimestamp("expiry_date");
				grossWeight = rs.getDouble("gross_weight");
				tareWeight = rs.getDouble("tare_weight");
				netWeight = rs.getDouble("net_weight");
				suppCodeMnfr = rs.getString("supp_code__mnfr");
				siteCodeMfg = rs.getString("site_code__mfg");
				grade = rs.getString("grade");
				convQtyStdUom = rs.getDouble("conv__qty_stduom");
				exShtQty = rs.getDouble("excess_short_qty");
				exShtLoc = rs.getString("loc_code__excess_short");
				receiptQty = rs.getDouble("excess_short_qty");
				batchNo = rs.getString("batch_no");
				noArt = rs.getDouble("no_art");
				if ( noArt == 0 )
				{
					pstmtItemLotPack.setString(1, itemCode);
					pstmtItemLotPack.setString(2, lotNo);
					pstmtItemLotPack.setString(3, lotNo);
					rsItemLotPack = pstmtItemLotPack.executeQuery();
					if ( rsItemLotPack.next() )
					{
						shipperSize =  rsItemLotPack.getDouble("SHIPPER_SIZE");
						grossWeight =  rsItemLotPack.getDouble("GROSS_WEIGHT");
						netWeight =  rsItemLotPack.getDouble("NET_WEIGHT");
						tareWeight = grossWeight - netWeight;
						if ( shipperSize > 0 )
						{
							noArt = (int)(quantity / shipperSize);
						}
					}
					rsItemLotPack.close();
					rsItemLotPack = null;
					pstmtItemLotPack.clearParameters();
				}
				mfgItemCode = rs.getString("item_code__mfg");
				stdRate = rs.getDouble("std_rate");
				effectStock = rs.getString("effect_stock");
				formNo = rs.getString("form_no");
				retestDate = rs.getTimestamp("retest_date");
				dutyPaid = rs.getString("duty_paid");
				batchSize = rs.getDouble("batch_size");
				//qcReqd = rs.getString("qc_reqd");//Gulzar this column is not present in porcpdet
				///////////////////////////////////////////////////////////////////////////////
				// maintain the lock list in the map as per supplier or supplieritem definition
				// suppLockList
				// itemLockList
				// lockCodeWiseMap
				
				tempLockGroup  = null;
				sql  = "select lock_group " 
					+ " from supplieritem " 
					+ " Where supp_code = ? And  item_code = ?";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, suppCode);
				pstmt1.setString(2, itemCode);
				rs1 = pstmt1.executeQuery();
				if ( rs1.next() )
				{
					tempLockGroup =  rs1.getString("lock_group");
				}
				pstmt1.close();
				pstmt1 = null;
				rs1.close();
				rs1 = null;
				itemLockList =  null;
				if (tempLockGroup != null && tempLockGroup.trim().length() > 0)
				{
					itemLockList = new ArrayList();
					sql = "select lock_code from lock_group "
						+ " where  lock_group = ? " ;
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, tempLockGroup);
					rs1 = pstmt1.executeQuery();
					while ( rs1.next() )
					{
						lockCode = rs1.getString("lock_code");
						itemLockList.add(lockCode);
					}			
					pstmt1.close();
					pstmt1 = null;					
					rs1.close();
					rs1 = null;
				
				}
				else if(suppLockList.size() > 0)
				{
					itemLockList = suppLockList;
				}
				if ( itemLockList != null && itemLockList.size() > 0 )
				{
					for (int ctr = 0; ctr < itemLockList.size(); ctr ++)
					{
						tempLockCode = (String) itemLockList.get(ctr);
						// find whether the lockCode is already in the HashMap
						if(lockCodeWiseMap.containsKey(tempLockCode))
						{
							tempList = (ArrayList) lockCodeWiseMap.get(tempLockCode);
						}
						else
						{
							tempList  = new ArrayList();
						}
						// populate the map with the stock keys
						tempMap = new HashMap();
						
						tempMap.put("site_code", siteRcp);
						tempMap.put("item_code", itemCode);
						tempMap.put("loc_code", locCode);
						tempMap.put("lot_no", lotNo);
						tempMap.put("lot_sl", lotSl);

						tempList.add(tempMap);
						
						if(lockCodeWiseMap.containsKey(tempLockCode))
						{
							lockCodeWiseMap.put(tempLockCode,tempList);
						}
						else
						{
							lockCodeWiseMap.put(tempLockCode,tempList);
						}
					}
				}
				///////////////////////////////////////////////////////////////////////////////
				if (purcOrder != null )
				{
					sql  = "select quantity, dlv_qty, status, site_code " 
						+ " from porddet " 
						+ " Where Purc_order = ? And  line_no    = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, purcOrder);
					pstmt1.setString(2, pordLine);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						ordQuantity =  rs1.getDouble("quantity");
						dlvQuantity =  rs1.getDouble("dlv_qty");
						ordStatus =  rs1.getString("status");
						siteCodeDet =  rs1.getString("site_code");
					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;
				}								
				if (effectStock ==  null )
				{
					effectStock = "Y";
				}
				if ("Y".equals(effectStock) )
				{
					if ("N".equals(qcReqd) )
					{
						if (batchSize > 0 )
						{
							sql  = "select batch_size from batchsize_aprv" 
								+ " where item_code	= ? "
								+ " and site_code__mfg	= ? "
								+ " and eff_from <= ? "
								+ " and valid_upto >= ? " 
								+ " and confirmed = 'Y' ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, itemCode);
							pstmt1.setString(2, siteRcp);
							pstmt1.setTimestamp(3, tranDate);
							pstmt1.setTimestamp(4, tranDate);
							rs1 = pstmt1.executeQuery();
							if ( rs1.next() )
							{
								batchSizeApprv  =  rs1.getDouble("batch_size"); 
							}
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;

							if (batchSize > batchSizeApprv)
							{

								sql  = "select key_string " 
									+ " where upper(tran_window) = 'W_INV_HOLD'";
								pstmt1 = conn.prepareStatement(sql);
								rs1 = pstmt1.executeQuery();
								if ( rs1.next() )
								{
									keyString  =  rs1.getString("key_string"); 
									pstmt1.close();
									pstmt1 = null;
									rs1.close();
									rs1 = null;
								}
								else
								{
									pstmt1.close();
									pstmt1 = null;
									rs1.close();
									rs1 = null;
									sql  = "select key_string " 
										+ " where upper(tran_window) = 'GENERAL'";
									pstmt1 = conn.prepareStatement(sql);
									rs1 = pstmt1.executeQuery();
									if ( rs1.next() )
									{
										keyString  =  rs1.getString("key_string"); 
									}
									pstmt1.close();
									pstmt1 = null;
									rs1.close();
									rs1 = null;
								}
								//tranDate = getCurrdateAppFormat(); 
								xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
								xmlValues = xmlValues + "<Header></Header>\r\n";
								xmlValues = xmlValues + "<Detail1>\r\n";
								xmlValues = xmlValues +	"<tran_id></tran_id>\r\n";
								xmlValues = xmlValues + "<site_code>" + siteRcp + "</site_code>\r\n";
								xmlValues = xmlValues + "<tran_date>"+ getCurrdateAppFormat() + "</tran_date>\r\n";
								xmlValues = xmlValues + "</Detail1>\r\n</Root>";
								System.out.println("xmlValues  :["+xmlValues+"]");
								TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
								tempTranId = tg.generateTranSeqID("INVHOL", "tran_id", keyString, conn);
								System.out.println("tempTranId ["+tempTranId + "]");

								sql = "insert into inv_hold (tran_id, tran_date, site_code, remarks,confirmed, chg_user, chg_date, chg_term) "
												+ " values	(?,?,?,?, 'N',?,?,?) " ;

								remarks	= "Auto generated from Purchase Receipt	:" + tranId ;
								chgDate = new java.sql.Timestamp(System.currentTimeMillis());
								java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateFormat());
								date = sdf.parse(chgDate.toString());
								chgDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
								
								pstmtUpd = conn.prepareStatement(sql);
								pstmtUpd.setString(1, tempTranId);
								pstmtUpd.setTimestamp(2, chgDate);
								chgDate = new java.sql.Timestamp(System.currentTimeMillis());
								pstmtUpd.setString(3, siteRcp);
								pstmtUpd.setString(4, remarks);
								pstmtUpd.setString(5, this.userId);
								pstmtUpd.setTimestamp(6, chgDate);
								pstmtUpd.setString(7, this.termId);
								
								updCnt = pstmtUpd.executeUpdate();
								pstmtUpd.close();
								pstmtUpd = null;					
								
								lineNoInv++;

								sql = "insert into inv_hold_det (tran_id, line_no, item_code, site_code,loc_code, lot_no, lot_sl, remerks,	hold_status) "
									+ " values	(?, ?, ?,?,?,?,?,?,	'H') ";
								
								pstmtUpd = conn.prepareStatement(sql);
								pstmtUpd.setString(1, tempTranId);
								pstmtUpd.setInt(2, lineNoInv);
								pstmtUpd.setString(3, itemCode);
								pstmtUpd.setString(4, siteRcp);
								pstmtUpd.setString(5, locCode);
								pstmtUpd.setString(6, lotNo);
								pstmtUpd.setString(7, lotSl);
								pstmtUpd.setString(8, remarks);
								
								updCnt = pstmtUpd.executeUpdate();
								pstmtUpd.close();
								pstmtUpd = null;					
							} // batchSize > approved batch size
						} //  batchSize > 0
					} // qc_reqd == N 
					sql  = "select inv_stat from location where loc_code = ? "; 
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,locCode);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						invStat  =  rs1.getString("inv_stat"); 
					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;
					sql  = "select item_ser from item where item_code = ? "; 
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,itemCode);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						itemSer  =  rs1.getString("item_ser"); 
					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;

					finCommon = new FinCommon(); 
					cctrCodeInv  = finCommon.getFromAcctDetr(itemCode, itemSer, "STKINV", conn);
					
					String tokens [] = cctrCodeInv.split(",");
					if ( tokens != null && tokens.length >=2 )
					{
						acctCodeInv = tokens[0];
						cctrCodeInv = tokens[1];
					}
							
					//Calculating effective rate
					// 22/01/01 manoharan argument gross_net added to the function
					// get the net rate
					effRate =	calcEffRate(lineNo, stdQuantity, rate, taxAmount, tranId,"N", conn );
					//////////////////////////////////////////////////////////////////////////////////
					// manoharan 29/03/02 effective rate should be more than 0
					// added and rate > 0 by Manoj on 10/5/02 as per Kandarp since when sorder was posted 
					// for free item and if porcp is created then eff rate and rate are 0 so to bypass that 
					// check validation has been modified
					if (effRate <= 0 && rate > 0 )
					{
						
						errString = itmDBAccessLocal.getErrorString("","VTRATE","");
	
						String begPart = errString.substring(0,errString.indexOf("<message>")+9);
						String begDesc = errString.substring(0,errString.indexOf("<description>")+13);
						String endDesc = errString.substring(errString.indexOf("</description>"));
						
						
						String mainStr= begPart + "Effective rate calculated is [" + effRate + "] for Line No / Item [" + lineNo + " / " + itemCode + "] should be more than 0 " + "</message><description>";
						mainStr= mainStr+"Please Check rate  "+endDesc;
						errString = mainStr;
						break;
					}
					//////////////// 29/03/02 ///////////////////////////////////////////////////////
					// get the gross_rate
					grossRate = calcEffRate(lineNo, stdQuantity, rate, taxAmount, tranId,"G", conn );
					
					stkUpdMap = new HashMap();
					
					if ( receiptQty == 0)
					{
						exShtQty = 0;
					}
                    stkUpdMap.put("quantity",Double.toString(quantity + exShtQty));
					if (exShtQty != 0)
					{
						//Sharon 12-Sep-2003
						if ( "Q".equals(uomRound) || "B".equals(uomRound))
						{
							stdQuantity = distCommon.convQtyFactor(unit,stdUom, itemCode, quantity + exShtQty, conn);
						}
						else
						{
							stdQuantity = distCommon.convQtyFactor(unit,stdUom, itemCode, quantity + exShtQty, conn);
						}
						//End Sharon
						//stdQuantity = gf_conv_qty_fact(unit,stdUom, itemCode, quantity + exShtQty, convQtyStdUom)
						
						//added by jasmina DI89ALL024-17/07/09
						//Script- if realised_qty is less than qty, than rate shd ne netamt/receiptQty(i.e net_amt/quantity + exShtQty)
	
						effRate = calcEffRate(lineNo, receiptQty, rate, taxAmount, tranId,"N", conn );
						//////////////////////////////////////////////////////////////////////////////////
						// manoharan 29/03/02 effective rate should be more than 0
						// added and rate > 0 by Manoj on 10/5/02 as per Kandarp since when sorder was posted 
						// for free item and if porcp is created then eff rate and rate are 0 so to bypass that 
						// check validation has been modified
						if ( effRate <= 0 && rate > 0 )
						{
							errString = itmDBAccessLocal.getErrorString("","VTRATE","");
		
							String begPart = errString.substring(0,errString.indexOf("<message>")+9);
							String begDesc = errString.substring(0,errString.indexOf("<description>")+13);
							String endDesc = errString.substring(errString.indexOf("</description>"));
							
							String mainStr= begPart+ "Effective rate calculated is [" + effRate + "] for Line No / Item [" + lineNo + " / " + itemCode + "] should be more than 0 " + 	"</message><description>";
							mainStr= mainStr+"Please Check rate  "+endDesc;
							errString = mainStr;
							break;
						}
						grossRate = calcEffRate(lineNo, receiptQty, rate, taxAmount, tranId,"G", conn );
					}
				
					stkUpdMap.put("gross_rate",Double.toString(grossRate));
					stkUpdMap.put("qty_stduom",Double.toString(stdQuantity));
	                stkUpdMap.put("item_code",itemCode);
	                stkUpdMap.put("no_art",Double.toString(noArt));
	                stkUpdMap.put("site_code",siteRcp);
	                stkUpdMap.put("loc_code",locCode);
	                stkUpdMap.put("lot_no",lotNo);
	                stkUpdMap.put("lot_sl",lotSl);
					stkUpdMap.put("unit",unit);
					stkUpdMap.put("unit__alt",unit);
	                stkUpdMap.put("tran_type","R");
	                stkUpdMap.put("tran_date",tranDate);
	                stkUpdMap.put("tran_ser",tranSer);
	                stkUpdMap.put("tran_id",tranId);
					stkUpdMap.put("acct_code__dr",acctCodeDR);
					stkUpdMap.put("cctr_code__dr",cctCodeDR);
					stkUpdMap.put("acct_code__cr",acctCodeCR);
					stkUpdMap.put("cctr_code__ccr",cctCodeCR);
					stkUpdMap.put("acct_code_inv",acctCodeDR);
					stkUpdMap.put("cctr_code_inv",cctCodeDR);
					stkUpdMap.put("line_no",lineNo);
					if ("A".equals(invacctRate) )
					{
						stkUpdMap.put("rate",Double.toString(effRate));
					}
					else if ("S".equals(invacctRate) )
					{
						stkUpdMap.put("rate",Double.toString(stdRate));
					}
					stkUpdMap.put("actual_rate",Double.toString(effRate));
					stkUpdMap.put("site_code__mfg",siteCodeMfg);
					stkUpdMap.put("supp_code__mfg",suppCodeMnfr);
					stkUpdMap.put("potency_perc",Double.toString(potencyPerc));
					stkUpdMap.put("pack_code",packCode);
					stkUpdMap.put("mfg_date",mfgDate);
					stkUpdMap.put("exp_date",expiryDate);
					stkUpdMap.put("inv_stat",invStat);
					//change done by Kunal on 09/10/12 start as per Manoj Sharma instructions  
					/*
					stkUpdMap.put("gross_weight",Double.toString(grossWeight * noArt));
	                stkUpdMap.put("tare_weight",Double.toString(tareWeight * noArt));
	                stkUpdMap.put("net_weight",Double.toString(netWeight * noArt));
	                */
	                stkUpdMap.put("gross_weight",Double.toString(grossWeight));
	                stkUpdMap.put("tare_weight",Double.toString(tareWeight));
	                stkUpdMap.put("net_weight",Double.toString(netWeight ));
	               //change done by Kunal on 09/10/12 end
	                
					stkUpdMap.put("retest_date",retestDate);
					stkUpdMap.put("grade",grade);
					stkUpdMap.put("conv__qty_stduom",Double.toString(convQtyStdUom));
					//stkUpdMap.put("pack_instr",packInstr);
					stkUpdMap.put("batch_no",batchNo);
					stkUpdMap.put("batch_size",Double.toString(batchSize));

					StockUpdate stkUpd =  new StockUpdate();
					errString = stkUpd.updateStock(stkUpdMap,xtraParams,conn);
					stkUpd =null;
					//stkUpdMap.clear();  //seems not required as the values are replaced properly in map
					if(errString != null && errString.indexOf("Error") != -1  )
					{
						break;
					}
					
					//stkUpdMap = null;

				
					/* // not required for taro to be developed later	
					if channelPartner = 'Y' and  disLink='E'  then
						Select var_value 
						Into :ls_loc_code_git 
						From disparm
						Where prd_code='999999' 
						And   var_name='TRANSIT_LOC';
						if get_sqlcode() < 0 then
							ls_errcode ='DS000' +string(sqlca.sqldbcode)
							exit
						end if
						
						Select inv_stat 
						Into :ls_tran_stat 
						From location
						Where loc_code = :ls_loc_code_git;
						if get_sqlcode() < 0 then
							ls_errcode = 'DS000'+string(sqlca.sqldbcode)
							exit
						end if
						s_update.inv_stat = ls_tran_stat
						s_update.locationcode = ls_loc_code_git
						s_update.trantype = 'I'
						ls_errcode = lnvo_stock.gbf_update_stock(s_update)
						if len(ls_errcode) > 0 then exit
					end if
					// end Kalpesh*/
					if (exShtQty != 0)
					{
						//Sharon 12-Sep-2003
						if ( "Q".equals(uomRound) || "B".equals(uomRound))
						{
							exShtQtystd = distCommon.convQtyFactor(unit,stdUom, itemCode, exShtQty, conn);
						}
						else
						{
							exShtQtystd = distCommon.convQtyFactor(unit,stdUom, itemCode, exShtQty, conn);
						}
						stkUpdMap.put("loc_code",exShtLoc);
						stkUpdMap.put("qty_stduom",Double.toString(-1 * exShtQty));
						stkUpdMap.put("qty_stduom",Double.toString(-1 * exShtQtystd));

						sql  = "select inv_stat from location where loc_code =  ? " ;
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, exShtLoc);
						rs1 = pstmt1.executeQuery();
						if ( rs1.next() )
						{
							invStat =  rs1.getString("status");
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						stkUpdMap.put("inv_stat",invStat);
						stkUpdMap.put("gross_weight",Double.toString(Math.abs(grossWeight / (quantity + exShtQty) * exShtQty)));
						stkUpdMap.put("tare_weight",Double.toString(Math.abs(tareWeight / (quantity + exShtQty) * exShtQty)));
						stkUpdMap.put("net_weight",Double.toString(Math.abs(netWeight / (quantity + exShtQty) * exShtQty)));
						
							
						stkUpd =  new StockUpdate();
						errString = stkUpd.updateStock(stkUpdMap,xtraParams,conn);
						stkUpd =null;
						stkUpdMap.clear();  
					}
				} //  effectStock
			
				//////////////////////////////////////////////////////////////////
				// If puchase order is closed then don't insert in any of the tables
				//	if ordStatus <> 'C' then
					
				if (purcOrder != null)
				{
					if ("Y".equals(cancelBo))
					{
						sql = "Update porddet set status = 'C', status_date = ?, dlv_qty = (case when dlv_qty is null then 0 else dlv_qty end) + ? "
							+ "	Where purc_order = ? and line_no = ? " ;
					}
					else
					{
						sql = "Update porddet set  status_date = ?, dlv_qty = (case when dlv_qty is null then 0 else dlv_qty end) + ? "
							+ "	Where purc_order = ? and line_no = ? " ;
						
					}
					chgDate = new java.sql.Timestamp(System.currentTimeMillis());
	
					pstmtUpd = conn.prepareStatement(sql);
					pstmtUpd.setTimestamp(1, chgDate);
					pstmtUpd.setDouble(2, quantity);
					pstmtUpd.setString(3, purcOrder);
					pstmtUpd.setString(4, pordLine);
					updCnt = pstmtUpd.executeUpdate();
					pstmtUpd.close();
					pstmtUpd = null;					
				}
				// 05/05/01 manoharan if all the PO Details are closed
				// then close the PO Header also
				if(errString != null && errString.indexOf("Error") == -1 && purcOrder != null )
				{
					sql  = "select count(1) from porddet where purc_order =  ? And (case when status is null then ' ' else status end) <> 'C' " ;
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, purcOrder);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						count =  rs1.getInt(1);
					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;
					 
					if (count == 0 )
					{
						sql = "update porder Set status = 'C' Where purc_order = ?";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setString(1, purcOrder);
						updCnt = pstmtUpd.executeUpdate();
						pstmtUpd.close();
						pstmtUpd = null;					
					}
				}
			
				//Added by Jasmina-28/11/08- EO89SUN002,
				//site is eou than update the qty used in ct3form table.
				if ("Y".equals(EOU) && "N".equals(dutyPaid) )
				{

					sql  = "select a.status as status , b.line_no as line_no, (case when b.quantity is null then 0 else b.quantity end) quantity, " 
						+ " (case when b.qty_used is null then 0 else b.qty_used end) as qty_used "
						+ " from ct3form_hdr a , ct3form_det b "
						+ " where a.form_no = b.form_no " 
						+ "	and a.form_no = ? " 
						+ "	and b.purc_order = ? "
						+ "	and b.line_no__ord = ? "
						+ "	and a.site_code = ? "
						+ "	and b.supp_code = ? "
						+ "	and b.item_code = ? "
						+ "	and ? >= a.eff_from "
						+ "	and ? <= a.valid_upto "
						+ "	and case when a.confirmed is null then 'N' else a.confirmed end = 'Y' ";

					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, formNo);
					pstmt1.setString(2, purcOrder);
					pstmt1.setString(3, pordLine);
					pstmt1.setString(4, siteRcp);
					pstmt1.setString(5, suppCode);
					pstmt1.setString(6, itemCode);
					pstmt1.setTimestamp(7, tranDate);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						formStatus = rs1.getString(1);
						formLineNo = rs1.getInt(1);
						ct3Quantity = rs1.getInt(1);
						qtyUsed = rs1.getInt(1);
					}
					else
					{
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						errString = itmDBAccessLocal.getErrorString("","VTCT3FORM1","");
						break;
						
					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;
					
					if (!"O".equals(formStatus))
					{
						errString = itmDBAccessLocal.getErrorString("","VTCT3FORM2","");//CT3 Form has been closed/cancelled/expired.
						break;
					}
					else if ( ct3Quantity - qtyUsed  < quantity )
					{
						errString = itmDBAccessLocal.getErrorString("","VTCT3QTY","");//Quantity exceeds the balance quantity of CT3 Form
						break;
					}
					else
					{
						sql = "update ct3form_det set qty_used = ?  + ? "
							+ " where form_no =  ? and line_no = ?";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, qtyUsed);
						pstmtUpd.setDouble(2, quantity);
						pstmtUpd.setString(3, formNo);
						pstmtUpd.setInt(4, formLineNo);
						updCnt = pstmtUpd.executeUpdate();
						pstmtUpd.close();
						pstmtUpd = null;					
					} 
				
					//for Updating Bond Value, bond value shd be debited
					if (bondTaxGroup != null && bondTaxGroup.trim().length() > 0 )
					{
						bondTaxAmount = 0;
						sql = "select Sum(case when tax_amt is null then 0 else tax_amt end) as tax_amount "
							+ " from taxtran Where  tran_code = 'P-RCP' "
							+ " and tran_id = ? "
							+ " and line_no = ? "
							+ " and tax_code IN ( select tax_code from tax where tax_group in ( " + bondTaxGroup + ")) ";

						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, tranId);
						pstmt1.setString(2, lineNo);
						rs1 = pstmt1.executeQuery();
						if ( rs1.next() )
						{
							bondTaxAmount =  rs1.getDouble(1);
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						
						if ( bondTaxAmount != 0 )
						{
							bondValue = 0;
							sql = "select bond_no, (case when bond_value is null then 0 else bond_value end) as bond_value, " 
								+ " (case when bank_guarantee is null then 0 else bank_guarantee end) as bank_guarantee "
								+ " from b17_bond " 
								+ " where site_code = ? "
								+ " and ? >= eff_from "
								+ " and ? <= valid_upto "
								+ " and case when confirmed is null then 'N' else confirmed end = 'Y' "
								+ " and bond_type = 'B' ";

							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, siteRcp);
							pstmt1.setString(2, lineNo);
							pstmt1.setTimestamp(3, tranDate);
							pstmt1.setTimestamp(4, tranDate);
							rs1 = pstmt1.executeQuery();
							if ( rs1.next() )
							{
								bondNo = rs1.getString("bond_no");
								bondValue = rs1.getDouble("bond_value");
								bankGuarantee =  rs1.getDouble("bank_guarantee");
							}
							else
							{
								pstmt1.close();
								pstmt1 = null;
								rs1.close();
								rs1 = null;
								errString = itmDBAccessLocal.getErrorString("","VTB17ERR1",""); //Active B17 Bond Not Found for the Site
								break;
							}
							pstmt1.close();
							pstmt1 = null;
							rs1.close();
							rs1 = null;

							if (bankGuarantee < (bondValue - bondTaxAmount) || (bondValue - bondTaxAmount) < 0 )
							{
								errString = itmDBAccessLocal.getErrorString("","VTB17ERR2",""); //Insufficient Bond Balance
								break;
							}
							else
							{
								sql = "update b17_bond set bond_value =  ?  + ? "
									+ " where bond_no = ? ";
								pstmtUpd = conn.prepareStatement(sql);
								pstmtUpd.setDouble(1, bondValue);
								pstmtUpd.setDouble(2, bondTaxAmount);
								pstmtUpd.setString(3, bondNo);
								updCnt = pstmtUpd.executeUpdate();
								pstmtUpd.close();
								pstmtUpd = null;					
							}
						} 
					} // bondTaxGroup
				} //  EOU and !dutyPaid
				//Added end by Jasmina-28/11/08- EO89SUN002
			} // end of details			
			pstmt.close();
			pstmt = null;					
			rs.close();
			rs = null;
			if (errString == null || errString.trim().length() == 0 )
			{
				
				Set setItem = lockCodeWiseMap.entrySet();
				tempList =  null;
				Iterator itrItem = setItem.iterator();
				InvHoldGen invHoldGen = new InvHoldGen();
				while(itrItem.hasNext())
				{
					Map.Entry itemMapEntry = (Map.Entry)itrItem.next();
					lockCode = (String)itemMapEntry.getKey();
					tempList = (ArrayList)lockCodeWiseMap.get(lockCode);
					errString = invHoldGen.generateHoldTrans(lockCode, tranId, "P-RCP", siteRcp, tempList, xtraParams, conn );
					if(errString != null && errString.trim().length() > 0 )
					{
						break;
					}
				}
				
			}
			//////////////////////////////////////////////////////////////////////////////////////////
			/*// not required for taro to be developed later
			// 30-06-2005 manoharan error checking added
			if len(trim(ls_errcode)) > 0 then 
				goto errfound
			end if
			// end 30-06-2005 manoharan
			////////////////////////////////////////////////
			// 14/02/03 manoharan this allocation need to be done 
			// only if necessary
			// 29-06-2005 manoharan not to allocate for some clients
			ls_autoallocord = gf_getenv_dis('999999',"AUTO_ALLOC_ORD")
			if ls_autoallocord = "NULLFOUND" then 
				ls_autoallocord = 'N'
			end if
	
			if gs_run_mode = 'I' and ls_autoallocord = 'Y' then
				//Req No.MP-03-0014
				Select key_string Into :ls_keystr from transetup
				Where upper(tran_window) = 'W_INV_ALLOCATE';
				if get_sqlcode() = 100 then
					Select key_string Into :ls_keystr From transetup
					where upper(tran_window) = 'GENERAL';
					if get_sqlcode() = 100 then
						ls_errcode = 'DS000' + string(sqlca.sqldbcode)
						goto errfound
					end if
				end if
				// MODIFIED BY RADHAKRISHNAN 0N 14-01-04
				// MERGED BY TWO CURSORS
				DECLARE CURITEMCODEMFG CURSOR FOR
				SELECT DISTINCT A.ITEM_CODE__MFG , E.WORK_ORDER
				FROM  PORCPDET A, ITEM B, PORCP C , PORDER D , PORDDET E
				WHERE A.ITEM_CODE = B.ITEM_CODE 
				AND   E.ITEM_CODE = B.ITEM_CODE 
				AND   A.PURC_ORDER = D.PURC_ORDER
				And 	D.PURC_ORDER = E.PURC_ORDER
				and   e.line_no = a.line_no__ord
				AND   A.TRAN_ID = C.TRAN_ID
				AND	C.TRAN_ID= :as_tranid
				//AND	DECODE(C.QC_REQD,'N',C.QC_REQD,B.QC_REQD) = 'N' 
				and (case when C.QC_REQD = 'N' then C.QC_REQD else B.QC_REQD end) = 'N' // to avoid decode ** kiran 20/05/05
				AND ((E.WORK_ORDER IS NOT NULL AND LENGTH(LTRIM(RTRIM(E.WORK_ORDER))) > 0)
				OR	  (A.ITEM_CODE__MFG IS NOT NULL AND LENGTH(LTRIM(RTRIM(A.ITEM_CODE__MFG))) > 0));
				
				OPEN CURITEMCODEMFG;
				FETCH CURITEMCODEMFG INTO :itemCode_code_mfg,:ls_workorder;
				DO WHILE Sqlca.SqlCode = 0
					Declare cur_inv_rcpdet Cursor For 
					Select a.line_no, a.item_code, a.quantity,a.loc_code, 
							 a.lot_no, a.lot_sl, (case when a.excess_short_qty is null then 0 else a.excess_short_qty end),
							 a.item_code__mfg, a.unit, a.unit__std, a.conv__qty_stduom, (case when a.realised_qty is null then 0 else a.realised_qty end)
					From  porcpdet a,  item b , porcp c ,porder d , porddet e
					Where b.item_code   = e.item_code 
						and b.item_code  = a.item_code
						And D.purc_order = a.purc_order
						and d.purc_order = e.purc_order
						and e.line_no = a.line_no__ord
						And a.tran_id = c.tran_id
						And a.tran_id = :as_tranid
						//And decode(c.qc_reqd,'N',c.qc_reqd,b.qc_reqd) = 'N'
						And (c.qc_reqd = 'N') or (b.qc_reqd = 'N') // to avoid decode ** kiran 20/05/05
						And (a.item_code__mfg = :itemCode_code_mfg
						or e.work_order = :ls_workorder);
							
					Open cur_inv_rcpdet;
					Fetch cur_inv_rcpdet Into :lineNo, :itemCode, :quantity,:locCode, 
							:lotNo, :lotSl,:exShtQty, :mfgItemCode, :unit,
							:stdUom, :convQtyStdUom, :receiptQty;
				
					ll_newrow = lds_alloc_hdr.InsertRow(0)
					lds_alloc_hdr.SetItem(ll_newrow,"tran_date",datetime(today(),time("00:00:00")))
					lds_alloc_hdr.SetItem(ll_newrow,"tran_type",'GIM')		// GIM //02Dec-2004 //sharon
					lds_alloc_hdr.SetItem(ll_newrow,"site_code",siteRcp)
					// added by radhakrishnan 0n 10-01-04
					if isnull(mfgItemCode) or len(trim(mfgItemCode)) = 0 then
							Select Item_Code
							Into :ls_WorkItem
							From WorkOrder
							Where Work_Order = :ls_workorder;
					  lds_alloc_hdr.SetItem(ll_newrow,"item_code",ls_WorkItem)
					else  
						lds_alloc_hdr.SetItem(ll_newrow,"item_code",mfgItemCode)
					end if	
					// end here 10-01-04	
					lds_alloc_hdr.SetItem(ll_newrow,"remarks","Auto Reserve " + as_tranid)
					lds_alloc_hdr.setitem(ll_newrow,"chg_date",datetime(today(),now()))
					lds_alloc_hdr.setitem(ll_newrow,"chg_user",userid)
					lds_alloc_hdr.setitem(ll_newrow,"chg_term",termid)
					lds_alloc_hdr.setitem(ll_newrow,"conf_date",datetime(today(),now()))
					lds_alloc_hdr.setitem(ll_newrow,"confirmed",'Y')
					lds_alloc_hdr.setitem(ll_newrow,"status",'O')
					lds_alloc_hdr.setitem(ll_newrow,"status_date",datetime(today(),now()))
					lds_alloc_hdr.SetItem(ll_newrow,"work_order",ls_workorder)
					// Assign the tran_id in the header
					if isnull(ls_tranid) or len(trim(ls_tranid)) = 0 then
						lb_insert = true
						// Generate INV Tran_Id
						ls_tranid = gf_gen_key_nvo(lds_alloc_hdr, "W-RIN", "tran_id", ls_keystr)
						if ls_tranid = 'ERROR' then
							ls_errcode = 'VTTRANID'
							goto errfound
						end if
					end if
					lds_alloc_hdr.SetItem(ll_newrow,"tran_id",ls_tranid)	
					
					Do While Sqlca.SqlCode = 0
						
						if isnull(receiptQty) or receiptQty = 0 then
							exShtQty = 0
						end if
						if isnull(lotNo) or len(trim(lotNo)) = 0 then
							lotNo = space(15)
						end if
						if isnull(lotSl) or len(trim(lotSl)) = 0  then
							lotSl = space(5)
						end if
						ld_quantity = quantity + exShtQty
						s_update.quantity = quantity + exShtQty
						If ls_value = 'Q' Or ls_value = 'B' Then
							stdQuantity = gf_conv_qty_fact1(unit,stdUom, itemCode, quantity + exShtQty, convQtyStdUom,'Y')
						Else
							stdQuantity = gf_conv_qty_fact1(unit,stdUom, itemCode, quantity + exShtQty, convQtyStdUom,'N')
						End If
						
						ll_allocrow = lds_alloc_det.insertrow(0)
						lds_alloc_det.SetItem(ll_allocrow,"tran_id",ls_tranid)
						lds_alloc_det.SetItem(ll_allocrow,"line_no",ll_allocrow)
						lds_alloc_det.SetItem(ll_allocrow,"site_code",siteRcp)
						lds_alloc_det.SetItem(ll_allocrow,"item_code",itemCode)				
						lds_alloc_det.SetItem(ll_allocrow,"loc_code",locCode)
						lds_alloc_det.SetItem(ll_allocrow,"lot_no",lotNo)
						lds_alloc_det.SetItem(ll_allocrow,"lot_sl",lotSl)
						lds_alloc_det.SetItem(ll_allocrow,"quantity",stdQuantity)
						lds_alloc_det.SetItem(ll_allocrow,"alloc_qty",quantity)
						lds_alloc_det.SetItem(ll_allocrow,"potency_adj",0)
						lds_alloc_det.SetItem(ll_allocrow,"dealloc_qty",0)
						lds_alloc_det.SetItem(ll_allocrow,"issue_qty",0)
						//lds_alloc_det.SetItem(ll_allocrow,"exp_lev",ls_explev)
						//lds_alloc_det.SetItem(ll_allocrow,"remarks",ls_remarks)
						//lds_alloc_det.SetItem(ll_allocrow,"reas_code",ls_reascode)
	
						Fetch cur_inv_rcpdet Into :lineNo, :itemCode, :quantity,:locCode, 
								:lotNo, :lotSl,:exShtQty, :mfgItemCode, :unit,
								:stdUom, :convQtyStdUom, :receiptQty;
					Loop
					Close cur_inv_rcpdet;
				
					if lds_alloc_det.rowcount() > 0 then
						if lb_insert then
							if lds_alloc_hdr.Update() = 1 then
								if lds_alloc_det.Update() = 1 then
								else
									ls_errcode = "DS000" + trim(string(sqlca.sqldbcode)) + "~t" + sqlca.sqlerrtext
									goto errfound
								end if
							else
								ls_errcode = "DS000" + trim(string(sqlca.sqldbcode)) + "~t" + sqlca.sqlerrtext
								goto errfound
							end if
						elseif lds_alloc_det.rowcount() > 0 then
							if lds_alloc_det.Update() = 1 then
							else
								ls_errcode = "DS000" + trim(string(sqlca.sqldbcode)) + "~t" + sqlca.sqlerrtext
								goto errfound
							end if
						end if
						if len(trim(ls_errcode)) = 0 and not isnull(ls_errcode) then  
							ls_errcode = lnvo_alloc.gbf_confirm_inv_allocate(ls_tranid,ai_commit)
						end if
					end if
			 
					FETCH NEXT CURITEMCODEMFG INTO :itemCode_code_mfg,:ls_workorder;
				LOOP 							
				CLOSE CURITEMCODEMFG;	
			end if //gs_run_mode = 'I'		
			// 30-06-2005 manoharan error checking
			if len(trim(ls_errcode)) > 0 then
				goto errfound
			end if
			// end 30-06-2005 manoharan
	
			// Changes for Accounting Effect 
			ls_errcode = lnvo_invacct.gbf_acct_po_rcpt(as_tranid,tranSer)
			if gs_run_mode = 'I' then // 24-02-2005 manoharan not required during loading
				update porcp set confirmed = 'Y'
						where tran_id = :as_tranid;
				if sqlca.sqlcode < 0 then
					ls_errcode = 'DS000' + trim(string(sqlca.SqlDbCode))
				elseif sqlca.sqlnrows <> 1 then
					ls_errcode = 'VTPORCP2'
				end if
				ls_err = lnvo_vouch.gbf_check_accept_criteria(as_tranid)
				if ls_errcode = 'CREATE-VOUCHER' and trim(ls_err) <> "DONOTCREATE" then
					ls_errcode = ""		
					// Commented by Brijesh Mishra on 20-10-04 due to bad argument
					// ls_errstr = lnvo_vouch.gbf_porcp_vouch_retrieve(as_tranid, as_tranid, 1)
					ls_errstr = lnvo_vouch.gbf_porcp_vouch_retrieve(as_tranid, as_tranid, 1, as_warning)
					if isnull(ls_errstr) then ls_errstr = ""
					ls_msgstr = ""
					if pos(ls_errstr,'~t') > 0 then 
						ls_msgstr = ls_errstr
						ls_errcode = f_get_token(ls_msgstr,'~t')
						if ls_errcode = 'VTREJRET' then
							ls_errcode = ""
						else
							ls_errcode = ls_errstr
						end if
					else
						ls_errcode = ls_errstr
					end if
				// 30-06-2005 manoharan open of w_msg box removed this warning is not necessary
				elseif ls_errcode = 'CREATE-VOUCHER' and trim(ls_err) = "DONOTCREATE" then
					ls_errcode = ''
				elseIf (IsNull(ls_errcode) Or Len(Trim(ls_errcode)) = 0) And (IsNull(ls_err) Or Len(Trim(ls_err)) = 0) Then		
					select count(1) into :ll_cnt
					from 	 pord_pay_term
					where  purc_order = :purcOrder
					and 	 rel_agnst = '02';
	
					if get_sqlcode() < 0 then
						ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode))
						populateerror(9999,'populateerror')
						ls_errcode = gf_error_location(ls_errcode)
						Return ls_errcode
					elseif ll_cnt > 0 then
						//function name changed by Sharon on 22-Jan-2003
						// added by kashinath
						//ls_errcode = lnvo_vouch.gbf_porcp_advance(as_tranid,' ',' ',0,' ')
						ls_errcode = lnvo_pord.gbf_porder_advance(as_tranid,0,'PR')
						// end of added by kashinath
					End If
				end if
	
				//Added by Brijesh Soni on 11-03-2006
				//Update Insurance Amount. in Insurance Master 
				if isnull(ls_errcode) or len(trim(ls_errcode)) = 0 then
					//Global structure s_ins_parm is used for storing insurance parameters.
					s_ins_parm lstr_ins
					
					select policy_no into :ls_policy_no from porder where purc_order = :purcOrder ;
					if get_sqlcode() < 0 then
						ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode))
						goto errfound
					end if
					
					if isnull(ls_errcode) or len(trim(ls_errcode)) = 0 then
						select tran_id	,	agent_code	into :ls_tran_id	,	:ls_agent_code
						from insurance where policy_no = :ls_policy_no ;
						if get_sqlcode() < 0 then
							ls_errcode = 'DS000' + trim(string(sqlca.sqldbcode))
							goto errfound
						end if
					end if
					if isnull(ls_errcode) or len(trim(ls_errcode)) = 0 then
						if len(trim(ls_tran_id)) > 0 then
							lstr_ins.tran_id__ins = ls_tran_id
							lstr_ins.doc_no 	= as_tranid
							lstr_ins.doc_date = tranDate
							lstr_ins.ref_ser 	= 'P-RCP'
							lstr_ins.doc_value = ld_net_amt
							lstr_ins.curr_code = currCode
					
							if isnull(lstr_ins.curr_code) then 
								ls_errcode = "VTCURRCD1"
							end if
							
							lstr_ins.exch_rate = exchRate
							if isnull(lstr_ins.exch_rate) then 
								ls_errcode = "VTEXCH1"
							end if
							lstr_ins.doc_type = 'I'
							//Certificate and Policy number are same
							lstr_ins.cert_no = ls_policy_no
							lstr_ins.bulk = 'Y'
							select count(*) into :ll_cnt from insurance_det 
							where tran_id__ins = :lstr_ins.tran_id__ins and
									ref_ser = 'P-RCP' and
									ref_id =   :lstr_ins.doc_no and
									cert_no =  :lstr_ins.cert_no; 
								if ll_cnt > 0 then
									ls_errcode = 'VTINSNF1'
								else
									ls_errcode = gf_ins_upd(lstr_ins)	
								end if
						end if
					end if
				end if
				//End by Brijesh Soni 
			end if
			//////////////////////////////////////////////////////////////*/
		} // end try
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}
				if(rs1 != null)
				{
					rs1.close();
					rs1 = null;					
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}
				if(pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;					
				}
				if(pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private double calcEffRate(String lineNo,double stdQuantity,double acrate,double taxAmount,String tranId,String grossNet, Connection conn ) throws RemoteException, ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		double rate = 0, recoAmount = 0, additionalCost = 0, netAmount = 0, exchRate = 0;
		try
		{

			sql = "select (case when exch_rate is null then 0 else exch_rate end ) exch_rate from porcp " 
				+ " where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				exchRate =  rs.getDouble(1); 
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
	
			if (exchRate == 0)
			{
				exchRate = 1;
			}
	
			sql = "select (case when sum(case when reco_amount is null then 0 else reco_amount end) is null then 0 else sum(case when reco_amount is null then 0 else reco_amount end) end) from taxtran "
					+ " where tran_code = 'P-RCP' and tran_id = ? and line_no = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			pstmt.setString(2,lineNo);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				recoAmount =  rs.getDouble(1); 
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			
			sql  = "select (case when additional_cost is null then 0 else additional_cost end), "
				+ "(case when net_amt is null then 0 else net_amt end ) " 
				+ " from porcpdet where tran_id = ? and line_no = ?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			pstmt.setString(2,lineNo);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				additionalCost =  rs.getDouble(1); 
				netAmount =  rs.getDouble(2);
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
	
			// 22/01/01 manoharan gross rate calculation added
			if ("N".equals(grossNet))
			{
				rate = ( ((netAmount - recoAmount) * exchRate)  + additionalCost ) / stdQuantity;
			}
			else
			{
				rate = ( (netAmount * exchRate) + additionalCost) / stdQuantity;
			}
		}
		catch(Exception e)
		{

			throw new ITMException(e);
		}

		return rate;		
	}
	private String getCurrdateAppFormat() throws RemoteException, ITMException
	{
		String currAppdate ="";
		java.sql.Timestamp currDate = null;
		try
		{
				Object date = null;
				currDate =new java.sql.Timestamp(System.currentTimeMillis()) ;
				System.out.println(genericUtility.getDBDateFormat());
				java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateFormat());
				date = sdf.parse(currDate.toString());
				currDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
				currAppdate = new java.text.SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDate).toString();
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return (currAppdate);
	}
	private java.sql.Timestamp getCurrtDate() throws RemoteException, ITMException
	{
		String currAppdate ="";
		java.sql.Timestamp currDate = null;
		try
		{
				Object date = null;
				currDate =new java.sql.Timestamp(System.currentTimeMillis()) ;
				java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(genericUtility.getDBDateFormat());
				date = sdf.parse(currDate.toString());
				currDate =	java.sql.Timestamp.valueOf(sdf.format(date).toString() + " 00:00:00.0");
				
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return (currDate);
	}
	
	public String checkReplVal( String tranId, Connection conn ) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		
		String ret = "";
		String rcpType = "";
		String retString = "";
		String sql = "";

		double amount = 0.0;
		double retAmount = 0.0;
		double diffAmount = 0.0;
		String parmValue = "0";

		try
		{
			
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
					
			sql = "SELECT TRAN_ID__REF ,RECIEPT_TYPE, AMOUNT FROM PORCP WHERE TRAN_ID = ? AND TRAN_SER = 'P-RCP'";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				ret = checkNull(rs.getString("TRAN_ID__REF"));
				rcpType = checkNull(rs.getString("RECIEPT_TYPE"));
				amount = rs.getDouble("AMOUNT");
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			
			if ( rcpType.equalsIgnoreCase("R") )
			{
				sql = "SELECT AMOUNT FROM PORCP WHERE TRAN_ID = ? AND TRAN_SER = 'P-RET'";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, ret);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					retAmount = rs.getDouble("AMOUNT");
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				DistCommon distCommon = new DistCommon();	
				parmValue = distCommon.getDisparams("999999", "DIFF_RCP_RET_AMT", conn);
				if (parmValue == null || "NULLFOUND".equals(parmValue))
				{
					diffAmount = Double.parseDouble(parmValue);
				}
				if ( (amount - retAmount) > diffAmount )
				{
					retString = itmDBAccessLocal.getErrorString("","VTOVRDIFF","");	
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;	
	}

	public String chkUpdAddlCost( String tranId, Connection conn ) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rs = null;

		String sql = "";
		String retString = "";
		String lineNo = "";
		
		double hdrCost = 0.0;
		double detCost = 0.0;
		double sumCost = 0.0;
		double sumValue = 0.0;
		double value = 0.0;
		double qty = 0.0;
		double rate = 0.0;

		int updCnt;

		long row = 0;

		try
		{
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			
			sql = "SELECT (CASE WHEN TOTAL_ADDITIONAL_COST IS NULL THEN 0 ELSE TOTAL_ADDITIONAL_COST END) AS TOTAL_ADDITIONAL_COST FROM PORCP WHERE TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				hdrCost = rs.getDouble("TOTAL_ADDITIONAL_COST");
			}
			else
			{
				retString = itmDBAccessLocal.getErrorString("","VTPORCP2","");
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}

			if ( hdrCost != 0 ) 
			{
				sql = "SELECT (CASE WHEN SUM(CASE WHEN ADDITIONAL_COST IS NULL THEN 0 ELSE ADDITIONAL_COST END) IS NULL THEN 0 ELSE		   SUM(CASE WHEN ADDITIONAL_COST IS NULL THEN 0 ELSE ADDITIONAL_COST END) END), ( CASE WHEN SUM( (CASE WHEN			   QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM END) * (CASE WHEN RATE__STDUOM IS NULL THEN 0 ELSE			   RATE__STDUOM END) ) IS NULL THEN 0 ELSE SUM( (CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM		   END) * (CASE WHEN RATE__STDUOM IS NULL THEN 0 ELSE RATE__STDUOM END) ) END) FROM PORCPDET WHERE TRAN_ID = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					sumCost = rs.getDouble(1);
					sumValue = rs.getDouble(2);
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs != null)
				{
					rs.close();
					rs = null;
				}

				if ( sumCost != 0 && hdrCost != sumCost )
				{
					retString = itmDBAccessLocal.getErrorString("","VTADDLCST","");
				}
				
				if ( sumCost == 0 )
				{
					sql = "SELECT PORCPDET.LINE_NO, PORCPDET.QUANTITY__STDUOM, PORCPDET.RATE__STDUOM FROM PORCPDET, ITEM ITEM_A,		   ITEM ITEM_B, ACCOUNTS, SPECIFICATION, PORCP WHERE ( PORCPDET.ITEM_CODE__MFG = ITEM_B.ITEM_CODE (+)) AND           ( PORCPDET.ACCT_CODE__DR = ACCOUNTS.ACCT_CODE (+)) AND  ( PORCPDET.SPEC_REF = SPECIFICATION.SPEC_REF (+))		   AND           ( PORCPDET.ITEM_CODE = ITEM_A.ITEM_CODE ) AND  ( PORCPDET.TRAN_ID = PORCP.TRAN_ID ) AND  ( (        PORCPDET.TRAN_ID = ? ) ) ORDER BY PORCPDET.LINE_NO ASC "; 
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);
					rs = pstmtSql.executeQuery();
					while ( rs.next() )
					{
						lineNo = checkNull(rs.getString("LINE_NO"));
						qty = rs.getDouble("QUANTITY__STDUOM");
						rate = rs.getDouble("RATE__STDUOM");
						value = qty * rate;
						detCost =  hdrCost / sumValue * value;
						sql = "UPDATE PORCPDET SET ADDITIONAL_COST = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, detCost);
						pstmtUpd.setString(2, tranId);
						//pstmtUpd.setString(3, lineNo);
						pstmtUpd.setInt(3,Integer.parseInt(lineNo.trim()));//change done by kunal  on 6/11/12
						updCnt = pstmtUpd.executeUpdate();
						if ( updCnt != 1 )
						{
							retString = itmDBAccessLocal.getErrorString("","UPDCNTNOT1","");
						}
						if( pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;					
						}
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if ( rs != null)
					{
						rs.close();
						rs = null;
					}
				}
			}
		}
		catch(Exception e)

		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				if( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}	
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}

	/*public String createAssetInstall ( String tranId, String orderNo, Connection conn)  throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rs = null;

		String retString = "";
		String key = "";
		String keyStr = "";
		String siteCode = "";
		String tranSer = "";
		String itemSer = "";
		String suppCode = "";
		String currCode = "";
		String acctCode = "";
		String cctrCode = "";
		String grpCode = "";
		String itemCode = "";
		String remarks = "";
		String assetCode = "";
		String lineNo = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";
		String suppName = "";
		String token = "";
		String string = "";
		String varValue = "";
		String sql = "";
		String cctrCodeDr = "";
		String excTax[] = {};
		String alocCode = "";
		String excTaxCodeAsset = "";
		String excTaxCode = "";
		String assetInstallTax = "";
		String installTax[] = {};
		String acctCodeAp = "";
		String cwipSubGroup = "";
		String sgroupCode = "";
		String subGroup = "";
		String tranType = "";
		String purcOrder = "";
		String lineNoOrd = "";
		String projCode = "";
		String billNo = "";
		String dcNo = "";
		String invoiceNo = "";
		String locCodeAprv = "";
		String qcLocCode = "";
		String qcItemCode = "";
		String qcLotNoNew = "";
		String qcLotNo = "";
		String commVarValue = "";
		String octroiVarValue = "";
		String taxOctroi[] = {};
		String taxComm[] = {};

		double othChg = 0.0;
		double exchRate = 0.0;
		double rate = 0.0;
		double netAmt = 0.0;
		double disc = 0.0;
		double quantity = 0.0;
		double passed = 0.0;
		double totQuantity = 0.0;
		double octroiChgs = 0.0;
		double commChgs = 0.0;

		Date today = new Date();
		Date tranDate = null;
		Date billDate = null;
		Date dcDate = null;
		Date invoiceDate = null;

		long lineNo = 0;
		long qcCnt = 0;

		try
		{
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			DistCommon distCommon = new DistCommon();
			FinCommon finCommon = new FinCommon();
			GenericUtility genericUtility = GenericUtility.getInstance();
		    DateFormat dtFormat = new SimpleDateFormat(genericUtility.getApplDateTimeFormat());

			if ( ( orderNo == null ) || ( orderNo.trim().length() == 0 ) )
			{
				orderNo = "";
			}

			sql = "SELECT TRAN_DATE, SITE_CODE, TRAN_SER, ITEM_SER, SUPP_CODE, CURR_CODE, OTHER_CHARGES, REMARKS, CASE WHEN EXCH_RATE		   IS NULL THEN 0 ELSE EXCH_RATE END, DC_NO, DC_DATE, INVOICE_NO, INVOICE_DATE FROM PORCP WHERE TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				tranDate = dtFormat.parse(rs.getDate("TRAN_DATE"));
				siteCode = checkNull(rs.getString("SITE_CODE"));
				tranSer = checkNull(rs.getString("TRAN_SER"));
				itemSer = checkNull(rs.getString("ITEM_SER"));
				suppCode = checkNull(rs.getString("SUPP_CODE"));
				currCode = checkNull(rs.getString("CURR_CODE"));
				othChg = rs.getDouble("OTHER_CHARGES");
				remarks = checkNull(rs.getString("REMARKS"));
				exchRate = rs.getDouble("EXCH_RATE");
				dcNo = checkNull(rs.getString("DC_NO"));
				dcDate = dtFormat.parse(rs.getDate("DC_DATE"));
				invoiceNo = checkNull(rs.getString("INVOICE_NO"));
				invoiceDate = dtFormat.parse(rs.getDate("INVOICE_DATE"));
				tranType = "C";
			}
			else
			{
				retString = itmDBAccessLocal.getErrorString("","VTPORCP1","");
				break;
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}

			excTaxCode = distCommon.getDisparams("999999", "EXC_TAX_CODE", conn);	
			
			if ( excTaxCode != null && excTaxCode.trim().length() > 0 )
			{	 
				//gf_populate_array(ls_exc_tax_code,",",ls_exc_tax);
				//ls_exc_tax_code = gf_get_SqlInString(ls_exc_tax)
			}
			else
			{
				excTaxCode = "";
			}

			assetInstallTax = distCommon.getDisparams("999999", "ASSET_REGISTER_INSTALL_TAXCODE", conn);
			if ( assetInstallTax != null && assetInstallTax.trim().length() > 0 )
			{	
				//gf_populate_array(ls_asset_install_tax,",",ls_install_tax)
				//ls_asset_install_tax = gf_get_SqlInString(ls_install_tax)
			}
			else
			{
				assetInstallTax = "";
			}

			octroiVarValue = distCommon.getDisparams("999999","ASSET_REGISTER_OCTROI_TAXCODE", conn);
			if ( octroiVarValue != null && octroiVarValue.trim().length() > 0 ) 
			{
				//gf_populate_array(ls_octroi_var_value,",",ls_taxoctroi)
				//ls_octroi_var_value = gf_get_SqlInString(ls_taxoctroi)
			}
			else
			{
				octroiVarValue = "";
			}

			commVarValue = distCommon.getDisparams("999999","ASSET_REGISTER_COMM_TAXCODE", conn);
			if ( commVarValue != null && commVarValue.trim().length() > 0 )
			{
				//gf_populate_array(ls_comm_var_value,",",ls_taxcomm)
				//ls_comm_var_value = gf_get_SqlInString(ls_taxcomm)
			}
			else
			{
				commVarValue = "";
			}

			if ( invoiceNo != null && invoiceNo.trim().length() > 0 )
			{
				billNo = invoiceNo;
				billDate = invoiceDate;
			}
			else
			{
				billNo = dcNo;
				billDate = dcDate;
			}

			sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, "W_ASSET_INSTALL");
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				key = rs.getString("KEY_STRING");
			}
			else
			{
				sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, "GENERAL");
				rs = pstmtSql.executeQuery();
				
				if( rs.next() )
				{
					key = rs.getString("KEY_STRING");	
				}
				else
				{
					retString = itmDBAccessLocal.getErrorString("","NOKEYSTRING","");
					break;
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}

			sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, "W_ASSET_REGISTER");
			rs = pstmtSql.executeQuery();
			if( rs.next() )
			{
				keyStr = rs.getString("KEY_STRING");	
			}
			else
			{
				retString = itmDBAccessLocal.getErrorString("","VTSEQ","");
			    break;
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}
	
			totQuantity = 0.0 ;
			
			sql = "SELECT LINE_NO, ITEM_CODE, QUANTITY, RATE, NET_AMT, LOC_CODE, LOT_NO, LOT_SL, DISCOUNT, CCTR_CODE__DR, PURC_ORDER,		   LINE_NO__ORD FROM PORCPDET WHERE TRAN_ID = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( !rs.next() )
			{
				retString = itmDBAccessLocal.getErrorString("","VTOPENCUR","");
				break;
			}
			while ( rs.next() )
			{
				ResultSet rs1;

				lineNo = checkNull(rs.getString("LINE_NO"));
				itemCode = checkNull(rs.getString("ITEM_CODE"));
				quantity = rs.getDouble("QUANTITY");
				rate = rs.getDouble("RATE");
				netAmt = rs.getDouble("NET_AMT"); 
				locCode = checkNull(rs.getString("LOC_CODE"));
				lotNo = checkNull(rs.getString("LOT_NO"));
				lotSl =  checkNull(rs.getString("LOT_SL")); 
				disc = rs.getDouble("DISCOUNT");
				cctrCodeDr = checkNull(rs.getString("CCTR_CODE__DR")); 
				purcOrder = checkNull(rs.getString("PURC_ORDER")); 
				lineNoOrd = checkNull(rs.getString("LINE_NO__ORD"));

				double taxExciseAmt = 0.0;
				double taxExciseRecoAmt = 0.0;
				double rcprecoAmt = 0.0;
				double rcptaxAmt = 0.0;
				double exciseAmt = 0.0;
				double taxAmt = 0.0;
				double excrecoAmt = 0.0;
				double discAmt = 0.0;
				double taxInstallChgs = 0.0;
				double taxAmount = 0.0;
				double originalValue = 0.0;
				double taxRecoAmt = 0.0;
				
				
				lineNo = Long.parseLong(lineNo);

				if ( orderNo != null  && orderNo.trim().length() > 0 )
				{
					sql = "SELECT LOC_CODE__APRV, LOC_CODE, ITEM_CODE, (CASE WHEN QTY_PASSED IS NULL THEN 0 ELSE QTY_PASSED END),		   LOT_NO__NEW, LOT_NO FROM QC_ORDER WHERE QORDER_NO = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, orderNo);
					rs1 = pstmtSql.executeQuery();
					if( rs1.next() )
					{
						locCodeAprv = checkNull(rs1.getString("LOC_CODE__APRV"));
						qcLocCode = checkNull(rs1.getString("LOC_CODE"));
						qcItemCode = checkNull(rs1.getString("ITEM_CODE"));
						passed =  rs1.getDouble("QTY_PASSED"); 
						qcLotNoNew = checkNull(rs1.getString("LOT_NO__NEW"));
						qcLotNo = checkNull(rs1.getString("LOT_NO"));
					}
					if( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					
					
					if ( ( qcLocCode == locCode) && ( qcItemCode == itemCode) && ( qcLotNo == lotNo) && ( totQuantity < passed ) )
					{
						locCode = locCodeAprv;
						lotNo	= qcLotNoNew;
						totQuantity = totQuantity + quantity;
					}
					else
					{
						continue;
					}
				}
				
				sql = "SELECT PROJ_CODE FROM PORDDET WHERE PURC_ORDER = ? AND LINE_NO  = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, purcOrder);
				pstmtSql.setString(2, lineNoOrd);	
				rs1 = pstmtSql.executeQuery();
				if ( rs1.next() )
				{
					projCode = checkNull(rs1.getString("PROJ_CODE"));
				}
				else
				{
					retString = itmDBAccessLocal.getErrorString("","NOPROJCODE","");
					break;
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				sql = "SELECT GRP_CODE FROM ITEM  WHERE ITEM_CODE = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, itemCode);
				rs1 = pstmtSql.executeQuery();
				if ( rs1.next() )
				{
					grpCode = checkNull(rs1.getString("GRP_CODE"));
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				sql = "INSERT INTO ASSET_REGISTER VALUES (?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, siteCode);
				pstmtSql.setString(2, itemSer);
				rs1 = pstmtSql.executeQuery();
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}

				//ls_asset_code = gf_gen_key_nvo(lds_keygen, 'ASSREG', 'asset_code', ls_keystr)
				
				if ( assetCode.equalsIgnoreCase("ERROR") || assetCode == null || assetCode.trim().length() == 0 )
				{
					retString = itmDBAccessLocal.getErrorString("","VTASSETCD","");
					break;
				}
				
				sql  = "SELECT ALOC_CODE FROM ASSET_LOCATION WHERE SITE_CODE = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, siteCode);	
				rs1 = pstmtSql.executeQuery();
				
				if ( rs1.next() )
				{
					alocCode =  checkNull(rs1.getString("ALOC_CODE"));; 
				}
				else
				{
					retString = itmDBAccessLocal.getErrorString("","NOALOCCODE","");	
					break;
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				sql = "INSERT INTO ASSET_INSTALL(TRAN_DATE, SITE_CODE, ASSET_CODE, ASSET_CODE__PAR, ALOC_CODE, ASSET_TYPE, DESCR,		   INST_DATE, USE_DATE, REMARKS, EMP_CODE, ITEM_CODE, GRP_CODE, CURR_CODE, EXCH_RATE, CHG_USER, CHG_DATE, CHG_TERM,		   ASSET_SALE_TYPE, CONFIRMED, TRAN_TYPE, PROJ_CODE, TRAN_ID__RCP) VALUES											   (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, today);	
				pstmtSql.setString(2, siteCode);	
				pstmtSql.setString(3, assetCode);	
				pstmtSql.setString(4, assetCode);	
				pstmtSql.setString(5, alocCode);	
				pstmtSql.setString(6, "D");	
				pstmtSql.setString(7, remarks);	
				pstmtSql.setDate(8, tranDate);	
				pstmtSql.setDate(9, tranDate);	
				pstmtSql.setString(10, remarks);	
				pstmtSql.setString(11, loginEmpCode);	
				pstmtSql.setString(12, itemCode);	
				pstmtSql.setString(13, grpCode);	
				pstmtSql.setString(14, currCode);	
				pstmtSql.setDouble(15, exchRate);	
				pstmtSql.setString(16, userid);	
				pstmtSql.setString(17, today);	
				pstmtSql.setString(18, termid);	
				pstmtSql.setString(19, "N");	
				pstmtSql.setString(20, "N");	
				pstmtSql.setString(21, tranType);	
				pstmtSql.setString(22, projCode);	
				pstmtSql.setString(23, tranId);	
				rs1 = pstmtSql.executeQuery();
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}

				//ls_tran_id = gf_gen_key_nvo(lds_asset_install,'FAINST', 'tran_id', ls_key)

				if ( assetInstallTax != null && assetInstallTax .trim.length()  > 0 )
				{
					sql = "SELECT SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE TAX_AMT END - CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE		   RECO_AMOUNT END) FROM   TAXTRAN  WHERE  TRAN_CODE = 'P-RCP'  AND TRAN_ID = ? AND LINE_NO = ? AND TAX_CODE IN		   (?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);	
					pstmtSql.setString(2, lineNo);	
					pstmtSql.setString(3, assetInstallTax);	
					rs1 = pstmtSql.executeQuery();
					if( rs1.next() )
					{
						taxInstallChgs = rs1.getDouble(1);;
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				if ( taxInstallChgs == null )
				{
					taxInstallChgs = 0.0 ;
				}

				if ( octroiVarValue != null && octroiVarValue.trim().length() > 0 )
				{
					sql = " SELECT SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE TAX_AMT END - CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE		    RECO_AMOUNT END) FROM   TAXTRAN WHERE  TRAN_CODE = 'P-RCP' AND TRAN_ID = ? AND LINE_NO = ? AND TAX_CODE IN		    (?)"; 
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);	
					pstmtSql.setString(2, lineNo);	
					pstmtSql.setString(3, octroiVarValue);	
					rs1 = pstmtSql.executeQuery();
					if( rs1.next() )
					{
						octroiChgs = rs1.getDouble(1);;
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				if ( octroiChgs == null )
				{
					octroiChgs = 0.0;
				}

				if ( commVarValue != null &&  commVarValue.trim().length() > 0 )
				{
					sql = " SELECT SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE TAX_AMT END - CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE		    RECO_AMOUNT END) FROM   TAXTRAN WHERE  TRAN_CODE = 'P-RCP' AND TRAN_ID = ? AND LINE_NO = ? AND TAX_CODE IN		    (?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);	
					pstmtSql.setString(2, lineNo);	
					pstmtSql.setString(3, commVarValue);	
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						commChgs = rs1.getDouble(1); 
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				if ( commChgs == null )
				{
					commChgs = 0.0;
				}
				
				sql = "INSERT INTO ASSET_INSTALL (CCTR_CODE, CURR_CODE__INST, EXCH_RATE__INST, INSTALL_CHGS, OCTROI_CHGS, COMM_CHGS,		   INST_CHGS__BC) VALUES (?,?,?,?,?,?,?) ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, cctrCodeDr);	
				pstmtSql.setString(2, currCode);	
				pstmtSql.setDouble(3, exchRate);	
				pstmtSql.setDouble(4, taxInstallChgs);	
				pstmtSql.setDouble(5, octroiChgs);	
				pstmtSql.setDouble(6, commChgs);	
				pstmtSql.setDouble(7, taxInstallChgs * exchRate);	
				rs1 = pstmtSql.executeQuery();
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				cctrCode = f.getFinparams("999999", "ACCT_CWIP", conn);
				if ( cctrCode !=  null ) 
				{
					acctCode = distCommon.getToken( cctrCode, ";");
					sql = "INSERT INTO ASSET_INSTALL(ACCT_CODE__INST, CCTR_CODE__INST) VALUES(?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, acctCode);	
					pstmtSql.setString(2, cctrCode);
					rs1 = pstmtSql.executeQuery();
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				
				sql = "INSERT INTO ASSET_INSTALLDET(TRAN_ID, LINE_NO, TRAN_ID__RCP, LINE_NO__RCP, ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL,		   QUANTITY, RATE, SUPP_CODE, SUPP_NAME, RCP_DATE, BILL_NO, BILL_DATE, CURR_CODE, EXCH_RATE, GRP_CODE, ASSET_CODE)		   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);	
				pstmtSql.setString(2, "1");	
				pstmtSql.setString(3, tranId);	
				pstmtSql.setString(4, lineNo);	
				pstmtSql.setString(5, itemCode);	
				pstmtSql.setString(6, locCode);	
				pstmtSql.setString(7, lotNo);	
				pstmtSql.setString(8, lotSl);	
				pstmtSql.setDouble(9, quantity);	
				pstmtSql.setDouble(10, rate);	
				pstmtSql.setString(11, suppCode);	
				pstmtSql.setString(12, suppName);	
				pstmtSql.setDate(13, tranDate);	
				pstmtSql.setString(14, billNo);	
				pstmtSql.setDate(15, billDate);	
				pstmtSql.setString(16, currCode);	
				pstmtSql.setDouble(17, exchRate);	
				pstmtSql.setString(18, grpCode);	
				pstmtSql.setString(19, assetCode);	
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				sql = "SELECT SUM(CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE RECO_AMOUNT END), SUM(CASE WHEN TAX_AMT IS NULL THEN 0 ELSE		   TAX_AMT END) FROM  TAXTRAN WHERE  TRAN_CODE = 'P-RCP' AND TRAN_ID = ? AND LINE_NO = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId );
				pstmtSql.setString(2, lineNo );	
				rs1 = pstmtSql.executeQuery();	
				if ( rs1.next() )
				{
					taxRecoAmt = rs1.getDouble("RECO_AMOUNT");
					taxAmt = rs1.getDouble("TAX_AMT");
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				if ( taxRecoAmt == null )
				{
					taxRecoAmt = 0.0;
				}
				if ( taxAmt == null )
				{
					taxAmt = 0.0;
				}
				
				if ( excTaxCode != null && excTaxCode.trim().length() > 0 )
				{
					sql = "SELECT SUM(CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE RECO_AMOUNT END), SUM(CASE WHEN TAX_AMT IS NULL THEN 0		   ELSE TAX_AMT END)  FROM TAXTRAN WHERE TRAN_CODE = 'P-RCP' AND TRAN_ID = ? AND LINE_NO = ? AND TAX_CODE IN          (?) ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId );
					pstmtSql.setString(2, lineNo );	
					pstmtSql.setString(3, excTaxCode );
					rs1 = pstmtSql.executeQuery();	

					if ( rs1.next() )
					{
						taxExciseRecoAmt = rs1.getDouble("RECO_AMOUNT");
						taxExciseAmt = rs1.getDouble("TAX_AMT");
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}		
				if ( taxExciseRecoAmt == null )
				{
					taxExciseRecoAmt = 0.0;
				}
				if ( taxExciseAmt == null )
				{
					taxExciseAmt = 0.0;
				}
				
				if ( excTaxCodeAsset != null && excTaxCodeAsset.trim().length() ) 
				{
					sql = "SELECT SUM(CASE WHEN RECO_AMOUNT IS NULL THEN 0 ELSE RECO_AMOUNT END), SUM(CASE WHEN TAX_AMT IS NULL THEN 0		   ELSE TAX_AMT END) FROM   TAXTRAN WHERE  TRAN_CODE = 'P-RCP' AND TRAN_ID = ? AND LINE_NO = ? AND TAX_CODE IN		   (?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId );
					pstmtSql.setString(2, lineNo );	
					pstmtSql.setString(3, excTaxCodeAsset );
					rs1 = pstmtSql.executeQuery();					
					if ( rs1.next() )
					{
						rcprecoAmt =  rs1.getDouble("RECO_AMOUNT");
						rcptaxAmt =  rs1.getDouble("TAX_AMT");
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}
					if( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				if ( rcptaxAmt == null )
				{
					rcptaxAmt = 0.0;
				}
				if ( rcprecoAmt == null )
				{
					rcprecoAmt 	= 0.0;
				}
				
				taxAmount = taxAmt - taxRecoAmt - taxInstallChgs - octroiChgs - commChgs;
				sql = "INSERT INTO ASSET_INSTALL(TAX_AMT) VALUES (?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, taxAmount );
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				exciseAmt  = ( taxExciseAmt - taxExciseRecoAmt) + ( rcptaxAmt - rcprecoAmt);
				excrecoAmt = taxExciseRecoAmt + rcprecoAmt;

				sql = "INSERT INTO ASSET_INSTALLDET(TAX_RECO_AMT, EXCISE_AMT, EXCRECO_AMT) VALUES(?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, taxRecoAmt - excrecoAmt );
				pstmtSql.setDouble(2, exciseAmt );
				pstmtSql.setDouble(3, excrecoAmt );
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}

				sql = "INSERT INTO ASSET_INSTALL(EXCRECO_AMT) VALUES(?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, excrecoAmt );
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}

				if ( disc == null )
				{
					disc = 0.0;
				}
				discAmt	=	(rate * quantity * disc) / 100 ; 	
				if ( discAmt == null )
				{
					discAmt = 0.0;
				}

				originalValue = ( rate * quantity ) + taxAmount - discAmt;

				sql  = "INSERT INTO ASSET_INSTALL(ORIGINAL_VALUE, ORIGINAL_VALUE__BC) VALUES(?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, originalValue );
				pstmtSql.setDouble(2 , originalValue * exchRate );
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				
				sql = "INSERT INTO ASSET_INSTALLDET(ORIGINAL_VALUE) VALUES(?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, originalValue );
				rs1 = pstmtSql.executeQuery();	
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
			}
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				if( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}	
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;	
	}*/

	public String createQc( String tranId, String siteCode, Connection conn) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		PreparedStatement pstmtInsert = null, pstmt1 = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;

		String sql = "";
		String retString = "";
		String itemCode = "";
		String qcType = "";
		String lot = "";
		String lotNoRcp = "";
		String lotSl = "";
		String key = "";
		String win = "w_qcorder_new";
		String qcNo = "";
		String lotRcp = "";
		String locCode = "";
		String lineNo = "";
		//String tranId = "";
		String unit = "";
		String batchNo = "";
		String aprv = "";
		String rej = "";
		String qcReqd = "";
		String lotNo = "";
		String genLotAuto = "";
		String rcpLine = "";
		String itemSer = "";
		String qcReqdSite = "";
		String confTran = "";
		String unitPur = "";
		String spec = "";
		String purcOrder = "";
		String lineNoOrd = "";
		String empCodeQcaprv = "";
		String purcorder = "";
		String pordType = "";
		String jobWorkType = "";
		String subContractType = "";
		String genLotSubctr = "";
		String value = "";
		String nullPo = "";
		String lotNoManualSite = "";
		String sitecode = "";
		String siteString = "";
		String emp = "";
		String procMth = "";
		String poRcpDetSpec = "";
		String dbName = "";
		String suppCode = "",suppCodeMnfr = "";//added by Kunal on 8/11/12

		double qty = 0.0;
		double qtySample = 0.0;
		double passedQty = 0.0;
		double qcLeadTime = 0.0;
		double convQty = 0.0;
		double quantity = 0.0, excessShortQty = 0.0 ,quantityStduom = 0.0; //added by Kunal on 5/11/12 

		java.sql.Timestamp expiryDate = null;
		java.sql.Timestamp qcDueDate = null;
		java.sql.Timestamp retestDt = null;
		java.sql.Timestamp tranDate = null;
		java.sql.Timestamp confDate = null;//new Date();
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp today = null;//new Date();	

		ArrayList qtyAr = null;
		
		int updCnt = 0;
		SimpleDateFormat dtFormat = null;
		String xmlValues = "";
		
		try
		{

			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			DistCommon distCommon = new DistCommon(); 
			GenericUtility genericUtility = GenericUtility.getInstance();
		    dtFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			dbName = CommonConstants.DB_NAME;
			
			nullPo = distCommon.getDisparams("999999", "RCP_WO_PO", conn); 
			
			sql = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, win );
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				key = checkNull(rs.getString("KEY_STRING"));
			}
			else
			{
				pstmtSql.close();
				pstmtSql = null;
				rs.close();
				rs = null;
				sql = "SELECT KEY_STRING FROM TRANSETUP WHERE  TRAN_WINDOW = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, "GENERAL");
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					key = checkNull(rs.getString("KEY_STRING"));
				}
				pstmtSql.close();
				pstmtSql = null;
				rs.close();
				rs = null;
			}
			pstmtSql.close();
			pstmtSql = null;
			rs.close();
			rs = null;
			if (key == null || key.trim().length() == 0)
			{
				return itmDBAccessLocal.getErrorString("","NOKEYSTRING","");
			}
			sql = "SELECT (CASE WHEN QC_REQD IS NULL THEN 'Y' ELSE QC_REQD END) AS QC_REQD, PURC_ORDER, TRAN_DATE,SUPP_CODE FROM PORCP WHERE TRAN_ID = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				qcReqd = checkNull(rs.getString("QC_REQD"));
				purcorder = checkNull(rs.getString("PURC_ORDER"));
				tranDate = rs.getTimestamp("TRAN_DATE");
				suppCode = checkNull(rs.getString("SUPP_CODE")); //added by Kunal on 8/11/12
			}
			pstmtSql.close();
			pstmtSql = null;
			rs.close();
			rs = null;
			genLotAuto = distCommon.getDisparams("999999", "GENERATE_LOT_NO_AUTO", conn);

			value = distCommon.getDisparams("999999", "UOM_ROUND", conn);
			if ( value == null )
			{
				retString = itmDBAccessLocal.getErrorString("","VTUOMVARPARM","");
				return retString;
				//break;
			}

			confTran = distCommon.getDisparams("999999", "RCP_TRAN_CONF_DATE", conn);
			if ( confTran == null )
			{
				retString = itmDBAccessLocal.getErrorString("","VTDISPARM","");
				return retString;
				//break;
			}

			if ( !"TRAN".equalsIgnoreCase(confTran) && !"CONF".equalsIgnoreCase(confTran) )
			{
				retString = itmDBAccessLocal.getErrorString("","VTDISPARM","");
				return retString;
				//break;
			}

			if ( "CONF".equalsIgnoreCase(confTran) )
			{
				tranDate = getCurrtDate(); //confDate;
			}

			if ( purcorder != null && !"Y".equals(nullPo) )
			{
				sql = "SELECT PORD_TYPE FROM PORDER WHERE PURC_ORDER = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, purcorder);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					pordType = checkNull(rs.getString("PORD_TYPE"));
				}
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
			}

			jobWorkType = distCommon.getDisparams("999999", "JOBWORK_TYPE", conn);
			if ( jobWorkType == null )
			{
				jobWorkType = "";
			} 
			
			subContractType = distCommon.getDisparams("999999", "SUBCONTRACT_TYPE", conn);
			if ( subContractType == null )
			{
				subContractType = "";
			}
			
			genLotSubctr = distCommon.getDisparams("999999", "GEN_LOT_SUBCTR", conn);
			if ( genLotSubctr == null )
			{
				genLotSubctr = "";
			}
			
			sql = "SELECT  EMP_CODE FROM USERS WHERE CODE = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, userId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				emp = checkNull(rs.getString("EMP_CODE"));
			}
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;

			if ( "Y".equalsIgnoreCase(qcReqd) )
			{
				sql = "SELECT ITEM_CODE, MIN(LINE_NO) AS LINE_NO FROM   PORCPDET WHERE  PORCPDET.TRAN_ID = ? GROUP BY ITEM_CODE "; //ORDER BY			   MIN(LINE_NO)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				while ( rs.next() )
				{
					itemCode = checkNull(rs.getString("ITEM_CODE"));
					lineNo = checkNull(rs.getString("LINE_NO"));
					
					sql = "SELECT (CASE WHEN QC_LEAD_TIME IS NULL THEN 0 ELSE QC_LEAD_TIME END) AS QC_LEAD_TIME FROM ITEM WHERE ITEM_CODE = ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCode);
					rs1 = pstmt1.executeQuery();
					
					if ( rs1.next() )
					{
						qcLeadTime = rs1.getDouble("QC_LEAD_TIME");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					//ldt_qcduedate = datetime(relativedate(date(ldt_tran_date),lc_qcleadtime))

					sql = "SELECT SITEITEM.ITEM_SER , SITEITEM.QC_REQD, SITEITEM. PROC_MTH,ITEM.QC_REQD_TYPE  FROM SITEITEM , ITEM WHERE ITEM.ITEM_CODE = SITEITEM.ITEM_CODE AND SITEITEM.ITEM_CODE = ? AND SITEITEM.SITE_CODE = ? ";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, itemCode);
					pstmt1.setString(2, siteCode);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						itemSer = checkNull(rs1.getString("ITEM_SER"));
						qcReqdSite = checkNull(rs1.getString("QC_REQD"));
						procMth = checkNull(rs1.getString("PROC_MTH"));
						qcType = checkNull(rs1.getString("QC_REQD_TYPE")); //added by Kunal Mandhre on 29/10/12
					}
					else
					{
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
						sql = "SELECT QC_REQD_TYPE, (CASE WHEN QTY_SAMPLE IS NULL THEN 0 ELSE QTY_SAMPLE END) AS QTY_SAMPLE, PROC_MTH FROM ITEM WHERE ITEM_CODE = ?";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, itemCode);
						rs1 = pstmt1.executeQuery();
						if ( rs1.next() )
						{
							qcType = checkNull(rs1.getString("QC_REQD_TYPE"));
							qtySample = rs1.getDouble("QTY_SAMPLE");
							procMth =  checkNull(rs1.getString("PROC_MTH"));
						}
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;

					if ( procMth != null && procMth.trim().length() > 0 )
					{
						qtySample = 0.0; 
					}
					
					if ( qcType == null || qcType.trim().length() == 0) //change done by Kunal on 26/10/12 add trim() cond. 
					{
						qcType = "S";
					}
					System.out.println("qc type new = "+qcType);

					if ( "S".equalsIgnoreCase(qcType) && "Y".equalsIgnoreCase(qcReqdSite) ) 
					{
						//change done by Kunal on 26/10/12 rename column as   CONV__QTY_STDUOM,SPEC_REF,BATCH_NO
						sql = "SELECT LINE_NO, LOC_CODE	, LOT_NO, LOT_SL,  (CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END + CASE		   WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END) , "
						    +" (CASE WHEN CONV__QTY_STDUOM IS NULL         THEN 1 ELSE CONV__QTY_STDUOM END) as CONV__QTY_STDUOM ,"
							+" UNIT__STD, (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE BATCH_NO END) as BATCH_NO	       , EXPIRY_DATE , PURC_ORDER, LINE_NO__ORD ,  "
							+" UNIT, (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF          END ) as SPEC_REF, MFG_DATE, "
							+"  ( CASE		   WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END) as EXCESS_SHORT_QTY , ( CASE		   WHEN quantity__stduom IS NULL THEN 0 ELSE quantity__stduom END) as quantity__stduom,supp_code__mnfr " //added by kunal on 5/11/12
							+" FROM  PORCPDET WHERE TRAN_ID   = ? AND   ITEM_CODE = ? ORDER BY LINE_NO ASC";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, tranId);
						pstmt1.setString(2, itemCode);
						rs1 = pstmt1.executeQuery();
						while ( rs1.next() )
						{
							lineNo = checkNull(rs1.getString("LINE_NO"));
							locCode  =  checkNull(rs1.getString("LOC_CODE"));
							lotNoRcp =  checkNull(rs1.getString("LOT_NO"));
							lotSl =  checkNull(rs1.getString("LOT_SL"));
							qty	= rs1.getDouble(5);
							convQty =  rs1.getDouble("CONV__QTY_STDUOM"); 
							unit = checkNull(rs1.getString("UNIT__STD"));
							batchNo	  = checkNull(rs1.getString("BATCH_NO")); 
							expiryDate =  rs1.getTimestamp("EXPIRY_DATE") ;
							purcOrder = checkNull(rs1.getString("PURC_ORDER"));
							lineNoOrd = checkNull(rs1.getString("LINE_NO__ORD"));
							unitPur	= checkNull(rs1.getString("UNIT"));	
							spec = checkNull(rs1.getString("SPEC_REF"));	
						    mfgDate = rs1.getTimestamp("MFG_DATE") ;
						    //added by kunal on 5/11/12 start  as per Pravin Sali Sir instruction  
							excessShortQty	= rs1.getDouble("EXCESS_SHORT_QTY"); //added by Kunal on 5/11/12
							quantityStduom	= rs1.getDouble("quantity__stduom"); //added by Kunal on 5/11/12
							System.out.println("excessShortQt ="+excessShortQty);
							System.out.println("quantityStduom ="+quantityStduom);
						    if(excessShortQty == 0)
						    {
						    	System.out.println("get qty from quantityStduom");
						    	qty = quantityStduom;
						    }
						   //added by kunal on 5/11/12 end  as per Pravin Sali Sir instruction
						    suppCodeMnfr = checkNull(rs1.getString("supp_code__mnfr"));//added by Kunal on 8/11/12
						    System.out.println("suppCodeMnfr = "+suppCodeMnfr);

							//if ( "Q".equalsIgnoreCase(value) || "B".equalsIgnoreCase(value) )
							//{
								//qty = gf_conv_qty_fact1(ls_unitpur, ls_unit, ls_itemcode, lc_Qty, lc_convqty,'Y')
								qtyAr = distCommon.getConvQuantityFact(unitPur, unit, itemCode, qty, convQty, conn);
								convQty = Double.parseDouble( qtyAr.get(0).toString() );
								qty = Double.parseDouble( qtyAr.get(1).toString() );
							//}
							//else
							//{
							//	//qty = gf_conv_qty_fact1(ls_unitpur, ls_unit, ls_itemcode, lc_Qty, lc_convqty,'N')
							//	qty = distCommon.getConvQuantityFact(unitPur, unit, itemCode, qty, convQty, conn);
							//}

							if ( purcOrder != null && !nullPo.equalsIgnoreCase("Y") )
							{
								sql = "SELECT EMP_CODE__QCAPRV FROM PORDDET WHERE  PURC_ORDER = ? AND LINE_NO = ? ";
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, purcOrder);
								pstmt2.setString(2, lineNo);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									empCodeQcaprv = checkNull(rs2.getString("EMP_CODE__QCAPRV"));
								}
								pstmt2.close();
								pstmt2 = null;
								rs2.close();
								rs2 = null;
							}

							if ( spec == null || spec.trim().length() == 0 )
							{
								sql = "SELECT CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END FROM SITEITEM WHERE SITE_CODE = ?		   AND    ITEM_CODE = ? "; 	
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, siteCode);
								pstmt2.setString(2, itemCode);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									spec = checkNull(rs2.getString(1));
								}
								pstmt2.close();
								pstmt2 = null;
								rs2.close();
								rs2 = null;
							}

							
							xmlValues = "";
							xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
							xmlValues = xmlValues + "<Header></Header>";
							xmlValues = xmlValues + "<Detail1>";
							xmlValues = xmlValues +	"<qorder_no></qorder_no>";
							xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
							xmlValues = xmlValues + "<qorder_date>" + getCurrdateAppFormat() + "</qorder_date>";
							xmlValues = xmlValues + "<qorder_type>I</qorder_type>";
							xmlValues = xmlValues + "<lot_no>" + lotNoRcp + "</lot_no>";
							xmlValues = xmlValues + "<item_ser>" + itemSer + "</item_ser>";
							xmlValues = xmlValues + "<porcp_no>" + tranId + "</porcp_no>";
							xmlValues = xmlValues + "<porcp_line_no>" + lineNo + "</porcp_line_no>";
							xmlValues = xmlValues +"</Detail1></Root>";
							System.out.println("xmlValues  :["+xmlValues+"]");
							TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
							qcNo = tg.generateTranSeqID("QC-ORD", "qorder_no", key, conn);
							System.out.println("qcNo1="+qcNo);
							
							if ( "ERROR".equals(qcNo) )
							{
								retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
								break;
							}
							
							if ( retString.trim().length() > 0 )
							{
								break;
							}
							else
							{
								sql = "SELECT LOC_CODE__APRV, LOC_CODE__REJ FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ? ";
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, siteCode);
								pstmt2.setString(2, itemCode);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									aprv = checkNull(rs2.getString("LOC_CODE__APRV"));
									rej =  checkNull(rs2.getString("LOC_CODE__REJ"));
								}
								pstmt2.close();
								pstmt2 = null;
								rs2.close();
								rs2 = null;

								
								if ( "Y".equalsIgnoreCase(genLotAuto) || "M".equalsIgnoreCase(genLotAuto) )
								{
									if ( (!pordType.equalsIgnoreCase(jobWorkType)) && (!pordType.equalsIgnoreCase(subContractType) ) )
									{
										lotNoRcp = qcNo;
									}
									else
									{
										if ( genLotSubctr.equalsIgnoreCase("Y")) 
										{
											lotNoRcp = qcNo;
										}
									}
								}

								passedQty  = qty - qtySample;
								
								if (dbName.equalsIgnoreCase("db2"))
								{
									if ( itemCode.length() == 0 )
									{
										itemCode = null;
									}
									if ( siteCode.length()== 0 )
									{
										siteCode = null;
									}
									if ( locCode .length()== 0 )
									{
										locCode = null;
									}
									if ( aprv.length() == 0 )
									{
										aprv = null;
									}
									if ( rej.length() == 0 )
									{
										rej = null;
									}
									if ( empCodeQcaprv.length() == 0 )
									{
										empCodeQcaprv = null;
									}
									if ( unit.length() == 0 )
									{
										unit = null;
									}
									if ( qcNo.length() == 0 )
									{
										qcNo = null;
									}
								}
								
								sql = "INSERT INTO QC_ORDER ( QORDER_NO, QORDER_TYPE, QORDER_DATE, SITE_CODE, "
									+ " ITEM_CODE, ROUTE_CODE, QUANTITY, QTY_PASSED, QTY_REJECTED, START_DATE, "
									+ " DUE_DATE, REL_DATE, PORCP_NO, PORCP_LINE_NO, LOT_NO, LOT_SL, CHG_DATE, "
									+ " CHG_USER, CHG_TERM, LOC_CODE, QTY_SAMPLE, STATUS, UNIT, QC_CREATE_TYPE, "
									+ " BATCH_NO, EXPIRY_DATE, LOC_CODE__APRV, LOC_CODE__REJ, UNIT__SAMPLE, "
									+ " LOT_NO__NEW, RETEST_DATE, EMP_CODE__QCAPRV, SPEC_REF, ITEM_CODE__NEW, "
									+ " MFG_DATE, EMP_CODE ,SUPP_CODE,SUPP_CODE__MFG) "
									+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
									+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)";
								pstmtInsert = conn.prepareStatement(sql);
								pstmtInsert.setString(1, qcNo);
								pstmtInsert.setString(2, "I");
								pstmtInsert.setTimestamp(3, tranDate);
								pstmtInsert.setString(4, siteCode);
								pstmtInsert.setString(5, itemCode);
								pstmtInsert.setString(6, null);
								pstmtInsert.setDouble(7, qty);
								pstmtInsert.setDouble(8, passedQty);
								pstmtInsert.setDouble(9, 0);
								pstmtInsert.setTimestamp(10, tranDate);
								pstmtInsert.setTimestamp(11, qcDueDate);
								pstmtInsert.setTimestamp(12, tranDate);
								pstmtInsert.setString(13, tranId);
								pstmtInsert.setString(14, lineNo);
								pstmtInsert.setString(15, lotNoRcp);
								pstmtInsert.setString(16, lotSl);
								pstmtInsert.setTimestamp(17, tranDate);
								pstmtInsert.setString(18, userId);
								pstmtInsert.setString(19, termId);
								pstmtInsert.setString(20, locCode);
								pstmtInsert.setDouble(21, qtySample);
								pstmtInsert.setString(22, "U");
								pstmtInsert.setString(23, unit);
								pstmtInsert.setString(24, "A");
								pstmtInsert.setString(25, batchNo);
								pstmtInsert.setTimestamp(26, expiryDate);
								pstmtInsert.setString(27, aprv);
								pstmtInsert.setString(28, rej);
								pstmtInsert.setString(29, unit);
								pstmtInsert.setString(30, lotNoRcp);
								pstmtInsert.setTimestamp(31, retestDt);
								pstmtInsert.setString(32, empCodeQcaprv);
								pstmtInsert.setString(33, spec);
								pstmtInsert.setString(34, itemCode);
								pstmtInsert.setTimestamp(35, mfgDate);
								pstmtInsert.setString(36, emp);
								pstmtInsert.setString(37, suppCode); //added by Kunal on 8/11/12 as per Pravin Sali intruction 
								pstmtInsert.setString(38, suppCodeMnfr);//added by Kunal on 8/11/12 as per Pravin Sali intruction
								updCnt = pstmtInsert.executeUpdate();
								pstmtInsert.close();
								pstmtInsert = null;
								
								if ( "Y".equalsIgnoreCase(genLotAuto) || "M".equalsIgnoreCase(genLotAuto) )
								{
									if ( !pordType.equals(jobWorkType) && !pordType.equals(subContractType) )
									{
										//retString = gbf_updlotnowithqcno(as_tranid,ls_itemcode,ls_lineno,ls_lotno_rcp,ls_qcno)
										if ( retString != null && retString.trim().length() > 0 )
										{
											break;
										}
									}
									else
									{
										if ( "Y".equals(genLotSubctr) ) 
										{
											lotNoRcp = qcNo;
											//retString = gbf_updlotnowithqcno(as_tranid,ls_itemcode,ls_lineno,ls_lotno_rcp,ls_qcno)
											if ( retString != null && retString.trim().length() > 0 )
											{
												break;
											}
										}
									}
								}
							}
							//ls_qord_no[upperbound(ls_qord_no[]) + 1] = ls_qcno;
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
						if (retString != null && retString.trim().length() > 0)
						{
							break;
						}
					}
					else if ( "L".equalsIgnoreCase(qcType) && "Y".equalsIgnoreCase(qcReqdSite) )
					{
						sql = "SELECT (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE BATCH_NO END) AS BATCH_NO, "
							+ " LOC_CODE, UNIT__STD, "
							+ " CASE WHEN (SUM(CASE WHEN (((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + "
							+ " (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * "
							+ " (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) IS NULL "
							+ " THEN 0 ELSE (((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + "
							+ " ( CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * "  //bug fixing done by kunal on 29/10/12 add bracket
							+ " (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) END)) IS NULL THEN 0"
							+ " ELSE (SUM(CASE WHEN (((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + "
							+ " (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * "
							+ " (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) IS NULL THEN "
							+ " 0 ELSE ( ((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + "  //bug fixing done by kunal on 29/10/12 add bracket
							+ " (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * "
							+ " (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END))END)) END, "
							+ " MIN(LINE_NO), MFG_DATE ,LOT_NO, "
							+ " ( CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END), "
							+ " sum(case when excess_short_qty is null then 0 else excess_short_qty end) as excess_short_qty ,sum((case when quantity__stduom is null then 0 else quantity__stduom end)) as quantity__stduom,supp_code__mnfr " //added by kunal on 5/11/12
							+ " FROM PORCPDET  WHERE TRAN_ID = ? "
							+ " AND ITEM_CODE = ? "
							+ " GROUP BY (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE BATCH_NO END), "
							+ " LOC_CODE, UNIT__STD, (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END), "
							+ " MFG_DATE ,LOT_NO,supp_code__mnfr "
							+ " ORDER BY MIN(LINE_NO) ASC  ";	
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, tranId);
						pstmt1.setString(2, itemCode);
						rs1 = pstmt1.executeQuery();
						while ( rs1.next() )
						{
							batchNo = checkNull(rs1.getString(1));
							locCode = checkNull(rs1.getString(2));
							unit = checkNull(rs1.getString(3));  
						    qty	= rs1.getDouble(4); 
							lineNo = checkNull(rs1.getString(5));
							
							spec = checkNull(rs1.getString(8));
							mfgDate = rs1.getTimestamp(6); 
							lotNoRcp = checkNull(rs1.getString(7)); 
							//added by kunal on 5/11/12 start  as per Pravin Sali Sir instruction  
							excessShortQty	= rs1.getDouble("excess_short_qty"); //added by Kunal on 5/11/12
							quantityStduom	= rs1.getDouble("quantity__stduom"); //added by Kunal on 5/11/12
							System.out.println("excessShortQt ="+excessShortQty);
							System.out.println("quantityStduom ="+quantityStduom);
						    if(excessShortQty == 0)
						    {
						    	System.out.println("qty get from qtystduom");
						    	qty = quantityStduom;
						    }
						   //added by kunal on 5/11/12 end  as per Pravin Sali Sir instruction
						    suppCodeMnfr = checkNull(rs1.getString("supp_code__mnfr"));//added by Kunal on 8/11/12
						    System.out.println("suppCodeMnfr = "+suppCodeMnfr);

							sql = "SELECT PURC_ORDER, LINE_NO__ORD, EXPIRY_DATE	, LOT_SL , LINE_NO, MFG_DATE FROM PORCPDET WHERE		   TRAN_ID = ? AND ITEM_CODE = ? AND LOC_CODE = ? AND (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE               BATCH_NO END) = ?";
							pstmt2 = conn.prepareStatement(sql);
							pstmt2.setString(1, tranId);
							pstmt2.setString(2, itemCode);
							pstmt2.setString(3, locCode);
							pstmt2.setString(4, batchNo);
							rs2 = pstmt2.executeQuery();
							if ( rs2.next() )
							{
								purcOrder = checkNull(rs2.getString("PURC_ORDER"));
								lineNoOrd = checkNull(rs2.getString("LINE_NO__ORD"));
								expiryDate = rs2.getTimestamp("EXPIRY_DATE");
								lotSl = checkNull(rs2.getString("LOT_SL"));
								rcpLine = checkNull(rs2.getString("LINE_NO"));
								mfgDate = rs2.getTimestamp("MFG_DATE");
							}
							pstmt2.close();
							pstmt2 = null;
							rs2.close();
							rs2 = null;

							sql = "SELECT EMP_CODE__QCAPRV FROM PORDDET WHERE PURC_ORDER = ? AND LINE_NO = ?";
							pstmt2 = conn.prepareStatement(sql);
							pstmt2.setString(1, purcOrder);
							pstmt2.setString(2, lineNoOrd);
							rs2 = pstmt2.executeQuery();
							if ( rs2.next() )
							{
								empCodeQcaprv = checkNull(rs2.getString("EMP_CODE__QCAPRV"));
							}
							pstmt2.close();
							pstmt2 = null;
							rs2.close();
							rs2 = null;
							
							poRcpDetSpec = spec; 

							xmlValues = "";
							xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
							xmlValues = xmlValues + "<Header></Header>";
							xmlValues = xmlValues + "<Detail1>";
							xmlValues = xmlValues +	"<qorder_no></qorder_no>";
							xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
							xmlValues = xmlValues + "<qorder_date>" + getCurrdateAppFormat() + "</qorder_date>";
							xmlValues = xmlValues + "<qorder_type>I</qorder_type>";
							xmlValues = xmlValues + "<lot_no>" + lotNoRcp + "</lot_no>";
							xmlValues = xmlValues + "<item_ser>" + itemSer + "</item_ser>";
							xmlValues = xmlValues + "<porcp_no>" + tranId + "</porcp_no>";
							xmlValues = xmlValues + "<porcp_line_no>" + rcpLine + "</porcp_line_no>";
							xmlValues = xmlValues +"</Detail1></Root>";
							System.out.println("xmlValues  :["+xmlValues+"]");
							TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
							qcNo = tg.generateTranSeqID("QC-ORD", "qorder_no", key, conn);
							System.out.println("qcNo2="+qcNo);
							
							if ( "ERROR".equals(qcNo) )
							{
								retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
								break;
							}

							if ( retString.trim().length() > 0 )
							{
								break;
							}
							else
							{
								sql = "SELECT LOC_CODE__APRV, LOC_CODE__REJ FROM SITEITEM WHERE  SITE_CODE = ?  AND  ITEM_CODE = ? ";
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, siteCode);
								pstmt2.setString(2, itemCode);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									aprv = checkNull(rs2.getString("LOC_CODE__APRV"));
									rej = checkNull(rs2.getString("LOC_CODE__REJ"));
								}
								pstmt2.close();
								pstmt2 = null;
								rs2.close();
								rs2 = null;

								
								if ( "Y".equalsIgnoreCase(genLotAuto) || "M".equalsIgnoreCase(genLotAuto) )
								{
									if ( (pordType != jobWorkType) && ( pordType != subContractType) )
									{
										lotNoRcp = qcNo;
									}
									else
									{
										if ( genLotSubctr.equalsIgnoreCase("Y") )
										{
											lotNoRcp = qcNo;
										}
									}
								}
								
								passedQty  = qty - qtySample;

								if ( spec == null || spec.length() == 0 )
								{
									sql = "SELECT (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END) AS SPEC_REF FROM SITEITEM WHERE			   SITE_CODE = ? AND    ITEM_CODE = ? "; 	
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, siteCode);
									pstmt2.setString(2, itemCode);
									rs2 = pstmt2.executeQuery();
									if ( rs2.next() )
									{
										spec =  checkNull(rs2.getString(1));
									}
									pstmt2.close();
									pstmt2 = null;
									rs2.close();
									rs2 = null;
								}
								
								if ( dbName.equalsIgnoreCase("db2") )
								{
									if ( itemCode.length() == 0 )
									{
										itemCode = null;
									}
									if ( siteCode.trim().length() == 0 )
									{
										siteCode = null;
									}
									if ( locCode.length() == 0 )
									{
										locCode = null;
									}
									if ( aprv.length() == 0 )
									{
										aprv = null;
									}
									if ( rej.length() == 0 )
									{
										rej = null;
									}
									if ( empCodeQcaprv.length() == 0 )
									{
										empCodeQcaprv = null;
									}
									if ( unit.length() == 0 )
									{
										unit = null;
									}
									if ( qcNo.length() == 0 )
									{
										qcNo = null;
									}
								}

								sql = "INSERT INTO QC_ORDER( QORDER_NO, QORDER_TYPE, QORDER_DATE, SITE_CODE ,ITEM_CODE, ROUTE_CODE,		   QUANTITY, QTY_PASSED ,"
									+" QTY_REJECTED	, START_DATE , DUE_DATE	, PORCP_NO , PORCP_LINE_NO, LOT_NO,	LOT_SL , CHG_DATE ,	CHG_USER, CHG_TERM	,"
									+" LOC_CODE,	QTY_SAMPLE, STATUS,	UNIT, QC_CREATE_TYPE ,  BATCH_NO, EXPIRY_DATE, "
									+" LOC_CODE__APRV , LOC_CODE__REJ , UNIT__SAMPLE, LOT_NO__NEW ,	RETEST_DATE  ,	EMP_CODE__QCAPRV, "
									+" SPEC_REF, ITEM_CODE__NEW , MFG_DATE, EMP_CODE,SUPP_CODE,SUPP_CODE__MFG ) " 
									+" VALUES(  ?,?,?,?,?,?,?,? ,?	," //bug fixing done by kunal on 29/10/12 
									+" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
									+" ?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
								pstmtInsert = conn.prepareStatement(sql);
								pstmtInsert.setString(1, qcNo);
								pstmtInsert.setString(2, "I");
								pstmtInsert.setTimestamp(3, tranDate);
								pstmtInsert.setString(4, siteCode);
								pstmtInsert.setString(5, itemCode);
								pstmtInsert.setString(6, null);
								pstmtInsert.setDouble(7, qty);
								double qtypass =  qty - qtySample;
								//pstmtInsert.setDouble(8, qty - qtySample);
								pstmtInsert.setDouble(8, qtypass);
								pstmtInsert.setDouble(9, 0);
								pstmtInsert.setTimestamp(10, tranDate);
								pstmtInsert.setTimestamp(11, qcDueDate);
								pstmtInsert.setString(12, tranId);
								pstmtInsert.setString(13, rcpLine);
								pstmtInsert.setString(14, lotNoRcp);
								pstmtInsert.setString(15, lotSl);
								pstmtInsert.setTimestamp(16, tranDate);
								pstmtInsert.setString(17, userId);
								pstmtInsert.setString(18, termId);
								pstmtInsert.setString(19, locCode);
								pstmtInsert.setDouble(20, qtySample);
								pstmtInsert.setString(21, "U");
								pstmtInsert.setString(22, unit);
								pstmtInsert.setString(23, "A");
								pstmtInsert.setString(24, batchNo);
								pstmtInsert.setTimestamp(25, expiryDate);
								pstmtInsert.setString(26, aprv);
								pstmtInsert.setString(27, rej);
								pstmtInsert.setString(28, unit);
								pstmtInsert.setString(29, lotNoRcp);
								pstmtInsert.setTimestamp(30, retestDt);
								pstmtInsert.setString(31, empCodeQcaprv);
								pstmtInsert.setString(32, spec);
								pstmtInsert.setString(33, itemCode);
								pstmtInsert.setTimestamp(34, mfgDate);
								pstmtInsert.setString(35, emp);
								pstmtInsert.setString(36, suppCode); //added by Kunal on 8/11/12 as per Pravin Sali intruction 
								pstmtInsert.setString(37, suppCodeMnfr);//added by Kunal on 8/11/12 as per Pravin Sali intruction
								updCnt = pstmtInsert.executeUpdate();
								pstmtInsert.close();
								pstmtInsert = null;
								
								spec = poRcpDetSpec;
								
								if ( "Y".equalsIgnoreCase(genLotAuto) || "M".equalsIgnoreCase(genLotAuto) )
								{
									if ( mfgDate == null )
									{
										mfgDate = today;
									}

									if ( !pordType.equals(jobWorkType) && !pordType.equals(subContractType) )
									{
										sql = "UPDATE PORCPDET SET 	LOT_NO = ?, LOT_SL = ? WHERE TRAN_ID = ? AND ITEM_CODE = ? AND		   LOC_CODE  = ? AND (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE BATCH_NO END) = ? AND (CASE		   WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END)	= ? AND (CASE WHEN MFG_DATE IS NULL		   THEN ? ELSE MFG_DATE END) = ? ";
										pstmtUpd = conn.prepareStatement(sql);
										pstmtUpd.setString(1, lotNoRcp);
										pstmtUpd.setString(2, lotSl);
										pstmtUpd.setString(3, tranId);
										pstmtUpd.setString(4, itemCode);
										pstmtUpd.setString(5, locCode);
										pstmtUpd.setString(6, batchNo);
										pstmtUpd.setString(7, spec);
										pstmtUpd.setTimestamp(8, today);
										pstmtUpd.setTimestamp(9, mfgDate);
										updCnt = pstmtUpd.executeUpdate();
										pstmtUpd.close();
										pstmtUpd = null;
									}
									else
									{
										if ( genLotSubctr.equalsIgnoreCase("Y") )
										{
											sql = "UPDATE PORCPDET SET   LOT_NO = ?, LOT_SL = ? WHERE TRAN_ID = ? AND ITEM_CODE = ? AND		   LOC_CODE  = ? AND (CASE WHEN BATCH_NO IS NULL THEN ' ' ELSE BATCH_NO END) = ? AND		   (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END)	= ?	 AND (CASE WHEN			   MFG_DATE IS NULL THEN ? ELSE MFG_DATE END) 	= ? ";
											pstmtUpd = conn.prepareStatement(sql);
											pstmtUpd.setString(1, lotNoRcp);
											pstmtUpd.setString(2, lotSl);
											pstmtUpd.setString(3, tranId);
											pstmtUpd.setString(4, itemCode);
											pstmtUpd.setString(5, locCode);
											pstmtUpd.setString(6, batchNo);
											pstmtUpd.setString(7, spec);
											pstmtUpd.setTimestamp(8, today);
											pstmtUpd.setTimestamp(9, mfgDate);
											updCnt = pstmtUpd.executeUpdate();
											pstmtUpd.close();
											pstmtUpd = null;
										}
									}
								}
							}
							//ls_qord_no[upperbound(ls_qord_no[]) + 1] = ls_qcno;
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						if (retString != null && retString.trim().length() > 0)
						{
							break;
						}
					}
					else if ( "I".equalsIgnoreCase(qcType) && "Y".equalsIgnoreCase(qcReqdSite) )
					{
						sql = "SELECT LOC_CODE , (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END) AS SPEC_REF, CASE WHEN (SUM(CASE WHEN		   (((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0		   ELSE EXCESS_SHORT_QTY END)) * (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) IS		   NULL THEN 0 ELSE (((CASE WHEN QUANTITY IS NULL THEN 0 ELSE QUANTITY END) + (CASE WHEN EXCESS_SHORT_QTY		   IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE				   CONV__QTY_STDUOM END)) END)) IS NULL THEN 0 ELSE (SUM(CASE WHEN (((CASE WHEN QUANTITY IS NULL THEN 0		   ELSE QUANTITY END) + (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END)) * (CASE WHEN		   CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) IS NULL THEN 0 ELSE (((CASE WHEN QUANTITY IS		   NULL THEN 0 ELSE QUANTITY END) + (CASE WHEN EXCESS_SHORT_QTY IS NULL THEN 0 ELSE EXCESS_SHORT_QTY END))		   * (CASE WHEN CONV__QTY_STDUOM IS NULL THEN 1 ELSE CONV__QTY_STDUOM END)) END)) END ,sum(case when excess_short_qty is null then 0 else excess_short_qty end) as excess_short_qty ,sum((case when quantity__stduom is null then 0 else quantity__stduom end)) as quantity__stduom ,supp_code__mnfr FROM PORCPDET WHERE		   TRAN_ID = ? AND ITEM_CODE = ? GROUP BY LOC_CODE , (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF		   END),supp_code__mnfr ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, tranId);
						pstmt1.setString(2, itemCode);
						rs1 =  pstmt1.executeQuery();
						while ( rs1.next() )
						{
							locCode = checkNull(rs1.getString("LOC_CODE"));
							spec = checkNull(rs1.getString("SPEC_REF"));
							qty = rs1.getDouble(3);
							//added by kunal on 5/11/12 start  as per Pravin Sali Sir instruction
							excessShortQty	= rs1.getDouble("excess_short_qty"); //added by Kunal on 5/11/12
							quantityStduom	= rs1.getDouble("quantity__stduom"); //added by Kunal on 5/11/12
						    if(excessShortQty == 0)
						    {
						    	System.out.println("qty get from qty stduom");
						    	qty = quantityStduom;
						    }
						   //added by kunal on 5/11/12 end  as per Pravin Sali Sir instruction
						    suppCodeMnfr = checkNull(rs1.getString("supp_code__mnfr"));//added by Kunal on 8/11/12
						    System.out.println("suppCodeMnfr = "+suppCodeMnfr);
							//tranDate = getCurrdateAppFormat(); 
							xmlValues = "";
							xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
							xmlValues = xmlValues + "<Header></Header>";
							xmlValues = xmlValues + "<Detail1>";
							xmlValues = xmlValues +	"<qorder_no></qorder_no>";
							xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
							xmlValues = xmlValues + "<qorder_date>" + getCurrdateAppFormat() + "</qorder_date>";
							xmlValues = xmlValues + "<qorder_type>I</qorder_type>";
							xmlValues = xmlValues + "<item_ser>" + itemSer + "</item_ser>";
							xmlValues = xmlValues + "<porcp_no>" + tranId + "</porcp_no>";
							xmlValues = xmlValues +"</Detail1></Root>";
							System.out.println("xmlValues  :["+xmlValues+"]");
							TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
							qcNo = tg.generateTranSeqID("QC-ORD", "qorder_no", key, conn);
							System.out.println("qcNo3="+qcNo);
							
							if ( "ERROR".equals(qcNo) )
							{
								retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
								break;
							}
							if ( retString.trim().length() == 0 )
							{
								sql = "SELECT LOC_CODE__APRV , LOC_CODE__REJ FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ? ";
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, siteCode);
								pstmt2.setString(2, itemCode);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									 aprv = checkNull(rs2.getString("LOC_CODE__APRV"));
									 rej = checkNull(rs2.getString("LOC_CODE__REJ"));
								}
								rs2.close();
								rs2 = null;
								pstmt2.close();
								pstmt2 = null;

								sql = "SELECT UNIT FROM ITEM WHERE ITEM_CODE = ?";
								pstmt2 = conn.prepareStatement(sql);
								pstmt2.setString(1, itemCode);
								rs2 = pstmt2.executeQuery();
								if ( rs2.next() )
								{
									 unit = checkNull(rs2.getString("UNIT"));
								}
								rs2.close();
								rs2 = null;
								pstmt2.close();
								pstmt2 = null;
								
								lineNo = null;
								//ls_lotno_rcp =  space(15)
								//ls_lotsl = space(5)
								batchNo = null; 
								expiryDate = null;
								retestDt = null;
								
								if ( spec == null || spec.length() == 0 )
								{
									sql= "SELECT (CASE WHEN SPEC_REF IS NULL THEN ' ' ELSE SPEC_REF END) AS SPEC_REF FROM   SITEITEM WHERE			  SITE_CODE = ? AND  ITEM_CODE = ? "; 			   
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, siteCode);
									pstmt2.setString(2, itemCode);	
									rs2 = pstmt2.executeQuery();
									if ( rs2.next() )
									{
										spec =  checkNull(rs2.getString("SPEC_REF"));
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;
								}
								
								if ( dbName.equalsIgnoreCase("db2") )
								{
									if ( itemCode.length() == 0 )
									{
										itemCode = null;
									}
									if ( siteCode.length() == 0 )
									{
										siteCode = null;
									}
									if ( locCode.length() == 0 )
									{
										locCode = null;
									}
									if ( aprv .length() == 0 )
									{
										aprv = null;
									}
									if ( rej.length() == 0)
									{
										rej = null;
									}
									if ( empCodeQcaprv.length() == 0)
									{
										empCodeQcaprv = null;
									}
									if ( unit.length() == 0)
									{
										unit = null;
									}
									if ( qcNo.length() == 0 )
									{
										qcNo = null;
									}
								}
								
								sql = "INSERT INTO QC_ORDER (QORDER_NO,	QORDER_TYPE	, QORDER_DATE ,	SITE_CODE ,	ITEM_CODE, ROUTE_CODE		   , QUANTITY	, QTY_PASSED , QTY_REJECTED, START_DATE	, DUE_DATE,	REL_DATE, PORCP_NO	,			   PORCP_LINE_NO , LOT_NO	, LOT_SL , CHG_DATE	, CHG_USER , CHG_TERM ,	LOC_CODE , QTY_SAMPLE ,	         STATUS,	UNIT, QC_CREATE_TYPE , BATCH_NO	, EXPIRY_DATE , LOC_CODE__APRV , LOC_CODE__REJ ,             UNIT__SAMPLE ,	LOT_NO__NEW	, RETEST_DATE ,	SPEC_REF , ITEM_CODE__NEW ,	EMP_CODE,SUPP_CODE,SUPP_CODE__MFG) VALUES ( ?         ,?, ? , ? , ?, ? , ?, ? , ?, ? , ? , ? , ?	, ? , ? , ? , ? , ? , ?	, ? , ? , ? , ? , ? , ? ,        ? , ? , ? , ? , ? , ? , ? ,	? , ?,?,?)";
								pstmtInsert = conn.prepareStatement(sql);
								pstmtInsert.setString(1, qcNo);
								pstmtInsert.setString(2, "I");
								pstmtInsert.setTimestamp(3, tranDate);
								pstmtInsert.setString(4, siteCode);
								pstmtInsert.setString(5, itemCode);
								pstmtInsert.setString(6, null);
								pstmtInsert.setDouble(7, qty);
								pstmtInsert.setDouble(8, qty - qtySample);
								pstmtInsert.setDouble(9, 0);
								pstmtInsert.setTimestamp(10, tranDate);
								pstmtInsert.setTimestamp(11, qcDueDate);
								pstmtInsert.setTimestamp(12, tranDate);
								pstmtInsert.setString(13, tranId);
								pstmtInsert.setString(14, lineNo);
								pstmtInsert.setString(15, lotNoRcp);
								pstmtInsert.setString(16, lotSl);
								pstmtInsert.setTimestamp(17, tranDate);
								pstmtInsert.setString(18, userId);
								pstmtInsert.setString(19, termId);
								pstmtInsert.setString(20, locCode);
								pstmtInsert.setDouble(21, qtySample);
								pstmtInsert.setString(22, "U");
								pstmtInsert.setString(23, unit);
								pstmtInsert.setString(24, "A");
								pstmtInsert.setString(25, batchNo);
								pstmtInsert.setTimestamp(26, expiryDate);
								pstmtInsert.setString(27, aprv);
								pstmtInsert.setString(28, rej);
								pstmtInsert.setString(29, unit);
								pstmtInsert.setString(30, lotNoRcp);
								pstmtInsert.setTimestamp(31, retestDt);
								pstmtInsert.setString(32, spec);
								pstmtInsert.setString(33, itemCode);
								pstmtInsert.setString(34, emp);
								pstmtInsert.setString(35, suppCode); //added by Kunal on 8/11/12 as per Pravin Sali intruction 
								pstmtInsert.setString(36, suppCodeMnfr);//added by Kunal on 8/11/12 as per Pravin Sali intruction
								updCnt = pstmtInsert.executeUpdate();
								pstmtInsert.close();
								pstmtInsert = null;
							}

							//ls_qord_no[upperbound(ls_qord_no[]) + 1] = ls_qcno;
						}
						pstmt1.close();
						pstmt1 = null;
						rs1.close();
						rs1 = null;
						if (retString != null && retString.trim().length() > 0)
						{
							break;
						}
					}

				}// end of main while
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;					
				if (retString != null && retString.trim().length() > 0)
				{
					return retString;
				}
			}
			else if ( "N".equalsIgnoreCase(qcReqd) )
			{
				genLotAuto = distCommon.getDisparams("999999", "GENERATE_LOT_NO_AUTO", conn);
				//lotNoManualSite = distCommon.getDisparams("999999" , "LOT_NO_MANUAL_SITE", conn); //Gulzar - 25/11/11

				if ( genLotAuto == null )
				{
					genLotAuto = "N";
				}
				genLotAuto = genLotAuto.trim();

				if ( "Y".equalsIgnoreCase(genLotAuto) )
				{
					lotNoManualSite = distCommon.getDisparams("999999" , "LOT_NO_MANUAL_SITE", conn); //Gulzar - 25/11/11
					siteString = lotNoManualSite;
					while ( siteString.trim().length() > 0 )
					{
						sitecode = distCommon.getToken( siteString , ",");
						if ( sitecode.trim() == siteCode.trim() )
						{
							break;
						}
					}
					
					sql = "SELECT DISTINCT A.ITEM_CODE, (CASE WHEN A.BATCH_NO IS NULL THEN ' ' ELSE A.BATCH_NO END) AS BATCH_NO, A.LOC_CODE FROM		   PORCPDET A, ITEM B  WHERE A.ITEM_CODE = B.ITEM_CODE AND TRAN_ID = ? AND (CASE WHEN B.STK_OPT IS NULL THEN		   '0' ELSE B.STK_OPT END) = '2' AND (A.LOT_NO IS NULL OR TRIM(A.LOT_NO) IS NULL OR LENGTH(TRIM(A.LOT_NO)) = 0		   ) ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);
					rs = pstmtSql.executeQuery();

					while ( rs.next() )
					{
						itemCode = checkNull(rs.getString("ITEM_CODE"));
						batchNo = checkNull(rs.getString("BATCH_NO"));
						locCode = checkNull(rs.getString("LOC_CODE"));
						
						//tranDate = getCurrdateAppFormat(); 
						xmlValues = "";
						xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
						xmlValues = xmlValues + "<Header></Header>";
						xmlValues = xmlValues + "<Detail1>";
						xmlValues = xmlValues +	"<qorder_no></qorder_no>";
						xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
						xmlValues = xmlValues + "<qorder_date>" + getCurrdateAppFormat() + "</qorder_date>";
						xmlValues = xmlValues + "<qorder_type>I</qorder_type>";
						xmlValues = xmlValues + "<item_ser>" + itemSer + "</item_ser>";
						xmlValues = xmlValues + "<porcp_no>" + tranId + "</porcp_no>";
						xmlValues = xmlValues + "<porcp_line_no>" + lineNo + "</porcp_line_no>";
						xmlValues = xmlValues +"</Detail1></Root>";
						System.out.println("xmlValues  :["+xmlValues+"]");
						TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
						qcNo = tg.generateTranSeqID("QC-ORD", "qorder_no", key, conn);
						System.out.println("qcNo4="+qcNo);
						
						if ( "ERROR".equals(qcNo) )
						{
							retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
							break;
						}

						sql = "UPDATE PORCPDET SET LOT_NO = ? WHERE TRAN_ID = ? AND ITEM_CODE = ? AND (CASE WHEN BATCH_NO IS NULL THEN		   ' ' ELSE BATCH_NO END) = ? AND LOC_CODE = ? AND (LOT_NO IS NULL OR LENGTH(TRIM(LOT_NO)) = 0)" ;
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setString(1, qcNo);
						pstmtUpd.setString(2, tranId);
						pstmtUpd.setString(3, itemCode);
						pstmtUpd.setString(4, batchNo);
						pstmtUpd.setString(5, locCode);
						updCnt = pstmtUpd.executeUpdate();
						if ( pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;
						}
					}
					pstmtSql.close();
					pstmtSql = null;
					rs.close();
					rs = null;
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Finally block, closing resultset and prepared statement variables..............");
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				if( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}	
				if( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;					
				}	
				if( pstmt2 != null)
				{
					pstmt2.close();
					pstmt2 = null;					
				}	
				if( rs != null)
				{
					rs.close();
					rs = null;					
				}	
				if( rs1 != null)
				{
					rs1.close();
					rs1 = null;					
				}	
				if( rs2 != null)
				{
					rs2.close();
					rs2 = null;					
				}	
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;	
	}

	/*public String autoBackflush( String tranId, Connection conn) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rs = null;

		String purcOrder = "";
		String retString = "";
		String suppCode	= "";
		String pordSite = "";
		String ordType = "";
		String channelPartner = "";
		String disLink = "";
		String sordSite = "";
		String distOrder = "";
		String distIss[] = {};
		String distRcp = "";
		String itemSer = "";
		String itemCode = "";
		String bomCode = "";
		String locCode[] = {};
		String unit = "";
		String itemCodeDet = "";
		String unitDet = "";
		String lotNo[] = {};
		String lotSl[] = {};
		String trNoFld = "";
		String keyStr = "";
		String exit = "";
		String locHdr = "";
		String lotNo[] = {};
		String saleOrder = "";
		String custCode = "";
		String custCodeDlv = "";
		String tranCode = "";
		String stanCode = "";
		String dlv1 = "";
		String dlv2 = "";
		String dlvCity = "";
		String dlvPin = "";
		String dlvCnt = "";
		String currCode = "";
		String currCodeFrt = "";
		String stanCodeInit = "";
		String orderType = "";
		String transMode = "";
		String remarks = "";
		String remarks2 = "";
		String remarks3	= "";
		String currCodeIns = "";
		String dlv3 = "";
		String nullVar = null;
		String gpSer = "";
		String itemDes = "";
		String lineNoSord = "";
		String siteCodeDet = "";
		String taxCl = "";
		String taxCh = "";
		String taxEn = "";
		String unitStd = "";
		String packIns = "";
		String stUnit = "";
		String itemCodeOrd = "";
		String expLev = "";
		String jobWorkType = "";
		String subContractType = "";
		String linePoRcpDet = "";
		String lineNo = "";
		String invStat = "";
		String allocated = "";
		String acctCodeDr = "";
		String cctrCodeDr = "";
		String sordType = "";
		String grade = "";
		String acctCodeCr = "";
		String cctrCodeCr = "";
		String status = "";
		String lot = "";
		String distIssTid = "";
		String cctrGl = "";
		String acctGl = "";
		String purcOrderDet[] = {};
		String lineNoOrd[] = {};
		String itemCodeRcp[] = {};
		String bomCodeOrd[] = {};
		String unitOrd[] = {};
		String itemCodeRcpNoBom[] = {};
		String unitOrdNoBom[] = {};
		String lnoPoRcpDet = "";
		String purcOrderTemp = "";
		String lnoOrdTemp = "";
		String itemCodeTemp = "";
		String bomCodeTemp = "";
		String unitTemp = "";
		String lnnoPoRcpDet[] = {};
		String locDetNoBom = "";
		String func = "";
		String totFunc = "";
		String lotSl = "";
		String sql = "";
		String itemStru = "";
		String convrReqd = "";

		double quantity = 0.0;
		double qtyDet = 0.0;
		double rate[] = {};
		double stkQty[] = {};	
		double updQty = 0.0;
		double amountHdr = 0.0;
		double conQtyStd = 0.0;
		double qtyDesp = 0.0;
		double frtAmt = 0.0;
		double exchRateIns = 0.0;
		double insAmt = 0.0;
		double discountDes = 0.0;		
		double despatchedQty = 0.0;
		double orderQty = 0.0;
		double pendingQty = 0.0;
		double minusQty = 0.0;
		double balQty = 0.0;
		double noArt = 0.0;
		double qtyAlloc = 0.0;
		double quantityStduom = 0.0;
		double amtHdrDesp = 0.0;
		double totalAdditionalCost = 0.0;
		double additionalCost = 0.0;
		double qtyChk = 0.0;
		double grWgh = 0.0;
		double netWgh = 0.0;
		double tareWgh = 0.0;
		double hGrWgh = 0.0;
		double hNetWgh = 0.0;
		double hTareWgh = 0.0;
		double taxAmt = 0.0;
		double chkQty = 0.0;
		double quantityNoBom = 0.0;
		double quantityPoRdDet[] = {};
		double quantityPoRcpDet[] = {};
		double amountHdrNoBom = 0.0;
		double quantityPoRdTemp = 0.0;
		double quantityPoRcpTemp = 0.0;
		double convQty = 0.0;
		double rateStd = 0.0;
		double rateClg = 0.0;
		double diffRate = 0.0;
		double totRate = 0.0;
		double itemRate = 0.0;
		double rateNoBom = 0.0;
		double convRate = 0.0;
		double exchRate = 0.0;
		double exchRateFrt = 0.0;
		double conv = 0.0;

		long line = 0;
		long ctr = 0;
		long row = 0;
		long rtn = 0;
		long setRow = 1;
		long count = 0;
		long currRow = 0;
		long currRowNoBom = 0;

		int iRow = 0;
		int currRow = 0;
		int hdrRow = 0;
		int lnoPoRcpDet = 0;
		int updCnt = 0;

		Date today = new Date();
		Date ordDate = null;
		Date mfgDt = null;
		Date expDt = null;

		boolean noBom = true; 
				
		try
		{

			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			DistCommon distCommon = new DistCommon();
			GenericUtility genericUtility = GenericUtility.getInstance();
		    DateFormat dtFormat = new SimpleDateFormat(genericUtility.getApplDateTimeFormat());
			dbName = CommonConstants.DB_NAME;
			
			jobWorkType = distCommon.getDisparams("999999", "JOBWORK_TYPE", conn);
			if ( jobWorkType == null )
			{
				jobWorkType = "";		
			}

			subContractType = distCommon.getDisparams("999999", "SUBCONTRACT_TYPE", conn);
			if ( subContractType == null )
			{
				subContractType = "";
			}

			convrReqd = distCommon.getDisparams("999999", "RBKFSH_CONV_REQD", conn);
			if ( convrReqd == null )
			{
				convrReqd = "Y";
			}
			
			//transer='R-BFS'
			trNoFld = "tranId";

			sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, "W_RECEIPT_BACKFLUSH");
			rs = pstmtSql.executeQuery();
			if ( pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;
			}
			if ( rs.next() )
			{
				keyStr = checkNull(rs.getString("KEY_STRING"));
			}
			else
			{	
				sql = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, "GENERAL");
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					keyStr = checkNull(rs.getString("KEY_STRING"));
				}
			}
			if ( pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}

			sql = "SELECT PURC_ORDER FROM PORCP WHERE TRAN_ID = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				purcOrder = checkNull(rs.getString("PURC_ORDER"));
			}
			if ( pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}

			sql = "SELECT SUPP_CODE	, SITE_CODE__DLV, PORD_TYPE FROM  PORDER WHERE PURC_ORDER = ? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, purcOrder);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				suppCode = checkNull(rs.getString("SUPP_CODE"));
				pordSite = checkNull(rs.getString("SITE_CODE__DLV"));
				ordType = checkNull(rs.getString("PORD_TYPE"));
			}
			if ( pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}

			if ( !ordType.equalsIgnoreCase("P") )
			{	
				sql = "SELECT (CASE WHEN CHANNEL_PARTNER IS NULL THEN 'N' ELSE CHANNEL_PARTNER END), DIS_LINK, SITE_CODE__CH FROM		   SITE_SUPPLIER WHERE SITE_CODE = ? AND SUPP_CODE = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, pordSite);
				pstmtSql.setString(2, suppCode);
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs.next() )
				{
					channelPartner = checkNull(rs.getString("CHANNEL_PARTNER"));
					disLink = checkNull(rs.getString("DIS_LINK"));
					sordSite = checkNull(rs.getString("SITE_CODE__CH"));
				}
				else if ( !rs.next() || channelPartner == null )
				{
					sql = "SELECT (CASE WHEN CHANNEL_PARTNER IS NULL THEN 'N' ELSE CHANNEL_PARTNER END), DIS_LINK, SITE_CODE FROM 		   SUPPLIER WHERE SUPP_CODE = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, suppCode);
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						channelPartner = checkNull(rs.getString("CHANNEL_PARTNER"));
						disLink = checkNull(rs.getString("DIS_LINK"));
						sordSite = checkNull(rs.getString("SITE_CODE"));
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs != null )
					{
						rs.close();
						rs = null;
					}
				}
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
			}
			
			if ( channelPartner.equalsIgnoreCase("Y") && disLink.equalsIgnoreCase("A") )
			{
				sql = "SELECT DIST_ORDER FROM  DISTORDER WHERE PURC_ORDER IN (SELECT DISTINCT PURC_ORDER FROM PORCPDET WHERE TRAN_ID =		   ?) AND CONFIRMED = 'Y'";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null )
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				while ( rs.next() )
				{
					distOrder = checkNull(rs.getString("DIST_ORDER"));
					row = row + 1;
					
					ResultSet rs1;
					sql = "SELECT TRAN_ID FROM DISTORD_ISS WHERE  DIST_ORDER = ? AND CONFIRMED = 'Y' ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, distOrder);		
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						distIss[row] = checkNull(rs1.getString("TRAN_ID")); 
					}
					else if( !rs.next() || distIss[row] == null )
					{
						retString = itmDBAccessLocal.getErrorString("","VTDIST13","");
						break;
					}
					if ( pstmtSql != null )
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT TRAN_ID FROM DISTORD_RCP WHERE DIST_ORDER = ? AND TRAN_ID__ISS = ? AND CONFIRMED = 'Y' ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, distOrder);	
					pstmtSql.setString(2, distIss[row]);	
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						distRcp = checkNull(rs1.getString("TRAN_ID"));
					}
					else if ( !rs1.next() || distRcp == null )
					{
						retString = itmDBAccessLocal.getErrorString("","VTPORCP","");
						break;
					}
					if ( pstmtSql != null )
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				}
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
		
				func = distCommon.getDisparams("999999", "TRANSFER_LOTSL", conn);
				if ( func == null )
				{
					retString = itmDBAccessLocal.getErrorString("","VTFUNCERR","");
					break;
				}

				sql = "SELECT ITEM_SER FROM PORDER WHERE PURC_ORDER = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, purcOrder);	
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					itemSer = checkNull(rs.getString("ITEM_SER"));
				}
				if ( pstmtSql != null )
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}

				sql = "SELECT PORCPDET.PURC_ORDER, PORCPDET.LINE_NO__ORD , PORCPDET.ITEM_CODE ,	PORCPDET.QUANTITY, PORDDET.QUANTITY	,		  PORDDET.BOM_CODE , PORCPDET.UNIT, PORCPDET.LINE_NO FROM PORCPDET, PORDDET WHERE PORCPDET.TRAN_ID = ? AND			  PORCPDET.PURC_ORDER 	= PORDDET.PURC_ORDER AND PORCPDET.LINE_NO__ORD = PORDDET.LINE_NO AND PORDDET.BOM_CODE IS	      NOT NULL";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);	
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				while ( rs.next() )
				{
					purcOrderTemp = checkNull(rs.getString("PURC_ORDER"));
					lnoOrdTemp = checkNull(rs.getString("LINE_NO__ORD"));
					itemCodeTemp = checkNull(rs.getString("ITEM_CODE"));
					quantityPoRcpTemp = rs.getDouble("QUANTITY");
					quantityPoRdTemp =	rs.getDouble("QUANTITY");
					bomCodeTemp = checkNull(rs.getString("BOM_CODE"));
					unitTemp = checkNull(rs.getString("UNIT"));
					lnoPoRcpDet = checkNull(rs.getString("LINE_NO"));
					
					lnoPoRcpDet = Integer.parseInt(lnoPoRcpDet);
					purcOrderDet[lnoPoRcpDet] = purcOrderTemp;
					lineNoOrd[lnoPoRcpDet] = lnoOrdTemp;
					itemCodeRcp[lnoPoRcpDet]  = itemCodeTemp;
					quantityPoRdDet[lnoPoRcpDet]  = quantityPoRdTemp;
					quantityPoRcpDet[lnoPoRcpDet] = quantityPoRcpTemp;
					bomCodeOrd[lnoPoRcpDet] = bomCodeTemp;
					unitOrd[lnoPoRcpDet] = unitTemp;
					lnnoPoRcpDet[lnoPoRcpDet] = lnoPoRcpDet;

					currRow = 0;
					hdrRow = 0;

					ResultSet rs1;
					sql = "SELECT MFG_DATE	, EXPIRY_DATE , GROSS_WEIGHT, NET_WEIGHT , TARE_WEIGHT FROM PORCPDET WHERE TRAN_ID 	= ?		   AND 	ITEM_CODE = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId);	
					pstmtSql.setString(2, itemCodeRcp[lnoPoRcpDet]);	
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						mfgDt = dtFormat.parse(rs1.getString("MFG_DATE"));
						expDt = dtFormat.parse(rs1.getString("EXPIRY_DATE"));
						grWgh = rs1.getDouble("GROSS_WEIGHT");
						netWgh = rs1.getDouble("NET_WEIGHT");
						tareWgh = rs1.getDouble("TARE_WEIGHT");
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT LOC_CODE__APRV FROM  SITEITEM WHERE ITEM_CODE = ? AND SITE_CODE = ? "; 
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, itemCodeRcp[lnoPoRcpDet] );	
					pstmtSql.setString(2,  sordSite );	
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1.next() )
					{
						locHdr =  checkNull(rs.getString("LOC_CODE__APRV"));
					}
					else
					{
						sql = "SELECT LOC_CODE FROM ITEM WHERE  ITEM_CODE = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, itemCodeRcp[lnoPoRcpDet] );
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							locHdr = checkNull(rs1.getString("LOC_CODE"));
						}
						else
						{
							retString = itmDBAccessLocal.getErrorString("","VMLOC4","");
							break;
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
					
					//ls_cctrgl = nvo_desp.gbf_acct_detr(ls_item_code_rcp[li_lno_porcpdet],'','CONVGL') 
					acctGl = distCommon.getToken( cctrGl, "~t");

					if ( acctGl.trim().length() == 0 )
					{
						acctGl = null;
					}
					if ( cctrGl.trim().length() == 0 )
					{
						cctrGl = null;
					}

					sql = "INSERT INTO RECEIPT_BACKFLUSH(TRAN_DATE, SITE_CODE, ITEM_CODE, BOM_CODE, QUANTITY, UNIT, BACKFLUSH_TYPE,		   LOT_SL, QC_REQD, LOC_CODE, CHG_DATE, MFG_DATE, EXP_DATE, CHG_USER, CHG_TERM, ACCT_CODE__CONV_GL,				   CCTR_CODE__CONV_GL) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setDate(1, today );
					pstmtSql.setString(2, sordSite );
					pstmtSql.setString(3, itemCodeRcp[lnoPoRcpDet] );
					pstmtSql.setString(4, bomCodeOrd[lnoPoRcpDet] );
					pstmtSql.setDouble(5, quantityPoRcpDet[lnoPoRcpDet] );
					pstmtSql.setString(6, unitOrd[lnoPoRcpDet] );
					pstmtSql.setString(7, "J" );
					pstmtSql.setString(8, "1S" );
					pstmtSql.setString(9, "N" );
					pstmtSql.setString(10, locHdr );
					pstmtSql.setDate(11, today );
					pstmtSql.setDate(12, mfgDt );
					pstmtSql.setDate(13, expDt );
					pstmtSql.setString(14, userId );
					pstmtSql.setString(15, termId );
					pstmtSql.setString(16, acctGl );
					pstmtSql.setString(17, cctrGl );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
				
					//tranId  = gf_gen_key_nvo(rcp_backflush_hdr,'R-BFS', 'tran_id', keystr)
					if ( tranId.equalsIgnoreCase("ERROR") )
					{
						retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
						break;
					}

					//lotNo[lnoPoRcpDet] = gf_gen_key_nvo(rcp_backflush_hdr,'QC-ORD', 'lot_no', keystr)
					if ( tranId.equalsIgnoreCase("ERROR") )
					{
						retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
						break;
					}

					sql = "INSERT INTO RECEIPT_BACKFLUSH(TRAN_ID, LOT_NO) VALUES(?, ?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId );
					pstmtSql.setString(2, lotNo[lnoPoRcpDet] );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}
					
					sql = "INSERT INTO WORKORDER_BILL(SITE_CODE, BOM_CODE, QUANTITY, WORK_ORDER, EXP_LEV) VALUES(?,?,?,?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, sordSite );
					pstmtSql.setString(2, bomCodeOrd[lnoPoRcpDet] );
					pstmtSql.setDouble(3, quantityPoRcpDet[lnoPoRcpDet] );
					pstmtSql.setString(4, "XYZ" );
					pstmtSql.setString(5, "1" );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}

					//rtn = nvo_bom.gbf_explode_bom(lds_bom,ls_bom_code_ord[li_lno_porcpdet],'1.','B','XYZ')
					if ( rtn == -1 )
					{
						retString = itmDBAccessLocal.getErrorString("","Failed to explode Bill of Material.","");
						break;
					}
					
					sql = "SELECT COUNT(*) AS COUNT FROM WORKORDER_BILL";
					pstmtSql = conn.prepareStatement(sql);
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						row = rs1.getInt("COUNT");
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}

					if ( row > 0 )
					{
						for( ctr= 2; ctr <= row ; ctr++ )
						{
							sql = "SELECT ITEM_CODE, QUANTITY FROM WORKORDER_BILL";
							pstmtSql = conn.prepareStatement(sql);
							rs1 = pstmtSql.executeQuery();
							if ( rs1.next() )
							{
								itemCodeDet = checkNull(rs1.getString("ITEM_CODE"));
								qtyDet = rs1.getDouble("QUANTITY");
							}
							if ( pstmtSql != null)
							{
								pstmtSql.close();
								pstmtSql = null;
							}
							if ( rs1 != null )
							{
								rs1.close();
								rs1 = null;
							}
			
							setRow = 1;	
							for ( iRow = 1 ; iRow <= distIss.Length; iRow++ )
							{
								distIssTid = distIss[iRow];

								//datastore ld_func
								//ld_func = create datastore
								//ld_func.dataobject = 'd_dynamic_function'
								//ls_tot_func = ls_func + '("' + ls_dist_iss_tid + '","' + 'ABC' + '")'
								//ld_func.modify("c_function.expression='" + ls_tot_func + "'")
								//ld_func.insertrow(0)
								//ls_lotsl = string(ld_func.object.c_function[1])
								//if left(trim(ls_lotsl),5) = 'ERROR' then
								//	ls_errcode = 'VTFUNCERR'
								//	exit
								//end if
								
								sql = " SELECT (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN			    STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY IS NULL           THEN 0 ELSE STOCK.HOLD_QTY END), STOCK.UNIT, STOCK.LOC_CODE, STOCK.LOT_NO, STOCK.LOT_SL,               STOCK.RATE FROM STOCK,LOCATION,INVSTAT, DISTORD_ISSDET WHERE STOCK.LOC_CODE = LOCATION.LOC_CODE         AND (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN                     STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY IS NULL           THEN 0 ELSE STOCK.HOLD_QTY END) > 0 AND LOCATION.INV_STAT = INVSTAT.INV_STAT AND                       STOCK.SITE_CODE = ? AND STOCK.ITEM_CODE = ? AND STOCK.LOT_NO    = DISTORD_ISSDET.LOT_NO";
								pstmtSql = conn.prepareStatement(sql);
								pstmtSql.setString(1, sordSite );
								pstmtSql.setString(2, itemCodeDet );
								rs1 = pstmtSql.executeQuery();
								if ( rs1.next() )
								{
									lotSl = checkNull(rs1.getString("LOT_SL"));
								}
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;

								}

								if ( lotSl.equalsIgnoreCase(distIss[iRow]) )
								{
									sql = "SELECT (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN		   STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY IS            NULL THEN 0 ELSE STOCK.HOLD_QTY END), STOCK.UNIT, STOCK.LOC_CODE, STOCK.LOT_NO,                    STOCK.LOT_SL, STOCK.RATE FROM STOCK,LOCATION,INVSTAT, DISTORD_ISSDET WHERE STOCK.LOC_CODE =        LOCATION.LOC_CODE  AND (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) -         (CASE WHEN  STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN                  STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END) > 0 AND LOCATION.INV_STAT =                 INVSTAT.INV_STAT AND STOCK.SITE_CODE = ? AND STOCK.ITEM_CODE = ? AND STOCK.LOT_NO    =             DISTORD_ISSDET.LOT_NO AND STOCK.LOT_SL = '" + distIss[iRow] + "'";
								}
								else
								{
									sql = "SELECT (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN		   STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY IS		   NULL THEN 0 ELSE STOCK.HOLD_QTY END), STOCK.UNIT, STOCK.LOC_CODE, STOCK.LOT_NO,                    STOCK.LOT_SL, STOCK.RATE FROM STOCK,LOCATION,INVSTAT, DISTORD_ISSDET WHERE STOCK.LOC_CODE =        LOCATION.LOC_CODE  AND (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) -        (CASE WHEN  STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN                   STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END) > 0 AND LOCATION.INV_STAT =                 INVSTAT.INV_STAT AND STOCK.SITE_CODE = ? AND STOCK.ITEM_CODE = ? AND STOCK.LOT_NO    =             DISTORD_ISSDET.LOT_NO AND STOCK.LOT_SL  = DISTORD_ISSDET.LOT_SL "; 
								}

								sql = sql + "AND DISTORD_ISSDET.TRAN_ID = '" + distIssTid + "'" + " AND (CASE WHEN STOCK.ALLOC_QTY IS		  NULL THEN 0 ELSE STOCK.ALLOC_QTY END) >= 0  AND INVSTAT.AVAILABLE = 'Y' ORDER BY					  STOCK.PARTIAL_USED, (CASE WHEN STOCK.EXP_DATE IS NULL THEN STOCK.CREA_DATE ELSE STOCK.EXP_DATE		  END),  STOCK.CREA_DATE, STOCK.LOT_NO, STOCK.LOT_SL";
								pstmtSql = conn.prepareStatement(sql);
								rs1 = pstmtSql.executeQuery();
								while ( rs1.next() )
								{
									stkQty[setRow] = rs1.getDouble(1);
									unitDet = checkNull(rs1.getString("UNIT"));
									locCode[setRow] = checkNull(rs1.getString("LOC_CODE"));
									lotNo[setRow] = checkNull(rs1.getString("LOT_NO"));
									lotSl[setRow] = checkNull(rs1.getString("LOT_SL"));
									rate[setRow] = rs1.getDouble("RATE"); 
									chkQty = chkQty  + stkQty[setRow];
									setRow = setRow + 1;		
								}
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;
								}
							}

							if ( qtyDet > chkQty )
							{
								sql = "SELECT (CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN			   STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY IS NULL		   THEN 0 ELSE STOCK.HOLD_QTY END), STOCK.UNIT, STOCK.LOC_CODE, STOCK.LOT_NO, STOCK.LOT_SL,			   STOCK.RATE FROM STOCK, LOCATION, INVSTAT WHERE STOCK.LOC_CODE = LOCATION.LOC_CODE AND ((CASE		   WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN STOCK.ALLOC_QTY IS		   NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - (CASE WHEN STOCK.HOLD_QTY  IS NULL THEN 0 ELSE			   STOCK.HOLD_QTY  END))  > 0 AND LOCATION.INV_STAT = INVSTAT.INV_STAT AND STOCK.SITE_CODE = ?		   AND STOCK.ITEM_CODE = ? AND (CASE WHEN STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END)		   >= 0 AND INVSTAT.AVAILABLE = ? AND STOCK.LOT_NO NOT IN (SELECT LOT_NO FROM DISTORD_ISSDET WHERE		   DISTORD_ISSDET.TRAN_ID = ? AND  DISTORD_ISSDET.ITEM_CODE = ? )	ORDER BY STOCK.PARTIAL_USED,         (CASE WHEN	STOCK.EXP_DATE IS NULL THEN STOCK.CREA_DATE ELSE STOCK.EXP_DATE END),				   STOCK.CREA_DATE, STOCK.LOT_NO, STOCK.LOT_SL ";
								pstmtSql = conn.prepareStatement(sql);
								pstmtSql.setString(1, sordSite );
								pstmtSql.setString(2, itemCodeDet );
								pstmtSql.setString(3, "Y" );
								pstmtSql.setString(4, distIssTid );
								pstmtSql.setString(5, itemCodeDet );
								rs1 = pstmtSql.executeQuery();
								while ( rs1.next() )
								{
									setRow = setRow + 1;
									stkQty[setRow] = rs1.getDouble(1);
									unitDet = checkNull(rs1.getString("UNIT"));
									locCode[setRow] = checkNull(rs1.getString("LOC_CODE"));
									lotNo[setRow] = checkNull(rs1.getString("LOT_NO"));
									lotSl[setRow] = checkNull(rs1.getString("LOT_SL"));
									rate[setRow] = rs1.getDouble("RATE");
								}
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;
								}
							}
							
							exit = "";
							iRow = 0;

							for ( iRow = 1; iRow <= setRow - 1 ; iRow++ )
							{
								if ( stkQty[iRow] = qtyDet ) 
								{
									updQty = qtyDet; 
									exit = "exit";
								}
								else if ( stkQty[iRow] > qtyDet )
								{
									updQty = qtyDet; 
									exit = "exit";
								}
								else if ( stkQty[iRow] < qtyDet )
								{
									updQty = stkQty[iRow];
									qtyDet = qtyDet - stkQty[iRow];
								}

								sql = "INSERT INTO RECEIPT_BACKFLUSH_DET(TRAN_ID, LINE_NO, ITEM_CODE, SITE_CODE, QUANTITY, UNIT,		   UNIT__DOC, LOC_CODE, LOT_NO, LOT_SL, TAX_AMT, DISCOUNT, RATE, AMOUNT, NET_AMT)					   VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
								pstmtSql = conn.prepareStatement(sql);
								pstmtSql.setString(1, tranId );
								pstmtSql.setString(2, currRow);
								pstmtSql.setString(3, itemCodeDet );
								pstmtSql.setString(4, sordSite );
								pstmtSql.setDouble(5, updQty );
								pstmtSql.setString(6, unitDet );
								pstmtSql.setString(7, unitOrd[lnoPoRcpDet] );
								pstmtSql.setString(8, locCode[iRow] );
								pstmtSql.setString(9, lotNo[iRow] );
								pstmtSql.setString(10, lotSl[iRow] );
								pstmtSql.setString(11, "0");
								pstmtSql.setString(12, "0");
								pstmtSql.setDouble(13, rate[iRow] );
								pstmtSql.setDouble(14, updQty * rate[iRow] );
								pstmtSql.setDouble(15, updQty * rate[iRow] );
								rs1 = pstmtSql.executeQuery();
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;
								}
								
								if ( convrReqd.equalsIgnoreCase("Y"))
								{
									conv = 0;
									//convQty = gf_conv_qty_fact(ls_unit_det, ls_unit_ord[li_lno_porcpdet], ls_item_code_det, lc_upd_qty, lc_conv)
									conv = 0;
									//lc_convrate	= gf_conv_qty_fact(ls_unit_det, ls_unit_ord[li_lno_porcpdet], ls_item_code_det, lc_rate[li_row], lc_conv)

									sql = "INSERT INTO RECEIPT_BACKFLUSH_DET(CONV_QTY_DOC, QTY_DOC, RATE_DOC) VALUES(?,?,?)";
									pstmtSql = conn.prepareStatement(sql);
									pstmtSql.setDouble(1, conv );
									pstmtSql.setDouble(2, convQty );
									pstmtSql.setDouble(3, convRate );
									rs1 = pstmtSql.executeQuery();
									if ( pstmtSql != null)
									{
										pstmtSql.close();
										pstmtSql = null;
									}
									if ( rs1 != null )
									{
										rs1.close();
										rs1 = null;
									}

									amountHdr = amountHdr + (convQty * convRate);
									if ( lc_convqty = -999999999 )
									{
										retString = itmDBAccessLocal.getErrorString("","VMUCNV1","");
										break;
									}
								}
								else
								{
									amountHdr = amountHdr + ( updQty * rate[iRow] );
								}
								
								//lstr_allocate.tran_date	= datetime(today())
								//lstr_allocate.ref_ser	= 'R-BFS'
								//lstr_allocate.ref_id		= ls_tran_id
								//lstr_allocate.ref_line	= ls_line_no 
								//lstr_allocate.site_code	= ls_sordsite
								//lstr_allocate.item_code	= ls_item_code_det
								//lstr_allocate.loc_code	= ls_loc_code[li_row]
								//lstr_allocate.lot_no		= ls_lot_no[li_row]
								//lstr_allocate.lot_sl		= ls_lot_sl[li_row]
								//lstr_allocate.alloc_qty	= lc_upd_qty
								//lstr_allocate.chg_user	= userid
								//lstr_allocate.chg_term	= termid
								//lstr_allocate.chg_win	= "W_RECEIPT_BACKFLUSH"	
								

								sql = "SELECT ITEM_STRU FROM ITEM WHERE ITEM_CODE = ?";
								pstmtSql = conn.prepareStatement(sql);
								pstmtSql.setString(1, itemCodeDet );
								rs1 = pstmtSql.executeQuery();
								if ( rs1.next() )
								{
									itemStru = checkNull(rs1.getString("ITEM_STRU"));
								}
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;
								}

								if ( !itemStru.equalsIgnoreCase("R") )
								{
									//ls_errcode = lnvo_allocate.gbf_upd_alloc_trace(lstr_allocate)
								}
								if ( retString != null && retString.trim().length() > 0 ) 
								{
									retString = itmDBAccessLocal.getErrorString("","RETSTRING","");
									break;
								}
								if ( exit.equalsIgnoreCase("exit"))
								{
									break;
								}
							}
							
							if ( retString != null && retString.trim().length() > 0 ) 
							{
								break;
							}
							
							if ( noBom )
							{
								amountHdrNoBom = 0.0;

								sql = "SELECT PORCPDET.PURC_ORDER ,	PORCPDET.LINE_NO__ORD, PORCPDET.ITEM_CODE,	PORCPDET.QUANTITY,            PORDDET.QUANTITY , PORDDET.BOM_CODE, PORCPDET.UNIT , PORCPDET.LINE_NO FROM 	PORCPDET, PORDDET 	   WHERE PORCPDET.TRAN_ID = ? AND	PORCPDET.PURC_ORDER = PORDDET.PURC_ORDER AND 	                      PORCPDET.LINE_NO__ORD = PORDDET.LINE_NO AND	PORDDET.BOM_CODE IS NULL";
								pstmtSql = conn.prepareStatement(sql);
								pstmtSql.setString(1, tranId );
								rs1 = pstmtSql.executeQuery();
								if ( pstmtSql != null)
								{
									pstmtSql.close();
									pstmtSql = null;
								}
								while ( rs1.next() )
								{
									purcOrderTemp =  checkNull(rs1.getString("PURC_ORDER"));
									lnoOrdTemp =  checkNull(rs1.getString("LINE_NO__ORD"));
								    itemCodeTemp =  checkNull(rs1.getString("ITEM_CODE"));
									quantityPoRcpTemp = rs1.getDouble("QUANTITY");
									quantityPoRdTemp = rs1.getDouble("QUANTITY");
									bomCodeTemp =  checkNull(rs1.getString("BOM_CODE"));
									unitTemp =  checkNull(rs1.getString("UNIT"));
									lnoPoRcpDet =  checkNull(rs1.getString("LINE_NO"));

									lnoPoRcpDet = Integer.parseInt(lnoPoRcpDet);
									purcOrderDet[lnoPoRcpDet] = purcOrderTemp;
									lineNoOrd[lnoPoRcpDet] 	= lnoOrdTemp;
									itemCodeRcp[lnoPoRcpDet] = itemCodeTemp;
									quantityPoRdDet[lnoPoRcpDet] = quantityPoRdTemp;
									quantityPoRcpDet[lnoPoRcpDet] = quantityPoRcpTemp;
									bomCodeOrd[lnoPoRcpDet] = bomCodeTemp;
									unitOrd[lnoPoRcpDet] = unitTemp;
									lnnoPoRcpDet[lnoPoRcpDet] = lnoPoRcpDet;
									
									ResultSet rs2;
									sql = "SELECT MFG_DATE,	EXPIRY_DATE, GROSS_WEIGHT, NET_WEIGHT, TARE_WEIGHT FROM  PORCPDET WHERE		   TRAN_ID = ? AND ITEM_CODE = ? ";
									pstmtSql = conn.prepareStatement(sql);
									pstmtSql.setString(1, tranId );
									pstmtSql.setString(2, itemCodeRcp[lnoPoRcpDet] );
									rs2 = pstmtSql.executeQuery();
									if ( rs2.next() )
									{
										mfgDt = dtFormat.parse(rs2.getString("MFG_DATE"));
										expDt = dtFormat.parse(rs2.getString("EXPIRY_DATE"));
										grWgh = rs2.getDouble("GROSS_WEIGHT");
										netWgh = rs2.getDouble("NET_WEIGHT");
										tareWg = rs2.getDouble("TARE_WEIGHT");
									}
									if ( pstmtSql != null)
									{
										pstmtSql.close();
										pstmtSql = null;
									}
									if ( rs2 != null )
									{
										rs2.close();
										rs2 = null;
									}	

									lotNo[lnoPoRcpDet] = "";

									sql = "SELECT COST_RATE FROM ITEM WHERE ITEM_CODE = ? ";
									pstmtSql = conn.prepareStatement(sql);
									pstmtSql.setString(1, itemCodeRcp[lnoPoRcpDet] );
									rs2 = pstmtSql.executeQuery();
									if ( rs2.next() )
									{
										rateNoBom = rs2.getDouble("COST_RATE");
									}
									if ( pstmtSql != null)
									{
										pstmtSql.close();
										pstmtSql = null;
									}
									if ( rs2 != null )
									{
										rs2.close();
										rs2 = null;
									}	

									if ( rateNoBom == null )
									{
										rateNoBom = 0;
									}

									quantityNoBom = 0 - quantityPoRcpDet[lnoPoRcpDet];

									sql = "INSERT INTO RECEIPT_BACKFLUSH_DET(TRAN_ID, LINE_NO ITEM_CODE SITE_CODE QUANTITY UNIT			   LOC_CODE LOT_NO LOT_SL TAX_AMT DISCOUNT RATE AMOUNT NET_AMT)									   VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
									pstmtSql = conn.prepareStatement(sql);
									pstmtSql.setString(1, tranId );
									pstmtSql.setString(2, currRow );
									pstmtSql.setString(3, itemCodeRcp[lnoPoRcpDet] );
									pstmtSql.setString(4, sordSite );
									pstmtSql.setDouble(5, quantityNoBom );
									pstmtSql.setString(6, unitOrd[lnoPoRcpDet] );
									pstmtSql.setString(7, locHdr );
									pstmtSql.setString(8, lotNo[lnoPoRcpDet] );
									pstmtSql.setString(9, "LS" );
									pstmtSql.setString(10, "0" );
									pstmtSql.setString(11, "0" );
									pstmtSql.setDouble(12, rateNoBom );
									pstmtSql.setDouble(13, quantityNoBom * rateNoBom );
									pstmtSql.setDouble(14, quantityNoBom * rateNoBom );
									rs2 = pstmtSql.executeQuery();
									if ( pstmtSql != null)
									{
										pstmtSql.close();
										pstmtSql = null;
									}
									if ( rs2 != null )
									{
										rs2.close();
										rs2 = null;
									}	

									amountHdrNoBom = amountHdrNoBom + (quantityNoBom * rateNoBom);
								}
								if ( rs1 != null )
								{
									rs1.close();
									rs1 = null;
								}
								noBom = false;
							}//end if noBom 
						} // end of for ctr =2;

						if ( retString != null && retString.trim().length() > 0 ) 
						{
							break;
						}

					}// end of row >0
					
					amountHdr = amountHdr + amountHdrNoBom;
					sql = "INSERT INTO RECEIPT_BACKFLUSH(AMOUNT, NET_AMT, INV_VALUE, TAX_AMT, DISCOUNT) VALUES(?,?,?,?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setDouble(1, amountHdr );
					pstmtSql.setDouble(2, amountHdr );
					pstmtSql.setDouble(3, amountHdr );
					pstmtSql.setString(4, "0");
					pstmtSql.setDouble(5, "0" );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null )
					{
						rs1.close();
						rs1 = null;
					}	
	
					amountHdr = 0;
					amountHdrNoBom = 0;

				//	if ( rcp_backflush_hdr.update() = 1 )
				//	{
				//		if rcp_backflush_det.update() = 1 then 
				//				ls_errcode = nvo_bkflush.gbf_retrieve_backflush(ls_tran_id,transer,1)
				//		else
				//			populateerror(9999,'populateerror')
				//			ls_errcode = 'DS000' + string(sqlca.sqldbcode)+'~t'+sqlca.sqlerrtext
				//			ls_errcode = gf_error_location(ls_errcode)
				//			exit
				//		end if
				//	}
				//	else
				//	{
				//		populateerror(9999,'populateerror')
				//		ls_errcode = 'DS000' + string(sqlca.sqldbcode)+'~t'+sqlca.sqlerrtext
				//		ls_errcode = gf_error_location(ls_errcode)
				//		exit
				//	}

				} // end of main while
				
				//transer = 'S-DSP';
				trNoFld = "despId";
				currRow = 0;
				bomCode = "";
				tranId = "";

				sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, "W_DESPATCH" );
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs.next() )
				{
					keyStr = checkNull(rs.getString("KEY_STRING"));
				}
				else
				{
					sql = "SELECT KEY_STRING FROM TRANSETUP  WHERE TRAN_WINDOW = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, "GENERAL" );
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						keyStr = checkNull(rs.getString("KEY_STRING"));
					}
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				sql = "SELECT SALE_ORDER FROM SORDER WHERE CUST_PORD = ? AND CONFIRMED = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, purcorder );
				pstmtSql.setString(2, "Y");
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					saleOrder = checkNull(rs.getString("SALE_ORDER"));
				}
				else if ( !rs.next() || saleOrder == null )
				{
					retString = itmDBAccessLocal.getErrorString("","no rec in sale ord","");
					break;
				}
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs.next() )
				{
					keyStr = checkNull(rs.getString("KEY_STRING"));
				}

				sql = "SELECT ORDER_DATE , CUST_CODE , CUST_CODE__DLV ,	TRAN_CODE , STAN_CODE ,	DLV_ADD1, DLV_ADD2 , DLV_CITY ,			   DLV_PIN, COUNT_CODE__DLV ,	CURR_CODE ,	EXCH_RATE , STAN_CODE__INIT	, ORDER_TYPE, TRANS_MODE ,	                     CURR_CODE__FRT,	EXCH_RATE__FRT	, FRT_AMT ,	CURR_CODE__INS,	EXCH_RATE__INS, INS_AMT, REMARKS, REMARKS2,	             REMARKS3, DLV_ADD3 FROM SORDER WHERE SALE_ORDER = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, saleOrder );	
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					ordDate	= dtFormat.parse(rs.getString("ORDER_DATE"));
					custCode =  checkNull(rs.getString("CUST_CODE"));
					custCodeDlv = checkNull(rs.getString("CUST_CODE__DLV"));
					tranCode = checkNull(rs.getString("TRAN_CODE"));
					stanCode =checkNull(rs.getString("STAN_CODE"));
					dlv1 = checkNull(rs.getString("DLV_ADD1"));
					dlv2 = checkNull(rs.getString("DLV_ADD2"));
					dlvCity = checkNull(rs.getString("DLV_CITY"));
					dlvPin = checkNull(rs.getString("DLV_PIN"));
					dlvCnt = checkNull(rs.getString("COUNT_CODE__DLV"));
					currCode = checkNull(rs.getString("CURR_CODE"));
					exchRate = rs.getDouble("EXCH_RATE");
					stanCodeInit = checkNull(rs.getString("STAN_CODE__INIT"));
					orderType = checkNull(rs.getString("ORDER_TYPE"));
					transMode = checkNull(rs.getString("TRANS_MODE"));
					currCodeFrt = checkNull(rs.getString("CURR_CODE__FRT"));
					exchRateFrt = rs.getDouble("EXCH_RATE__FRT");
					frtAmt = rs.getDouble("FRT_AMT");
					currCodeIns = checkNull(rs.getString("CURR_CODE__INS"));
					exchRateIns = rs.getDouble("EXCH_RATE__INS");
					insAmt = rs.getDouble("INS_AMT");
					remarks = checkNull(rs.getString("REMARKS"));
					remarks2 = checkNull(rs.getString("REMARKS2"));
					remarks3 = checkNull(rs.getString("REMARKS3"));
					dlv3 = checkNull(rs.getString("DLV_ADD3"));
				}
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				sql = "INSERT INTO DESPATCH(DESP_DATE, SITE_CODE, SORD_NO, SORD_DATE, CUST_CODE, CUST_CODE__DLV, EFF_DATE, DLV_ADD1,		   DLV_ADD2, DLV_ADD3, DLV_CITY, DLV_PIN, COUNT_CODE__DLV, CURR_CODE, EXCH_RATE, CURR_CODE__FRT, EXCH_RATE__FRT,		   FREIGHT, CURR_CODE__INS, EXCH_RATE__INS, INSURANCE, REMARKS, REMARKS2, REMARKS3)									   VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDate(1, today );	
				pstmtSql.setString(2, sordSite );	
				pstmtSql.setString(3, saleOrder );	
				pstmtSql.setDate(4, ordDate );	
				pstmtSql.setString(5, custCode );	
				pstmtSql.setString(6, custCodeDlv );	
				pstmtSql.setDate(7, today );	
				pstmtSql.setString(8, dlv1);	
				pstmtSql.setString(9, dlv2 );	
				pstmtSql.setString(10, dlv3 );	
				pstmtSql.setString(11, dlvCity );	
				pstmtSql.setString(12, dlvPin );	
				pstmtSql.setString(13, dlvCnt );	
				pstmtSql.setString(14, currCode );	
				pstmtSql.setDouble(15, exchRate );	
				pstmtSql.setString(16, currCodeFrt );	
				pstmtSql.setDouble(17, exchRateFrt );	
				pstmtSql.setDouble(18, frtAmt );	
				pstmtSql.setString(19, currCodeIns );	
				pstmtSql.setDouble(20, exchRateIns );	
				pstmtSql.setDouble(21, insAmt );	
				pstmtSql.setString(22, remarks );	
				pstmtSql.setString(23, remarks2 );	
				pstmtSql.setString(24, remarks3 );	
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				gpSer = orderType;
				if ( !gpSer.equalsIgnoreCase("E") )
				{
					sql = "INSERT INTO DESPATCH(GP_SER) VALUES(?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, gpSer );
					rs = pstmtSql.executeQuery();
				}
				else
				{
					sql = "INSERT INTO DESPATCH(GP_SER) VALUES(?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, nullVar );
					rs = pstmtSql.executeQuery();
				}
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				sql = "INSERT INTO DESPATCH(STAN_CODE, TRAN_CODE, TRANS_MODE, STAN_CODE__INIT) VALUES(?,?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, stanCode );
				pstmtSql.setString(2, tranCode );
				pstmtSql.setString(3, transMode );
				pstmtSql.setString(4, stanCodeInit );
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}
				
				//ls_tran_id  = gf_gen_key_nvo(desp_hdr,'S-DSP', 'desp_id', keystr);

				if ( tranId.equalsIgnoreCase("ERROR") )
				{
					retString = itmDBAccessLocal.getErrorString("","VTTRANID","");
					break;
				}

				sql = "SELECT ITEM_CODE, LINE_NO FROM PORCPDET WHERE TRAN_ID = ? ";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId );
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				while ( rs.next() )
				{
					itemDes = checkNull(rs.getString("ITEM_CODE"));
					linePoRcpDet = checkNull(rs.getString("LINE_NO"));

					currRow = currRow + 1;
					lnoPoRcpDet = Integer.parseInt(linePoRcpDet);

					ResultSet rs1;
					sql = "SELECT BOM_CODE FROM PORDDET WHERE PURC_ORDER = ? AND ITEM_CODE = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, purcOrder );
					pstmtSql.setString(2, itemCodeRcp[lnoPoRcpDet] );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						bomCode =  checkNull(rs1.getString("BOM_CODE"));
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT LINE_NO, SITE_CODE, TAX_CLASS, TAX_CHAP, TAX_ENV, DISCOUNT, RATE__STDUOM,	RATE__CLG, UNIT__STD, 		   CONV__QTY_STDUOM, UNIT,	PACK_INSTR, (CASE WHEN NO_ART IS NULL THEN 0 ELSE NO_ART END) FROM	SORDDET WHERE		   SALE_ORDER = ? AND ITEM_CODE  = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, saleOrder );
					pstmtSql.setString(2, itemCodeRcp[lnoPoRcpDet] );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						lineNoSord =  checkNull(rs1.getString("LINE_NO"));
						siteCodeDet =  checkNull(rs1.getString("SITE_CODE"));
						taxCl =  checkNull(rs1.getString("TAX_CLASS"));	
						taxCh =  checkNull(rs1.getString("TAX_CHAP"));
						taxEn =	  checkNull(rs1.getString("TAX_ENV"));
						discountDes = rs1.getDouble("DISCOUNT");
						rateStd	=	rs1.getDouble("RATE__STDUOM");
						rateClg = rs1.getDouble("RATE__CLG");
						stUnit = checkNull(rs1.getString("UNIT__STD"));
						conQtyStd = rs1.getDouble("CONV__QTY_STDUOM");
						unitStd = checkNull(rs1.getString("UNIT"));
						packIns = checkNull(rs1.getString("PACK_INSTR"));
						noArt =  rs1.getDouble("NO_ART");
					} 
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT SUM(CASE WHEN QTY_DESP IS NULL THEN 0 ELSE QTY_DESP END), SUM(CASE WHEN QUANTITY IS NULL THEN 0 ELSE		   QUANTITY END)  FROM  SORDITEM WHERE	SALE_ORDER = ? AND	LINE_NO = ? AND LINE_TYPE  <> ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, saleOrder );
					pstmtSql.setString(2, lineNoSord );
					pstmtSql.setString(3, "B" );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						despatchedQty = rs1.getDouble("QTY_DESP");
						orderQty = rs1.getDouble("QUANTITY");
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					
					pendingQty = orderQty - despatchedQty;
					minusQty = 0;

					sql = "SELECT SUM(QUANTITY) FROM DESPATCHDET WHERE	SORD_NO = ? AND DESP_ID = ?  AND LINE_NO__SORD = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, saleOrder );
					pstmtSql.setString(2, tranId );
					pstmtSql.setString(3, lineNoSord );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						minusQty = rs1.getDouble("QUANTITY");
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					if ( minusQty == null )
					{
						minusQty = 0.0;
					}
					if ( pendingQty == null )
					{
						pendingQty = 0.0;
					}

					sql = "INSERT INTO DESPATCHDET(PENDING_QTY) VALUES(?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setDouble(1, pendingQty - minusQty );
					rs1 = pstmtSql.executeQuery();
					balQty = pendingQty - minusQty;
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT ITEM_CODE__ORD, QUANTITY,	EXP_LEV	, QTY_ALLOC FROM SORDITEM WHERE  SALE_ORDER = ? AND	 LINE_NO = ?		   AND	 LINE_TYPE = ? ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, saleOrder );
					pstmtSql.setString(2, lineNoSord );
					pstmtSql.setString(3, "I" );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						itemCodeOrd = checkNull(rs1.getString("ITEM_CODE__ORD"));
						qtyDesp = rs1.getDouble("QUANTITY");
						expLev	= checkNull(rs1.getString("EXP_LEV"));
						qtyAlloc = rs1.getDouble("QTY_ALLOC");
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					qtyDesp = quantityPoRcpDet[lnoPoRcpDet];
					
					//lc_quantity__stduom = gf_conv_qty_fact(ls_unit_std, ls_stunit, ls_item_des, lc_qty_desp, lc_con_qty_std);

					sql = "INSERT INTO DESPATCHDET(DESP_ID, LINE_NO, ITEM_CODE__ORD, QUANTITY__ORD, SORD_NO, LINE_NO__SORD, SITE_CODE,		   EXP_LEV, ITEM_CODE, LOT_NO, LOT_SL, UNIT__STD, CONV__QTY_STDUOM, UNIT, QUANTITY__STDUOM, GROSS_WEIGHT,		   TARE_WEIGHT, NETT_WEIGHT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, tranId );
					pstmtSql.setString(2, currRow );
					pstmtSql.setString(3, itemCodeOrd );
					pstmtSql.setDouble(4, orderQty );
					pstmtSql.setString(5, saleOrder );
					pstmtSql.setString(6, lineNoSord );
					pstmtSql.setString(7, siteCodeDet );
					pstmtSql.setString(8, expLev );
					pstmtSql.setString(9, itemCodeRcp[lnoPoRcpDet]);
					pstmtSql.setString(10, lotNo[lnoPoRcpDet] );
					pstmtSql.setString(11, "IS" );
					pstmtSql.setString(12, stUnit );
					pstmtSql.setDouble(13, conQtyStd );
					pstmtSql.setString(14, unitStd );
					pstmtSql.setDouble(15, quantityStduom );
					pstmtSql.setDouble(16, grWgh );
					pstmtSql.setDouble(17, tareWgh );
					pstmtSql.setDouble(18, netWgh );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
						
					hGrWgh  = hGrWgh + grWgh;
					hNetWgh = hNetWgh + netWgh;
					hTareWgh = hTareWgh + tareWgh;

					if ( bomCode == null  ||  bomCode.length() == 0 )
					{
						sql = "SELECT COST_RATE FROM ITEM WHERE ITEM_CODE = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, itemCodeRcp[lnoPoRcpDet] );	
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							rateNoBom = rs1.getDouble("COST_RATE"); 
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}

						if ( rateNoBom == null )
						{
							rateNoBom = 0.0;
						}

						sql = "INSERT INTO DESPATCHDET(QUANTITY, QUANTITY_REAL, RATE__STDUOM, RATE__CLG, DISCOUNT) VALUES(?,?,?,?,?)";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setDouble(1, qtyDesp * -1 );
						pstmtSql.setDouble(2, qtyDesp * -1 );
						pstmtSql.setDouble(3, rateNoBom );
						pstmtSql.setDouble(4, rateNoBom );
						pstmtSql.setString(5, "0" );
						rs1 = pstmtSql.executeQuery();
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}
					}
					else
					{
						sql = "INSERT INTO DESPATCHDET(QUANTITY, QUANTITY_REAL, RATE__STDUOM, RATE__CLG, DISCOUNT) VALUES(?,?,?,?,?)";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setDouble(1, qtyDesp );
						pstmtSql.setDouble(2, qtyDesp );
						pstmtSql.setDouble(3, rateStd );
						pstmtSql.setDouble(4, rateClg );
						pstmtSql.setDouble(5, discountDes );
						rs1 = pstmtSql.executeQuery();
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}
					}

					sql = "INSERT INTO DESPATCHDET(TAX_CLASS, TAX_CHAP, TAX_ENV, LOC_CODE, PACK_INSTR, NO_ART) VALUES(?,?,?,?,?,?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, taxCl );
					pstmtSql.setString(2, taxCh );
					pstmtSql.setString(3, taxEn );
					pstmtSql.setString(4, locHdr );
					pstmtSql.setString(5, packIns );
					pstmtSql.setDouble(6, noArt );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					
					if ( bomCode == null  || bomCode.length() == 0 )
					{
						sql = "SELECT  DESPATCHDET.RATE__STDUOM FROM DESPATCHDET, ITEM, SORDDET, SORDITEM, DESPATCH WHERE (				   ESPATCHDET.ITEM_CODE = ITEM.ITEM_CODE (+)) AND ( DESPATCHDET.SORD_NO = SORDDET.SALE_ORDER (+)) AND (		   DESPATCHDET.LINE_NO__SORD = SORDDET.LINE_NO (+)) AND ( DESPATCHDET.SORD_NO = SORDITEM.SALE_ORDER (+))		   AND     ( DESPATCHDET.LINE_NO__SORD = SORDITEM.LINE_NO (+)) AND ( DESPATCHDET.EXP_LEV =                        SORDITEM.EXP_LEV (+)) AND     ( DESPATCH.DESP_ID = DESPATCHDET.DESP_ID ) AND ( ( DESPATCHDET.DESP_ID =         ? ) ) ORDER BY DESPATCHDET.LINE_NO ASC ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, trNoFld );
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							itemRate = rs1.getDouble("RATE__STDUOM");
							amtHdrDesp = amtHdrDesp + (qtyDesp * itemRate);
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}
					}
					else
					{

						sqk = "SELECT  DESPATCHDET.RATE__STDUOM FROM DESPATCHDET, ITEM, SORDDET, SORDITEM, DESPATCH WHERE (				   ESPATCHDET.ITEM_CODE = ITEM.ITEM_CODE (+)) AND ( DESPATCHDET.SORD_NO = SORDDET.SALE_ORDER (+)) AND (		   DESPATCHDET.LINE_NO__SORD = SORDDET.LINE_NO (+)) AND ( DESPATCHDET.SORD_NO = SORDITEM.SALE_ORDER (+))		   AND     ( DESPATCHDET.LINE_NO__SORD = SORDITEM.LINE_NO (+)) AND ( DESPATCHDET.EXP_LEV =                        SORDITEM.EXP_LEV (+)) AND     ( DESPATCH.DESP_ID = DESPATCHDET.DESP_ID ) AND ( ( DESPATCHDET.DESP_ID =         ? ) ) ORDER BY DESPATCHDET.LINE_NO ASC ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, trNoFld );
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							itemRate = rs1.getDouble("RATE__STDUOM");
							amtHdrDesp = amtHdrDesp + ( (qtyDesp * itemRate) - (qtyDesp * itemRate *  discountDes / 100) ); 
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}
					 }

					 additionalCost = 0;
					 if ( ordType.equalsIgnoreCase(jobWorkType) || ordType.equalsIgnoreCase(subContractType) )
					{
						sql = "SELECT RATE FROM	STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND	LOC_CODE  = ? AND LOT_NO = ? AND 	         LOT_SL = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, itemCodeRcp[lnoPoRcpDet] );
						pstmtSql.setString(2, siteCodeDet );
						pstmtSql.setString(3, locHdr );
						pstmtSql.setString(4, lotNo[lnoPoRcpDet] );
						pstmtSql.setString(5, "1S" );
						rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							additionalCost = rs1.getDouble("RATE");	
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}
					}
					
					if ( additionalCost == null )
					{
						additionalCost = 0.0;
					}

					additionalCost = additionalCost * qtyDesp;
					totalAdditionalCost = totalAdditionalCost + additionalCost;

					sql = " UPDATE PORCPDET SET ADDITIONAL_COST = ADDITIONAL_COST + ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
					pstmtUpd = conn.prepareStatement(sql);
					pstmtUpd.setDouble(1, additionalCost);
					pstmtUpd.setString(2, tranId);
					pstmtUpd.setString(3, linePoRcpDet);
					updCnt = pstmtUpd.executeUpdate();
					if ( updCnt != 1)
					{
						retString =  itmDBAccessLocal.getErrorString("","UPDCNTNOT1","");
						break;
					}
					if ( pstmtUpd != null)
					{
						pstmtUpd.close();
						pstmtUpd = null;
					}
					
					
					//ls_line_no = right('   ' + string(li_curr_row),3)
					//lstr_allocate.tran_date	= datetime(today())
					//lstr_allocate.ref_ser	= 'S-DSP'
					//lstr_allocate.ref_id		= ls_tran_id
					//lstr_allocate.ref_line	= ls_line_no 
					//lstr_allocate.site_code	= ls_sordsite
					//lstr_allocate.item_code	= ls_item_code_ord
					//lstr_allocate.loc_code	= ls_loc_hdr
					//lstr_allocate.lot_no		= ls_lotno[li_lno_porcpdet]				
					//lstr_allocate.lot_sl		= '1S'
					//lstr_allocate.alloc_qty	= lc_qty_desp
					//lstr_allocate.chg_user	= userid
					//lstr_allocate.chg_term	= termid
					//lstr_allocate.chg_win	= "W_DESPATCH"

					//ls_errcode = lnvo_allocate.gbf_upd_alloc_trace(lstr_allocate)
					
					if ( retString != null && retString.trim().length() > 0) 
					{
						retString =  itmDBAccessLocal.getErrorString("","VTRIDNORED","");
						break;
					}

					//lc_tax_amt = gf_calc_tax_ds(desp_det, lds_tax, 'S-DSP', ls_tran_id, ldt_today, "rate__stduom", "quantity__stduom",0,ls_curr_code);
					if  ( taxAmt == -999999999 )
					{
						retString =  itmDBAccessLocal.getErrorString("","Error calculating tax","");
						break;
					}
					if ( taxAmt == null )
					{
						taxAmt = 0.0;
					}

					sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, locHdr );
					rs1 = pstmtSql.executeQuery();
					if ( rs1.next() )
					{
						invStat = rs1.getString("INV_STAT");	
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					sql = "SELECT COUNT(*) AS CNT FROM SORDALLOC WHERE SALE_ORDER = ? AND LINE_NO = ?  AND	EXP_LEV = ? AND				   ITEM_CODE__ORD = ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ?  AND LOC_CODE = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, saleOrder );
					pstmtSql.setString(2, lineNoSord );
					pstmtSql.setString(3, expLev );
					pstmtSql.setString(4, itemCodeOrd );
					pstmtSql.setString(5, itemCodeRcp[lnoPoRcpDet] );
					pstmtSql.setString(6, lotNo[lnoPoRcpDet] );
					pstmtSql.setString(7, "1S" );
					pstmtSql.setString(8, locHdr );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1.next() )
					{
						count = getLong("CNT");
					
						if ( count == 0 )
						{
							allocated = "N";
						}
						else
						{
							allocated = "Y";
						}
					
						if ( allocated.equalsIgnoreCase("Y") )
						{
							sql = "UPDATE SORDALLOC SET STATUS = 'D' WHERE  SALE_ORDER 	 = ? AND LINE_NO = ? AND EXP_LEV = ? 			   AND ITEM_CODE__ORD = ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ?";
							pstmtUpd = conn.prepareStatement(sql);
							pstmtUpd.setString(1, saleOrder);
							pstmtUpd.setString(2, lineNoSord);
							pstmtUpd.setString(3, expLev);
							pstmtUpd.setString(4, itemCodeOrd);
							pstmtUpd.setString(5, itemCodeRcp[lnoPoRcpDet]);
							pstmtUpd.setString(6, lotNo[lnoPoRcpDet] );
							pstmtUpd.setString(7, "1S" );
							pstmtUpd.setString(8, locHdr );
							updCnt = pstmtUpd.executeUpdate();
							if ( pstmtUpd != null)
							{
								pstmtUpd.close();
								pstmtUpd = null;
							}
						}

						sql = "UPDATE SORDITEM SET QTY_DESP = QTY_DESP + ?, DATE_DESP = ? WHERE	SALE_ORDER = ? AND 	LINE_NO = ? AND		   SITE_CODE  = ? AND EXP_LEV = ?";	
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, qtyDesp);
						pstmtUpd.setDate(2, today);
						pstmtUpd.setString(3, saleOrder);
						pstmtUpd.setString(4, lineNoSord);
						pstmtUpd.setString(5, siteCodeDet);
						pstmtUpd.setString(6, expLev);
						updCnt = pstmtUpd.executeUpdate();
						if ( pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;
						}

						sql = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC - ? WHERE	SALE_ORDER = ? AND LINE_NO = ? AND SITE_CODE  = ?        AND	EXP_LEV = ? AND QTY_ALLOC > ? ";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, qtyDesp);
						pstmtUpd.setString(2, saleorder);
						pstmtUpd.setString(3, lineNoSord);
						pstmtUpd.setString(4, siteCodeDet);
						pstmtUpd.setString(5, expLev);
						pstmtUpd.setString(6, "0");
						updCnt = pstmtUpd.executeUpdate();
						if ( pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;
						}

						sql = "SELECT SUM(QUANTITY) - SUM(QTY_DESP) FROM SORDITEM WHERE SALE_ORDER = ? AND LINE_TYPE = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, saleOrder );
						pstmtSql.setString(2, "I" );
					    rs1 = pstmtSql.executeQuery();
						if ( rs1.next() )
						{
							qtyChk  = rs1.getDouble(1);
						}
						if ( pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;
						}
						if ( rs1 != null)
						{
							rs1.close();
							rs1 = null;
						}

						if ( qtyChk == null )
						{
							qtyChk = 0.0;
						}

						if ( qtyChk > 0 )
						{
							status = "P";
						}
						else
						{
							status = "C";
						}

						sql = " UPDATE SORDER SET ALLOC_FLAG  = ?, STATUS = ?, STATUS_DATE = ? WHERE SALE_ORDER = ?";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setString(1, "Y");
						pstmtUpd.setString(2, status);
						pstmtUpd.setDate(3, today);
						pstmtUpd.setString(4, saleOrder);
						updCnt = pstmtUpd.executeUpdate();
						if ( pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;
						}
					}
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}

					sql = "INSERT INTO DESPATCHDET(TAX_AMT) VALUES(?)";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setDouble(1, taxAmt );
					rs1 = pstmtSql.executeQuery();
					if ( pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					if ( rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
						
					amtHdrDesp = amtHdrDesp + taxAmt;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				sql = "INSERT INTO DESPATCH(TOT_VALUE, GROSS_WEIGHT, TARE_WEIGHT, NETT_WEIGHT) VALUES (?,?,?,?)";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setDouble(1, amtHdrDesp );
				pstmtSql.setDouble(2, hGrWgh );
				pstmtSql.setDouble(3, hTareWgh );
				pstmtSql.setDouble(4, hNetWgh );
				rs = pstmtSql.executeQuery();
				if ( pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;
				}
				if ( rs != null)
				{
					rs.close();
					rs = null;
				}

				sql = " UPDATE PORCP SET TOTAL_ADDITIONAL_COST = TOTAL_ADDITIONAL_COST + ? WHERE TRAN_ID = ? ";
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setDouble(1, totalAdditionalCost );
				pstmtUpd.setString(2, tranId );
				updCnt = pstmtUpd.executeUpdate();
				if ( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;
				}
			}
		}	
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				if( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}	
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;	
	}*/

    /*public String stockAllocatePur( String purRcpt, String tranType, Connection con) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rs = null;
		
		double Quantity = 0.0;
		double quantityAlloc = 0.0;
		double convQty = 0.0;
		double lotsQty = 0.0;
		double lotsSamQty = 0.0;

		long count = 0;
		long soCnt = 0;
		long i = 0;
		long lineNo = 0;
		long cnt= 0;

		String purcOrd = "";
		String saleOrder = "";
		String status = "";
		String grade = "";
		String siteCodeMfg = "";
		String itemCode = "";
		String lotNo = "";
		String lotSl = "";
		String locCode = "";
		String retString = "";
		String unit = "";
		String unitStd = "";
		String lineSord = "";
		String itemRef = "";
		String expLev = "";
		String siteCode = "";
		String itemOrd = "";
		String linepOrder = "";
		String qcReqd = "";
		String poRcpNo = "";
		String linePoRcp = "";
		//String tranType = "";
		String qcCreateType = "";
		String sql = "";

		int updCnt = 0;

		Date allocDate = null;
		Date expDate = null;
		Date mfgDate = null;

		try
		{
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			GenericUtility genericUtility = GenericUtility.getInstance();
		    DateFormat dtFormat = new SimpleDateFormat(genericUtility.getApplDateTimeFormat());
			dbName = CommonConstants.DB_NAME;
			
			if ( tranType.equalsIgnoreCase("P") ) 
			{
				allocDate = new Date();
				int rowCount = 0;
				sql = "SELECT COUNT(*) AS COUNT FROM PORCPDET";
				pstmtSql = conn.prepareStatement(sql);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					rowCount = rs.getInt("COUNT");
				}
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}	
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}

				for ( i = 1; i <= rowCount; i++)
				{
					sql = "SELECT PORCPDET.ITEM_CODE FROM PORCPDET, ITEM ITEM_A, ITEM ITEM_B, ACCOUNTS, SPECIFICATION, PORCP WHERE (		   PORCPDET.ITEM_CODE__MFG = ITEM_B.ITEM_CODE (+)) AND  ( PORCPDET.ACCT_CODE__DR = ACCOUNTS.ACCT_CODE (+)) AND        ( PORCPDET.SPEC_REF = SPECIFICATION.SPEC_REF (+)) AND ( PORCPDET.ITEM_CODE = ITEM_A.ITEM_CODE ) AND (		   PORCPDET.TRAN_ID = PORCP.TRAN_ID ) AND ( ( PORCPDET.TRAN_ID = ? ) ) ORDER BY PORCPDET.LINE_NO ASC ";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, purRcpt );
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						itemCode = checkNull(rs.getString("ITEM_CODE"));
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}	
					if ( rs != null )
					{
						rs.close();
						rs = null;
					}	

					sql = "SELECT QC_REQD FROM  ITEM WHERE ITEM_CODE = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setString(1, itemCode );
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						qcReqd = checkNull(rs.getString("QC_REQD"));
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;					
					}	
					if ( rs != null )
					{
						rs.close();
						rs = null;
					}

					if ( qcReqd.equalsIgnoreCase("N") )
					{
						sql = "SELECT  PORCPDET.LINE_NO, PORCPDET.PURC_ORDER, PORCPDET.QUANTITY, PORCPDET.LOC_CODE, PORCPDET.LOT_NO,          PORCPDET.LOT_SL, PORCPDET.LINE_NO__ORD FROM PORCPDET, ITEM ITEM_A, ITEM ITEM_B, ACCOUNTS,                      SPECIFICATION, PORCP WHERE ( PORCPDET.ITEM_CODE__MFG = ITEM_B.ITEM_CODE (+)) AND (						   PORCPDET.ACCT_CODE__DR = ACCOUNTS.ACCT_CODE (+)) AND ( PORCPDET.SPEC_REF = SPECIFICATION.SPEC_REF (+))         AND ( PORCPDET.ITEM_CODE = ITEM_A.ITEM_CODE ) AND ( PORCPDET.TRAN_ID = PORCP.TRAN_ID ) AND ( (			   PORCPDET.TRAN_ID = ? ) )  ORDER BY PORCPDET.LINE_NO ASC";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, purRcpt );
						rs = pstmtSql.executeQuery();	
						if ( rs.next() )
						{
							linePoRcp = checkNull(rs.getString("LINE_NO"));
							purcOrd = checkNull(rs.getString("PURC_ORDER"));
							lotNo  = checkNull(rs.getString("LOT_NO"));
							lotSl = checkNull(rs.getString("LOT_SL"));
							locCode = checkNull(rs.getString("LOC_CODE"));
							quantityAlloc = rs.getDouble("QUANTITY");
							linepOrder = checkNull(rs.getString("LINE_NO__ORD"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}
						
						sql = "SELECT SALE_ORDER FROM PORDER WHERE PURC_ORDER = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, purcOrd );
						rs = pstmtSql.executeQuery();	
						if ( rs.next() )
						{
							saleOrder = checkNull(rs.getString("SALE_ORDER"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						sql = "SELECT LINE_NO__SORD FROM PORDDET WHERE PURC_ORDER = ? AND LINE_NO = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, purcOrd );
						pstmtSql.setString(2, linepOrder );
						rs = pstmtSql.executeQuery();
						if ( rs.next() )
						{
							lineSord = checkNull(rs.getString("LINE_NO__SORD"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						if ( lineSord == null  || lineSord.length() == 0 )
						{
							return "";
						}

						sql = "SELECT ITEM_CODE__ORD, EXP_LEV, ITEM_REF, SITE_CODE, QUANTITY - QTY_DESP, UNIT, STATUS FROM SORDITEM		   WHERE SALE_ORDER = ? AND LINE_NO = ? AND LINE_TYPE = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, saleOrder );
						pstmtSql.setString(2, lineSord );
						pstmtSql.setString(3, "I" );
						rs = pstmtSql.executeQuery();
						if ( rs.next() )
						{
							itemOrd = checkNull(rs.getString("ITEM_CODE__ORD"));
							expLev = checkNull(rs.getString("EXP_LEV"));
							itemRef = checkNull(rs.getString("ITEM_REF"));
							siteCode = checkNull(rs.getString("SITE_CODE"));
							quantity = rs.getDouble(5);
							unit = checkNull(rs.getString("UNIT"));
							status = checkNull(rs.getString("STATUS"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						sql = "SELECT GRADE, EXP_DATE, MFG_DATE, SITE_CODE__MFG FROM STOCK WHERE ITEM_CODE = ?	AND  SITE_CODE = ? 		   AND  LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, itemCode );
						pstmtSql.setString(2, siteCode );
						pstmtSql.setString(3, locCode );
						pstmtSql.setString(4, lotNo );
						pstmtSql.setString(5, lotSl);
						rs = pstmtSql.executeQuery();
						if ( rs.next() )
						{
							grade = checkNull(rs.getString("GRADE"));
							expDate = dtFormat.parse(rs.getString("EXP_DATE"));
							mfgDate = dtFormat.parse(rs.getString("MFG_DATE"));
							siteCodeMfg = checkNull(rs.getString("SITE_CODE__MFG"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						sql = " SELECT CONV__QTY_STDUOM, UNIT__STD FROM SORDDET WHERE SALE_ORDER = ? AND LINE_NO = ? ";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, saleOrder );
						pstmtSql.setString(2, lineSord );
						rs = pstmtSql.executeQuery();
						if ( rs.next() )
						{
							convQty = rs.getDouble("CONV__QTY_STDUOM");
							unitStd = checkNull(rs.getString("UNIT__STD"));
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						sql = "SELECT COUNT(*)  AS COUNT FROM SORDALLOC WHERE SALE_ORDER = ? AND LINE_NO = ? AND EXP_LEV = ? AND 		   ITEM_CODE__ORD = ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ?";
						pstmtSql = conn.prepareStatement(sql);
						pstmtSql.setString(1, saleOrder );
						pstmtSql.setString(2, lineSord );
						pstmtSql.setString(3, expLev );
						pstmtSql.setString(4, itemOrd );
						pstmtSql.setString(5, itemCode );
						pstmtSql.setString(6, lotNo );
						pstmtSql.setString(7, lotSl );
						pstmtSql.setString(8, locCode );
						rs = pstmtSql.executeQuery();
						if ( rs.next() )
						{
							soCnt = rs.getLong("COUNT");
						}
						if(pstmtSql != null)
						{
							pstmtSql.close();
							pstmtSql = null;					
						}	
						if ( rs != null )
						{
							rs.close();
							rs = null;
						}

						if ( soCnt == 0 )
						{
							sql = "INSERT INTO SORDALLOC (SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE__ORD, ITEM_CODE, LOT_NO,				   LOT_SL, LOC_CODE, ITEM_REF, QUANTITY, UNIT, QTY_ALLOC, DATE_ALLOC,									   STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, CONV__QTY_STDUOM,								   UNIT__STD, QUANTITY__STDUOM, MFG_DATE, SITE_CODE__MFG, REF_ID__ALLOC, REF_LINE__NO) VALUES ( ?		   , ? , ?, ?, ? , ?, ? , ? , ? , ? , ? , ? ,  ? , ? , ? , ? , ? , ? , ? , ? , ? , ?, ? , ? , ?)";
							pstmtSql = conn.prepareStatement(sql);
							pstmtSql.setString(1, saleOrder );
							pstmtSql.setString(2, lineSord );
							pstmtSql.setString(3, expLev);
							pstmtSql.setString(4, itemOrd );
							pstmtSql.setString(5, itemCode );
							pstmtSql.setString(6, lotNo );
							pstmtSql.setString(7, lotSl );
							pstmtSql.setString(8, locCode );
							pstmtSql.setString(9, itemRef );
							pstmtSql.setDouble(10, quantity );
							pstmtSql.setString(11, unit );
							pstmtSql.setDouble(12, quantityAlloc );
							pstmtSql.setDate(13, allocDate );
							pstmtSql.setString(14, status );
							pstmtSql.setString(15, grade );
							pstmtSql.setDate(16, expDate );
							pstmtSql.setString(17, "A" );
							pstmtSql.setString(18, siteCode );
							pstmtSql.setDouble(19, convQty );
							pstmtSql.setString(20, unitStd );
							pstmtSql.setDouble(21, quantityAlloc * convQty );
							pstmtSql.setDate(22, mfgDate );
							pstmtSql.setString(23, siteCodeMfg );
							pstmtSql.setString(24, purRcpt );
							pstmtSql.setString(25, linePoRcp );
							rs = pstmtSql.executeQuery();
							if(pstmtSql != null)
							{
								pstmtSql.close();
								pstmtSql = null;					
							}	
							if ( rs != null )
							{
								rs.close();
								rs = null;
							}
						}
						else
						{
							sql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ?, QUANTITY__STDUOM = (QTY_ALLOC + ? ) * ?, 			   STATUS = ?, REF_ID__ALLOC = ? , REF_LINE__NO	= ?  WHERE SALE_ORDER = ? AND LINE_NO = ? 				   AND EXP_LEV = ? AND ITEM_CODE__ORD = ? AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? 				   AND LOC_CODE = ?";
							pstmtUpd = conn.prepareStatement(sql);
							pstmtUpd.setDouble(1, quantityAlloc );
							pstmtUpd.setDouble(2, quantityAlloc );
							pstmtUpd.setDouble(3, convQty );
							pstmtUpd.setString(4, "D" );
							pstmtUpd.setString(5, purRcpt );
							pstmtUpd.setString(6, linePoRcp );
							pstmtUpd.setString(7, saleOrder );
							pstmtUpd.setString(8, lineSord );
							pstmtUpd.setString(9, expLev );
							pstmtUpd.setString(10, itemOrd );
							pstmtUpd.setString(11, itemCode );
							pstmtUpd.setString(12, lotNo );
							pstmtUpd.setString(13, lotSl );
							pstmtUpd.setString(14, locCode );
							updCnt = pstmtUpd.executeUpdate();
							if(pstmtUpd != null)
							{
								pstmtUpd.close();
								pstmtUpd = null;					
							}
						}
						
						sql = "UPDATE SORDER SET ALLOC_FLAG = ?	WHERE SALE_ORDER = ?";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, "Y" );
						pstmtUpd.setDouble(2, saleOrder );
						updCnt = pstmtUpd.executeUpdate();
						if(pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;					
						}

						sql = "UPDATE SORDITEM SET QTY_ALLOC = QTY_ALLOC + ? WHERE SALE_ORDER = ? AND LINE_NO = ? AND EXP_LEV = ?";
						pstmtUpd = conn.prepareStatement(sql);
						pstmtUpd.setDouble(1, quantityAlloc );
						pstmtUpd.setString(2, saleOrder );	
						pstmtUpd.setString(3, lineSord );	
						pstmtUpd.setString(4, expLev );	
						updCnt = pstmtUpd.executeUpdate();
						if(pstmtUpd != null)
						{
							pstmtUpd.close();
							pstmtUpd = null;					
						}


						if ( quantityAlloc == null )
						{
							quantityAlloc = 0.0; 
						}
						//lstr_allocate.tran_date	= datetime(today())
						//lstr_allocate.ref_ser	= 'P-RCP'
						//lstr_allocate.ref_id		= as_pur_rcpt
						// 23-09-04 manoharan line_no is char(3)
						//lstr_allocate.ref_line	= string(li_line_no)
						//lstr_allocate.ref_line	= ls_line_porcp
						// end 23-09-04 manoharan
						//lstr_allocate.site_code	= ls_sitecode
						//lstr_allocate.item_code	= ls_itemcode
						//lstr_allocate.loc_code	= ls_loc_code
						//lstr_allocate.lot_no		= ls_lotno
						//lstr_allocate.lot_sl		= ls_lotsl
						//lstr_allocate.alloc_qty	= lc_quantity_alloc
						//lstr_allocate.chg_user	= userid
						//lstr_allocate.chg_term	= termid
						//lstr_allocate.chg_win	= "W_PORCP"
						
						//retString = lnvo_allocate.gbf_upd_alloc_trace(lstr_allocate)
			
						if ( retString != null  && retString.trim().length() > 0 )
						{
							break;
						}
					}
				}
			}
			else if ( tranType.equalsIgnoreCase("Q") )
			{
				// just commented not clear as it is incomplete
				// allocDate = new Date();
				//
				//sql = "";
				//ls_itemcode = lds_pur_rcpt_dtl.getitemstring(1,"item_code__new")
				//ls_porcpno  = lds_pur_rcpt_dtl.getitemstring(1,"porcp_no")
				//ls_line_porcp = lds_pur_rcpt_dtl.getitemstring(1,"porcp_line_no")
				//
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmtSql != null)
				{
					pstmtSql.close();
					pstmtSql = null;					
				}		
				if( pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}	
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;	
	}*/

	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}

}
