<?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_label" pageWidth="700" pageHeight="800" columnWidth="700" 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 
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
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
FROM WAVE_TASK_DET WDET,
CARTON_MASTER CM,
PICK_ORD_DET PDET,
ITEM,
DESPATCH DESP
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 DESP.DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE REF_SER = 'S-DSP' AND PTCN = WDET.PTCN)
AND NVL(ITEM.HAZARDOUS , 'N') = 'Y'
AND DESP.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 = DESP.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
ORDER BY T.PTCN,T.CARTON_NO,T.TRACKING_NO,T.REF_SER,T.SHIP_DATE)
]]>
	</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="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>
	<group name="CARTON_NO_GR" isStartNewPage="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="DATE">
						<subreportParameterExpression><![CDATA[$F{SHIP_DATE}]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="TRACK_NO">
						<subreportParameterExpression><![CDATA[$F{TRACKING_NO}]]></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_sub1.jasper")]]></subreportExpression>
				</subreport>
			</band>
		</groupHeader>
	</group>
	<pageHeader>
		<band/>
	</pageHeader>
	<detail>
		<band/>
	</detail>
</jasperReport>
