package ibase.webitm.ejb.mfg;


import java.util.*;
import java.sql.*;
import java.sql.Date;


import org.w3c.dom.*;

import java.text.SimpleDateFormat;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;

import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.InvAllocTrace;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockAllocationPrc;
import ibase.webitm.ejb.dis.StockAllocationPrc.*;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;

import ibase.webitm.utility.TransIDGenerator;

public class MrpWizPrsSaveBean 
{


	public String preSaveForm(String xmlString1 , String chgUser , String chgTerm  )throws ITMException
	{
		Document dom = null;
		System.out.println("@@@@@@@@@---------MrpWizPrsSaveBean EJB called...");


		try
		{
			System.out.println("@@@@@@@xmlString1["+xmlString1+"]@@@@@@@");

			System.out.println("@@@@@@ in bean chgUser["+chgUser+"]::chgTerm["+chgTerm+"]");

			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				executepreSaveForm(dom,chgUser,chgTerm);
			}			
		}
		catch(Exception e)
		{
			System.out.println("Exception :MrpWizPrsSaveBean ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}

	private String executepreSaveForm(Document dom , String chgUser , String chgTerm )throws ITMException
	{
		PreparedStatement pstmt = null, pstmt1 = null;
		String sql = "", itemCode = "", effDate = "", itemSer = "", sql1="";
		int updCnt = 0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		NodeList hdrDom = null;
		//Node currDetail = null;
		ResultSet rs1 = null;
		String saleOrder=" ",lineNo=" ", siteCode=" ", lotNo=" ", locCode="", lotSl=" ", tranSer="", runningSupplyStr="", qtyReqdStr="",allocQtyStr="";
		double runningSupply=0,qtyReqd=0;
		String tranId="",refId="",refLineNo="",unit="",errString="";
		double quantity=0,qtyAllocated=0,totAllocQty=0,allocQty=0;
		int cnt=0;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver(); 
		String supplyStr="",availableSupplyStr="" ,suppSour="",remarks=" ";

		InvAllocTraceBean invAllocTrace = null;
		ArrayList retValue = new ArrayList();

		HashMap invAllocateMap = new HashMap ();
		HashMap invAllocDetMap = new HashMap ();
		java.util.Date currentDate = new java.util.Date();
		//	SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
		//	Timestamp newsysDate = java.sql.Timestamp.valueOf( sdf.format(currentDate)+" 00:00:00.0");
		//	System.out.println("Now the date is :=>  ["+newsysDate+"]");
		java.sql.Date date = new java.sql.Date(currentDate.getTime());
		String parentItem="",prevItemWorder="",previousItem="";

		
		StockAllocationPrc stockAllocationPrc = new StockAllocationPrc()  ;
		ArrayList itemValueList = new ArrayList();
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");

			NodeList hdrDommList = dom.getElementsByTagName("Detail4");
			System.out.println("len===["+hdrDommList.getLength()+"]");

			for (int dtlCtr = 0; dtlCtr < hdrDommList.getLength(); dtlCtr++)
			{
				Node detailListNode = hdrDommList.item(dtlCtr);
				NodeList detail4List= detailListNode.getChildNodes(); 
				System.out.println("@@@@@@@@@node name[" + detailListNode.getNodeName()+"]");

				if("Detail4".equalsIgnoreCase(detailListNode.getNodeName()))
				{
					System.out.println("@@@@inside detail4----------------");
					for (int cntr = 0; cntr < detail4List.getLength(); cntr++) 
					{
						Node	detail4Node = detail4List.item(cntr); 
						//NodeList detail4NodeList = detail4Node.getChildNodes(); 
						System.out.println("@@@@@@@@@node name[" + detail4Node.getNodeName()+"]"); 

						// if(  detail4Node != null  &&  detail4Node.getNodeValue() != null )
						{
							if("sale_order".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								saleOrder =  detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("line_no".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								lineNo = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("site_code".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								siteCode =  detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("item_code".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								itemCode = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("lot_no".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								lotNo =  detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("lot_sl".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								lotSl = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("loc_code".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								locCode =  detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("tran_ser".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								tranSer = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("running_supply".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								runningSupplyStr =  detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("qty_reqd".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								qtyReqdStr = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("alloc_qty".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								allocQtyStr = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("supply".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								supplyStr = detail4List.item(cntr).getFirstChild().getNodeValue();   
							}
							if("available_supply".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								availableSupplyStr = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("supp_sour".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								suppSour = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("parent_item".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								parentItem = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							
							if("prev_item_worder".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								prevItemWorder = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							if("previous_item".equalsIgnoreCase( detail4Node.getNodeName()))
							{
								previousItem = detail4List.item(cntr).getFirstChild().getNodeValue();
							}
							
						}
					}

					System.out.println("@@@@ctr["+dtlCtr+"]saleOrder["+saleOrder+"]-lineNo["+lineNo+"]-qtyReqd["+qtyReqdStr+"]prevItemWorder["+prevItemWorder+"]previousItem["+previousItem+"]@@@");
					System.out.println("@@@@ctr["+dtlCtr+"]siteCode["+siteCode+"]-itemCode["+itemCode+"]-lotNo["+lotNo+"]-lotSl["+lotSl+"]-locCode["+locCode+"]-tranSer["+tranSer+"]allocQtyStr["+allocQtyStr+"]@@@");

					String itemCodeHdr="";
					remarks = "From mrp process";

					sql1=" select item_code from sorddet where sale_order = ? and line_no = ? ";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setString(1,saleOrder);
					pstmt1.setString(2,lineNo);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						itemCodeHdr =rs1.getString("item_code");
					}
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;

					if( runningSupplyStr.trim().length() > 0)
					{
						runningSupply = runningSupplyStr==null?0:Double.parseDouble(runningSupplyStr);
					}

					if( qtyReqdStr.trim().length() > 0)
					{
						qtyReqd = qtyReqdStr==null?0:Double.parseDouble(qtyReqdStr);
					}
					if( allocQtyStr.trim().length() > 0)
					{
						allocQty = allocQtyStr==null?0:Double.parseDouble(allocQtyStr);
					}

					if(itemCodeHdr.equalsIgnoreCase(itemCode))
					{
					
						InvAllocTrace invallocTrace = new InvAllocTrace();
						invallocTrace.setRefSer("S-ORD");
						invallocTrace.setRefId(saleOrder);
						invallocTrace.setRefLine(lineNo);
						invallocTrace.setSiteCode(siteCode);
						invallocTrace.setItemCode(itemCode);
						invallocTrace.setLocCode(locCode);
						invallocTrace.setLotNo(lotNo);
						invallocTrace.setLotSl(lotSl);
						invallocTrace.setAllocQty(allocQty);
						invallocTrace.setChgUser(chgUser);
						invallocTrace.setChgTerm(chgTerm);
						invallocTrace.setChgDate( date );

						errString = stockAllocationPrc.updateInvallocTrace(invallocTrace, conn);

						if (errString != null && errString.trim().length() > 0)
						{
							System.out.println("@@@@@@rolledback errString :::"+ errString );
							conn.rollback();
							return errString;
						}
						
					}
					else
					{
						String tranType = "AUT";

						tranId = generateTranId( "w_inv_allocate",siteCode, conn );

						sql = " insert into inv_allocate ( TRAN_ID,TRAN_DATE,TRAN_TYPE,WORK_ORDER,SITE_CODE,ITEM_CODE," +
								" CHG_DATE,CHG_USER,CHG_TERM,REMARKS,CONFIRMED,CONF_DATE,STATUS,STATUS_DATE )" +
								" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
						System.out.println("header sql :"+sql);
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranId);
						pstmt.setDate(2,date);   // TRAN_DATE                       
						pstmt.setString(3, tranType);   // TRAN_TYPE                               VARCHAR2(3)                                                                                                                                                                                                      
						pstmt.setString(4, prevItemWorder);   //  WORK_ORDER                              CHAR(10)                                                                                                                                                                                                                                                   
						pstmt.setString(5, siteCode);   // SITE_CODE                               CHAR(5)                                                                                                                                                                                       
						pstmt.setString(6, previousItem);   //  ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
						pstmt.setDate(7, date);   //CHG_DATE                                DATE                                                                                                                                                                                          
						pstmt.setString(8, chgUser);   //CHG_USER                                CHAR(10)                                                                                                                                                                                      
						pstmt.setString(9, chgTerm );   // CHG_TERM                                CHAR(15)                                                                                                                                                                                      
						pstmt.setString(10, remarks);   //REMARKS                                 VARCHAR2(40)                                                                                                                                                                                  
						pstmt.setString(11, "Y");   //CONFIRMED                               CHAR(1)                                                                                                                                                                                       
						pstmt.setDate(12, date);   //CONF_DATE                               DATE                                                                                                                                                                                          
						pstmt.setString(13, "O");   //STATUS                                  CHAR(1)                                                                                                                                                                                       
						pstmt.setDate(14, date);   //STATUS_DATE  

						int hdrCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;

						// for inserting detail
						sql = " insert into inv_alloc_det ( TRAN_ID,LINE_NO,SITE_CODE,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE," +
								" LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,ALLOC_QTY,DEALLOCATED )" +
								" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
						System.out.println("detail sql :"+sql);
						pstmt = conn.prepareStatement(sql);

						pstmt.setString(1,tranId);
						pstmt.setString(2,lineNo); ////LINE_NO                        NOT NULL NUMBER(3)                                                                                                                                                                                     
						pstmt.setString(3, siteCode ); //SITE_CODE                               CHAR(5)                                                                                                                                                                                       
						pstmt.setString(4, itemCode ); //ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
						pstmt.setString(5, lotNo ); //LOT_NO                                  CHAR(15)                                                                                                                                                                                      
						pstmt.setString(6, lotSl ); //LOT_SL                                  CHAR(15)                                                                                                                                                                                      
						pstmt.setDouble(7, allocQty ); //QUANTITY                                NUMBER(14,3)                                                                                                                                                                                  
						pstmt.setString(8, remarks ); //REMARKS                                 VARCHAR2(60)                                                                                                                                                                                  
						pstmt.setString(9, "" ); //REAS_CODE                               CHAR(5)                                                                                                                                                                                       
						pstmt.setString(10, locCode ); //LOC_CODE                                CHAR(8)                                                                                                                                                                                       
						pstmt.setString(11, "" ); //EXP_LEV                                 VARCHAR2(20)                                                                                                                                                                                  
						pstmt.setDouble(12, 0 ); //POTENCY_ADJ                             NUMBER(14,3)                                                                                                                                                                                  
						pstmt.setDouble(13, 0 ); //ISSUE_QTY                               NUMBER(14,3)                                                                                                                                                                                  
						pstmt.setDouble(14, 0 ); //DEALLOC_QTY                             NUMBER(14,3)                                                                                                                                                                                  
						pstmt.setDouble(15, allocQty ); //ALLOC_QTY                               NUMBER(14,3)                                                                                                                                                                                  
						pstmt.setString(16, "" ); //DEALLOCATED                             CHAR(1)  

						int detCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;	

						System.out.println("Inserted row :::hdrCnt ["+hdrCnt+"]::detCnt["+detCnt+"]");

						String refSer = "W-RIN" ; 
						// for new 17/06/14
						HashMap hashMap = null;
						hashMap = new HashMap();
						hashMap.put("tran_date",date);
						hashMap.put("ref_ser",refSer);
						hashMap.put("ref_id",tranId);
						hashMap.put("ref_line",lineNo);					
						hashMap.put("site_code",siteCode);
						hashMap.put("item_code",itemCode);
						hashMap.put("loc_code",locCode);
						hashMap.put("lot_no",lotNo);
						hashMap.put("lot_sl",lotSl);
						hashMap.put("alloc_qty",allocQty); 
						hashMap.put("chg_user",chgUser);
						hashMap.put("chg_term",chgTerm);
						hashMap.put("chg_date", date );
						System.out.println("CALLING updateInvallocTrace() --");
						invAllocTrace = new InvAllocTraceBean();
						errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
						System.out.println("updateInvallocTrace  1....."+errString);
						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("@@@@@@rolledback errString :::"+ errString );
							conn.rollback();
							return errString;
							/*
					retValue.add("Error");
					retValue.add(new Double(allocQty));
					return retValue;    
							 */
						}
					} 
				}

				/*


				if("M".equalsIgnoreCase( suppSour ) && (!itemCodeHdr.equalsIgnoreCase(itemCode)))
				{
					ArrayList valueList = new ArrayList();
					String tranType = "AUT";

					//valueList.add( tranId );  //					TRAN_ID                        NOT NULL CHAR(10)
					valueList.add(date);   // TRAN_DATE                               DATE
					valueList.add(tranType);        // TRAN_TYPE                               VARCHAR2(3)                                                                                                                                                                                                      
					valueList.add(" ");  //  WORK_ORDER                              CHAR(10)                                                                                                                                                                                                                                                   
					valueList.add( siteCode ) ; // SITE_CODE                               CHAR(5)                                                                                                                                                                                       
					valueList.add(itemCode );   //  ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
					valueList.add(date);  //CHG_DATE                                DATE                                                                                                                                                                                          
					valueList.add(chgUser);  //CHG_USER                                CHAR(10)                                                                                                                                                                                      
					valueList.add(chgTerm);  // CHG_TERM                                CHAR(15)                                                                                                                                                                                      
					valueList.add(remarks); //REMARKS                                 VARCHAR2(40)                                                                                                                                                                                  
					valueList.add("Y");    //CONFIRMED                               CHAR(1)                                                                                                                                                                                       
					valueList.add(date);  //CONF_DATE                               DATE                                                                                                                                                                                          
					valueList.add("O");  //STATUS                                  CHAR(1)                                                                                                                                                                                       
					valueList.add(date); //STATUS_DATE                             DATE  

					invAllocateMap.put(itemCode, valueList);

				}
				else
				{
					ArrayList valueList = new ArrayList();
					String tranType = "AUT";

					//valueList.add( tranId );  //					TRAN_ID                        NOT NULL CHAR(10)
					//valueList.add(lineNo); ////LINE_NO                        NOT NULL NUMBER(3)                                                                                                                                                                                     
					valueList.add(siteCode);//SITE_CODE                               CHAR(5)                                                                                                                                                                                       
					valueList.add(itemCode);//ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
					valueList.add(lotNo);//LOT_NO                                  CHAR(15)                                                                                                                                                                                      
					valueList.add(lotSl);//LOT_SL                                  CHAR(15)                                                                                                                                                                                      
					valueList.add(allocQty);//QUANTITY                                NUMBER(14,3)                                                                                                                                                                                  
					valueList.add(remarks);//REMARKS                                 VARCHAR2(60)                                                                                                                                                                                  
					valueList.add(""); //REAS_CODE                               CHAR(5)                                                                                                                                                                                       
					valueList.add(locCode); //LOC_CODE                                CHAR(8)                                                                                                                                                                                       
					valueList.add("");//EXP_LEV                                 VARCHAR2(20)                                                                                                                                                                                  
					valueList.add("");//POTENCY_ADJ                             NUMBER(14,3)                                                                                                                                                                                  
					valueList.add("");//ISSUE_QTY                               NUMBER(14,3)                                                                                                                                                                                  
					valueList.add("");//DEALLOC_QTY                             NUMBER(14,3)                                                                                                                                                                                  
					valueList.add("");//ALLOC_QTY                               NUMBER(14,3)                                                                                                                                                                                  
					valueList.add("");//DEALLOCATED                             CHAR(1)    

					itemValueList = (ArrayList) invAllocDetMap.get(parentItem)
					itemValueList.add(valueList);
					invAllocDetMap.put(parentItem, itemValueList);

				}

				if( runningSupplyStr.trim().length() > 0)
				{
					runningSupply = runningSupplyStr==null?0:Double.parseDouble(runningSupplyStr);
				}

				if( qtyReqdStr.trim().length() > 0)
				{
					qtyReqd = qtyReqdStr==null?0:Double.parseDouble(qtyReqdStr);
				}
				if( allocQtyStr.trim().length() > 0)
				{
					allocQty = allocQtyStr==null?0:Double.parseDouble(allocQtyStr);
				}


				String refSer = "S-ORD" ; 
				// for new 17/06/14
				HashMap hashMap = null;
				hashMap = new HashMap();
				hashMap.put("tran_date",date);
				hashMap.put("ref_ser",refSer);
				hashMap.put("ref_id",saleOrder);
				hashMap.put("ref_line",lineNo);					
				hashMap.put("site_code",siteCode);
				hashMap.put("item_code",itemCode);
				hashMap.put("loc_code",locCode);
				hashMap.put("lot_no",lotNo);
				hashMap.put("lot_sl",lotSl);
				hashMap.put("alloc_qty",allocQty); 
				hashMap.put("chg_user",chgUser);
				hashMap.put("chg_term",chgTerm);
				hashMap.put("chg_date", date );
				System.out.println("CALLING updateInvallocTrace() --");
				invAllocTrace = new InvAllocTraceBean();
				errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
				System.out.println("updateInvallocTrace  1....."+errString);
				if ( errString != null && errString.trim().length() > 0 )
				{
					System.out.println("@@@@@@rolledback errString :::"+ errString );
					conn.rollback();
					return errString;
				}
				// end 1706/14

			}//end if

			// for insertion logic
			Set s1=invAllocateMap.entrySet(); 
			Iterator itr=s1.iterator();

			System.out.println("@@@@@final---keyMap["+invAllocateMap+"]]");

			while(itr.hasNext())
			{ 
				for ( int i=0; i < invAllocateMap.size() ; i++ )
				{
					Map.Entry map=(Map.Entry)itr.next(); 
					{ 
						String mapKey =  map.getKey().toString(); 

						// for inserting header

						ArrayList hdrValueList = new ArrayList();
						hdrValueList =    (ArrayList) invAllocateMap.get(mapKey);
						int k=0;
						sql = " insert into inv_allocate ( TRAN_ID,TRAN_DATE,TRAN_TYPE,WORK_ORDER,SITE_CODE,ITEM_CODE," +
							  " CHG_DATE,CHG_USER,CHG_TERM,REMARKS,CONFIRMED,CONF_DATE,STATUS,STATUS_DATE )" +
							  " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
								System.out.println("header sql :"+sql);
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,tranId);
								pstmt.setDate(2,date);   // TRAN_DATE                       
								pstmt.setString(3,hdrValueList.get(k++).toString());   // TRAN_TYPE                               VARCHAR2(3)                                                                                                                                                                                                      
								pstmt.setString(4,hdrValueList.get(k++).toString());   //  WORK_ORDER                              CHAR(10)                                                                                                                                                                                                                                                   
								pstmt.setString(5,hdrValueList.get(k++).toString());   // SITE_CODE                               CHAR(5)                                                                                                                                                                                       
								pstmt.setString(6,hdrValueList.get(k++).toString());   //  ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
								pstmt.setString(7,hdrValueList.get(k++).toString());   //CHG_DATE                                DATE                                                                                                                                                                                          
								pstmt.setString(8,hdrValueList.get(k++).toString());   //CHG_USER                                CHAR(10)                                                                                                                                                                                      
								pstmt.setString(9,hdrValueList.get(k++).toString());   // CHG_TERM                                CHAR(15)                                                                                                                                                                                      
								pstmt.setString(10,hdrValueList.get(k++).toString());   //REMARKS                                 VARCHAR2(40)                                                                                                                                                                                  
								pstmt.setString(11,hdrValueList.get(k++).toString());   //CONFIRMED                               CHAR(1)                                                                                                                                                                                       
								pstmt.setString(12,hdrValueList.get(k++).toString());   //CONF_DATE                               DATE                                                                                                                                                                                          
								pstmt.setString(13,hdrValueList.get(k++).toString());   //STATUS                                  CHAR(1)                                                                                                                                                                                       
								pstmt.setString(14,hdrValueList.get(k++).toString());   //STATUS_DATE  

								int hdrCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;

						// for inserting detail

								int  detCnt = 0;
								ArrayList detValList = new ArrayList();
								lineNo="0";

								detValList =    (ArrayList) invAllocDetMap.get(mapKey);

								for( int m=0; m < detValList.size(); m++)
								{
									lineNo = "" + Integer.parseInt(lineNo==null?"0":lineNo)+1;
									ArrayList detValueList = new ArrayList();
									detValueList = (ArrayList) detValueList.get(m);

									k=0;
									sql = " insert into inv_alloc_det ( TRAN_ID,LINE_NO,ITEM_CODE,LOT_NO,LOT_SL,QUANTITY,REMARKS,REAS_CODE," +
										  " LOC_CODE,EXP_LEV,POTENCY_ADJ,ISSUE_QTY,DEALLOC_QTY,ALLOC_QTY,DEALLOCATED )" +
										  " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
											System.out.println("detail sql :"+sql);
											pstmt = conn.prepareStatement(sql);

											pstmt.setString(1,tranId);
											pstmt.setString(2,lineNo); ////LINE_NO                        NOT NULL NUMBER(3)                                                                                                                                                                                     
											pstmt.setString(3,detValueList.get(k++).toString()); //SITE_CODE                               CHAR(5)                                                                                                                                                                                       
											pstmt.setString(4,detValueList.get(k++).toString()); //ITEM_CODE                               CHAR(10)                                                                                                                                                                                      
											pstmt.setString(5,detValueList.get(k++).toString()); //LOT_NO                                  CHAR(15)                                                                                                                                                                                      
											pstmt.setString(6,detValueList.get(k++).toString()); //LOT_SL                                  CHAR(15)                                                                                                                                                                                      
											pstmt.setString(7,detValueList.get(k++).toString()); //QUANTITY                                NUMBER(14,3)                                                                                                                                                                                  
											pstmt.setString(8,detValueList.get(k++).toString()); //REMARKS                                 VARCHAR2(60)                                                                                                                                                                                  
											pstmt.setString(9,detValueList.get(k++).toString()); //REAS_CODE                               CHAR(5)                                                                                                                                                                                       
											pstmt.setString(10,detValueList.get(k++).toString()); //LOC_CODE                                CHAR(8)                                                                                                                                                                                       
											pstmt.setString(11,detValueList.get(k++).toString()); //EXP_LEV                                 VARCHAR2(20)                                                                                                                                                                                  
											pstmt.setString(12,detValueList.get(k++).toString()); //POTENCY_ADJ                             NUMBER(14,3)                                                                                                                                                                                  
											pstmt.setString(13,detValueList.get(k++).toString()); //ISSUE_QTY                               NUMBER(14,3)                                                                                                                                                                                  
											pstmt.setString(14,detValueList.get(k++).toString()); //DEALLOC_QTY                             NUMBER(14,3)                                                                                                                                                                                  
											pstmt.setString(15,detValueList.get(k++).toString()); //ALLOC_QTY                               NUMBER(14,3)                                                                                                                                                                                  
											pstmt.setString(16,detValueList.get(k++).toString()); //DEALLOCATED                             CHAR(1)  

											detCnt = pstmt.executeUpdate();
											pstmt.close();
											pstmt = null;	

								}

								System.out.println("Inserted row :::hdrCnt ["+hdrCnt+"]::detCnt["+detCnt+"]");
					}	 
				} 
			}

				 */

			}	
			// end insertion logic
		}//try end
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in MrpWizPrsSaveBean :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The SQLException occurs in MrpWizPrsSaveBean  :"+e);			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally 
		{
			try
			{

				if (errString == null || errString.trim().length() == 0)
				{
					System.out.println("@@@@@@commit errString :::"+ errString );
					conn.commit();
					//return errString;
				}

				if (pstmt!= null)
				{
					pstmt = null;
				}
				if( conn != null )
				{
					conn.close();
					conn=null;
				}
			}
			catch(Exception e)
			{
				System.out.println("The SQLException occurs in MrpWizPrsSaveBean  :"+e);			
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return "";
	}

	private String generateTranId( String windowName,  String siteCode, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		java.sql.Timestamp currDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			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){}
		}
		return tranId;
	}//generateTranTd()

	/*
	private String generateTranId( String windowName, String siteCode, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		java.sql.Timestamp currDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";  // w_mrp_allocate
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;    
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			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){}
		}
		return tranId;
	}//generateTranTd()

	 */


	/*
	private String updateInvallocTrace(InvAllocTrace invallocTrace, Connection conn) throws ITMException
	{
		String errString = "";
		String errCode = "" ;
		String sql = null;
		String sqlUpdate = null;
		String keyStringQuery = null;
		java.sql.Date tranDate = invallocTrace.getTranDate();
		String refSer = invallocTrace.getRefSer();
		String refId = invallocTrace.getRefId();
		String refLine = invallocTrace.getRefLine();
		String siteCode = invallocTrace.getSiteCode();
		String itemCode = invallocTrace.getItemCode();
		String locCode = invallocTrace.getLocCode();
		String lotNo = invallocTrace.getLotNo();
		String lotSl = invallocTrace.getLotSl();
		double allocQty = invallocTrace.getAllocQty();
		String chgUser = invallocTrace.getChgUser();
		String chgTerm = invallocTrace.getChgTerm();
		String chgWin = invallocTrace.getChgWin();
		java.sql.Date chgDate = new java.sql.Date(System.currentTimeMillis());
		//Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rSet = null ;
		Statement stmt = null;
		String tranId = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

		try
		{
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
			System.out.println("keyStringQuery--------->>"+keyStringQuery);
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");
			//connDriver = null;
			stmt = conn.createStatement();
			rSet = stmt.executeQuery(keyStringQuery);
			System.out.println("keyString :"+rSet.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString(1);
				keyCol = rSet.getString(2);
				tranSer1 = rSet.getString(3);				
			}

			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String tranDateStr = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);

			String xmlValues = "";
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<dummy>D</dummy>";
			xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>";
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);

			sql = "INSERT INTO INVALLOC_TRACE (TRAN_ID, TRAN_DATE, REF_SER, REF_ID,"
					+"REF_LINE,ITEM_CODE, SITE_CODE, LOC_CODE,LOT_NO, LOT_SL, ALLOC_QTY, CHG_WIN," 
					+"CHG_USER, CHG_TERM, CHG_DATE )VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			pstmt = conn.prepareStatement(sql);

			System.out.println("trans id="+tranId);
			System.out.println("tranDate="+tranDate);
			System.out.println("refSer="+refSer);
			System.out.println("refId="+refId);
			System.out.println("refLine="+refLine);
			System.out.println("itemCode="+itemCode);
			System.out.println("siteCode="+siteCode);
			System.out.println("locCode="+locCode);
			System.out.println("lotNo="+lotNo);
			System.out.println("LOT_SL="+lotSl);
			System.out.println("LLOC_QTY="+allocQty);
			System.out.println("chgWin="+chgWin);
			System.out.println("chgUser="+chgUser);
			System.out.println("chgTerm="+chgTerm);
			System.out.println("chgDate="+chgDate);

			pstmt.setString(1,tranId);
			pstmt.setDate(2,tranDate);
			pstmt.setString(3,refSer);
			pstmt.setString(4,refId);
			pstmt.setString(5,refLine);
			pstmt.setString(6,itemCode );
			pstmt.setString(7,siteCode);
			pstmt.setString(8,locCode);
			pstmt.setString(9,lotNo );
			pstmt.setString(10,lotSl);
			pstmt.setDouble(11,allocQty);
			pstmt.setString(12,chgWin);
			pstmt.setString(13,chgUser);
			pstmt.setString(14,chgTerm);
			pstmt.setDate(15,chgDate);
			pstmt.executeUpdate();
			System.out.println("insertion of sql inside updateInvallocTrace success on date "+ chgDate);

			System.out.println("Stock Updated...............................");
			if(allocQty >= 0)
			{

				sqlUpdate = "UPDATE STOCK SET ALLOC_QTY =(CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + ? "
						+"WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
				pstmt = conn.prepareStatement(sqlUpdate);
				pstmt.setDouble(1,allocQty);
				pstmt.setString(2,itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4,locCode);
				pstmt.setString(5,lotNo);
				pstmt.setString(6,lotSl);
				pstmt.executeUpdate();
			}
			System.out.println("Updated End.");
		}
		catch(SQLException e)
		{
			System.out.println("SQLException :updateInvallocTrace : "  + sqlUpdate + "\n" +e.getMessage());
			System.out.println("ALLOC_QTY : " + allocQty);
			System.out.println("ITEM_CODE : " + itemCode);
			System.out.println("SITE_CODE : " + siteCode);
			System.out.println("LOC_CODE : " + locCode);
			System.out.println("LOT_NO : " + lotNo);
			System.out.println("LOT_SL : " + lotSl);
			errString = e.getMessage();
			e.printStackTrace();
			return errString;

		}
		catch(Exception e)
		{
			System.out.println("manohar :updateInvallocTrace connection rollback:"  + sqlUpdate + "\n" +e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString;	

		}
		finally
		{
			try
			{
				//if(conn != null)
				//{	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				//conn.close();
				//conn = null;
				//}
			}
			catch(Exception e)
			{
				errString = e.getMessage(); 
				e.printStackTrace();
				return errString;	
			}
			return errString;	
		}

	}

	 */








}