package ibase.webitm.ejb.mfg;
// BD0783 replaced with BD0783 -- 14/01/09
/*
1. truncate table plan_hdr;
2. add sale_order and line_no, seq_no in plan_header
	alter table plan_hdr add sale_ordr char(10);
	alter table plan_hdr add line_no char(3);
	alter table plan_hdr add seq_no number(10);
3. make seq_no not null
	alter table plan_hdr modify seq_no number(10) not null;
4. Drop primay key of plan_hdr
	alter table plan_hdr drop constraint plan_hdr_x;
	alter table plan_hdr drop constraint planhdr_x;

5. Create new primary key with sale_order and line_no
	alter table add constraint plan_hdr_x primary key (site_code,item_code,seq_no) using index tablespace itm_mfg_i1;

6. in of case mfgtype = "O" create workorder instead of mps order
*/
import org.w3c.dom.*;
import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;
import java.io.*;
import java.io.FileInputStream;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.File;
import java.text.SimpleDateFormat;
import java.text.NumberFormat;
import java.text.DecimalFormat;

import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import ibase.system.config.*;
import ibase.utility.CommonConstants;
import javax.ejb.Stateless; // added for ejb3
// 12/04/10 manoharan MF90BHU003
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.xssf.usermodel.*;
//import org.apache.poi.ss.util.CellRangeAddress;
//import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.w3c.dom.*;
import javax.xml.parsers.*;


// end 12/04/10 manoharan MF90BHU003

@Stateless // added for ejb3


public class RunMRPPrc extends ProcessEJB implements RunMRPPrcLocal,RunMRPPrcRemote //SessionBean
{

	String errString = "";
	String siteCodeFrom = "0";
	String siteCodeTo = "zz";
	String itemCodeFrom = "0";
	String itemCodeTo = "zz";
	String itemSerFrom = "0";
	String itemSerTo = "zz";
	String disPlan = null;
	String mfgPlan = null;
	String matPlan = null;
	String verifier = null;
	// 19-12-2005 manoharan
	java.sql.Timestamp dateFrom =  null;
	java.sql.Timestamp dateTo =  null;
	String sDateFrom = "";
	String sDateTo = "";
	String dbDateFrom = "";
	String dbDateTo = "";
	String dateFromString = "";
	String dateToString = "";
	// end 19-12-2005 manoharan
	// 01-10-2007 manoharan
	String sRunDate = null;
	java.sql.Timestamp runDate =  null;
	String dbRunDate = null;
	String runDateString = "";
	// end 01-10-2007 manoharan

	String standingOrdTypes = "";
	String mrpExpediate = "";
	String mrpTrackTranId = "";
	String mrpDynMinStk = "";
	String roundReqTo = "";
	String minOrderChk = "";
	String deptCodeMfg = "";
	String madeToOrder = null;
	String madeToStock = null;
	String balIntersite = "";
    String userId;
    String genIndent = "";

	int MrpClubbingHorizon;
	int processCycle;
	int expediateDays;

	private ArrayList adpeList = new ArrayList();
	private ArrayList interSiteList = new ArrayList();
	private ArrayList surplusList = new ArrayList();
	private ArrayList surplusListOriginal = new ArrayList();

	private ArrayList indList = new ArrayList();
	private ArrayList mpsList = new ArrayList();
	private ArrayList indDemList = new ArrayList();
	private ArrayList disList = new ArrayList();

    Connection conn = null;

	transient GenericUtility genericUtility = null;

	HashMap itemCodeMap = new HashMap();
	HashMap siteCodeMap = new HashMap();
	HashMap itemHashMap = new HashMap();
        Set itemList = new HashSet();
        Set cyclicCheck = null;
	String logInSite = "";
	String chgUser = "";
	String chgTerm = "";
        HashMap hmap=new HashMap();
        int level=0;


	CommonConstants commonConstants = new CommonConstants();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

	CyclicItem cyclicItem = new CyclicItem();//Added by JIten 07/11/06
	FileWriter fw = null;
	FileWriter fw1 = null;
	FileWriter fwInvalidBom = null;
	String validBomItemCode = null;
	boolean invaldBomExists = false;

	/*public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("RunMRPPrcEJB ejbCreate called.........");
		}
		catch (Exception e)
		{
			System.out.println("Exception :ProcessEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}

	public void ejbRemove()
	{
	}

	public void ejbActivate()
	{
	}

	public void ejbPassivate()
	{
	}*/

/*	public String process() throws RemoteException,ITMException
	{
		System.out.println("Called process().....");
		return "";
	}
*/
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{

		System.out.println("Called process(string, string).....");
		String resultString = null;
		Document dom = null,dom1 = null;
		String  retString = null;
		long startTime =  0,endTime = 0,totalTime = 0, totalHrs = 0, totlMts = 0, totSecs = 0; // Added by Jiten 13/11/06 -
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				//System.out.println("XML String :" + xmlString);
				genericUtility = GenericUtility.getInstance();
				dom = genericUtility.parseString(xmlString);
			}
		//added by Jiten 13/11/06 -
		startTime = System.currentTimeMillis();
		retString = getData(dom, dom1,windowName, xtraParams);
		endTime = System.currentTimeMillis();
		totalTime = endTime - startTime;
		System.out.println("Total Time Spend :: "+totalTime+" Milliseconds");

		totSecs = (int)(((double)1/1000)*(totalTime));
		totalHrs = (int)(totSecs / 3600);
		totlMts = (int)(((totSecs - ( totalHrs *  3600))/ 60));
		totSecs = (int) ( totSecs - (( totalHrs *  3600) + (totlMts * 60)) );

		System.out.println("Total Time Spend :: " + totalHrs +" Hours " + totlMts + " Minutes " + totSecs + " seconds");
		//End adding 13/11/06 -
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :Process (String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("Returning from Process :: " + retString);
	    return retString;
	}

	public String getData(Document dom, Document dom1, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		errString = "";
		String resultString = "";
		ResultSet rs = null;
		//System.out.println("Initial Size of Item Has Map :" + itemHashMap.size());
		logInSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		try
		{
			//Added by Jiten 07/11/06
			//this.fw =  new FileWriter(CommonConstants.JBOSSHOME+"\\log\\RepeatBom.log");
			this.fw=new FileWriter(CommonConstants.JBOSSHOME + File.separator +"log"+File.separator+"RepeatBom.log");
			this.fw.write("Site Code		Bom Code		Item Code		CyclicItemCode"+"\r\n");
			//End
			//Added by jiten 10/11/06
			//this.fw1 =  new FileWriter(CommonConstants.JBOSSHOME+"\\log\\SkipItem.log");
			this.fw1=new FileWriter(CommonConstants.JBOSSHOME + File.separator +"log"+File.separator+"SkipItem.log");
			this.fw1.write("Site Code	 Product Code	 ItemCode Skipped"+"\r\n");
			//End

			// 19-01-2007 manohar
			this.fwInvalidBom =  new FileWriter(CommonConstants.JBOSSHOME + File.separator +"log"+File.separator+"InValidBom.log");
			this.fwInvalidBom.write("Site Code  Item Code   Bom Code     Message\r\n");
			this.fwInvalidBom.write("=========  ==========  ==========   ========\r\n");
			//End 19-01-2007 manohar

			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

            chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
            chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			CommonConstants.setIBASEHOME();
			conn = connDriver.getConnectDB("DriverITM");
   			conn.setAutoCommit(false);
   			connDriver = null;
			// validate the filter parameter entered by the user
			errString = validateFilterParams(dom);
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error validating filter parameters:..." + errString);
				return errString;
			}

			//	Populating Common Parameters...
			errString = "";
			//System.out.println("Populating Common Parameters...");
			errString = populateCommonParameters();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While Populating Common Parameters:..." + errString);
				return errString;
			}

			//	Populating Site Code Map.......
			System.out.println("Populating Site Code Map.......");
			siteCodeTo = siteCodeTo.trim() + "     ";
			siteCodeTo = siteCodeTo.substring(0,5);// + "z";
			errString = populateSiteMap();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While Populating Site:..." + errString);
				return errString;
			}

			//	Populating Item Code Map.......
			itemCodeTo = itemCodeTo.trim() + "          ";
			itemCodeTo = itemCodeTo.substring(0,10);// + "z";

			itemSerTo = itemSerTo.trim() + "     ";
			itemSerTo = itemSerTo.substring(0,5);// + "z";

			System.out.println("Populating Item Code Map.......");
			errString = populateItemMap();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While Populating Item code:..." + errString);
				return errString;
			}

			//	Populating All Items...........
			//  if MatPlan equals 'Y' then call method populateAllItem()
			if( matPlan.equals("Y") )
			{
				System.out.println("Populating All Items...........");
				errString = populateAllItem();

				if ( errString != null && errString.trim().length() > 0 )
				{
					System.out.println("Error While Populating All Items:..." + errString);
					return errString;
				}
				// 19-01-2007 manoharan
				if(verifier != null && verifier.equalsIgnoreCase("Y"))
				{
					if (invaldBomExists)
					{
						errString = itmDBAccessEJB.getErrorString("","VTBOMUCON","","",conn);
						return errString;

					}
				}
				// end 19-01-2007 manoharan
			}
			// generate the ADPElement array here..

			errString = populateADPElementList();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While Populating ADPEList:..." + errString);
				return errString;
			}
			if( disPlan.equals("Y") && balIntersite.equals("Y"))
			{
				errString = populateInterSiteDistance();
				if ( errString != null && errString.trim().length() > 0 )
				{
					System.out.println("Error While Optimizing ADPEList:..." + errString);
					return errString;
				}
				populateSurplusList();
				System.out.println("Size of populateSurplusList : " + surplusList.size());
				System.out.println("Size of populateInterSiteDistance : " + interSiteList.size());
			}
			// demand/supply to be inserted in separate table

			//
			errString = mrpOptimize();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While Optimizing ADPEList:..." + errString);
				return errString;
			}

            errString = generatePlanDemand();
			if ( errString != null && errString.trim().length() > 0 )
			{
				System.out.println("Error While generating Plan Demand:..." + errString);
				return errString;
			}
			// 31/01/2008 manoharan not to insert plan demand in case of verification
			if(verifier != null && verifier.equalsIgnoreCase("N"))
			{
	            errString  = InsertPlanDemand();
				if ( errString != null && errString.trim().length() > 0 )
				{
					System.out.println("Error While inserting Plan Demand:..." + errString);
					return errString;
				}
				//Added by Jiten
				String sql = "";
				String lsWhere = "";
				int noOfPlan = 0 ;
				Statement stmt = null;
				if(matPlan.equalsIgnoreCase("Y") && (mfgPlan.equalsIgnoreCase("Y")) && (disPlan.equalsIgnoreCase("Y")) )
				{
					lsWhere = "";
				}
				else
				{
					if(mfgPlan.equalsIgnoreCase("Y")){
						lsWhere = lsWhere + "'M'";
						noOfPlan = noOfPlan + 1;
					}
					if(disPlan.equalsIgnoreCase("Y"))
					{
						if(noOfPlan == 0)
						{
							lsWhere = "'D'";
						}
						else
						{
							lsWhere = lsWhere + ",'D'";
						}
						noOfPlan = noOfPlan + 1;
					}
					if(matPlan.equalsIgnoreCase("Y"))
					{
						if(noOfPlan == 0)
						{
							lsWhere = "'P'";
						}
						else
						{
							lsWhere = lsWhere + ",'P'";
						}
						noOfPlan = noOfPlan + 1;
					}
					if (noOfPlan == 1)
					{
						lsWhere = " AND SUPP_SOUR = " + lsWhere + "";
					}
					else if (noOfPlan > 1)
					{
						lsWhere = " AND SUPP_SOUR IN (" + lsWhere + ")";
					}
				}

				// uncommented on 04/04/11 manoharan  foreign key defined with on delete cascade
				sql = "DELETE FROM PLAN_DET WHERE SITE_CODE >='"+siteCodeFrom+"' "
					+ " AND SITE_CODE <= '"+siteCodeTo+ "' ";
					//+ " AND ITEM_CODE >= '"+itemCodeFrom+"' "
					//+ " AND ITEM_CODE <= '"+itemCodeTo+"'" ;//+ lsWhere;
				stmt = conn.createStatement();
				int deletedInDetail = stmt.executeUpdate(sql);
				stmt.close();

				System.out.println("Deleted in detail For Material Plan: " + deletedInDetail);

				sql = "DELETE FROM PLAN_HDR WHERE SITE_CODE >='"+siteCodeFrom.trim()+"' "
					+ " AND SITE_CODE <= '"+siteCodeTo+ "' "  + lsWhere;
					//+ " AND ITEM_CODE >= '"+itemCodeFrom.trim()+"' "
					//+ " AND ITEM_CODE <= '"+itemCodeTo+"'" + lsWhere;

				System.out.println("Delete plan_hdr Sql : " + sql);
				stmt = conn.createStatement();
				int deletedInHeader = stmt.executeUpdate(sql);

				System.out.println("Deleted in Header For Material Plan: " + deletedInHeader);
				stmt.close();
				//End adding

				//commented by Jiten
				/*String sql = "DELETE FROM PLAN_HDR";
				Statement stmt = conn.createStatement();
				int deletedInHeader = stmt.executeUpdate(sql);
				stmt.close();
				System.out.println("Deleted in Header : " + deletedInHeader);

				sql = "DELETE FROM PLAN_DET";
				stmt = conn.createStatement();
				int deletedInDetail = stmt.executeUpdate(sql);
				System.out.println("Deleted in Detail : " + deletedInDetail);
				stmt.close();*/
				//End

				errString  = InsertPlanTable();
				if ( errString != null && errString.trim().length() > 0 )
				{
					System.out.println("Error While inserting Plan HdrDet:..." + errString);
					return errString;
				}
			}

			resultString = setValuesInBrow();
			Write2XSL();
		}
		catch (SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :process(Document dom, Document dom2, String windowName, String xtraParams):" + se.getMessage() + ":");
			this.errString = genericUtility.createErrorString(se);
			se.printStackTrace();
			throw new ITMException(se);
		}
		catch (Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :process(Document dom, Document dom2, String windowName, String xtraParams):" + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try{
				if(conn!=null)
                            {
                                conn.close();
                                conn=null;
                            }
				this.fw.flush();//Added by Jiten 07/11/06
				this.fw.close();//Added by Jiten 07/11/06
				this.fw1.flush();//Added by JIten 10/11/06
				this.fw1.close();//Added by JIten 10/11/06
				this.fwInvalidBom.flush(); // 19-01-2007 manohar
				this.fwInvalidBom.close(); // 19-01-2007 manohar
				cleanup();
			}catch(Exception e){}

		}
		return resultString; //Jiten - 03/11/06 Moved here from Finally block
	}

	private String validateFilterParams(Document dom) throws ITMException
	{
		String processCycleString = null;
		try
		{
			//	Fetching SiteCodeFrom from Entered by User in GUI
			verifier = genericUtility.getColumnValue("verifier",dom);
			//System.out.println("verifier ::"+verifier);
			siteCodeFrom = genericUtility.getColumnValue("site_code__from",dom);
			if ( siteCodeFrom == null || siteCodeFrom.trim().length() == 0 )
			{
				siteCodeFrom = "";
				System.out.println("Site Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Site Code From............." + siteCodeFrom);
			//	Fetching SiteCodeTo from Entered by User in GUI
			siteCodeTo = genericUtility.getColumnValue("site_code__to",dom);
			if ( siteCodeTo == null || siteCodeTo.trim().length() == 0 )
			{
				siteCodeTo = "";
				System.out.println("Site Code To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Site Code To..............." + siteCodeTo);
			//	Fetching ItemCodeFrom from Entered by User in GUI
			itemCodeFrom = genericUtility.getColumnValue("item_code__from",dom);
			if ( itemCodeFrom == null || itemCodeFrom.trim().length() == 0 )
			{
				itemCodeFrom = "";
				System.out.println("Item Code From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Code From............." + itemCodeFrom);
			//	Fetching ItemCodeTo from Entered by User in GUI
			itemCodeTo = genericUtility.getColumnValue("item_code__to",dom);
			if ( itemCodeTo == null || itemCodeTo.trim().length() == 0 )
			{
				itemCodeTo = "";
				System.out.println("Item Code To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Code To............." + itemCodeTo);
			//	Fetching DateFrom from Entered by User in GUI
			sDateFrom = genericUtility.getColumnValue("date_from",dom);
			if ( sDateFrom == null || sDateFrom.trim().length() == 0 )
			{
				sDateFrom = "";
				System.out.println("From Date is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}

			System.out.println("From Date............." + sDateFrom);
			//	Fetching dateTo from Entered by User in GUI
			sDateTo = genericUtility.getColumnValue("date_to",dom);

			if ( sDateTo == null || sDateTo.trim().length() == 0 )
			{
				sDateTo = "";
				System.out.println("To Date is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}

			System.out.println("To Date............." + sDateTo);
			//	Fetching ItemSerFrom from Entered by User in GUI
			itemSerFrom = genericUtility.getColumnValue("item_ser__from",dom);
			if ( itemSerFrom == null || itemSerFrom.trim().length() == 0 )
			{
				itemSerFrom = "";
				System.out.println("From Item Series is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Item Series From............." + itemSerFrom);
			//	Fetching ItemSerTo from Entered by User in GUI
			itemSerTo = genericUtility.getColumnValue("item_ser__to",dom);
			if ( itemSerTo == null || itemSerTo.trim().length() == 0 )
			{
				itemSerTo = "";
				System.out.println("Item Series To is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("To Item Series............." + itemSerTo);
			//	Fetching DisPlan from Entered by User in GUI
			disPlan = genericUtility.getColumnValue("dis_plan",dom);
			System.out.println("Dis Plan............."+disPlan);
			if ( disPlan == null || disPlan.trim().length() == 0 )
			{
				disPlan = "";
				System.out.println("Dis Plan is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Dis Plan............." + disPlan);
			//	Fetching MfgPlan from Entered by User in GUI
			mfgPlan = genericUtility.getColumnValue("mfg_plan",dom);
			if ( mfgPlan == null || mfgPlan.trim().length() == 0 )
			{
				mfgPlan = "";
				System.out.println("Mfg Plan is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Mfg Plan............." + mfgPlan);
			//	Fetching MatPlan from Entered by User in GUI
			matPlan = genericUtility.getColumnValue("mat_plan",dom);
			if ( matPlan == null || matPlan.trim().length() == 0 )
			{
				matPlan = "";
				System.out.println("Mat Plan is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Mat Plan............." + matPlan);

			processCycleString = genericUtility.getColumnValue("process_cycle",dom);
			if ( processCycleString == null || processCycleString.trim().length() == 0 )
			{
				System.out.println("Process Cycle is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			try
			{
				processCycle = Integer.parseInt(processCycleString);
			}
			catch (Exception e)
			{
				processCycle = 1;
			}
			System.out.println("Process Cycle............." + processCycle);

			//	Fetching Made To Stock from Entered by User in GUI
			madeToStock = genericUtility.getColumnValue("made_to_stock",dom);
			if ( madeToStock == null || madeToStock.trim().length() == 0 )
			{
				madeToStock = "";
				System.out.println("Made To Stock is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Made To Stock............." + madeToStock);

			//	Fetching Made To Oerder from Entered by User in GUI
			madeToOrder = genericUtility.getColumnValue("made_to_order",dom);
			if ( madeToOrder == null || madeToOrder.trim().length() == 0 )
			{
				madeToOrder = "";
				System.out.println("Made To Order is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Made To Oerder............."+madeToOrder);

			//	Fetching Balance Intersite from Entered by User in GUI
			balIntersite = genericUtility.getColumnValue("balance_intersite",dom);
			if ( balIntersite == null || balIntersite.trim().length() == 0 )
			{
				balIntersite = "";
				System.out.println("Balance Intersite is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			genIndent = genericUtility.getColumnValue("gen_ind",dom);
			if ( genIndent == null || genIndent.trim().length() == 0 )
			{
				genIndent = "";
				System.out.println("Generate Indent is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Generate Indent............." + genIndent);

			// 19-12-2005 manoharan convert string date to sql timestamp
			dbDateFrom = sDateFrom;
			dbDateTo = sDateTo;
			sDateFrom = genericUtility.getValidDateString(sDateFrom, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dbDateFrom = genericUtility.getValidDateString(dbDateFrom, genericUtility.getApplDateFormat(), "dd-MMM-yyyy");
			System.out.println("sDateFrom : " + sDateFrom);
			//System.out.println();
			dateFrom =  java.sql.Timestamp.valueOf(sDateFrom + " 00:00:00");
			sDateTo = genericUtility.getValidDateString(sDateTo, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			dbDateTo = genericUtility.getValidDateString(dbDateTo, genericUtility.getApplDateFormat(), "dd-MMM-yyyy");
			dateTo = java.sql.Timestamp.valueOf(sDateTo + " 00:00:00");
			System.out.println("Db Date from :" + dbDateFrom);
			System.out.println("Db Date to   :" + dbDateTo);
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			dateFromString = simpleDateFormat.format(dateFrom);
			dateToString = simpleDateFormat.format(dateTo);
			System.out.println("Db Date from [Timestamp]:" + dateFrom);
			System.out.println("Db Date to   [Timestamp]:" + dateTo);
			System.out.println("Db Date from [Timestamp : yyyy-MM-dd]:" + dateFromString);
			System.out.println("Db Date to   [Timestamp : yyyy-MM-dd]:" + dateToString);

			// end 19-12-2005 manoharan string converted to timestamp
			// 01-10-2007 manoharan
			sRunDate = genericUtility.getColumnValue("run_date",dom);
			if ( sRunDate == null || sRunDate.trim().length() == 0 )
			{
				sRunDate = "";
				System.out.println("Run Date is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			}
			System.out.println("Run Date............." + sRunDate);

			dbRunDate = genericUtility.getValidDateString(sRunDate, genericUtility.getApplDateFormat(), "dd-MMM-yyyy");
			sRunDate = genericUtility.getValidDateString(sRunDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			runDate =  java.sql.Timestamp.valueOf(sRunDate + " 00:00:00");
			runDateString = simpleDateFormat.format(runDate);
			// end 01-10-2007 manoharan
		}
		catch (Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :process(Document dom, Document dom2, String windowName, String xtraParams):" + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}
	// Populating Common Parameters i.e. :

	private String populateCommonParameters() throws ITMException
	{
		String  mrpClubbingHorizonString = "";
		String expediateDaysString = null;
		Statement stmt = null;
		String sql = "";
		ResultSet rs = null;
		errString = "";
		ArrayList arrStdOrdType = null;
		System.out.println("Database Name:........."+CommonConstants.DB_NAME);
		try
		{
			stmt = conn.createStatement();
			standingOrdTypes = itmDBAccessEJB.getEnvDis("999999", "STANDING_ORDTYPES",conn);
			if(standingOrdTypes.equals("NULLFOUND"))
			{
				standingOrdTypes = "";
			}
			System.out.println("standingOrdTypes:............."+standingOrdTypes);

			// ***** standingOrdTypes	to be changed to make an in ()
			// e.g original value will be R,S,JW, etc.
			// to convert in the form of "'R','S','JW'"

			arrStdOrdType = genericUtility.getTokenList(standingOrdTypes, ",");
			standingOrdTypes = "";
			for (int index = 0; index < arrStdOrdType.size() ; index++)
			{
				if(index == arrStdOrdType.size() - 1)
				{
					standingOrdTypes = standingOrdTypes.concat("'").concat((String)arrStdOrdType.get(index)).concat("'");
				}
				else
				{
					standingOrdTypes = standingOrdTypes.concat("'").concat((String)arrStdOrdType.get(index)).concat("',");
				}
			}
			System.out.println("New Str Ord Type :" +standingOrdTypes);

			sql = "SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE ='999999' AND VAR_NAME ='MRP_EXPEDIATE'";

			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				if(rs.getString(1) == null)
				mrpExpediate = "N";
				else
				mrpExpediate = rs.getString(1);
			}
			System.out.println("MrpExpediate:................."+mrpExpediate);
			rs.close();

			sql = "SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE ='999999' AND VAR_NAME ='EXPEDIATE_DAYS'";

			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				if(rs.getString(1) == null)
					expediateDaysString = "0";
				else
					expediateDaysString = rs.getString(1);
			}
			if (expediateDaysString.equals("NULLFOUND") || expediateDaysString == null)
			{
				expediateDaysString ="0";
			}
			try
			{
				expediateDays = Integer.parseInt(expediateDaysString);
			}
			catch (NumberFormatException e)
			{
				expediateDays = 0;
			}
			System.out.println("Expediate Days :..............."+expediateDays);
			rs.close();


			sql = "SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE ='999999' AND VAR_NAME ='MRP_TRACK_TRANID'";

			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				if(rs.getString(1) == null)
					mrpTrackTranId = "N";
				else
					mrpTrackTranId = rs.getString(1);
			}
			System.out.println("mrpTrackTranId:..............."+mrpTrackTranId);
			rs.close();

			mrpClubbingHorizonString = itmDBAccessEJB.getEnvDis("999999", "MRP_CLUBBING_HORIZON",conn);
			if(mrpClubbingHorizonString.equals("NULLFOUND"))
			{
				MrpClubbingHorizon = 0;
			}
			else
			{
				try
				{
					MrpClubbingHorizon = Integer.parseInt(mrpClubbingHorizonString);
				}
				catch (Exception e)
				{
					MrpClubbingHorizon = 0;
				}
			}
			System.out.println("MrpClubbingHorizon:..........."+MrpClubbingHorizon);


			mrpDynMinStk = itmDBAccessEJB.getEnvDis("999999", "MRP_DYN_MIN_STK",conn);
			if(mrpDynMinStk.equals("NULLFOUND"))
				mrpDynMinStk = "N";
			System.out.println("mrpDynMinStk:................."+mrpDynMinStk);


			roundReqTo = itmDBAccessEJB.getEnvDis("999999", "ROUND_REQ_TO",conn);
			if(roundReqTo.equals("NULLFOUND"))
				roundReqTo = "N";
			System.out.println("roundReqTo:................."+roundReqTo);


			minOrderChk = itmDBAccessEJB.getEnvDis("999999", "MIN_ORDER_CHK",conn);
			if(minOrderChk.equals("NULLFOUND"))
				minOrderChk = "N";
			System.out.println("minOrderChk:................."+minOrderChk);


			deptCodeMfg = itmDBAccessEJB.getEnvDis("999999", "DEPT_CODE_MFG",conn);
			if(deptCodeMfg.equals("NULLFOUND"))
			{
				errString = itmDBAccessEJB.getErrorString("","VTMRPDEPT","","",conn);
				return errString;
			}
			else
			{
				sql = "SELECT COUNT(1) FROM DEPARTMENT WHERE DEPT_CODE = '"+deptCodeMfg+"'";
				rs = stmt.executeQuery(sql);
				if(rs.next() && rs.getInt(1) > 0)
				{
					System.out.println("Department Code Mfg ["+deptCodeMfg+"] exists in deparment master");
				}
				else
				{
					System.out.println("Department Code Mfg  ["+deptCodeMfg+"] not exists in deparment master");
					errString = itmDBAccessEJB.getErrorString("","VTINVDEPT","","",conn);
				}
				rs.close();
			}
			System.out.println("deptCodeMfg:................."+deptCodeMfg);


			stmt.close();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :populateCommonParameters().." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :populateCommonParameters():...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (stmt != null)
			{
				stmt = null;
			}
			if (rs != null)
			{
				rs = null;
			}
			System.out.println("Error String :===> " + errString);
		}
		return errString; //moved here from finally block - Jiten - 03/11/06
	}

	// Populating Site Map

	private String populateSiteMap() throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		int count = 0;
		try
		{
			sql = "SELECT DISTINCT ";
			sql = sql + "CASE WHEN SITE_CODE__PLAN IS NULL THEN SITE_CODE ELSE SITE_CODE__PLAN END ";
			sql = sql + "FROM SITEITEM ";
			sql = sql + "WHERE ";
			sql = sql + "CASE WHEN SITE_CODE__PLAN IS NULL THEN SITE_CODE ELSE SITE_CODE__PLAN END >= ? ";
			sql = sql + "AND CASE WHEN SITE_CODE__PLAN IS NULL THEN SITE_CODE ELSE SITE_CODE__PLAN END <= ?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCodeFrom);
			pstmt.setString(2,siteCodeTo);
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				count++;
				siteCodeMap.put(rs.getString(1),new Integer(count));
				//System.out.println(rs.getString(1));

			}


		}
		catch(SQLException se)
		{
			System.out.println("SQLException : RunMRPPrcEJB :populateSiteMap()" +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :populateSiteMap():" + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch(Exception e){}
		}
		return errString;// Moved here as finally block does not complete normally - jiten 03/11/06
	}

	//	Poulating Item code
	private String populateItemMap() throws ITMException
	{

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		Set setSite = siteCodeMap.entrySet();
		Iterator itrSite = setSite.iterator();
		//System.out.println("Database Name:............"+CommonConstants.DB_NAME);
		try
		{
			if((CommonConstants.DB_NAME).equalsIgnoreCase("db2"))
			{
				sql = "SELECT CASE WHEN C.ITEM_CODE__PLAN IS NULL THEN C.ITEM_CODE ELSE C.ITEM_CODE__PLAN END ITEM_CODE,";
				sql = sql + "CASE WHEN B.ITEM_CODE IS NULL THEN C.ITEM_SER ELSE ";
				sql = sql + "CASE WHEN B.VALID_UPTO IS NULL THEN B.ITEM_SER ELSE C.ITEM_SER END END ITEM_SER, A.MASTER_SCH ";
				sql = sql + "FROM SITEITEM A LEFT OUTER JOIN ITEMSER_CHANGE B ON A.ITEM_CODE = B.ITEM_CODE, ITEM C ";
				sql = sql + "WHERE A.ITEM_CODE = C.ITEM_CODE ";
				sql = sql + "AND C.ITEM_CODE >= ? ";
				sql = sql + "AND C.ITEM_CODE <= ? ";
				sql = sql + "AND A.SITE_CODE = ? ";
				sql = sql + "AND CASE WHEN A.MASTER_SCH IS NULL THEN 'Y' ELSE A.MASTER_SCH END  <> 'N' ";
				sql = sql + "AND B.VALID_UPTO IS NULL ";
				sql = sql + "AND C.ITEM_SER >=? ";
				sql = sql + "AND C.ITEM_SER <=?  ORDER BY ITEM_CODE ASC";
			}
			else
			{
				sql = "SELECT CASE WHEN C.ITEM_CODE__PLAN IS NULL THEN C.ITEM_CODE ELSE C.ITEM_CODE__PLAN END ITEM_CODE, ";
				sql = sql + "CASE WHEN B.ITEM_CODE IS NULL THEN C.ITEM_SER ELSE  ";
				sql = sql + "CASE WHEN B.VALID_UPTO IS NULL THEN B.ITEM_SER ELSE C.ITEM_SER END END ITEM_SER, A.MASTER_SCH ";
				sql = sql + "FROM SITEITEM A, ITEMSER_CHANGE B, ITEM C ";
				sql = sql + "WHERE A.ITEM_CODE = C.ITEM_CODE ";
				sql = sql + "AND A.ITEM_CODE = B.ITEM_CODE (+) ";
				sql = sql + "AND C.ITEM_CODE >= ? ";
				sql = sql + "AND C.ITEM_CODE <= ? ";
				sql = sql + "AND A.SITE_CODE = ? ";
				sql = sql + "AND CASE WHEN A.MASTER_SCH IS NULL THEN 'Y' ELSE A.MASTER_SCH END  <> 'N' ";
				sql = sql + "AND B.VALID_UPTO IS NULL ";
				sql = sql + "AND C.ITEM_SER >= ? ";
				sql = sql + "AND C.ITEM_SER <= ? ORDER BY ITEM_CODE ASC";

			}

		//	System.out.println("sql :"+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,itemCodeFrom);
			pstmt.setString(2,itemCodeTo);
			pstmt.setString(4,itemSerFrom);
			pstmt.setString(5,itemSerTo);
			while(itrSite.hasNext())
			{
				Map.Entry siteMapEntry = (Map.Entry)itrSite.next();
				String siteCode = (String)siteMapEntry.getKey();

				pstmt.setString(3,siteCode);

				rs = pstmt.executeQuery();

				System.out.println("Populating item for site : "+siteCode);
				while(rs.next())
				{
                    System.out.println("rs.getString(1)--"+rs.getString(1));
					if(itemCodeMap.containsKey(rs.getString(1)))
					{
						System.out.println("Item Code already exists : "+rs.getString(1));
					}
					else
					{
						itemCodeMap.put(rs.getString(1),new Integer(itemCodeMap.size() + 1));
						//System.out.println("Item Code added : "+rs.getString(1));
					}
				}
			}
		}
		catch(SQLException se)
		{
			System.out.println("SQLException : RunMRPPrcEJB :populateItemMap()" +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :populateItemMap():" + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null){
					pstmt.close();
					pstmt = null;
				}
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch(Exception e)
			{}
		}
		return errString;//Moved here as finally block does not complete normally - Jiten - 03/11/06
	}

	// Populating All Items

	private String populateAllItem() throws ITMException
	{
		String bomCode = "";
		String suppSour = "";
		Set setSite = siteCodeMap.entrySet();
		Set setItem = itemCodeMap.entrySet();

		Iterator itrItem = setItem.iterator();
		ArrayList itemArr = new ArrayList();

		int count = 0;


		while(itrItem.hasNext())
		{
			Map.Entry itemMapEntry = (Map.Entry)itrItem.next();
			itemArr.add((String)itemMapEntry.getKey());
		}
		Iterator itrSite = setSite.iterator();
		try
		{

			while(itrSite.hasNext())
			{
				Map.Entry siteMapEntry = (Map.Entry)itrSite.next();
				//System.out.println("All items for site : " + (String)siteMapEntry.getKey());
				int ctr;
				for (ctr = 0; ctr < itemArr.size() ; ctr++)
				{
                                        System.out.println("(String)itemArr.get(ctr)--"+(String)itemArr.get(ctr));
					suppSour = getSuppSour((String)siteMapEntry.getKey(),(String)itemArr.get(ctr));
					suppSour = suppSour.trim();
					bomCode = getBomCode((String)siteMapEntry.getKey(),(String)itemArr.get(ctr));
                                        System.out.println("SuppSour---"+suppSour+"bomCode--"+bomCode);
					//System.out.println("populateAllItem(): Supp_Sour:..." + suppSour + "   Bom Code:..." + bomCode);
					if( ( (suppSour != null) && (!suppSour.equals("P")) ) && (bomCode != null) )
					{
                                            Set levelBom=new HashSet();
                                            level=1;
                                            levelBom.add(bomCode);
                                            hmap.put(new Integer(level),levelBom);
						//itemHashMap.put( (String)itemArr.get(ctr), new Integer(count) );
                         System.out.println("itemArr.get(ctr)--"+itemArr.get(ctr));
			             itemList.add((String)itemArr.get(ctr));
						//System.out.println("Exploding BOM for site : " + (String)siteMapEntry.getKey() + " Item : " + (String)itemArr.get(ctr) + " BOM : " + bomCode);
						//Added by Jiten 07/11/06
						cyclicItem.bomCode = bomCode;
						cyclicItem.itemCode = (String)itemArr.get(ctr);
						cyclicItem.siteCode = (String)siteMapEntry.getKey();
						//end

						validBomItemCode = cyclicItem.itemCode;	// 19-01-2007 manoharan
						cyclicCheck = new HashSet();
						errString = bomExplode((String)siteMapEntry.getKey(), bomCode);
						itemHashMap.clear();
						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While Calling method bomExplode(String, String):..."+errString);
							return errString;
						}
						level=0;
						hmap.clear();
					}
				}
                itemList.clear();

                }
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :populateAllItem()");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		return errString;
	}
private String bomExplode(String siteCode, String bomCode) throws ITMException
	{
		//PreparedStatement pstmt = null;
		Statement pstmt = null;
		PreparedStatement pstmtInner1 = null;
		PreparedStatement pstmtInner2 = null;
		ResultSet rsCount = null;
		String sql = "", sqlInner1 = "", sqlInner2 = "";
		ResultSet rs = null, rsInner1 = null, rsInner2 = null;
		String innerSuppSour = null;
		String innerBomCode = null;

		PreparedStatement pstmtValidBom = null;
		String sqlValidBom = "SELECT CONFIRMED, ACTIVE FROM BOM WHERE BOM_CODE = ?";
		ResultSet rsValidBom = null;
		boolean bomExists = false;
		String logMsg = null;
		String confirmedBom = null;
		String activeBom = null;

		try
		{

			// 19-01-2007 manoharan
			// check whether the bom is valid should be confirmed and active
			pstmtValidBom = conn.prepareStatement(sqlValidBom);
			pstmtValidBom.setString(1,bomCode);
			rsValidBom = pstmtValidBom.executeQuery();
			bomExists = false;
			while(rsValidBom.next())
			{
				bomExists = true;
				confirmedBom = rsValidBom.getString(1);
				activeBom = rsValidBom.getString(2);
			}
			rsValidBom.close();
			pstmtValidBom.close();
			//this.fwInvalidBom.write("Site Code	 Item Code	 Bom Code   Message\r\n");
			if(confirmedBom == null || confirmedBom.trim().length() == 0 || confirmedBom.equalsIgnoreCase("N") || activeBom == null || activeBom.trim().length() == 0 || activeBom.equalsIgnoreCase("N"))
			{
				siteCode = (siteCode.trim() + "     ").substring(0,5);
				validBomItemCode = (validBomItemCode.trim() + "          ").substring(0,10);
				bomCode = (bomCode.trim() + "          ").substring(0,10);
				logMsg = siteCode + "      " + validBomItemCode + "  " +  bomCode  +  "  ";
				//=========	 ==========	 ========== ========
				if(confirmedBom == null || confirmedBom.trim().length() == 0 || confirmedBom.equalsIgnoreCase("N"))
				{
					logMsg = logMsg + "BOM not confirmed";
				}
				if(activeBom == null || activeBom.trim().length() == 0 || activeBom.equalsIgnoreCase("N"))
				{
					logMsg = logMsg + " BOM not active";
				}
				invaldBomExists = true;
				this.fwInvalidBom.write(logMsg + "\r\n");
			}

			// end 19-01-2007 manoharan
			Set levelBom=new HashSet();
			sql = "SELECT DISTINCT CASE WHEN ITEM.ITEM_CODE__PLAN IS NULL THEN ITEM.ITEM_CODE ELSE ITEM.ITEM_CODE__PLAN END "
			+ " FROM BOMDET, ITEM "
			+ " WHERE ITEM.ITEM_CODE = BOMDET.ITEM_CODE "
			+" AND BOMDET.BOM_CODE = '"+bomCode+"'";
			//pstmt = conn.prepareStatement(sql);
				System.out.println(sql);
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			//pstmt.setString(1,bomCode);
			rs = pstmt.executeQuery(sql);
			int ctr=0;

			while(rs.next())
			{
			System.out.println("rs.getString(1)----"+rs.getString(1));
			levelBom.add(rs.getString(1));
			ctr=1;
			}
			level=level+ctr;
			hmap.put(new Integer(level),levelBom);
			System.out.println("level--"+level);
			for(int i=1;i<level;i++)
			{
			   System.out.println("Level Loop Entered---"+i+"---"+hmap.size());
				levelBom=(HashSet) hmap.get(new Integer(i));
				System.out.println("Level Loop Entered---"+i+"---"+levelBom.size());
				Iterator it=levelBom.iterator();
				while(it.hasNext())
				{
					System.out.println("SET Value---"+it.next().toString());
				}
			}
			rs.beforeFirst();
			while(rs.next())
			{

				//System.out.println("Item Code : "+rs.getString(1));

				sqlInner1 = "SELECT COUNT(1) FROM ITEM WHERE ITEM_CODE = ?";
				pstmtInner1 = conn.prepareStatement(sqlInner1);
				pstmtInner1.setString(1,rs.getString(1));
				rsCount = pstmtInner1.executeQuery();
				//rsCount = stmt.executeQuery("SELECT COUNT(1) FROM ITEM WHERE ITEM_CODE = '" + rs.getString(1) + "'");
				if(rsCount.next() && rsCount.getInt(1) > 0)
				{
					rsCount.close();
					pstmtInner1.close();
					//	Add ItemCode in ItemCodeMap if it not present earlier
					innerSuppSour = getSuppSour(siteCode,rs.getString(1));
					innerBomCode = getBomCode(siteCode,rs.getString(1));
                    System.out.println("innerBomCode---------"+innerBomCode);
					//System.out.println("bomExplode Function :: Site Code :: "+siteCode+"Bom Code ::"+bomCode+"Item Code ::"+rs.getString(1));
					boolean isCyclic=false;
                    System.out.println("innerSuppSour-----"+innerSuppSour+"----innerBomCode----"+innerBomCode);
					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null) )
					{

						for(int i=1;i<level;i++)
						{

							levelBom=(HashSet) hmap.get(new Integer(i));
							System.out.println("levelBom.contains(rs.getString(1))--"+level+"---"+i+"---"+levelBom.size()+"--"+levelBom.contains(rs.getString(1)));
							if(levelBom.size()>0 && levelBom.contains(rs.getString(1)))
							{
								System.out.println("levelBom.contains(rs.getString(1))--"+levelBom.contains(rs.getString(1)));

								//Added by Jiten 07/11/06//
								if(verifier != null && verifier.equalsIgnoreCase("Y"))
								{
									this.fw.write(cyclicItem.siteCode+"		"+cyclicItem.bomCode+"		"+cyclicItem.itemCode+"		"+rs.getString(1)+"\r\n");
									System.out.println("Cyclic Item Code is : " + rs.getString(1));
								}
								else
								{
									System.out.println("Cyclic Item Code is : " + rs.getString(1));
									errString = itmDBAccessEJB.getErrorString("","VTMRPITEME","","",conn);
									rs.close();
									pstmt.close();
									return errString;
								}
								isCyclic=true;
							}

							System.out.println("manohar 14/01/12 checking item existance in  itemCodeMap-------"+rs.getString(1));
							if(!itemCodeMap.containsKey(rs.getString(1)))
							{
								System.out.println("manohar 14/01/12 Placing value in itemCodeMap-------"+rs.getString(1));
								itemCodeMap.put(rs.getString(1),new Integer(itemCodeMap.size() + 1));
							}
							else
							{
								System.out.println("manohar 14/01/12 already in itemCodeMap-------"+rs.getString(1));
							}

						}
					}
					else
					{
						System.out.println("manohar 14/01/12 checking item existance in  itemCodeMap material -------"+rs.getString(1));
						if(!itemCodeMap.containsKey(rs.getString(1)))
						{
							System.out.println("manohar 14/01/12 Placing value in itemCodeMap-------material"+rs.getString(1));
							itemCodeMap.put(rs.getString(1),new Integer(itemCodeMap.size() + 1));
						}
						else
						{
							System.out.println("manohar 14/01/12 already in itemCodeMap-------material"+rs.getString(1));
						}
					}
					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null)  && (isCyclic==false) )
					{
						//System.out.println("Inner Exploding BOM for site : " + siteCode + " Item : " + rs.getString(1) + " BOM : " + innerBomCode);
						validBomItemCode = rs.getString(1);
						errString = bomExplode(siteCode, innerBomCode);

						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While Calling method bomExplode(String, String):..."+errString);
							rs.close();
							pstmt.close();
							return errString;
						}
					}

				}
				else
				{
					rsCount.close();
					sqlInner2 = "SELECT COUNT(1) FROM BOM WHERE BOM_CODE = ?";
					pstmtInner2 = conn.prepareStatement(sqlInner2);
					pstmtInner2.setString(1,rs.getString(1));
					rsCount = pstmtInner2.executeQuery();
					//rsCount = stmt.executeQuery("SELECT COUNT(1) FROM BOM WHERE BOM_CODE = '" + rs.getString(1) + "'");
					if(rsCount.next() && rsCount.getInt(1) > 0)
					{
						rsCount.close();
						pstmtInner2.close();
						validBomItemCode = "BOMDET:" +rs.getString(1);
						errString = bomExplode(siteCode, rs.getString(1));
						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While Calling method bomExplode(String, String):..."+errString);
							rs.close();
							pstmt.close();
							return errString;
						}
					}
					rsCount.close();
				}

			}
            level--;
			rs.close();
			rs = null;//Added by Jiten 09/11/06 -Sun
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :bomExplode(String siteCode, String MatPlan, String bomCode).." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :bomExplode(String siteCode, String MatPlan, String bomCode):...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (rsInner1 != null)
				{
					rsInner1.close();
					rsInner1 = null;
				}
				if (pstmtInner1 != null)
				{
					pstmtInner1.close();
					pstmtInner1 = null;
				}
				if (rsInner1 != null){ rsInner1 = null; }
				if (pstmtInner2 != null)
				{
					pstmtInner2.close();
					pstmtInner2 = null;
				}
				if (rsInner2 != null)
				{
					rsInner2.close();
					rsInner2 = null;
				}
				this.fw.flush();//Added by Jiten 07/11/06
			}
			catch(Exception e)
			{
				System.out.println(e);
				throw new ITMException(e);
			}
		}
		return errString;//Moved here as finally does not complete - jiten -03/11/06
	}

/* 	private String bomExplode(String siteCode, String bomCode) throws ITMException
	{
		PreparedStatement pstmt = null;
		PreparedStatement pstmtInner1 = null;
		PreparedStatement pstmtInner2 = null;
		ResultSet rsCount = null;
		String sql = "", sqlInner1 = "", sqlInner2 = "";
		ResultSet rs = null, rsInner1 = null, rsInner2 = null;
		String innerSuppSour = null;
		String innerBomCode = null;

		PreparedStatement pstmtValidBom = null;
		String sqlValidBom = "SELECT CONFIRMED, ACTIVE FROM BOM WHERE BOM_CODE = ?";
		ResultSet rsValidBom = null;
        boolean bomExists = false;
		String logMsg = null;
		String confirmedBom = null;
		String activeBom = null;
		// 29/09/10 manoharan to close the main resultset before processing
		ArrayList mainList = new ArrayList();
		String tempItemCode = "";
                int setLength=0;

		// end 29/09/10 manoharan
		try
		{

			// 19-01-2007 manoharan
			// check whether the bom is valid should be confirmed and active
			pstmtValidBom = conn.prepareStatement(sqlValidBom);
			pstmtValidBom.setString(1,bomCode);
			rsValidBom = pstmtValidBom.executeQuery();
			bomExists = false;
			if(rsValidBom.next())
			{
				bomExists = true;
				confirmedBom = rsValidBom.getString(1);
				activeBom = rsValidBom.getString(2);
			}
			rsValidBom.close();
			pstmtValidBom.close();
			//this.fwInvalidBom.write("Site Code	 Item Code	 Bom Code   Message\r\n");
			if(confirmedBom == null || confirmedBom.trim().length() == 0 || confirmedBom.equalsIgnoreCase("N") || activeBom == null || activeBom.trim().length() == 0 || activeBom.equalsIgnoreCase("N"))
			{
				siteCode = (siteCode.trim() + "     ").substring(0,5);
				validBomItemCode = (validBomItemCode.trim() + "          ").substring(0,10);
				bomCode = (bomCode.trim() + "          ").substring(0,10);
				logMsg = siteCode + "      " + validBomItemCode + "  " +  bomCode  +  "  ";
				//=========	 ==========	 ========== ========
				if(confirmedBom == null || confirmedBom.trim().length() == 0 || confirmedBom.equalsIgnoreCase("N"))
				{
					logMsg = logMsg + "BOM not confirmed";
				}
				if(activeBom == null || activeBom.trim().length() == 0 || activeBom.equalsIgnoreCase("N"))
				{
					logMsg = logMsg + " BOM not active";
				}
				invaldBomExists = true;
				this.fwInvalidBom.write(logMsg + "\r\n");
			}
			// end 19-01-2007 manoharan
                        Set levelBom=new HashSet();
			sql = "SELECT DISTINCT CASE WHEN ITEM.ITEM_CODE__PLAN IS NULL THEN ITEM.ITEM_CODE ELSE ITEM.ITEM_CODE__PLAN END "
			+ " FROM BOMDET, ITEM "
			+ " WHERE ITEM.ITEM_CODE = BOMDET.ITEM_CODE "
			+" AND BOMDET.BOM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,bomCode);
			rs = pstmt.executeQuery();
                        int ctr=0;
			while(rs.next())
			{
				System.out.println("Item Code : "+rs.getString(1));
				mainList.add(rs.getString(1));
				cyclicCheck.add(rs.getString(1));
				levelBom.add(rs.getString(1));
				ctr=1;
			}
                        level=level+ctr;
                        hmap.put(new Integer(level),levelBom);
                        System.out.println("level--"+level);
			rs.close();
			pstmt.close();
//                        for(int i=1;i<level;i++)
//                        {
//                            System.out.println("Level Loop Entered---"+i+"---"+hmap.size());
//                         levelBom=(HashSet) hmap.get(new Integer(i));
//                         System.out.println("Level Loop Entered---"+i+"---"+levelBom.size());
//                         Iterator it=levelBom.iterator();
//                         while(it.hasNext())
//                         {
//                             System.out.println("SET Value---"+it.next().toString());
//                         }
//
//
//
//                        }



                       		for (int mainCtr = 0; mainCtr < mainList.size() ; mainCtr++)
			{
                         		  System.out.println("(String) mainList.get(mainCtr)--"+(String) mainList.get(mainCtr));
                       		 }

			for (int mainCtr = 0; mainCtr < mainList.size() ; mainCtr++)
			{
				tempItemCode = (String) mainList.get(mainCtr);
                 mainList.remove(mainCtr);

                                		//System.out.println("Manoj  tempItemCode--"+tempItemCode);
                 setLength=itemList.size();
				 //setLength = cyclicCheck.size();
                 Iterator itr=itemList.iterator();
				while(itr.hasNext())
				{
		          System.out.println(itr.next());
				}
                 System.out.println("setLength--"+setLength);
		         System.out.println("Hello");
                 System.out.println("tempItemCode--"+tempItemCode+"--"+itemList.size());
		         itemList.add(tempItemCode);
                 System.out.println("tempItemCode--"+tempItemCode+"--"+itemList.size());
				sqlInner1 = "SELECT COUNT(1) FROM ITEM WHERE ITEM_CODE = ?";

				pstmtInner1 = conn.prepareStatement(sqlInner1);
				pstmtInner1.setString(1,tempItemCode);
				rsCount = pstmtInner1.executeQuery();

				//rsCount = stmt.executeQuery("SELECT COUNT(1) FROM ITEM WHERE ITEM_CODE = '" + rs.getString(1) + "'");
				if(rsCount.next() && rsCount.getInt(1) > 0)
				{
					rsCount.close();
					rsCount  = null;
					pstmtInner1.close();
					pstmtInner1 = null;
					//	Add ItemCode in ItemCodeMap if it not present earlier
					innerSuppSour = getSuppSour(siteCode,tempItemCode);
					innerBomCode = getBomCode(siteCode,tempItemCode);
                                        			System.out.println("Inner Bom Code  "+innerSuppSour+"--"+innerBomCode+"--"+tempItemCode);
					//System.out.println("bomExplode Function :: Site Code :: "+siteCode+"Bom Code ::"+bomCode+"Item Code ::"+rs.getString(1));

					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null) )
					{

                      System.out.println("itemList.size()--"+verifier+"--"+setLength+"--"+itemList.size());
                      //14.11.2010 Manoj

                      for(int i=1;i<level;i++)
                      {

                         levelBom=(HashSet) hmap.get(new Integer(i));
                         System.out.println("levelBom.contains(tempItemCode)--"+level+"---"+i+"---"+levelBom.size()+"--"+levelBom.contains(tempItemCode));
                         if(levelBom.size()>0 && levelBom.contains(tempItemCode))
                         {
                         System.out.println("levelBom.contains(tempItemCode)--"+levelBom.contains(tempItemCode));
                        if(verifier != null && verifier.equalsIgnoreCase("Y"))
                        {
                         System.out.println("itemList.size()--"+verifier+"--"+setLength+"--"+itemList.size());
						 this.fw.write(cyclicItem.siteCode+"		"+cyclicItem.bomCode+"		"+cyclicItem.itemCode+"		"+tempItemCode+"\r\n");
						 System.out.println("Cyclic Item Code is : " + tempItemCode);
						 //return "";
                         System.out.println("itemList.size()--"+verifier+"--"+setLength+"--"+itemList.size());
 					    }
        		     	else
						{
						  System.out.println("Cyclic Item Code is : " + tempItemCode);
						  errString = itmDBAccessEJB.getErrorString("","VTMRPITEME","","",conn);
						  rs.close();
						  pstmt.close();
		                  itemList.clear();
						  return errString;
		              	}
                     }
                    }
                      // end 14.11.2010 Manoj

				    }
					if(itemCodeMap.containsKey(tempItemCode))
					{
                     System.out.println("Test1");
                    }
					else
					{
                      System.out.println("Test2");
					  itemCodeMap.put(tempItemCode,new Integer(itemCodeMap.size() + 1));

					}
					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null) )
					{
						System.out.println("Inner Exploding BOM for site : " + siteCode + " Item : " + tempItemCode + " BOM : " + innerBomCode);
						validBomItemCode = tempItemCode;
						errString = bomExplode(siteCode, innerBomCode);
                        System.out.println("Inner 1"+siteCode+"--"+innerBomCode);
                        System.out.println("Inner 2"+siteCode+"--"+innerBomCode);

						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While Calling method bomExplode(String, String):..."+errString);
							//rs.close();
							//pstmt.close();
							//rs = null;
							//pstmt = null;
							return errString;
						}
					}

				}
				else
				{
					rsCount.close();
					pstmtInner1.close();
					sqlInner2 = "SELECT COUNT(1) FROM BOM WHERE BOM_CODE = ?";
					pstmtInner2 = conn.prepareStatement(sqlInner2);
					pstmtInner2.setString(1,tempItemCode);
					rsCount = pstmtInner2.executeQuery();
					//rsCount = stmt.executeQuery("SELECT COUNT(1) FROM BOM WHERE BOM_CODE = '" + rs.getString(1) + "'");
					if(rsCount.next() && rsCount.getInt(1) > 0)
					{
						rsCount.close();
						rsCount = null;
						pstmtInner2.close();
						pstmtInner2 = null;

						validBomItemCode = "BOMDET:" +tempItemCode;
						errString = bomExplode(siteCode, tempItemCode);
						if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While Calling method bomExplode(String, String):..."+errString);
							//rs.close();
							//pstmt.close();
							//rs = null;
							//pstmt = null;
							return errString;
						}
					}
					else
					{
						rsCount.close();
						rsCount = null;
						pstmtInner2.close();
						pstmtInner2 = null;
					}
				}
			}

			if ( rs != null)
			{
				rs.close();
				rs = null;//Added by Jiten 09/11/06 -Sun
			}
			if ( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :bomExplode(String siteCode, String MatPlan, String bomCode).." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :bomExplode(String siteCode, String MatPlan, String bomCode):...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if ( rs != null)
				{
					rs.close();
					rs = null;//Added by Jiten 09/11/06 -Sun
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (rsInner1 != null)
				{
					rsInner1.close();
					rsInner1 = null;
				}
				if (pstmtInner1 != null)
				{
					pstmtInner1.close();
					pstmtInner1 = null;
				}
				if (rsInner1 != null){ rsInner1 = null; }
				if (pstmtInner2 != null)
				{
					pstmtInner2.close();
					pstmtInner2 = null;
				}
				if (rsInner2 != null)
				{
					rsInner2.close();
					rsInner2 = null;
				}
				this.fw.flush();//Added by Jiten 07/11/06
			}
			catch(Exception e)
			{
				System.out.println(e);
				throw new ITMException(e);
			}
		}
		return errString;//Moved here as finally does not complete - jiten -03/11/06
	} */

	// Populating Bom code

	private String getBomCode(String siteCode, String itemCode) throws ITMException
	{
		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rs = null;
		String bomCode = null;
		try
		{
			//sql = "select bom_code from siteitem where site_code ='" + siteCode + "' AND item_code = '" + ItemCode + "'";
			sql = "SELECT BOM_CODE FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				bomCode = rs.getString(1);
			}
			else
			{
				rs.close();
				pstmt.close();
				sql = "SELECT BOM_CODE FROM ITEM WHERE ITEM_CODE = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					bomCode = rs.getString(1);
				}
			}
			rs.close();
			pstmt.close();
			rs = null;
			pstmt = null;
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}

		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmt != null) { pstmt = null; }
			if (rs != null) { rs = null; }
		}
		return bomCode; // Moved here as Finally block does not complete normally - jiten -03/11/06
	}
	private String getBomCode(String siteCode, String itemCode, double quantity) throws ITMException
	{
		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rs = null;
		String bomCode = null;
		int count;
		if (quantity > 0 )
		{
			try
			{
				sql = "SELECT count(1) FROM BOM WHERE ITEM_CODE = ? AND CONFIRMED = 'Y' AND ACTIVE = 'Y' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next() && rs.getInt(1) > 1)
				{
					count = rs.getInt(1);
					sql = "SELECT BOM_CODE FROM BOM WHERE ITEM_CODE = ? AND CONFIRMED = 'Y' AND ACTIVE = 'Y' "
					+ "AND ? BWTWEEN MIN_BATCH_QTY AND MAX_BATCH_QTY ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,itemCode);
					pstmt.setDouble(2,quantity);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						bomCode = rs.getString(1);
						rs.close();
						pstmt.close();
					}
					else
					{
						rs.close();
						pstmt.close();
						bomCode = getBomCode(siteCode, itemCode);
					}
				}
				else
				{

					rs.close();
					pstmt.close();
					bomCode = getBomCode(siteCode, itemCode);

				}
			}
			catch(SQLException se)
			{
				System.out.println("SQLException :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +se.getMessage());
				se.printStackTrace();
				errString = se.getMessage();
				throw new ITMException(se);
			}

			catch(Exception e)
			{
				System.out.println("Exception :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +e.getMessage());
				this.errString = genericUtility.createErrorString(e);
				e.printStackTrace();
				errString = e.getMessage();
				throw new ITMException(e);
			}
		}
		else
		{
			bomCode =  getBomCode(siteCode, itemCode);
		}
		return bomCode;
	}


	private String getSuppSour(String siteCode, String itemCode) throws ITMException
	{
		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rs = null;
		String suppSour = null;
		try
		{
			//sql = "select supp_sour from siteitem where site_code ='" + siteCode + "' AND item_code = '" + ItemCode + "'";
			sql = "SELECT SUPP_SOUR FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				suppSour = rs.getString(1);
			}
			else
			{
				rs.close();
				pstmt.close();
				sql = "SELECT SUPP_SOUR FROM ITEM WHERE ITEM_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					suppSour = rs.getString(1);
				}
			}

			rs.close();
			pstmt.close();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :getSuppSour(String siteCode, String ItemCode).." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}

		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :getSuppSour(String siteCode, String ItemCode).." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null) { pstmt.close(); pstmt = null; }
				if (rs != null) { rs.close(); rs = null; }
			}
			catch(Exception e){}
		}
		return suppSour.trim(); // Moved here as finally block does not complete normally - Jiten -03/11/06
	}
	private String getSiteCodeSupp(String siteCode, String itemCode) throws ITMException
	{
		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rs = null;
		String siteCodeSupp = null;
		try
		{
			//sql = "select supp_sour from siteitem where site_code ='" + siteCode + "' AND item_code = '" + ItemCode + "'";
			sql = "SELECT SITE_CODE__SUPP FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				siteCodeSupp = rs.getString(1);
			}
			else
			{

				siteCodeSupp = null;

			}

			rs.close();
			pstmt.close();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :getSuppSour(String siteCode, String ItemCode).." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}

		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :getSuppSour(String siteCode, String ItemCode).." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pstmt != null) { pstmt.close(); pstmt = null; }
				if (rs != null) { rs.close(); rs = null; }
			}
			catch(Exception e){}
			//return siteCodeSupp;
		}
		return siteCodeSupp; // Jiten - Moved here from finally 03/11/06
	}
	private String populateADPElementList() throws Exception
	{
		ADPElement adpElement = null;
		Iterator itrItem = null;
		Set setSite = siteCodeMap.entrySet();
		Iterator itrSite = setSite.iterator();
		PreparedStatement pstmt = null;
		String sql = "",siteCode = "",itemCode = "",mfgType = "";
		ResultSet rs = null;

		Set setItem = itemCodeMap.entrySet();
		//System.out.println("In populateADPElementList()");
		try
		{
			while(itrSite.hasNext())
			{
				Map.Entry siteMapEntry = (Map.Entry)itrSite.next();
				itrItem = setItem.iterator();
				while(itrItem.hasNext())
				{
					adpElement = new ADPElement();

					Map.Entry itemMapEntry = (Map.Entry)itrItem.next();

					siteCode = (String)siteMapEntry.getKey();
					itemCode = (String)itemMapEntry.getKey();

					// 01/04/11 in case of made to order only made to order item will be planned
					/*if ("Y".equals(madeToOrder))
					{
						sql = "SELECT mfg_type FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							mfgType = rs.getString(1);
						}
						rs.close();
						pstmt.close();
						if (!"O".equals(mfgType))
						{
							continue;
						}
					}*/
					// end 01/04/11 in case of made to order only made to order item will be planned
					adpElement.setSiteCode((String)siteMapEntry.getKey());
					adpElement.setItemCode((String)itemMapEntry.getKey());
					adpElement = setAdpeValues(adpElement);
					//System.out.println("Site Code :: "+adpElement.getSiteCode()+"Item Code :: "+adpElement.getItemCode());//Jiten 01/11/06
					//System.out.println("adpElement.getSuppSour()"+adpElement.getSuppSour());
					//System.out.println("Supply Source = " + adpElement.getSuppSour());
					if (adpElement.getSuppSour() != null)
					{
						if (adpElement.getSuppSour().equals("D") && !disPlan.equals("Y"))
						{
							continue;
						}
						if (adpElement.getSuppSour().equals("M") && (!mfgPlan.equals("Y") && !matPlan.equals("Y") ))
						{
							continue;
						}
						if (adpElement.getSuppSour().equals("P") && !matPlan.equals("Y"))
						{
							continue;
						}
					}
					else
					{
						System.out.println("NULL : SiteCode : [" + siteMapEntry.getKey() + "] ItemCode : [" + itemMapEntry.getKey() + "]");
						errString = "Supply source is null...";
						return errString;
					}

					// Code for adding Demand (i.e. getDemand()) in adpElement // adpElement.setDemand(adpElement.getDemand(demand));
					//adpElement = populateDemandSupply(adpElement);//Commented by Jiten 09/11/06

					if(adpElement != null)
					adpeList.add(adpElement);
				}
			}
			populateADPList();//Added by Jiten 09/11/06
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :populateADPElementList().." +e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		return errString;
	}

	private ADPElement setAdpeValues(ADPElement adpElement) throws Exception
	{
		String orderOpt = "";
		String suppSour = "";
		String bomCode = "";
		String masterSch = "";

		double minQty = 0 ;
		int minStkDay = 0 ;
		double reoQty = 0;
		int purLeadTime = 0 ;
		int qcLeadTime = 0 ;
		int mfgLeadTime = 0 ;
		double integralQty = 0 ;
		double yieldPerc=0 ;
		String itemStru = "";
		String siteCodeSupp = "";
		String siteCodePlan = "";
		String batchQtyType = "";
		double minOrderQty = 0 ;
		int maxStkDay = 0 ;
		double maxQty = 0 ;
		double minPlanPerc=0 ;

		String siteCode = "";
		String itemCode = "";
		String itemSer = "";
		String sql = "" ;
		String unit = null;

		String acctCodeWp = "";
		String cctrCodeWp = "";
		String acctCode = "";
		String cctrCode = "";
		// 22/06/10 manoharan mfg lead time basis and batch size
		String mfgLeadBasis = "F";
		double batchSizeLead = 0;
		//

		ResultSet rs = null;
		ResultSet rsInner = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmtInner = null;
		itemCode = adpElement.getItemCode();
		siteCode = adpElement.getSiteCode();



		try
		{		//1
			sql ="SELECT CASE WHEN (CASE WHEN A.SUPP_SOUR IS NULL THEN B.SUPP_SOUR ELSE A.SUPP_SOUR END) IS NULL THEN '' ELSE "
				+"(CASE WHEN A.SUPP_SOUR IS NULL THEN B.SUPP_SOUR ELSE A.SUPP_SOUR END) END ,"
				//2
				+"CASE WHEN (CASE WHEN A.BOM_CODE IS NULL THEN B.BOM_CODE ELSE A.BOM_CODE END) IS NULL THEN '' ELSE "
				+"(CASE WHEN A.BOM_CODE IS NULL THEN B.BOM_CODE ELSE A.BOM_CODE END) END, "
				//3
				+"CASE WHEN (CASE WHEN A.MIN_QTY IS NULL THEN B.MIN_QTY ELSE A.MIN_QTY END) IS NULL THEN 0 ELSE "
				+"(CASE WHEN A.MIN_QTY IS NULL THEN B.MIN_QTY ELSE A.MIN_QTY END) END, "
				//4
				+"CASE WHEN (CASE WHEN A.MIN_STK_DAY IS NULL THEN B.MIN_STK_DAY ELSE A.MIN_STK_DAY END) IS NULL THEN 0 ELSE "
				+"(CASE WHEN A.MIN_STK_DAY IS NULL THEN B.MIN_STK_DAY ELSE A.MIN_STK_DAY END) END,"
				//5
				+"CASE WHEN A.REO_QTY IS NULL THEN 0 ELSE A.REO_QTY END, "
				//6
				+"CASE WHEN A.PUR_LEAD_TIME IS NULL THEN 0 ELSE A.PUR_LEAD_TIME END, "
				//7
				+"CASE WHEN A.QC_LEAD_TIME IS NULL THEN 0 ELSE A.QC_LEAD_TIME END, "
				//8
				+"CASE WHEN A.MFG_LEAD_TIME IS NULL THEN 0 ELSE A.MFG_LEAD_TIME END, "
				//9
				+"CASE WHEN A.INTEGRAL_QTY IS NULL THEN 0 ELSE A.INTEGRAL_QTY END, "
				//10
				+"CASE WHEN B.ITEM_STRU IS NULL THEN '' ELSE B.ITEM_STRU END, "
				//11
				+"CASE WHEN A.SITE_CODE__SUPP IS NULL THEN '' ELSE A.SITE_CODE__SUPP END, "
				//12
				+"CASE WHEN A.SITE_CODE__PLAN IS NULL THEN A.SITE_CODE ELSE A.SITE_CODE__PLAN END,"
				//13
				+"CASE WHEN (CASE WHEN A.BATCH_QTY_TYPE IS NULL THEN B.BATCH_QTY_TYPE ELSE A.BATCH_QTY_TYPE END) IS NULL THEN 'F' ELSE "
				+"(CASE WHEN A.BATCH_QTY_TYPE IS NULL THEN B.BATCH_QTY_TYPE ELSE A.BATCH_QTY_TYPE END) END,	"
				//14
				+"CASE WHEN (CASE WHEN A.MIN_ORDER_QTY IS NULL THEN B.MIN_ORDER_QTY ELSE A.MIN_ORDER_QTY END) IS NULL THEN 0 ELSE "
				+"(CASE WHEN A.MIN_ORDER_QTY IS NULL THEN B.MIN_ORDER_QTY ELSE A.MIN_ORDER_QTY END) END, "
				//15
				+"CASE WHEN (CASE WHEN A.MAX_STK_DAY IS NULL THEN B.MAX_STK_DAY ELSE A.MAX_STK_DAY END) IS NULL THEN 0 ELSE "
				+"(CASE WHEN A.MAX_STK_DAY IS NULL THEN B.MAX_STK_DAY ELSE A.MAX_STK_DAY END) END, "
				//16
				+"A.MASTER_SCH , "
				//17
				+"CASE WHEN (CASE WHEN A.MAX_QTY IS NULL THEN B.MAX_QTY ELSE A.MAX_QTY END) IS NULL THEN 0 ELSE "
				+"(CASE WHEN A.MAX_QTY IS NULL THEN B.MAX_QTY ELSE A.MAX_QTY END) END ,"
				//18
				+"(CASE WHEN A.ORDER_OPT IS NULL THEN B.ORDER_OPT ELSE A.ORDER_OPT END) END ,"
				//19
				+"(CASE WHEN A.YIELD_PERC IS NULL THEN B.YIELD_PERC ELSE A.YIELD_PERC END) END, "
				//20
				+"(CASE WHEN A.MIN_PLAN_PERC IS NULL THEN 0 ELSE A.MIN_PLAN_PERC END) END, "
                //21
                +"B.UNIT, "
                //22
                +"B.DESCR, "
                // 23
                +"B.APR_CODE, "
                // 24
                +"B.PACK_CODE, "
                // 25
                +"CASE WHEN A.EMP_CODE__PUR IS NULL THEN CASE WHEN b.EMP_CODE__PUR IS NULL THEN C.EMP_CODE__PUR ELSE B.EMP_CODE__PUR END ELSE A.EMP_CODE__PUR END , "
                // 26
                +"(CASE WHEN A.SUPP_CODE__PREF IS NULL THEN B.SUPP_CODE__PREF ELSE A.SUPP_CODE__PREF END ), "
                // 27
                +"B.PURC_RATE, "
                // 28
               +"C.ACCT_CODE__PH, "
                // 29
                +"C.CCTR_CODE__PH, "
                // 30
                +" B.ITEM_SER, "
                // 31, 32
                +" CASE WHEN A.MFG_TYPE IS NULL THEN 'MRP' ELSE A.MFG_TYPE END, A.ROUTE_CODE, "
				// 33, 34
				+" case when a.mfg_lead_basis is null then 'F' else a.mfg_lead_basis end, case when a.batch_size_lead is null then 0 else a.batch_size_lead end  "
				+" FROM SITEITEM A, ITEM B, ITEMSER C "
				+" WHERE A.ITEM_CODE = B.ITEM_CODE "
                +" AND B.ITEM_SER = C.ITEM_SER "
				+" AND A.SITE_CODE = ? "
				+" AND A.ITEM_CODE = ? ";
				//+" AND A.SITE_CODE = '" + siteCode + "' "
				//+" AND A.ITEM_CODE = '" + itemCode + "' ";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				suppSour = rs.getString(1);
				adpElement.setSuppSour(suppSour.trim());
				suppSour= suppSour.trim();

				bomCode = rs.getString(2);
				adpElement.setBomCode(bomCode);

				minQty = rs.getDouble(3);
				adpElement.setMinQty(minQty);

				minStkDay = rs.getInt(4);
				adpElement.setMinStkDay(minStkDay);

				reoQty = rs.getDouble(5);
				adpElement.setReoQty(reoQty);

				purLeadTime = rs.getInt(6);
				adpElement.setPurLeadTime(purLeadTime);

				qcLeadTime = rs.getInt(7);
				adpElement.setQcLeadTime(qcLeadTime);

				mfgLeadTime =rs.getInt(8);
				adpElement.setMfgLeadTime(mfgLeadTime);

				integralQty = rs.getDouble(9);
				if (integralQty == 0)
				{
					integralQty = 1;
				}
				adpElement.setIntegralQty(integralQty);

				itemStru = rs.getString(10);
				adpElement.setItemStru(itemStru);

				siteCodeSupp = rs.getString(11);
				adpElement.setSiteCodeSupp(siteCodeSupp);

				siteCodePlan = rs.getString (12);
				adpElement.setSiteCodePlan(siteCodePlan);

				batchQtyType = rs.getString(13);
				adpElement.setBatchQtyType(batchQtyType);

				minOrderQty = rs.getDouble(14);
				adpElement.setMinOrderQty(minOrderQty);

				maxStkDay = rs.getInt(15);
				adpElement.setMaxStkDay(maxStkDay);

				masterSch = rs.getString(16);
				adpElement.setMasterSch(masterSch);

				maxQty = rs.getDouble(17);
				adpElement.setMaxQty(maxQty);

				orderOpt = rs.getString(18);
				if(orderOpt == null)
				{
					orderOpt = "C";
				}
				if(orderOpt.equals("B") && batchQtyType == null)
				{
					 adpElement.setBatchQtyType("V");
				}
				adpElement.setOrderOpt(orderOpt);

				yieldPerc = rs.getDouble(19);
                if (yieldPerc == 0)
                {
                   yieldPerc = 100;
                }

				adpElement.setYieldPerc(yieldPerc);
				minPlanPerc = rs.getDouble(20);
				adpElement.setMinPlanPerc(minPlanPerc);
                unit = rs.getString(21);
                adpElement.setUnit(unit);
                adpElement.setItemDescr(rs.getString(22));
                adpElement.setAprCode(rs.getString(23));
                adpElement.setPackCode(rs.getString(24));
                adpElement.setEmpCodePur(rs.getString(25));
                adpElement.setSuppCodePref(rs.getString(26));
                adpElement.setPurcRate(rs.getDouble(27));
                adpElement.setAcctCodePh(rs.getString(28));
                adpElement.setCctrCodePh(rs.getString(29));
                adpElement.setItemSer(rs.getString(30));
                String mfgType = rs.getString(31);
                if (mfgType != null)
                {
                	adpElement.setMfgType(mfgType.trim());
                }
                String routeCode = rs.getString(32);
				if (routeCode != null && routeCode.trim().length() > 0 )
                {
					adpElement.setRouteCode(routeCode);
                }
				// 22/06/10 manoharan
				mfgLeadBasis = rs.getString(33);
				mfgLeadBasis = (mfgLeadBasis == null ? "F" : mfgLeadBasis);
				adpElement.setMfgLeadBasis(mfgLeadBasis);
				batchSizeLead = rs.getDouble(34);
				adpElement.setBatchSizeLead(batchSizeLead);
				// end 22/06/10 manoharan
				rs.close();
			}
			else
			{
				System.out.println("*** No SiteItem *** " + siteCode + " " + itemCode);
				//***************************** 04-01-2007 manoharan take all values from item if no siteitem

				rs.close();
				pstmt.close();
				sql ="SELECT CASE WHEN B.SUPP_SOUR IS NULL THEN '' ELSE B.SUPP_SOUR END ,"
				//2
				+"CASE WHEN B.BOM_CODE IS NULL THEN '' ELSE B.BOM_CODE END, "
				//3
				+"CASE WHEN B.MIN_QTY IS NULL THEN 0 ELSE B.MIN_QTY END, "
				//4
				+"CASE WHEN B.MIN_STK_DAY IS NULL THEN 0 ELSE B.MIN_STK_DAY END,"
				//5
				+"CASE WHEN B.REO_QTY IS NULL THEN 0 ELSE B.REO_QTY END, "
				//6
				+"CASE WHEN B.PUR_LEAD_TIME IS NULL THEN 0 ELSE B.PUR_LEAD_TIME END, "
				//7
				+"CASE WHEN B.QC_LEAD_TIME IS NULL THEN 0 ELSE B.QC_LEAD_TIME END, "
				//8
				+"CASE WHEN B.MFG_LEAD IS NULL THEN 0 ELSE B.MFG_LEAD END, "
				//9
				+"CASE WHEN B.INTEGRAL_QTY IS NULL THEN 0 ELSE B.INTEGRAL_QTY END, "
				//10
				+"CASE WHEN B.ITEM_STRU IS NULL THEN '' ELSE B.ITEM_STRU END, "
				//11
				+"'' AS SITE_CODE__SUPP, "
				//12
				+ "'" + siteCode + "' AS SITE_CODE__PLAN,"
				//13
				+"CASE WHEN B.BATCH_QTY_TYPE IS NULL THEN 'F' ELSE B.BATCH_QTY_TYPE END,	"
				//14
				+"CASE WHEN B.MIN_ORDER_QTY IS NULL THEN 0 ELSE B.MIN_ORDER_QTY END, "
				//15
				+"CASE WHEN B.MAX_STK_DAY IS NULL THEN 0 ELSE B.MAX_STK_DAY END, "
				//16
				+"'Y' AS MASTER_SCH , "
				//17
				+"CASE WHEN B.MAX_QTY IS NULL THEN 0 ELSE B.MAX_QTY END ,"
				//18
				+"CASE WHEN B.ORDER_OPT IS NULL THEN 'C' ELSE B.ORDER_OPT END ,"
				//19
				+"CASE WHEN B.YIELD_PERC IS NULL THEN 100 ELSE B.YIELD_PERC END, "
				//20
				+" 0 AS MIN_PLAN_PERC, "
                //21
                +"B.UNIT, "
                //22
                +"B.DESCR, "
                // 23
                +"B.APR_CODE, "
                // 24
                +"B.PACK_CODE, "
                // 25
                +"CASE WHEN B.EMP_CODE__PUR IS NULL THEN C.EMP_CODE__PUR ELSE B.EMP_CODE__PUR END , "
                // 26
                +"B.SUPP_CODE__PREF, "
                // 27
                +"B.PURC_RATE, "
                // 28
               +"C.ACCT_CODE__PH, "
                // 29
                +"C.CCTR_CODE__PH, "
                // 30
                +" B.ITEM_SER, "
                // 31
                +" CASE WHEN B.MFG_TYPE IS NULL THEN 'MRP' ELSE B.MFG_TYPE END "
				+" FROM ITEM B, ITEMSER C "
				+" WHERE B.ITEM_SER = C.ITEM_SER"
				+" AND B.ITEM_CODE = '" + itemCode + "' ";


				pstmt = conn.prepareStatement(sql);
				//pstmt.setString(1,siteCode);
				//pstmt.setString(2,itemCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					suppSour = rs.getString(1);
					adpElement.setSuppSour(suppSour.trim());

					bomCode = rs.getString(2);
					adpElement.setBomCode(bomCode);

					minQty = rs.getDouble(3);
					adpElement.setMinQty(minQty);

					minStkDay = rs.getInt(4);
					adpElement.setMinStkDay(minStkDay);

					reoQty = rs.getDouble(5);
					adpElement.setReoQty(reoQty);

					purLeadTime = rs.getInt(6);
					adpElement.setPurLeadTime(purLeadTime);

					qcLeadTime = rs.getInt(7);
					adpElement.setQcLeadTime(qcLeadTime);

					mfgLeadTime =rs.getInt(8);
					adpElement.setMfgLeadTime(mfgLeadTime);

					integralQty = rs.getDouble(9);
					if (integralQty == 0)
					{
						integralQty = 1;
					}
					adpElement.setIntegralQty(integralQty);

					itemStru = rs.getString(10);
					adpElement.setItemStru(itemStru);

					siteCodeSupp = rs.getString(11);
					adpElement.setSiteCodeSupp(siteCodeSupp);

					siteCodePlan = rs.getString (12);
					adpElement.setSiteCodePlan(siteCodePlan);

					batchQtyType = rs.getString(13);
					adpElement.setBatchQtyType(batchQtyType);

					minOrderQty = rs.getDouble(14);
					adpElement.setMinOrderQty(minOrderQty);

					maxStkDay = rs.getInt(15);
					adpElement.setMaxStkDay(maxStkDay);

					masterSch = rs.getString(16);
					adpElement.setMasterSch(masterSch);

					maxQty = rs.getDouble(17);
					adpElement.setMaxQty(maxQty);

					orderOpt = rs.getString(18);
					if(orderOpt == null)
					{
						orderOpt = "C";
					}
					if(orderOpt.equals("B") && batchQtyType == null)
					{
						 adpElement.setBatchQtyType("V");
					}
					adpElement.setOrderOpt(orderOpt);

					yieldPerc = rs.getDouble(19);
	                if (yieldPerc == 0)
	                {
	                   yieldPerc = 100;
	                }

					adpElement.setYieldPerc(yieldPerc);
					minPlanPerc = rs.getDouble(20);
					adpElement.setMinPlanPerc(minPlanPerc);
	                unit = rs.getString(21);
	                adpElement.setUnit(unit);
	                adpElement.setItemDescr(rs.getString(22));
	                adpElement.setAprCode(rs.getString(23));
	                adpElement.setPackCode(rs.getString(24));
	                adpElement.setEmpCodePur(rs.getString(25));
	                adpElement.setSuppCodePref(rs.getString(26));
	                adpElement.setPurcRate(rs.getDouble(27));
	                adpElement.setAcctCodePh(rs.getString(28));
	                adpElement.setCctrCodePh(rs.getString(29));
	                adpElement.setItemSer(rs.getString(30));
	                String mfgType = rs.getString(31);
	                if (mfgType != null)
	                {
	                	adpElement.setMfgType(mfgType.trim());
	                }
					// 22/06/10 manoharan
					adpElement.setMfgLeadBasis("F");
					adpElement.setBatchSizeLead(0);
					// end 22/06/10 manoharan

					rs.close();
				}


//***************************** end 04-01-2007 manoharan
			}
			if (adpElement.getSuppSour() == null)
			{
				adpElement.setSuppSour(this.getSuppSour(siteCode, itemCode));
			}
			if (adpElement.getSiteCodeSupp() == null)
			{
				adpElement.setSiteCodeSupp(getSiteCodeSupp(siteCode, itemCode));
			}
			if (adpElement.getOrderOpt() != null && adpElement.getOrderOpt().equals("B") && adpElement.getBatchQtyType() == null)
			{
				  adpElement.setBatchQtyType("C");
			}
			if(suppSour.equals("M"))
			{
			                        itemSer = adpElement.getItemSer();
				sql = "SELECT ACCT_CODE__WP, CCTR_CODE__WP FROM ITEM_ACCT_DETR WHERE ITEM_CODE = ? AND ITEM_SER = ? AND TRAN_TYPE = ' ' ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, itemCode);
				pstmt1.setString(2, itemSer);
				rs = pstmt1.executeQuery();
				if(rs.next())
				{
					acctCodeWp = rs.getString(1);
					cctrCodeWp = rs.getString(2);
			                                rs.close();
			                                pstmt1.close();
				}
				else
				{
			                                rs.close();
			                                pstmt1.close();
					sql = "SELECT ACCT_CODE__WP, CCTR_CODE__WP FROM ITEM_ACCT_DETR WHERE ITEM_SER = ' ' AND ITEM_CODE = ? AND TRAN_TYPE = ' '";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,itemCode);
					rs = pstmt1.executeQuery();
					if(rs.next())
					{
						acctCodeWp = rs.getString(1);
						cctrCodeWp = rs.getString(2);
			            rs.close();
			            pstmt1.close();
					}
					else
					{
			          rs.close();
			          pstmt1.close();
			          sql = "SELECT ACCT_CODE__WP, CCTR_CODE__WP FROM ITEM_ACCT_DETR WHERE ITEM_SER = ? AND ITEM_CODE = ' ' AND TRAN_TYPE = ' '";
			          pstmt1 = conn.prepareStatement(sql);
			          pstmt1.setString(1,itemSer);         // itemSer ???
			          rs = pstmt1.executeQuery();
			          if(rs.next())
			          {
			                  acctCodeWp = rs.getString(1);
			                  cctrCodeWp = rs.getString(2);
			                  rs.close();
			                  pstmt1.close();
			          }
			          else
			          {
			                  rs.close();
			                  pstmt1.close();
			                  sql = "SELECT ACCT_CODE__WP, CCTR_CODE__WP FROM ITEMSER WHERE ITEM_SER = ? ";
			                  pstmt1 = conn.prepareStatement(sql);
			                  pstmt1.setString(1,itemSer);
			                  rs = pstmt1.executeQuery();
			                  if(rs.next())
			                  {
			                      acctCodeWp = rs.getString(1);
			                      cctrCodeWp = rs.getString(2);
			                      rs.close();
			                      pstmt1.close();
			                  }
			          }
					}
				}
				adpElement.setAcctCodeWp(acctCodeWp);
				adpElement.setCctrCodeWp(cctrCodeWp);
			}
			                // set the batch quantity for later use
			sql = "SELECT BATCH_QTY FROM BOM WHERE BOM_CODE = ?" ;
			pstmtInner = conn.prepareStatement(sql);
			pstmtInner.setString(1,bomCode);
			rsInner = pstmtInner.executeQuery();
			if (rsInner.next())
			{
				adpElement.setBatchQty(rsInner.getDouble(1));
			}
			rsInner.close();
			if (orderOpt.equals("B") && batchQtyType.equals("F") && suppSour.equals("M") )
			{
				//adpElement.setReoQty(adpElement.getBatchQty());
			}
			if(roundReqTo.equals("R"))
			{
				System.out.println("item ["+ itemCode +" ] roundReqTo ..[" +roundReqTo + "] setting integral to [" + adpElement.getReoQty() + "]");
				adpElement.setIntegralQty(adpElement.getReoQty());
			}
		}
		catch(SQLException se)
		{
			System.out.println("SQLException .." +se.getMessage());
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception .." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmt != null) { pstmt.close(); pstmt = null; }
			if (pstmt1 != null) { pstmt1 = null; }
			if (pstmtInner != null) { pstmtInner.close(); pstmtInner = null; }
			if (rs != null) { rs.close(); rs = null; }
			if (rsInner != null) { rsInner.close(); rsInner = null; }
		}
		return adpElement;// Moved here as Finally block does not complete normally - jiten -03/11/06
	}

	private String readFile(String file) throws Exception
	{
		String sqlQry = "";
		try
		{
			//System.out.println("File "+file);
			FileInputStream fin = new FileInputStream(file);
			BufferedReader br = new BufferedReader(new InputStreamReader(fin));
			String tempStr = "";
			while((tempStr = br.readLine()) != null)
			{
				sqlQry = sqlQry + tempStr;
			}
		}
		catch(FileNotFoundException fe)
		{
			System.out.println("FileNotFoundException :RunMRPPrcEJB :readFile(String).." +fe.getMessage());
			fe.printStackTrace();
			errString = fe.getMessage();
			throw new ITMException(fe);
		}

		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :readFile(String).." +e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		return sqlQry;
	}

	private void populateADPList() throws Exception
	{
		ArrayList tempList = new ArrayList();
		try{

			if ("0".equals(madeToOrder))
			{
				for(int ctr=0;ctr<adpeList.size();ctr++)
				{
					ADPElement adpElement = (ADPElement)adpeList.get(ctr);
					tempList.add(adpElement);
				}
				//adpeList.clear();
				System.out.println("01/04/11 populateADPList madeToOrder [" + madeToOrder + "] tempList.size() [" + tempList.size() + "]");
				for(int ctr=0;ctr<tempList.size();ctr++)
				{
					ADPElement adpElement = 	(ADPElement)tempList.get(ctr);
					//adpElement =
					populateMTODemandSupply(adpElement.getSiteCode(),adpElement.getItemCode(),adpElement);
				}
				tempList.clear();
				tempList = null;
			}
			System.out.println("01/04/11 populateADPList madeToOrder [" + madeToOrder + "] adpeList.size() [" + adpeList.size() + "]");
			for(int ctr=0;ctr<adpeList.size();ctr++)
			{
				ADPElement adpElement = 	(ADPElement)adpeList.get(ctr);
				adpElement = populateDemandSupply(adpElement);
				adpeList.set(ctr,adpElement);
			}

		}catch(Exception e){
			throw(e);
		}
	}
	private ADPElement populateDemandSupply(ADPElement adpElement) throws Exception
	{
		double demand = 0;
		double supply = 0;
		java.sql.Timestamp dueDate = null;
		String itemCode = null;
		String siteCode = null;
		// 12/04/10 manoharan MF90BHU001
		double stockQty = 0;
		double othSupply = 0;
		// end 12/04/10 manoharan MF90BHU001
		Statement stmt = null;
		ResultSet rs = null;
		errString = "";
		String adpQuery = null;
		PreparedStatement pstmt = null;
		String saleOrder = null;
		String lineNo = null;


		try
		{
			itemCode = adpElement.getItemCode();
			siteCode = adpElement.getSiteCode();
			// 01/04/11 manoharan if made to order is selected and item is of made to order
			// don't process here
			if ("0".equals(madeToOrder) && "O".equals(adpElement.getMfgType()))
			{
				return adpElement;
			}
			// end 01/04/11 manoharan
			//System.out.println("commonConstants.DB_NAME "+CommonConstants.DB_NAME);
			// 14-02-2007 manoharan
			String sqlFileName = null;
			if ( matPlan.equalsIgnoreCase("N") && mfgPlan.equalsIgnoreCase("N"))
			{
				sqlFileName = CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplyforecastonly" ;
			}
			else if ( matPlan.equalsIgnoreCase("Y") && mfgPlan.equalsIgnoreCase("N"))
			{
				sqlFileName = CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplymaterial" ;
			}
			else
			{
				sqlFileName = CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupply" ;
			}
			// end 14-02-2007 manoharan



			if((CommonConstants.DB_NAME).equalsIgnoreCase("db2"))
			{
				//System.out.println("Firing DB2 Sql For Site Code :" + siteCode + " Item Code : " + itemCode + " From Date : " + dateFrom + " To Date : "+ dateTo + " StandgOrdTypes : "+ standingOrdTypes +" MadeToOrder " +madeToOrder+ " MadeToStock : " + madeToStock);
				// 14-02-2007 manoharan
				//String adpQueryOriginal = readFile(CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplyDB2.sql");
				String adpQueryOriginal = readFile(sqlFileName+ "DB2.sql");
				// end 14-02-2007 manoharan

				String strDateFrom = dateFrom.toString();
				strDateFrom = strDateFrom.replaceAll(" ","-");
				strDateFrom = strDateFrom.replaceAll(":",".");
				String strDateTo = dateTo.toString();
				strDateTo = strDateTo.replaceAll(" ","-");
				strDateTo = strDateTo.replaceAll(":",".");
				/*// 01-10-2007 manoharan
				String strRunDate = runDate.toString();
				strRunDate = strRunDate.replaceAll(" ","-");
				strRunDate = strRunDate.replaceAll(":",".");
				// end 01-10-2007 manoharan*/
				adpQuery = adpQueryOriginal;
				//System.out.println("Afetr Reading File adpQuery..");
				adpQuery = adpQuery.replaceAll("@sitecode@",siteCode);
				adpQuery = adpQuery.replaceAll("@itemcode@",itemCode);
				adpQuery = adpQuery.replaceAll("@fromdate@",dateFromString);
				adpQuery = adpQuery.replaceAll("@todate@",dateToString );
				adpQuery = adpQuery.replaceAll("@ordtypes@",standingOrdTypes);
				adpQuery = adpQuery.replaceAll("@madetoorder@",madeToOrder);
				adpQuery = adpQuery.replaceAll("@madetostock@",madeToStock);
				// 01-10-2007 manoharan
				adpQuery = adpQuery.replaceAll("@rundate@",runDateString );
				// end 01-10-2007 manoharan
			}
			else if((CommonConstants.DB_NAME).equalsIgnoreCase("oracle"))
			{
				// 14-02-2007 manoharan
				//String adpQueryOriginal = readFile(CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplyOracle.sql");
				String adpQueryOriginal = readFile(sqlFileName + "Oracle.sql");
				// end 14-02-2007 manoharan
				adpQuery = adpQueryOriginal;
				//System.out.println("Replacing Values.in SQL.....");
				adpQuery = adpQuery.replaceAll("@sitecode@",siteCode);
				adpQuery = adpQuery.replaceAll("@itemcode@",itemCode);
				adpQuery = adpQuery.replaceAll("@fromdate@",dbDateFrom);
				adpQuery = adpQuery.replaceAll("@todate@",dbDateTo);
				adpQuery = adpQuery.replaceAll("@ordtypes@",standingOrdTypes);
				adpQuery = adpQuery.replaceAll("@madetoorder@",madeToOrder);
				adpQuery = adpQuery.replaceAll("@madetostock@",madeToStock);
				// 01-10-2007 manoharan
				adpQuery = adpQuery.replaceAll("@rundate@",dbRunDate);
				// end 01-10-2007 manoharan
				//System.out.println("Replaced Values.in SQL.....");
			}
			FileOutputStream fout = null;
			BufferedWriter bw = null;
			fout = new FileOutputStream("demandsupply.sql");
			bw = new BufferedWriter(new OutputStreamWriter(fout));
			bw.write(adpQuery);
			bw.close();
			fout.close();

			//stmt = conn.createStatement();
			//System.out.println("Executing Query After Replacement.....");
			//rs = stmt.executeQuery(adpQuery);
			pstmt = conn.prepareStatement(adpQuery);
			rs = pstmt.executeQuery();

			//System.out.println("Query Executed for item : " + itemCode);
			String rsItemCode = "",adpItemcode = "";
			while(rs.next())
			{//item code ==         != sitecode ,item find adpelemt list index demand, supply
				rsItemCode = rs.getString(1);//Added by JIten 02/11/06 as per Manoharan Sir
				dueDate = rs.getTimestamp(2);
				demand = rs.getDouble(3);
				supply = rs.getDouble(4);
				// 12/04/10 manoharan MF90BHU001 stock and other supply to be separated
				stockQty =  rs.getDouble(5);
				othSupply =  rs.getDouble(6);
				// end 12/04/10 manoharan
				// 18-11-2006 manoharan if dueDate < dateFrom then set the dueDate as dateFrom
				if (dueDate.compareTo(dateFrom) < 0 )
				{
					dueDate = dateFrom;
				}
				// end 18-11-2006 manoharan
				//if else condition added by Jiten as per Manoharan Sir 02/11/06
				if(rsItemCode != null && rsItemCode.trim().equalsIgnoreCase(itemCode.trim()))
				{
					adpElement.setDemand(dueDate, demand,"          ","   ");
					adpElement.setSupply(dueDate, supply);
					// 12/04/10 manoharan MF90BHU001
					adpElement.setStockQty(dueDate, stockQty);
					adpElement.setOthSupply(dueDate, othSupply);
					// end 12/04/10 manoharan MF90BHU001
				}
				else
				{
					System.out.println("Site Code :: "+siteCode+" rsItemCode :: "+rsItemCode+" Item Code :: "+itemCode);
					int ctr = indexOfADPElement(siteCode, rsItemCode,"          ","   ");
					//System.out.println("AdpList : "+adpeList.size()+"Index ctr :: "+ctr);//Added by  Jiten 09/11/06 Sun
					if(ctr > 0){//if condition added by jiten as per Manoharan Sir 10/11/06
						ADPElement tempADPElement = (ADPElement)adpeList.get(ctr);
						tempADPElement.setDemand(dueDate, demand,"          ","   ");
						tempADPElement.setSupply(dueDate, supply);
						adpeList.set(ctr,tempADPElement);
					}else if(ctr == -1){//Else if condition added by jiten 10/11/06 as per Tarun Bhai to write for skipped item
						this.fw1.write(siteCode+"	 "+itemCode+"	"+rsItemCode+"\r\n");
					}
				}
				//End addding
				if(demand != 0 || supply != 0)
				{
					if(adpElement.getSuppSour().equals("M"))
						//System.out.println("adpQuery ::"+adpQuery);
					System.out.println("Site Code : "+siteCode+"   Item Code : "+itemCode+"    Due Date : "+dueDate+"     Demand : "+demand+" supply : " + supply);
				}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB : populateDemandSupply(ADPElement).." +se.getMessage());
			System.out.println(adpQuery);
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB : populateDemandSupply(ADPElement):...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmt != null) { pstmt = null; }
			if (rs != null) { rs = null; }
			this.fw1.flush();//Added by jiten 10/11/06
			//System.out.println("populateDemandSupply() finally block......");
//			return adpElement;
		}
		//ResultSetMetaData rsmd = rs.getMetaData();
		//int noOfColumns = rsmd.getColumnCount();

		//return commented & changed by sabyasachi 25-03-2011
		//return adpElement; //Moved here from finaly - jiten - 03/11/06
		return adpElement;
		//end return commented & changed by sabyasachi 25-03-2011
	}
////////////////////////////////////////////////////////////////////////////
	private void populateMTODemandSupply(String siteCode, String itemCode,ADPElement adpElement) throws Exception
	{
		double demand = 0;
		double supply = 0;
		java.sql.Timestamp dueDate = null;
		//String itemCode = null;
		//String siteCode = null;
		// 12/04/10 manoharan MF90BHU001
		double stockQty = 0;
		double othSupply = 0;
		// end 12/04/10 manoharan MF90BHU001
		Statement stmt = null;
		ResultSet rs = null;
		errString = "";
		String adpQuery = null;
		PreparedStatement pstmt = null;
		String saleOrder = null,rsItemCode = "";
		String lineNo = null;


		try
		{
			//itemCode = adpElement.getItemCode();
			//siteCode = adpElement.getSiteCode();
			//ADPElement adpElement = new ADPElement(); // 01/04/11 manoharan dummy for nothing
			if (!"0".equals(madeToOrder) || !"O".equals(adpElement.getMfgType()))
			{
				return;
			}

			//System.out.println("commonConstants.DB_NAME "+CommonConstants.DB_NAME);
			// 14-02-2007 manoharan
			String sqlFileName = null;
			sqlFileName = CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "madeToOrder" ;
			// end 14-02-2007 manoharan


			//added by Sabyasachi 30-03-2011

			if((CommonConstants.DB_NAME).equalsIgnoreCase("db2"))
			{
				//System.out.println("Firing DB2 Sql For Site Code :" + siteCode + " Item Code : " + itemCode + " From Date : " + dateFrom + " To Date : "+ dateTo + " StandgOrdTypes : "+ standingOrdTypes +" MadeToOrder " +madeToOrder+ " MadeToStock : " + madeToStock);
				// 14-02-2007 manoharan
				//String adpQueryOriginal = readFile(CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplyDB2.sql");
				String adpQueryOriginal = readFile(sqlFileName+ "DB2.sql");
				// end 14-02-2007 manoharan

				String strDateFrom = dateFrom.toString();
				strDateFrom = strDateFrom.replaceAll(" ","-");
				strDateFrom = strDateFrom.replaceAll(":",".");
				String strDateTo = dateTo.toString();
				strDateTo = strDateTo.replaceAll(" ","-");
				strDateTo = strDateTo.replaceAll(":",".");
				/*// 01-10-2007 manoharan
				String strRunDate = runDate.toString();
				strRunDate = strRunDate.replaceAll(" ","-");
				strRunDate = strRunDate.replaceAll(":",".");
				// end 01-10-2007 manoharan*/
				adpQuery = adpQueryOriginal;
				//System.out.println("Afetr Reading File adpQuery..");
				adpQuery = adpQuery.replaceAll("@sitecode@",siteCode);
				adpQuery = adpQuery.replaceAll("@itemcode@",itemCode);
				adpQuery = adpQuery.replaceAll("@fromdate@",dateFromString);
				adpQuery = adpQuery.replaceAll("@todate@",dateToString );
				adpQuery = adpQuery.replaceAll("@ordtypes@",standingOrdTypes);
				adpQuery = adpQuery.replaceAll("@madetoorder@",madeToOrder);
				adpQuery = adpQuery.replaceAll("@madetostock@",madeToStock);
				// 01-10-2007 manoharan
				adpQuery = adpQuery.replaceAll("@rundate@",runDateString );
				// end 01-10-2007 manoharan
			}
			else if((CommonConstants.DB_NAME).equalsIgnoreCase("oracle"))
			{
				// 14-02-2007 manoharan
				//String adpQueryOriginal = readFile(CommonConstants.JBOSSHOME + File.separator +"sql" + File.separator + "demandsupplyOracle.sql");
				String adpQueryOriginal = readFile(sqlFileName + "Oracle.sql");
				// end 14-02-2007 manoharan
				adpQuery = adpQueryOriginal;
				//System.out.println("Replacing Values.in SQL.....");
				adpQuery = adpQuery.replaceAll("@sitecode@",siteCode);
				adpQuery = adpQuery.replaceAll("@itemcode@",itemCode);
				adpQuery = adpQuery.replaceAll("@fromdate@",dbDateFrom);
				adpQuery = adpQuery.replaceAll("@todate@",dbDateTo);
				adpQuery = adpQuery.replaceAll("@ordtypes@",standingOrdTypes);
				adpQuery = adpQuery.replaceAll("@madetoorder@",madeToOrder);
				adpQuery = adpQuery.replaceAll("@madetostock@",madeToStock);
				// 01-10-2007 manoharan
				adpQuery = adpQuery.replaceAll("@rundate@",dbRunDate);
				// end 01-10-2007 manoharan
				//System.out.println("Replaced Values.in SQL.....");
			}
			FileOutputStream fout = null;
			BufferedWriter bw = null;
			fout = new FileOutputStream("demandsupply.sql");
			bw = new BufferedWriter(new OutputStreamWriter(fout));
			bw.write(adpQuery);
			bw.close();
			fout.close();

			pstmt = conn.prepareStatement(adpQuery);
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				saleOrder = rs.getString(1);
				lineNo = rs.getString(2);
				rsItemCode = rs.getString(3);
				dueDate = rs.getTimestamp(4);
				demand =  rs.getDouble(5);
				supply = rs.getDouble(6);
				stockQty = rs.getDouble(7);
				othSupply =  rs.getDouble(8);
				if (dueDate.compareTo(dateFrom) < 0 )
				{
					dueDate = dateFrom;
				}

				/*if(rsItemCode != null && rsItemCode.trim().equalsIgnoreCase(itemCode.trim()) && (adpElement.getSaleOrder().equals(saleOrder)) && (adpElement.getLineNo().equals(lineNo)))
				{
					adpElement.setDemand(dueDate, demand, saleOrder, lineNo);
					adpElement.setSupply(dueDate, supply);
					adpElement.setStockQty(dueDate, stockQty);
					adpElement.setOthSupply(dueDate, othSupply);
				}
				else
				{	*/
					// 01/04/11 find same item without sales order, if exists remove from array
					/*
					int ctr = indexOfADPElement(siteCode, rsItemCode,"          ","   ");
					//
					if(ctr > -1)
					{
						adpeList.remove(ctr);
					}
					// end 01/04/11*/
					int ctr = indexOfADPElement(siteCode, rsItemCode,saleOrder,lineNo);
					System.out.println("01/04/11 BHU saleOrder [" + saleOrder + "] lineNo [" + lineNo + "] ctr [" + ctr + "]");

					if(ctr > -1)
					{
						ADPElement tempADPElement = (ADPElement)adpeList.get(ctr);
						tempADPElement.setDemand(dueDate, demand, saleOrder, lineNo);
						tempADPElement.setSupply(dueDate, supply);
						adpeList.set(ctr,tempADPElement);
					}
					else if(ctr == -1)
					{
						ADPElement newADPElement = new ADPElement();
						newADPElement.setSiteCode(siteCode);
						newADPElement.setItemCode(rsItemCode);
						newADPElement = setAdpeValues(newADPElement);
						newADPElement.setSaleOrder(saleOrder);
						newADPElement.setLineNo(lineNo);
						newADPElement.setDemand(dueDate, demand, saleOrder, lineNo);
						newADPElement.setSupply(dueDate, supply);
						adpeList.add(newADPElement);
						System.out.println("01/04/11 BHU not found adding saleOrder [" + saleOrder + "] lineNo [" + lineNo + "] ctr [" + ctr + "]");
					}
				//}
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			//added by Sabyasachi ends 30-03-2011
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB : populateDemandSupply(ADPElement).." +se.getMessage());
			System.out.println(adpQuery);
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB : populateDemandSupply(ADPElement):...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmt != null) { pstmt = null; }
			if (rs != null) { rs = null; }
			this.fw1.flush();//Added by jiten 10/11/06
			//System.out.println("populateDemandSupply() finally block......");
//			return adpElement;
		}
		//ResultSetMetaData rsmd = rs.getMetaData();
		//int noOfColumns = rsmd.getColumnCount();

		//return commented & changed by sabyasachi 25-03-2011
		//return adpElement; //Moved here from finaly - jiten - 03/11/06
		//return adpElement;
		//end return commented & changed by sabyasachi 25-03-2011
	}
////////////////////////////////////////////////////////////////////////////
	private String mrpOptimize() throws RemoteException,ITMException
	{
		String errString = "";
		ADPElement adpElement = null;

		try
		{
			for (int cycle = 1; cycle <= processCycle; cycle++)
			{
				System.out.println("Processing level no : " + cycle + " of " + processCycle);
				//System.out.println("Size of Adpe List : " + adpeList.size());
				for (int adpeCtr = 0; adpeCtr < adpeList.size(); adpeCtr++)
				{
					adpElement = (ADPElement)adpeList.get(adpeCtr);
					if (cycle == 1 )
					{
			            errString = deleteOldPlan(adpElement.getSiteCode(), adpElement.getItemCode());
			            if ( errString != null && errString.trim().length() > 0 )
						{
							System.out.println("Error While deleting old plan:..." + errString);
							return errString;
						}
					}

					adpElement = mrpOptimize(adpElement);
					adpeList.set(adpeCtr,adpElement);
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :mrpOptimize():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
	    return errString;
	}

	private ADPElement mrpOptimize(ADPElement adpElement) throws RemoteException,ITMException
	{
             String errString = "";
             java.sql.Timestamp dueDate =  null;

             double demand = 0;
             double supply = 0;
             double runningDemand = 0;
             double runningSupply = 0;
             double runningPlanSupply = 0;
             double quantity = 0;
             double batchQty = 0;
             double yieldPerc = 0;
             double minPlanPerc = 0;
             String suppSour;
             String lineType = "B";
             String bomCode = "";
             String siteCode = "";
             String siteCodeSupp = "";
             String itemCode = "";
             String sDueDate = "";
             String orderOpt = "";
             String batchQtyType = "";
             int mfgLeadTime = 0;
             int qcLeadTime = 0;
			 // 22/06/10 manoharan
			 String mfgLeadBasis = "F";
			 double batchSizeLead = 0;
			 // end 22/06/10 manoharan
             ExplodeBom explodeBomObj = new ExplodeBom();

             demand = adpElement.getDemand();
             supply = adpElement.getSupply();
             suppSour = adpElement.getSuppSour();
             bomCode = adpElement.getBomCode();
             siteCode = adpElement.getSiteCode();
             itemCode = adpElement.getItemCode();
             mfgLeadTime = adpElement.getMfgLeadTime();
             qcLeadTime = adpElement.getQcLeadTime();
             batchQty = adpElement.getBatchQty();
             yieldPerc = adpElement.getYieldPerc();
             batchQtyType = adpElement.getBatchQtyType();
             minPlanPerc = adpElement.getMinPlanPerc();
			 // 22/06/10 manoharan
			 mfgLeadBasis = adpElement.getMfgLeadBasis();
			 batchSizeLead = adpElement.getBatchSizeLead();
			 // end 22/06/10 manoharan

             System.out.println("14012009 Optimize site : " + siteCode + " Item : " + itemCode + " Demand : "+demand+"  Supply : "+supply);
             try
             {
                  if ((demand - supply) > 0)
                  {
                     ArrayList timeMRParr = adpElement.getTimeMrpList();
                     for (int timeCtr = 0; timeCtr < timeMRParr.size() ; timeCtr++)
                     {
                         TimeMRP timeMrp = (TimeMRP)timeMRParr.get(timeCtr);
                         runningDemand = timeMrp.getRunningDemand();
                         runningSupply = timeMrp.getRunningSupply();
                         runningPlanSupply = timeMrp.getRunningPlanSupply();
                         if((suppSour.equals("M")) || (suppSour.equals("C"))) //&& orderOpt.equals("B") )
                         	{
							 	runningPlanSupply = runningPlanSupply / 100 * yieldPerc;//added by jiten as per Manoharan Sir 01/11/06
							}
                         // 10-04-2006 manoharan
                         // end 10-04-2006 manoharan
                         SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
                         dueDate = timeMrp.getDueDate();
                         sDueDate = simpleDateFormat.format(dueDate);
                         quantity = runningDemand - runningSupply - runningPlanSupply;
                        if   (quantity > 0)
                        {
							// 28-11-2006 manoharan
                           if((suppSour.equals("M")) || (suppSour.equals("C"))) //&& orderOpt.equals("B") )
                           {
                               quantity = Math.round(((quantity) * 100) / yieldPerc);
                           }
                           // 10-04-2006 manoharan

                           quantity = calculateQuantity(adpElement, dueDate, quantity);

                        }
                        /*else
                        {
                        	System.out.println("Optimize site " + siteCode + " Item " + itemCode + " runDem " + decFormat(runningDemand) + " runSup " +  decFormat(runningSupply) + " runPlanSup " + decFormat(runningPlanSupply) + " cal "+ decFormat(quantity) );
                        }*/

                         if (quantity > 0)
                         {
                         	/*********************
                           if((suppSour.equals("M")) || (suppSour.equals("C"))) //&& orderOpt.equals("B") )
                           {
                               quantity = Math.round((quantity * 100) / yieldPerc);
                           }
                           // 10-04-2006 manoharan
                           **********/
                           if(itemCode.trim().equalsIgnoreCase("BD0783"))
                           {
                           	System.out.println("Mrpoptimize site " + siteCode + " Item " + itemCode + " runDem " + decFormat(runningDemand) + " runSup " +  decFormat(runningSupply) + " runPlanSup " + decFormat(runningPlanSupply) + " cal "+ decFormat(quantity) + " Actual " +  decFormat(runningDemand - runningSupply - runningPlanSupply) );
                           }

						   //System.out.println("Actual " +  decFormat(runningDemand - runningSupply - runningPlanSupply) + " cal "+ decFormat(quantity) );
                           timeMrp.setPlanSupply(quantity);
                           adpElement.setSupply(dueDate, 0); // for the sake of updating running demand, supplu & planSupply
                           // independent demand ************************************
                           //if (suppSour.equals("P") && adpElement.getSiteCodeSupp().trim() > 0 ) Changed by sudhir
                           if (suppSour.equals("P") && adpElement.getSiteCodeSupp() !=  null && adpElement.getSiteCodeSupp().trim().length() > 0 )
                           {
                           		updateIndependentDemand(adpElement.getSiteCodeSupp(), itemCode, quantity, dueDate, siteCode);
                           }
                           // ???????? independent demand *****************************

                           if (suppSour.equals("D"))
                           {
                              // generate a demand for the supply site
                               if (balIntersite.equals("Y"))
                               {
                                   String siteIdst = null;
                                   siteIdst = getSurplusStockSite(siteCode,itemCode,quantity,surplusList);
                                   // = wf_get_idst_site(ld_dist_intersite,ld_surplus_ext,ls_site_code,ls_item_code,lc_reo_qty)
                                   if (siteIdst != null && siteIdst.trim().length() > 0)
                                   {
                                           siteCodeSupp = siteIdst;
                                   }
                                    if (siteCodeSupp.trim().length() > 0)
                                    {
                                       siteCodeSupp = adpElement.getSiteCodeSupp();
                                    }
									if(siteIdst == null)
									{
							          siteCodeSupp = getSiteCodeSupp(siteCode, itemCode);
							          System.out.println("SiteCodeSupp "+siteCodeSupp+" if siteCodeSupp is null"); //uttam
							        }
                               }
                               else
                               {
                               		siteCodeSupp = getSiteCodeSupp(siteCode, itemCode);
                               }
                               updateSupplySite(siteCodeSupp, itemCode, quantity,dueDate);
                           }
                           // end 10-04-2006 manoharan
                           if (suppSour != null && suppSour.trim().equals("M") && matPlan.trim().equals("Y"))
                           {

							// 22/06/10 manoharan to calculate due_date based on batch size
							// if configured as lead time per batch
							if (mfgLeadBasis ==  null)
							{
								mfgLeadBasis = "F";
							}

							if ("B".equals(mfgLeadBasis) && quantity > batchSizeLead )
							{
								System.out.println("manohar  mfgLeadTime before ["+mfgLeadTime + "]");
								mfgLeadTime = (int) (quantity * mfgLeadTime / batchSizeLead);
								System.out.println("manohar  mfgLeadTime after ["+mfgLeadTime + "]");
							}
							// end 22/06/10 manoharan
                             StringBuffer xmlString = new StringBuffer("<Root>");
                             String retXMLStr = null;
                             xmlString.append("<Detail>");
                             xmlString.append("<site_code>").append("<![CDATA[").append(siteCode).append("]]>").append("</site_code>");
                             xmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>");
                             xmlString.append("<line_type>").append("<![CDATA[").append(lineType).append("]]>").append("</line_type>");
                             xmlString.append("<quantity>").append("<![CDATA[").append(quantity).append("]]>").append("</quantity>");
                             xmlString.append("<bom_code>").append("<![CDATA[").append(bomCode).append("]]>").append("</bom_code>");
                             xmlString.append("<due_date>").append("<![CDATA[").append(sDueDate).append("]]>").append("</due_date>");
                             xmlString.append("<mfg_lead_time>").append("<![CDATA[").append(mfgLeadTime).append("]]>").append("</mfg_lead_time>");
                             xmlString.append("<qc_lead_time>").append("<![CDATA[").append(qcLeadTime).append("]]>").append("</qc_lead_time>");
                             xmlString.append("</Detail>");
                             xmlString.append("</Root>");
                             //retXMLStr = explodeBomObj.explodeBom(xmlString.toString(), conn).toString();//Commented by Jiiten as per Kandarp Sir 13/11/06 -

							 retXMLStr = explodeBomObj.explodeBom(xmlString.toString()).toString(); // Added by Jiten 13/11/06 -
							 if (itemCode.trim().equals("BF21135"))
							 {
							 	System.out.println("14012009 itemCode [" + itemCode + "] explodeBomObj  retXMLStr [" + retXMLStr + "]");
							 }

                             if (xmlString == null || xmlString.toString().trim().length() == 0)
                             {
                                     errString = "XML String is Either null";
                                     //return //errString;
                             }
                             else
                             {
								if ("O".equals(adpElement.getMfgType()))
								{
									updateDemand(retXMLStr,adpElement.getSaleOrder(),adpElement.getLineNo());
								}
								else
								{
									updateDemand(retXMLStr,"          ","   ");
								}
                             }
                           }
                         } // inner -- if(runningDemand - runningSupply)
                     } // inner -- for(timectr)
                  } // outer -- if(demand - supply)
             }
             catch(Exception e)
             {
                     System.out.println("Exception :RunMRPPrcEJB :mrpOptimize (ADPElement):" + e.getMessage() + ":");
                     e.printStackTrace();
                     throw new ITMException(e);
             }
             return adpElement;//errString;
	}
    private void updateSupplySite(String siteCode, String itemCode, double quantity, java.sql.Timestamp dueDate) throws ITMException, Exception
    {
       ADPElement adpElement;
       int found = -1;
		try
		{
	                for (int adpeCtr = 0; adpeCtr < adpeList.size(); adpeCtr++)
	                {
	                        adpElement = (ADPElement)adpeList.get(adpeCtr);
	                        if (adpElement.getItemCode().trim().equals(itemCode.trim()) && adpElement.getSiteCode().trim().equals(siteCode.trim()) )
	                        {
	                           adpElement.setDemand(dueDate,quantity,"          ","   ");
	                           found = adpeCtr;
	                           adpeList.set(adpeCtr,adpElement);
	                           break;
	                        }
	                }
	                if (found == -1)
	                {
	                   adpElement = new ADPElement();
	                   adpElement.setItemCode(itemCode);
	                   adpElement.setSiteCode(siteCode);
	                   adpElement = setAdpeValues(adpElement);
	                   adpElement.setDemand(dueDate,quantity,"          ","   ");
	                   adpeList.add(adpElement);
	                }
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :mrpOptimize():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}

    }

    private void updateIndependentDemand(String siteCode, String itemCode, double quantity, java.sql.Timestamp dueDate, String siteCodeCust) throws ITMException, Exception
    {
       ADPElement adpElement;
       int found = -1;
		try
		{
	                for (int adpeCtr = 0; adpeCtr < adpeList.size(); adpeCtr++)
	                {
	                        adpElement = (ADPElement)adpeList.get(adpeCtr);
	                        if (adpElement.getItemCode().trim().equals(itemCode.trim()) && adpElement.getSiteCode().trim().equals(siteCode.trim()) )
	                        {
	                           adpElement.setIndependentDemand(dueDate,quantity);
	                           adpElement.setSiteCodeCust(siteCodeCust);
	                           found = adpeCtr;
	                           adpeList.set(adpeCtr,adpElement);
	                           break;
	                        }
	                }
	                if (found == -1)
	                {
	                   adpElement = new ADPElement();
	                   adpElement.setItemCode(itemCode);
	                   adpElement.setSiteCode(siteCode);
	                   adpElement = setAdpeValues(adpElement);
	                   adpElement.setIndependentDemand(dueDate,quantity);
	                   adpElement.setSiteCodeCust(siteCodeCust);
	                   adpeList.add(adpElement);
	                }
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :mrpOptimize():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}

    }

	private double calculateQuantity(ADPElement adpElement, java.sql.Timestamp dueDate, double reqQty) throws ITMException, Exception
	{
		double demand = 0;
		double supply = 0;
		double batchQty = 0;
		double yieldPerc = 0;
		double minPlanPerc = 0;
        double integralQty = 0;
        double reoQty = 0;
		String suppSour;
		String batchQtyType = "";
		String orderOpt = "";
        int minStkDay = 0;

        double runningDemand = 0;
		double runningSupply = 0;
		double stkLevel = 0;
        double chkOrdQty = 0;
        int multiplier = 1;
        double reqQtyOriginal = reqQty;

		demand = adpElement.getDemand();
		supply = adpElement.getSupply();
		batchQty = adpElement.getBatchQty();
		yieldPerc = adpElement.getYieldPerc();
		minPlanPerc = adpElement.getMinPlanPerc();
        integralQty = adpElement.getIntegralQty();
        reoQty = adpElement.getReoQty();
		suppSour = adpElement.getSuppSour();
		batchQtyType = adpElement.getBatchQtyType();
        orderOpt = adpElement.getOrderOpt();
        minStkDay = adpElement.getMinStkDay();
		System.out.println("25022010 Item " + adpElement.getItemCode() + " integralQty " + decFormat(integralQty) + " req qty " + decFormat(reqQty) + " Batch Qty " + decFormat(batchQty) );
        if ( minStkDay > 0 && mrpDynMinStk.equals("Y") )
        {
            stkLevel = getStkLevel(adpElement, dueDate);
        }
        else
        {
            stkLevel = adpElement.getMinQty();
            if ( minOrderChk.equals("Y") )
            {
                chkOrdQty = adpElement.getMinOrderQty();
            }
            else
            {
                chkOrdQty = stkLevel;
            }
        }

       	System.out.println("25022010 1-> chkOrdQty " + decFormat(chkOrdQty) + " reqQty " + decFormat(reqQty) + " stkLevel  " + decFormat(stkLevel) + " reoQty " + decFormat(reoQty));

		if (reqQty >  chkOrdQty && chkOrdQty > 0)
        {
            if ( minOrderChk.equals("Y") )
            {
                multiplier = new Double(Math.ceil( (reqQty  + stkLevel)  / integralQty)).intValue();
				System.out.println("25022010 2->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
            }
             else
            {
                if (mfgPlan.equals("N") && matPlan.equals("N") )
                {
                    multiplier = new Double(Math.ceil(((reqQty  + stkLevel)) / integralQty)).intValue();
					System.out.println("25022010 3->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
                }
                else
                {
					if (reqQty > 0 && reqQty > Math.floor(reqQty))
					{
							if (integralQty > 1)
							{
									multiplier = new Double(Math.ceil((reqQty + stkLevel) / integralQty)).intValue();
									System.out.println("25022010 4->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
							}
							else
							{
									multiplier = new Double(Math.ceil(reqQty + stkLevel)).intValue();//(new Double(reqQty + stkLevel).intValue()) + 1; // 25/02/10
									System.out.println("25022010 5->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
							}
					}
					else
					{
							multiplier = new Double(Math.ceil( (reqQty + stkLevel) / integralQty)).intValue();
							System.out.println("25022010 6->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
					}
                }
            }
        }
       integralQty = multiplier * integralQty;
       multiplier = 1;
	   System.out.println("25022010 7->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
       if (integralQty > reoQty)
       {
          reoQty = integralQty;
       }
	   System.out.println("25022010 8->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		if ( reoQty == 0)
		{
		   reoQty = reqQty + stkLevel;
		}
		System.out.println("25022010 8->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		if ( minOrderChk.equals("Y") )
		{
			 multiplier = new Double(Math.ceil( (stkLevel + reqQty ) / reoQty)).intValue();
			 System.out.println("25022010 9->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		}
		else
		{
			 if (mfgPlan.equals("N") && matPlan.equals("N") )
			 {
					 multiplier = new Double(Math.ceil((reqQty + stkLevel)/ reoQty)).intValue();
					 System.out.println("25022010 10->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
			 }
			 else
			 {
				if (reqQty > 0 && reqQty > Math.floor(reqQty))
				{
				  if (reoQty > 1)
				  {
					 multiplier = new Double(Math.ceil((reqQty + stkLevel) / reoQty)).intValue();
					 System.out.println("25022010 11->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
				  }
				  else
				  {
					  multiplier = new Double(Math.ceil(reqQty + stkLevel)).intValue() ; // 25/02/10
					  System.out.println("25022010 12->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
				  }
				}
				else
				{


				   multiplier = new Double(Math.ceil( (reqQty + stkLevel) / reoQty)).intValue();
				   System.out.println("25022010 13->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");

				}
			 }
		}
		if ( minOrderChk.equals("Y") )
		{
		  if (reqQty < chkOrdQty && adpElement.getMinQty() < reoQty)
		  {
			 reqQty = reoQty;
			 System.out.println("25022010 14->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		  }
		  else
		  {
			 reqQty = reoQty * multiplier;
			 System.out.println("25022010 15->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		  }
		}
		else
		{
		   reqQty = reoQty * multiplier;
		   System.out.println("25022010 16->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
		}

		if (orderOpt.equals("B") && !batchQtyType.equals("V"))
       {

         multiplier = 0;
         if  (reqQty > batchQty)
         {
            if ( getMod(reqQty, batchQty) > 0 )
           {
                if ( getMod(reqQty,batchQty) >= (batchQty * minPlanPerc / 100 ))
                {
                   multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() + 1;
				   System.out.println("25022010 17->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
                }
                else
                {
                  multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
				  System.out.println("25022010 18->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
                }
           }
           else
           {
              multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() ;
			  System.out.println("25022010 19->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
           }
          }
         else
         {
         	// 26/12/07 manoharan consider min_plan_perc
         	if (reqQty >= (batchQty * minPlanPerc / 100 ) )
         	{
         		multiplier = 1;
				System.out.println("25022010 20->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
         	}
         	else
         	{
         		multiplier = 0;
				System.out.println("25022010 21->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
         	}

         }
         reqQty = multiplier * batchQty;
		 System.out.println("25022010 22->Item [" + adpElement.getItemCode() + "] reqQty [" + reqQty + "] reoQty [" + reoQty + "]] integralQty [" + integralQty + "] multiplier ["+multiplier + "]");
       }

		return reqQty;
	}
    private double getMod(double total, double batch) throws ITMException, Exception
    {
       int multiplier = 0;
       multiplier = new Double(Math.floor(total / batch)).intValue();
       return total - (multiplier * batch);
    }
    private double getStkLevel(ADPElement adpElement, java.sql.Timestamp dueDate) throws ITMException, Exception
	{
		TimeMRP timeMRP = null;
		int timeEle = -1, ctr;
                ArrayList timeMRPArr = adpElement.getTimeMrpList();
                java.sql.Timestamp tmpDate;
                double retValue = 0;
		try
		{
			if (timeMRPArr.size() > 0)
			{
                          for (ctr = 0; ctr < timeMRPArr.size(); ctr++)
                          {
                            timeMRP = (TimeMRP)timeMRPArr.get(ctr);
                            if (timeMRP.getDueDate() != null)
                            {
                              if (timeMRP.getDueDate().equals(dueDate))
                              {
                                      timeEle = ctr;
                                      break;
                              }
                            }
                          }
			}
			if (timeEle >  -1)
			{
               java.sql.Timestamp uptoDate = null;

               Calendar calen = Calendar.getInstance();
               calen.setTimeInMillis(dueDate.getTime());
               calen.add(Calendar.DAY_OF_MONTH,adpElement.getMinStkDay());
               uptoDate.setTime(calen.getTimeInMillis());

               for (ctr = timeEle; ctr < timeMRPArr.size(); ctr++)
               {
                 timeMRP = (TimeMRP)timeMRPArr.get(timeEle);
                 tmpDate =  timeMRP.getDueDate();
                 if (tmpDate.compareTo(uptoDate) <= 0)
                 {
                  retValue += timeMRP.getDemand();
                 }
                 else
                 {
                   break;
                 }
               }
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :adpeLEMENT : updateTimeMrp(Date, supply, demand):...." + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
        return retValue;
	}

	private void updateDemand(String xmlString) throws ITMException, Exception
	{	updateDemand(xmlString,"          ","   ");
	}
	private void updateDemand(String xmlString,String saleOrder, String lineNo) throws ITMException, Exception
	{
             Document dom;
             String siteCode = null;
             String itemCode = null;
             double quantity = 0;
             String lineType = null;
             String sdate = null;
             java.sql.Timestamp dueDate =  null;
             NodeList nodeList = null;
             Node parentNode = null;
             Node childNode = null;
             String nodeName = null;
             String nodeValue = null;
             int totalNodes = 0;
             int ctr = 0;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql = null;
             ADPElement adpElement = null;
             try
             {
				System.out.println("In updateDemand xmlString [" + xmlString + "]");
                dom = genericUtility.parseString(xmlString);
                nodeList = dom.getElementsByTagName("Detail");
                totalNodes = nodeList.getLength();

                for (ctr = 0;ctr < totalNodes; ctr ++)
                {
		    		Node node = nodeList.item(ctr);
		    		nodeName = node.getNodeName();
		    		Node valueNode = node.getFirstChild();
		    		while (valueNode != null)
		              {
		                   nodeName = valueNode.getNodeName();
		                   nodeValue = valueNode.getFirstChild().getNodeValue();
		                   if (nodeName.equals("site_code"))
		                   {
		                           siteCode = nodeValue;
		                   }
		                   if (nodeName.equals("item_code"))
		                   {
		                           itemCode = nodeValue;
		                   }
		                   if (nodeName.equals("quantity"))
		                   {
		                           quantity = Double.parseDouble(nodeValue);
		                   }
		                   if (nodeName.equals("line_type"))
		                   {
		                           lineType = nodeValue;
		                   }
		                   if (nodeName.equals("due_date"))
		                   {
		                           dueDate = java.sql.Timestamp.valueOf(nodeValue + " 00:00:00");
		                           sdate = nodeValue ;
		                   }
		                   valueNode = valueNode.getNextSibling();
		              }
	             	sql = "SELECT CASE WHEN ITEM_CODE__PLAN IS NULL THEN ITEM_CODE ELSE ITEM_CODE__PLAN END "
	             	+ " FROM ITEM WHERE ITEM_CODE = ?";

					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,itemCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						itemCode = rs.getString(1);
					}
					rs.close();
					pstmt.close();

					int index = indexOfADPElement(siteCode, itemCode,"          ","   ");
		    		if (index != -1)
		    		{
	    				adpElement = (ADPElement)adpeList.get(index);
						if (saleOrder == null || saleOrder.trim().length() == 0 || !"M".equals(adpElement.getSuppSour()))
						{
		    				//adpElement = (ADPElement)adpeList.get(index);
	                        adpElement.setDemand(dueDate, quantity,"          ","   ");
	                        adpeList.set(index,adpElement);
						}
						else
						{
							ADPElement newADPElement = new ADPElement();
							newADPElement.setSiteCode(siteCode);
							newADPElement.setItemCode(itemCode);
							newADPElement = setAdpeValues(newADPElement);
							newADPElement.setSaleOrder(saleOrder);
							newADPElement.setLineNo(lineNo);
							newADPElement.setDemand(dueDate, quantity, saleOrder, lineNo);
							adpeList.add(newADPElement);

						}
	                             //System.out.println("Material Site " + siteCode + " Item " + itemCode + " Demand " + decFormat(quantity) +  "Due Date " + sdate );
		    		}
		    		else
		    		{
	                        System.out.println("No index found for Site Code : " + siteCode + " & Item Code : " + itemCode );
		    		}
                }
              }
              catch(Exception e)
              {
                     System.out.println("Exception : XML String : " + xmlString);
                     System.out.println("Exception :RunMRPPrcEJB : updateDemand(String).." +e.getMessage());
                     e.printStackTrace();
                     errString = e.getMessage();
					 throw new ITMException(e);
              }
	}

	private int indexOfADPElement(String siteCode, String itemCode, String saleOrder, String lineNo) throws ITMException, Exception
	{
             int retVal = -1, ctr = 0;
             ADPElement adpElement = null;
			 if (saleOrder == null || saleOrder.trim().length() == 0)
			 {
				saleOrder = "          ";
			 }
			 if (lineNo == null || lineNo.trim().length() == 0)
			 {
				lineNo = "   ";
			 }
			 System.out.println("adpeList.size()------"+adpeList.size());
             for (ctr = 0; ctr < adpeList.size(); ctr++)
             {
                     adpElement = (ADPElement)adpeList.get(ctr);
					 System.out.println("adpElement------"+adpElement.getSiteCode()+"----"+adpElement.getItemCode()+"----"+adpElement.getSaleOrder()+"----"+adpElement.getLineNo());
                     if ( (adpElement.getSiteCode().equals(siteCode)) && (adpElement.getItemCode().equals(itemCode))
                    		 && (adpElement.getSaleOrder().equals(saleOrder)) && (adpElement.getLineNo().equals(lineNo)))
                     {
                             retVal = ctr;
                             break;
                     }
             }
             return retVal;
	}

	private String populateInterSiteDistance() throws ITMException, Exception
	{
             String siteCode = "";
             String siteCodeFrom = "";
             double distance = 0;
             Statement stmt = null;
             ResultSet rs = null;
	    try
         	{
			stmt = conn.createStatement();
			String sql = "SELECT SITE_CODE, SITE_CODE__FROM, DISTANCE "
                                    + "FROM DIST_INTERSITE_OPTIONS  "
                                    + "ORDER BY SITE_CODE ASC,"
                                    + "DISTANCE ASC";
                   rs = stmt.executeQuery(sql);
                   while(rs.next())
                   {
                        InterSiteDistance interSite = new InterSiteDistance();
                        siteCode = rs.getString(1);
                        siteCodeFrom = rs.getString(2);
                        distance = rs.getDouble(3);

                        interSite.setSiteCode(siteCode);
                        interSite.setSiteCodeFrom(siteCodeFrom);
                        interSite.setDistance(distance);

                        interSiteList.add(interSite);
                    }
                    rs.close();
                    stmt.close();
               }
		catch(SQLException e)
		{
                  System.out.println("SQLException : populateInterSiteDistance() : " +e.getMessage());
                  this.errString = genericUtility.createErrorString(e);
                  e.printStackTrace();
				  throw new ITMException(e);
		}
		catch(Exception e)
		{
                  System.out.println("Exception :populateInterSiteDistance():" + e.getMessage() + ":");
                  this.errString = genericUtility.createErrorString(e);
                  e.printStackTrace();
				  throw new ITMException(e);
		}
		finally
		{
                  if (stmt != null) { stmt = null; }
                  if (rs != null) { rs = null; }
                  //Commented by Jiten as the block is not required -  Jiten 03/11/06
                  /*if( (errString != null) && (errString.trim().length() > 0) )
                  {
                          System.out.println("Error String :===> "+errString);
                          return errString;
                  }
                  else
                  {
                          return errString;
                  }*/
		}
		return errString;// Moved here as Finally block does not complete normally - jiten -03/11/06
	}

	private double getDistance(String siteCodeFrom,String siteCodeTo) throws ITMException, Exception
	{
		InterSiteDistance interSite = null;
		int interSiteEle = -1;
		for (int index = 0; index < interSiteList.size() ; index++)
		{
                  interSite = (InterSiteDistance)(interSiteList.get(index));

                  if((interSite.getSiteCodeFrom().trim().equals(siteCodeFrom.trim())) && (interSite.getSiteCode().trim().equals(siteCodeTo.trim())))
                  {
                    interSiteEle = index;
                    break;
                  }
                  else if((interSite.getSiteCodeFrom().trim().equals(siteCodeTo.trim())) && (interSite.getSiteCode().trim().equals(siteCodeFrom.trim())))
                  {
                      interSiteEle = index;
                      break;
                  }
		}
		if(interSiteEle == -1)
		{
                  System.out.println("Site Code From/To not found in intersite distance");
                  return -1;
		}
		else
		{
                     return interSite.getDistance();
		}
	}

	private void populateSurplusList() throws ITMException, Exception
	{
             ADPElement adpElement = null;
             SurplusStock surplusStock = null;
             double surplusQuantity = 0;
             String siteCode = "";
             String itemCode = "";
             for (int index = 0; index < adpeList.size() ; index ++)
             {
               adpElement = (ADPElement)adpeList.get(index);
               if(adpElement.getSupply() > adpElement.getMaxQty())
               {
                 surplusStock = new SurplusStock();
                 siteCode = adpElement.getSiteCode();
                 itemCode = adpElement.getItemCode();
                 surplusQuantity = adpElement.getSupply() - adpElement.getMaxQty();
                 surplusStock.setSiteCode(siteCode);
                 surplusStock.setItemCode(itemCode);
                 surplusStock.setSurplusQuantity(surplusQuantity);
                 surplusList.add(surplusStock);
                 surplusListOriginal.add(surplusStock);
               }
             }
	}

	private String getSurplusStockSite(String siteCode,String itemCode,double Qty,ArrayList surplusList) throws ITMException, Exception
	{
          String siteCodeFrom = null;
          SurplusStock surplusStock = null;
          InterSiteDistance intersiteDistance = null;
          String surplusSiteCode = null;
          for (int ctr = 0; ctr < interSiteList.size(); ctr++)
          {
            intersiteDistance = (InterSiteDistance)interSiteList.get(ctr);
            if (intersiteDistance.getSiteCode().trim().equals(siteCode.trim()))
            {
              siteCodeFrom = intersiteDistance.getSiteCodeFrom();
              for (int index = 0; index < surplusList.size() ; index++)
              {
                surplusStock = (SurplusStock)surplusList.get(index);
                if( (surplusStock.getSiteCode().trim().equals(siteCodeFrom.trim())) && (surplusStock.getItemCode().trim().equals(itemCode.trim())))
                {
                  if(Qty <= surplusStock.getSurplusQuantity())
                  {
                          surplusSiteCode = siteCodeFrom ;
                          break;
                  }
                }
              }
              if (surplusSiteCode != null)
              {
                      break;
              }
            }
          }
          return surplusSiteCode;
	}

	void cleanup()
	{
            itemCodeMap.clear();
            siteCodeMap.clear();
            itemHashMap.clear();
            adpeList.clear();
            interSiteList.clear();
            surplusList.clear();
            surplusListOriginal.clear();
            indList.clear();
            mpsList.clear();
            indDemList.clear();
            disList.clear();
         }
	public String getDateFromTimeStamp(java.sql.Timestamp timeStampDate, String targetFormat) throws ITMException, Exception
	{
             String retStrDate =  null;
             try
             {
               SimpleDateFormat simpleDateFormat = new SimpleDateFormat(targetFormat);
               retStrDate = simpleDateFormat.format(timeStampDate);
             }
             catch(Exception e)
             {
                     System.out.println("Exception :RunMRPPrcEJB :getDateFromTimeStamp(TimeStampDate) " + e);
					 throw new ITMException(e);
             }
             return retStrDate;
	}

	private String deleteOldPlan(String siteCode, String itemCode) throws ITMException, Exception
	{
             PreparedStatement pstmt = null;

             String sqlMpsOrder, sqlWorkOrder;
             String sqlIndent;
             String sqlDistDemand;
             int rowsDeleted = 0;
             try
             {
                 sqlIndent = "DELETE FROM INDENT "
                            + "WHERE SITE_CODE = ? "
                            + "AND ITEM_CODE IN ("
                            + "SELECT CASE WHEN ITEM_CODE__PLAN IS NULL THEN ITEM_CODE ELSE ITEM_CODE__PLAN END "
                            + "FROM ITEM "
                            + "WHERE ITEM_CODE = ? ) "
                            + "AND STATUS = 'U' "
                            + "AND IND_TYPE = 'M' ";

                   sqlMpsOrder = "DELETE FROM MPS_ORDER "
                                 + "WHERE SITE_CODE = ? "
                                 + "AND ITEM_CODE = ? ";

                   sqlWorkOrder = "DELETE FROM WORKORDER "
                                 + "WHERE SITE_CODE = ? "
                                 + "AND ITEM_CODE = ? "
								 + " AND STATUS in ('M','P')";
                   sqlDistDemand = "DELETE FROM DIST_DEMAND "
                                    + " WHERE STATUS = 'P' "
                                    + "AND SITE_CODE  = ? "
                                    + "AND ITEM_CODE = ? ";

                  if (matPlan.equals("Y"))
                  {
                     pstmt = conn.prepareStatement(sqlIndent);
                     pstmt.setString(1,siteCode);
                     pstmt.setString(2,itemCode);
                     rowsDeleted = pstmt. executeUpdate();
                     System.out.println("Rows Deleted in Indent :" + rowsDeleted);
                     conn.commit();
                     pstmt.close();
                  }

                  if ( mfgPlan.equals("Y"))
                  {
                     pstmt = conn.prepareStatement(sqlMpsOrder);
                     pstmt.setString(1,siteCode);
                     pstmt.setString(2,itemCode);
                     rowsDeleted = pstmt.executeUpdate();
                     System.out.println("Rows Deleted in MpsOrder :" + rowsDeleted);
                     conn.commit();
                     pstmt.close();
                     pstmt = conn.prepareStatement(sqlWorkOrder);
                     pstmt.setString(1,siteCode);
                     pstmt.setString(2,itemCode);
                     rowsDeleted = pstmt.executeUpdate();
                     System.out.println("Rows Deleted in WorkOrder :" + rowsDeleted);
                     conn.commit();
                     pstmt.close();
                  }

                  if (disPlan.equals("Y"))
                  {
                     pstmt = conn.prepareStatement(sqlDistDemand);
                     pstmt.setString(1,siteCode);
                     pstmt.setString(2,itemCode);
                     rowsDeleted = pstmt. executeUpdate();
                     System.out.println("Rows Deleted in DistDemand :" + rowsDeleted);
                     conn.commit();
                     pstmt.close();
                  }
             }
             catch(SQLException e)
             {
                     System.out.println("SQLException :RunMRPPrcEJB : deleteOldPlan().." +e.getMessage());
                     this.errString = genericUtility.createErrorString(e);
                     e.printStackTrace();
                     errString = e.getMessage();
             }
             catch(Exception e)
             {
                     System.out.println("Exception :RunMRPPrcEJB : deleteOldPlan() : " +e.getMessage());
                     this.errString = genericUtility.createErrorString(e);
                     e.printStackTrace();
                     errString = e.getMessage();
					 throw new ITMException(e);
             }
             finally
             {
                     if (pstmt != null){pstmt = null; }
                     //return errString;
             }
             return errString;//Moved here as Finally block does not complete normally - jiten -03/11/06
	}
	private String generatePlanDemand() throws ITMException, Exception
	{
		ADPElement adpElement = null;

		try
		{
			for (int adpeCtr = 0; adpeCtr < adpeList.size(); adpeCtr++)
			{
                           adpElement = (ADPElement)adpeList.get(adpeCtr);
						   System.out.println("01/04/11 SO adpeCtr [" + adpeCtr + "] SO [" + adpElement.getSaleOrder() + "]");
                           errString = generatePlanDemand(adpElement);
                           if ( errString != null && errString.trim().length() > 0 )
                           {
                                System.out.println("Error While generating Plan Demand for " + adpElement.getItemCode() + "..." + errString);
                                return errString;
                           }
			}
		}
		catch(Exception e)
		{
                     System.out.println("Exception :RunMRPPrcEJB :CreatePlanDemand ():" + e.getMessage() + ":");
                     e.printStackTrace();
                     errString = e.getMessage();
					 throw new ITMException(e);
		}
		      	return errString;
	}
	private String generatePlanDemand(ADPElement adpElement) throws ITMException, Exception
	{
		    PreparedStatement pstmtMpsOrder = null;
        	PreparedStatement pstmtIndent = null;
        	PreparedStatement pstmtDistDemand = null;
            double runningDemand = 0;
            double runningSupply = 0;
            double runningPlanSupply = 0;
             try
             {

                java.sql.Timestamp dueDate = null;
                java.sql.Timestamp curDate = null;
                java.sql.Timestamp chgDate = new java.sql.Timestamp(System.currentTimeMillis());
                double reqQty = 0;
                double balQty = 0;
                double createQty = 0;
				double independentDemand = 0;

				double demand = adpElement.getDemand();
				double supply = adpElement.getSupply();
				String itemCode = adpElement.getItemCode();
				String siteCode = adpElement.getSiteCode();
				String suppSour = adpElement.getSuppSour();
				String bomCode = adpElement.getBomCode();
				double  minQty = adpElement.getMinQty();
				int minStkDay = adpElement.getMinStkDay();
				double reoQty = adpElement.getReoQty();
				int purLeadTime = adpElement.getPurLeadTime();
				int qcLeadTime = adpElement.getQcLeadTime();
				double integralQty = adpElement.getIntegralQty();
				String itemStru = adpElement.getItemStru();
				String siteCodeSupp = adpElement.getSiteCodeSupp();
				String siteCodePlan = adpElement.getSiteCodePlan();
				String batchQtyType = adpElement.getBatchQtyType();
				double minOrderQty = adpElement.getMinOrderQty();
				int maxStkDay = adpElement.getMaxStkDay();
				double maxQty = adpElement.getMaxQty();
				String orderOpt = adpElement.getOrderOpt();
				double yieldPerc = adpElement.getYieldPerc();
				String masterSch = adpElement.getMasterSch();
				int mfgLeadTime = adpElement.getMfgLeadTime();
				double minPlanPerc = adpElement.getMinPlanPerc();
				double batchQty = adpElement.getBatchQty();
				String unit = adpElement.getUnit();
				String itemDescr = adpElement.getItemDescr();
				String mfgType = adpElement.getMfgType();
				ArrayList timeMRParr = adpElement.getTimeMrpList();

				curDate = new java.sql.Timestamp(System.currentTimeMillis());
				SimpleDateFormat sdt = new SimpleDateFormat("yyyy-MM-dd");
				curDate = java.sql.Timestamp.valueOf(sdt.format(curDate) + " 00:00:00.000");
				java.sql.Timestamp horizonDate = null;

				int multiplier = 0;

				String errString = "";
				// check stk_opt
				if (getStkOpt(itemCode,siteCode).equals("0"))
                {
                   return "";
                }
               if (demand - supply <= 0)
               {
                   return "";
               }
               for (int timeCtr = 0; timeCtr < timeMRParr.size() ; timeCtr++)
               {
                    TimeMRP timeMrp = (TimeMRP)timeMRParr.get(timeCtr);
                    runningDemand = timeMrp.getRunningDemand();
                    runningSupply = timeMrp.getRunningSupply();
                    runningPlanSupply = timeMrp.getRunningPlanSupply();
                    independentDemand = timeMrp.getIndependentDemand();
                    dueDate = timeMrp.getDueDate();
                     if (MrpClubbingHorizon > 0)
                     {
                           Calendar calen = Calendar.getInstance();
                           calen.setTime(dueDate);
                           calen.add(Calendar.DAY_OF_MONTH, MrpClubbingHorizon);
                           horizonDate = new java.sql.Timestamp(System.currentTimeMillis());
                           horizonDate.setTime( ((java.util.Date)calen.getTime()).getTime());
                     }
					 else // 02/05/11 manoharan
					 {
						horizonDate = dueDate;
					 }
                   reqQty = timeMrp.getPlanSupply(); //runningDemand - runningSupply - runningPlanSupply;

                   if (reqQty > 0)
                   {
                   	//System.out.println("Plan  Supply :" + reqQty + " Orderopt " + orderOpt + " Suppsour " + suppSour + " Mfg Type " + mfgType + " Made To Order " + madeToOrder + "Mfg Plan " + mfgPlan );

                        if (dueDate.compareTo(curDate) < 0)
                        {
                           dueDate = curDate;
                        }

                         //if (suppSour.equals("D") &&  disPlan.equals("Y") && siteCodeSupp != null && siteCodeSupp.trim().length() > 0 )
                         if (independentDemand > 0 )
                         {
                           // independent demand to be created
                           if (orderOpt.equals("B"))
                           {
                                if (independentDemand > batchQty)
                                {
                                     if (independentDemand % batchQty  > 0)
                                     {
                                             //multiplier = ((int)Math.floor(reqQty / batchQty)) + 1;
                                             multiplier =  new Double(Math.floor(independentDemand / batchQty)).intValue() + 1;
                                     }
                                     else
                                     {
                                           // multiplier = (int)Math.floor(reqQty / batchQty);
                                           multiplier =  new Double(Math.floor(independentDemand / batchQty)).intValue();
                                     }
                                 }
                                else
                                {
                                        multiplier = 1;
                                }

                                createQty = batchQty;
                                for (int planCtr = 0; planCtr < multiplier; planCtr++)
                                {
                                       IndependentDemand indDem = new IndependentDemand();
                                       indDem.tranDate = curDate;
                                       indDem.siteCode = siteCode;
                                       indDem.itemCode = itemCode;
                                       indDem.planDueDate = dueDate;
                                       indDem.dueDate = dueDate;
                                       indDem.quantity = createQty;
                                       indDem.quantityStdUom = createQty;
                                       indDem.convQtyStdUom = 1;
                                       indDem.unit = unit;
                                       indDem.unitStd = unit;
                                       indDem.status = "O";
                                       indDem.siteCodeCust = adpElement.getSiteCodeCust();

                                       indDemList.add(indDem);
                                }
                           }
                           else if ( orderOpt.equals("C") )
                           {
                                int findEle = -1;
                                IndependentDemand indDem = null;
                                if ( indDemList.size() > 0)
                                {
                                       // check for the last ones existence if found add the demand to that
                                       for ( int listCtr = indDemList.size() -1; listCtr > 0; listCtr-- )
                                       {
                                          indDem = (IndependentDemand)indDemList.get(listCtr);
                                          if (indDem.itemCode.trim() == itemCode.trim() && indDem.siteCode.trim() == siteCode.trim() && indDem.siteCodeCust.trim() == adpElement.getSiteCodeCust().trim() && (dueDate.compareTo(horizonDate) <= 0))
                                          {
                                              findEle = listCtr;
                                              break;
                                          }
                                       }
                                }
                                if (findEle >= 0)
                                {
                                    indDem.quantity += independentDemand;
                                    indDem.quantityStdUom += independentDemand;
                                    indDemList.set(findEle, indDem);
                                }
                                else
                                {
                                    IndependentDemand newIndDem = new IndependentDemand();
                                    newIndDem.tranDate = curDate;
                                    newIndDem.siteCode = siteCode;
                                    newIndDem.itemCode = itemCode;
                                    newIndDem.planDueDate = dueDate;
                                    newIndDem.dueDate = dueDate;
                                    newIndDem.quantity = independentDemand;
                                    newIndDem.quantityStdUom = independentDemand;
                                    newIndDem.convQtyStdUom = 1;
                                    newIndDem.unit = unit;
                                    newIndDem.unitStd = unit;
                                    newIndDem.status = "O";
                                    newIndDem.siteCodeCust = adpElement.getSiteCodeCust();

                                    indDemList.add(newIndDem);
                                }
                           }

                        } // (suppSour.equals("D") &&  disPlan.equals("Y") && siteCodeSupp != null && siteCodeSupp.trim().length() > 0 )

                        //else if (( suppSour.equals("P") || suppSour.equals("C") )&&  matPlan.equals("Y") )
                        if (( suppSour.equals("P") || suppSour.equals("C") )&&  matPlan.equals("Y") )
                        {
                        	//System.out.println("Indent creation Plan  Supply :" + reqQty + " Orderopt " + orderOpt + " Suppsour " + suppSour + " Mat Plan " + matPlan );
                           // independent demand to be created
                           if (orderOpt.equals("B"))
                           {
                                if (reqQty > batchQty)
                                {
                                     if (reqQty % batchQty  > 0)
                                     {
                                             //multiplier = ((int)Math.floor(reqQty / batchQty)) + 1;
                                             multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() + 1;
                                     }
                                     else
                                     {
                                            //multiplier = (int)Math.floor(reqQty / batchQty);
                                            multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
                                     }
                                 }
                                else
                                {
                                        multiplier = 1;
                                }

                                createQty = batchQty;
                                for (int planCtr = 0; planCtr < multiplier; planCtr++)
                                {
                                    Indent indent = new Indent();
                                    indent.indDate = curDate;
                                    indent.deptCode = deptCodeMfg;
                                    indent.planDueDate = dueDate;
                                    indent.reqDate = dueDate;
                                    indent.itemCode = itemCode;
                                    indent.itemDescr = itemDescr;
                                    indent.quantity = batchQty;
                                    indent.quantityStdUom = batchQty;
                                    indent.unitInd = unit;
                                    indent.unitStd = unit;
                                    indent.siteCode = siteCode;
                                    indent.siteCodeDlv = siteCode;
                                    indent.siteCodeAcct = siteCode;
                                    indent.aprCode = adpElement.getAprCode();
                                    indent.acctCode = adpElement.getAcctCodePh();
                                    indent.cctrCode = adpElement.getCctrCodePh();
                                    indent.empCodePur = adpElement.getEmpCodePur();
                                    indent.empCodeReq = adpElement.getEmpCodePur();
                                    indent.suppCodePref = adpElement.getSuppCodePref();
                                    indent.purcRate = adpElement.getPurcRate(); ;
                                    indent.packCode = adpElement.getPackCode();
                                    indent.chgDate = chgDate;
                                    indent.chgUser = userId;
                                    indent.chgTerm = userId;
                                    indent.purLeadTime = purLeadTime;
                                    indent.convQtyStdUom = 1;

                                    indList.add(indent);
                                }
                           }
                           else if ( orderOpt.equals("C") )
                           {
                                int findEle = -1;
                                Indent indent = null;
                                if ( indList.size() > 0)
                                {
                                       // check for the last ones existence if found add the demand to that
                                       for ( int listCtr = indList.size() -1; listCtr > 0; listCtr-- )
                                       {
                                          indent = (Indent)indList.get(listCtr);
                                          if (indent.itemCode.trim() == itemCode.trim() && indent.siteCode.trim() == siteCode.trim() && (dueDate.compareTo(horizonDate) <= 0))
                                          {
                                              findEle = listCtr;
                                              break;
                                          }
                                       }
                                }
                                if (findEle >= 0)
                                {
                                    indent.quantity += reqQty;
                                    indent.quantityStdUom += reqQty;
                                    indList.set(findEle, indent);
                                }
                                else
                                {
                                    Indent newIndent = new Indent();
                                    newIndent.indDate = curDate;
                                    newIndent.deptCode = deptCodeMfg;
                                    newIndent.planDueDate = dueDate;
                                    newIndent.reqDate = dueDate;
                                    newIndent.itemCode = itemCode;
                                    newIndent.itemDescr = itemDescr;
                                    newIndent.quantity = reqQty;
                                    newIndent.quantityStdUom = reqQty;
                                    newIndent.unitInd = unit;
                                    newIndent.unitStd = unit;
                                    newIndent.siteCode = siteCode;
                                    newIndent.siteCodeDlv = siteCode;
                                    newIndent.siteCodeAcct = siteCode;
                                    newIndent.aprCode = adpElement.getAprCode();
                                    newIndent.acctCode = adpElement.getAcctCodePh();
                                    newIndent.cctrCode = adpElement.getCctrCodePh();
                                    newIndent.empCodePur = adpElement.getEmpCodePur();
                                    newIndent.empCodeReq = adpElement.getEmpCodePur();
                                    newIndent.suppCodePref = adpElement.getSuppCodePref();
                                    newIndent.purcRate = adpElement.getPurcRate(); ;
                                    newIndent.packCode = adpElement.getPackCode();
                                    newIndent.chgDate = chgDate;
                                    newIndent.chgUser = userId;
                                    newIndent.chgTerm = userId;
                                    newIndent.purLeadTime = purLeadTime;
                                    newIndent.convQtyStdUom = 1;

                                    //System.out.println("Indent creation for :" + itemCode  + " " + decFormat(reqQty) );

                                    indList.add(newIndent);
                                }
                           }
                        } // (( suppSour.equals("P") || suppSour.equals("C") )&&  matPlan.equals("Y") )
                        else if ( suppSour.equals("M") && mfgPlan.equals("Y"))
                        {
                           // IF ITEM IS MADE TO ORDER AND IF MADE TO STOCK OPTION IS SELECTED THEN
                           // SKIP CREATION OF WORKORDER......AT GNL 10/4/02 BY MANOJ AS PER KANDARP
                           if (madeToOrder.equals("1") && mfgType != null && mfgType.trim().equals("O"))
                           {
                               return "";
                           }
//                         batchQtyType != v added as per Manoharan Sir - Jiten - 02/11/06
                           if (orderOpt.equals("B") && !(batchQtyType.equalsIgnoreCase("V")))
                           {
                                System.out.println("11012008 Req Qty : " + reqQty + " batch Qty :" + batchQty + " Min Plan :" + minPlanPerc);
                                if (reqQty > batchQty)
                                {
                                     if ( reqQty % batchQty > 0)
                                     {
                                        if ( (reqQty % batchQty) >= (batchQty * minPlanPerc / 100 ))
                                        {
                                                //multiplier = ((int)Math.floor(reqQty / batchQty)) + 1;
                                                multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() + 1;
                                        }
                                        else
                                        {
                                               //multiplier = (int)Math.floor(reqQty / batchQty);
                                               multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
                                        }
                                     }
                                     else
                                     {
                                        //multiplier = (int)Math.floor(reqQty / batchQty);
                                        multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
                                     }
                                 }
                                else
                                {
                                   if (reqQty >= (batchQty * minPlanPerc / 100 ))
                                   {
                                        multiplier = 1;
                                   }
                                }

                                createQty = batchQty;
						        if(itemCode.trim().equalsIgnoreCase("BD0783"))
						        {
                                	System.out.println("11012008 Mulitplier : " + multiplier + " Batch Qty " + decFormat(batchQty) + " Req Qty : " + decFormat(reqQty) + " Item : " + itemCode + " Site " + siteCode);
						        }
                                for (int planCtr = 0; planCtr < multiplier; planCtr++)
                                {
                                    MPSOrder mpsOrder = new MPSOrder();

                                    mpsOrder.ordType = adpElement.getMfgType();
                                    mpsOrder.itemCode = itemCode;
                                    mpsOrder.siteCode = siteCode;
                                    mpsOrder.quantity = batchQty;
                                    mpsOrder.ordDate = curDate;
                                    mpsOrder.planDueDate = dueDate;
                                    //ld_worder.setitem(ll_row, "MRP_RUN_ID", ls_org_tran_id)
                                    //ld_worder.setitem(ll_row, "ADDN_ORDER", trim(ls_addntranid))
                                    mpsOrder.dueDate = dueDate;
                                    mpsOrder.status = "P";
                                    mpsOrder.statusDate = curDate;
                                    mpsOrder.yieldPerc = yieldPerc;
                                    mpsOrder.routeCode = adpElement.getRouteCode();
                                    mpsOrder.bomCode = adpElement.getBomCode();
                                    //ld_worder.setitem(ll_row, "proj_code", ls_proj_code)
                                    mpsOrder.chgDate = chgDate;
                                    mpsOrder.chgUser = userId;
                                    mpsOrder.chgTerm = userId;
                                    mpsOrder.unit = unit;
                                    mpsOrder.acctCodeWp = adpElement.getAcctCodeWp();
                                    mpsOrder.cctrCodeWp = adpElement.getCctrCodeWp();
                                    mpsOrder.curBatchQty = batchQty;
                                    //ld_worder.setitem(ll_row, "std_qty", lc_create_qty) // 04-07-03
                                    //ld_worder.setitem(ll_row, "demand_qty", lc_actualdemand)
									// 01/04/11 manoharan
									System.out.println("01/04/11 setting SO in MPS A [" + adpElement.getSaleOrder() + "]");
									mpsOrder.saleOrder = adpElement.getSaleOrder();
									mpsOrder.lineNo = adpElement.getLineNo();
									mpsOrder.mfgType = adpElement.getMfgType();
									// end 01/04/11 manoharan

                                    mpsList.add(mpsOrder);
                                }
                           }
                           //Condition to be added as per Manoharan Sir - Jiten 02/11/06
                           //Else if condition added by Jiten as per Manoharan Sir - 03/11/06
                           else if(orderOpt.equals("B") && batchQtyType.equalsIgnoreCase("V"))
                           {
                           		if (reqQty >= batchQty)
	                            {
                           			//Below block is commented as per Manoharan Sir - Jiten - 03/11/06
	                                 /*if ( reqQty % batchQty > 0)
	                                 {
	                                    if ( (reqQty % batchQty) >= (batchQty * minPlanPerc / 100 ))
	                                    {
	                                            //multiplier = ((int)Math.floor(reqQty / batchQty)) + 1;
	                                            multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() + 1;
	                                    }
	                                    else
	                                    {
	                                           //multiplier = (int)Math.floor(reqQty / batchQty);
	                                           multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
	                                    }
	                                 }*/
	                                 //else
	                                 //{
	                                    //multiplier = (int)Math.floor(reqQty / batchQty);
	                                    multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
	                                    balQty = reqQty - (multiplier * batchQty);
	                                 //}
	                                 //End Comment
	                            }
	                            else
	                            {
	                               //if (reqQty >= (batchQty * minPlanPerc / 100 ))
	                               //{
	                               //     multiplier = 1;
	                               //}
	                               balQty = reqQty;
	                               multiplier = 0;
	                            }
                           		createQty = batchQty;
						        if(itemCode.trim().equalsIgnoreCase("BD0783"))
						        {
		                            System.out.println("11012008 Mulitplier : " + multiplier + " Batch Qty " + decFormat(batchQty) + " Req Qty : " + decFormat(reqQty) + " Item : " + itemCode + " Site " + siteCode);
						        }
	                            for (int planCtr = 0; planCtr < multiplier; planCtr++)
	                            {
	                                MPSOrder mpsOrder = new MPSOrder();

	                                mpsOrder.ordType = adpElement.getMfgType();
	                                mpsOrder.itemCode = itemCode;
	                                mpsOrder.siteCode = siteCode;
	                                mpsOrder.quantity = batchQty;
	                                mpsOrder.ordDate = curDate;
	                                mpsOrder.planDueDate = dueDate;
	                                //ld_worder.setitem(ll_row, "MRP_RUN_ID", ls_org_tran_id)
	                                //ld_worder.setitem(ll_row, "ADDN_ORDER", trim(ls_addntranid))
	                                mpsOrder.dueDate = dueDate;
	                                mpsOrder.status = "P";
	                                mpsOrder.statusDate = curDate;
	                                mpsOrder.yieldPerc = yieldPerc;
	                                mpsOrder.routeCode = adpElement.getRouteCode();
	                                mpsOrder.bomCode = adpElement.getBomCode();
	                                //ld_worder.setitem(ll_row, "proj_code", ls_proj_code)
	                                mpsOrder.chgDate = chgDate;
	                                mpsOrder.chgUser = userId;
	                                mpsOrder.chgTerm = userId;
	                                mpsOrder.unit = unit;
	                                mpsOrder.acctCodeWp = adpElement.getAcctCodeWp();
	                                mpsOrder.cctrCodeWp = adpElement.getCctrCodeWp();
	                                mpsOrder.curBatchQty = batchQty;
	                                //ld_worder.setitem(ll_row, "std_qty", lc_create_qty) // 04-07-03
	                                //ld_worder.setitem(ll_row, "demand_qty", lc_actualdemand)
									// 01/04/11 manoharan
									System.out.println("01/04/11 setting SO in MPS 0 [" + adpElement.getSaleOrder() + "]");
									mpsOrder.saleOrder = adpElement.getSaleOrder();
									mpsOrder.lineNo = adpElement.getLineNo();
									mpsOrder.mfgType = adpElement.getMfgType();
									// end 01/04/11 manoharan

	                                mpsList.add(mpsOrder);
	                            }
	                            //below block added as per Manoharan Sir - jiten 03/11/06 - For remaining reqQty
	                            //reqQty = reqQty - (batchQty * multiplier);
	                            //if(reqQty > 0){
	                            if(balQty > 0){
	                            	MPSOrder mpsOrder = new MPSOrder();

	                                mpsOrder.ordType = adpElement.getMfgType();
	                                mpsOrder.itemCode = itemCode;
	                                mpsOrder.siteCode = siteCode;
	                                mpsOrder.quantity = balQty;
	                                mpsOrder.ordDate = curDate;
	                                mpsOrder.planDueDate = dueDate;
	                                mpsOrder.dueDate = dueDate;
	                                mpsOrder.status = "P";
	                                mpsOrder.statusDate = curDate;
	                                mpsOrder.yieldPerc = yieldPerc;
	                                mpsOrder.routeCode = adpElement.getRouteCode();
	                                mpsOrder.bomCode = adpElement.getBomCode();
	                                mpsOrder.chgDate = chgDate;
	                                mpsOrder.chgUser = userId;
	                                mpsOrder.chgTerm = userId;
	                                mpsOrder.unit = unit;
	                                mpsOrder.acctCodeWp = adpElement.getAcctCodeWp();
	                                mpsOrder.cctrCodeWp = adpElement.getCctrCodeWp();
	                                mpsOrder.curBatchQty = balQty;
									// 01/04/11 manoharan
									System.out.println("01/04/11 setting SO in MPS 1 [" + adpElement.getSaleOrder() + "]");
									mpsOrder.saleOrder = adpElement.getSaleOrder();
									mpsOrder.lineNo = adpElement.getLineNo();
									mpsOrder.mfgType = adpElement.getMfgType();
									// end 01/04/11 manoharan

	                                mpsList.add(mpsOrder);
	                            }
	                            //Jiten - End 03/11/06
                           }
                           else if ( orderOpt.equals("C") )
                           {
                                int findEle = -1;
								MPSOrder mpsOrder = null;
								if (!"O".equals(adpElement.getMfgType())) // 01/04/11 manoharan in case made to order not to club
								{
									if ( mpsList.size() > 0)
									{
										   // check for the last ones existence if found add the demand to that
										   for ( int listCtr = mpsList.size() -1; listCtr > 0; listCtr-- )
										   {
											  mpsOrder = (MPSOrder)mpsList.get(listCtr);

											  if (mpsOrder.itemCode.trim() == itemCode.trim() && mpsOrder.siteCode.trim() == siteCode.trim() && (dueDate.compareTo(horizonDate) <= 0))
											  {
												  findEle = listCtr;
												  break;
											  }
										   }
									}
								}
                                if (findEle >= 0)
                                {
                                    mpsOrder.quantity += reqQty;
                                    mpsOrder.curBatchQty += reqQty;
                                    mpsList.set(findEle, mpsOrder);
                                }
                                else
                                {
                                    mpsOrder = new MPSOrder();

                                    mpsOrder.ordType = adpElement.getMfgType();
                                    mpsOrder.itemCode = itemCode;
                                    mpsOrder.siteCode = siteCode;
                                    mpsOrder.quantity = reqQty;
                                    mpsOrder.ordDate = curDate;
                                    mpsOrder.planDueDate = dueDate;
                                    mpsOrder.dueDate = dueDate;
                                    mpsOrder.status = "P";
                                    mpsOrder.statusDate = curDate;
                                    mpsOrder.yieldPerc = yieldPerc;
                                    mpsOrder.routeCode = adpElement.getRouteCode();
                                    mpsOrder.bomCode = adpElement.getBomCode();
                                    mpsOrder.chgDate = chgDate;
                                    mpsOrder.chgUser = userId;
                                    mpsOrder.chgTerm = userId;
                                    mpsOrder.unit = unit;
                                    mpsOrder.acctCodeWp = adpElement.getAcctCodeWp();
                                    mpsOrder.cctrCodeWp = adpElement.getCctrCodeWp();
                                    mpsOrder.curBatchQty = reqQty;
									// 01/04/11 manoharan
									System.out.println("01/04/11 setting SO in MPS 2 [" + adpElement.getSaleOrder() + "]");
									mpsOrder.saleOrder = adpElement.getSaleOrder();
									mpsOrder.lineNo = adpElement.getLineNo();
									mpsOrder.mfgType = adpElement.getMfgType();
									// end 01/04/11 manoharan

                                    mpsList.add(mpsOrder);
                                }
                           }

                        } //( suppSour.equals("M") && mfgPlan.equals("Y"))
                        else if ( suppSour.equals("D") && disPlan.equals("Y"))
                        {

							siteCodeSupp = " ";
							if (balIntersite.equals("Y"))
				            {
								String siteIdst = null;
                                siteIdst = getSurplusStockSite(siteCode,itemCode,reqQty,surplusListOriginal);
								// = wf_get_idst_site(ld_dist_intersite,ld_surplus_ext,ls_site_code,ls_item_code,lc_reo_qty)
								if (siteIdst != null && siteIdst.trim().length() > 0)
                                {
         							siteCodeSupp = siteIdst;
                                }
	                        }
							//
							if (siteCodeSupp == null || siteCodeSupp.trim().length() == 0)
	                        {
	                           siteCodeSupp = adpElement.getSiteCodeSupp();
	                        }

	                        DistDemand distDemand = new DistDemand();

	                        distDemand.tranDate = curDate;
	                        distDemand.itemCode = itemCode;
	                        distDemand.siteCode = siteCode;
	                        distDemand.siteCodeSrc = siteCodeSupp;
	                        distDemand.planDueDate = dueDate;
	                        distDemand.dueDate = dueDate;
	                        distDemand.qtyRequired = reqQty;
	                        distDemand.unit = unit;
	                        distDemand.status = "P";
	                        distDemand.dueDateOrd = dueDate;
	                        distDemand.chgDate = chgDate;
	                        distDemand.chgUser = userId;
	                        distDemand.chgTerm = userId;

	                        disList.add(distDemand);
                        	/*
	                         if (siteCodeSupp != null && siteCodeSupp.trim().length() > 0 )
	                         {
	                           // independent demand to be created
	                           if (orderOpt.equals("B"))
	                           {
	                                if (reqQty > batchQty)
	                                {
	                                     if (reqQty % batchQty  > 0)
	                                     {
	                                             //multiplier = ((int)Math.floor(reqQty / batchQty)) + 1;
	                                             multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue() + 1;
	                                     }
	                                     else
	                                     {
	                                           // multiplier = (int)Math.floor(reqQty / batchQty);
	                                           multiplier =  new Double(Math.floor(reqQty / batchQty)).intValue();
	                                     }
	                                 }
	                                else
	                                {
	                                        multiplier = 1;
	                                }

	                                createQty = batchQty;
	                                for (int planCtr = 0; planCtr < multiplier; planCtr++)
	                                {
	                                       IndependentDemand indDem = new IndependentDemand();
	                                       indDem.tranDate = curDate;
	                                       indDem.siteCode = siteCodeSupp;
	                                       indDem.itemCode = itemCode;
	                                       indDem.planDueDate = dueDate;
	                                       indDem.dueDate = dueDate;
	                                       indDem.quantity = createQty;
	                                       indDem.quantityStdUom = createQty;
	                                       indDem.convQtyStdUom = 1;
	                                       indDem.unit = unit;
	                                       indDem.unitStd = unit;
	                                       indDem.status = "O";
	                                       indDem.siteCodeCust = siteCode;

	                                       indDemList.add(indDem);
	                                }
	                           }
	                           else if ( orderOpt.equals("C") )
	                           {
	                                int findEle = -1;
	                                IndependentDemand indDem = null;
	                                if ( indDemList.size() > 0)
	                                {
	                                       // check for the last ones existence if found add the demand to that
	                                       for ( int listCtr = indDemList.size() -1; listCtr > 0; listCtr-- )
	                                       {
	                                          indDem = (IndependentDemand)indDemList.get(listCtr);
	                                          if (indDem.itemCode.trim() == itemCode.trim() && indDem.siteCode.trim() == siteCodeSupp.trim() && indDem.siteCodeCust.trim() == siteCode.trim() && (dueDate.compareTo(horizonDate) <= 0))
	                                          {
	                                              findEle = listCtr;
	                                              break;
	                                          }
	                                       }
	                                }
	                                if (findEle >= 0)
	                                {
	                                    indDem.quantity += reqQty;
	                                    indDem.quantityStdUom += reqQty;
	                                    indDemList.set(findEle, indDem);
	                                }
	                                else
	                                {
	                                    IndependentDemand newIndDem = new IndependentDemand();
	                                    newIndDem.tranDate = curDate;
	                                    newIndDem.siteCode = siteCodeSupp;
	                                    newIndDem.itemCode = itemCode;
	                                    newIndDem.planDueDate = dueDate;
	                                    newIndDem.dueDate = dueDate;
	                                    newIndDem.quantity = reqQty;
	                                    newIndDem.quantityStdUom = reqQty;
	                                    newIndDem.convQtyStdUom = 1;
	                                    newIndDem.unit = unit;
	                                    newIndDem.unitStd = unit;
	                                    newIndDem.status = "O";
	                                    newIndDem.siteCodeCust = siteCode;

	                                    indDemList.add(newIndDem);
	                                }
	                           }

	                        }*/
                        } //( suppSour.equals("D") && disPlan.equals("Y"))

                  } // reqQty > 0
                } // end for
             } // End of try
             catch(Exception e)
             {
                     System.out.println("Exception :RunMRPPrcEJB : CreatePlanDemand(ADPElement adpElement) : " +e.getMessage());
                     this.errString = genericUtility.createErrorString(e);
                     e.printStackTrace();
                     errString = e.getMessage();
					 throw new ITMException(e);
             }
             finally
             {
                     if (pstmtMpsOrder != null)
                     {
                             pstmtMpsOrder = null;
                     }
                     if (pstmtIndent != null)
                     {
                             pstmtIndent = null;
                     }
                     if (pstmtDistDemand != null)
                     {
                             pstmtDistDemand = null;
                     }
	                //System.out.println("Size of Indent List : [" + indList.size() + "]");
	                //System.out.println("Size of IndDem List : [" + indDemList.size() + "]");
	                //System.out.println("Size of Mps    List : [" + mpsList.size() + "]");
	                //System.out.println("Size of Dis    List : [" + disList.size() + "]");

             }
             return errString;//Moved here from finally as it does not complete normally - jiten - 03/11/06

        }
         private String InsertPlanDemand() throws Exception
         {
         	String errString = "";
         	//System.out.println("In Insert Plan Demand()");

            if (indDemList.size() > 0)
            {
            	//System.out.println("Ind Dem List Size :" + indDemList.size());
               errString = InsertPlanDemand("W_INDEPENDENT_DEMAND", indDemList);
               if ( errString != null && errString.trim().length() > 0 )
               {
                    System.out.println("Error While Inserting independent Demand" + errString);
                    return errString;
               }
            }
            if (indList.size() > 0 && genIndent.equalsIgnoreCase("Y"))
            {
            	//System.out.println("Ind List Size :" + mpsList.size());
               errString = InsertPlanDemand("W_INDENT", indList);
               if ( errString != null && errString.trim().length() > 0 )
               {
                    System.out.println("Error While Inserting independent Demand" + errString);
                    return errString;
               }
            }
            if (mpsList.size() > 0)
            {
								errString = InsertPlanDemand("W_MPS_ORDER", mpsList);
               if ( errString != null && errString.trim().length() > 0 )
               {
                    System.out.println("Error While Inserting mps order" + errString);
                    return errString;
               }
            }
            if (disList.size() > 0)
            {
            	//System.out.println("Dis List Size :" + mpsList.size());
				errString = InsertPlanDemand("W_DIST_DEMAND", disList);
               if ( errString != null && errString.trim().length() > 0 )
               {
                    System.out.println("Error While Inserting Dist. Demand" + errString);
                    return errString;
               }
            }
            return "";
         }

        private String getStkOpt(String itemCode,String siteCode) throws Exception
        {
 		PreparedStatement pstmt = null;
		String sql = "";
		ResultSet rs = null;
		String stkOpt = "";
		try
		{

			sql = "SELECT STK_OPT FROM SITEITEM WHERE SITE_CODE = ? AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				stkOpt = rs.getString(1);
			}
			else
			{
				rs.close();
				pstmt.close();
				sql = "SELECT STK_OPT FROM ITEM WHERE ITEM_CODE = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					stkOpt = rs.getString(1);
				}
			}
			rs.close();
			pstmt.close();
		}
		catch(SQLException e)
		{
			System.out.println("SQLException :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}

		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :getBomCode(String siteCode, String ItemCode).." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmt != null) { pstmt = null; }
			if (rs != null) { rs = null; }
			//return stkOpt;
         }
		return stkOpt;  // Moved here from finally block - jiten -03/11/06
    }



	private String decFormat(double num)
	{
		NumberFormat nf = new DecimalFormat("##############0.000");
		nf.setMaximumFractionDigits(3);
		//nf.setMaximumIntegerDigits(10);
		return nf.format(num);
	}




	private String InsertPlanDemand(String winName, java.util.ArrayList list) throws ITMException, Exception
	{
		PreparedStatement pstmtMpsOrder = null;
		PreparedStatement pstmtIndent = null;
		PreparedStatement pstmtDistDemand = null;
		PreparedStatement pstmtIndDemand = null;
		PreparedStatement pstmtKeyStr = null;
		PreparedStatement pstmtWorkOrder = null;
		ResultSet rs = null;

		String xmlValues = "";
		String keyStringQuery = null;

		String tranId = "";
		String ordType = "";
		String itemCode = "";
		String itemSer = "";
		String siteCode = "";
		String mrpRunId = "";
		String addnOrder = "";
		String routeCode = "";
		String bomCode = "";
		String projCode = "";
		String unit = "";
		String acctCodeWp = "";
		String cctrCodeWp = "";
		String indNo = "";
		final String indType = "M";
		String itemDescr= "";
		String unitInd = "";
		String unitStd = "";
		String siteCodeDlv = "";
		String siteCodeAcct = "";
		String siteCodeCust = "";
		String aprCode = "";
		final String priority = "R";
		String acctCode = "";
		String cctrCode = "";
		String empCodePur = "";
		String empCodeReq = "";
		String suppCodePref = "";
		String packCode = "";
		String siteCodeSrc = "";
		final String status = "P";

		double quantity = 0;
		double yieldPerc = 0 ;
		double purLeadTime = 0 ;
		double ordQuantity = 0;
		double purcRate = 0;
		double qtyRequired = 0;
		double quantityStdUom = 0;
		double convQtyStdUom = 1;

		String ordDateStr = "";
		String planDueDateStr = "";
		String dueDateStr = "";
		String statusDateStr = "";
		String chgDateStr = "";
		String indDateStr = "";
		String reqDateStr = "";
		String tranDateStr = "";
		String dueDateOrdStr = "";

		java.sql.Timestamp ordDate = null;
		java.sql.Timestamp planDueDate = null;
		java.sql.Timestamp dueDate = null;
		java.sql.Timestamp statusDate = null;
		java.sql.Timestamp chgDate = null;
		java.sql.Timestamp indDate = null;
		java.sql.Timestamp reqDate = null;
		java.sql.Timestamp tranDate = null;
		java.sql.Timestamp dueDateOrd = null;

		MPSOrder mpsOrder = null;
		Indent indent = null;
		DistDemand distDemand = null;
		IndependentDemand independentDemand = null;

		String mpsKeyStr = null;
		String indKeyStr = null;
		String indDemKeyStr = null;
		String disDemKeyStr = null;

		String tranIdIndDemand = null;
		String tranIdDistDemand = null;
		String tranIdIndent = null;
		String tranIdMpsOrder = null;
		//variables added by sabyasachi 30-03-11
		String saleOrder = "";
		String lineNo = "";
		String woKeyStr = null;
		String workOrder = null;
		PreparedStatement pstmtworkOrder = null;
		String sqlWorkOrder =  null;
		//variables added by sabyasachi ends 30-03-11

		try
		{

			/*keyStringQuery = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pstmtKeyStr = conn.prepareStatement(keyStringQuery);
			pstmtKeyStr.setString(1,"w_mps_order");
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				mpsKeyStr = rs.getString("KEY_STRING");
				rs.close();
			}
			*/
			keyStringQuery = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = 'w_mps_order' ";
			pstmtKeyStr = conn.prepareStatement(keyStringQuery);
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				mpsKeyStr = rs.getString("KEY_STRING");
				rs.close();
			}
			pstmtKeyStr.close(); // temporary
			//pstmtKeyStr.clearParameters();
			keyStringQuery = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pstmtKeyStr = conn.prepareStatement(keyStringQuery);
			pstmtKeyStr.setString(1,"w_indent");
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				indKeyStr = rs.getString("KEY_STRING");
				rs.close();
			}
			pstmtKeyStr.clearParameters();
			pstmtKeyStr.setString(1,"w_independent_demand");
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				indDemKeyStr = rs.getString("KEY_STRING");
				rs.close();
			}
			pstmtKeyStr.clearParameters();
			pstmtKeyStr.setString(1,"w_dist_demand");
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				disDemKeyStr = rs.getString("KEY_STRING");
				rs.close();
			}
			pstmtKeyStr.clearParameters();
			pstmtKeyStr.setString(1,"w_worder");
			rs = pstmtKeyStr.executeQuery();
			if(rs.next())
			{
				woKeyStr = rs.getString("KEY_STRING");
			}
			rs.close();
			rs = null;
			pstmtKeyStr.close();
			pstmtKeyStr = null;


			// 24/07/08 manoharan
			//SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yy");
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());


			sqlWorkOrder = "INSERT INTO WORKORDER(TRAN_ID,ORDER_TYPE,ITEM_CODE,SITE_CODE, "
						+ "QUANTITY,ORD_DATE,PLAN_DUE_DATE,MRP_RUN_ID, "
						+ "ADDN_ORDER,DUE_DATE,STATUS,STATUS_DATE,YIELD_PERC,ROUTE_CODE, "
						+ "BOM_CODE,PROJ_CODE,CHG_DATE,CHG_USER,CHG_TERM,UNIT, "
						+ "ACCT_CODE__WP,CCTR_CODE__WP,CURRENT_BATCH_QTY,UNIT__STD,"
						+ " STD_QTY,CONV__QTY_STDUOM,SALE_ORDER,LINE_NO,WORK_ORDER) "
						+ "VALUES ( ?, ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? ,?, ?, ?,?,?)";

			pstmtWorkOrder = conn.prepareStatement(sqlWorkOrder);

			String sqlMpsOrder = "INSERT INTO MPS_ORDER(TRAN_ID,ORDER_TYPE,ITEM_CODE,SITE_CODE, "
						+ "QUANTITY,ORD_DATE,PLAN_DUE_DATE,MRP_RUN_ID, "
						+ "ADDN_ORDER,DUE_DATE,STATUS,STATUS_DATE,YIELD_PERC,ROUTE_CODE, "
						+ "BOM_CODE,PROJ_CODE,CHG_DATE,CHG_USER,CHG_TERM,UNIT, "
						+ "ACCT_CODE__WP,CCTR_CODE__WP,CURRENT_BATCH_QTY,UNIT__STD,STD_QTY,CONV__QTY_STDUOM,SALE_ORDER,LINE_NO) "
						+ "VALUES ( ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? , ? , ? ,"
							 	+ " ? , ? , ? ,?, ?, ?,?,?)";

			pstmtMpsOrder = conn.prepareStatement(sqlMpsOrder);

			String sqlIndent = "INSERT INTO INDENT(IND_NO,IND_DATE,IND_TYPE,DEPT_CODE, "
					  + "PLAN_DUE_DATE,REQ_DATE,ITEM_CODE,ITEM_DESCR, "
					  + "QUANTITY,QUANTITY__STDUOM,ORD_QTY,UNIT__IND,UNIT__STD,SITE_CODE, "
					  + "SITE_CODE__DLV,SITE_CODE__ACCT,APR_CODE,PRIORITY,ACCT_CODE, "
					  + "CCTR_CODE,EMP_CODE__PUR,EMP_CODE__REQ,SUPP_CODE__PREF,PURC_RATE,"
					  + "PACK_CODE,STATUS,CHG_DATE,CHG_USER,CHG_TERM,PUR_LEAD_TIME,CONV__QTY_STDUOM) "
					  + "VALUES ( ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? )";
			pstmtIndent = conn.prepareStatement(sqlIndent);

			String sqlDistDemand = "INSERT INTO DIST_DEMAND(TRAN_ID,TRAN_DATE,SITE_CODE,ITEM_CODE, "
						  + "PLAN_DUE_DATE,DUE_DATE,QTY_REQUIRED,UNIT,SITE_CODE__SOURCE, "
						  + "STATUS,DUE_DATE__ORD,CHG_DATE,CHG_USER,CHG_TERM) "
						  + "VALUES ( ? , ? , ? , ? , ? ,"
								  + " ? , ? , ? , ? , ? , "
								  + " ? , ? , ? , ? )";
			pstmtDistDemand = conn.prepareStatement(sqlDistDemand);

			String sqlIndDemand = "INSERT INTO INDEPENDENT_DEMAND(TRAN_ID, TRAN_DATE, SITE_CODE, ITEM_CODE, "
						 + "QUANTITY, UNIT, CONV__QTY_STDUOM, QUANTITY__STD_UOM, UNIT__STD, DUE_DATE, "
						 + "STATUS, SITE_CODE__CUST, CHG_DATE, CHG_USER, CHG_TERM, PLAN_DUE_DATE ) "
						 + "VALUES( ? , ? , ? , ? , ? , ? ,"
					  		  	+ " ? , ? , ? , ? , ? , ? ,"
					  		  	+ " ? , ? , ? , ? ) ";
			pstmtIndDemand = conn.prepareStatement(sqlIndDemand);

			//String tranDate = this.genericUtility.getValidDateString(new java.util.Date().toString(),  this.genericUtility.getDBDateFormat(),this.genericUtility.getApplDateFormat());

			if(winName.equalsIgnoreCase("W_MPS_ORDER"))
			{
				//System.out.println("W_MPS_ORDER");
				for(int count = 0; count<list.size(); count++)
				{

					mpsOrder = (MPSOrder)list.get(count);

					ordType = mpsOrder.ordType;
					// 30/06/10 manoharan
					if (ordType == null || ordType.trim().length() == 0)
					{
						ordType = "O";
					}
					itemCode = mpsOrder.itemCode;
					siteCode = mpsOrder.siteCode;
					quantity = mpsOrder.quantity;
					ordDate = mpsOrder.ordDate;
					ordDateStr = sdf.format(mpsOrder.ordDate);
					planDueDateStr = sdf.format(mpsOrder.planDueDate);
					planDueDate = mpsOrder.planDueDate;
					mrpRunId = mpsOrder.mrpRunId;
					addnOrder = mpsOrder.addnOrder;
					dueDate = mpsOrder.dueDate;
					dueDateStr = sdf.format(mpsOrder.dueDate);
					statusDate = mpsOrder.statusDate;
					statusDateStr = sdf.format(mpsOrder.statusDate);
					yieldPerc = mpsOrder.yieldPerc;
					routeCode = mpsOrder.routeCode;
					bomCode = mpsOrder.bomCode;
					projCode = mpsOrder.projCode;
					chgDate = mpsOrder.chgDate;
					chgDateStr = sdf.format(mpsOrder.chgDate);
					unit = mpsOrder.unit;
					acctCodeWp = mpsOrder.acctCodeWp;
					cctrCodeWp = mpsOrder.cctrCodeWp;
					//added by sabyasachi 30-03-11
					saleOrder = mpsOrder.saleOrder;
					lineNo = mpsOrder.lineNo;
					//added by sabyasachi ends 30-03-11
					// 20/01/09 manoharan
					//xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<work_order><![CDATA[]]></work_order>";
					xmlValues = xmlValues + "<order_type><![CDATA[" + ordType + "]]></order_type>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<ord_date><![CDATA[" + ordDateStr + "]]></ord_date>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDateStr + "]]></plan_due_date>";
					xmlValues = xmlValues + "<mrp_run_id><![CDATA[" + mrpRunId + "]]></mrp_run_id>";
					xmlValues = xmlValues + "<addn_order><![CDATA[" + addnOrder + "]]></addn_order>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDateStr + "]]></due_date>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<status_date><![CDATA[" + statusDateStr + "]]></status_date>";
					xmlValues = xmlValues + "<yield_perc><![CDATA[" + yieldPerc + "]]></yield_perc>";
					xmlValues = xmlValues + "<route_code><![CDATA[" + routeCode + "]]></route_code>";
					xmlValues = xmlValues + "<bom_code><![CDATA[" + bomCode + "]]></bom_code>";
					xmlValues = xmlValues + "<proj_code><![CDATA[" + projCode + "]]></proj_code>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDateStr + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<acct_code__wp><![CDATA[" + acctCodeWp + "]]></acct_code__wp>";
					xmlValues = xmlValues + "<cctr_code__wp><![CDATA[" + cctrCodeWp + "]]></cctr_code__wp>";
					xmlValues = xmlValues + "<current_batch_qty><![CDATA[" + quantity + "]]></current_batch_qty>";
					xmlValues = xmlValues + "</Detail1></Root>";
					if ("O".equals(mpsOrder.mfgType))
					{
						workOrder = getUniqueKey(xmlValues, mpsKeyStr, "work_order", "W-ORD");
						pstmtWorkOrder.setString(1, workOrder);
						pstmtWorkOrder.setString(2, ordType);
						pstmtWorkOrder.setString(3, itemCode);
						pstmtWorkOrder.setString(4, siteCode);
						pstmtWorkOrder.setDouble(5, quantity);
						pstmtWorkOrder.setTimestamp(6, ordDate);
						pstmtWorkOrder.setTimestamp(7, planDueDate);
						pstmtWorkOrder.setString(8, mrpRunId);
						pstmtWorkOrder.setString(9, addnOrder);
						pstmtWorkOrder.setTimestamp(10, dueDate);
						pstmtWorkOrder.setString(11, "P");
						pstmtWorkOrder.setTimestamp(12, statusDate);
						pstmtWorkOrder.setDouble(13, yieldPerc);
						pstmtWorkOrder.setString(14, routeCode);
						pstmtWorkOrder.setString(15, bomCode);
						pstmtWorkOrder.setString(16, projCode);
						pstmtWorkOrder.setTimestamp(17, chgDate);
						pstmtWorkOrder.setString(18, chgUser);
						pstmtWorkOrder.setString(19, chgTerm);
						pstmtWorkOrder.setString(20, unit);
						pstmtWorkOrder.setString(21, acctCodeWp);
						pstmtWorkOrder.setString(22, cctrCodeWp);
						pstmtWorkOrder.setDouble(23, quantity);
						pstmtWorkOrder.setString(24, unit);
						pstmtWorkOrder.setDouble(25, quantity);
						pstmtWorkOrder.setDouble(26, 1);
						if(saleOrder != null && saleOrder.trim().length() > 0)
						{
							pstmtWorkOrder.setString(27,saleOrder);
						}
						else
						{
							pstmtWorkOrder.setNull(27,java.sql.Types.CHAR);
						}
						if(lineNo != null && lineNo.trim().length() > 0)
						{
							pstmtWorkOrder.setString(28,lineNo);
						}
						else
						{
							pstmtWorkOrder.setNull(28,java.sql.Types.CHAR);
						}
						pstmtWorkOrder.setString(29,workOrder);
						pstmtWorkOrder.addBatch();
					}
					else
					{
						tranIdMpsOrder = getUniqueKey(xmlValues, mpsKeyStr, "tran_id", "W-MPS");
						pstmtMpsOrder.setString(1, tranIdMpsOrder);
						pstmtMpsOrder.setString(2, ordType);
						pstmtMpsOrder.setString(3, itemCode);
						pstmtMpsOrder.setString(4, siteCode);
						pstmtMpsOrder.setDouble(5, quantity);
						pstmtMpsOrder.setTimestamp(6, ordDate);
						pstmtMpsOrder.setTimestamp(7, planDueDate);
						pstmtMpsOrder.setString(8, mrpRunId);
						pstmtMpsOrder.setString(9, addnOrder);
						pstmtMpsOrder.setTimestamp(10, dueDate);
						pstmtMpsOrder.setString(11, "P");
						pstmtMpsOrder.setTimestamp(12, statusDate);
						pstmtMpsOrder.setDouble(13, yieldPerc);
						pstmtMpsOrder.setString(14, routeCode);
						pstmtMpsOrder.setString(15, bomCode);
						pstmtMpsOrder.setString(16, projCode);
						pstmtMpsOrder.setTimestamp(17, chgDate);
						pstmtMpsOrder.setString(18, chgUser);
						pstmtMpsOrder.setString(19, chgTerm);
						pstmtMpsOrder.setString(20, unit);
						pstmtMpsOrder.setString(21, acctCodeWp);
						pstmtMpsOrder.setString(22, cctrCodeWp);
						pstmtMpsOrder.setDouble(23, quantity);
						pstmtMpsOrder.setString(24, unit);
						pstmtMpsOrder.setDouble(25, quantity);
						pstmtMpsOrder.setDouble(26, 1);
						if(saleOrder != null && saleOrder.trim().length() > 0)
						{
							pstmtMpsOrder.setString(27,saleOrder);
						}
						else
						{
							pstmtMpsOrder.setNull(27,java.sql.Types.CHAR);
						}
						if(lineNo != null && lineNo.trim().length() > 0)
						{
							pstmtMpsOrder.setString(28,lineNo);
						}
						else
						{
							pstmtMpsOrder.setNull(28,java.sql.Types.CHAR);
						}

						//**************pstmtMpsOrder.setDouble(25, demandQty);
						pstmtMpsOrder.addBatch();
					}

					//System.out.println("Inserting mpsorder # " + count + " : " + tranIdMpsOrder);

				}
			}
			else if(winName.equalsIgnoreCase("W_INDENT"))
			{
				for(int count = 0; count<list.size(); count++)
				{
					indent = (Indent)list.get(count);

					indDateStr = sdf.format(indent.indDate);
					indDate = indent.indDate;
					planDueDateStr = sdf.format(indent.planDueDate);
					planDueDate = indent.planDueDate;
					reqDate = indent.reqDate;
					reqDateStr = sdf.format(indent.reqDate);
					itemCode = indent.itemCode;
					itemDescr = indent.itemDescr;
					quantity = indent.quantity;
					quantityStdUom = indent.quantityStdUom;
					unitInd = indent.unitInd;
					unitStd = indent.unitStd;
					siteCode = indent.siteCode;
					siteCodeDlv = indent.siteCodeDlv;
					siteCodeAcct = indent.siteCodeAcct;
					aprCode = indent.aprCode;
					acctCode = indent.acctCode;
					cctrCode = indent.cctrCode;
					empCodePur = indent.empCodePur;
					empCodeReq = indent.empCodeReq;
					suppCodePref = indent.suppCodePref;
					purcRate = indent.purcRate;
					packCode = indent.packCode;
					chgDateStr = sdf.format(indent.chgDate);
					chgDate = indent.chgDate;
					purLeadTime = indent.purLeadTime;
					convQtyStdUom = indent.convQtyStdUom;

					// 20/01/09 manoharan
					//xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues + "<ind_no><![CDATA[]]></ind_no>";
					xmlValues = xmlValues + "<ind_date><![CDATA[" + indDateStr + "]]></ind_date>";
					xmlValues = xmlValues + "<ind_type><![CDATA[" + indType + "]]></ind_type>";
					xmlValues = xmlValues + "<dept_code><![CDATA[" + deptCodeMfg + "]]></dept_code>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDateStr + "]]></plan_due_date>";
					xmlValues = xmlValues + "<req_date><![CDATA[" + reqDate + "]]></req_date>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<item_desc><![CDATA[" + itemDescr + "]]></item_desc>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<quantity__stduom><![CDATA[" + quantityStdUom + "]]></quantity__stduom>";
					xmlValues = xmlValues + "<unit__ind><![CDATA[" + unitInd + "]]></unit__ind>";
					xmlValues = xmlValues + "<unit__std><![CDATA[" + unitStd + "]]></unit__std>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<site_code__dlv><![CDATA[" + siteCodeDlv + "]]></site_code__dlv>";
					xmlValues = xmlValues + "<site_code__acct><![CDATA[" + siteCodeAcct + "]]></site_code__acct>";
					xmlValues = xmlValues + "<apr_code><![CDATA[" + aprCode + "]]></apr_code>";
					xmlValues = xmlValues + "<priority><![CDATA[" + priority + "]]></priority>";
					xmlValues = xmlValues + "<acct_code><![CDATA[" + acctCode + "]]></acct_code>";
					xmlValues = xmlValues + "<cctr_code><![CDATA[" + cctrCode + "]]></cctr_code>";
					xmlValues = xmlValues + "<emp_code__pur><![CDATA[" + empCodePur + "]]></emp_code__pur>";
					xmlValues = xmlValues + "<emp_code__req><![CDATA[" + empCodeReq + "]]></emp_code__req>";
					xmlValues = xmlValues + "<supp_code__pref><![CDATA[" + suppCodePref + "]]></supp_code__pref>";
					xmlValues = xmlValues + "<purc_rate><![CDATA[" + purcRate + "]]></purc_rate>";
					xmlValues = xmlValues + "<pack_code><![CDATA[" + packCode + "]]></pack_code>";
					xmlValues = xmlValues + "<status><![CDATA[U]]></status>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDateStr + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";
					xmlValues = xmlValues + "<pur_lead_time><![CDATA[" + purLeadTime + "]]></pur_lead_time>";
					xmlValues = xmlValues + "<conv__qty_stdoum><![CDATA[" + convQtyStdUom + "]]></conv__qty_stdoum>";
					xmlValues = xmlValues + "</Detail1></Root>";

					tranIdIndent = getUniqueKey(xmlValues, indKeyStr, "ind_no", "IND");

					//System.out.println("Inserting indent # " + count + " " + tranIdIndent);

					pstmtIndent.setString(1, tranIdIndent);
					pstmtIndent.setTimestamp(2, indDate);
					pstmtIndent.setString(3, indType);
					pstmtIndent.setString(4, deptCodeMfg);
					pstmtIndent.setTimestamp(5, planDueDate);
					pstmtIndent.setTimestamp(6, reqDate);
					pstmtIndent.setString(7, itemCode);
					pstmtIndent.setString(8, itemDescr);
					pstmtIndent.setDouble(9, quantity);
					pstmtIndent.setDouble(10, quantity);
					pstmtIndent.setDouble(11, 0);
					pstmtIndent.setString(12, unitInd);
					pstmtIndent.setString(13, unitStd);
					pstmtIndent.setString(14, siteCode);
					pstmtIndent.setString(15, siteCodeDlv);
					pstmtIndent.setString(16, siteCodeAcct);
					pstmtIndent.setString(17, aprCode);
					pstmtIndent.setString(18, priority);
					pstmtIndent.setString(19, acctCode);
					pstmtIndent.setString(20, cctrCode);
					pstmtIndent.setString(21, empCodePur);
					pstmtIndent.setString(22, empCodeReq);
					pstmtIndent.setString(23, suppCodePref);
					pstmtIndent.setDouble(24, purcRate);
					pstmtIndent.setString(25, packCode);
					pstmtIndent.setString(26, "U");
					pstmtIndent.setTimestamp(27, chgDate);
					pstmtIndent.setString(28, chgUser);
					pstmtIndent.setString(29, chgTerm);
					pstmtIndent.setDouble(30, purLeadTime);
					pstmtIndent.setDouble(31, convQtyStdUom);

					pstmtIndent.addBatch();
				}
			}
			else if(winName.equalsIgnoreCase("W_DIST_DEMAND"))
			{
				for(int count = 0; count<list.size(); count++)
				{
					distDemand = (DistDemand)list.get(count);

					tranDate = distDemand.tranDate;
					tranDateStr = sdf.format(distDemand.tranDate);
					siteCode = distDemand.siteCode;
					itemCode = distDemand.itemCode;
					planDueDateStr = sdf.format(distDemand.planDueDate);
					planDueDate = distDemand.planDueDate;
					dueDateStr = sdf.format(distDemand.dueDate);
					dueDate = distDemand.dueDate;
					qtyRequired = distDemand.qtyRequired;
					unit = distDemand.unit;
					siteCodeSrc = distDemand.siteCodeSrc;
					dueDateOrd = distDemand.dueDateOrd;
					dueDateOrdStr = sdf.format(distDemand.dueDateOrd);
					chgDate = distDemand.chgDate;
					chgDateStr = sdf.format(distDemand.chgDate);

					// 20/01/09 manoharan
					//xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<tran_date><![CDATA[" + tranDateStr + "]]></tran_date>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDateStr + "]]></plan_due_date>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDateStr + "]]></due_date>";
					xmlValues = xmlValues + "<qty_required><![CDATA[" + qtyRequired + "]]></qty_required>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<site_code__source><![CDATA[" + siteCodeSrc + "]]></site_code__source>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<due_date__ord><![CDATA[" + dueDateOrd + "]]></due_date__ord>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDateStr + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";
					xmlValues = xmlValues + "</Detail1></Root>";

					tranIdDistDemand = getUniqueKey(xmlValues, disDemKeyStr, "tran_id", "D-DEM");

					//System.out.println("Inserting dist demand # " + count + " " + tranIdDistDemand);

					pstmtDistDemand.setString(1, tranIdDistDemand);
					pstmtDistDemand.setTimestamp(2, tranDate);
					pstmtDistDemand.setString(3, siteCode);
					pstmtDistDemand.setString(4, itemCode);
					pstmtDistDemand.setTimestamp(5, planDueDate);
					pstmtDistDemand.setTimestamp(6, dueDate);
					pstmtDistDemand.setDouble(7, qtyRequired);
					pstmtDistDemand.setString(8, unit);
					pstmtDistDemand.setString(9, siteCodeSrc);
					pstmtDistDemand.setString(10, status);
					pstmtDistDemand.setTimestamp(11, dueDateOrd);
					pstmtDistDemand.setTimestamp(12, chgDate);
					pstmtDistDemand.setString(13, chgUser);
					pstmtDistDemand.setString(14, chgTerm);

					pstmtDistDemand.addBatch();
				}
			}
			else if(winName.equalsIgnoreCase("W_INDEPENDENT_DEMAND"))
			{
				for(int count = 0; count<list.size(); count++)
				{
					independentDemand = (IndependentDemand)list.get(count);

					siteCode = independentDemand.siteCode;
					siteCodeCust = independentDemand.siteCodeCust;
					itemCode = independentDemand.itemCode;
					unit = independentDemand.unit;
					unitStd = independentDemand.unitStd;
					quantity = independentDemand.quantity;
					quantityStdUom = independentDemand.quantityStdUom;
					convQtyStdUom = independentDemand.convQtyStdUom;
					tranDateStr = sdf.format(independentDemand.tranDate);
					tranDate = independentDemand.tranDate;
					planDueDate = independentDemand.planDueDate;
					planDueDateStr = sdf.format(independentDemand.planDueDate);
					dueDate = independentDemand.dueDate;
					dueDateStr = sdf.format(independentDemand.dueDate);

					// 20/01/09 manoharan
					//xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
					xmlValues ="<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?><Root>";
					xmlValues = xmlValues + "<Header></Header>";
					xmlValues = xmlValues + "<Detail1>";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<site_code__cust><![CDATA[" + siteCodeCust + "]]></site_code__cust>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<unit__std><![CDATA[" + unitStd + "]]></unit__std>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<quantity__std_uom><![CDATA[" + quantityStdUom + "]]></quantity__std_uom>";
					xmlValues = xmlValues + "<conv__qty_std_oum><![CDATA[" + convQtyStdUom + "]]></conv__qty_std_oum>";
					xmlValues = xmlValues + "<tran_date><![CDATA[" + tranDateStr + "]]></tran_date>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDateStr + "]]></plan_due_date>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDateStr + "]]></due_date>";
					xmlValues = xmlValues + "</Detail1></Root>";

					tranIdIndDemand = getUniqueKey(xmlValues, indDemKeyStr, "tran_id", "I-DEM");
					//System.out.println(" ");
					//System.out.println(xmlValues);
					//System.out.println(" ");

					//System.out.println("Inserting independent demand # " + count + " " + tranIdIndDemand);
					pstmtIndDemand.setString(1, tranIdIndDemand);
					pstmtIndDemand.setTimestamp(2, tranDate);
					pstmtIndDemand.setString(3, siteCode);
					pstmtIndDemand.setString(4, itemCode);
					pstmtIndDemand.setDouble(5, quantity);
					pstmtIndDemand.setString(6, unit);
					pstmtIndDemand.setDouble(7, convQtyStdUom);
					pstmtIndDemand.setDouble(8, quantityStdUom);
					pstmtIndDemand.setString(9, unitStd);
					pstmtIndDemand.setTimestamp(10, dueDate);
					pstmtIndDemand.setString(11, status);
					pstmtIndDemand.setString(12, siteCodeCust);
					pstmtIndDemand.setTimestamp(13, chgDate);
					pstmtIndDemand.setString(14, chgUser);
					pstmtIndDemand.setString(15, chgTerm);
					pstmtIndDemand.setTimestamp(16, planDueDate);

					pstmtIndDemand.addBatch();

				}
			}

			int rowsInsertedWorkOrder[] = pstmtWorkOrder.executeBatch();
			//Commented by Jiten 31/10/06 - error -null pointer Exception
			//pstmtMpsOrder.clearParameters();
			pstmtWorkOrder.close();
			//int [] insertToIndependentDemandCounts = pstmtMpsOrder.executeBatch() ;
			int rowsInsertedMpsOrder[] = pstmtMpsOrder.executeBatch();
			//Commented by Jiten 31/10/06 - error -null pointer Exception
			//pstmtMpsOrder.clearParameters();
			pstmtMpsOrder.close();
			//End
			//conn.commit();

			//int [] insertTopstmtIndentCounts = pstmtIndent.executeBatch();
			int rowsInsertedIndent[] = pstmtIndent.executeBatch();
			//pstmtIndent.clearParameters();//Commented by Jiten 14/11/06 as giving null pointer exception
			pstmtIndent.close();
			//conn.commit();

			//int [] insertToDistDemandCounts = pstmtDistDemand.executeBatch();
			int rowsInsertedDistDemand[] = pstmtDistDemand.executeBatch();
			//pstmtDistDemand.clearParameters();//Commented by Jiten 14/11/06 as giving null pointer exception
			pstmtDistDemand.close();
			//conn.commit();

			int rowsInsertedIndDemand[] = pstmtIndDemand.executeBatch();
			//pstmtIndDemand.clearParameters();//Commented by Jiten 14/11/06 as giving null pointer exception
			pstmtIndDemand.close();
			conn.commit();
			System.out.println("Rows Inserted in Work Order   : [" + rowsInsertedWorkOrder.length + "]");
			System.out.println("Rows Inserted in Mps Order   : [" + rowsInsertedMpsOrder.length + "]");
			System.out.println("Rows Inserted in Indent      : [" + rowsInsertedIndent.length + "]");
			System.out.println("Rows Inserted in Dist Demand : [" + rowsInsertedDistDemand.length + "]");
			System.out.println("Rows Inserted in Ind Demand  : [" + rowsInsertedIndDemand.length + "]");

		} // End of try
		catch(BatchUpdateException be)
		{
			be.printStackTrace();
			errString = be.getMessage();
			System.out.println("BatchUpdateException :RunMRPPrcEJB: CreatePlanDemand(ADPElement)" + errString);
			System.out.println("Detail Exception : " + be.getNextException());
		}
		catch(SQLException e)
		{
			System.out.println("SQLException :RunMRPPrcEJB : CreatePlanDemand(ADPElement adpElement).." +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB : CreatePlanDemand(ADPElement adpElement) : " +e.getMessage());
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			if (pstmtWorkOrder != null)
			{
				pstmtWorkOrder = null;
			}
			if (pstmtMpsOrder != null)
			{
				pstmtMpsOrder = null;
			}
			if (pstmtIndent != null)
			{
				pstmtIndent = null;
			}
			if (pstmtDistDemand != null)
			{
				pstmtDistDemand = null;
			}
			//return errString;
		}
		return errString;//Moved by Jiten as finally block does not complete normally
	}
	private String getUniqueKey(String xmlValues, String keyString, String keyCol, String tranSer)throws ITMException
	{
		String uniqueKey = null;
		try
		{
			//System.out.println("Key String for " + tranSer + " - " + keyString);
			TransIDGenerator tg = new TransIDGenerator(xmlValues, userId, CommonConstants.DB_NAME);
			uniqueKey = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
		}
		catch(Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB : getUniqueKey :==>\n" + e.getMessage());
			e.printStackTrace();
			uniqueKey = null;
			throw new ITMException(e);
		}
		//System.out.println("Generated Primary Key :" + uniqueKey);
		return uniqueKey;
	}
////////////////////////
	public String InsertPlanTable() throws Exception
	{
		PreparedStatement pstmtPlanHdr = null;
		PreparedStatement pstmtPlanDet = null;
		String resultString = "";

		String siteCode = "";
		String siteCodeSupp = "";
		String itemCode = "";
		String itemSer = "";
		String itemStru = "";
		String masterSch = "";
		String bomCode = "";
		String suppSour = "";
		String suppCodePref = "";
		String orderOpt = "";
		String dueDateStr = "";
		String batchQtyType = "";
		String empCodePur = "";
		String packCode = "";
		String unit = "";
		String acctCodePh = "";
		String acctCodeWp = "";
		String cctrCodePh = "";
		String aprCode = "";
		String cctrCodeWp = "";

		double demand = 0;
		double supply = 0;
		double planDemand = 0;
		double planSupply = 0;
		double runningDemand = 0;
		double runningSupply = 0;
		double runningPlanSupply = 0;
		double independentDemand = 0;
		double minOrderQty = 0;
		double maxOrderQty = 0;
		double integralQty = 0;
		double maxQty = 0;
		double maxStkQty = 0;
		double minQty = 0;
		double reorderQty = 0;
		double yieldPerc = 100;
		double minPlanPerc = 0;
		double batchQty = 0;
		double purcRate = 0;
		// 12/04/10 manoharan MF90BHU001
		double stockQty = 0;
		double othSupply = 0;
		// end 12/04/10 Manoharan MF90BHU001
		int purLeadTime = 0;
		int qcLeadTime = 0;
		int mfgLeadTime = 0;
		int minStkDay = 0;
		int maxStkDay = 0;
		int hdrSeq = 0,seqNo = 0;
		String saleOrder, lineNo;
		boolean flag = true;

		java.sql.Timestamp dueDate = null;

		ADPElement adpElement = null;
		try
		{
			//modified query. added SALE_ORDER & LINE_NO by sabyasachi on 29-03-2011
			// 01/04/11 manoharan sale_order,line_no and seq_no added
			String sqlPlanHdr = "INSERT INTO PLAN_HDR(SITE_CODE, ITEM_CODE, ITEM_SER, SITE_CODE__SUPP, "
						 	  + "SUPP_SOUR, BOM_CODE, MIN_QTY, MIN_ORDER_QTY, MIN_STK_DAY, MAX_QTY, "
						 	  + "MAX_STK_DAY, ITEM_STRU, REO_QTY, PUR_LEAD_TIME, QC_LEAD_TIME, "
						 	  + "MFG_LEAD_TIME, INTEGRAL_QTY, BATCH_QTY, BATCH_QTY_TYPE, MASTER_SCH, ORDER_OPT, "
						 	  + "YIELD_PERC, MIN_PLAN_PERC, UNIT, APR_CODE, PACK_CODE, EMP_CODE__PUR, "
						 	  + "SUPP_CODE__PREF, PURC_RATE, ACCT_CODE__PH, CCTR_CODE__PH, ACCT_CODE__WP,"
							  + " CCTR_CODE__WP,SALE_ORDER,LINE_NO,seq_no) "
						 	  + "VALUES( ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ?, ?, ?) ";
			pstmtPlanHdr = conn.prepareStatement(sqlPlanHdr);
			// 12/04/10 manoharan MF90BHU001 stock_qty and oth_supply added
			String sqlPlanDet = "INSERT INTO PLAN_DET(SITE_CODE, ITEM_CODE, DEMAND, SUPPLY, PLAN_SUPP, "
						 	  + "RUNNING_DEMAND, RUNNING_SUPP, RUNNING_PLAN_SUPP, IND_DEMAND, DUE_DATE, "
							  + " STOCK_QTY, OTH_SUPPLY,SALE_ORDER,LINE_NO,SEQ_NO) "
						 	  + " VALUES( ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ?, ?, ?, ?, ? ,?) ";
			// end 12/04/10 manoharan MF90BHU001 stock_qty and oth_supply added
			pstmtPlanDet = conn.prepareStatement(sqlPlanDet);
			hdrSeq = 0;
			for(int adpeCount=0; adpeCount<adpeList.size(); adpeCount++)
			{
				flag = true;
				adpElement = (ADPElement)adpeList.get(adpeCount);

				siteCode = adpElement.getSiteCode();
				itemCode = adpElement.getItemCode();
				itemSer = adpElement.getItemSer();
				siteCodeSupp = adpElement.getSiteCodeSupp();
				suppSour = adpElement.getSuppSour();
				bomCode =  adpElement.getBomCode();
				minQty = adpElement.getMinQty();
				minOrderQty =  adpElement.getMinOrderQty();
				minStkDay =  adpElement.getMinStkDay();
				maxQty = adpElement.getMaxQty();
				maxStkDay =  adpElement.getMaxStkDay();
				itemStru = adpElement.getItemStru();
				reorderQty =  adpElement.getReoQty();
				purLeadTime = adpElement.getPurLeadTime();
				qcLeadTime = adpElement.getQcLeadTime();
				mfgLeadTime = adpElement.getMfgLeadTime();
				integralQty =  adpElement.getIntegralQty();
				batchQty =  adpElement.getBatchQty();
				batchQtyType = adpElement.getBatchQtyType();
				masterSch = adpElement.getMasterSch();
				orderOpt = adpElement.getOrderOpt();
				yieldPerc =  adpElement.getYieldPerc();
				minPlanPerc =  adpElement.getMinPlanPerc();
				unit =  adpElement.getUnit();
				aprCode = adpElement.getAprCode();
				packCode = adpElement.getPackCode();
				empCodePur = adpElement.getEmpCodePur();
				suppCodePref = adpElement.getSuppCodePref();
				purcRate = adpElement.getPurcRate();
				acctCodePh = adpElement.getAcctCodePh();
				cctrCodePh = adpElement.getCctrCodePh();
				acctCodeWp = adpElement.getAcctCodeWp();
				cctrCodeWp = adpElement.getCctrCodeWp();
				//added by sabyasachi on 29-03-2011
				saleOrder = adpElement.getSaleOrder();
				lineNo = adpElement.getLineNo();
				//seqNo = 0;
				//added by sabyasachi on 29-03-2011 ends
				// 02-03-2007 manoharan not to insert if the plan is not selected
				if(!disPlan.equalsIgnoreCase("Y") && (suppSour.trim().equalsIgnoreCase("D")))
					 {
						continue;
					 }
				if(!matPlan.equalsIgnoreCase("Y") && (suppSour.trim().equalsIgnoreCase("P")))
				 {
					continue;
				 }
				if(!mfgPlan.equalsIgnoreCase("Y") && (suppSour.trim().equalsIgnoreCase("M") ||suppSour.trim().equalsIgnoreCase("C") ))
				 {
					continue;
				 }
				// end 02-03-2007 manoharan not to insert if the plan is not selected
				ArrayList timeMRParr = adpElement.getTimeMrpList();
				if(bomCode != null && bomCode.trim().length() == 0) bomCode = null;
				for (int timeCtr = 0; timeCtr < timeMRParr.size() ; timeCtr++)
				{
					TimeMRP timeMrp = (TimeMRP)timeMRParr.get(timeCtr);

					demand = timeMrp.getDemand();
					supply = timeMrp.getSupply();
					planSupply = timeMrp.getPlanSupply();
					runningDemand = timeMrp.getRunningDemand();
					runningSupply = timeMrp.getRunningSupply();
					runningPlanSupply = timeMrp.getRunningPlanSupply();
					// 01/04/11
					if (runningPlanSupply <= 0 && demand <= 0 && supply <= 0 && planSupply <= 0 && runningDemand <= 0 && runningSupply <= 0)
					{
						continue;
					}
					independentDemand = timeMrp.getIndependentDemand();
					dueDate = timeMrp.getDueDate();
					// 12/04/10 Manoharan MF90BHU001
					stockQty = timeMrp.getStockQty();
					othSupply = timeMrp.getOthSupply();
					// end 12/04/10 Manoharan MF90BHU001

					pstmtPlanDet.setString(1,siteCode);
					pstmtPlanDet.setString(2,itemCode);
					pstmtPlanDet.setDouble(3,demand);
					pstmtPlanDet.setDouble(4,supply);
					pstmtPlanDet.setDouble(5,planSupply);
					pstmtPlanDet.setDouble(6,runningDemand);
					pstmtPlanDet.setDouble(7,runningSupply);
					pstmtPlanDet.setDouble(8,runningPlanSupply);
					pstmtPlanDet.setDouble(9,independentDemand);
					pstmtPlanDet.setTimestamp(10,dueDate);
					// 12/04/10 Manoharan MF90BHU001
					pstmtPlanDet.setDouble(11,stockQty);
					pstmtPlanDet.setDouble(12,othSupply);
					// end 12/04/10 Manoharan MF90BHU001
					// 01/04/11 manoharan
					if(saleOrder != null && saleOrder.trim().length() > 0)
					{
						pstmtPlanDet.setString(13,saleOrder);
					}
					else
					{
						pstmtPlanDet.setNull(13,java.sql.Types.CHAR);
					}
					if(lineNo != null && lineNo.trim().length() > 0)
					{
						pstmtPlanDet.setString(14,lineNo);
					}
					else
					{
						pstmtPlanDet.setNull(14,java.sql.Types.CHAR);
					}
					seqNo++;
					pstmtPlanDet.setInt(15,seqNo);
					// end 01/04/11 manoharan

					pstmtPlanDet.addBatch();
				}
				if (seqNo > 0)
				{
					pstmtPlanHdr.setString(1,siteCode);
					pstmtPlanHdr.setString(2,itemCode);
					pstmtPlanHdr.setString(3,itemSer);
					pstmtPlanHdr.setString(4,siteCodeSupp);
					pstmtPlanHdr.setString(5,suppSour);
					if(bomCode != null && bomCode.trim().length() > 0){//If Else condition added by Jiten 14/11/06
						pstmtPlanHdr.setString(6,bomCode);
					}
					else{
						pstmtPlanHdr.setNull(6,java.sql.Types.VARCHAR);
					}
					pstmtPlanHdr.setDouble(7,minQty);
					pstmtPlanHdr.setDouble(8,minOrderQty);
					pstmtPlanHdr.setInt(9,minStkDay);
					pstmtPlanHdr.setDouble(10,maxQty);
					pstmtPlanHdr.setInt(11,maxStkDay);
					pstmtPlanHdr.setString(12,itemStru);
					pstmtPlanHdr.setDouble(13,reorderQty);
					pstmtPlanHdr.setInt(14,purLeadTime);
					pstmtPlanHdr.setInt(15,qcLeadTime);
					pstmtPlanHdr.setInt(16,mfgLeadTime);
					pstmtPlanHdr.setDouble(17,integralQty);
					pstmtPlanHdr.setDouble(18,batchQty);
					pstmtPlanHdr.setString(19,batchQtyType);
					pstmtPlanHdr.setString(20,masterSch);
					pstmtPlanHdr.setString(21,orderOpt);
					pstmtPlanHdr.setDouble(22,yieldPerc);
					pstmtPlanHdr.setDouble(23,minPlanPerc);
					pstmtPlanHdr.setString(24,unit);
					pstmtPlanHdr.setString(25,aprCode);
					pstmtPlanHdr.setString(26,packCode);
					pstmtPlanHdr.setString(27,empCodePur);
					pstmtPlanHdr.setString(28,suppCodePref);
					pstmtPlanHdr.setDouble(29,purcRate);
					pstmtPlanHdr.setString(30,acctCodePh);
					pstmtPlanHdr.setString(31,cctrCodePh);
					pstmtPlanHdr.setString(32,acctCodeWp);
					pstmtPlanHdr.setString(33,cctrCodeWp);
					pstmtPlanHdr.setString(34,saleOrder);
					pstmtPlanHdr.setString(35,lineNo);
					hdrSeq++;

					pstmtPlanHdr.setInt(36,hdrSeq);
					pstmtPlanHdr.addBatch();
				}
				//System.out.println("Site Code : [" + siteCode + "] Item Code : [" + itemCode + "] Bom Code : [" + bomCode + "] Item Ser : [" + itemSer + "]");
			}
			int rowsInsertedPlanHdr[] = pstmtPlanHdr.executeBatch();
			//commented by ted by JIten -31/10/06
			//pstmtPlanHdr.clearParameters();
			//pstmtPlanHdr.close();
			//End
			int rowsInsertedPlanDet[] = pstmtPlanDet.executeBatch();
			//Below Two lines commented by Jiten 31/10/06
			//pstmtPlanDet.clearParameters();
			//pstmtPlanDet.close();
			//End

			conn.commit();
			System.out.println("Rows Inserted in Plan Header : " + rowsInsertedPlanHdr.length);
			System.out.println("Rows Inserted in Plan Deatil : " + rowsInsertedPlanDet.length);
		}
		catch (BatchUpdateException be)
		{
			System.out.println("BatchUpdateException :RunMRPPrcEJB :InsertPlanTable():" + be.getMessage());
			System.out.println("Detail Exception : " + be.getNextException());
			be.printStackTrace();
			int[] ptr = be.getUpdateCounts();//added by jiten 15/11/06
			for(int i=0;i<ptr.length;i++){//loop added by jiten 15/11/06
				if(ptr[i] == Statement.EXECUTE_FAILED){
						System.out.println("Index ["+i+"] Error...");
						break;
				}
			}
			resultString = be.getMessage();
			throw new ITMException(be);
		}
		catch (SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :InsertPlanTable():" + se.getMessage());
			se.printStackTrace();
			resultString = se.getMessage();
			throw new ITMException(se);
		}
		catch (Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :InsertPlanTable():" + e.getMessage());
			e.printStackTrace();
			resultString = e.getMessage();
			throw new ITMException(e);
		}
		return resultString;
	}
////////////////////////


	private String setValuesInBrow() throws ITMException
	{
		String errCode = "";
		String errString = "";
		String resultString = "";
		StringBuffer retTabSepStrBuff = new StringBuffer();

		String siteCode = "0";
		String itemCode = "0";
		String bomCode = "0";
		String suppSour = "0";
		String orderOpt = "0";
		String dueDateStr = "0";
		String batchQtyType = "0";
		String unit = "0";
		String desc = null;

		double demand = 0;
		double supply = 0;
		double planDemand = 0;
		double planSupply = 0;
		double runningDemand = 0;
		double runningSupply = 0;
		double runningPlanSupply = 0;
		double independentDemand = 0;
		double minOrderQty = 0;
		double integralQty = 0;
		double maxQty = 0;
		double minQty = 0;
		double reorderQty = 0;
		double yieldPerc = 100;
		double minPlanPerc = 0;
		double batchQty = 0;
	    double purcRate = 0;
		int purLeadTime = 0;
		int qcLeadTime = 0;
		int mfgLeadTime = 0;
		boolean flag = true;
		// 12/04/10 Manoharan MF90BHU001
		double stockQty = 0;
		double othSupply = 0;
		// end 12/04/10 manoharan MF90BHU001
		ADPElement adpElement = null;
		try
		{
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			for(int adpeCount=0; adpeCount<adpeList.size(); adpeCount++)
			{
				flag = true;
				adpElement = (ADPElement)adpeList.get(adpeCount);

				siteCode = adpElement.getSiteCode();
				itemCode = adpElement.getItemCode();
				maxQty = adpElement.getMaxQty();
				minQty = adpElement.getMinQty();
				suppSour = adpElement.getSuppSour();
				orderOpt = adpElement.getOrderOpt();
				bomCode =  adpElement.getBomCode();
				unit =  adpElement.getUnit();
				batchQtyType = adpElement.getBatchQtyType();
				minOrderQty =  adpElement.getMinOrderQty();
				integralQty =  adpElement.getIntegralQty();
				reorderQty =  adpElement.getReoQty();
				yieldPerc =  adpElement.getYieldPerc();
				minPlanPerc =  adpElement.getMinPlanPerc();
				batchQty =  adpElement.getBatchQty();
				purcRate = adpElement.getPurcRate();
				qcLeadTime = adpElement.getQcLeadTime();
				purLeadTime = adpElement.getPurLeadTime();
				mfgLeadTime = adpElement.getMfgLeadTime();
				desc = adpElement.getItemDescr();

				ArrayList timeMRParr = adpElement.getTimeMrpList();
				for (int timeCtr = 0; timeCtr < timeMRParr.size() ; timeCtr++)
				{
					TimeMRP timeMrp = (TimeMRP)timeMRParr.get(timeCtr);

					demand = timeMrp.getDemand();
					supply = timeMrp.getSupply();
					planSupply = timeMrp.getPlanSupply();
					runningDemand = timeMrp.getRunningDemand();
					runningSupply = timeMrp.getRunningSupply();
					runningPlanSupply = timeMrp.getRunningPlanSupply();
					independentDemand = timeMrp.getIndependentDemand();
					dueDateStr = simpleDateFormat.format(timeMrp.getDueDate());
					// 12/04/10 manoharan MF90BHU001
					stockQty = timeMrp.getStockQty();
					othSupply = timeMrp.getOthSupply();
					// end 12/04/10 manoharan MF90BHU001

					if(flag)
					{
						retTabSepStrBuff.append(siteCode).append("\t");
						retTabSepStrBuff.append(itemCode).append("\t");
						retTabSepStrBuff.append(suppSour).append("\t");
					}
					else
					{
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append(" ").append("\t");
					}
					retTabSepStrBuff.append(decFormat(demand)).append("\t");
					retTabSepStrBuff.append(decFormat(supply)).append("\t");
					retTabSepStrBuff.append(dueDateStr).append("\t");
					retTabSepStrBuff.append(decFormat(planSupply)).append("\t");
					retTabSepStrBuff.append(decFormat(runningDemand)).append("\t");
					retTabSepStrBuff.append(decFormat(runningSupply)).append("\t");
					retTabSepStrBuff.append(decFormat(runningPlanSupply)).append("\t");
					retTabSepStrBuff.append(decFormat(independentDemand)).append("\t");
					if(flag)
					{	if (bomCode != null )
						{
							retTabSepStrBuff.append(bomCode).append("\t");
						}
						else
						{
							retTabSepStrBuff.append(" ").append("\t");
						}
						retTabSepStrBuff.append(orderOpt).append("\t");
						retTabSepStrBuff.append(batchQtyType).append("\t");
						retTabSepStrBuff.append(unit).append("\t");
						retTabSepStrBuff.append(minOrderQty).append("\t");
						retTabSepStrBuff.append(integralQty).append("\t");
						retTabSepStrBuff.append(maxQty).append("\t");
						retTabSepStrBuff.append(minQty).append("\t");
						retTabSepStrBuff.append(reorderQty).append("\t");
						retTabSepStrBuff.append(yieldPerc).append("\t");
						retTabSepStrBuff.append(minPlanPerc).append("\t");
						retTabSepStrBuff.append(batchQty).append("\t");
						retTabSepStrBuff.append(purcRate).append("\t");
						retTabSepStrBuff.append(purLeadTime).append("\t");
						retTabSepStrBuff.append(qcLeadTime).append("\t");
						retTabSepStrBuff.append(mfgLeadTime).append("\t");
						retTabSepStrBuff.append(desc).append("\t");
						// 12/04/10 manoharan MF90BHU001
						retTabSepStrBuff.append(decFormat(stockQty)).append("\t");
						retTabSepStrBuff.append(decFormat(othSupply)).append("\n");
						// end end 12/04/10 manoharan MF90BHU001
					}
					else
					{
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append("0").append("\t");
						retTabSepStrBuff.append(desc).append("\t");
						// 12/04/10 manoharan MF90BHU001
						retTabSepStrBuff.append(decFormat(stockQty)).append("\t");
						retTabSepStrBuff.append(decFormat(othSupply)).append("\n");
						// end end 12/04/10 manoharan MF90BHU001
					}
					flag = false;
				}
			}
			//System.out.println("End of collected datas......");
			resultString = retTabSepStrBuff.toString();
		}
		catch (SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :setValuesInBrow():" + se.getMessage() + ":");
			se.printStackTrace();
			throw new ITMException(se);
		}
		catch (Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :setValuesInBrow():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
				retTabSepStrBuff = null;
			}
			catch(Exception e)
			{}
		}
		return resultString;
	}
	public double getRequiredDecimal(double actVal, int prec)
	{
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(3);
		String strValue = numberFormat.format(DoubleValue);
		strValue = strValue.replaceAll(",","");
		double reqVal = Double.parseDouble(strValue);
		return reqVal;
	}

	private void Write2XSL() throws ITMException
	{
		String errCode = "", date;
		String errString = "";
		String resultString = "";
		StringBuffer retTabSepStrBuff = new StringBuffer();

		String siteCode = "0";
		String itemCode = "0";
		String bomCode = "0";
		String suppSour = "0";
		String orderOpt = "0";
		String dueDateStr = "0";
		String batchQtyType = "0";
		String unit = "0";
		String desc = null;

		double demand = 0;
		double supply = 0;
		double planDemand = 0;
		double planSupply = 0;
		double runningDemand = 0;
		double runningSupply = 0;
		double runningPlanSupply = 0;
		double independentDemand = 0;
		double minOrderQty = 0;
		double integralQty = 0;
		double maxQty = 0;
		double minQty = 0;
		double reorderQty = 0;
		double yieldPerc = 100;
		double minPlanPerc = 0;
		double batchQty = 0;
	    double purcRate = 0;
		int purLeadTime = 0;
		int qcLeadTime = 0;
		int mfgLeadTime = 0;
		boolean flag = true;
		// 12/04/10 Manoharan MF90BHU001
		double stockQty = 0;
		double othSupply = 0;
		// end 12/04/10 manoharan MF90BHU001
		ADPElement adpElement = null;
		java.sql.Timestamp dueDate;
		HSSFCell cell = null;
		HSSFRow row = null;
		HSSFCellStyle cellStyle = null;
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

		final String[] titles = {
		"Site Code",	"Item Code", "Item Description", "Supply Source", "Demand", "Supply",
		"Stock Qty", "Other Supply", "Due Date",
		"Plan Supply", "Running Demand", "Running Supply", "Running Plan Supply", "Independent Demand",
		"Bom Code","Order Opt","Batch Qty Type","Unit","Min Order Qty","Integral Qty",
		"Max Qty","Min Qty","Reorder Qty","Yield Perc", "Min Plan %","Batch Qty",
		"Purc Rate", "Pur Lead Time","QC Lead Time", "MFG Lead Time"
		};
			// 30
		try
		{

			HSSFWorkbook wb;
			wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("MRPRun");
	        HSSFRow titleRow = sheet.createRow(0);
			titleRow.setHeightInPoints(45);
			cellStyle = wb.createCellStyle();

			HSSFCellStyle style = wb.createCellStyle();
			style.setBorderTop((short) 6); // double lines border
			style.setBorderBottom((short) 1); // single line border
			style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
			HSSFFont font = wb.createFont();
			font.setFontName(HSSFFont.FONT_ARIAL);
			font.setFontHeightInPoints((short) 20);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			font.setColor(HSSFColor.BLUE.index);
			style.setFont(font);



			HSSFCell titleCell = titleRow.createCell((short) 0);
			titleCell.setCellValue(new HSSFRichTextString("MRP - Material Requirements"));
			titleCell.setCellStyle(style);
			sheet.autoSizeColumn((short) 1);


			//titleCell.setCellStyle(styles.get("title"));

			HSSFRow headerRow = sheet.createRow(1);
			headerRow.setHeightInPoints(40);
			HSSFCell headerCell;

			for (int cellCtr = 0; cellCtr < titles.length; cellCtr++)
			{
				headerCell = headerRow.createCell((short)cellCtr);
				headerCell.setCellValue(titles[cellCtr]);
			}

			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			int insertRow = 2;
			for(int adpeCount=0; adpeCount<adpeList.size(); adpeCount++)
			{


				flag = true;
				adpElement = (ADPElement)adpeList.get(adpeCount);

				siteCode = adpElement.getSiteCode();
				itemCode = adpElement.getItemCode();
				maxQty = adpElement.getMaxQty();
				minQty = adpElement.getMinQty();
				suppSour = adpElement.getSuppSour();
				orderOpt = adpElement.getOrderOpt();
				bomCode =  adpElement.getBomCode();
				unit =  adpElement.getUnit();
				batchQtyType = adpElement.getBatchQtyType();
				minOrderQty =  adpElement.getMinOrderQty();
				integralQty =  adpElement.getIntegralQty();
				reorderQty =  adpElement.getReoQty();
				yieldPerc =  adpElement.getYieldPerc();
				minPlanPerc =  adpElement.getMinPlanPerc();
				batchQty =  adpElement.getBatchQty();
				purcRate = adpElement.getPurcRate();
				qcLeadTime = adpElement.getQcLeadTime();
				purLeadTime = adpElement.getPurLeadTime();
				mfgLeadTime = adpElement.getMfgLeadTime();
				desc = adpElement.getItemDescr();

				ArrayList timeMRParr = adpElement.getTimeMrpList();
				for (int timeCtr = 0; timeCtr < timeMRParr.size() ; timeCtr++)
				{
					TimeMRP timeMrp = (TimeMRP)timeMRParr.get(timeCtr);
					dueDate = timeMrp.getDueDate();
					demand = timeMrp.getDemand();
					supply = timeMrp.getSupply();
					planSupply = timeMrp.getPlanSupply();
					runningDemand = timeMrp.getRunningDemand();
					runningSupply = timeMrp.getRunningSupply();
					runningPlanSupply = timeMrp.getRunningPlanSupply();
					independentDemand = timeMrp.getIndependentDemand();
					dueDateStr = simpleDateFormat.format(timeMrp.getDueDate());
					// 12/04/10 manoharan MF90BHU001
					stockQty = timeMrp.getStockQty();
					othSupply = timeMrp.getOthSupply();
					// end 12/04/10 manoharan MF90BHU001
					insertRow++;
					row = sheet.createRow(insertRow);
					for (int j = 0; j < titles.length; j++)
					{
						cell = row.createCell((short)j);
					}

					if(flag)
					{
						row.getCell(0).setCellValue(siteCode);
						row.getCell(1).setCellValue(itemCode);
						row.getCell(2).setCellValue(desc);
						row.getCell(3).setCellValue(suppSour);
					}

					row.getCell(4).setCellValue(demand);
					row.getCell(5).setCellValue(supply);
					row.getCell(6).setCellValue(stockQty);
					row.getCell(7).setCellValue(othSupply);

					cell = row.getCell(8);
					cell.setCellValue(HSSFDateUtil.getExcelDate(dueDate));
					cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
					cell.setCellStyle(cellStyle);

					row.getCell(9).setCellValue(planSupply);
					row.getCell(10).setCellValue(runningDemand);
					row.getCell(11).setCellValue(runningSupply);
					row.getCell(12).setCellValue(runningPlanSupply);
					row.getCell(13).setCellValue(independentDemand);

					if(flag)
					{
						row.getCell(14).setCellValue(bomCode);
						row.getCell(15).setCellValue(orderOpt);
						row.getCell(16).setCellValue(batchQtyType);
						row.getCell(17).setCellValue(unit);
						row.getCell(18).setCellValue(minOrderQty);
						row.getCell(19).setCellValue(integralQty);
						row.getCell(20).setCellValue(maxQty);
						row.getCell(21).setCellValue(minQty);
						row.getCell(22).setCellValue(reorderQty);
						row.getCell(23).setCellValue(yieldPerc);
						row.getCell(24).setCellValue(minPlanPerc);
						row.getCell(25).setCellValue(batchQty);
						row.getCell(26).setCellValue(purcRate);
						row.getCell(27).setCellValue(purLeadTime);
						row.getCell(28).setCellValue(qcLeadTime);
						row.getCell(29).setCellValue(mfgLeadTime);
					}

					//flag = false; // 25/02/11 manoharan commented as BTPL want the same to be repeated in the excel file
				}
			}

			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document dom = db.parse( "/IBASEHOME/ibase.xml" );
			String mrpOutputLocation = "";
			try
			{
				mrpOutputLocation = dom.getElementsByTagName( "mrp_output_location" ).item(0).getFirstChild().getNodeValue();
			}
			catch (Exception e)
			{
				System.out.println("Exception :RunMRPPrcEJB :setValuesInBrow():" + e.getMessage() + ":");
				e.printStackTrace();
				throw new ITMException(e);
			}
			Calendar cal = Calendar.getInstance();
			String curTime = ( new java.text.DecimalFormat( "0000" ) ).format( cal.get(Calendar.YEAR) )
				+ ( new java.text.DecimalFormat( "00" ) ).format( cal.get(Calendar.MONTH) + 1 )
				+ ( new java.text.DecimalFormat( "00" ) ).format( cal.get(Calendar.DAY_OF_MONTH) )
				+ ( new java.text.DecimalFormat( "00" ) ).format( cal.get(Calendar.HOUR_OF_DAY) )
				+ ( new java.text.DecimalFormat( "00" ) ).format( cal.get(Calendar.MINUTE) ) ;


			String fileName = mrpOutputLocation + File.separator+"MRPRun_" + curTime + ".xls";
			dom = null;
			db = null;
			dbf = null;
			File outputDir = new File( fileName );
			File subDirFile = null;

			if( !outputDir.exists() )
			{
				String subDirName = "";

				if (mrpOutputLocation.indexOf("\\") > 0)
				{
					String subDirs[] = mrpOutputLocation.split( "\\\\" );
					subDirName = subDirs[0];
				   System.out.println("windows stype path");
					for( int idx = 1; idx < subDirs.length; idx++ )
					{
						subDirName = subDirName + File.separator + subDirs[ idx ];
						System.out.println("subDirs[ idx ] [" + subDirs[ idx ] + "]");
					}
				}
				else
				{
					String subDirs[] = mrpOutputLocation.split( "/" );
					subDirName = subDirs[0];
					System.out.println("Unix style path");
					for( int idx = 1; idx < subDirs.length; idx++ )
					{
						subDirName = subDirName + File.separator + subDirs[ idx ];
						System.out.println("subDirs[ idx ] [" + subDirs[ idx ] + "]");
					}

				}
				subDirFile = new File( subDirName );
				System.out.println("File path [" + subDirName + "]");
				if( !subDirFile.exists() )
				{
					System.out.println("creating folders [" + subDirName + "]");
					subDirFile.mkdirs();
				}
			}


			// Write the output to a file
			//if(wb instanceof XSSFWorkbook) file += "x";
			FileOutputStream out = new FileOutputStream(fileName);
			wb.write(out);
			out.close();

			//System.out.println("End of collected datas......");
			resultString = retTabSepStrBuff.toString();
		}
		catch (SQLException se)
		{
			System.out.println("SQLException :RunMRPPrcEJB :setValuesInBrow():" + se.getMessage() + ":");
			se.printStackTrace();
			throw new ITMException(se);
		}
		catch (Exception e)
		{
			System.out.println("Exception :RunMRPPrcEJB :setValuesInBrow():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				conn.close();
				conn = null;
				retTabSepStrBuff = null;
			}
			catch(Exception e)
			{}
		}

	}

    /**
     * Create a library of cell styles
     */
   /* private static Map<String, HSSFCellStyle> createStyles(Workbook wb){
        Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
        HSSFCellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)18);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);

        Font monthFont = wb.createFont();
        monthFont.setFontHeightInPoints((short)11);
        monthFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFont(monthFont);
        style.setWrapText(true);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);

        style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula", style);

        style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula_2", style);

        return styles;
    }

	public double getRequiredDecimal(double actVal, int prec)
	{
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(3);
		String strValue = numberFormat.format(DoubleValue);
		strValue = strValue.replaceAll(",","");
		double reqVal = Double.parseDouble(strValue);
		return reqVal;
	}*/


}

//Added by Jiten 07/11/06
class CyclicItem
{
	public String siteCode;
	public String bomCode;
	public String itemCode;
	public String cylicItemCode;
}

//End
	/*
	private String InsertPlanDemand(String winName, java.util.ArrayList list)
	{
		String xmlValues = "";
		String errString  = "";
		try
		{
		   InitialContext ctx = null;

		   AppConnectParm appConnect = new AppConnectParm();
		   ctx = new InitialContext(appConnect.getProperty());
		   MasterStatefulHome masterStatefulHome = (MasterStatefulHome)ctx.lookup("MasterStateful");
		   MasterStateful masterStateful = masterStatefulHome.create();
		   String [] authencate = new String[2];
		   authencate[0] = userId;
		   authencate[1] = userId;

			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yy");
			if(winName.equalsIgnoreCase("W_MPS_ORDER"))
			{
				System.out.println("W_MPS_ORDER");
				String tranId = "";
				String ordType = "";
				String itemCode = "";
				String siteCode = "";
				String mrpRunId = "";
				String addnOrder = "";
				String status = "";
				String routeCode = "";
				String bomCode = "";
				String projCode = "";
				//String chgUser = "";
				//String chgTerm = "";
				String unit = "";
				String acctCodeWp = "";
				String cctrCodeWp = "";

				double quantity = 0;
				double yieldPerc = 0 ;
				double curBatchQty = 0;

				String ordDate = "";
				String planDueDate = "";
				String dueDate = "";
				String statusDate = "";
				String chgDate = "";

			//	java.sql.Timestamp ordDate = null;
			//	java.sql.Timestamp planDueDate = null;
			//	java.sql.Timestamp dueDate = null;
			//	java.sql.Timestamp statusDate = null;
			//	java.sql.Timestamp chgDate = null;

				MPSOrder mpsOrder = null;
				for(int count = 0; count<list.size(); count++)
				{

					mpsOrder = (MPSOrder)list.get(count);

					ordType = mpsOrder.ordType;
					itemCode = mpsOrder.itemCode;
					siteCode = mpsOrder.siteCode;
					quantity = mpsOrder.quantity;
					ordDate = sdf.format(mpsOrder.ordDate);
					//mpsOrder.ordDate;
					planDueDate = sdf.format(mpsOrder.planDueDate);
					//mpsOrder.planDueDate;
					mrpRunId = mpsOrder.mrpRunId;
					addnOrder = mpsOrder.addnOrder;
					dueDate = sdf.format(mpsOrder.dueDate);
					//mpsOrder.dueDate;
					status = mpsOrder.status;
					statusDate = sdf.format(mpsOrder.statusDate);
					//mpsOrder.statusDate;
					yieldPerc = mpsOrder.yieldPerc;
					routeCode = mpsOrder.routeCode;
					bomCode = mpsOrder.bomCode;
					projCode = mpsOrder.projCode;
					chgDate = sdf.format(mpsOrder.chgDate);
					//mpsOrder.chgDate;
				//	chgUser = mpsOrder.chgUser;
				//	chgTerm = mpsOrder.chgTerm;
					unit = mpsOrder.unit;
					acctCodeWp = mpsOrder.acctCodeWp;
					cctrCodeWp = mpsOrder.cctrCodeWp;
					curBatchQty = mpsOrder.curBatchQty;
					xmlValues = "<?xml version='1.0' encoding='UTF-8'?>\n" + "<Root>\n";
					xmlValues = xmlValues + "<DocumentRoot>" + "<description>" + "Datawindow Root" + "</description>";
					xmlValues = xmlValues + "<group0>" + "<description>" + "Group0 description" + "</description>";
					xmlValues = xmlValues + "<Header0>";
					xmlValues = xmlValues + "<objName><![CDATA[mps_order]]></objName>";
					xmlValues = xmlValues + "<pageContext><![CDATA[1]]></pageContext>";
					xmlValues = xmlValues + "<objContext><![CDATA[1]]></objContext>";
					xmlValues = xmlValues + "<editFlag><![CDATA[A]]></editFlag>";
					xmlValues = xmlValues + "<focusedColumn><![CDATA[]]></focusedColumn>";
					xmlValues = xmlValues + "<action><![CDATA[SAVE]]></action>";
					xmlValues = xmlValues + "<elementName><![CDATA[]]></elementName>";
					xmlValues = xmlValues + "<keyValue><![CDATA[1]]></keyValue>";
					xmlValues = xmlValues + "<taxKeyValue><![CDATA[]]></taxKeyValue>";
					xmlValues = xmlValues + "<saveLevel><![CDATA[1]]></saveLevel>";
					xmlValues = xmlValues + "<forcedSave><![CDATA[true]]></forcedSave>";
					xmlValues = xmlValues + "<taxInFocus><![CDATA[false]]></taxInFocus>";
					xmlValues = xmlValues + "<description>Header0 members</description>";


					xmlValues = xmlValues + "<Detail1 dbID=\"\" domID=\"1\" objName=\"mps_order\" objContext=\"1\" >";
					xmlValues = xmlValues + "<attribute pkNames=\"\" status=\"N\" updateFlag=\"A\" selected=\"N\" />";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<order_type><![CDATA[" + ordType + "]]></order_type>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<ord_date><![CDATA[" + ordDate + "]]></ord_date>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDate + "]]></plan_due_date>";
					xmlValues = xmlValues + "<mrp_run_id><![CDATA[" + mrpRunId + "]]></mrp_run_id>";
					xmlValues = xmlValues + "<addn_order><![CDATA[" + addnOrder + "]]></addn_order>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDate + "]]></due_date>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<status_date><![CDATA[" + statusDate + "]]></status_date>";
					xmlValues = xmlValues + "<yield_perc><![CDATA[" + yieldPerc + "]]></yield_perc>";
					xmlValues = xmlValues + "<route_code><![CDATA[" + routeCode + "]]></route_code>";
					xmlValues = xmlValues + "<bom_code><![CDATA[" + bomCode + "]]></bom_code>";
					xmlValues = xmlValues + "<proj_code><![CDATA[" + projCode + "]]></proj_code>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDate + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<acct_code__wp><![CDATA[" + acctCodeWp + "]]></acct_code__wp>";
					xmlValues = xmlValues + "<cctr_code__wp><![CDATA[" + cctrCodeWp + "]]></cctr_code__wp>";
					xmlValues = xmlValues + "<current_batch_qty><![CDATA[" + curBatchQty + "]]></current_batch_qty>";

					xmlValues = xmlValues + "</Detail1>";
					xmlValues = xmlValues + "</Header0>";
					xmlValues = xmlValues + "</group0>";
					xmlValues = xmlValues + "</DocumentRoot>";
					xmlValues = xmlValues + "</Root>\r\n";
		   			errString = masterStateful.processRequest(authencate, logInSite, true, xmlValues);
					//errString = saveData(xmlValues);

				}
			}
			else if(winName.equalsIgnoreCase("W_DIST_DEMAND"))
			{
				String tranId = "";
				String siteCode = "";
				String itemCode = "";
				String unit = "";
				String siteCodeSrc = "";
				final String status = "T";
				//String chgUser = "";
		//		String chgTerm = "";

				double qtyRequired = 0;

				String tranDate = "";
				String planDueDate = "";
				String dueDateOrd = "";
				String dueDate = "";
				String chgDate = "";

			//	java.sql.Timestamp tranDate = null;
			//	java.sql.Timestamp planDueDate = null;
			//	java.sql.Timestamp dueDateOrd = null;
			//	java.sql.Timestamp dueDate = null;
			//	java.sql.Timestamp chgDate = null;

				DistDemand distDemand = new DistDemand();
				for(int count = 0; count<list.size(); count++)
				{
					distDemand = (DistDemand)list.get(count);

					tranDate = sdf.format(distDemand.tranDate);
					siteCode = distDemand.siteCode;
					itemCode = distDemand.itemCode;
					planDueDate = sdf.format(distDemand.planDueDate);
					//distDemand.planDueDate;
					dueDate = sdf.format(distDemand.dueDate);
					//distDemand.dueDate;
					qtyRequired = distDemand.qtyRequired;
					unit = distDemand.unit;
					siteCodeSrc = distDemand.siteCodeSrc;
					dueDateOrd = sdf.format(distDemand.dueDateOrd);
					chgDate = sdf.format(distDemand.chgDate);
					//distDemand.chgDate;
					//chgUser = distDemand.chgUser;
					//chgTerm = distDemand.chgTerm;
					xmlValues = "<?xml version='1.0' encoding='UTF-8'?>\n" + "<Root>\n";
					xmlValues = xmlValues + "<DocumentRoot>" + "<description>" + "Datawindow Root" + "</description>";
					xmlValues = xmlValues + "<group0>" + "<description>" + "Group0 description" + "</description>";
					xmlValues = xmlValues + "<Header0>";
					xmlValues = xmlValues + "<objName><![CDATA[dist_demand]]></objName>";
					xmlValues = xmlValues + "<pageContext><![CDATA[1]]></pageContext>";
					xmlValues = xmlValues + "<objContext><![CDATA[1]]></objContext>";
					xmlValues = xmlValues + "<editFlag><![CDATA[A]]></editFlag>";
					xmlValues = xmlValues + "<focusedColumn><![CDATA[]]></focusedColumn>";
					xmlValues = xmlValues + "<action><![CDATA[SAVE]]></action>";
					xmlValues = xmlValues + "<elementName><![CDATA[]]></elementName>";
					xmlValues = xmlValues + "<keyValue><![CDATA[1]]></keyValue>";
					xmlValues = xmlValues + "<taxKeyValue><![CDATA[]]></taxKeyValue>";
					xmlValues = xmlValues + "<saveLevel><![CDATA[1]]></saveLevel>";
					xmlValues = xmlValues + "<forcedSave><![CDATA[true]]></forcedSave>";
					xmlValues = xmlValues + "<taxInFocus><![CDATA[false]]></taxInFocus>";
					xmlValues = xmlValues + "<description>Header0 members</description>";


					xmlValues = xmlValues + "<Detail1 dbID=\"\" domID=\"1\" objName=\"dist_demand\" objContext=\"1\" >";
					xmlValues = xmlValues + "<attribute pkNames=\"\" status=\"N\" updateFlag=\"A\" selected=\"N\" />";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<tran_date><![CDATA[" + tranDate + "]]></tran_date>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDate + "]]></plan_due_date>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDate + "]]></due_date>";
					xmlValues = xmlValues + "<qty_required><![CDATA[" + qtyRequired + "]]></qty_required>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<site_code__source><![CDATA[" + siteCodeSrc + "]]></site_code__source>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<due_date__ord><![CDATA[" + dueDateOrd + "]]></due_date__ord>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDate + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";

					xmlValues = xmlValues + "</Detail1>";
					xmlValues = xmlValues + "</Header0>";
					xmlValues = xmlValues + "</group0>";
					xmlValues = xmlValues + "</DocumentRoot>";
					xmlValues = xmlValues + "</Root>\r\n";

					//errString = saveData(xmlValues);
					errString = masterStateful.processRequest(authencate, logInSite, true, xmlValues);
				}
			}

			else if(winName.equalsIgnoreCase("W_INDENT"))
			{
				String indNo = "";
				final String indType = "M";
			    String deptCode = "";
				String itemCode = "";
				String itemDescr= "";
				String unitInd = "";
				String unitStd = "";
				String siteCode = "";
				String siteCodeDlv = "";
				String siteCodeAcct = "";
				String siteCodeCust = "";
				String aprCode = "";
				final String priority = "R";
				String acctCode = "";
				String cctrCode = "";
				String empCodePur = "";
				String empCodeReq = "";
				String suppCodePref = "";
				String packCode = "";
				final String status = "T";
				//String chgUser = "";
		//		String chgTerm = "";

				double purLeadTime = 0 ;
				double quantity = 0;
				double quantityStdUom = 0;
				double ordQuantity = 0;
				double purcRate = 0;
				double convQtyStdUom = 0;

				String indDate = "";
				String planDueDate = "";
				String reqDate = "";
				String chgDate = "";
			//
			//	java.sql.Timestamp indDate = null;
			//	java.sql.Timestamp planDate = null;
			//	java.sql.Timestamp reqDate = null;
			//	java.sql.Timestamp chgDate = null;


				Indent indent = new Indent();
				for(int count = 0; count<list.size(); count++)
				{
					indent = (Indent)list.get(count);

					//indent.tranDate;
					indDate = sdf.format(indent.indDate);
					//indent.indDate;
					deptCode = indent.indType;
					planDueDate = sdf.format(indent.planDueDate);
					//indent.planDueDate;
					reqDate = sdf.format(indent.reqDate);
					//indent.reqDate;
					itemCode = indent.itemCode;
					itemDescr = indent.itemDescr;
					quantity = indent.quantity;
					quantityStdUom = indent.quantityStdUom;
					unitInd = indent.unitInd;
					unitStd = indent.unitStd;
					siteCode = indent.siteCode;
					siteCodeDlv = indent.siteCodeDlv;
					siteCodeAcct = indent.siteCodeAcct;
					aprCode = indent.aprCode;
					acctCode = indent.acctCode;
					cctrCode = indent.cctrCode;
					empCodePur = indent.empCodePur;
					empCodeReq = indent.empCodeReq;
					suppCodePref = indent.suppCodePref;
					purcRate = indent.purcRate;
					packCode = indent.packCode;
					chgDate = sdf.format(indent.chgDate);
					//chgUser = indent.chgUser;
					//chgTerm = indent.chgTerm;
					purLeadTime = indent.purLeadTime;
					convQtyStdUom = indent.convQtyStdUom;
					planDueDate = sdf.format(indent.planDueDate);
					xmlValues = "<?xml version='1.0' encoding='UTF-8'?>\n" + "<Root>\n";
					xmlValues = xmlValues + "<DocumentRoot>" + "<description>" + "Datawindow Root" + "</description>";
					xmlValues = xmlValues + "<group0>" + "<description>" + "Group0 description" + "</description>";
					xmlValues = xmlValues + "<Header0>";
					xmlValues = xmlValues + "<objName><![CDATA[indent]]></objName>";
					xmlValues = xmlValues + "<pageContext><![CDATA[1]]></pageContext>";
					xmlValues = xmlValues + "<objContext><![CDATA[1]]></objContext>";
					xmlValues = xmlValues + "<editFlag><![CDATA[A]]></editFlag>";
					xmlValues = xmlValues + "<focusedColumn><![CDATA[]]></focusedColumn>";
					xmlValues = xmlValues + "<action><![CDATA[SAVE]]></action>";
					xmlValues = xmlValues + "<elementName><![CDATA[]]></elementName>";
					xmlValues = xmlValues + "<keyValue><![CDATA[1]]></keyValue>";
					xmlValues = xmlValues + "<taxKeyValue><![CDATA[]]></taxKeyValue>";
					xmlValues = xmlValues + "<saveLevel><![CDATA[1]]></saveLevel>";
					xmlValues = xmlValues + "<forcedSave><![CDATA[true]]></forcedSave>";
					xmlValues = xmlValues + "<taxInFocus><![CDATA[false]]></taxInFocus>";
					xmlValues = xmlValues + "<description>Header0 members</description>";

					xmlValues = xmlValues + "<Detail1 dbID=\"\" domID=\"1\" objName=\"indent\" objContext=\"1\" >";
					xmlValues = xmlValues + "<attribute pkNames=\"\" status=\"N\" updateFlag=\"A\" selected=\"N\" />";
					xmlValues = xmlValues + "<ind_no><![CDATA[]]></ind_no>";
					xmlValues = xmlValues + "<ind_date><![CDATA[" + indDate + "]]></ind_date>";
					xmlValues = xmlValues + "<ind_type><![CDATA[" + indType + "]]></ind_type>";
					xmlValues = xmlValues + "<dept_code><![CDATA[" + deptCode + "]]></dept_code>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDate + "]]></plan_due_date>";
					xmlValues = xmlValues + "<req_date><![CDATA[" + reqDate + "]]></req_date>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<item_desc><![CDATA[" + itemDescr + "]]></item_desc>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<quantity__stduom><![CDATA[" + quantityStdUom + "]]></quantity__stduom>";
					xmlValues = xmlValues + "<unit__ind><![CDATA[" + unitInd + "]]></unit__ind>";
					xmlValues = xmlValues + "<unit__std><![CDATA[" + unitStd + "]]></unit__std>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<site_code__dlv><![CDATA[" + siteCodeDlv + "]]></site_code__dlv>";
					xmlValues = xmlValues + "<site_code__acct><![CDATA[" + siteCodeAcct + "]]></site_code__acct>";
					xmlValues = xmlValues + "<apr_code><![CDATA[" + aprCode + "]]></apr_code>";
					xmlValues = xmlValues + "<priority><![CDATA[" + priority + "]]></priority>";
					xmlValues = xmlValues + "<acct_code><![CDATA[" + acctCode + "]]></acct_code>";
					xmlValues = xmlValues + "<cctr_code><![CDATA[" + cctrCode + "]]></cctr_code>";
					xmlValues = xmlValues + "<emp_code__pur><![CDATA[" + empCodePur + "]]></emp_code__pur>";
					xmlValues = xmlValues + "<emp_code__req><![CDATA[" + empCodeReq + "]]></emp_code__req>";
					xmlValues = xmlValues + "<supp_code__pref><![CDATA[" + suppCodePref + "]]></supp_code__pref>";
					xmlValues = xmlValues + "<purc_rate><![CDATA[" + purcRate + "]]></purc_rate>";
					xmlValues = xmlValues + "<pack_code><![CDATA[" + packCode + "]]></pack_code>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<chg_date><![CDATA[" + chgDate + "]]></chg_date>";
					xmlValues = xmlValues + "<chg_user><![CDATA[" + chgUser + "]]></chg_user>";
					xmlValues = xmlValues + "<chg_term><![CDATA[" + chgTerm + "]]></chg_term>";
					xmlValues = xmlValues + "<pur_lead_time><![CDATA[" + purLeadTime + "]]></pur_lead_time>";
					xmlValues = xmlValues + "<conv__qty_stdoum><![CDATA[" + convQtyStdUom + "]]></conv__qty_stdoum>";

					xmlValues = xmlValues + "</Detail1>";
					xmlValues = xmlValues + "</Header0>";
					xmlValues = xmlValues + "</group0>";
					xmlValues = xmlValues + "</DocumentRoot>";
					xmlValues = xmlValues + "</Root>\r\n";
FileOutputStream fout = null;
BufferedWriter bw = null;
fout = new FileOutputStream("XML.xml");
bw = new BufferedWriter(new OutputStreamWriter(fout));
bw.write(xmlValues);
bw.close();
fout.close();

					//errString = saveData(xmlValues);
					errString = masterStateful.processRequest(authencate, logInSite, true, xmlValues);
				}

			}

			else if(winName.equalsIgnoreCase("W_INDEPENDENT_DEMAND"))
			{
				String tranId = "";
				String siteCode = "";
				String siteCodeCust = "";
				String itemCode = "";
				String unit = "";
				String unitStd = "";
				String status = "T";

				double  quantity = 0;
				double  quantityStdUom = 0;
				double  convQtyStdUom = 1;

				String tranDate = "";
				String planDueDate = "";
				String dueDate = "";

			//	java.sql.Timestamp tranDate = null;
			//	java.sql.Timestamp planDueDate = null;
			//	java.sql.Timestamp dueDate = null;


				IndependentDemand independentDemand = new IndependentDemand();
				for(int count = 0; count<list.size(); count++)
				{
					independentDemand = (IndependentDemand)list.get(count);

					siteCode = independentDemand.siteCode;
					siteCodeCust = independentDemand.siteCodeCust;
					itemCode = independentDemand.itemCode;
					unit = independentDemand.unit;
					unitStd = independentDemand.unitStd;
					quantity = independentDemand.quantity;
					quantityStdUom = independentDemand.quantityStdUom;
					convQtyStdUom = independentDemand.convQtyStdUom;
					tranDate = sdf.format(independentDemand.tranDate);
					//independentDemandTranDate;
					planDueDate = sdf.format(independentDemand.planDueDate);
					//independentDemandPlanDueDate;
					dueDate = sdf.format(independentDemand.dueDate);
					//independentDemandDueDate;
					xmlValues = "<?xml version='1.0' encoding='UTF-8'?>\n" + "<Root>\n";
					xmlValues = xmlValues + "<DocumentRoot>" + "<description>" + "Datawindow Root" + "</description>";
					xmlValues = xmlValues + "<group0>" + "<description>" + "Group0 description" + "</description>";
					xmlValues = xmlValues + "<Header0>";
					xmlValues = xmlValues + "<objName><![CDATA[independent_demand]]></objName>";
					xmlValues = xmlValues + "<pageContext><![CDATA[1]]></pageContext>";
					xmlValues = xmlValues + "<objContext><![CDATA[1]]></objContext>";
					xmlValues = xmlValues + "<editFlag><![CDATA[A]]></editFlag>";
					xmlValues = xmlValues + "<focusedColumn><![CDATA[]]></focusedColumn>";
					xmlValues = xmlValues + "<action><![CDATA[SAVE]]></action>";
					xmlValues = xmlValues + "<elementName><![CDATA[]]></elementName>";
					xmlValues = xmlValues + "<keyValue><![CDATA[1]]></keyValue>";
					xmlValues = xmlValues + "<taxKeyValue><![CDATA[]]></taxKeyValue>";
					xmlValues = xmlValues + "<saveLevel><![CDATA[1]]></saveLevel>";
					xmlValues = xmlValues + "<forcedSave><![CDATA[true]]></forcedSave>";
					xmlValues = xmlValues + "<taxInFocus><![CDATA[false]]></taxInFocus>";
					xmlValues = xmlValues + "<description>Header0 members</description>";

					xmlValues = xmlValues + "<Detail1 dbID=\"\" domID=\"1\" objName=\"independent_demand\" objContext=\"1\" >";
					xmlValues = xmlValues + "<attribute pkNames=\"\" status=\"N\" updateFlag=\"A\" selected=\"N\" />";
					xmlValues = xmlValues + "<tran_id><![CDATA[]]></tran_id>";
					xmlValues = xmlValues + "<site_code><![CDATA[" + siteCode + "]]></site_code>";
					xmlValues = xmlValues + "<site_code__cust><![CDATA[" + siteCodeCust + "]]></site_code__cust>";
					xmlValues = xmlValues + "<item_code><![CDATA[" + itemCode + "]]></item_code>";
					xmlValues = xmlValues + "<unit><![CDATA[" + unit + "]]></unit>";
					xmlValues = xmlValues + "<unit__std><![CDATA[" + unitStd + "]]></unit__std>";
					xmlValues = xmlValues + "<status><![CDATA[" + status + "]]></status>";
					xmlValues = xmlValues + "<quantity><![CDATA[" + quantity + "]]></quantity>";
					xmlValues = xmlValues + "<quantity__std_uom><![CDATA[" + quantityStdUom + "]]></quantity__std_uom>";
					xmlValues = xmlValues + "<conv__qty_std_oum><![CDATA[" + convQtyStdUom + "]]></conv__qty_std_oum>";
					xmlValues = xmlValues + "<tran_date><![CDATA[" + tranDate + "]]></tran_date>";
					xmlValues = xmlValues + "<plan_due_date><![CDATA[" + planDueDate + "]]></plan_due_date>";
					xmlValues = xmlValues + "<due_date><![CDATA[" + dueDate + "]]></due_date>";

					xmlValues = xmlValues + "</Detail1>";

					xmlValues = xmlValues + "</Header0>";
					xmlValues = xmlValues + "</group0>";
					xmlValues = xmlValues + "</DocumentRoot>";
					xmlValues = xmlValues + "</Root>\r\n";

					//errString = saveData(xmlValues);
					errString = masterStateful.processRequest(authencate, logInSite, true, xmlValues);
				}

			}
			masterStateful = null;
		//	xmlValues = xmlValues + "</Header>";
		//	xmlValues = xmlValues + "</Root>";




		}
		catch(Exception e)
		{
			System.out.println("Exception [InsertPlanDemand] : " + e);
			e.printStackTrace();
			errString = e.getMessage();
		}
		return errString;
	}*/