<?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="555" pageHeight="750" columnWidth="555" 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
     I.ITEM_CODE  AS ITEM_ITEM_CODE,
    CASE WHEN I.SITE_CODE IS NULL OR LENGTH(TRIM(I.SITE_CODE)) = 0 OR ST1.COUNT_CODE IS NULL OR LENGTH(TRIM(ST1.COUNT_CODE)) = 0 OR ST2.COUNT_CODE IS NULL OR LENGTH(TRIM(ST2.COUNT_CODE)) = 0
    then ' ' when ST1.COUNT_CODE  = ST2.COUNT_CODE THEN 'D' ELSE 'F' END AS ITEM_COUNT_CODE__MFG,
    case when  I.HARMONIZATION_NO is null then '0' else  I.HARMONIZATION_NO end AS ITEM_HARMONIZATION_NO,
    SUM(case when DT.QUANTITY__STDUOM is null then 0 else  DT.QUANTITY__STDUOM end ) AS DESPATCHDET_QUANTITY,
SUM(((case when DT.QUANTITY__STDUOM is null then 0 else  DT.QUANTITY__STDUOM end) * (case when  DT.RATE__STDUOM is null then 0 else DT.RATE__STDUOM end))) AS Value,
SUM(round(NVL(DT.GROSS_WEIGHT,0) / 2.20462262185, 2)) as Weight_In_KG,
case when  I.HARMONIZATION_DESCR is null then ' ' else  I.HARMONIZATION_DESCR end as har_descr
FROM
     SITE S1,ITEM I,SITE S2,DESPATCHDET DT ,STATE ST1,STATE ST2, DESPATCH D
WHERE DT.ITEM_CODE = I.ITEM_CODE
AND I.SITE_CODE = S1.SITE_CODE(+)
AND DT.SITE_CODE = S2.SITE_CODE
AND S1.STATE_CODE = ST1.STATE_CODE(+)
AND S2.STATE_CODE = ST2.STATE_CODE(+)
AND I.ITEM_CODE = DT.ITEM_CODE
AND  DT.DESP_ID = D.DESP_ID
AND D.DESP_ID = '$P!{DespId}'
GROUP BY I.ITEM_CODE,(CASE WHEN I.SITE_CODE IS NULL OR LENGTH(TRIM(I.SITE_CODE)) = 0 OR ST1.COUNT_CODE IS NULL OR LENGTH(TRIM(ST1.COUNT_CODE)) = 0 OR ST2.COUNT_CODE IS NULL OR LENGTH(TRIM(ST2.COUNT_CODE)) = 0
    then ' ' when ST1.COUNT_CODE  = ST2.COUNT_CODE THEN 'D' ELSE 'F' END),
I.HARMONIZATION_NO,I.HARMONIZATION_DESCR)]]>
	</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"/>
	<variable name="variable1" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[new java.lang.Double($F{DESPATCHDET_QUANTITY}.doubleValue())]]></variableExpression>
	</variable>
	<variable name="variable2" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[new java.lang.Double(($F{Weight_In_KG}.doubleValue())/2.20462262185)]]></variableExpression>
	</variable>
	<variable name="variable3" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[new java.lang.Double($F{Value}.doubleValue())]]></variableExpression>
	</variable>
	<background>
		<band height="280">
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToBandHeight" x="32" y="89" width="1" height="191" isPrintInFirstWholeBand="true"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="228" y="89" width="1" height="137"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="310" y="89" width="1" height="137"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="375" y="89" width="1" height="137"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="470" y="89" width="1" height="137"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="552" y="89" width="1" height="137"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToBandHeight" x="2" y="89" width="1" height="191"/>
			</line>
		</band>
	</background>
	<title>
		<band splitType="Stretch"/>
	</title>
	<columnHeader>
		<band height="55" splitType="Stretch">
			<staticText>
				<reportElement positionType="Float" x="470" y="0" width="76" height="39" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[            VALU (USD)              (Selling price or cost if not sold)]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" x="236" y="14" width="57" height="25" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[QUANTITY-SCHEDULE UNIT(S)]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" x="228" y="38" width="72" height="15" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[(23)]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" x="5" y="1" width="332" height="11" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font size="6" isBold="false"/>
				</textElement>
				<text><![CDATA[20.SCHEDULE B DESCRIPTION OF COMMODITIES (Use Columns 22-24)]]></text>
			</staticText>
			<line>
				<reportElement x="1" y="54" width="550" height="1" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="32" y="15" width="195" height="24" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[SCHEDULE B NUMBER]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="2" y="13" width="373" height="1" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="11" y="13" width="17" height="26" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6" isBold="false"/>
				</textElement>
				<text><![CDATA[D/F or M]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="227" y="14" width="1" height="41" isRemoveLineWhenBlank="true"/>
			</line>
			<line>
				<reportElement positionType="Float" x="469" y="-1" width="1" height="55" isRemoveLineWhenBlank="true"/>
			</line>
			<line>
				<reportElement positionType="Float" x="1" y="-1" width="1" height="55" isRemoveLineWhenBlank="true"/>
			</line>
			<line>
				<reportElement positionType="Float" x="551" y="-1" width="1" height="55" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="470" y="38" width="76" height="15" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[(26)]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" x="10" y="38" width="18" height="15" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[(21)]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="31" y="14" width="1" height="41" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="310" y="14" width="62" height="25" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[SHIPPING WEIGHT(Kilogram)]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="374" y="1" width="1" height="53" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="376" y="0" width="98" height="39" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[VIN/PRODUCT NUMBER/ VEICHEL NUMBER]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="309" y="14" width="1" height="41" isRemoveLineWhenBlank="true"/>
			</line>
			<staticText>
				<reportElement positionType="Float" x="310" y="38" width="64" height="15" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[(24)]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" x="376" y="38" width="103" height="15" isRemoveLineWhenBlank="true"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<text><![CDATA[(25)]]></text>
			</staticText>
			<line>
				<reportElement positionType="Float" x="2" y="0" width="550" height="1" isRemoveLineWhenBlank="true">
					<printWhenExpression><![CDATA[new Boolean($V{PAGE_NUMBER}.intValue()>1?true:false)]]></printWhenExpression>
				</reportElement>
				<graphicElement>
					<pen lineWidth="1.0" lineStyle="Solid"/>
				</graphicElement>
			</line>
		</band>
	</columnHeader>
	<detail>
		<band height="26" splitType="Stretch">
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" x="18" y="1" width="29" height="9"/>
				<textElement textAlignment="Left">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_COUNT_CODE__MFG}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" x="86" y="1" width="95" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_HARMONIZATION_NO}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.0000" isBlankWhenNull="true">
				<reportElement positionType="Float" x="243" y="1" width="55" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{DESPATCHDET_QUANTITY}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.0000" isBlankWhenNull="true">
				<reportElement positionType="Float" x="488" y="1" width="58" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{Value}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00" isBlankWhenNull="true">
				<reportElement positionType="Float" x="305" y="1" width="55" height="9"/>
				<textElement textAlignment="Right">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{Weight_In_KG}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement positionType="Float" x="44" y="11" width="183" height="10"/>
				<textElement>
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{har_descr}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement positionType="Float" x="44" y="1" width="80" height="10"/>
				<textElement>
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_ITEM_CODE}]]></textFieldExpression>
			</textField>
			<line>
				<reportElement stretchType="RelativeToTallestObject" x="31" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement positionType="FixRelativeToBottom" stretchType="RelativeToTallestObject" x="1" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement stretchType="RelativeToTallestObject" x="227" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement stretchType="RelativeToTallestObject" x="309" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement stretchType="RelativeToTallestObject" x="374" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement stretchType="RelativeToTallestObject" x="469" y="0" width="1" height="26"/>
			</line>
			<line>
				<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="551" y="0" width="1" height="26"/>
			</line>
		</band>
	</detail>
</jasperReport>
