/********************************************************
	Title : ShipmentConf
	Date  : 01/06/2012
	Developer: Mahesh Patidar
 ********************************************************/

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.awt.image.BufferedImage;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;
import javax.imageio.ImageIO;
import javax.xml.rpc.ParameterMode;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.apache.poi.hssf.util.HSSFColor.RED;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
//Changed by sumit on 06/10/12
import org.w3c.dom.*;

import sun.misc.BASE64Decoder;

import com.adsionline.ADSIShippingWS.ADSIShippingLocator;
import com.adsionline.ADSIShippingWS.ADSIShippingSoap;
import com.adsionline.ADSIShippingWS.Address;
import com.adsionline.ADSIShippingWS.Carrier;
import com.adsionline.ADSIShippingWS.CommodityInfo;
import com.adsionline.ADSIShippingWS.Country;
import com.adsionline.ADSIShippingWS.EnumManifestAction;
import com.adsionline.ADSIShippingWS.EnumPackageType;
import com.adsionline.ADSIShippingWS.EnumTerms;
import com.adsionline.ADSIShippingWS.EnumTimeInTransit;
import com.adsionline.ADSIShippingWS.EnumUnitOfMeasure;
import com.adsionline.ADSIShippingWS.ImageRotation;
import com.adsionline.ADSIShippingWS.NameValuePair;
import com.adsionline.ADSIShippingWS.Package2;
import com.adsionline.ADSIShippingWS.PackageResult2;
import com.adsionline.ADSIShippingWS.ShipWithLabelType;
import com.adsionline.ADSIShippingWS.Shipment2;
import com.adsionline.ADSIShippingWS.ShipmentResult2;
import com.adsionline.ADSIShippingWS.ShipmentResultReturn2;
import com.adsionline.ADSIShippingWS.StringDictionaryReturn;


@Stateless
public class ShipmentConf extends ActionHandlerEJB implements ShipmentConfLocal, ShipmentConfRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	
	
	//start new 
	public String confirm(String tranId, String xtraParam, String forcedFlag) throws RemoteException,ITMException
	{

		
		String errString = "";
		String sql = "",sSQLUpdatSord = "";
		String refId = "",proNo = "",tranProNo = "";
		String lorryNo = "";
		String tranCode = "";
		String lrNo = "";
		String confirmed = "";
		String chDigitYn = "";
		String refSer = "", refSerNew = "";
		int noOfPallet = 0, count = 0;
		Timestamp lrDate = null;
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		PreparedStatement pstmt1 = null,pstmtSord = null;
		String winName = "";
		String objName = "";
		String adsiShippingURL = "";
		DistCommon discommon = new DistCommon();
		ADSIShippingLocator locator = null;
		ADSIShippingSoap  soap = null;
		Shipment2 shipment2 = null;
		Address recipient = null;
		Package2 pkg2 = null;
		CommodityInfo comInfo = null;
			
		ArrayList<String> cartonNoList = new ArrayList<String>();

		String custName = "";
		String shipAdd1 = "";
		String shipAdd2 = "";
		String shipAdd3 = "";
		String shipCity = "";
		String stateCodeDlv = "";
		String shipCountry = "";
		String serviceCode = "";
		String saleOrder = "";
		String cartonNo = "";
		String sqlNoPkg = "";
		String strlen = "";
		String widthStr = "";
		String heightStr = "";
		String defPh = "";
		String shipPin = "";
		String palleNo = "";
		String pkgDescr = "";
		String status = "";
		String phone = "";
		String itemDescr = "";
		//Changed By Pragyan 29/DEC/14 To store seal no and data logger no
		String sealNo = "",dataLoggerNo = "";
		
		int pkg = 0;
		int noOfPkgs = 0;
		int commCnt = 0;
		double caseWeightTotal = 0.0;
		double slength = 0.0;
		double width = 0.0;
		double height = 0.0;
		double noArt = 0.0;
		double caseWeight = 0.0;
		double unitValue = 0.0;
		double dimensionDouble = 0.0;
		
		CommodityInfo[] infoCom1 = null;
		
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmtTransetUp = null;
		ResultSet rs2 = null,rsSet = null;
		ResultSet rs3 = null;
		boolean isAllDespConfirmed = false;
		//changed by sumitp on 10/03/13 for declare varibles start.
		String taroShipName= "";
		String shipTypeName= "";
		//changed by sumitp on 10/03/13 for declare varibles end.
		//Changed By Pragyan 18-DEC-13 To check ADSI Connection is required.(KB/Ashish mail Dt.121413) 
		String isADSIConnReq = "N";
		//changed by sankara on 03/01/14 for update pro_no
		String tranProNofrom = "";
		String tranProNoto = "";
		//changed by sankara on 08/01/14 for update lr_date in despatch.
		String ledgPostConf = "";
		String chgDateonConf = "";
		int pronumCount = 0;
		//changed by sankara on 22/09/14 for site transporter
		String siteCodeShip = "";
		boolean isSiteTransporterexist = true;
		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			String userID = genericUtility.getValueFromXTRA_PARAMS( xtraParam, "LoginCode" );
			System.out.println(" forced flag:::>>>>:: ["+forcedFlag+"]");
			//changed by sankara on 10/10/14 for adeded condition isLoading
			String isLoading = genericUtility.getValueFromXTRA_PARAMS( xtraParam, "isLoading" );
			//Changed by Pragyan
			isLoading = isLoading == null?"N":isLoading;
			System.out.println("isLoading:::::"+isLoading);
			ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
			
			//changed by sankara on 22/09/14 for site transporter start.
			sql = " SELECT DISTINCT S.SITE_CODE__SHIP FROM SORDER S, SHIPMENT D, SHIP_DOCS SD, WAVE_TASK W, WAVE_TASK_DET WT " +
					" WHERE D.SHIPMENT_ID = SD.SHIPMENT_ID AND SD.PTCN  = WT.PTCN AND S.SALE_ORDER = WT.SALE_ORDER AND D.SHIPMENT_ID = ? ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, tranId );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				siteCodeShip = checkNull(rs.getString("SITE_CODE__SHIP"));
				System.out.println("siteCodeShip::::::"+siteCodeShip);
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;		

			if(siteCodeShip == null || siteCodeShip.trim().length() == 0 )
			{
				sql = " SELECT SITE_CODE FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
				pstmt = conn.prepareStatement( sql );
				pstmt.setString( 1, tranId );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					siteCodeShip = checkNull(rs.getString("SITE_CODE"));
					System.out.println("manualsiteCodeShip::::::"+siteCodeShip);
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;		
			}	
			//changed by sankara on 22/09/14 for site transporter end.	
			
			//Changed By Pragyan 29/DEC/14 To get seal no and data logger no
			//sql = "SELECT SHIPMENT.CONFIRMED, SHIPMENT.NO_PALLET FROM SHIPMENT WHERE SHIPMENT.SHIPMENT_ID = ?  "; 
			sql = "SELECT SHIPMENT.CONFIRMED, SHIPMENT.NO_PALLET,SHIPMENT.SEAL_NO,SHIPMENT.DATA_LOGGER_NO FROM SHIPMENT WHERE SHIPMENT.SHIPMENT_ID = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				confirmed = rs.getString("CONFIRMED");
				noOfPallet = rs.getInt("NO_PALLET");
				//Changed By Pragyan 29/DEC/14 To get seal no and data logger no
				sealNo = checkNull(rs.getString("SEAL_NO"));
				dataLoggerNo = checkNull(rs.getString("DATA_LOGGER_NO"));
				
			}
			
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			sql = "SELECT REF_SER FROM SHIP_DOCS WHERE SHIPMENT_ID = ?  "; 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				refSer = checkNull(rs.getString("REF_SER"));
			}
			System.out.println("-- confirmed ["+confirmed+"] noOfPallet ["+noOfPallet+"] refSer ["+refSer.trim()+"]");
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			//isADSIConnReq = discommon.getDisparams("999999","CONN_TO_ADSI_APP",conn);
			
			if( "Y".equalsIgnoreCase(confirmed))
			{
				errString = itmDBAccessLocal.getErrorString("","SHIPALLCON","");
			}
			else
			{
				if( "S-DSP".equalsIgnoreCase(refSer.trim()) && "N".equalsIgnoreCase(forcedFlag))
				{
					errString = itmDBAccessLocal.getErrorString("","SDSPCONFIR","");
				}
				else
				{
			
					if(("".equalsIgnoreCase(refSer.trim())) || ("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()) ))
					{
						//changed by sankara on 10/10/14 for added isloading condition
						if( !"Y".equalsIgnoreCase(isLoading) )
						{
							if(noOfPallet <= 0 && !refSer.trim().equalsIgnoreCase("S-DSP"))
							{
							
								errString = itmDBAccessLocal.getErrorString("","VTINVPALNO","");
								return errString;
							}
							
							
							
							sql = "SELECT STATUS FROM PALLET_NO WHERE PALLET_NO IN ( SELECT PALLET_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							while( rs.next() )
							{
								status = checkNull(rs.getString("STATUS"));
								if((!"".equalsIgnoreCase(status) || status != null ) && !"A".equalsIgnoreCase(status) )
								{
									errString = itmDBAccessLocal.getErrorString("","VTINVPALET","");
									return errString;
									
								}
							}
							
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							sql = "SELECT STATUS FROM CARTON_MASTER WHERE CARTON_NO IN ( SELECT CARTON_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							while( rs.next() )
							{
							
								status = checkNull(rs.getString("STATUS"));
								if((!"".equalsIgnoreCase(status) || status != null ) && !"A".equalsIgnoreCase(status) )
								{
									errString = itmDBAccessLocal.getErrorString("","VTINVCARTN","");
									return errString;
									
								}
							}
							
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							
							sql = "SELECT CONSIGNEE_NAME,ADDR1,ADDR2,ADDR3,LORRY_NO,TRAN_CODE,LR_NO,LR_DATE, PRO_NO  FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
							
								custName = checkNull(rs.getString("CONSIGNEE_NAME")).trim();
								shipAdd1 = checkNull(rs.getString("ADDR3")).trim();
								shipAdd2 = checkNull(rs.getString("ADDR2")).trim();
								shipAdd3 = checkNull(rs.getString("ADDR1")).trim();
								lorryNo = checkNull(rs.getString("LORRY_NO"));
								tranCode = checkNull(rs.getString("TRAN_CODE"));
								lrNo = checkNull(rs.getString("LR_NO"));
								lrDate = rs.getTimestamp("LR_DATE");
								proNo = checkNull(rs.getString("PRO_NO"));
							}
							
							if("".equalsIgnoreCase(custName) && custName.trim().length() <= 0)
							{
								errString = itmDBAccessLocal.getErrorString("","VTINVCONSE","");
								return errString;
							}
							if(("".equalsIgnoreCase(shipAdd1) && shipAdd1.trim().length() <= 0 ) && ("".equalsIgnoreCase(shipAdd2) && shipAdd2.trim().length() <= 0 ) && ("".equalsIgnoreCase(shipAdd3) && shipAdd3.trim().length() <= 0 ))
							{
								errString = itmDBAccessLocal.getErrorString("","VTINVADDR","");
								return errString;
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						} //changed by sankara on 10/10/14 for added isloading condition end	
						//changed by sankara on 10/10/14 for added isloading condition start. 
						else
						{
							sql = "SELECT CONSIGNEE_NAME,ADDR1,ADDR2,ADDR3,LORRY_NO,TRAN_CODE,LR_NO,LR_DATE, PRO_NO  FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranId);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
							
								custName = checkNull(rs.getString("CONSIGNEE_NAME")).trim();
								shipAdd1 = checkNull(rs.getString("ADDR3")).trim();
								shipAdd2 = checkNull(rs.getString("ADDR2")).trim();
								shipAdd3 = checkNull(rs.getString("ADDR1")).trim();
								lorryNo = checkNull(rs.getString("LORRY_NO"));
								tranCode = checkNull(rs.getString("TRAN_CODE"));
								lrNo = checkNull(rs.getString("LR_NO"));
								lrDate = rs.getTimestamp("LR_DATE");
								proNo = checkNull(rs.getString("PRO_NO"));
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						//changed by sankara on 10/10/14 for added isloading condition end. 
					}//if(("".equalsIgnoreCase(refSer.trim())) || ("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()) ))
					
					sql = "SELECT COUNT(*) as COUNT FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						count = rs.getInt("COUNT");
					}
					rs.close();rs = null;
					pstmt.close();pstmt = null;
					
				//  changed by sankara on 10/10/14 for added loading condition	
				//	if( noOfPallet != count && !("S-DSP".equalsIgnoreCase(refSer.trim()))
					if( noOfPallet != count && !("S-DSP".equalsIgnoreCase(refSer.trim())) && !"Y".equalsIgnoreCase(isLoading) )
					{
						errString = itmDBAccessLocal.getErrorString("","NOOFPALLET","");
					}
					else 
					{
						
						
						
						if(("".equalsIgnoreCase(refSer.trim()) || ("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim())) ) && ( "".equalsIgnoreCase(tranCode.trim()) || tranCode == null) && tranCode.trim().length() <= 0 )
						{
							errString = itmDBAccessLocal.getErrorString("","VTTRANCDBK","");
							return errString;
						}	

						sql = "SELECT ST.PRONO_LAST, ST.CHK_DIGIT_YN FROM SITE_TRANSPORTER ST WHERE ST.TRAN_CODE = ? " +
								"AND ST.SITE_CODE = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranCode);
						pstmt.setString(2, siteCodeShip);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							tranProNo = rs.getString(1);
							chDigitYn = rs.getString("CHK_DIGIT_YN");
						}
						else
						{
							sql = "SELECT PRONO_LAST,CHK_DIGIT_YN FROM TRANSPORTER WHERE TRAN_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, tranCode);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								tranProNo = rs.getString(1);
								chDigitYn = rs.getString("CHK_DIGIT_YN");
							}
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sSQLUpdatSord = " UPDATE SORDER SET SHIP_STATUS = ? WHERE "
										+ " SALE_ORDER IN (SELECT SORD_NO FROM DESPATCH D,SHIP_DOCS S "
										+ " WHERE D.DESP_ID = S.REF_ID AND S.SHIPMENT_ID = ?) ";
						pstmtSord = conn.prepareStatement(sSQLUpdatSord);
						
						sql = " SELECT TRAN_WINDOW FROM TRANSETUP WHERE REF_SER = ? ";
						pstmtTransetUp = conn.prepareStatement(sql);
						pstmtTransetUp.setString(1, refSer.trim());						
						rsSet = pstmtTransetUp.executeQuery();
						if( rsSet.next() )
						{
							winName = rsSet.getString("TRAN_WINDOW");			
							objName = winName != null ? winName.substring(2):objName;
						}					
					    
					    System.out.println("OBJNAME=="+objName);
					    
					    if(pstmtTransetUp != null)
						{
							pstmtTransetUp.close();
							pstmtTransetUp = null;
						}
						if(rsSet != null)
						{
							rsSet.close();
							rsSet = null;
						}
						
						if(!("S-DSP".equalsIgnoreCase(refSer.trim())))
						{
							sql = "SELECT REF_ID, REF_SER FROM SHIP_DOCS WHERE SHIPMENT_ID = ? " 
								+ " AND DESP_CONFIRMED = ?";
						}
						else
						{
						
							sql = " SELECT S.REF_ID, S.REF_SER FROM SHIP_DOCS S,DESPATCH D WHERE "
								  +" S.SHIPMENT_ID = ? AND D.DESP_ID = S.REF_ID AND D.CONFIRMED = ?";
						}
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);		
						pstmt.setString(2, "N");
						
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							refId = rs.getString(1);
							refSerNew = checkNull(rs.getString("REF_SER"));
							System.out.println("ref_id " +refId+" refSer: ["+refSerNew.trim()+"]");
							//changed by sankara on 08/01/14 update lr_date in despatch start.
							int despcount = updateDespatch (refId);
							if( despcount > 0 )
							{
								System.out.println("lr date updated in despatch sucessfully");
							}
							//changed by sankara on 08/01/14 update lr_date in despatch end.
							if(refId != null && refId.trim().length() > 0)
							{
	                            errString = confirmShipment( objName, refId.trim(), xtraParam, forcedFlag, conn);
								System.out.println(" errString ---------------- > ["+errString);
							
								//Changed By Pragyan seal no and data logger no
								//updateShipDocs(refId,refSerNew, findError(errString));
								updateShipDocs(refId,refSerNew,sealNo,dataLoggerNo, findError(errString));
							}
						
						}
						
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
						if(rs != null)
						{
							rs.close();
							rs = null;
						}

						
						isAllDespConfirmed =  isCountSame(tranId, conn);
						
						if( isAllDespConfirmed || !("S-DSP".equalsIgnoreCase(refSer.trim())))
						{
							
							if("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()) || "".equalsIgnoreCase(refSer.trim()))
							{
								//Changed By Pragyan to take on Point not connecting to ADSI
								/*sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
				    				 +" S.SHIPMENT_ID = ? ";	*/

								//changed by sankara on 22/09/14 for site transporter start.	
								/*	sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE,T.ADSI_FLAG FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
					    				 +" S.SHIPMENT_ID = ? ";	
				    			PreparedStatement pstmtTrans = conn.prepareStatement(sql);
				    			pstmtTrans.setString(1, tranId);
								ResultSet rsTrans = pstmtTrans.executeQuery();
								if(rsTrans.next())
								{
									serviceCode = checkNull(rsTrans.getString("SERVICE_CODE"));
									dimensionDouble = rsTrans.getDouble("PACK_SIZE");
									isADSIConnReq = checkNull(rsTrans.getString("ADSI_FLAG"));
								}*/

								sql = "SELECT ST.SERVICE_CODE,S.PACK_SIZE,ST.ADSI_FLAG FROM SHIPMENT S, SITE_TRANSPORTER ST WHERE ST.TRAN_CODE = S.TRAN_CODE  " 
										+"AND S.SHIPMENT_ID = ? AND ST.SITE_CODE = ? ";	
								PreparedStatement pstmtTrans = conn.prepareStatement(sql);
								pstmtTrans.setString(1, tranId);
								pstmtTrans.setString(2, siteCodeShip);
								ResultSet rsTrans = pstmtTrans.executeQuery();
								if(rsTrans.next())
								{
									serviceCode = checkNull(rsTrans.getString("SERVICE_CODE"));
									dimensionDouble = rsTrans.getDouble("PACK_SIZE");
									isADSIConnReq = checkNull(rsTrans.getString("ADSI_FLAG"));
								}
								else
								{
									sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE,T.ADSI_FLAG FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
											+" S.SHIPMENT_ID = ? ";	
									pstmtTrans = conn.prepareStatement(sql);
									pstmtTrans.setString(1, tranId);
									rsTrans = pstmtTrans.executeQuery();
									if(rsTrans.next())
									{
										serviceCode = checkNull(rsTrans.getString("SERVICE_CODE"));
										dimensionDouble = rsTrans.getDouble("PACK_SIZE");
										isADSIConnReq = checkNull(rsTrans.getString("ADSI_FLAG"));
									}
								}
								//changed by sankara on 22/09/14 for site transporter end.	
								if(pstmtTrans != null)
								{
									pstmtTrans.close();
									pstmtTrans = null;
								}
								
								if(rsTrans != null)
								{
									rsTrans.close();
									rsTrans = null;
								}
								//isADSIConnReq
								//if(serviceCode != null && serviceCode.trim().length() > 0)
								if("Y".equalsIgnoreCase(isADSIConnReq) && serviceCode != null && serviceCode.trim().length() > 0)
								{

										System.out.println("Enetr in manual Shipment whit shipment ID"+tranId);
										adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
										defPh = discommon.getDisparams("999999","SHIPPER_DEFAULT_PH",conn);
										//changed by sumitp shipment type getting from Dispram Table on 10/03/2013 start.
										taroShipName = discommon.getDisparams("999999","TARO_SHIPPER_NAME",conn);
										System.out.println("TARO_SHIPPER_NAME" + taroShipName);
										//changed by sumitp shipment type getting from Dispram Table on 10/03/2013 end.
										com.adsionline.ADSIShippingWS.Service service = new com.adsionline.ADSIShippingWS.Service();
										NameValuePair[] additionalFields = new NameValuePair[1];
										Package2[] packgeList = null;
										ArrayList<CommodityInfo> comodityInfoList = new ArrayList<CommodityInfo>();
										System.out.println("After disparm value"+adsiShippingURL);
										long timeBefore = System.currentTimeMillis();
										locator = new ADSIShippingLocator();
							    		soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
							    		long timeAfter = System.currentTimeMillis();
							    		System.out.println("Differance In Tme for connecting to ADSI Server time differance in seconds["+(timeAfter-timeBefore)/1000+"]");
							    		
							    		if(soap.testADSIServer())
							    		{
							    			 
							    				System.out.println("---ADSI Server Connected---");
												System.out.println("************Enter in manual shiment ******************************");
								    			sql = "SELECT S.CONSIGNEE_NAME,S.ADDR1,S.ADDR2,S.ADDR3,S.CITY,S.COUNT_CODE,S.STATE_CODE,S.PIN,S.NO_ART,S.PKG_DESCR,S.UNIT_VALUE,S.ITEM_DESCR,S.FRT_LIST,S.TELE1,S.GROSS_WEIGHT " 
									    			  +"  FROM SHIPMENT S WHERE S.SHIPMENT_ID = ? ";
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													custName = checkNull(rs.getString("CONSIGNEE_NAME"));
													shipAdd1 = checkNull(rs.getString("ADDR1"));
													shipAdd2 = checkNull(rs.getString("ADDR2"));
													shipAdd3 = checkNull(rs.getString("ADDR3"));
													shipCity = checkNull(rs.getString("CITY"));
													shipCountry = checkNull(rs.getString("COUNT_CODE"));
													stateCodeDlv = checkNull(rs.getString("STATE_CODE"));
													shipPin = checkNull(rs.getString("PIN"));
													pkgDescr = checkNull(rs.getString("PKG_DESCR"));
													itemDescr = checkNull(rs.getString("ITEM_DESCR"));
													unitValue = rs.getDouble("UNIT_VALUE");
													noArt = rs.getDouble("NO_ART");
													phone  = checkNull(rs.getString("TELE1"));
													caseWeight = rs.getDouble("GROSS_WEIGHT");
													
												}
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
								    			shipment2 = new Shipment2();	
								    			recipient = new Address();
								    			shipment2.setID(refId);
								    			recipient.setID(custName);
								    			recipient.setContact(custName);
								    			recipient.setCompany("");
								    			if(!"".equalsIgnoreCase(shipAdd3) && shipAdd3.trim().length() > 0)
								    			{
								    				recipient.setAddress1(shipAdd3);//Customer Adress1
									    			recipient.setAddress2(shipAdd2);//Customer Adress2
									    			recipient.setAddress3(shipAdd1);//Customer Adress3
								    			}
								    			else if(!"".equalsIgnoreCase(shipAdd2) && shipAdd2.trim().length() > 0)
								    			{
								    				recipient.setAddress1(shipAdd2);//Customer Adress1
									    			recipient.setAddress2(shipAdd1);//Customer Adress2
									    			recipient.setAddress3("");
								    			}
								    			else
								    			{
								    				recipient.setAddress1(shipAdd1);//Customer Adress1
									    			recipient.setAddress2("");//Customer Adress2
									    			recipient.setAddress3("");
								    			}
								    			
								    			recipient.setCity(shipCity);//Customer Adress City
								    			//changed by sumitp getting shipment type from transporter on 10/03/13
								    			/*sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
								    				 +" S.SHIPMENT_ID = ? ";	*/
								    			
								    			//changed by sankara on 22/09/14 for site transporter start.
								    			/*	sql = " SELECT T.SERVICE_CODE, S.PACK_SIZE, T.SHIPMENT_TYPE FROM SHIPMENT S, TRANSPORTER T  WHERE " +
									    			  " T.TRAN_CODE = S.TRAN_CODE AND S.SHIPMENT_ID = ? ";	
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													serviceCode = checkNull(rs.getString("SERVICE_CODE"));
													dimensionDouble = rs.getDouble("PACK_SIZE");
													//changed by sumitp getting shipment type from transporter on 10/03/13
													shipTypeName = checkNull(rs.getString("SHIPMENT_TYPE"));
												}*/
								    			
								    			sql = " SELECT ST.SERVICE_CODE, S.PACK_SIZE, ST.SHIPMENT_TYPE FROM SHIPMENT S, SITE_TRANSPORTER ST WHERE " +
								    					" ST.TRAN_CODE = S.TRAN_CODE AND S.SHIPMENT_ID = ? AND ST.SITE_CODE = ? ";	
								    			pstmt = conn.prepareStatement(sql);
								    			pstmt.setString(1, tranId);
								    			pstmt.setString(2, siteCodeShip);
								    			rs = pstmt.executeQuery();
								    			if(rs.next())
								    			{
								    				serviceCode = checkNull(rs.getString("SERVICE_CODE"));
								    				dimensionDouble = rs.getDouble("PACK_SIZE");
								    				//changed by sumitp getting shipment type from transporter on 10/03/13
								    				shipTypeName = checkNull(rs.getString("SHIPMENT_TYPE"));
								    			}
								    			else
								    			{
								    				sql = " SELECT T.SERVICE_CODE, S.PACK_SIZE, T.SHIPMENT_TYPE FROM SHIPMENT S, TRANSPORTER T  WHERE " +
								    						" T.TRAN_CODE = S.TRAN_CODE AND S.SHIPMENT_ID = ? ";	
								    				pstmt = conn.prepareStatement(sql);
								    				pstmt.setString(1, tranId);
								    				rs = pstmt.executeQuery();
								    				if(rs.next())
								    				{
								    					serviceCode = checkNull(rs.getString("SERVICE_CODE"));
								    					dimensionDouble = rs.getDouble("PACK_SIZE");
								    					//changed by sumitp getting shipment type from transporter on 10/03/13
								    					shipTypeName = checkNull(rs.getString("SHIPMENT_TYPE"));
								    				}
								    			}
								    			//changed by sankara on 22/09/14 for site transporter end.				
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
								    			recipient.setState(stateCodeDlv);//Customer Adress State
								    			
								    			shipCountry = shipCountry !=null ?shipCountry.trim():"";
								    			System.out.println("---Consignee Country AS IT IS---["+shipCountry+"]");
								    			
								    			Country country = soap.getCountry("ISO3", shipCountry).getResponseCountry();
								    			
								    			if(country != null)
								    			{
								    				System.out.println("---ADSI return Default Country Friendly Name---["+country.getFriendlyName()+"]");
								    				System.out.println("---ADSI return Default Country ISO3---["+country.getISO3()+"]");
								    				System.out.println("---ADSI return Default Country ISO2---["+country.getISO2()+"]");
								    				
								    			}
								    			
								    			recipient.setCountry(country);//Customer Adress Country
								    			
								    			if(!"".equalsIgnoreCase(phone) && phone.trim().length() > 0)
								    			{
								    				recipient.setPhone(phone);
								    			}
								    			else
								    			{
								    				recipient.setPhone(defPh);
								    			}
								    			
								    			recipient.setEMail("");
								    			recipient.setFax("");
								    			recipient.setPostalCode(shipPin);
								    			
								    			shipment2.setRecipient(recipient);
								    			service.setFriendlyName(serviceCode);
								    	
								    			shipment2.setService(service);
								    			System.out.println("Service name is :"+shipment2.getService());
								    			System.out.println("Service code is :"+serviceCode);
								    			shipment2.setTerms(EnumTerms.trmSHIPPER);
								    			
								    			shipment2.setSignatureReleaseFlag(false);//false  As per Taro
								    			shipment2.setSignatureRequiredFlag(false);//true Required As per Taro
								    			shipment2.setSaturdayDeliveryFlag(false);// Taro Need To clerify "what is based on service level?"
								    	    			
								    			java.util.Date date = new java.util.Date();
								        		Calendar calendar = Calendar.getInstance();
								        		calendar.setTime(date);
								        		shipment2.setShipDate(calendar);
		
								        		shipment2.setConsigneeAccount("");//Not Required As per Taro
								        		shipment2.setThirdPartyBillingFlag(false);//Not Required As per Taro
								        		shipment2.setThirdPartyBillingAccount("");//Not Required As per Taro
								        		Address addrs = new Address();//Need to capture if required
								        		shipment2.setThirdPartyBillingAddress(addrs);//Not Required As per Taro
								    			
								        		shipment2.setHALFlag(false);//Need clarification from Taro
								        		shipment2.setHALAddress(addrs);//Need clarification from Taro
								        		
								        		shipment2.setManifestAction(EnumManifestAction.maRELEASE);//As per taro by default its maRELEASE
								        		
								        		
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		
								        		//shipment2.setShipperReference(ptcn);//As per Taro use PTCN(Pick Ticket Control No)
								        		shipment2.setShipperReference(tranId);
								        		//shipment2.setConsigneeReference(custPord);//As per Taro it is sorder.cust_pord
								        		shipment2.setConsigneeReference(refId);//As per Taro it is sorder.cust_pord
								        		//changed by sumitp on 03/10/13 getting value from disparm
								        		//shipment2.setShipperName("Taro Fulfillment");//Needs to change after clarity
								        		shipment2.setTimeInTransit(EnumTimeInTransit.tntFOREVER);//As per taro its tntFOREVER
								        		shipment2.setSaturdayDeliveryFlag(false);//Need to discuss with Taro
								        		//chnaged by sumitp on 10/03/13 for checking shipment type start. 
								        		System.out.println("taroShipNamefromdisparm:"+taroShipName);
								        		System.out.println("shipTypeName:"+shipTypeName);
								        		//changed by sankara on 10/04/13 shipment type compare using equalsIgnore case 
								        		//if(taroShipName != null && shipTypeName != null && taroShipName == shipTypeName)
								        		if( taroShipName != null && shipTypeName != null && taroShipName.trim().equalsIgnoreCase( shipTypeName.trim() ) ) 
								        		{
													shipment2.setShipperName(taroShipName);
												}
												else
												{
													errString = itmDBAccessLocal.getErrorString("","VTINVSHTYN","");
													return errString;
												}
								        		//chnaged by sumitp on 10/03/13 for checking shipment type end. 
								        		sqlNoPkg = "SELECT COUNT(*) AS NO_OF_PKG FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ";
								        		pstmt2 = conn.prepareStatement(sqlNoPkg);
								        		pstmt2.setString(1, tranId);
												rs2 = pstmt2.executeQuery();
												if(rs2.next())
												{
													noOfPkgs = rs2.getInt("NO_OF_PKG");
												}
												packgeList = new Package2[noOfPkgs];
												infoCom1 = new CommodityInfo[noOfPkgs] ;
												sql = "SELECT C.CARTON_NO,C.PALLET_NO,C.LENGTH,C.WIDTH,C.HEIGHT FROM SHIPMENT_CONTENT C WHERE  C.SHIPMENT_ID = ? ";
								    			
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												while(rs.next())
												{
													
													cartonNo = checkNull(rs.getString("CARTON_NO"));
													palleNo = checkNull(rs.getString("PALLET_NO"));
													slength = rs.getDouble("LENGTH");																					
													width =  rs.getDouble("WIDTH");;
													height = rs.getDouble("HEIGHT");
													caseWeightTotal = noArt * caseWeight;
													cartonNoList.add(cartonNo);
													
													pkg2 =  new Package2();
													pkg2.setID(cartonNo);
													pkg2.setWeight(caseWeightTotal);
													System.out.println("Case total weight"+caseWeightTotal);
													
													strlen = Double.toString(slength);
													widthStr =Double.toString(width);
													heightStr = Double.toString(height);
													
													System.out.println("strlen"+strlen+"widthStr"+widthStr+"heightStr"+heightStr);
													
													additionalFields[0] = new NameValuePair("carton_no", cartonNo);
													pkg2.setDimension(strlen+"x"+widthStr+"x"+heightStr);	
													System.out.println("Dimension of master package----< :"+pkg2.getDimension());
													pkg2.setTrackingNumber("");//Not Required Optional
													if(!"".equalsIgnoreCase(pkgDescr.trim()) && pkgDescr.trim().length() > 0)
													{	
														pkg2.setDescription(pkgDescr);//As per Taro 
													}
													else
													{
														pkg2.setDescription("Pharmaceuticals");
													}
													pkg2.setOversizeFlag(false);//As per Taro 
													pkg2.setDeclaredValueFlag(false);//As per Taro 
													pkg2.setDeclaredValueAmount(0.0);//As per Taro not required
													pkg2.setDeclaredValueCustoms(0.0);//As per Taro not required
													pkg2.setBillOfLadingComment("");//As per Taro not required
													pkg2.setAdditionalHandlingFlag(false);//As per Taro false
													pkg2.setPackaging(EnumPackageType.pkgCUSTOM);//As per Taro default pkgCUSTOM
													pkg2.setCODFlag(false);//As per Taro false
													pkg2.setCODAmount(0.0);//As per Taro not required 
													pkg2.setCODPaymentType(0);//As per Taro not required 
													Address addr = new Address();
													pkg2.setCODReturnAddress(addr);//As per Taro not required 
													
													pkg2.setAdditionalFields(additionalFields);
													//originCountry = checkNull(rs3.getString("COUNT_CODE__MFG"));
													pkg2.setHazmatFlag(false);
													pkg2.setCODReturnAddress(addr);//As per Taro not required
													comInfo = new CommodityInfo();
													//Change according 09-OCT-13 to Ashish/Stepehn for ItemDescription
													//comInfo.setDescription("");//As per taro Harmonization description
													comInfo.setDescription(itemDescr);//As per taro Harmonization description
													comInfo.setQuantity(noArt);
													comInfo.setHarmonizedCode("");//As per Taro Haromonization code
													if(unitValue > 0)
													{	
														comInfo.setUnitValue(unitValue);
													}
													else
													{
														comInfo.setUnitValue(0);
													}
													comInfo.setUnitWeight(caseWeight);
													Country originCountryMfg = soap.getCountry("ISO3", shipCountry).getResponseCountry();
													comInfo.setOriginCountry(originCountryMfg);
													comInfo.setNaftaFlag(false);
													comInfo.setExportNumberExpireDate(calendar);
													comInfo.setUnitOfMeasure(EnumUnitOfMeasure.uomPCS);
													
													infoCom1[commCnt] = comInfo;
													commCnt++;
															
													pkg2.setCommodityInfos(infoCom1);
													packgeList[pkg]=pkg2;
													pkg++;
												}
														
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
												if(pstmt2 != null)
												{
													pstmt2.close();
													pstmt2 = null;
												}
												
												if(rs2 != null)
												{
													rs2.close();
													rs2 = null;
												}
								    		
							    			//}
							    			//Changed by Rohan on 30-07-13 for generating UPS/FEDEX lable in case of non inventory manual shipment.end
							        		
							        		if(packgeList.length != 0  || packgeList.length > 0)
											{
												
												Timestamp currDate = new Timestamp(System.currentTimeMillis());
												shipment2.setPackages2(packgeList);
												ShipmentResultReturn2 objReturn2=soap.shipWithLabel(shipment2, ShipWithLabelType.PNG, ImageRotation.Rotation_180);
									    		System.out.println("Is successfully Executed ["+objReturn2.isSuccess()+"]");
									    		System.out.println("Message String ["+objReturn2.getMessage()+"]");
									    		
									    		if(!objReturn2.isSuccess())
									    		{
									    			//errString = getError(saleOrder + "[" + objReturn2.getMessage() +"]", "ADSISRVERR", conn);
									    			errString = getError(saleOrder + "[" + objReturn2.getMessage() +"]", "ADSISRVERR", conn);
									    			return errString;
									    		}
									    		else
									    		{
									    			//Updating ADSI getting the images.start
													ShipmentResult2 objShipResult = objReturn2.getShipmentResultResponse2();
													int  lenPacakges =  objShipResult.getPackageResults2().length;
													String value = "";
													String pckgId = "";
													String trackNo="";
													double msnNo=0.0;
													String imagePath =null;
													imagePath = discommon.getDisparams("999999","SHIP_WITH_PATH",conn);
													//changed by sankara on 04/02/14 for gwetting carrier symbol and varrier friendly name from adsi start.
													String carrierFriendlyName = "";
													String serviceSymbol = objShipResult.getServiceSymbol();
													System.out.println("serviceSymbol["+serviceSymbol+"]");
													String carrierSymbol = serviceSymbol.substring(0,serviceSymbol.lastIndexOf(".") );
													System.out.println("Carrier Symbol ["+carrierSymbol+"]");
													Carrier[] carr = soap.getCarriers().getCarrierList();
													for(int j = 0;j<carr.length;j++)
													{
														Carrier carr1 = carr[j];
														System.out.println("Carrier Symbol["+carr1.getSymbol());
														System.out.println("Friendly Name["+carr1.getFriendlyName());
														System.out.println("Short Name["+carr1.getShortName());

														if(carr1.getSymbol().equalsIgnoreCase(carrierSymbol))
														{
															carrierFriendlyName = carr1.getFriendlyName();
															break;
														}								
													}
													System.out.println("Carrier friwndley ["+carrierFriendlyName+"]");
													//changed by sankara on 04/02/14 for gwetting carrier symbol and varrier friendly name from adsi ned.
													for(int pCtr = 0;pCtr < lenPacakges;pCtr++)
													{
														PackageResult2 pkg2frm =  (PackageResult2)objShipResult.getPackageResults2()[pCtr];	
														value = pkg2frm.getBase64Label();
														pckgId = pkg2frm.getPackageID();
														//change by rekha on 23-05-13.start
														pkg2frm.getAdditionalFields();
														trackNo=pkg2frm.getTrackingNumber();				
														
														cartonNo = cartonNoList.get(pCtr);
														
														System.out.println("cnumber is :"+cartonNo);
														System.out.println("Package ID["+pckgId+"]");
														System.out.println("Tracking No ["+pkg2frm.getTrackingNumber()+"]");
														System.out.println("BASE64lABEL Length["+value.length()+"]");
														BASE64Decoder decoder = new BASE64Decoder(); 
														System.out.println("Package id for carton no is :[" +decoder.decodeBuffer(pckgId) +"]");
														msnNo = pkg2frm.getMSN();
														
														System.out.println("MSN NO IN EACH PACKAGE IS :" +msnNo);
														byte[] imgBytes = decoder.decodeBuffer(value);    
														System.out.println("IMAGE Byte Length ["+imgBytes.length+"]");
														BufferedImage bufImg = ImageIO.read(new ByteArrayInputStream(imgBytes));  
														
														File imgOutFile = new File(imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+cartonNo +".png");
														
														String path = imgOutFile.getPath();
														System.out.println("Get Path ["+path+"]");
													
														File createFolder = new File(path);
														if(!createFolder.exists())
														{
															createFolder.mkdirs();
														}
														//changed by sankara on 04/02/14 for gwetting carrier symbol and varrier friendly name from adsi
														/*sql = "UPDATE CARTON_MASTER SET TRACKING_NO = ?  , SHIP_LABEL= ? , MSN_NO = ? ,STATUS = 'U' "
																+ " WHERE CARTON_NO = ?";	*/	
														sql = "UPDATE CARTON_MASTER SET TRACKING_NO = ?  , SHIP_LABEL= ? , MSN_NO = ? ,STATUS = 'U', CARRIER_SYMBOL = ?, " +
															  "CARRIER_FRIENDLYNAME = ? WHERE CARTON_NO = ? ";		
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, trackNo);
														pstmt.setString(2, path );
														pstmt.setDouble(3, msnNo);
														//changed by sankara on 04/02/14 for gwetting carrier symbol and varrier friendly name from adsi start.
														//pstmt.setString(4, cartonNo);
														pstmt.setString(4, carrierSymbol);
														pstmt.setString(5, carrierFriendlyName);
														pstmt.setString(6, cartonNo);
														//changed by sankara on 04/02/14 for gwetting carrier symbol and varrier friendly name from adsi ned.
														System.out.println("carton number in for loop: "+cartonNo);
														System.out.println("msn no for pkg result is :" +msnNo);
														int updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("carton master update succesfully");
															
														}
														else    
														{
															System.out.println("Error in carton master updation");
														}
														//update tracking number in shipment contents.start
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														sql = " UPDATE SHIPMENT_CONTENT SET TRACKING_NO = ? WHERE SHIPMENT_ID = ? AND LINE_NO = ? ";
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, trackNo);
														pstmt.setString(2,tranId);
														pstmt.setInt(3, (pCtr+1));
														updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("SHIPMENT_CONTENT update succesfully");
															
														}
														else    
														{
															System.out.println("Error in SHIPMENT_CONTENTr updation");
														}
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														
														sql = " UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN (SELECT PALLET_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? AND LINE_NO = ? )";
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, tranId);
														pstmt.setInt(2, (pCtr+1));
													
														updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("PALLET_NO update succesfully");
															
														}
														else    
														{
															System.out.println("Error in PALLET_NO updation");
														}
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														
														String n = pkg2frm.getTrackingNumber();
														System.out.println("new tracking no  of master package :"+n);
														double n1 = pkg2.getWeight();
														
														System.out.println("new weight  of master package :"+n1);
													
														imgOutFile.createNewFile();
														ImageIO.write(bufImg, "png", imgOutFile);
														System.out.println("Write File Complete----");
															
													}
													
													OutputStream file = new FileOutputStream(imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+"Ship"+".ser" );
										    	    OutputStream buffer = new BufferedOutputStream( file );
										    	    ObjectOutput output = new ObjectOutputStream( buffer );
										   	        output.writeObject(shipment2);
										   	        output.flush();
										   	        output.close();  
										   	        file = new FileOutputStream( imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+"ShipRslt"+".ser" );
										   	        buffer = new BufferedOutputStream( file );
										    	    output = new ObjectOutputStream( buffer );
										   	        output.writeObject(objReturn2);
										   	        output.flush();
										   	        output.close();  
									    		}
								    		}
						        		    else
						        		    {
						        		    	System.out.println("---Packages not set succesfully---");
								    			//errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
								    			errString = itmDBAccessLocal.getErrorString("","VTADSIPKGE","");
								    			return errString;
						        		    }
							        			
							    		}
							    		else
							    		{
							    			System.out.println("---ADSI Server Not Connected throw error---");
							    			//errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
							    			errString = itmDBAccessLocal.getErrorString("","ADSISRVERR","");
							    			return errString;
		
							    		}
		
								}
								//chamged by sankara on 26/06/14 for pro number start.  
								/*if( updateShipment(tranId , conn, xtraParam) > 0)
								{
									System.out.println(" update sucessfully mannual shipment");
								}*/
								if(isCountSameForManulShip(tranId, conn))
								{
									//changed by sankara on 22/09/14 for sitetransporter start
									/*if( updateShipment(tranId , conn, xtraParam) > 0)
									{
										System.out.println(" update sucessfully mannual shipment");
									}*/
									if( updateShipment(tranId ,siteCodeShip, conn, xtraParam) > 0)
									{
										System.out.println(" update sucessfully mannual shipment");
									}
									
								/*	pronumCount = duplicateProNumber(tranId , conn);
									if( pronumCount > 0 )
									{
										errString = itmDBAccessLocal.getErrorString("","VTINVPRONO","");
										return errString;
									}*/
									
									sql = "UPDATE SHIPMENT SET CONFIRMED = ? , CONF_DATE = ?, LR_DATE = ? WHERE SHIPMENT_ID = ?";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, "Y");
									pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
									pstmt.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
									pstmt.setString(4, tranId);
									
									pstmt.executeUpdate();
									pstmt.close();
									pstmt = null;
								}
								//chamged by sankara on 26/06/14 for pro number end.  
							
							}
							if(isAllDespConfirmed)
							{
								//changed by sankara on 22/09/14 for sitetransporter
								/*if( updateShipment(tranId , conn, xtraParam) > 0)
								{
									System.out.println(" update sucessfully ");
								}*/
								if( updateShipment(tranId ,siteCodeShip, conn, xtraParam) > 0)
								{
									System.out.println(" update sucessfully ");
								}
						
								sql = "SELECT LORRY_NO,TRAN_CODE,LR_NO,LR_DATE, pro_no FROM SHIPMENT WHERE SHIPMENT_ID = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, tranId);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									lorryNo = checkNull(rs.getString(1));
									tranCode = checkNull(rs.getString(2));
									lrNo = checkNull(rs.getString(3));
									lrDate = rs.getTimestamp(4);
									proNo = checkNull(rs.getString(5));
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								sql = "UPDATE DESPATCH  SET LORRY_NO = ?, SHIPMENT_ID = ?, TRAN_CODE = ?," 
									//changed by sankara on 08/01/14 for update lr_date in despatch	
									//+ " LR_NO  = ?,  LR_DATE = ?, PRO_NO = ? WHERE " 
									+ " LR_NO  = ?, PRO_NO = ? WHERE " 	
									+ " DESP_ID IN (SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ?)";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lorryNo);
								pstmt1.setString(2, tranId);
								pstmt1.setString(3, tranCode);
								pstmt1.setString(4, lrNo);
								//changed by sankara on 08/01/14 for update lr_date in despatch start.	
								/*pstmt1.setTimestamp(5, lrDate);
								pstmt1.setString(6, proNo);
								pstmt1.setString(7, tranId);*/
								pstmt1.setString(5, proNo);
								pstmt1.setString(6, tranId);
								//changed by sankara on 08/01/14 for update lr_date in despatch end.	
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								
								sql = " UPDATE INVOICE SET LR_NO  = ? WHERE DESP_ID IN " 
									+ " (SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ?)";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lrNo);
								pstmt1.setString(2, tranId);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								
								//changed by sankara on 03/01/14 for update of pro no start.
								
								//changed by sankara on 22/09/14 for sitetransporter start.
								/*sql = " SELECT PRONO_FROM, PRONO_TO, PRONO_LAST, CHK_DIGIT_YN FROM TRANSPORTER WHERE TRAN_CODE = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, tranCode);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									tranProNofrom = rs.getString(1);
									System.out.println("tranProNofrom::::"+tranProNofrom);
									tranProNoto= rs.getString(2);
									System.out.println("tranProNoto::::"+tranProNoto);
									tranProNo = rs.getString(3);
									System.out.println("tranprolastno::::"+tranProNo);
									chDigitYn = rs.getString("CHK_DIGIT_YN");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;*/
																
								sql = " SELECT PRONO_FROM, PRONO_TO, PRONO_LAST, CHK_DIGIT_YN FROM SITE_TRANSPORTER WHERE TRAN_CODE = ? AND SITE_CODE = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, tranCode);
								pstmt.setString(2, siteCodeShip);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									isSiteTransporterexist = true;
									tranProNofrom = rs.getString(1);
									System.out.println("tranProNofrom::::"+tranProNofrom);
									tranProNoto= rs.getString(2);
									System.out.println("tranProNoto::::"+tranProNoto);
									tranProNo = rs.getString(3);
									System.out.println("tranprolastno::::"+tranProNo);
									chDigitYn = rs.getString("CHK_DIGIT_YN");
								}
								else
								{
									sql = " SELECT PRONO_FROM, PRONO_TO, PRONO_LAST, CHK_DIGIT_YN FROM TRANSPORTER WHERE TRAN_CODE = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, tranCode);
									rs = pstmt.executeQuery();
									if( rs.next() )
									{
										tranProNofrom = rs.getString(1);
										System.out.println("tranProNofrom::::"+tranProNofrom);
										tranProNoto= rs.getString(2);
										System.out.println("tranProNoto::::"+tranProNoto);
										tranProNo = rs.getString(3);
										System.out.println("tranprolastno::::"+tranProNo);
										chDigitYn = rs.getString("CHK_DIGIT_YN");
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								//changed by sankara on 03/01/14 for update of pro no end.				
								if((tranProNo != null && proNo != null) && proNo.length() > tranProNo.length())
								{
									proNo = proNo.substring(0,tranProNo.length());
								}
								else if( tranProNo == null && "Y".equalsIgnoreCase(chDigitYn))
								{
									proNo = proNo.substring(0,proNo.length() - 1);
								}
								//changed by sankara on 03/01/14 for update of pro no start.
								else if( tranProNofrom == null && tranProNoto == null && tranProNo == null  )
								{
									proNo = "";
								}
								//changed by sankara on 03/01/14 for update of pro no end.
								System.out.println(" pro_no ["+proNo+"]");
								
								//changed by sankara on 22/09/14 for sitetransporter start..
								/*	sql = "UPDATE TRANSPORTER SET PRONO_LAST  = ? WHERE TRAN_CODE = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, proNo);
								pstmt1.setString(2, tranCode);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;*/

								if(!isSiteTransporterexist)
								{
									sql = "UPDATE TRANSPORTER SET PRONO_LAST  = ? WHERE TRAN_CODE = ?";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, proNo);
									pstmt1.setString(2, tranCode);
									pstmt1.executeUpdate();
									pstmt1.close();
									pstmt1 = null;
								}
								if(isSiteTransporterexist)
								{
									sql = "UPDATE SITE_TRANSPORTER SET PRONO_LAST  = ? WHERE TRAN_CODE = ? AND SITE_CODE = ? ";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, proNo);
									pstmt1.setString(2, tranCode);
									pstmt1.setString(3, siteCodeShip);
									pstmt1.executeUpdate();
									pstmt1.close();
									pstmt1 = null;
								}
								//changed by sankara on 22/09/14 for sitetransporter end.

								pstmtSord.setString(1, "C");
								pstmtSord.setString(2, tranId);
								int updateSorder = pstmtSord.executeUpdate();
								
								if(updateSorder > 0)
								{
									System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
								}
								if( pstmtSord!= null)
								{
									pstmtSord.close();
									pstmtSord = null;
								}
								//chamged by sankara on 26/06/14 for pro number start.
							/*	pronumCount = duplicateProNumber(tranId , conn);
								if( pronumCount > 0 )
								{
									errString = itmDBAccessLocal.getErrorString("","VTINVPRONO","");
									return errString;
								}*/
								//changed by sankara on 08/01/14 for update lr_date in shipment header
								//sql = "UPDATE SHIPMENT SET CONFIRMED = ? , CONF_DATE = ? WHERE SHIPMENT_ID = ?";
								sql = "UPDATE SHIPMENT SET CONFIRMED = ? , CONF_DATE = ?, LR_DATE = ? WHERE SHIPMENT_ID = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, "Y");
								//changed by sankara on 08/01/14 for update lr_date in shipment start.
							  /*pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
								pstmt.setString(3, tranId);*/
								pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
								pstmt.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
								pstmt.setString(4, tranId);
								//changed by sankara on 08/01/14 for update lr_date in shipment end.
								pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								//conn.commit();
							}		
							//changed by sankara on 08/01/14 for update shipment_date in shipment header based on transetup start.
							sql = " SELECT LEDG_POST_CONF, CHG_DATE_ON_CONF FROM TRANSETUP WHERE TRAN_WINDOW = 'w_shipment' ";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{				
								ledgPostConf = checkNull( rs.getString("LEDG_POST_CONF") );		
								chgDateonConf = checkNull( rs.getString("CHG_DATE_ON_CONF") );		 
							}
							if("Y".equalsIgnoreCase(ledgPostConf) && "Y".equalsIgnoreCase(chgDateonConf) )
							{
								sql = " UPDATE SHIPMENT SET SHIPMENT_DATE = ? WHERE SHIPMENT_ID = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
								pstmt.setString(2, tranId);
								pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
							}
							if( rs != null)
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}
							//changed by sankara on 08/01/14 for update shipment_date in shipment header based on transetup end.
							
						    if(conn != null)
							{
								conn.commit();
							}
						    //changed by sankara on 26/06/14 for consumption issue start.
							//errString = itmDBAccessLocal.getErrorString("","VTCONFIRM","");
						    System.out.println("coming confirm error message::::::"+errString);
						    if(isAllDespConfirmed || isCountSameForManulShip(tranId, conn) )
						    {
						    	errString = itmDBAccessLocal.getErrorString("","VTCONFIRM","");
						    }
						    else
						    {
						    	errString = itmDBAccessLocal.getErrorString("","VTUNCNFIRM","");
						    }
						    //changed by sankara on 26/06/14 for consumption issue end.
						}
					}
				}
			}
			if( pstmtSord!= null)
			{
				pstmtSord.close();
				pstmtSord = null;
			}
		}
		catch(Exception e)
		{
			try{
				conn.rollback();
			}
			catch(SQLException es)
			{
				System.out.println("Rollback exception : " + es.getMessage() + ":");
			}
			System.out.println("Exception : Shipment Confirm():" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("!! Closing Connection in Shipment Confirm() !!");
			try
			{
				if( pstmtSord!= null)
				{
					pstmtSord.close();
					pstmtSord = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}

				if(pstmtTransetUp != null)
				{
					pstmtTransetUp.close();
					pstmtTransetUp = null;
				}
				if(rsSet != null)
				{
					rsSet.close();
					rsSet = null;
				}
							
				conn.close();
				conn = null;
			}
			catch(Exception se){
				System.out.println(se.getMessage());
				throw new ITMException(se);
			}
		}
		return errString;
	
	}
	
	//end new
	
	
	/*public String confirm(String tranId, String xtraParam, String forcedFlag) throws RemoteException,ITMException
	{
		
		int cnt = 0;
		String errString = "";
		String sql = "",sSQLUpdatSord = "";
		String refId = "",proNo = "",tranProNo = "";
		String lorryNo = "";
		String tranCode = "";
		String lrNo = "";
		//Changed by sumit on 28/09/12 
		String confirmed = "";
		String newErro = "";
		//Changed by sumit on 05/10/12 created ref_ser variable 
		//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] adding variable for CHK_DIGIT_YN
		String chDigitYn = "";
		String refSer = "", refSerNew = "";
		int noOfPallet = 0, count = 0;
		ArrayList<String> errList = new ArrayList<String>();
		Timestamp lrDate = null;
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		PreparedStatement pstmt1 = null,pstmtSord = null;
	//  changed by sankara on 12-07-13 for confirmation of consumption issue start...	
		String winName = "";
		String objName = "";
	//  changed by sankara on 12-07-13 for confirmation of consumption issue end...	
		//Changed by Rohan on 03-june define variables for manual shipment print lable.start
		String adsiShippingURL = "";
		DistCommon discommon = new DistCommon();
		ADSIShippingLocator locator = null;
		ADSIShippingSoap  soap = null;
		Shipment2 shipment2 = null;
		Address recipient = null;
		Package2 pkg2 = null;
		CommodityInfo comInfo = null;
			
		ArrayList<String> cartonNoList = new ArrayList<String>();
		
		String custCodeDlv = "";
		String custName = "";
		String custShName = "";
		String shipAdd1 = "";
		String shipAdd2 = "";
		String shipAdd3 = "";
		String shipCity = "";
		String stateCodeDlv = "";
		String descr = "";
		String shipCountry = "";
		String shipPh = "";
		String shipMailId = "";
		String shipFax = "";
		String serviceCode = "";
		String ptcn = "";
		String saleOrder = "";
		String custPord = "";
		String cartonNo = "";
		String sqlSize = "";
		String sqlNoPkg = "";
		String originCountry = "";
		String harmonizDescr = "";
		String harmonizCode = "";
		String itemCode = "";
		String sqlItem ="";
		String strlen = "";
		String widthStr = "";
		String heightStr = "";
		String dimension = "";
		String hazardous = "";
		String defPh = "";
		String shipPin = "";
		String palleNo = "";
		String pkgDescr = "";
		String status = "",palletNo = "",itemDescr = "";
		String frtList ="",frtTerm = "";
		String phone = "";
		
		int pkg = 0;
		int noOfPkg = 0;
		int noOfPkgs = 0;
		int commCnt = 0;
		double caseWeightTotal = 0.0;
		double slength = 0.0;
		double width = 0.0;
		double height = 0.0;
		double noArt = 0.0;
		double packSizeCase = 0.0;
		double caseWeight = 0.0;
		double unitValue = 0.0;
		double dimensionDouble = 0.0;
		
		CommodityInfo[] infoCom1 = null;
		
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmt3 = null,pstmtTransetUp = null;
		ResultSet rs2 = null,rsSet = null;
		ResultSet rs3 = null;
		
		//Changed by Rohan on 03-june define variables for manual shipment print lable.start
		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			String userID = genericUtility.getValueFromXTRA_PARAMS( xtraParam, "LoginCode" );
			//Changed by sumit on 28/09/12 checking whether shipment is already confirmed or not start.
			System.out.println(" forced flag:::>>>>:: ["+forcedFlag+"]");
			ITMDBAccessLocal itmDBAccessLocal = new ITMDBAccessEJB();
			//Changed by Rohan on 30-07-13 for non inventory manual shipment
			
			sql = "SELECT SHIPMENT.CONFIRMED, SHIPMENT.NO_PALLET, SHIP_DOCS.REF_SER " +
					" FROM SHIPMENT, SHIP_DOCS WHERE SHIPMENT.SHIPMENT_ID = ? " +
					" AND SHIP_DOCS.SHIPMENT_ID = SHIPMENT.SHIPMENT_ID";
			
			sql = "SELECT SHIPMENT.CONFIRMED, SHIPMENT.NO_PALLET FROM SHIPMENT WHERE SHIPMENT.SHIPMENT_ID = ?  "; 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				confirmed = rs.getString("CONFIRMED");
				noOfPallet = rs.getInt("NO_PALLET");
				
			}
			
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			//Changed by Rohan on 30-07-13 for getting ref ser.start
			sql = "SELECT REF_SER FROM SHIP_DOCS WHERE SHIPMENT_ID = ?  "; 
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				refSer = checkNull(rs.getString("REF_SER"));
			}
			System.out.println("-- confirmed ["+confirmed+"] noOfPallet ["+noOfPallet+"] refSer ["+refSer.trim()+"]");
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			//Changed by Rohan on 30-07-13 for getting pallet No .start
			
			if( "Y".equalsIgnoreCase(confirmed))
			{
				errString = itmDBAccessLocal.getErrorString("","SHIPALLCON","");
			}
			else
			{
				if( "S-DSP".equalsIgnoreCase(refSer.trim()) && "N".equalsIgnoreCase(forcedFlag))
				{
					errString = itmDBAccessLocal.getErrorString("","SDSPCONFIR","");
				}
				else
				{
					//Chnaged by Rohan on 31-07-13 for checking status of pallet No.start
					if(("".equalsIgnoreCase(refSer.trim())) || ("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()) ))
					{	
						
						if(noOfPallet <= 0 && !refSer.trim().equalsIgnoreCase("S-DSP"))
						{
						
							errString = itmDBAccessLocal.getErrorString("","VTINVPALNO","");
							return errString;
						}
						
						
						
						sql = "SELECT STATUS FROM PALLET_NO WHERE PALLET_NO IN ( SELECT PALLET_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							status = checkNull(rs.getString("STATUS"));
							if((!"".equalsIgnoreCase(status) || status != null ) && !"A".equalsIgnoreCase(status) )
							{
								errString = itmDBAccessLocal.getErrorString("","VTINVPALET","");
								return errString;
								
							}
						}
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sql = "SELECT STATUS FROM CARTON_MASTER WHERE CARTON_NO IN ( SELECT CARTON_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
						
							status = checkNull(rs.getString("STATUS"));
							if((!"".equalsIgnoreCase(status) || status != null ) && !"A".equalsIgnoreCase(status) )
							{
								errString = itmDBAccessLocal.getErrorString("","VTINVCARTN","");
								return errString;
								
							}
						}
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						//Chnaged by Rohan on 21-08-13 for validating consinee name in case of manual shipment.start
						sql = "SELECT CONSIGNEE_NAME,ADDR1,ADDR2,ADDR3 FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
						
							custName = checkNull(rs.getString("CONSIGNEE_NAME")).trim();
							shipAdd1 = checkNull(rs.getString("ADDR3")).trim();
							shipAdd2 = checkNull(rs.getString("ADDR2")).trim();
							shipAdd3 = checkNull(rs.getString("ADDR1")).trim();
						}
						
						if("".equalsIgnoreCase(custName) && custName.trim().length() <= 0)
						{
							errString = itmDBAccessLocal.getErrorString("","VTINVCONSE","");
							return errString;
						}
						if(("".equalsIgnoreCase(shipAdd1) && shipAdd1.trim().length() <= 0 ) && ("".equalsIgnoreCase(shipAdd2) && shipAdd2.trim().length() <= 0 ) && ("".equalsIgnoreCase(shipAdd3) && shipAdd3.trim().length() <= 0 ))
						{
							errString = itmDBAccessLocal.getErrorString("","VTINVADDR","");
							return errString;
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						//Chnaged by Rohan on 21-08-13 for validating consinee name in case of manual shipment.end
					
					}
					
					//Changed by Rohan on 31-07-13 for checking status of pallet No.end
					sql = "SELECT COUNT(*) as COUNT FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if( rs.next())
					{
						count = rs.getInt("COUNT");
					}
					rs.close();rs = null;
					pstmt.close();pstmt = null;
					if( noOfPallet != count && !("S-DSP".equalsIgnoreCase(refSer.trim())))
					{
						errString = itmDBAccessLocal.getErrorString("","NOOFPALLET","");
					}
					else 
					{
						//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment start.
						if( updateShipment(tranId , conn, xtraParam) > 0)
						{
							System.out.println(" update sucessfully ");
						}
						//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment end.
						
						//Changed by sumit on 28/09/12 checking whether shipment is already confirmed or not end.					
						sql = "SELECT LORRY_NO,TRAN_CODE,LR_NO,LR_DATE, pro_no FROM SHIPMENT WHERE SHIPMENT_ID = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							lorryNo = checkNull(rs.getString(1));
							tranCode = checkNull(rs.getString(2));
							lrNo = checkNull(rs.getString(3));
							lrDate = rs.getTimestamp(4);
							proNo = checkNull(rs.getString(5));
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						//changed by sankara on 16/08/13 tran code is blank in shipment header in case of manual shipment.start.
						
						if(("".equalsIgnoreCase(refSer.trim()) || ("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim())) ) && ( "".equalsIgnoreCase(tranCode.trim()) || tranCode == null) && tranCode.trim().length() <= 0 )
						{
							errString = itmDBAccessLocal.getErrorString("","VTTRANCDBK","");
							return errString;
						}	
					   //changed by sankara on 16/08/13 tran code is blank in shipment header in case of manual shipment.end.	
						//Changed by sumit on 28/09/12 wrong sql
						//sql = "SELECT PRONO_LASTe FROM transporter WHERE tran_code = ? ";
						//sql = "SELECT PRONO_LAST FROM transporter WHERE tran_code = ? ";
						//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] adding columns
						sql = "SELECT PRONO_LAST,CHK_DIGIT_YN FROM transporter WHERE tran_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranCode);
						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							tranProNo = rs.getString(1);
							//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] getting CHK_DIGIT_YN
							chDigitYn = rs.getString("CHK_DIGIT_YN");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sSQLUpdatSord = "UPDATE SORDER SET SHIP_STATUS = ? WHERE SALE_ORDER IN (SELECT SORD_NO FROM DESPATCH WHERE DESP_ID = ?)";
						pstmtSord = conn.prepareStatement(sSQLUpdatSord);
						sql = " SELECT TRAN_WINDOW FROM TRANSETUP WHERE REF_SER = ? ";
						pstmtTransetUp = conn.prepareStatement(sql);
						//Changed by sumit on 05/10/12 added condition where ref_ser = S-DSP
						//sql = "SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ?";
						sql = "SELECT REF_ID, REF_SER FROM SHIP_DOCS WHERE SHIPMENT_ID = ? AND DESP_CONFIRMED = 'N'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, tranId);						
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							refId = rs.getString(1);
							//Changed by sumit on 05/10/12 getting value of refser
							refSerNew = checkNull(rs.getString("REF_SER"));
							
							System.out.println("ref_id " +refId+" refSer: ["+refSerNew.trim()+"]");
							//Changed by sumit on 05/10/12 add condition do not call despatch confirmed other than despatch
							//if(refId != null && refId.trim().length() > 0 )
							if(refId != null && refId.trim().length() > 0 && "S-DSP".equalsIgnoreCase(refSerNew.trim()))
							{
								
								
								System.out.println("after commit in ShipmentConf for id ["+ refId +"]");
								errString = confirmShipment("despatch", refId.trim(), xtraParam, forcedFlag, conn);
								
								//Changed by sumit on 05/10/12 updating ship_docs  start.
								System.out.println(" errString ---------------- > ["+errString);		
								
								//Changed by sumit on 05/10/12 updating desp_confirmed in ship docs table
								if ( newErro == null || newErro.trim().length() == 0)
								{
									//Changed by Rohan on 14-08-13 for passing argument in refser
									//updateShipDocs(refId, findError(errString));
								updateShipDocs(refId,refSerNew, findError(errString));
								//}
								
								//errList.add(errString);
								// check digit to be removed from pro_no before update
								//if(proNo.length() > tranProNo.length())
								//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] commenting this, as not required for despatch start
								if((tranProNo != null && proNo != null) && proNo.length() > tranProNo.length())
								{
									proNo = proNo.substring(0,tranProNo.length());
								}
								//Changed by sumit on 13/03/13 of Req.Id [WM3ASUN007] commenting this, as not required for despatch end
									
								sql = "UPDATE DESPATCH SET LORRY_NO = ?, SHIPMENT_ID = ?, TRAN_CODE = ?, LR_NO  = ?, LR_DATE = ?, pro_no = ? WHERE DESP_ID = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lorryNo);
								pstmt1.setString(2, tranId);
								pstmt1.setString(3, tranCode);
								pstmt1.setString(4, lrNo);
								pstmt1.setTimestamp(5, lrDate);
								pstmt1.setString(6, proNo);
								pstmt1.setString(7, refId);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								//conn.commit();
								
								//changed by sumit on 12/04/13 of Req>ID [WM3ASUN007] updating lr number in invoice against desp_id start.
								sql = "UPDATE INVOICE SET LR_NO  = ? WHERE DESP_ID = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, lrNo);
								pstmt1.setString(2, refId);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								
								//Removing check digit number from pro_no and then updating in PRONO_LAST of TRANSPORTER.
								
								if((tranProNo != null && proNo != null) && proNo.length() > tranProNo.length())
								{
									proNo = proNo.substring(0,tranProNo.length());
								}
								else if( tranProNo == null && "Y".equalsIgnoreCase(chDigitYn))
								{
									proNo = proNo.substring(0,proNo.length() - 1);
								}
								System.out.println(" pro_no ["+proNo+"]");
								//changed by sumit on 12/04/13 of Req>ID [WM3ASUN007] updating lr number in invoice against desp_id end.
								
								sql = "UPDATE TRANSPORTER SET PRONO_LAST  = ? WHERE TRAN_CODE = ?";
								pstmt1 = conn.prepareStatement(sql);
								pstmt1.setString(1, proNo);
								pstmt1.setString(2, tranCode);
								pstmt1.executeUpdate();
								pstmt1.close();
								pstmt1 = null;
								//conn.commit();		
								
								//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .start
								pstmtSord.setString(1, "C");
								pstmtSord.setString(2, refId);
								int updateSorder = pstmtSord.executeUpdate();
								
								if(updateSorder > 0)
								{
									System.out.println(" SORDER UPDATE COUNT ["+updateSorder+"]");
								}
								pstmtSord.clearParameters();
								//Changed By Pragyan WM3DSUN015 To update status in Sorder as InProcess after .end
									
							}
							//changed by sankara on 12-07-13 for confirmation of consumption issue start....
							else
							{
								
								pstmtTransetUp.setString(1, refSer.trim());						
								rsSet = pstmtTransetUp.executeQuery();
								if( rsSet.next() )
								{
									winName = rsSet.getString("TRAN_WINDOW");									
								}					
							    objName = winName.substring(2);
								System.out.println("OBJNAME=="+objName);
                                errString = confirmShipment( objName, refId.trim(), xtraParam, forcedFlag, conn);
								System.out.println(" errString ---------------- > ["+errString);
								//Changed by Rohan on 14-08-13 for passing argument in refser
								
								updateShipDocs(refId,refSerNew, findError(errString));
								
								//or id="VTSUCC1" type="P" column_name="">
								<message><![CDATA[Prompt : Transaction confirmed!]]></message>
								<description><![CDATA[Transaction Confirmed Sucessfully.]]></description>
								<trace><![CDATA[]]></trace>
								<redirect>1</redirect>
								</error>
								</Errors>
								</Root>]
								
								//if(errString != "" && errString.)error string should not check as it will call for multiple times.
								
							}
							//changed by sankara on 12-07-13 for confirmation of consumption issue end....
						}
						
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
						if(rs != null)
						{
							rs.close();
							rs = null;
						}

						if(pstmtTransetUp != null)
						{
							pstmtTransetUp.close();
							pstmtTransetUp = null;
						}
						if(rsSet != null)
						{
							rsSet.close();
							rsSet = null;
						}
						
						
						//Changed by sumit on 05/10/12 adding condition to check whether all despatch are confirmed or not 
						if( isCountSame(tranId, conn) || !("S-DSP".equalsIgnoreCase(refSer.trim())))
						{
							//Changed by Rohna on 22-07-13 for generating UPS/FEDEX lable in case of manual shipment.start
									
							
							if("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()) || "".equalsIgnoreCase(refSer.trim()))
							{
								
								sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
				    				 +" S.SHIPMENT_ID = ? ";	
				    			PreparedStatement pstmtTrans = conn.prepareStatement(sql);
				    			pstmtTrans.setString(1, tranId);
								ResultSet rsTrans = pstmtTrans.executeQuery();
								if(rsTrans.next())
								{
									serviceCode = checkNull(rsTrans.getString("SERVICE_CODE"));
									dimensionDouble = rsTrans.getDouble("PACK_SIZE");
								}
									
								if(pstmtTrans != null)
								{
									pstmtTrans.close();
									pstmtTrans = null;
								}
								
								if(rsTrans != null)
								{
									rsTrans.close();
									rsTrans = null;
								}
								
								
								if(serviceCode != null && serviceCode.trim().length() > 0)
								{
										
										
										
										System.out.println("Enetr in manual Shipment whit shipment ID"+tranId);
										adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
										defPh = discommon.getDisparams("999999","SHIPPER_DEFAULT_PH",conn);				
										com.adsionline.ADSIShippingWS.Service service = new com.adsionline.ADSIShippingWS.Service();
										NameValuePair[] additionalFields = new NameValuePair[1];
										Package2[] packgeList = null;
										ArrayList<CommodityInfo> comodityInfoList = new ArrayList<CommodityInfo>();
										System.out.println("After disparm value"+adsiShippingURL);
										long timeBefore = System.currentTimeMillis();
										locator = new ADSIShippingLocator();
							    		soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
							    		long timeAfter = System.currentTimeMillis();
							    		System.out.println("Differance In Tme for connecting to ADSI Server time differance in seconds["+(timeAfter-timeBefore)/1000+"]");
							    		
							    		if(soap.testADSIServer())
							    		{
							    			 
							    			System.out.println("---ADSI Server Connected---");
							    			//Changed by Rohan on 30-07-13 for generating UPS/FEDEX lable in case of inventory manual shipment.start
							    			
							    			if("D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()))
							    			{
							    				System.out.println("************Enter in manual shiment for inventory item ******************************");
								    			sql = "SELECT S.CONSIGNEE_NAME,S.ADDR1,S.ADDR2,S.ADDR3,S.CITY,S.COUNT_CODE,S.STATE_CODE,S.PIN,S.PKG_DESCR,S.UNIT_VALUE,S.ITEM_DESCR,S.FRT_LIST,S.TELE1 "
								    				 +" ,S.GROSS_WEIGHT,S.NO_ART"	
									    			  +"  FROM SHIPMENT S WHERE S.SHIPMENT_ID = ? ";
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													
													//custCodeDlv = checkNull(rs.getString("CUST_CODE"));
													custName = checkNull(rs.getString("CONSIGNEE_NAME"));
													//custShName = checkNull(rs.getString("SH_NAME"));
													shipAdd1 = checkNull(rs.getString("ADDR1"));
													shipAdd2 = checkNull(rs.getString("ADDR2"));
													shipAdd3 = checkNull(rs.getString("ADDR3"));
													shipCity = checkNull(rs.getString("CITY"));
													
													shipCountry = checkNull(rs.getString("COUNT_CODE"));
													//shipPh = checkNull(rs.getString("TELE1"));
													//shipMailId = checkNull(rs.getString("EMAIL_ADDR"));
													//shipFax = checkNull(rs.getString("FAX"));
													stateCodeDlv = checkNull(rs.getString("STATE_CODE"));
													shipPin = checkNull(rs.getString("PIN"));
													
													pkgDescr = checkNull(rs.getString("PKG_DESCR"));
													itemDescr = checkNull(rs.getString("ITEM_DESCR"));
													unitValue = rs.getDouble("UNIT_VALUE");
													//frtList = checkNull(rs.getString("FRT_LIST"));
													//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone
													phone  = checkNull(rs.getString("TELE1"));
													caseWeight = rs.getDouble("GROSS_WEIGHT");
													noArt = rs.getDouble("NO_ART");
													
												}
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												/*
												if(!"".equalsIgnoreCase(frtList) && frtList.trim().length() > 0)
												{	
													sql = "SELECT FRT_TERM FROM FREIGHT_LIST WHERE FRT_LIST = ? ";
													pstmt = conn.prepareStatement(sql);
													pstmt.setString(1, frtList);
													rs = pstmt.executeQuery();
													if(rs.next())
													{
														frtTerm = checkNull(rs.getString("FRT_TERM"));
													}
													pstmt.close();pstmt = null;
													rs.close();rs = null;
													
												}		
												
							    			
								    			shipment2 = new Shipment2();	
								    			recipient = new Address();//To set the Address
								    			//Setting Shipment ID 
								    			shipment2.setID(refId);
								    			//recipient.setID(custCodeDlv);//Customer Code
								    			recipient.setID(custName);//Customer Code
								    			recipient.setContact(custName);//Customer Name
								    			recipient.setCompany(custName);//Customer Company Name
								    			recipient.setAddress1(shipAdd1);//Customer Adress1
								    			recipient.setAddress2(shipAdd2);//Customer Adress2
								    			recipient.setAddress3(shipAdd3);//Customer Adress3
								    			recipient.setCity(shipCity);//Customer Adress City
								    			
								    			/*sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
								    				 +" S.SHIPMENT_ID = ? ";	
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													serviceCode = checkNull(rs.getString("SERVICE_CODE"));
													dimensionDouble = rs.getDouble("PACK_SIZE");
												}
														
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
												
								    			recipient.setState(stateCodeDlv);//Customer Adress State
								    			Country country = soap.getCountry("ISO3", shipCountry).getResponseCountry();
								    			recipient.setCountry(country);//Customer Adress Country
								    			//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone.start
								    			//recipient.setPhone(defPh);
								    			if(!"".equalsIgnoreCase(phone) && phone.trim().length() > 0)
								    			{
								    				recipient.setPhone(phone);
								    				System.out.println("Setting phone npo"+phone);
								    			}
								    			else
								    			{
								    				recipient.setPhone(defPh);
								    			}
								    			//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone.end
								    			recipient.setEMail("");
								    			recipient.setFax("");
								    			recipient.setPostalCode(shipPin);
								    			
								    			shipment2.setRecipient(recipient);
								    			service.setFriendlyName(serviceCode);
								    	
								    			shipment2.setService(service);
								    			System.out.println("Service name is :"+shipment2.getService());
								    			System.out.println("Service code is :"+serviceCode);
								    			/*
								    			if(!"".equalsIgnoreCase(frtTerm) && frtTerm.trim().length() > 0)
								    			{	
								    				shipment2.setTerms(EnumTerms.fromString(frtTerm));
								    				
								    			}
								    			else
								    			{
								    				shipment2.setTerms(EnumTerms.trmSHIPPER);
								    			}
								    			
							    				
								    			shipment2.setTerms(EnumTerms.trmSHIPPER);
								    			//HardCoded valus needs to be change later.
								    			shipment2.setSignatureReleaseFlag(false);//false  As per Taro
								    			shipment2.setSignatureRequiredFlag(false);//true Required As per Taro
								    			shipment2.setSaturdayDeliveryFlag(false);// Taro Need To clerify "what is based on service level?"
								    	    			
								    			java.util.Date date = new java.util.Date();
								        		Calendar calendar = Calendar.getInstance();
								        		calendar.setTime(date);
								        		shipment2.setShipDate(calendar);
			
								        		shipment2.setConsigneeAccount("");//Not Required As per Taro
								        		shipment2.setThirdPartyBillingFlag(false);//Not Required As per Taro
								        		shipment2.setThirdPartyBillingAccount("");//Not Required As per Taro
								        		Address addrs = new Address();//Need to capture if required
								        		shipment2.setThirdPartyBillingAddress(addrs);//Not Required As per Taro
								    			
								        		shipment2.setHALFlag(false);//Need clarification from Taro
								        		shipment2.setHALAddress(addrs);//Need clarification from Taro
								        		
								        		shipment2.setManifestAction(EnumManifestAction.maRELEASE);//As per taro by default its maRELEASE
								        		
								        		
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		
								        		//shipment2.setShipperReference(ptcn);//As per Taro use PTCN(Pick Ticket Control No)
								        		shipment2.setShipperReference(tranId);
								        		
								        		//need to ask
								        		//shipment2.setConsigneeReference(custPord);//As per Taro it is sorder.cust_pord
								        		shipment2.setConsigneeReference(refId);//As per Taro it is sorder.cust_pord
								        		shipment2.setShipperName("Taro Fulfillment");//Needs to change after clarity
								        		shipment2.setTimeInTransit(EnumTimeInTransit.tntFOREVER);//As per taro its tntFOREVER
								        		shipment2.setSaturdayDeliveryFlag(false);//Need to discuss with Taro
								        		
								        		System.out.println("Shipment setting completr"+shipment2.getID());
								        		//Adding package to the shipment
								        	    /*
								        		sqlSize = "SELECT LENGTH , WIDTH , HEIGHT FROM PACKING WHERE PACK_CODE = (SELECT PACK_CODE FROM CARTON_MASTER WHERE CARTON_NO = ? )";
								        		 pstmt2 = conn.prepareStatement(sqlSize);
								        		 
								        		
								        		if("D-ISS".equalsIgnoreCase(refSer.trim()))
								        		{
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.start
								        			/*
								        			sqlItem = " SELECT I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG,"
								        					+" D.ITEM_CODE  FROM DISTORD_ISSDET P,ITEM I,ITEM_LOT_PACKSIZE L,  DISTORDER_DET D "
								        					+" WHERE P.TRAN_ID IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? )  "
								        					+" AND P.CARTON_NO = ? AND P.PALLET_NO = ?  AND D.DIST_ORDER = P.DIST_ORDER AND P.LINE_NO_DIST_ORDER = D.LINE_NO "
								        					+" AND I.ITEM_CODE = D.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE";
								        			
								        			
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.start
							    					
								        			sqlItem = "SELECT  I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG, "
								        					  +" P.ITEM_CODE  FROM DISTORD_ISSDET P,ITEM I,ITEM_LOT_PACKSIZE L,SHIPMENT_CONTENT C,SHIP_DOCS S " 
								        					  +" WHERE P.TRAN_ID IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? )  " 
								        					  +" AND S.SHIPMENT_ID = C.SHIPMENT_ID AND C.CARTON_NO = ? AND C.PALLET_NO = ?  " 
								        					  +" AND I.ITEM_CODE = P.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE";
								        			
								        			
								        			

								        		}
								        		else if("C-ISS".equalsIgnoreCase(refSer.trim()))
								        		{
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.start
								        			/*
								        			sqlItem =" SELECT I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG,P.ITEM_CODE "
								        					 +" FROM CONSUME_ISS_DET P,ITEM I,ITEM_LOT_PACKSIZE L " 
								        					 +" WHERE P.CONS_ISSUE IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ) " 
								        					 + " AND P.CARTON_NO = ? and P.PALLET_NO = ? "
								        					+" AND I.ITEM_CODE = P.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE ";
								        					
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.end
							    					
								        			sqlItem =" SELECT I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG, "
								        					 +" P.ITEM_CODE FROM CONSUME_ISS_DET P,ITEM I,ITEM_LOT_PACKSIZE L, SHIPMENT_CONTENT C,SHIP_DOCS S " 
								        					+" WHERE P.CONS_ISSUE IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ) " 
								        					+" AND C.SHIPMENT_ID = S.SHIPMENT_ID AND C.CARTON_NO = ? and C.PALLET_NO = ? "
								        					+" AND I.ITEM_CODE = P.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE ";
								        		}
								        		else if("P-RET".equalsIgnoreCase(refSer.trim()))
								        		{
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.start
								        			/*
								        			sqlItem = " SELECT I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG,P.ITEM_CODE "
								        			 +" FROM PORCPDET P,ITEM I,ITEM_LOT_PACKSIZE L "
								        			 +" WHERE P.TRAN_ID IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ) " 
								        			 + " AND P.CARTON_NO = ? and P.PALLET_NO = ? "
								        			 +" AND I.ITEM_CODE = P.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE ";
								        			
							    					
								        			sqlItem ="SELECT I.HARMONIZATION_DESCR,I.HARMONIZATION_NO,I.HAZARDOUS,L.SHIPPER_SIZE,L.GROSS_WEIGHT,I.COUNT_CODE__MFG, "
								        					+" P.ITEM_CODE FROM PORCPDET P,ITEM I,ITEM_LOT_PACKSIZE L ,SHIPMENT_CONTENT C,SHIP_DOCS S " 
								        					+" WHERE P.TRAN_ID IN(SELECT REF_ID FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ) AND C.SHIPMENT_ID = S.SHIPMENT_ID" 
								        					+" AND C.CARTON_NO = ? and C.PALLET_NO = ? AND I.ITEM_CODE = P.ITEM_CODE AND I.ITEM_CODE = L.ITEM_CODE ";
								        			//Changed By Rohan on 12-08-13 for considering carton no and pallet no from shipment content.end
			
								        		}
								        			
								        		pstmt3 = conn.prepareStatement(sqlItem);
								        		
								        		sqlNoPkg = "SELECT COUNT(*) AS NO_OF_PKG FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ";
								        		pstmt2 = conn.prepareStatement(sqlNoPkg);
								        		pstmt2.setString(1, tranId);
												rs2 = pstmt2.executeQuery();
												if(rs2.next())
												{
													noOfPkgs = rs2.getInt("NO_OF_PKG");
												}
												packgeList = new Package2[noOfPkgs];
												infoCom1 = new CommodityInfo[noOfPkgs] ;
												System.out.println("noOfPkgs"+noOfPkgs);
								        		//sql = "SELECT COUNT(*) AS NO_OF_PACKAGE FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ";
								        		sql = "SELECT C.CARTON_NO,C.PALLET_NO,C.LENGTH,C.WIDTH,C.HEIGHT FROM SHIPMENT_CONTENT C WHERE  C.SHIPMENT_ID = ? ";
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												while(rs.next())
												{
													
													cartonNo = checkNull(rs.getString("CARTON_NO"));
													palleNo = checkNull(rs.getString("PALLET_NO"));
													slength = rs.getDouble("LENGTH");																					
													width =  rs.getDouble("WIDTH");;
													height = rs.getDouble("HEIGHT");
													//noArt = rs.getDouble("NO_ART");
													//caseWeight = rs.getDouble("GROSS_WEIGHT");
													caseWeightTotal = noArt * caseWeight;
													cartonNoList.add(cartonNo);
													
													pkg2 =  new Package2();
													pkg2.setID(cartonNo);
													pkg2.setWeight(caseWeightTotal);
													System.out.println("Case total weight"+caseWeightTotal);
													
													strlen = Double.toString(slength);
													widthStr =Double.toString(width);
													heightStr = Double.toString(height);
													
													System.out.println("strlen"+strlen+"widthStr"+widthStr+"heightStr"+heightStr);
													
													additionalFields[0] = new NameValuePair("carton_no", cartonNo);
													pkg2.setDimension(strlen+"x"+widthStr+"x"+heightStr);	
													System.out.println("Dimension of master package----< :"+pkg2.getDimension());
													pkg2.setTrackingNumber("");//Not Required Optional
													if(!"".equalsIgnoreCase(pkgDescr.trim()) && pkgDescr.trim().length() > 0)
													{	
														pkg2.setDescription(pkgDescr);//As per Taro 
													}
													else
													{
														pkg2.setDescription("Pharmaceuticals");
													}
													pkg2.setOversizeFlag(false);//As per Taro 
													pkg2.setDeclaredValueFlag(false);//As per Taro 
													pkg2.setDeclaredValueAmount(0.0);//As per Taro not required
													pkg2.setDeclaredValueCustoms(0.0);//As per Taro not required
													pkg2.setBillOfLadingComment("");//As per Taro not required
													pkg2.setAdditionalHandlingFlag(false);//As per Taro false
													pkg2.setPackaging(EnumPackageType.pkgCUSTOM);//As per Taro default pkgCUSTOM
													pkg2.setCODFlag(false);//As per Taro false
													pkg2.setCODAmount(0.0);//As per Taro not required 
													pkg2.setCODPaymentType(0);//As per Taro not required 
													Address addr = new Address();
													pkg2.setCODReturnAddress(addr);//As per Taro not required 
													pkg2.setAdditionalFields(additionalFields);
													//setting commdity information.start
													
													
													pstmt3 = conn.prepareStatement(sqlItem);
													pstmt3.setString(1,tranId);
													pstmt3.setString(2,cartonNo);
													pstmt3.setString(3,palleNo);
													rs3 = pstmt3.executeQuery();
													while(rs3.next())
													{	
													
														harmonizDescr = checkNull(rs3.getString("HARMONIZATION_DESCR"));
														harmonizCode = checkNull(rs3.getString("HARMONIZATION_NO"));
														itemCode = checkNull(rs3.getString("ITEM_CODE"));
														//need to check
														System.out.println("ITEM CODE=============>"+itemCode);
														originCountry = checkNull(rs3.getString("COUNT_CODE__MFG"));
														packSizeCase = rs3.getDouble("SHIPPER_SIZE");
														caseWeight = rs3.getDouble("GROSS_WEIGHT");
														hazardous = checkNull(rs3.getString("HAZARDOUS"));
														System.out.println("Enter in while");
														if("Y".equalsIgnoreCase(hazardous))
														{
															pkg2.setHazmatFlag(true);
														}
														else
														{
															pkg2.setHazmatFlag(false);
														}
														
														pkg2.setCODReturnAddress(addr);//As per Taro not required
														comInfo = new CommodityInfo();
														//infoCom1 = new CommodityInfo[1] ;
														comInfo.setDescription(harmonizDescr);//As per taro Harmonization description
														comInfo.setQuantity(noArt * packSizeCase);
														//comInfo.setQuantity();
														comInfo.setHarmonizedCode(harmonizCode);//As per Taro Haromonization code
														//unitValue = 0;//getRateStdoum(saleOrder, itemCode, conn);
														if(unitValue > 0)
														{	
															comInfo.setUnitValue(unitValue);
														}
														else
														{
															comInfo.setUnitValue(0);
														}
														comInfo.setUnitWeight(caseWeight);
														Country originCountryMfg = soap.getCountry("ISO3", originCountry).getResponseCountry();
														comInfo.setOriginCountry(originCountryMfg);
														comInfo.setNaftaFlag(false);//False as Per Taro Need To change canda/Mexico as true.
														//Calendar calendar1 = Calendar.getInstance();
														comInfo.setExportNumberExpireDate(calendar);
														comInfo.setUnitOfMeasure(EnumUnitOfMeasure.uomPCS);
														System.out.println("comdity info"+comInfo.getDescription());
														infoCom1[commCnt] = comInfo;
														commCnt++;
														System.out.println("Commodity Info List"+infoCom1.toString());
																
													}
													if(pstmt3 != null)
													{
														pstmt3.close();
														pstmt3 = null;
													}
													if(rs3 != null)
													{
														rs3.close();
														rs3 = null;
													}
													pkg2.setCommodityInfos(infoCom1);
													packgeList[pkg]=pkg2;
													pkg++;
												}
														
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
												if(pstmt2 != null)
												{
													pstmt2.close();
													pstmt2 = null;
												}
												
												if(rs2 != null)
												{
													rs2.close();
													rs2 = null;
												}
							    			}//if close for refser
							    			
							    			//Changed by Rohan on 30-07-13 for generating UPS/FEDEX lable in case of inventory manual shipment.end
							    			//Changed by Rohan on 30-07-13 for generating UPS/FEDEX lable in case of non inventory manual shipment.start
							    			
						    				//if("".equalsIgnoreCase(refSer) && refSer.trim().length() <= 0 || "D-ISS".equalsIgnoreCase(refSer.trim()) || "C-ISS".equalsIgnoreCase(refSer.trim()) || "P-RET".equalsIgnoreCase(refSer.trim()))
											//{
												System.out.println("************Enter in manual shiment ******************************");
								    			sql = "SELECT S.CONSIGNEE_NAME,S.ADDR1,S.ADDR2,S.ADDR3,S.CITY,S.COUNT_CODE,S.STATE_CODE,S.PIN,S.NO_ART,S.PKG_DESCR,S.UNIT_VALUE,S.ITEM_DESCR,S.FRT_LIST,S.TELE1,S.GROSS_WEIGHT " 
									    			  +"  FROM SHIPMENT S WHERE S.SHIPMENT_ID = ? ";
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													
													//custCodeDlv = checkNull(rs.getString("CUST_CODE"));
													custName = checkNull(rs.getString("CONSIGNEE_NAME"));
													//custShName = checkNull(rs.getString("SH_NAME"));
													shipAdd1 = checkNull(rs.getString("ADDR1"));
													shipAdd2 = checkNull(rs.getString("ADDR2"));
													shipAdd3 = checkNull(rs.getString("ADDR3"));
													shipCity = checkNull(rs.getString("CITY"));
													
													shipCountry = checkNull(rs.getString("COUNT_CODE"));
													//shipPh = checkNull(rs.getString("TELE1"));
													//shipMailId = checkNull(rs.getString("EMAIL_ADDR"));
													//shipFax = checkNull(rs.getString("FAX"));
													stateCodeDlv = checkNull(rs.getString("STATE_CODE"));
													shipPin = checkNull(rs.getString("PIN"));
													
													pkgDescr = checkNull(rs.getString("PKG_DESCR"));
													itemDescr = checkNull(rs.getString("ITEM_DESCR"));
													unitValue = rs.getDouble("UNIT_VALUE");
													noArt = rs.getDouble("NO_ART");
													//frtList = checkNull(rs.getString("FRT_LIST"));
													//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone
													phone  = checkNull(rs.getString("TELE1"));
													caseWeight = rs.getDouble("GROSS_WEIGHT");
													
												}
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
												if(!"".equalsIgnoreCase(frtList) && frtList.trim().length() > 0)
												{	
													sql = "SELECT FRT_TERM FROM FREIGHT_LIST WHERE FRT_LIST = ? ";
													pstmt = conn.prepareStatement(sql);
													pstmt.setString(1, frtList);
													rs = pstmt.executeQuery();
													if(rs.next())
													{
														frtTerm = checkNull(rs.getString("FRT_TERM"));
													}
													pstmt.close();pstmt = null;
													rs.close();rs = null;
													
												}
															
								    			shipment2 = new Shipment2();	
								    			recipient = new Address();//To set the Address
								    			//Setting Shipment ID 
								    			shipment2.setID(refId);
								    			//recipient.setID(custCodeDlv);//Customer Code
								    			recipient.setID(custName);//Customer Code
								    			recipient.setContact(custName);//Customer Name
								    			//Chnaged by Rohan on 20-08-13 for set company blank.
								    			//recipient.setCompany(custName);//Customer Company Name
								    			recipient.setCompany("");//Customer Company Name
								    			
								    			//Chnaged by Rohan on 14-07-13 for ship label show address in revers order.start
								    			
								    			recipient.setAddress1(shipAdd1);//Customer Adress1
								    			recipient.setAddress2(shipAdd2);//Customer Adress2
								    			recipient.setAddress3(shipAdd3);//Customer Adress3
								    			
								    			
								    			//Chnaged by Rohan on 21-08-13 for shipAddrs 3 is blank then set address 2.start
								    			
								    			recipient.setAddress1(shipAdd3);//Customer Adress1
								    			recipient.setAddress2(shipAdd2);//Customer Adress2
								    			recipient.setAddress3(shipAdd1);//Customer Adress3
								    			
								    			if(!"".equalsIgnoreCase(shipAdd3) && shipAdd3.trim().length() > 0)
								    			{
								    				recipient.setAddress1(shipAdd3);//Customer Adress1
									    			recipient.setAddress2(shipAdd2);//Customer Adress2
									    			recipient.setAddress3(shipAdd1);//Customer Adress3
								    			}
								    			else if(!"".equalsIgnoreCase(shipAdd2) && shipAdd2.trim().length() > 0)
								    			{
								    				recipient.setAddress1(shipAdd2);//Customer Adress1
									    			recipient.setAddress2(shipAdd1);//Customer Adress2
									    			recipient.setAddress3("");
								    			}
								    			else
								    			{
								    				recipient.setAddress1(shipAdd1);//Customer Adress1
									    			recipient.setAddress2("");//Customer Adress2
									    			recipient.setAddress3("");
								    			}
								    			//Chnaged by Rohan on 21-08-13 for shipAddrs 3 is blank then set address 2.end
								    			//Chnaged by Rohan on 14-07-13 for ship label show address in revers order.end
								    			recipient.setCity(shipCity);//Customer Adress City
								    			sql = "SELECT T.SERVICE_CODE,S.PACK_SIZE FROM SHIPMENT S,TRANSPORTER T  WHERE  T.TRAN_CODE = S.TRAN_CODE AND" 
								    				 +" S.SHIPMENT_ID = ? ";	
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													serviceCode = checkNull(rs.getString("SERVICE_CODE"));
													dimensionDouble = rs.getDouble("PACK_SIZE");
												}
														
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
								    			recipient.setState(stateCodeDlv);//Customer Adress State
								    			Country country = soap.getCountry("ISO3", shipCountry).getResponseCountry();
								    			recipient.setCountry(country);//Customer Adress Country
								    			//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone.start
								    			//recipient.setPhone(defPh);
								    			if(!"".equalsIgnoreCase(phone) && phone.trim().length() > 0)
								    			{
								    				recipient.setPhone(phone);
								    			}
								    			else
								    			{
								    				recipient.setPhone(defPh);
								    			}
								    			//Chnaged by Rohan on 12-08-13 for getting phone from header if it balank then set default phone.end
								    			
								    			recipient.setEMail("");
								    			recipient.setFax("");
								    			recipient.setPostalCode(shipPin);
								    			
								    			shipment2.setRecipient(recipient);
								    			service.setFriendlyName(serviceCode);
								    	
								    			shipment2.setService(service);
								    			System.out.println("Service name is :"+shipment2.getService());
								    			System.out.println("Service code is :"+serviceCode);
								    			
								    			if(!"".equalsIgnoreCase(frtTerm) && frtTerm.trim().length() > 0)
								    			{	
								    				shipment2.setTerms(EnumTerms.fromString(frtTerm));
								    				System.out.println("terms::::"+shipment2.getTerms()+"]");
								    			}
								    			else
								    			{
								    				shipment2.setTerms(EnumTerms.trmSHIPPER);
								    			}
								    			
								    			shipment2.setTerms(EnumTerms.trmSHIPPER);
								    			//HardCoded valus needs to be change later.
								    			shipment2.setSignatureReleaseFlag(false);//false  As per Taro
								    			shipment2.setSignatureRequiredFlag(false);//true Required As per Taro
								    			shipment2.setSaturdayDeliveryFlag(false);// Taro Need To clerify "what is based on service level?"
								    	    			
								    			java.util.Date date = new java.util.Date();
								        		Calendar calendar = Calendar.getInstance();
								        		calendar.setTime(date);
								        		shipment2.setShipDate(calendar);
		
								        		shipment2.setConsigneeAccount("");//Not Required As per Taro
								        		shipment2.setThirdPartyBillingFlag(false);//Not Required As per Taro
								        		shipment2.setThirdPartyBillingAccount("");//Not Required As per Taro
								        		Address addrs = new Address();//Need to capture if required
								        		shipment2.setThirdPartyBillingAddress(addrs);//Not Required As per Taro
								    			
								        		shipment2.setHALFlag(false);//Need clarification from Taro
								        		shipment2.setHALAddress(addrs);//Need clarification from Taro
								        		
								        		shipment2.setManifestAction(EnumManifestAction.maRELEASE);//As per taro by default its maRELEASE
								        		
								        		
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		shipment2.setConsigneeBillingFlag(false);//As per Taro Not Required
								        		
								        		//shipment2.setShipperReference(ptcn);//As per Taro use PTCN(Pick Ticket Control No)
								        		shipment2.setShipperReference(tranId);
								        		
								        		//need to ask
								        		//shipment2.setConsigneeReference(custPord);//As per Taro it is sorder.cust_pord
								        		shipment2.setConsigneeReference(refId);//As per Taro it is sorder.cust_pord
								        		shipment2.setShipperName("Taro Fulfillment");//Needs to change after clarity
								        		shipment2.setTimeInTransit(EnumTimeInTransit.tntFOREVER);//As per taro its tntFOREVER
								        		shipment2.setSaturdayDeliveryFlag(false);//Need to discuss with Taro
								        		
								        		sqlNoPkg = "SELECT COUNT(*) AS NO_OF_PKG FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ";
								        		pstmt2 = conn.prepareStatement(sqlNoPkg);
								        		pstmt2.setString(1, tranId);
												rs2 = pstmt2.executeQuery();
												if(rs2.next())
												{
													noOfPkgs = rs2.getInt("NO_OF_PKG");
												}
												packgeList = new Package2[noOfPkgs];
												infoCom1 = new CommodityInfo[noOfPkgs] ;
												
								        		//sql = "SELECT COUNT(*) AS NO_OF_PACKAGE FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? ";
								        		//sql = "SELECT C.CARTON_NO,C.PALLET_NO,C.GROSS_WEIGHT,S.NO_ART,C.LENGTH,C.WIDTH,C.HEIGHT,S.UNIT_VALUE,S.PKG_DESCR FROM SHIPMENT_CONTENT C,SHIPMENT S WHERE C.SHIPMENT_ID = S.SHIPMENT_ID AND" 
								        		//	+" C.SHIPMENT_ID = ? ";
												
												sql = "SELECT C.CARTON_NO,C.PALLET_NO,C.LENGTH,C.WIDTH,C.HEIGHT FROM SHIPMENT_CONTENT C WHERE  C.SHIPMENT_ID = ? ";
								    			
								    			pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tranId);
												rs = pstmt.executeQuery();
												while(rs.next())
												{
													
													cartonNo = checkNull(rs.getString("CARTON_NO"));
													palleNo = checkNull(rs.getString("PALLET_NO"));
													slength = rs.getDouble("LENGTH");																					
													width =  rs.getDouble("WIDTH");;
													height = rs.getDouble("HEIGHT");
													//noArt = rs.getDouble("NO_ART");
													//caseWeight = rs.getDouble("GROSS_WEIGHT");
													caseWeightTotal = noArt * caseWeight;
													//unitValue = rs.getDouble("UNIT_VALUE");
													cartonNoList.add(cartonNo);
													//pkgDescr = checkNull(rs.getString("PKG_DESCR"));
													pkg2 =  new Package2();
													pkg2.setID(cartonNo);
													pkg2.setWeight(caseWeightTotal);
													System.out.println("Case total weight"+caseWeightTotal);
													
													strlen = Double.toString(slength);
													widthStr =Double.toString(width);
													heightStr = Double.toString(height);
													
													System.out.println("strlen"+strlen+"widthStr"+widthStr+"heightStr"+heightStr);
													
													additionalFields[0] = new NameValuePair("carton_no", cartonNo);
													pkg2.setDimension(strlen+"x"+widthStr+"x"+heightStr);	
													System.out.println("Dimension of master package----< :"+pkg2.getDimension());
													pkg2.setTrackingNumber("");//Not Required Optional
													if(!"".equalsIgnoreCase(pkgDescr.trim()) && pkgDescr.trim().length() > 0)
													{	
														pkg2.setDescription(pkgDescr);//As per Taro 
													}
													else
													{
														pkg2.setDescription("Pharmaceuticals");
													}
													pkg2.setOversizeFlag(false);//As per Taro 
													pkg2.setDeclaredValueFlag(false);//As per Taro 
													pkg2.setDeclaredValueAmount(0.0);//As per Taro not required
													pkg2.setDeclaredValueCustoms(0.0);//As per Taro not required
													pkg2.setBillOfLadingComment("");//As per Taro not required
													pkg2.setAdditionalHandlingFlag(false);//As per Taro false
													pkg2.setPackaging(EnumPackageType.pkgCUSTOM);//As per Taro default pkgCUSTOM
													pkg2.setCODFlag(false);//As per Taro false
													pkg2.setCODAmount(0.0);//As per Taro not required 
													pkg2.setCODPaymentType(0);//As per Taro not required 
													Address addr = new Address();
													pkg2.setCODReturnAddress(addr);//As per Taro not required 
													
													pkg2.setAdditionalFields(additionalFields);
													//originCountry = checkNull(rs3.getString("COUNT_CODE__MFG"));
													pkg2.setHazmatFlag(false);
													pkg2.setCODReturnAddress(addr);//As per Taro not required
													comInfo = new CommodityInfo();
													
													comInfo.setDescription("");//As per taro Harmonization description
													comInfo.setQuantity(noArt);
													comInfo.setHarmonizedCode("");//As per Taro Haromonization code
													if(unitValue > 0)
													{	
														comInfo.setUnitValue(unitValue);
													}
													else
													{
														comInfo.setUnitValue(0);
													}
													comInfo.setUnitWeight(caseWeight);
													Country originCountryMfg = soap.getCountry("ISO3", shipCountry).getResponseCountry();
													comInfo.setOriginCountry(originCountryMfg);
													comInfo.setNaftaFlag(false);//False as Per Taro Need To change canda/Mexico as true.
													//Calendar calendar1 = Calendar.getInstance();
													comInfo.setExportNumberExpireDate(calendar);
													comInfo.setUnitOfMeasure(EnumUnitOfMeasure.uomPCS);
													
													infoCom1[commCnt] = comInfo;
													commCnt++;
															
													pkg2.setCommodityInfos(infoCom1);
													packgeList[pkg]=pkg2;
													pkg++;
												}
														
												pstmt.close();
												pstmt = null;
												rs.close();
												rs = null;
												
												if(pstmt2 != null)
												{
													pstmt2.close();
													pstmt2 = null;
												}
												
												if(rs2 != null)
												{
													rs2.close();
													rs2 = null;
												}
								    		
							    			//}
							    			//Changed by Rohan on 30-07-13 for generating UPS/FEDEX lable in case of non inventory manual shipment.end
							        		
							        		if(packgeList.length != 0  || packgeList.length > 0)
											{
												
												Timestamp currDate = new Timestamp(System.currentTimeMillis());
												shipment2.setPackages2(packgeList);
												ShipmentResultReturn2 objReturn2=soap.shipWithLabel(shipment2, ShipWithLabelType.PNG, ImageRotation.Rotation_180);
									    		System.out.println("Is successfully Executed ["+objReturn2.isSuccess()+"]");
									    		System.out.println("Message String ["+objReturn2.getMessage()+"]");
									    		
									    		if(!objReturn2.isSuccess())
									    		{
									    			//errString = getError(saleOrder + "[" + objReturn2.getMessage() +"]", "ADSISRVERR", conn);
									    			errString = getError(saleOrder + "[" + objReturn2.getMessage() +"]", "ADSISRVERR", conn);
									    			return errString;
									    		}
									    		else
									    		{
									    			//Updating ADSI getting the images.start
													ShipmentResult2 objShipResult = objReturn2.getShipmentResultResponse2();
													int  lenPacakges =  objShipResult.getPackageResults2().length;
													String value = "";
													String pckgId = "";
													String trackNo="";
													double msnNo=0.0;
													String imagePath =null;
													imagePath = discommon.getDisparams("999999","SHIP_WITH_PATH",conn);
													
													for(int pCtr = 0;pCtr < lenPacakges;pCtr++)
													{
														PackageResult2 pkg2frm =  (PackageResult2)objShipResult.getPackageResults2()[pCtr];	
														value = pkg2frm.getBase64Label();
														pckgId = pkg2frm.getPackageID();
														//change by rekha on 23-05-13.start
														pkg2frm.getAdditionalFields();
														trackNo=pkg2frm.getTrackingNumber();				
														
														cartonNo = cartonNoList.get(pCtr);
														
														System.out.println("cnumber is :"+cartonNo);
														System.out.println("Package ID["+pckgId+"]");
														System.out.println("Tracking No ["+pkg2frm.getTrackingNumber()+"]");
														System.out.println("BASE64lABEL Length["+value.length()+"]");
														BASE64Decoder decoder = new BASE64Decoder(); 
														System.out.println("Package id for carton no is :[" +decoder.decodeBuffer(pckgId) +"]");
														msnNo = pkg2frm.getMSN();
														
														System.out.println("MSN NO IN EACH PACKAGE IS :" +msnNo);
														byte[] imgBytes = decoder.decodeBuffer(value);    
														System.out.println("IMAGE Byte Length ["+imgBytes.length+"]");
														BufferedImage bufImg = ImageIO.read(new ByteArrayInputStream(imgBytes));  
														
														File imgOutFile = new File(imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+cartonNo +".png");
														
														String path = imgOutFile.getPath();
														System.out.println("Get Path ["+path+"]");
													
														File createFolder = new File(path);
														if(!createFolder.exists())
														{
															createFolder.mkdirs();
														}
														
														sql = "UPDATE CARTON_MASTER SET TRACKING_NO = ?  , SHIP_LABEL= ? , MSN_NO = ? ,STATUS = 'U' "
																+ " WHERE CARTON_NO = ?";		
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, trackNo);
														pstmt.setString(2, path );
														pstmt.setDouble(3, msnNo);
														pstmt.setString(4, cartonNo);
														System.out.println("carton number in for loop: "+cartonNo);
														System.out.println("msn no for pkg result is :" +msnNo);
														int updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("carton master update succesfully");
															
														}
														else    
														{
															System.out.println("Error in carton master updation");
														}
														//update tracking number in shipment contents.start
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														sql = " UPDATE SHIPMENT_CONTENT SET TRACKING_NO = ? WHERE SHIPMENT_ID = ? AND LINE_NO = ? ";
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, trackNo);
														pstmt.setString(2,tranId);
														pstmt.setInt(3, (pCtr+1));
														updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("SHIPMENT_CONTENT update succesfully");
															
														}
														else    
														{
															System.out.println("Error in SHIPMENT_CONTENTr updation");
														}
														//update tracking number in shipment contents.end
														//update tracking number in pallet number stauas.start
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														
														sql = " UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN (SELECT PALLET_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? AND LINE_NO = ? )";
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, tranId);
														pstmt.setInt(2, (pCtr+1));
													
														updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("PALLET_NO update succesfully");
															
														}
														else    
														{
															System.out.println("Error in PALLET_NO updation");
														}
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														///update tracking number in pallet number stauas.end
														
														///update carton no status  in carton no.start
														
														sql = " UPDATE CARTON_MASTER SET STATUS = 'U' WHERE CARTON_NO IN (SELECT CARTON_NO FROM SHIPMENT_CONTENT WHERE SHIPMENT_ID = ? AND LINE_NO = ? )";
														pstmt = conn.prepareStatement(sql);
														pstmt.setString(1, tranId);
														pstmt.setInt(2, (pCtr+1));
													
														updateCnt = pstmt.executeUpdate();
														System.out.println("update statement is :"+updateCnt);
														if(updateCnt > 0)
														{
															System.out.println("CARTON_NO update succesfully");
															
														}
														else    
														{
															System.out.println("Error in CARTON_NO updation");
														}
														if(pstmt != null)
														{
															pstmt.close();
															pstmt = null;
														}
														///update carton no status  in carton no.end
														
														String n = pkg2frm.getTrackingNumber();
														System.out.println("new tracking no  of master package :"+n);
														double n1 = pkg2.getWeight();
														
														System.out.println("new weight  of master package :"+n1);
													
														imgOutFile.createNewFile();
														ImageIO.write(bufImg, "png", imgOutFile);
														System.out.println("Write File Complete----");
															
													}
													
													OutputStream file = new FileOutputStream(imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+"Ship"+".ser" );
										    	    OutputStream buffer = new BufferedOutputStream( file );
										    	    ObjectOutput output = new ObjectOutputStream( buffer );
										   	        output.writeObject(shipment2);
										   	        output.flush();
										   	        output.close();  
										   	        file = new FileOutputStream( imagePath+ File.separator+"Manual_Ship_Label"+File.separator+ tranId + File.separator + tranId+"_"+"ShipRslt"+".ser" );
										   	        buffer = new BufferedOutputStream( file );
										    	    output = new ObjectOutputStream( buffer );
										   	        output.writeObject(objReturn2);
										   	        output.flush();
										   	        output.close();  
									    		}
								    		}
						        		    else
						        		    {
						        		    	System.out.println("---Packages not set succesfully---");
								    			//errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
								    			errString = itmDBAccessLocal.getErrorString("","VTADSIPKGE","");
								    			return errString;
						        		    }
							        			
							    		}
							    		else
							    		{
							    			System.out.println("---ADSI Server Not Connected throw error---");
							    			//errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
							    			errString = itmDBAccessLocal.getErrorString("","ADSISRVERR","");
							    			return errString;
		
							    		}
		
								}
									//Changed by Rohna on 22-07-13 for generating UPS/FEDEX lable in case of manual shipment.end
							
							}
							
							sql = "UPDATE SHIPMENT SET CONFIRMED = ? , CONF_DATE = ? WHERE SHIPMENT_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, "Y");
							pstmt.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
							pstmt.setString(3, tranId);
							pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							conn.commit();
							
							errString = itmDBAccessLocal.getErrorString("","VTCONFIRM","");
						}
					}
				}
			}
			if( pstmtSord!= null)
			{
				pstmtSord.close();
				pstmtSord = null;
			}
		}
		catch(Exception e)
		{
			try{
				conn.rollback();
			}
			catch(SQLException es)
			{
				System.out.println("Rollback exception : " + es.getMessage() + ":");
			}
			System.out.println("Exception : Shipment Confirm():" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("!! Closing Connection in Shipment Confirm() !!");
			try
			{
				if( pstmtSord!= null)
				{
					pstmtSord.close();
					pstmtSord = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}

				if(pstmtTransetUp != null)
				{
					pstmtTransetUp.close();
					pstmtTransetUp = null;
				}
				if(rsSet != null)
				{
					rsSet.close();
					rsSet = null;
				}
							
				conn.close();
				conn = null;
			}
			catch(Exception se){
				System.out.println(se.getMessage());
				throw new ITMException(se);
			}
		}
		return errString;
	}*/

	
	private  String getError(String message,String Code,Connection conn)  throws ITMException, Exception
    {
        String mainStr ="";
        
        try
        {
        	String errString = "";
        	errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Invalid Data"+"</message><description>";
			mainStr= mainStr+"ADSI ="+message+""+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new ITMException(e);
        }
        return mainStr;
    }
	
	private String confirmShipment(String businessObj, String tranIdFr,String xtraParams, String forcedFlag , Connection conn) throws ITMException
	{
		String methodName = "gbf_post";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		//Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		System.out.println("confirmShipment");
		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");

			//methodName = "";
			actionURI = "http://NvoServiceurl.org/" + methodName;
                           System.out.println(businessObj+"---"+tranIdFr+"---"+xtraParams+"---"+forcedFlag);

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,businessObj);
			rs = pStmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			System.out.println("serviceCode = "+serviceCode+" compName "+compName);
			sql = "SELECT SERVICE_URI,METHOD_NAME FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1,serviceCode);
			rs = pStmt.executeQuery();
			if ( rs.next() )//serviceURI = http://192.168.0.124/BaseE12DotNetService6-3-0-003/E12BusinessLogic6-3-0-003.asmx compName = nvo_business_object_dist_despatch   
			{
				serviceURI = rs.getString("SERVICE_URI");
                                methodName=rs.getString("METHOD_NAME");
			}
			System.out.println("serviceURI = "+serviceURI+" compName = "+compName);
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName.trim());
			aobj[1] = new String(tranIdFr.trim());
			aobj[2] = new String(xtraParams);
			aobj[3] = new String(forcedFlag);

			call.setReturnType(XMLType.XSD_STRING);

			retString = (String)call.invoke(aobj);

			System.out.println("Return string from NVO is:==>["+retString+"]");

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (pStmt != null )
				{
					pStmt.close();
					pStmt = null;
				}
				 /*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
                            System.out.println("Exception inCalling confirmed");
                            e.printStackTrace();
                            try{
                            conn.rollback();

                            }catch (Exception s)
                            {
                                System.out.println("Unable to rollback");
                                s.printStackTrace();
                            }
            				throw new ITMException(e);
                        }
		}
		return retString;
	}
	//Changed by sumit on 02/12/12 for checking null start.
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	//Changed by sumit on 02/12/12 for checking null end.
	
	//Changed by sumit on 05/10/12 retrieving error string start.
	private HashMap findError(String errorString) throws ITMException
	{
		Document errorDom = null;
		NodeList parentNodeList = null;
		NodeList childList = null;
		int parentNodeListLength ;
		String description = "";
		Element elementAttr = null;
		Map error =  new HashMap();
		String columnValue ="",nodeName= "",errType = "";
		try
		{
			if(errorString != null && errorString.trim().length()!=0)
			{
				errorDom = genericUtility.parseString(errorString); 
			}
			
			parentNodeList = errorDom.getElementsByTagName("error");
			parentNodeListLength = parentNodeList.getLength();	
			
			for(int row = 0; row < parentNodeListLength; row++)
			{
				Node childNode = parentNodeList.item(row);
				nodeName = childNode.getNodeName();
				//elementAttr = (Element)parentNode;
				errType = childNode.getAttributes().getNamedItem("type").getNodeValue();
				System.out.println(" error type ->["+errType+"]");
				if( "E".equalsIgnoreCase(errType))
				{
					description = checkNull(GenericUtility.getInstance().getColumnValueFromNode("description", errorDom.getElementsByTagName("error").item(row)));
				}				
			}	
			description = description.replace("\"", " ");
			description = description.replace("'", "");
			error.put("type", errType);
			error.put("description", description);
			System.out.println(" error description - > ["+error+"]");				
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return (HashMap) error;
	}
	//Changed by sumit on 05/10/12 retrieving error string end.
	//Changed by sumit on 05/10/12 counting ship detail start.
	private boolean isCountSame(String shipId, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0, count1 = 0;
		boolean isSame = false;
		try
		{
			
			sql = " SELECT COUNT(*) AS COUNT FROM SHIP_DOCS WHERE SHIPMENT_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count = rs.getInt("COUNT");
			}
			rs.close();rs = null;			
			pstmt.close();pstmt = null;	
			
			sql = " SELECT COUNT(*) AS COUNT FROM SHIP_DOCS S,DESPATCH D WHERE " +
					" S.SHIPMENT_ID = ? AND D.CONFIRMED = ? AND D.DESP_ID = S.REF_ID ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipId);
			pstmt.setString(2, "Y");		
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count1 = rs.getInt("COUNT");
			}
			rs.close();rs = null;			
			pstmt.close();pstmt = null;	
			if (count == count1)
			{
				isSame = true;
			}
			else
			{
				isSame = false;
			}
			System.out.println(" isSame ->["+isSame+"]");
		}		
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try {
				
				if (rs != null) 
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		return isSame;
	}
	//Changed by sumit on 05/10/12 counting ship detail end.
	
		
	
	//Changed by sumit on 05/10/12 update desp_confirmed in ship_docs ship detail start.
	//Chnaged by Rohan on 14-08-13 for passing argument
	//private String updateShipDocs ( String refId, HashMap error) throws ITMException
	//private String updateShipDocs ( String refId,String refSer, HashMap error) throws ITMException
	private String updateShipDocs ( String refId,String refSer,String sealNo ,String dataLoggerNo, HashMap error) throws ITMException
	{
		String sql = "",sSQL = "";
		//Changed By Pragyan 
		//PreparedStatement pstmt1 = null, pstmt = null;
		PreparedStatement pstmt1 = null, pstmt = null,pstmtLog = null;
		Connection conn = null;
		ConnDriver connDriver = null;
		try
		{
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			sql = " UPDATE SHIP_DOCS SET DESP_CONFIRMED = ?, ERROR_MSG = ? WHERE REF_ID = ? ";
			pstmt1 = conn.prepareStatement(sql);
			 if( "E".equals(error.get("type").toString()))
			 {
				pstmt1.setString(1, "N");
				pstmt1.setString(2, error.get("description").toString());
				pstmt1.setString(3, refId);
				//Changed by shankra on 16/08/13
				pstmt1.executeUpdate();
				
			 }
			 else
			 {
				//Changed by sumit on 20/12/12 updating wave_task_det after despatch get confirmed start.
				//CHnaged by Rohan on 14-08-13 for update wave task det in case of shipment
				 if((!"".equalsIgnoreCase(refSer) || refSer != null) && refSer.trim().length() > 0 && "S-DSP".equalsIgnoreCase(refSer.trim()))
				 {		 
					sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' WHERE REF_ID = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, refId);
					if(pstmt.executeUpdate() > 0 )
					{
						System.out.println(" wave_status of wave_task_det updated successfully");
					}
					if( pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}				
					//Changed by sumit on 20/12/12 updating wave_task_det after despatch get confirmed end.
					pstmt1.setString(1, "Y");
					pstmt1.setString(2, "");
					pstmt1.setString(3, refId);
					//Changed by shankra on 16/08/13
					pstmt1.executeUpdate();
					
					
					sSQL = "UPDATE DESPATCH SET SEAL_NO = ?, DATA_LOGGER_NO = ? WHERE DESP_ID = ? ";
					pstmtLog = conn.prepareStatement(sSQL);
					
					pstmtLog.setString(1, sealNo);
					pstmtLog.setString(2, dataLoggerNo);
					pstmtLog.setString(3, refId);
					
					if(pstmtLog.executeUpdate() > 0 )
					{
						System.out.println(" update Despatch  with  seal and  ogger no");
					}
					if( pstmtLog != null)
					{
						pstmtLog.close();
						pstmtLog = null;
					}				
					
					
				 }
				 //changed by sankara on 24/06/14 for update ship_docs in manual ship case start.
				 else
				 {
					 	pstmt1.setString(1, "Y");
						pstmt1.setString(2, "");
						pstmt1.setString(3, refId);
						pstmt1.executeUpdate();
						
						if("D-ISS".equalsIgnoreCase(refSer.trim()))
						{
							sSQL = "UPDATE DISTORD_ISS SET SEAL_NO = ?, DATA_LOGGER_NO = ? WHERE TRAN_ID = ? ";
							pstmtLog = conn.prepareStatement(sSQL);
							
							pstmtLog.setString(1, sealNo);
							pstmtLog.setString(2, dataLoggerNo);
							pstmtLog.setString(3, refId);
							
							if(pstmtLog.executeUpdate() > 0 )
							{
								System.out.println(" update Consume Issue  with  seal and  logger no");
							}
							if( pstmtLog != null)
							{
								pstmtLog.close();
								pstmtLog = null;
							}		
						}
				 }
				//changed by sankara on 24/06/14 for update ship_docs in manual ship case end.
			 }
			 //Changed by shankra on 16/08/13
			//pstmt1.executeUpdate();
			 if(pstmt1 != null)
			 {		 
				 pstmt1.close();
				 pstmt1 = null;
			 }	 
			 conn.commit();
		}
		catch( Exception e)
		{
			try
			{
                conn.rollback();                
			}
			catch (Exception s)
            {
                System.out.println("Unable to rollback");
                s.printStackTrace();
            }
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if ( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if( conn != null)
				{
					conn.close();
					conn = null;
				}
			} catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		
		return null;
	}
	//Changed by sumit on 05/10/12 update desp_confirmed in ship_docs ship detail end.
	
	//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment start
	
	//changed by sankara on 22/09/14 for site transporter start.
	//private int updateShipment(String shipmentId, Connection conn, String xtraParams) throws ITMException
	private int updateShipment(String shipmentId, String siteCodeShip, Connection conn, String xtraParams) throws ITMException
	{
		String sql = "";
		String descr = "";
		String descr1 = "";		
		String prefix = "";
		String sProno = "";
		String lrNo = "";
		String tranCode = "";
		long pronoLast = 0;
		int update = 0;		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		//changed by sankara on 03/01/14 for update pro_no
		String shiprono = "";
		String adsiFlag = "N";
		String chkDigit = "N";
		System.out.println(" sumit inside update shipment ");
		
		
		try
		{
			
			sql = "SELECT TRAN_CODE FROM SHIPMENT WHERE SHIPMENT_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipmentId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				tranCode = rs.getString("TRAN_CODE");				
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			
			//changed by sankara on 03/01/14 for update pro_no end.
			
			//changed by sankara on 22/09/14 for sitetransporter start.
			/*sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX,ADSI_FLAG,CHK_DIGIT_YN FROM transporter WHERE tran_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranCode);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				descr = rs.getString(1);
				descr1 = rs.getString(2);
				prefix = rs.getString(4);
				adsiFlag = rs.getString(5);
				chkDigit = rs.getString(6);
				if(descr1 == null || descr1.trim().length() == 0)
				{
					descr1 = checkNull(rs.getString(3));
				}
			}*/
					
			sql = " SELECT T.TRAN_NAME, ST.PRONO_LAST, ST.PRONO_FROM, ST.PREFIX, ST.ADSI_FLAG, ST.CHK_DIGIT_YN FROM SITE_TRANSPORTER ST," +
				  " TRANSPORTER T WHERE ST.TRAN_CODE = T.TRAN_CODE AND ST.TRAN_CODE = ? AND ST.SITE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranCode);
			pstmt.setString(2, siteCodeShip);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				descr = rs.getString(1);
				descr1 = rs.getString(2);
				prefix = rs.getString(4);
				adsiFlag = rs.getString(5);
				chkDigit = rs.getString(6);
				if(descr1 == null || descr1.trim().length() == 0)
				{
					descr1 = checkNull(rs.getString(3));
				}
			}
			else
			{
				sql = "SELECT TRAN_NAME, PRONO_LAST, PRONO_FROM, PREFIX, ADSI_FLAG, CHK_DIGIT_YN FROM TRANSPORTER WHERE TRAN_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranCode);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{				
					descr = rs.getString(1);
					descr1 = rs.getString(2);
					prefix = rs.getString(4);
					adsiFlag = rs.getString(5);
					chkDigit = rs.getString(6);
					if(descr1 == null || descr1.trim().length() == 0)
					{
						descr1 = checkNull(rs.getString(3));
					}
				}
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
			
			if("Y".equalsIgnoreCase(adsiFlag))
			{
				return 0;
			}
		
			if(descr1.trim().length() > 0)
			{
				pronoLast = Long.parseLong(descr1) + 1;				
			}
		
			
			
			
			
			//changed by sankara on 03/01/14 for update pro_no start.
			sql = " SELECT PRO_NO FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipmentId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				shiprono = checkNull( rs.getString("PRO_NO") );				
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}			
			/*//changed by sankara on 03/01/14 for update pro_no end.
			sql = "SELECT tran_name, PRONO_LAST , PRONO_FROM, PREFIX FROM transporter WHERE tran_code = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranCode);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				descr = rs.getString(1);
				descr1 = rs.getString(2);
				prefix = rs.getString(4);
				if(descr1 == null || descr1.trim().length() == 0)
				{
					descr1 = checkNull(rs.getString(3));
				}
			}
			if( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}			
		
			if(descr1.trim().length() > 0)
			{
				pronoLast = Long.parseLong(descr1) + 1;				
			}
		*/	// changed by sankara on 03/01/14 for update pro_no 	
			//if(pronoLast != 0)
			//{
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( pronoLast ).append( "]]></lr_no>\r\n" ); // commented to generate SSCC18 complaint number
		
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></lr_no>\r\n" ); // added to generate SSCC18 complaint number //commented as per suggestion   
					sProno = ("" + pronoLast).trim();
					System.out.println("manohar before prefix["+prefix+"]::::sProno["+sProno+"]");
					sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranCode);
					pstmt.setString(2, sProno);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						sProno = checkNull(rs.getString(1));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				System.out.println("manohar after prefix["+prefix+"]::::sProno["+sProno+"]");
				//valueXmlString.append( "<pro_no><![CDATA[" ).append( getSSCC(prefix, pronoLast, conn) ).append( "]]></pro_no>\r\n" );
				//valueXmlString.append( "<lr_no><![CDATA[" ).append( getLrNo(prefix,pronoLast,xtraParams,conn) ).append( "]]></lr_no>\r\n" ); 
				//valueXmlString.append( "<pro_no><![CDATA[" ).append( sProno ).append( "]]></pro_no>\r\n" );
				//Changed By Pragyan for paddin.start 
				int latsProlength = descr1.length();
				int currProlength = sProno.length();
				int diffProlen = 0;
				System.out.println("descr1["+descr1+"]") ;
				System.out.println("Last Pro length["+latsProlength+"]") ;
				System.out.println("Current Pro length["+currProlength+"]") ;
				System.out.println("CHECK DIGIT["+chkDigit+"]") ;
				
				if("Y".equalsIgnoreCase(chkDigit))
				{
					if(latsProlength > (currProlength - 1))
					{
						diffProlen = latsProlength - (currProlength -1);
						System.out.println("diffProlen CHECK DIGIT Y out["+diffProlen+"]");
						
						while(diffProlen > 0)
						{
							System.out.println("diffProlen CHECK DIGIT Y ["+diffProlen+"]");
							sProno = "0" + sProno;
							diffProlen--;
						}
					}
				}
				else
				{
					
					if(latsProlength > currProlength)
					{
						diffProlen = latsProlength - currProlength;
						System.out.println("diffProlen CHECK DIGIT N["+diffProlen+"]");
						
						while(diffProlen > 0)
						{
							System.out.println("diffProlen CHECK DIGIT N["+diffProlen+"]");
							sProno = "0" + sProno;
							diffProlen--;
						}
					}
					
				}
				
				System.out.println("Final sprono["+sProno+"]") ;
				//Changed By Pragyan for paddin.end
				
				lrNo = getLrNo(xtraParams, conn);
				System.out.println(" --sumit-- lrno - >["+lrNo+"]");
				if (lrNo == null || lrNo.trim().length() ==0 )
				{
					lrNo = getLrNo(xtraParams,conn);
					System.out.println("manohar before lr_no ["+lrNo+"]");
					sql = "SELECT fn_prono(?,to_char(?)) FROM dual";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranCode);
					pstmt.setString(2, lrNo);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						lrNo = checkNull(rs.getString(1));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				
					System.out.println("manohar after lr_no ["+lrNo+"]");
					
					//valueXmlString.append( "<lr_no><![CDATA[" ).append( sProno  ).append( "]]></lr_no>\r\n" ); 
				}
			// changed by sankara on 03/01/14 for update pro_no 	
			//}
				// changed by sankara on 03/01/14 for update pro_no start.		
				/*sql = "UPDATE SHIPMENT SET LR_NO = ? , PRO_NO = ? WHERE SHIPMENT_ID = ?";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, lrNo);
				pstmt.setString(2, sProno);
				pstmt.setString(3, shipmentId);
				update = pstmt.executeUpdate();
				if( update > 0)
				{
					System.out.println(" shipment updated sucessfully");
				}*/
				if(shiprono != null && shiprono.trim().length() > 0 )
				{
					sql = "UPDATE SHIPMENT SET LR_NO = ? , PRO_NO = ? WHERE SHIPMENT_ID = ?";		
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, lrNo);
					pstmt.setString(2, shiprono);
					pstmt.setString(3, shipmentId);
					update = pstmt.executeUpdate();
					if( update > 0)
					{
						System.out.println(" shipment updated sucessfully");
					}
				}
				else
				{
					if( "0".equalsIgnoreCase(sProno) )
					{
						sProno = "";
					}
					sql = "UPDATE SHIPMENT SET LR_NO = ? , PRO_NO = ? WHERE SHIPMENT_ID = ?";				
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, lrNo);
					pstmt.setString(2, sProno);
					pstmt.setString(3, shipmentId);
					update = pstmt.executeUpdate();
					if( update > 0)
					{
						System.out.println(" shipment updated sucessfully");
					}
				}	
				// changed by sankara on 03/01/14 for update pro_no end.	
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
						
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if( rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} 
			catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		
		return update;
	}
	//Changed by sumit on 10/11/12 updating lr_no and pro_no in shipment end
	//Changed by sumit on 10/11/12 generating LrNo ( Copied from shipmentIC, as per manoharan sir) start.
	private String getLrNo( String xtraParams,Connection conn) throws Exception
	{

		boolean isOdd = false;
		int sumOdd = 0, sumEven = 0;
		String inputStr = "", lrNo = "",  runNumberStr =""; //mfgCode = "0351672",
		//ibase.utility.GenericUtility genUtility = ibase.utility.GenericUtility.getInstance();
		int ctr, curDigit = 0, chkDigit = 0, padLen = 0; // runNumber = 4967235
		long runNumber=0;
		//TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
		String windowName="";
		PreparedStatement pSel = null;
		PreparedStatement pIns = null;	
		ResultSet rsSel = null;
	//	Connection conn = null;
		String mfgCode = null;
		String sSel = "";
		String sIns = "";
		String siteCode = "";
		String labelType = "";
		String labelID = "";
		String keyString = "";
		String tranIDCol = "";
		String refSer = "";
		String retString = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		try
		{
			if (mfgCode == null) 
			{
				mfgCode = "04";
			}
			System.out.println("@@@@@ getLrNo called..........");
			windowName = "w_bol_gen";
			sSel = "SELECT KEY_STRING, TRAN_ID_COL,REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			pSel = conn.prepareStatement(sSel);
			pSel.setString(1, windowName);
			rsSel = pSel.executeQuery();
			if( rsSel.next() )
			{
				keyString = rsSel.getString( "KEY_STRING" );
				tranIDCol = rsSel.getString("TRAN_ID_COL");
				refSer = rsSel.getString("REF_SER");
			}
			//refSer="BOL-NO";
			String loginCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"site_code");
			String chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String currDate = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			StringBuffer xmlValues = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>");
			xmlValues = xmlValues.append("<Header></Header>");
			xmlValues = xmlValues.append("<Detail1>");
			xmlValues.append( "<dummy>").append("<![CDATA[]]>").append("</dummy>\r\n" );
			xmlValues.append( "<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>\r\n" );
			xmlValues.append( "<tran_date>").append("<![CDATA[" + currDate + "]]>").append("</tran_date>\r\n");
			xmlValues.append( "<loginCode><![CDATA[" ).append(loginCode).append( "]]></loginCode>\r\n" );
			xmlValues.append( "<lr_no></lr_no>\r\n");
			xmlValues.append( "<bol_no></bol_no>\r\n");
			xmlValues = xmlValues.append( "</Detail1></Root>");
			
			System.out.println("refSer:["+refSer+"]:::tranIDCol:["+tranIDCol+"]::::keyString:["+keyString+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues.toString(), "BASE",CommonConstants.DB_NAME);
			
			runNumber = Long.parseLong(tg.generateTranSeqID(refSer, tranIDCol, keyString, conn));
			//Changed by sumit on 12/04/13 of Req.ID [WM3ASUN007] commenting commit from here.
			//conn.commit();
			System.out.println("runNumber:["+runNumber+"]");
	
			padLen = 16 - mfgCode.length(); // total length should be 16 excluding extension digit and check digit
			
			runNumberStr = "0000000000000000" + String.valueOf(runNumber);
			runNumberStr = runNumberStr.substring(runNumberStr.length() - padLen ,runNumberStr.length()); 
			lrNo = mfgCode + runNumberStr;
			System.out.println(  " lrNo [" + lrNo + "] lrNo.length() [" +  lrNo.length() + "]" );
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return lrNo;
	
	}
	//changed by sankara on 08/01/14 for update lr_date in despatch start.
	private int updateDespatch (String refId) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt1 = null;
		int despcount = 0;
		Connection con = null;
		ConnDriver conDriver = null;
		try
		{
			conDriver = new ConnDriver();
			con = conDriver.getConnectDB("DriverITM");
			conDriver = null;
			if(refId != null && refId.trim().length() > 0 )
			{
				System.out.println("sankara in despatc2");
				sql = " UPDATE DESPATCH SET LR_DATE  = ? WHERE DESP_ID = ? ";
				pstmt1 = con.prepareStatement(sql);
				pstmt1.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
				pstmt1.setString(2, refId);		
				despcount = pstmt1.executeUpdate();
				if(despcount > 0)
				{
					System.out.println("despcount:::::"+despcount);
					System.out.println("update lr date in despatch");
				}
			}
			if(pstmt1 != null)
			{		 
				pstmt1.close();
				pstmt1 = null;
			}
			con.commit();
		}
		catch( Exception e)
		{
			try
			{
				con.rollback();                
			}
			catch (Exception s)
			{
				System.out.println("Unable to rollback");
				s.printStackTrace();
			}
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if ( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}
				if( con != null)
				{
					con.close();
					con = null;
				}
			}
			catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}	
		return despcount;
	}
	//changed by sankara on 08/01/14 for update lr_date in despatch end.	
	//changed by sankara on 26/06/14 for duplicate pro number and manual shipment start.
	private int duplicateProNumber (String tranId, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String proNumber = ""; 
		int pronumCount = 0;

		try
		{
			sql = " SELECT PRO_NO FROM SHIPMENT WHERE SHIPMENT_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{				
				proNumber = checkNull( rs.getString("PRO_NO"));		
				System.out.println("proNumber::::::"+proNumber);
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

			if( proNumber != null && proNumber.trim().length() > 0 )
			{
				System.out.println("proNumber1::::::"+proNumber);
				sql = " SELECT COUNT(*) AS COUNT FROM SHIPMENT WHERE PRO_NO = ? AND SHIPMENT_ID <> ? " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, proNumber);
				pstmt.setString(2, tranId);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{				
					pronumCount = rs.getInt("COUNT");		
					System.out.println("pronumCount::::::"+pronumCount);
				}
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try 
			{
				if (rs != null) 
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} 
			catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}	
		return pronumCount;
	}
	private boolean isCountSameForManulShip(String shipId, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0, count1 = 0;
		boolean isSame = false;
		try
		{

			sql = " SELECT COUNT(*) AS COUNT FROM SHIP_DOCS WHERE SHIPMENT_ID = ?  AND DESP_CONFIRMED = 'N'";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, shipId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count = rs.getInt("COUNT");
			}
			rs.close();rs = null;			
			pstmt.close();pstmt = null;	

			if (count > 0)
			{
				isSame = false;
			}
			else
			{
				isSame = true;
			}
			System.out.println(" isSame ->["+isSame+"]");
		}		
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try {

				if (rs != null) 
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e2) 
			{
				throw new ITMException(e2);
			}
		}
		return isSame;
	}
	//changed by sankara on 26/06/14 for duplicate pro number and manual shipment end.
}
