<?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="shippers_Expo_subreport1" pageWidth="520" pageHeight="750" columnWidth="520" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="2.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="DespId" class="java.lang.String"/>
	<queryString>
		<![CDATA[SELECT ITEM_ITEM_CODE,
ITEM_COUNT_CODE__MFG,
ITEM_HARMONIZATION_NO,
DESPATCHDET_QUANTITY,
Value,
Weight_In_KG,
har_descr FROM (
SELECT
     ITEM.ITEM_CODE  AS ITEM_ITEM_CODE,
    case when  DESPATCHDET.SITE_CODE  = ITEM.COUNT_CODE__MFG then 'D' else 'F' end AS ITEM_COUNT_CODE__MFG,
    case when  ITEM.HARMONIZATION_NO is null then '0' else  ITEM.HARMONIZATION_NO end AS ITEM_HARMONIZATION_NO,
    sum(case when DESPATCHDET.QUANTITY__STDUOM is null then 0 else  DESPATCHDET.QUANTITY__STDUOM end) AS DESPATCHDET_QUANTITY,
sum(((case when DESPATCHDET.QUANTITY__STDUOM is null then 0 else  DESPATCHDET.QUANTITY__STDUOM end) * (case when  DESPATCHDET.RATE__STDUOM is null then 0 else DESPATCHDET.RATE__STDUOM end))) AS Value,
sum(round(NVL(DESPATCHDET.GROSS_WEIGHT,0) / 2.20462262185, 2)) as Weight_In_KG,
(case when DDF_GET_GEN_DESCR('harmonization_no', 'W_ITEM', ITEM.harmonization_no,'D') is null then ' '  else DDF_GET_GEN_DESCR('harmonization_no', 'W_ITEM', ITEM.harmonization_no,'D') end) as har_descr
FROM
     ITEM ,
     DESPATCHDET,
     DESPATCH
WHERE
      ITEM.ITEM_CODE = DESPATCHDET.ITEM_CODE
    AND  DESPATCHDET.DESP_ID = DESPATCH.DESP_ID
   AND DESPATCH.DESP_ID = '$P!{DespId}'
GROUP BY ITEM.ITEM_CODE,
(case when  DESPATCHDET.SITE_CODE  = ITEM.COUNT_CODE__MFG then 'D' else 'F' end),
ITEM.HARMONIZATION_NO ) WHERE ROWNUM >=5]]>
	</queryString>
	<field name="ITEM_ITEM_CODE" class="java.lang.String"/>
	<field name="ITEM_COUNT_CODE__MFG" class="java.lang.String"/>
	<field name="ITEM_HARMONIZATION_NO" class="java.lang.String"/>
	<field name="DESPATCHDET_QUANTITY" class="java.lang.Double"/>
	<field name="har_descr" class="java.lang.String"/>
	<field name="Value" class="java.lang.Double"/>
	<field name="Weight_In_KG" class="java.lang.Double"/>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band splitType="Stretch"/>
	</title>
	<pageHeader>
		<band splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="23" splitType="Stretch">
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" stretchType="RelativeToBandHeight" x="29" y="0" width="13" height="9"/>
				<textElement textAlignment="Left">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_COUNT_CODE__MFG}.contains("IND")?"D":"F"]]></textFieldExpression>
			</textField>
			<textField pattern="###0.0000" isBlankWhenNull="true">
				<reportElement positionType="Float" stretchType="RelativeToBandHeight" x="184" y="0" width="55" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{DESPATCHDET_QUANTITY}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" stretchType="RelativeToBandHeight" x="43" y="0" width="80" height="10"/>
				<textElement>
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_ITEM_CODE}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" stretchType="RelativeToBandHeight" x="77" y="0" width="95" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_HARMONIZATION_NO}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="43" y="9" width="129" height="10"/>
				<textElement>
					<font size="7" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{har_descr}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="247" y="0" width="55" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{Weight_In_KG}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.0000">
				<reportElement x="436" y="0" width="58" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{Value}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<lastPageFooter>
		<band/>
	</lastPageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>
