<?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="Standard Back Order Report" pageWidth="792" pageHeight="612" orientation="Landscape" columnWidth="752" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="231"/>
	<property name="ireport.y" value="0"/>
	<parameter name="as_site_code" class="java.lang.String"/>
	<parameter name="frm_order_date" class="java.util.Date"/>
	<parameter name="to_order_date" class="java.util.Date"/>
	<queryString>
		<![CDATA[select SALE_ORDER,
LINE_NO,
ORDER_DATE,
DUE_DATE,
STATUS_DATE,
CUST_CODE,
CUST_NAME,
SUM(ORD_QTY) AS ORD_QTY,
SUM(SHIP_QTY) AS SHIP_QTY,
SUM(ORD_QTY-SHIP_QTY) AS BACKORDER_QTY,
SUM(ORD_VALUE-SHIP_VALUE) AS BACKORDER_VALUE,
SUM(ORD_VALUE) AS ORD_VALUE,
SUM(SHIP_VALUE) AS SHIP_VALUE,
ITEM_CODE,
ITEM_DESCR,
SITE_DESCR  from
(
select sorder.sale_order,sorddet.line_no,sorder.order_date,sorder.due_date,sorder.status_date,sorder.cust_code,customer.cust_name,sorddet.quantity as ord_qty,(sorddet.quantity*sorddet.rate) as ord_value,0 as ship_qty,0 as ship_value,sorddet.item_code,item.descr as item_descr,ddf_get_site_descr(sorder.site_code) as site_descr
from sorder,sorddet,customer,item
where sorder.order_date between $P{frm_order_date} AND $P{to_order_date}
and sorder.site_code = '$P!{as_site_code}'
and sorder.confirmed = 'Y' and sorder.status = 'C' and sorddet.sale_order = sorder.sale_order and customer.cust_code = sorder.cust_code and item.item_code = sorddet.item_code
union all
select sorder.sale_order,sorditem.line_no,sorder.order_date,sorditem.due_date,sorder.status_date,sorder.cust_code,customer.cust_name,0 as ord_qty,0 as ord_value,sum(sorditem.qty_desp) as ship_qty,sum(sorditem.qty_desp*sorditem.rate) as ship_value,sorditem.item_code,item.descr as item_descr, ddf_get_site_descr(sorder.site_code) as site_descr
from sorder,sorditem,customer,item
where sorder.order_date between $P{frm_order_date} AND $P{to_order_date}
and sorder.site_code = '$P!{as_site_code}'
and sorder.confirmed = 'Y' and sorder.status = 'C' and sorditem.sale_order = sorder.sale_order and customer.cust_code = sorder.cust_code and item.item_code = sorditem.item_code
group by sorder.sale_order,sorditem.line_no,sorder.order_date,sorditem.due_date,sorder.status_date,sorder.cust_code,customer.cust_name,sorditem.item_code,item.descr,ddf_get_site_descr(sorder.site_code))
group by sale_order,line_no,order_date,due_date,status_date,cust_code,cust_name,item_code,item_descr,site_descr having sum(ord_qty-ship_qty) > 0]]>
	</queryString>
	<field name="SALE_ORDER" class="java.lang.String"/>
	<field name="LINE_NO" class="java.lang.Double"/>
	<field name="ORDER_DATE" class="java.util.Date"/>
	<field name="DUE_DATE" class="java.util.Date"/>
	<field name="STATUS_DATE" class="java.util.Date"/>
	<field name="CUST_CODE" class="java.lang.String"/>
	<field name="CUST_NAME" class="java.lang.String"/>
	<field name="ORD_QTY" class="java.lang.Double"/>
	<field name="SHIP_QTY" class="java.lang.Double"/>
	<field name="BACKORDER_QTY" class="java.lang.Double"/>
	<field name="BACKORDER_VALUE" class="java.lang.Double"/>
	<field name="ITEM_CODE" class="java.lang.String"/>
	<field name="ITEM_DESCR" class="java.lang.String"/>
	<field name="SITE_DESCR" class="java.lang.String"/>
	<field name="ORD_VALUE" class="java.lang.Double"/>
	<field name="SHIP_VALUE" class="java.lang.Double"/>
	<sortField name="CUST_CODE"/>
	<variable name="variable1" class="java.util.Date" resetType="None">
		<variableExpression><![CDATA[new java.util.Date()]]></variableExpression>
	</variable>
	<variable name="ord_tot" class="java.lang.Double" resetType="Group" resetGroup="customer" calculation="Sum">
		<variableExpression><![CDATA[$F{ORD_VALUE}]]></variableExpression>
	</variable>
	<variable name="backord_grand" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[$F{BACKORDER_VALUE}]]></variableExpression>
	</variable>
	<variable name="back_ord_tot" class="java.lang.Double" resetType="Group" resetGroup="customer" calculation="Sum">
		<variableExpression><![CDATA[$F{BACKORDER_VALUE}]]></variableExpression>
	</variable>
	<variable name="ord_grand" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[$F{ORD_VALUE}]]></variableExpression>
	</variable>
	<variable name="ship_tot" class="java.lang.Double" resetType="Group" resetGroup="customer" calculation="Sum">
		<variableExpression><![CDATA[$F{SHIP_VALUE}]]></variableExpression>
	</variable>
	<variable name="ship_grand" class="java.lang.Double" calculation="Sum">
		<variableExpression><![CDATA[$F{SHIP_VALUE}]]></variableExpression>
	</variable>
	<group name="customer" isReprintHeaderOnEachPage="true">
		<groupExpression><![CDATA[$F{CUST_CODE}]]></groupExpression>
		<groupHeader>
			<band height="18">
				<textField>
					<reportElement x="78" y="2" width="174" height="16"/>
					<textElement textAlignment="Left" verticalAlignment="Middle">
						<font fontName="Times New Roman" isBold="true"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[$F{CUST_NAME}]]></textFieldExpression>
				</textField>
				<staticText>
					<reportElement x="2" y="0" width="76" height="18"/>
					<textElement textAlignment="Left" verticalAlignment="Middle">
						<font fontName="Times New Roman" isBold="true"/>
					</textElement>
					<text><![CDATA[Customer Name : ]]></text>
				</staticText>
				<line>
					<reportElement x="0" y="17" width="752" height="1"/>
				</line>
			</band>
		</groupHeader>
		<groupFooter>
			<band height="20">
				<textField pattern="###0.00">
					<reportElement x="686" y="0" width="52" height="18"/>
					<textElement textAlignment="Right" verticalAlignment="Middle">
						<font fontName="Times New Roman"/>
					</textElement>
					<textFieldExpression class="java.lang.Double"><![CDATA[$V{back_ord_tot}]]></textFieldExpression>
				</textField>
				<line>
					<reportElement x="0" y="0" width="752" height="1"/>
				</line>
				<line>
					<reportElement x="0" y="19" width="752" height="1"/>
				</line>
				<staticText>
					<reportElement x="293" y="1" width="146" height="18"/>
					<textElement textAlignment="Left" verticalAlignment="Middle">
						<font fontName="Times New Roman" isBold="true"/>
					</textElement>
					<text><![CDATA[Customer Code Wise Total]]></text>
				</staticText>
				<textField pattern="###0.00">
					<reportElement x="484" y="0" width="52" height="18"/>
					<textElement textAlignment="Right" verticalAlignment="Middle">
						<font fontName="Times New Roman"/>
					</textElement>
					<textFieldExpression class="java.lang.Double"><![CDATA[$V{ord_tot}]]></textFieldExpression>
				</textField>
				<textField pattern="###0.00">
					<reportElement x="581" y="0" width="52" height="18"/>
					<textElement textAlignment="Right" verticalAlignment="Middle">
						<font fontName="Times New Roman"/>
					</textElement>
					<textFieldExpression class="java.lang.Double"><![CDATA[$V{ship_tot}]]></textFieldExpression>
				</textField>
			</band>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<pageHeader>
		<band height="88" splitType="Stretch">
			<textField>
				<reportElement x="12" y="11" width="735" height="17"/>
				<textElement textAlignment="Center">
					<font fontName="Times New Roman" size="12" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SITE_DESCR}]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Report">
				<reportElement x="732" y="10" width="15" height="20"/>
				<textElement>
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="14" y="11" width="28" height="17"/>
				<textElement>
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Date:]]></text>
			</staticText>
			<staticText>
				<reportElement x="549" y="50" width="29" height="37"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Ship Qty]]></text>
			</staticText>
			<line>
				<reportElement x="0" y="87" width="752" height="1"/>
			</line>
			<staticText>
				<reportElement x="685" y="10" width="20" height="20"/>
				<textElement>
					<font fontName="Times New Roman" isBold="false"/>
				</textElement>
				<text><![CDATA[Page]]></text>
			</staticText>
			<textField evaluationTime="Page">
				<reportElement x="707" y="10" width="13" height="20"/>
				<textElement>
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="439" y="50" width="45" height="38"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Order Qty]]></text>
			</staticText>
			<staticText>
				<reportElement x="293" y="51" width="65" height="36"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Item Description]]></text>
			</staticText>
			<staticText>
				<reportElement x="686" y="50" width="56" height="38"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[BackOrder Value]]></text>
			</staticText>
			<staticText>
				<reportElement x="237" y="50" width="57" height="37"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Item]]></text>
			</staticText>
			<staticText>
				<reportElement x="14" y="29" width="733" height="18"/>
				<textElement textAlignment="Center">
					<font fontName="Times New Roman" size="14" isBold="true"/>
				</textElement>
				<text><![CDATA[Standard Backorder Report]]></text>
			</staticText>
			<line>
				<reportElement x="0" y="50" width="752" height="1"/>
			</line>
			<staticText>
				<reportElement x="719" y="10" width="10" height="14"/>
				<textElement>
					<font fontName="Times New Roman" isBold="false"/>
				</textElement>
				<text><![CDATA[of]]></text>
			</staticText>
			<staticText>
				<reportElement x="634" y="50" width="51" height="38"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[BackOrder Qty]]></text>
			</staticText>
			<textField pattern="MM/dd/yy">
				<reportElement x="46" y="11" width="133" height="17"/>
				<textElement>
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[$V{variable1}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="2" y="50" width="14" height="37"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Sr No]]></text>
			</staticText>
			<staticText>
				<reportElement x="21" y="51" width="41" height="36"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Sale Order]]></text>
			</staticText>
			<staticText>
				<reportElement x="79" y="51" width="40" height="36"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Order Date]]></text>
			</staticText>
			<staticText>
				<reportElement x="484" y="50" width="52" height="38"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Order Value]]></text>
			</staticText>
			<staticText>
				<reportElement x="593" y="49" width="37" height="38"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Ship Value]]></text>
			</staticText>
			<staticText>
				<reportElement x="131" y="50" width="48" height="37"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Status Date]]></text>
			</staticText>
			<staticText>
				<reportElement x="183" y="50" width="38" height="37"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Due Date]]></text>
			</staticText>
		</band>
	</pageHeader>
	<detail>
		<band height="18" splitType="Stretch">
			<textField pattern="###0">
				<reportElement x="2" y="0" width="14" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{LINE_NO}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="293" y="0" width="146" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_DESCR}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="439" y="0" width="45" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{ORD_QTY}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="686" y="0" width="50" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{BACKORDER_VALUE}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="237" y="0" width="57" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM_CODE}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="21" y="0" width="53" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{SALE_ORDER}]]></textFieldExpression>
			</textField>
			<textField pattern="MM/dd/yy">
				<reportElement x="79" y="0" width="57" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[$F{ORDER_DATE}]]></textFieldExpression>
			</textField>
			<textField pattern="MM/dd/yy">
				<reportElement x="131" y="0" width="52" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[$F{ORDER_DATE}]]></textFieldExpression>
			</textField>
			<textField pattern="MM/dd/yy">
				<reportElement x="183" y="0" width="52" height="18"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[$F{ORDER_DATE}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="536" y="0" width="45" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{SHIP_QTY}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="635" y="0" width="50" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{BACKORDER_QTY}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="484" y="0" width="51" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{ORD_VALUE}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="582" y="0" width="50" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{SHIP_VALUE}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band height="18" splitType="Stretch">
			<textField pattern="###0.00">
				<reportElement x="686" y="0" width="51" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{backord_grand}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="293" y="0" width="146" height="18"/>
				<textElement textAlignment="Left" verticalAlignment="Middle">
					<font fontName="Times New Roman" isBold="true"/>
				</textElement>
				<text><![CDATA[Grand Total]]></text>
			</staticText>
			<textField pattern="###0.00">
				<reportElement x="484" y="0" width="51" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{ord_grand}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00">
				<reportElement x="581" y="0" width="51" height="18"/>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font fontName="Times New Roman"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{ship_grand}]]></textFieldExpression>
			</textField>
		</band>
	</summary>
</jasperReport>
