/**
 * PURPOSE : Post Save Logic implementation for Replenishment component.
 * AUTHOR : Chaitali Parab 15/11/11
 */
package ibase.webitm.ejb.wms;

import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.*;
import java.util.TreeMap;

import javax.ejb.Stateless; 
import org.w3c.dom.*;

import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.wms.ReplIssConf;

@Stateless 
public class ReplIssPos extends ValidatorEJB implements ReplIssPosRemote, ReplIssPosLocal
{
	public String postSave( String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		System.out.println("--->postSave called xml str["+domString+"]");
		String retString = "";
		String forcedFlag = "false" ;

		String replOrder = "";
		String sql = "";
		String orderType = "";
		String locCodeTo = "";
		String lotSl = "";
		String lineNoIss = "";
		//Changed by sumit on 09/10/12 start 
		String ptcn = "";
		String errString = "";
		String locCodeTwo = "";
		//Changed by sumit on 09/10/12 end 
		String objName = ""; //Changed by sumit on 21/02/13 
		PreparedStatement pstmt = null;
		ResultSet rs = null ;

		NodeList parentNodeList = null;
		Node parentNode = null;
		int parentNodeListLength = 0;

		int totalRow = 0;
		int selectedRow = 0;
		int ctr = 0;
		int locCtr = 0;
		Document dom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		boolean isConn= false;
		try
		{
			dom = genericUtility.parseString(domString);
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			//Changed by sumit on 20/02/13 updating repl_iss_det start			
			objName = getObjNameFromDom(dom, "objName", "1");
			if("active_repl_iss".equalsIgnoreCase(objName))
			{
				updateReplOrder(dom, tranId, conn);
			}			
			//Changed by sumit on 20/02/13 updating repl_iss_det end 
			if(detail3List != null && detail3List.getLength() > 0)
			{
				System.out.println("Detail3 length = "+detail3List.getLength());

				replOrder = GenericUtility.getInstance().getColumnValueFromNode("repl_order", dom.getElementsByTagName("Detail1").item(0));
				//Changed by sumit on 09/10/12 for taking value of locCodeto 
				locCodeTwo = GenericUtility.getInstance().getColumnValueFromNode("loc_code__to", dom.getElementsByTagName("Detail3").item(0));
				System.out.println(" loc_code__to ["+locCodeTwo+"]");
				//Changed by Rohan on 19-02-13 to count only uncancle wave
				//sql = "select count(*) from repl_ord_det where repl_order = ?";
				sql = "SELECT COUNT(*) FROM REPL_ORD_DET WHERE REPL_ORDER = ? AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, replOrder);						
				rs = pstmt.executeQuery();	
				if( rs.next() )
				{
					totalRow = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;					
				System.out.println("======>totalRow : "+totalRow);

				sql = "select count(*) from repl_iss_det where repl_order = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, replOrder);						
				rs = pstmt.executeQuery();	
				if( rs.next() )
				{
					selectedRow = rs.getInt(1);
				}
				System.out.println(" selectedRow ==>["+selectedRow+"]");
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				//Added By Mahesh Patidar on 31/07/12
				//Changed by sumit on 09/10/12 getting ptcn value 
				//sql = "select order_type from repl_ord_hdr where repl_order = ?";
				sql = 	" SELECT ROH.ORDER_TYPE, WID.PTCN FROM REPL_ORD_HDR ROH, WAVE_TASK_DET WID WHERE ROH.REPL_ORDER = ? " +
						" AND ROH.REPL_ORDER = WID.REF_ID ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, replOrder);						
				rs = pstmt.executeQuery();	
				if( rs.next() )
				{
					orderType = rs.getString(1);
					ptcn = rs.getString(2);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				System.out.println("orderType="+orderType);//Changed by sumit on 09/10/12 calling report start.
				if("D".equals(orderType) || "L".equals(orderType))
				{
					System.out.println("Calling Jasper Report ***************************************");
					errString = generateReport(replOrder, ptcn, locCodeTwo, conn,xtraParams);
					if( errString.trim().length() == 0)
					{
						System.out.println(" report genetrated successfully ");
					}
					
					//Changed by sumit on 16/11/12 updating pallet no in carton master start.
					sql = "UPDATE CARTON_MASTER SET PALLET_NO = (SELECT CARTON_NO FROM CARTON_MASTER WHERE PTCN = ? " +
							" AND PICK_ORDER = ? ) WHERE PTCN = ? AND PICK_ORDER = ? ";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, ptcn);
					pstmt.setString(2, replOrder);
					pstmt.setString(3, ptcn);
					pstmt.setString(4, replOrder);
					if ( pstmt.executeUpdate() > 0 )
					{
						System.out.println(" pallet no updated sucessfully ");
					}
					if( pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					//Changed by sumit on 16/11/12 updating pallet no in carton master end.
					
				}
				//Changed By 10/12/12 Pragyan To support for PND to active PSO to Active
				//Changed By Mahesh Patidar on 07/08/12
				//changed condition by Kunal Mandhre on 31/08/12
				//if ("A".equalsIgnoreCase(orderType) || "Q".equalsIgnoreCase(orderType) || "E".equalsIgnoreCase(orderType) || "T".equalsIgnoreCase(orderType))
				if ("A".equalsIgnoreCase(orderType) || "Q".equalsIgnoreCase(orderType) || "E".equalsIgnoreCase(orderType) || "T".equalsIgnoreCase(orderType) || "I".equalsIgnoreCase(orderType) || "P".equalsIgnoreCase(orderType))
				{
					System.out.println("for case repl::::");
					parentNodeList = dom.getElementsByTagName("Detail4");
					parentNodeListLength = parentNodeList.getLength();
					locCtr = 0;
					if ( parentNodeListLength > 0)
					{
						for(ctr = 0; ctr < parentNodeListLength ; ctr++ )
						{
							parentNode = parentNodeList.item(ctr);
							locCodeTo = genericUtility.getColumnValueFromNode("loc_code__to", parentNode);
							lotSl = genericUtility.getColumnValueFromNode("lot_sl", parentNode);
							lineNoIss = genericUtility.getColumnValueFromNode("line_no", parentNode);
							
							
							if(lineNoIss == null  || lineNoIss.trim().length() <= 0)
							{
								lineNoIss = "0";
							}
							else
							{
								lineNoIss = lineNoIss.trim();
							}
								
							System.out.println("Location Code["+locCodeTo+"] and lineNoIss["+lineNoIss+"] and tran_id["+tranId+"]");
							//Commented By Mahesh Patidar on 07/08/12
							//					parentNodeList = dom.getElementsByTagName("Detail2");
							//					parentNodeListLength = parentNodeList.getLength();
							//					parentNode = parentNodeList.item(0);
							//					tranId1 = genericUtility.getColumnValueFromNode("tran_id", parentNode);
							//Commented By Mahesh Patidar 
							if (locCodeTo != null && locCodeTo.trim().length() > 0)
							{
								locCtr++;
								//Changed by sumit on 07/02/13 updating loc_to_assigned
								//Chnaged by sumit on 10-05-13 no need of sql.start
								//sql = "update repl_iss_det set loc_code__to = ? where tran_id = ?  and line_no = ? ";
								/*
								sql = "update repl_iss_det set loc_code__to = ?, loc_to_assigned = 'Y' where tran_id = ?  and line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString( 1, locCodeTo);								
								pstmt.setString( 2, tranId);		
								pstmt.setInt( 3, Integer.parseInt(lineNoIss));		
								pstmt.executeUpdate();	
								pstmt.close();
								pstmt = null;
								*/
								//Chnaged by sumit on 10-05-13 no need of sql.end
							}							
							
							//Changed by sumit on 23/02/13 commenting below code from for loop and written outside of for loop start . 
							/*if(totalRow == locCtr)
							{
								System.out.println("=======>totalRow [" + totalRow + "] locCtr [" + locCtr + "]");
								ReplIssConf replIssConf = new ReplIssConf();
								retString = replIssConf.confirm(tranId, xtraParams, forcedFlag, conn, isConn);
								System.out.println("retString[" +retString +"]");
								if (retString.indexOf("REPISSCONF") > -1)
								{
									retString = "";
								}
								if ( retString != null && retString.length() > 0 )
								{
									//throw new Exception("Exception while calling confirm for tran  Id:["+tranId+"]");
									throw new Exception(retString);
								}
							}
							else
							{
								System.out.println("All rows are not selected");				
							}*/
							//Changed by sumit on 23/02/13 commenting below code from for loop and written outside of for loop END . 
						}
						//Changed by sumit on 23/02/13 written above commented code here start . 
						if("active_repl_iss".equalsIgnoreCase(objName) && totalRow == selectedRow )
						{
							sql = "select COUNT(*) FROM REPL_ISS_DET WHERE TRAN_ID = ? AND LOC_CODE__TO IS NOT NULL ";
							
							pstmt = conn.prepareStatement(sql);												
							pstmt.setString( 1, tranId);
							rs = pstmt.executeQuery();
							if( rs.next())
							{
								locCtr = rs.getInt(1);
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							System.out.println(" locCtr ==>["+locCtr+"]");
						}
						//Changed by sumit on 23/02/13 written above commented code here end . 
						if(totalRow == locCtr)
						{
							System.out.println("=======>totalRow [" + totalRow + "] locCtr [" + locCtr + "]");
							ReplIssConf replIssConf = new ReplIssConf();
							retString = replIssConf.confirm(tranId, xtraParams, forcedFlag, conn, isConn);
							System.out.println("retString[" +retString +"]");
							if (retString.indexOf("REPISSCONF") > -1)
							{
								retString = "";
							}
							if ( retString != null && retString.length() > 0 )
							{
								//throw new Exception("Exception while calling confirm for tran  Id:["+tranId+"]");
								throw new Exception(retString);
							}
						}
						else
						{
							System.out.println("All rows are not selected");				
						}
					}
				}
				else
				{
					//End Changed BY Mahesh Patidar
					//Ended By Mahesh Patidar
					if(totalRow == selectedRow)
					{
						System.out.println("=======>totalRow == selectedRow");
						ReplIssConf replIssConf = new ReplIssConf();
						retString = replIssConf.confirm(tranId, xtraParams, forcedFlag, conn, isConn);
						System.out.println("retString[" +retString +"]");
						if (retString.indexOf("REPISSCONF") > -1)
						{
							retString = "";
						}
						if ( retString != null && retString.length() > 0 )
						{
							//throw new Exception("Exception while calling confirm for tran  Id:["+tranId+"]");
							throw new Exception(retString);
						}
					}
					else
					{
						System.out.println("All rows are not selected");				
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :ReplIssPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	//Changed by sumit on 09/10/12 generating report start.
	private String generateReport(String replOrder,String ptcn, String locCodeTo, Connection conn,String xtraParams ) throws ITMException
    {
    	String errString ="";
    	String printMode = "";
    	String outputFilename = "";
    	TreeMap argMap = null;
    	DistCommon discommon = new DistCommon();
    	GenericUtility genericUtility = GenericUtility.getInstance();
    	try
    	{    		
    		//String printerStr  = discommon.getDisparams("999999","STOCK_TO_DOCK_PRINTER",conn);
    		System.out.println("PRINT MODE CALLED");
    		String chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");								
			System.out.println(" printerStr ->["+locCodeTo+"]");				
			if(locCodeTo.trim().length() > 0)
			{					
				String reportObjName = discommon.getDisparams("999999","STOCK_TO_DOCK_OBJ_NAME",conn);					
				String reportSavetype = "NATIVE";					
				ibase.webitm.reports.utility.JasperReportGenerator jrGenerator = new ibase.webitm.reports.utility.JasperReportGenerator();
				String xsdString ="<Arguments>" +
							"<Argument id=\"REPLORDER\" name=\"REPLORDER\">" +
							"<argType>java.lang.String</argType>" +
							//"<value>"+ptcnList.get(i)+"</value>" +
							"<value>"+replOrder+"</value>" +
							"</Argument>" +
							"<Argument id=\"PTCN\" name=\"PTCN\">" +
							"<argType>java.lang.String</argType>" +
							//"<value>"+ptcnList.get(i)+"</value>" +
							"<value>"+ptcn+"</value>" +
							"</Argument>" +
							"<Argument id=\"x_path\" name=\"x_path\">" +
							"<argType>java.lang.String</argType>" +
							"<value>/DocumentRoot/wave_task_main</value>" +
							"</Argument>" +
							"<Argument id=\"design_source\" name=\"design_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>wave_task_main.jrxml</value>" +
							"</Argument>" +
							"<Argument id=\"data_source\" name=\"data_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>SQL</value>" +
							"</Argument>" +
							"<Argument id=\"bind_type\" name=\"bind_type\">" +
							"<argType>java.lang.Integer</argType>" +
							"<value>0</value>" +
							"</Argument>" +
							"<Argument id=\"report_type\" name=\"report_type\">" +
							"<argType>java.lang.String</argType>" +
							"<value>JASPER</value>" +
							"</Argument>" +
							"<Argument id=\"report_save_type\" name=\"report_save_type\">" +
							"<argType>java.lang.String</argType>" +
							//Changed by sumit on 07/09/12  changed NATIVE to PDF
							//"<value>NATIVE</value>" +									
							"<value>"+reportSavetype+"</value>" +
							"</Argument>" +
							"</Arguments>";
				System.out.println(" XSD parser **->["+xsdString+"]");						
				errString = jrGenerator.printReport(reportObjName, xsdString, chgUser, locCodeTo, 1, conn);			
					
				//}
				System.out.println(" PRINTING task completed--------**------->");
			}			
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		throw new ITMException(e);
    	}
    	
    	return errString;
    }
	//Changed by sumit on 09/10/12 generating report end.
	
	//Changed by sumit on 21/02/13 to reterive obj_name start
	private String getObjNameFromDom( Document dom, String attribute, String objContext ) throws RemoteException,ITMException
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String objName = "";
		int	detailListLength = 0;

		try
		{
			detailList = dom.getElementsByTagName("Detail"+objContext);
			detailListLength = detailList.getLength();			
			for (int ctr = 0; ctr < detailListLength; ctr++)
			{				
				currDetail = detailList.item(ctr);
				objName = currDetail.getAttributes().getNamedItem(attribute).getNodeValue();
			}			
		}
		catch ( Exception e )
		{
			throw new ITMException(e);
		}
		return objName;
	}
	
	private String checkNull(String str)
	{
		if( str == null)
		{
			str = "";
		}
		return str;
	}
	//Changed by sumit on 21/02/13 to reterive obj_name end
	
	//Changed by sumit on 21/02/13 to update repl_iss_det start
	private String updateReplOrder ( Document dom , String tranId, Connection conn) throws ITMException
	{
		String sql = "";
		String errString = "";
		String locCode = "", lotNo = "", siteCode = "", itemCode = "";
		String replOrder = "";
		String locCodeSys = "";
		String lotSlNew = "";
		String locCodeTo = "";
		int lineNoOrd = 0;
		String lotSl = "";
		double noArt = 0.0, quantity = 0.0;
		int update = 0, tranLine = 0;
		int replCount = 0;//Added by sumit on 11/05/13
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;
		ResultSet rs = null, rs1 = null, rs2 = null;
		
		NodeList parentNodeList = null;
		Node parentNode = null;
		int parentNodeListLength = 0;
		
		try
		{
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			parentNodeListLength = detail3List.getLength();
			sql = "SELECT COUNT(*) AS COUNT FROM REPL_ORD_DET WHERE REPL_ORDER IN " +
					" ( SELECT DISTINCT REPL_ORDER FROM REPL_ISS_DET WHERE TRAN_ID = ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				replCount = rs.getInt(1);
			}
			System.out.println(" replCount ["+replCount+"]");
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			//sql = "SELECT MAX(LINE_NO) FROM REPL_ISS_DET WHERE TRAN_ID = ?";
			sql = "SELECT COUNT(*) AS COUNT FROM REPL_ISS_DET WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				tranLine = rs.getInt(1);
			}
			System.out.println(" COUNT ["+tranLine+"]");
			tranLine = tranLine + replCount;
			System.out.println(" maxline ["+tranLine+"]");
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;	
			
			for(int row = 0; row < parentNodeListLength; row++)
			{				
				
	        	replOrder = GenericUtility.getInstance().getColumnValueFromNode("repl_order", dom.getElementsByTagName("Detail3").item(row));
	        	//lineNoOrd = Integer.parseInt(GenericUtility.getInstance().getColumnValueFromNode("line_no__ord", dom.getElementsByTagName("Detail3").item(row)));
				itemCode = GenericUtility.getInstance().getColumnValueFromNode("item_code", dom.getElementsByTagName("Detail3").item(row));
				siteCode = GenericUtility.getInstance().getColumnValueFromNode("site_code", dom.getElementsByTagName("Detail3").item(row));
				locCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code", dom.getElementsByTagName("Detail3").item(row));
				lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no", dom.getElementsByTagName("Detail3").item(row));
				lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl", dom.getElementsByTagName("Detail3").item(row));
				locCodeSys = GenericUtility.getInstance().getColumnValueFromNode("loc_code__sys", dom.getElementsByTagName("Detail3").item(row));
				lotSlNew = GenericUtility.getInstance().getColumnValueFromNode("lot_sl__new", dom.getElementsByTagName("Detail3").item(row));
				noArt = Double.parseDouble(GenericUtility.getInstance().getColumnValueFromNode("no_art", dom.getElementsByTagName("Detail3").item(row)));
				
				System.out.println(" replOrder ["+replOrder+"] lineNoOrd ["+lineNoOrd+"]");
				System.out.println(" itemCode ["+itemCode+"] siteCode ["+siteCode+"] locCode["+locCode+"] lotNo["+lotNo+"] lotSl["+lotSl+"]");
				System.out.println(" locCodeSys ["+locCodeSys+"] lotSlNew ["+lotSlNew+"] noArt["+noArt+"]");
				
				sql = 	" UPDATE REPL_ISS_DET SET LOT_SL__NEW = ? WHERE REPL_ORDER = ? AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? " +
						" AND LOT_NO = ? AND LOT_SL = ? AND TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sql);			
				pstmt.setString(1, lotSlNew);
				pstmt.setString(2, replOrder);
				pstmt.setString(3, itemCode);
				pstmt.setString(4, siteCode);
				pstmt.setString(5, locCode);
				pstmt.setString(6, lotNo);	
				pstmt.setString(7, lotSl);
				pstmt.setString(8, tranId);
				update = 0;
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(" update seccessfully ");
				}
				pstmt.close(); pstmt = null;
				
				/*if( noArt == 1 )
				{
					continue;
				}*/
				
				System.out.println(" **********-----------**********");
				sql = " SELECT REPL_ORDER, LINE_NO, QUANTITY, NO_ART FROM REPL_ORD_DET WHERE REPL_ORDER = ? " +
					  " AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL  = ? " +
					  " AND CASE WHEN CANCEL_MODE IS NULL THEN 'N' ELSE CANCEL_MODE END <> 'Y' ORDER BY LINE_NO ";
				pstmt = conn.prepareStatement(sql);
				
				sql = " SELECT REPL_ORDER, LINE_NO__ORD, QUANTITY, NO_ART FROM REPL_ISS_DET WHERE TRAN_ID = ? AND REPL_ORDER = ? " +
						" AND LINE_NO__ORD = ? AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
				pstmt1 = conn.prepareStatement(sql);		
				
				
				pstmt.setString(1, replOrder);				
				pstmt.setString(2, itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4, locCode);
				pstmt.setString(5, lotNo);
				pstmt.setString(6, lotSl);				
				rs = pstmt.executeQuery();
				while( rs.next() )
				{
					quantity = rs.getDouble("QUANTITY");			
					
					pstmt1.setString(1, tranId);					
					pstmt1.setString(2, replOrder);
					pstmt1.setInt(3, rs.getInt("LINE_NO"));
					pstmt1.setString(4, itemCode);
					pstmt1.setString(5, siteCode);
					pstmt1.setString(6, locCode);
					pstmt1.setString(7, lotNo);
					pstmt1.setString(8, lotSl);
					rs1 = pstmt1.executeQuery();
					if( rs1.next())
					{
						if( quantity != rs1.getDouble("QUANTITY"))
						{
							sql = " UPDATE REPL_ISS_DET SET QUANTITY = ?, NO_ART = 1 WHERE REPL_ORDER = ? AND LINE_NO__ORD = ? " +
								  " AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND TRAN_ID = ?";
							pstmt2 = conn.prepareStatement(sql);							
							pstmt2.setDouble(1, quantity);
							pstmt2.setString(2, replOrder);
							pstmt2.setInt(3, rs.getInt("LINE_NO"));
							pstmt2.setString(4, itemCode);
							pstmt2.setString(5, siteCode);
							pstmt2.setString(6, locCode);
							pstmt2.setString(7, lotNo);
							pstmt2.setString(8, lotSl);
							pstmt2.setString(9, tranId);
							update = 0;
							update = pstmt2.executeUpdate();
							if( update > 0 )
							{
								System.out.println("repl order ["+replOrder+"] updated ");
							}
							else
							{
								throw new Exception("update fail of repl_iss_det ");
							}							
							pstmt2.close(); pstmt2 = null;			
							
						}
					}
					else
					{				
						
						//tranLine++;//Increment tran line_no
						// inserting new record in case of record not preset in repl_iss_det
						sql = " INSERT INTO REPL_ISS_DET(TRAN_ID, LINE_NO, REPL_ORDER, LINE_NO__ORD, ITEM_CODE, SITE_CODE, " +
								" LOC_CODE, LOT_NO, LOT_SL, QUANTITY, NO_ART, LOC_CODE__SYS, LOT_SL__NEW ) " +
								" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
						pstmt2 = conn.prepareStatement(sql);
						pstmt2.setString(1, tranId);
						//Changed by sumit on 11/05/13 incremented max line.
						//pstmt2.setInt(2, rs.getInt("LINE_NO"));
						pstmt2.setInt(2, tranLine++);
						pstmt2.setString(3, replOrder);
						pstmt2.setInt(4, rs.getInt("LINE_NO"));
						pstmt2.setString(5, itemCode);
						pstmt2.setString(6, siteCode);
						pstmt2.setString(7, locCode);
						pstmt2.setString(8, lotNo);
						pstmt2.setString(9, lotSl);					
						pstmt2.setDouble(10, quantity);
						pstmt2.setDouble(11, 1);
						pstmt2.setString(12, locCodeSys);
						pstmt2.setString(13, lotSlNew);
						update = 0;
						update = pstmt2.executeUpdate();
						if( update > 0 )
						{
							System.out.println("repl order ["+replOrder+"] updated ");
						}
						else
						{
							throw new Exception("INSERTION FAIL OF REPL_ISS_DET");
						}							
						pstmt2.close(); pstmt2 = null;
					}
					
				}				
				rs.close(); rs =null;
				pstmt.close(); pstmt = null;										
				
			}
			
			sql = 	" UPDATE REPL_ISS_DET SET LOC_CODE__TO = ?, LOC_TO_ASSIGNED = 'Y' WHERE REPL_ORDER = ? AND ITEM_CODE = ? " +
					" AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			
			NodeList detail4List = dom.getElementsByTagName("Detail4");
			parentNodeListLength = detail4List.getLength();
			for(int row = 0; row < parentNodeListLength; row++)
			{
				replOrder = GenericUtility.getInstance().getColumnValueFromNode("repl_order", dom.getElementsByTagName("Detail3").item(row));	        	
				itemCode = GenericUtility.getInstance().getColumnValueFromNode("item_code", dom.getElementsByTagName("Detail4").item(row));
				siteCode = GenericUtility.getInstance().getColumnValueFromNode("site_code", dom.getElementsByTagName("Detail4").item(row));
				locCode = GenericUtility.getInstance().getColumnValueFromNode("loc_code", dom.getElementsByTagName("Detail4").item(row));
				lotNo = GenericUtility.getInstance().getColumnValueFromNode("lot_no", dom.getElementsByTagName("Detail4").item(row));
				lotSl = GenericUtility.getInstance().getColumnValueFromNode("lot_sl", dom.getElementsByTagName("Detail4").item(row));
				locCodeTo = GenericUtility.getInstance().getColumnValueFromNode("loc_code__to", dom.getElementsByTagName("Detail4").item(row));
				noArt = Double.parseDouble(GenericUtility.getInstance().getColumnValueFromNode("no_art", dom.getElementsByTagName("Detail3").item(row)));
				lotSlNew = GenericUtility.getInstance().getColumnValueFromNode("lot_sl__new", dom.getElementsByTagName("Detail3").item(row));
							
				System.out.println(" replOrder ["+replOrder+"] ");
				System.out.println(" itemCode ["+itemCode+"] siteCode ["+siteCode+"] locCode["+locCode+"] lotNo["+lotNo+"] lotSl["+lotSl+"]");
				System.out.println(" locCodeTo ["+locCodeTo+"] noArt["+noArt+"]");
				
				/*if( noArt ==  1) 
				{
					continue;
				}*/
				
				pstmt.setString(1, locCodeTo);				
				pstmt.setString(2, replOrder);
				pstmt.setString(3, itemCode);
				pstmt.setString(4, siteCode);
				pstmt.setString(5, locCode);
				pstmt.setString(6, lotNo);	
				pstmt.setString(7, lotSl);
				pstmt.setString(8, tranId);
				update = 0;
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println(" update seccessfully ");
				}
				pstmt.clearParameters();
			}
			pstmt.close(); pstmt = null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close(); rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch( Exception ex)
			{
				ex.printStackTrace();
				errString = ex.getMessage();
				throw new ITMException(ex);
			}
		}
		
		return errString;
	}
	//Changed by sumit on 21/02/13 to update repl_iss_det end
}
