<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="haz_mat_form" pageWidth="800" pageHeight="800" orientation="Landscape" columnWidth="800" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryNewPage="true">
	<property name="com.jasperassistant.designer.GridHeight" value="12"/>
	<property name="com.jasperassistant.designer.GridWidth" value="12"/>
	<property name="com.jasperassistant.designer.HGuides" value="0"/>
	<property name="com.jasperassistant.designer.DataSource" value="genral_ledger"/>
	<property name="com.jasperassistant.designer.SnapToGrid" value="false"/>
	<property name="com.jasperassistant.designer.Units" value="Inches"/>
	<property name="com.jasperassistant.designer.Grid" value="false"/>
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<reportFont name="Report_Font" isDefault="false" fontName="Courier New" size="9" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
	<parameter name="PTCN" class="java.lang.String"/>
	<queryString>
		<![CDATA[SELECT PTCN,TRACKING_NO,CARTON_NO,REF_SER,
ROUND(GROSS_WEIGHT + (CASE WHEN REF_SER = 'M-PICK' THEN
(SELECT NETT_WEIGHT FROM PACKING WHERE PACK_CODE = (SELECT PACK_CODE FROM CARTON_MASTER WHERE CARTON_NO = CARTON_NO)) ELSE 0 END) / 2.20462262185 , 5) AS GROSS_WEIGHT,
SHIP_DATE,SHIP_FROM,SHIP_ADD1,SHIP_ADD2,SHIP_ADD3,SHIP_CITY,
SHIP_STATE_CODE,SHIP_PIN,CONSIGEE_NAME,CONSIGEE_ADD1,CONSIGEE_ADD2,CONSIGEE_ADD3,CONSIGEE_CITY,
CONSIGEE_STATE_CODE,CONSIGEE_PIN,CONSIGEE_COUNT_CODE
FROM
(
SELECT T.PTCN AS PTCN,
T.TRACKING_NO AS TRACKING_NO,
T.CARTON_NO AS CARTON_NO,
T.REF_SER AS REF_SER,
SUM(T.GROSS_WEIGHT) AS GROSS_WEIGHT,
T.SHIP_DATE AS SHIP_DATE,
T.SHIP_FROM AS SHIP_FROM,
T.SHIP_ADD1 AS SHIP_ADD1,
T.SHIP_ADD2 AS SHIP_ADD2,
T.SHIP_ADD3 AS SHIP_ADD3,
T.SHIP_CITY AS SHIP_CITY,
T.SHIP_STATE_CODE AS SHIP_STATE_CODE,
T.SHIP_PIN AS SHIP_PIN,
T.CONSIGEE_NAME AS CONSIGEE_NAME,
T.CONSIGEE_ADD1 AS CONSIGEE_ADD1,
T.CONSIGEE_ADD2 AS CONSIGEE_ADD2,
T.CONSIGEE_ADD3 AS CONSIGEE_ADD3,
T.CONSIGEE_CITY AS CONSIGEE_CITY,
T.CONSIGEE_STATE_CODE AS CONSIGEE_STATE_CODE,
T.CONSIGEE_PIN AS CONSIGEE_PIN,
T.CONSIGEE_COUNT_CODE AS CONSIGEE_COUNT_CODE
FROM
(
SELECT WDET.PTCN AS PTCN,
' ' AS TRACKING_NO,
CASE WHEN CM.MASTER_CARTON IS NULL THEN CM.CARTON_NO ELSE CM.MASTER_CARTON END AS CARTON_NO,
WDET.REF_SER AS REF_SER,
NVL((CASE WHEN WDET.REF_SER = 'C-PICK' OR WDET.REF_SER = 'M-PICK' THEN
 (SELECT GROSS_WEIGHT FROM ITEM_LOT_PACKSIZE
  WHERE PDET.ITEM_CODE = ITEM_LOT_PACKSIZE.ITEM_CODE
  AND (PDET.LOT_NO >= ITEM_LOT_PACKSIZE.LOT_NO__FROM
  AND PDET.LOT_NO <= ITEM_LOT_PACKSIZE.LOT_NO__TO))
 ELSE PDET.QUANTITY * ITEM.GROSS_WEIGHT
 END ),0)  AS GROSS_WEIGHT ,
(SELECT DESPATCH.DESP_DATE FROM DESPATCH WHERE DESP_ID =
 (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_TASK_DET.REF_SER = 'S-DSP'
  AND WAVE_TASK_DET.PTCN = WDET.PTCN) ) AS SHIP_DATE,
SITE.DESCR AS SHIP_FROM,
SITE.ADD1 AS SHIP_ADD1,
SITE.ADD2 AS SHIP_ADD2,
SITE.ADD3 AS SHIP_ADD3,
SITE.CITY AS SHIP_CITY,
SITE.STATE_CODE AS SHIP_STATE_CODE,
SITE.PIN AS SHIP_PIN,
CUSTOMER.CUST_NAME AS CONSIGEE_NAME,
CUSTOMER.ADDR1 AS CONSIGEE_ADD1,
CUSTOMER.ADDR2 AS CONSIGEE_ADD2,
CUSTOMER.ADDR3 AS CONSIGEE_ADD3,
CUSTOMER.CITY AS CONSIGEE_CITY,
CUSTOMER.STATE_CODE AS CONSIGEE_STATE_CODE,
CUSTOMER.PIN AS CONSIGEE_PIN,
CUSTOMER.COUNT_CODE AS CONSIGEE_COUNT_CODE

FROM WAVE_TASK_DET WDET,
CARTON_MASTER CM,
PICK_ORD_DET PDET,
ITEM ,
DESPATCH DESPATCH,
CUSTOMER CUSTOMER,
SITE SITE

WHERE WDET.PTCN = '$P!{PTCN}'
AND WDET.REF_SER IN ('C-PICK','A-PICK','M-PICK')
AND PDET.PICK_ORDER = WDET.REF_ID
AND CM.PICK_ORDER = PDET.PICK_ORDER
AND CM.LINE_NO__PICK = PDET.LINE_NO
AND ITEM.ITEM_CODE = PDET.ITEM_CODE
AND NVL(ITEM.HAZARDOUS , 'N') = 'Y'
AND DESPATCH.DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE REF_SER = 'S-DSP' AND PTCN = WDET.PTCN)
AND SITE.SITE_CODE = DESPATCH.SITE_CODE
AND CUSTOMER.CUST_CODE = DESPATCH.CUST_CODE__DLV
AND DESPATCH.TRANS_MODE = 'A'
AND CM.CARTON_NO <> NVL(CM.MASTER_CARTON , '**') 
AND CASE WHEN CM.PALLET_NO IS NULL THEN NVL(CM.MASTER_CARTON,'**') ELSE CM.PALLET_NO END 
IN (SELECT PALLET_NO FROM DESPATCHDET DD WHERE DD.DESP_ID = DESPATCH.DESP_ID AND DD.PALLET_NO = CASE WHEN CM.PALLET_NO IS NULL THEN NVL(CM.MASTER_CARTON,'**') ELSE CM.PALLET_NO END)
ORDER BY CM.PICK_ORDER,CM.LINE_NO__PICK,CASE WHEN CM.MASTER_CARTON IS NULL THEN CM.CARTON_NO ELSE CM.MASTER_CARTON END
) T
GROUP BY T.PTCN,T.CARTON_NO,T.TRACKING_NO,T.REF_SER,T.SHIP_DATE,T.SHIP_DATE ,T.SHIP_FROM ,T.SHIP_ADD1 ,T.SHIP_ADD2 ,T.SHIP_ADD3 ,T.SHIP_CITY ,T.SHIP_STATE_CODE ,T.SHIP_PIN ,T.CONSIGEE_NAME ,T.CONSIGEE_ADD1 ,T.CONSIGEE_ADD2 ,T.CONSIGEE_ADD3 ,T.CONSIGEE_CITY ,T.CONSIGEE_STATE_CODE ,T.CONSIGEE_PIN ,T.CONSIGEE_COUNT_CODE
ORDER BY T.PTCN,T.CARTON_NO,T.TRACKING_NO,T.REF_SER,T.SHIP_DATE,T.SHIP_DATE ,T.SHIP_FROM ,T.SHIP_ADD1 ,T.SHIP_ADD2 ,T.SHIP_ADD3 ,T.SHIP_CITY ,T.SHIP_STATE_CODE ,T.SHIP_PIN ,T.CONSIGEE_NAME ,T.CONSIGEE_ADD1 ,T.CONSIGEE_ADD2 ,T.CONSIGEE_ADD3 ,T.CONSIGEE_CITY ,T.CONSIGEE_STATE_CODE ,T.CONSIGEE_PIN ,T.CONSIGEE_COUNT_CODE)]]>
	</queryString>
	<field name="PTCN" class="java.lang.String"/>
	<field name="GROSS_WEIGHT" class="java.lang.Double"/>
	<field name="SHIP_DATE" class="java.util.Date"/>
	<field name="TRACKING_NO" class="java.lang.String"/>
	<field name="SHIP_FROM" class="java.lang.String"/>
	<field name="SHIP_ADD1" class="java.lang.String"/>
	<field name="SHIP_ADD2" class="java.lang.String"/>
	<field name="SHIP_ADD3" class="java.lang.String"/>
	<field name="CONSIGEE_CITY" class="java.lang.String"/>
	<field name="CONSIGEE_STATE_CODE" class="java.lang.String"/>
	<field name="CONSIGEE_PIN" class="java.lang.String"/>
	<field name="CONSIGEE_COUNT_CODE" class="java.lang.String"/>
	<field name="SHIP_CITY" class="java.lang.String"/>
	<field name="SHIP_PIN" class="java.lang.String"/>
	<field name="SHIP_STATE_CODE" class="java.lang.String"/>
	<field name="CONSIGEE_NAME" class="java.lang.String"/>
	<field name="CONSIGEE_ADD1" class="java.lang.String"/>
	<field name="CONSIGEE_ADD2" class="java.lang.String"/>
	<field name="CONSIGEE_ADD3" class="java.lang.String"/>
	<field name="CARTON_NO" class="java.lang.String"/>
	<field name="REF_SER" class="java.lang.String"/>
	<variable name="varPage01" class="java.lang.String" resetType="None">
		<variableExpression><![CDATA[new java.lang.String("Page " + ($V{PAGE_NUMBER}).intValue())]]></variableExpression>
	</variable>
	<variable name="varPgCount02" class="java.lang.String" resetType="None">
		<variableExpression><![CDATA[new java.lang.String(""+ " of " + ($V{PAGE_NUMBER}).intValue())]]></variableExpression>
	</variable>
	<variable name="variable1" class="java.lang.String" resetType="None">
		<variableExpression><![CDATA[new java.lang.String((ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_FROM}) ? " " : ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_FROM}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_ADD1}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_ADD1}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_ADD2}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_ADD2}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_ADD3}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_ADD3}))
+"\n"+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_CITY}) ? " " : ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_CITY})+", ")+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_STATE_CODE}) ? " " :ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_STATE_CODE}))+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SHIP_PIN}) ? " " :" "+ibase.webitm.reports.utility.BaseReportMethods.trim($F{SHIP_PIN})))]]></variableExpression>
	</variable>
	<variable name="variable2" class="java.lang.String" resetType="None">
		<variableExpression><![CDATA[new java.lang.String((ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_NAME}) ? " " : ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_NAME}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_ADD1}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_ADD1}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_ADD2}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_ADD2}))
+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_ADD3}) ? " " : "\n"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_ADD3}))
+"\n"+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_CITY}) ? " " : ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_CITY})+", ")+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_COUNT_CODE}) ? " " :ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_COUNT_CODE}))+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{CONSIGEE_PIN}) ? " " :" "+ibase.webitm.reports.utility.BaseReportMethods.trim($F{CONSIGEE_PIN})))]]></variableExpression>
	</variable>
	<group name="CARTON_NO_GR" isStartNewPage="true" isResetPageNumber="true">
		<groupExpression><![CDATA[$F{CARTON_NO}]]></groupExpression>
		<groupHeader>
			<band height="42">
				<subreport>
					<reportElement positionType="Float" isPrintRepeatedValues="false" x="0" y="0" width="680" height="42"/>
					<parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
					<subreportParameter name="SHIP">
						<subreportParameterExpression><![CDATA[new java.lang.String($V{variable1})]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="DATE">
						<subreportParameterExpression><![CDATA[$F{SHIP_DATE}]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="TRACK_NO">
						<subreportParameterExpression><![CDATA[$F{TRACKING_NO}]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="CONSIGNEE">
						<subreportParameterExpression><![CDATA[new java.lang.String($V{variable2})]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="GROSS">
						<subreportParameterExpression><![CDATA[$F{GROSS_WEIGHT}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression class="java.io.InputStream"><![CDATA[new FileInputStream("C:/jboss-5.1.0.GA/server/default/deploy/ibase.ear/ibase.war/webitm/reports/jrxml/haz_mat_label_sub2.jasper")]]></subreportExpression>
				</subreport>
			</band>
		</groupHeader>
	</group>
	<pageHeader>
		<band/>
	</pageHeader>
</jasperReport>
