<?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="Walmart shipping label" pageWidth="283" pageHeight="438" columnWidth="283" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="1.5"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="144"/>
	<parameter name="REPLORDER" class="java.lang.String"/>
	<parameter name="FORMATID" class="java.lang.String"/>
	<queryString>
		<![CDATA[select site.descr as site_descr,
site.add1 as site_add1,
site.add2 as site_add2,
site.city as site_city,
site.state_code as site_state_code,
site.pin as site_pin,
(SELECT CUST_NAME FROM CUSTOMER WHERE CUST_CODE = T.CUST_CODE__DLV) as cust_name,
T.DLV_ADD1,
T.DLV_ADD2,
T.DLV_PIN,
substr(T.DLV_PIN,0,5) as DLV_PIN_SUB,
t.DLV_CITY AS DLV_CITY,
T.DLV_STATE AS DLV_STATE,
T.DLV_PIN AS DLV_PIN,
(SELECT TRAN_NAME FROM TRANSPORTER WHERE TRAN_CODE = T.TRAN_CODE) AS TRAN_NAME,
T.CUST_PORD AS CUST_PORD,
T.ITEM_CODE AS ITEM_CODE,
T.LOC_CODE AS LOC_CODE,
T.NO_ART AS NO_ART,
T.QUANTITY AS QUANTITY,
T.QTY_EACHES AS QTY_EACHES,
T.NOOF_CARTONS,
T.LINE_NO AS PICK_LINE_NO,
CM.CARTON_NO  AS SSCC_18,
1 AS CARTON_NO1,
T.SSCC_14 as  SSCC_14,
T.DEPT_CODE AS DEPT_CODE,
T.ORDER_SO AS ORDER_SO,
(SELECT STORE_NUMBER FROM CUSTOMER WHERE CUST_CODE = T.CUST_CODE__DLV) as STORE_NUMBER,
(SELECT CUST_ITEM__REF  FROM SORDDET WHERE SORDDET.SALE_ORDER = T.SALE_ORDER AND SORDDET.LINE_NO = T.LINE_NO__SORD) as CUST_ITEM_REF,
CASE WHEN CM.MASTER_CARTON IS NULL OR LENGTH(TRIM(CM.MASTER_CARTON)) = 0 THEN '1' ELSE MASTER_CARTON END AS MASTER_CARTON

 from  (

select r.REPL_ORDER REPL_ORDER,
r.ORDER_DATE,
r.SITE_CODE,
r.SALE_ORDER,
r.WAVE_ID,
r.ORDER_TYPE,
r.CHG_USER,
r.CHG_DATE,
r.CHG_TERM,
rd.LINE_NO,
rd.LINE_NO__SORD,
rd.ITEM_CODE,
rd.LOC_CODE,
rd.LOT_NO,
rd.LOT_SL,
rd.NO_ART,
rd.QUANTITY,
rd.EXP_LEV   ,
i.unit,
i.COUNT_CODE__MFG,
SO.CUST_CODE__DLV AS CUST_CODE__DLV,
SO.CUST_PORD AS CUST_PORD,
SO.DLV_ADD1 AS DLV_ADD1,
SO.DLV_ADD2 AS DLV_ADD2,
SO.DLV_CITY AS DLV_CITY,
SO.STATE_CODE__DLV DLV_STATE,
SO.DLV_PIN AS DLV_PIN,
SO.TRAN_CODE AS TRAN_CODE,
(RD.QUANTITY / RD.NO_ART) AS QTY_EACHES,
(SELECT SUM(NO_ART) FROM repl_ord_det RD1 WHERE RD1.REPL_ORDER = RD.REPL_ORDER
	AND RD1.LOC_CODE = RD.LOC_CODE AND RD1.ITEM_CODE = RD.ITEM_CODE) AS NOOF_CARTONS,
i.gtin_case as SSCC_14,
SO.DEPT_CODE AS DEPT_CODE,
SO.ORDER_TYPE AS ORDER_SO
from repl_ord_hdr r, repl_ord_det rd,item i,SORDER SO
where r.repl_order = rd.repl_order
and i.item_code = rd.item_code
and r.repl_order = '$P!{REPLORDER}'
AND SO.SALE_ORDER = R.SALE_ORDER
AND  '$P!{FORMATID}' = '04'
union all
select p.PICK_ORDER PICK_ORDER,
p.ORDER_DATE ORDER_DATE,
p.SITE_CODE SITE_CODE,
p.SALE_ORDER,
p.WAVE_ID,
p.PICK_TYPE,
p.CHG_USER,
p.CHG_DATE,
p.CHG_TERM,
pd.LINE_NO,
pd.LINE_NO__SORD,
pd.ITEM_CODE,
pd.LOC_CODE,
pd.LOT_NO,
pd.LOT_SL,
pd.NO_ART,
pd.QUANTITY,
pd.EXP_LEV ,
i.unit,
i.COUNT_CODE__MFG,
SO.CUST_CODE__DLV AS CUST_CODE__DLV,
SO.CUST_PORD AS CUST_PORD,
SO.DLV_ADD1 AS DLV_ADD1,
SO.DLV_ADD2 AS DLV_ADD2,
SO.DLV_CITY AS DLV_CITY,
SO.STATE_CODE__DLV DLV_STATE,
SO.DLV_PIN AS DLV_PIN,
SO.TRAN_CODE AS TRAN_CODE,
(PD.QUANTITY / PD.NO_ART) AS QTY_EACHES,
(SELECT SUM(NO_ART) FROM pick_ord_det PD1 WHERE PD1.PICK_ORDER = PD.PICK_ORDER
	AND PD1.LOC_CODE = PD.LOC_CODE AND PD1.ITEM_CODE = PD.ITEM_CODE) AS NOOF_CARTONS,
i.gtin_case as SSCC_14,
SO.DEPT_CODE AS DEPT_CODE,
SO.ORDER_TYPE AS ORDER_SO
from pick_ord_hdr p, pick_ord_det pd,item i, SORDER SO
where p.pick_order = pd.pick_order
and i.item_code = pd.item_code
and p.pick_order = '$P!{REPLORDER}'
AND SO.SALE_ORDER = P.SALE_ORDER
AND  '$P!{FORMATID}' = '04'
) t,
CARTON_MASTER CM,
SITE
WHERE CM.PICK_ORDER = T.REPL_ORDER
AND CM.LINE_NO__PICK = T.LINE_NO
AND SITE.SITE_CODE = T.SITE_CODE
order by CASE WHEN CM.MASTER_CARTON IS NULL OR LENGTH(TRIM(CM.MASTER_CARTON)) = 0 THEN '1' ELSE MASTER_CARTON END,t.loc_code,t.loc_code,T.ITEM_CODE,T.LINE_NO]]>
	</queryString>
	<field name="TRAN_NAME" class="java.lang.String"/>
	<field name="CARTON_NO1" class="java.lang.Double"/>
	<field name="CUST_PORD" class="java.lang.String"/>
	<field name="DLV_ADD1" class="java.lang.String"/>
	<field name="DLV_ADD2" class="java.lang.String"/>
	<field name="DLV_CITY" class="java.lang.String"/>
	<field name="DLV_STATE" class="java.lang.String"/>
	<field name="DLV_PIN" class="java.lang.String"/>
	<field name="QTY_EACHES" class="java.lang.String"/>
	<field name="SITE_ADD1" class="java.lang.String"/>
	<field name="SITE_ADD2" class="java.lang.String"/>
	<field name="SITE_CITY" class="java.lang.String"/>
	<field name="SITE_PIN" class="java.lang.String"/>
	<field name="SITE_STATE_CODE" class="java.lang.String"/>
	<field name="cust_name" class="java.lang.String"/>
	<field name="ITEM_CODE" class="java.lang.String"/>
	<field name="LOC_CODE" class="java.lang.String"/>
	<field name="SSCC_18" class="java.lang.String"/>
	<field name="NO_ART" class="java.lang.Integer"/>
	<field name="SITE_DESCR" class="java.lang.String"/>
	<field name="SSCC_14" class="java.lang.String"/>
	<field name="DEPT_CODE" class="java.lang.String"/>
	<field name="STORE_NUMBER" class="java.lang.String"/>
	<field name="CUST_ITEM_REF" class="java.lang.String"/>
	<field name="ORDER_SO" class="java.lang.String"/>
	<field name="DLV_PIN_SUB" class="java.lang.String"/>
	<field name="MASTER_CARTON" class="java.lang.String"/>
	<variable name="compute_1" class="java.lang.String">
		<variableExpression><![CDATA[new java.lang.String(ibase.webitm.reports.sun.BaseReportGFMethods.gf_bar_code("420"+ibase.webitm.reports.utility.BaseReportMethods.trim($F{DLV_PIN}).substring(0,5) ) )]]></variableExpression>
	</variable>
	<variable name="compute_2" class="java.lang.String">
		<variableExpression><![CDATA[new java.lang.String(ibase.webitm.reports.sun.BaseReportGFMethods.gf_bar_code(ibase.webitm.reports.utility.BaseReportMethods.trim($F{SSCC_14}) ) )]]></variableExpression>
	</variable>
	<variable name="compute_3" class="java.lang.String">
		<variableExpression><![CDATA[new java.lang.String((ibase.webitm.reports.utility.BaseReportMethods.isNull($F{DLV_CITY}) ? " " : ($F{DLV_CITY}).trim())+" "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{DLV_STATE} ) ? " " : ($F{DLV_STATE}).trim())+" "+ (ibase.webitm.reports.utility.BaseReportMethods.isNull($F{DLV_PIN}) ? " " :($F{DLV_PIN}).trim()))]]></variableExpression>
	</variable>
	<variable name="compute_4" class="java.lang.String">
		<variableExpression><![CDATA[new java.lang.String("(420) "+ibase.webitm.reports.utility.BaseReportMethods.trim($F{DLV_PIN_SUB}))]]></variableExpression>
	</variable>
	<variable name="COMPUTE_5" class="java.lang.String">
		<variableExpression><![CDATA[new java.lang.String((ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SITE_CITY}) ? " " : ($F{SITE_CITY}).trim())+"  "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SITE_STATE_CODE} ) ? " " : ($F{SITE_STATE_CODE}).trim())+" "+ (ibase.webitm.reports.utility.BaseReportMethods.isNull($F{SITE_PIN}) ? " " :($F{SITE_PIN}).trim()))]]></variableExpression>
	</variable>
	<group name="Location" isStartNewPage="true">
		<groupExpression><![CDATA[$F{LOC_CODE}]]></groupExpression>
		<groupHeader>
			<band/>
		</groupHeader>
		<groupFooter>
			<band height="7">
				<subreport>
					<reportElement isPrintRepeatedValues="false" x="6" y="2" width="254" height="5" printWhenGroupChanges="Location"/>
					<parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
					<subreportParameter name="LOC_CODE">
						<subreportParameterExpression><![CDATA[$F{LOC_CODE}]]></subreportParameterExpression>
					</subreportParameter>
					<subreportParameter name="REPLORDER">
						<subreportParameterExpression><![CDATA[$P{REPLORDER}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression class="java.io.InputStream"><![CDATA[new FileInputStream("/data/jboss-5.1.0.GA/server/default/deploy/ibase.ear/ibase.war/webitm/reports/jrxml/pick_task_break.jasper")]]></subreportExpression>
				</subreport>
			</band>
		</groupFooter>
	</group>
	<group name="carton break" isStartNewPage="true">
		<groupExpression><![CDATA[$F{MASTER_CARTON}]]></groupExpression>
		<groupHeader>
			<band/>
		</groupHeader>
		<groupFooter>
			<band height="7">
				<subreport>
					<reportElement isPrintRepeatedValues="false" x="6" y="2" width="254" height="5" printWhenGroupChanges="carton break">
						<printWhenExpression><![CDATA[new Boolean(!$F{MASTER_CARTON}.equals("1")?true:false)]]></printWhenExpression>
					</reportElement>
					<parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
					<subreportParameter name="MASTER_CARTON">
						<subreportParameterExpression><![CDATA[$F{MASTER_CARTON}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression class="java.io.InputStream"><![CDATA[new FileInputStream("/data/jboss-5.1.0.GA/server/default/deploy/ibase.ear/ibase.war/webitm/reports/jrxml/carton_break.jasper")]]></subreportExpression>
				</subreport>
			</band>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band splitType="Stretch"/>
	</title>
	<pageHeader>
		<band splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="411" splitType="Stretch">
			<rectangle>
				<reportElement mode="Transparent" x="2" y="14" width="250" height="393">
					<printWhenExpression><![CDATA[new Boolean(false)]]></printWhenExpression>
				</reportElement>
			</rectangle>
			<staticText>
				<reportElement x="3" y="15" width="92" height="10"/>
				<textElement>
					<font fontName="Times New Roman" size="8" isBold="false"/>
				</textElement>
				<text><![CDATA[SHIP FROM:]]></text>
			</staticText>
			<line>
				<reportElement x="113" y="15" width="1" height="67"/>
			</line>
			<staticText>
				<reportElement x="115" y="15" width="100" height="10"/>
				<textElement>
					<font fontName="Times New Roman" size="8" isBold="false"/>
				</textElement>
				<text><![CDATA[SHIP TO:]]></text>
			</staticText>
			<staticText>
				<reportElement x="5" y="86" width="124" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="9"/>
				</textElement>
				<text><![CDATA[(420) Ship to Postal Code:]]></text>
			</staticText>
			<line>
				<reportElement x="2" y="298" width="250" height="2"/>
			</line>
			<line>
				<reportElement x="2" y="226" width="250" height="1"/>
			</line>
			<staticText>
				<reportElement x="3" y="164" width="68" height="15"/>
				<textElement textAlignment="Center">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[DC#]]></text>
			</staticText>
			<staticText>
				<reportElement x="72" y="164" width="34" height="15"/>
				<textElement textAlignment="Center" verticalAlignment="Top">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Type]]></text>
			</staticText>
			<line>
				<reportElement x="3" y="82" width="250" height="1"/>
			</line>
			<line>
				<reportElement x="3" y="154" width="250" height="1"/>
			</line>
			<line>
				<reportElement x="130" y="82" width="1" height="72"/>
			</line>
			<staticText>
				<reportElement x="133" y="118" width="29" height="15"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" size="9" isBold="false"/>
				</textElement>
				<text><![CDATA[PRO#]]></text>
			</staticText>
			<staticText>
				<reportElement x="133" y="83" width="80" height="12"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman" size="9" isBold="false"/>
				</textElement>
				<text><![CDATA[CARRIER:]]></text>
			</staticText>
			<staticText>
				<reportElement x="106" y="164" width="72" height="15"/>
				<textElement textAlignment="Center">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[DEPT]]></text>
			</staticText>
			<staticText>
				<reportElement x="178" y="164" width="72" height="15"/>
				<textElement textAlignment="Center" verticalAlignment="Top">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Order #]]></text>
			</staticText>
			<staticText>
				<reportElement x="133" y="136" width="29" height="15"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" size="9" isBold="false"/>
				</textElement>
				<text><![CDATA[B/L #]]></text>
			</staticText>
			<textField isBlankWhenNull="true">
				<reportElement x="-9" y="300" width="258" height="74"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Code 128AB" size="40" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{compute_2}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="27" y="204" width="45" height="15"/>
				<textElement textAlignment="Left" verticalAlignment="Top">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[WMIT :]]></text>
			</staticText>
			<textField isBlankWhenNull="true">
				<reportElement x="2" y="41" width="111" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SITE_ADD1}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="116" y="55" width="133" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DLV_ADD2}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="116" y="41" width="134" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DLV_ADD1}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="133" y="96" width="117" height="15"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" size="9"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{TRAN_NAME}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="4" y="180" width="67" height="12"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{STORE_NUMBER}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="71" y="180" width="35" height="12"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ORDER_SO}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="106" y="180" width="72" height="12"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DEPT_CODE}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="178" y="180" width="72" height="12"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{CUST_PORD}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="71" y="204" width="150" height="15"/>
				<textElement>
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{CUST_ITEM_REF}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="3" y="247" width="247" height="30"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SSCC_18}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="6" y="102" width="122" height="33"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Code 128AB" size="22"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{compute_1}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="4" y="27" width="109" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SITE_DESCR}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="-9" y="374" width="260" height="25"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SSCC_14}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="115" y="68" width="135" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{compute_3}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="6" y="134" width="122" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Top">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{compute_4}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="116" y="27" width="133" height="12"/>
				<textElement>
					<font size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{cust_name}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="3" y="54" width="111" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SITE_ADD2}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="2" y="68" width="111" height="12"/>
				<textElement>
					<font fontName="Times New Roman" size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{COMPUTE_5}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>
