package ibase.webitm.ejb.mfg;
// BD0783 replaced with BD0783 -- 14/01/09
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

@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();

	String logInSite = "";
	String chgUser = "";
	String chgTerm = "";


	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.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.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 + ")";
					}
				}

				/* 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;
				Statement 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();
		}
		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{
				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 || madeToOrder.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 <=? ";
			}
			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 <= ? ";

			}

		//	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())
				{
					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++)
				{
					suppSour = getSuppSour((String)siteMapEntry.getKey(),(String)itemArr.get(ctr));
					suppSour = suppSour.trim();
					bomCode = getBomCode((String)siteMapEntry.getKey(),(String)itemArr.get(ctr));
					//System.out.println("populateAllItem(): Supp_Sour:..." + suppSour + "   Bom Code:..." + bomCode);
					if( ( (suppSour != null) && (!suppSour.equals("P")) ) && (bomCode != null) )
					{
						itemHashMap.put( (String)itemArr.get(ctr), new Integer(count) );
						//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

						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;
						}
					}
				}
			}
		}
		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;
		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

			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();
			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("bomExplode Function :: Site Code :: "+siteCode+"Bom Code ::"+bomCode+"Item Code ::"+rs.getString(1));
					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null) )
					{
						if(itemHashMap.containsKey(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));
								//return "";
							}
							else{
								System.out.println("Cyclic Item Code is : " + rs.getString(1));
								errString = itmDBAccessEJB.getErrorString("","VTMRPITEME","","",conn);
								rs.close();
								pstmt.close();
								return errString;
							}
							//End//

						}
						else
						{
							itemHashMap.put(rs.getString(1),new Integer(itemHashMap.size()+1));
						}
					}
					if(itemCodeMap.containsKey(rs.getString(1)))
					{}
					else
					{
						itemCodeMap.put(rs.getString(1),new Integer(itemCodeMap.size() + 1));
					}
					if( ( (innerSuppSour != null) && (!innerSuppSour.equals("P")) ) && (innerBomCode != null) )
					{
						//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();
				}
			}
			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
	}

	// 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();
		}
		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();

		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();


					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 = "";

		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
                +" CASE WHEN A.MFG_TYPE IS NULL THEN 'MRP' ELSE A.MFG_TYPE END, A.ROUTE_CODE "
				+" 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 = '" + siteCode + "' "
				+" AND A.ITEM_CODE = '" + itemCode + "' ";

				//+" AND A.SITE_CODE = ? "
				//+" AND A.ITEM_CODE = ? ";

			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);
                }
				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());
	                }

					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
	{
		try{
			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;

		Statement stmt = null;
		ResultSet rs = null;
		errString = "";
		String adpQuery = null;

		try
		{
			itemCode = adpElement.getItemCode();
			siteCode = adpElement.getSiteCode();

			//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);
			//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);
				// 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);
				}
				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();
			stmt.close();
		}
		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 (stmt != null) { stmt = 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 adpElement; //Moved here from finaly - jiten - 03/11/06
	}

	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;
             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();

             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"))
                           {
                             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
                             {
                             	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)
    {
       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
    {
       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)
	{
		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)
    {
       int multiplier = 0;
       multiplier = new Double(Math.floor(total / batch)).intValue();
       return total - (multiplier * batch);
    }
    private double getStkLevel(ADPElement adpElement, java.sql.Timestamp dueDate)
	{
		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();
		}
                return retValue;
	}

	private void updateDemand(String xmlString)
	{
             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
             {
                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);
	                             adpElement.setDemand(dueDate, quantity);
	                             adpeList.set(index,adpElement);
	                             //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();
              }
	}

	private int indexOfADPElement(String siteCode, String itemCode)
	{
             int retVal = -1, ctr = 0;
             ADPElement adpElement = null;
             for (ctr = 0; ctr < adpeList.size(); ctr++)
             {
                     adpElement = (ADPElement)adpeList.get(ctr);
                     if ( (adpElement.getSiteCode().equals(siteCode)) && (adpElement.getItemCode().equals(itemCode)) )
                     {
                             retVal = ctr;
                             break;
                     }
             }
             return retVal;
	}

	private String populateInterSiteDistance()
	{
             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();
		}
		catch(Exception e)
		{
                  System.out.println("Exception :populateInterSiteDistance():" + e.getMessage() + ":");
                  this.errString = genericUtility.createErrorString(e);
                  e.printStackTrace();
		}
		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)
	{
		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()
	{
             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)
	{
          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)
	{
             String retStrDate =  null;
             try
             {
               SimpleDateFormat simpleDateFormat = new SimpleDateFormat(targetFormat);
               retStrDate = simpleDateFormat.format(timeStampDate);
             }
             catch(Exception e)
             {
                     System.out.println("Exception :RunMRPPrcEJB :getDateFromTimeStamp(TimeStampDate) " + e);
             }
             return retStrDate;
	}

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

             String sqlMpsOrder;
             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 = ? ";

                   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();
                  }

                  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();
             }
             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()
	{
		ADPElement adpElement = null;

		try
		{
			for (int adpeCtr = 0; adpeCtr < adpeList.size(); adpeCtr++)
			{
                           adpElement = (ADPElement)adpeList.get(adpeCtr);
                           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();
		}
		      	return errString;
	}
	private String generatePlanDemand(ADPElement adpElement)
	{
		    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());
                     }
                   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)

                                    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)

	                                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;
	                                mpsList.add(mpsOrder);
	                            }
	                            //Jiten - End 03/11/06
                           }
                           else if ( orderOpt.equals("C") )
                           {
                                int findEle = -1;
                                MPSOrder mpsOrder = null;
                                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;

                                    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();
             }
             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()
         {
         	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)
        {
 		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();
		}

		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();
		}
		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)
	{
		PreparedStatement pstmtMpsOrder = null;
		PreparedStatement pstmtIndent = null;
		PreparedStatement pstmtDistDemand = null;
		PreparedStatement pstmtIndDemand = null;
		PreparedStatement pstmtKeyStr = 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;

		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.close();
			pstmtKeyStr = null;
			rs = null;

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


			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) "
						+ "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;
					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;
					// 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 + "<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>";

					tranIdMpsOrder = getUniqueKey(xmlValues, mpsKeyStr, "tran_id", "W-MPS");

					//System.out.println("Inserting mpsorder # " + count + " : " + tranIdMpsOrder);
					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);

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

				}
			}
			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 [] 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 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();
		}
		finally
		{
			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;

		int purLeadTime = 0;
		int qcLeadTime = 0;
		int mfgLeadTime = 0;
		int minStkDay = 0;
		int maxStkDay = 0;

		boolean flag = true;

		java.sql.Timestamp dueDate = null;

		ADPElement adpElement = null;
		try
		{
			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) "
						 	  + "VALUES( ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? ) ";
			pstmtPlanHdr = conn.prepareStatement(sqlPlanHdr);

			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) "
						 	  + "VALUES( ? , ? , ? , ? , ? , ? ,"
					  		  + " ? , ? , ? , ? ) ";
			pstmtPlanDet = conn.prepareStatement(sqlPlanDet);

			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();
				// 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();
					independentDemand = timeMrp.getIndependentDemand();
					dueDate = timeMrp.getDueDate();

					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);

					pstmtPlanDet.addBatch();
				}
				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.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;

		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());

					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("\n");
					}
					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(" ").append("\n");
					}
					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;
	}

}

//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;
	}*/
