<?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="Unnamed" pageWidth="864" pageHeight="650" orientation="Landscape" columnWidth="824" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<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="as_site_code" class="java.lang.String"/>
	<parameter name="as_inv_fr" class="java.lang.String"/>
	<parameter name="as_inv_to" class="java.lang.String"/>
	<queryString>
		<![CDATA[select
	inv_hold_det.item_code as itemcd ,
	inv_hold_det.site_code as sitecd ,
	inv_hold_det.loc_code as loccd,
 	inv_hold_det.lot_no as lotno ,
 	inv_hold_det.lot_sl as lotsl,
	stock.quantity as stkqty ,
	stock.alloc_qty as stkallocqty ,
	stock.hold_qty as stkholdqty,
	invstat.inv_stat as invstat,
	invstat.descr as invstatdesc,
	item.descr as itemdescr ,
	( select descr from disparm where disparm.prd_code ='999999' and var_name in 	('EXP_LOCK','NEAR_EXP_LOCK') and var_value = trim(inv_hold.lock_code)) AS disexplock,
CASE WHEN INSTR((DDF_GET_DISPARM_VALUE('PRT_ZEROCASE_INVSTAT','999999')),trim(INVSTAT.INV_STAT)) > 0 THEN 0 else ceil((STOCK.QUANTITY/STOCK.QTY_PER_ART)) end as no_of_cases,
	stock.exp_date as stkexp_date
from
		inv_hold,
		inv_hold_det,
		stock,
		item,
		invstat,
		location

where
		inv_hold.tran_id = inv_hold_det.tran_id
and 	inv_hold.lock_code in ('EXP_LOCK','NEXP_LOCK' )
and 	inv_hold_det.hold_status ='H'
and 	stock.quantity >0
and 	inv_hold_det.item_code = stock.item_code
and 	inv_hold_det.site_code = stock.site_code
and 	inv_hold_det.loc_code = stock.loc_code
and 	inv_hold_det.lot_no = stock.lot_no
and 	inv_hold_det.lot_sl = stock.lot_sl
and 	inv_hold_det.item_code = item.item_code
and 	location.inv_stat = invstat.inv_stat
and	location.loc_code = inv_hold_det.loc_code
and 	inv_hold.site_code = '$P!{as_site_code}'
and 	invstat.inv_stat >= '$P!{as_inv_fr}'
and  	invstat.inv_stat <= '$P!{as_inv_to}'
order by
		inv_hold.site_code,
		disexplock,
		invstat.descr,
		inv_hold_det.loc_code,
		item.descr ,
		inv_hold_det.lot_no,
		inv_hold_det.lot_sl]]>
	</queryString>
	<field name="sitecd" class="java.lang.String"/>
	<field name="itemcd" class="java.lang.String"/>
	<field name="loccd" class="java.lang.String"/>
	<field name="lotno" class="java.lang.String"/>
	<field name="lotsl" class="java.lang.String"/>
	<field name="itemdescr" class="java.lang.String"/>
	<field name="invstat" class="java.lang.String"/>
	<field name="invstatdesc" class="java.lang.String"/>
	<field name="stkallocqty" class="java.lang.Double"/>
	<field name="stkholdqty" class="java.lang.Double"/>
	<field name="disexplock" class="java.lang.String"/>
	<field name="stkqty" class="java.lang.Double"/>
	<field name="no_of_cases" class="java.lang.Double"/>
	<field name="stkexp_date" class="java.util.Date"/>
	<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="disexplock" isStartNewPage="true" isResetPageNumber="true">
		<groupExpression><![CDATA[$F{disexplock}]]></groupExpression>
		<groupHeader>
			<band/>
		</groupHeader>
		<groupFooter>
			<band/>
		</groupFooter>
	</group>
	<pageHeader>
		<band height="118" splitType="Stretch">
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="63" width="787" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[------------------------------------------------------------------------------------------------------------------------------------]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="105" width="787" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[------------------------------------------------------------------------------------------------------------------------------------]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="78" width="56" height="24">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Type of Location]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="148" y="78" width="70" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Item Code]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="223" y="78" width="79" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Item Descr]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="86" y="78" width="59" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Location]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="359" y="78" width="74" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Lot No]]></text>
			</staticText>
			<textField evaluationTime="Page" pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="663" y="49" width="64" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{varPage01}]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Group" evaluationGroup="disexplock" pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="727" y="49" width="49" height="13"/>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{varPgCount02}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="49" width="69" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Site Code:]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="0" width="798" height="26">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Center">
					<font fontName="Courier New" size="20" isBold="false"/>
				</textElement>
				<text><![CDATA[Short Expiry/Expiry Report]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="437" y="78" width="67" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[LPN]]></text>
			</staticText>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="94" y="49" width="49" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isUnderline="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{sitecd}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="623" y="78" width="52" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Quantity]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="695" y="78" width="30" height="24">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[HoldQty]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="735" y="78" width="65" height="24">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[AllocationQty]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="580" y="77" width="36" height="25">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[No Of Cases]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="513" y="78" width="56" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[Exp Date]]></text>
			</staticText>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="23" y="26" width="798" height="23"/>
				<textElement textAlignment="Center">
					<font fontName="Courier New" size="15"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{disexplock}]]></textFieldExpression>
			</textField>
		</band>
	</pageHeader>
	<detail>
		<band height="17" splitType="Stretch">
			<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="23" y="0" width="56" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isUnderline="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{invstat}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">
				<reportElement positionType="Float" x="226" y="0" width="131" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isUnderline="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{itemdescr}]]></textFieldExpression>
			</textField>
			<textField pattern="dd/MM/yy" isBlankWhenNull="true">
				<reportElement positionType="Float" x="151" y="0" width="70" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isUnderline="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{itemcd}]]></textFieldExpression>
			</textField>
			<textField pattern="0.000" isBlankWhenNull="true">
				<reportElement positionType="Float" x="362" y="0" width="74" height="13"/>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{lotno}]]></textFieldExpression>
			</textField>
			<textField pattern="0.000" isBlankWhenNull="true">
				<reportElement positionType="Float" x="86" y="0" width="59" height="13"/>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{loccd}]]></textFieldExpression>
			</textField>
			<textField pattern="0.000" isBlankWhenNull="true">
				<reportElement positionType="Float" x="440" y="0" width="67" height="13"/>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{lotsl}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="15" y="0" width="8" height="13">
					<printWhenExpression><![CDATA[new Boolean($F{stkallocqty}.doubleValue()>0?true:false)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[*]]></text>
			</staticText>
			<textField pattern="###0" isBlankWhenNull="true">
				<reportElement positionType="Float" x="613" y="0" width="62" height="13"/>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{stkqty}]]></textFieldExpression>
			</textField>
			<textField pattern="###0" isBlankWhenNull="true">
				<reportElement positionType="Float" x="679" y="0" width="52" height="13"/>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{stkholdqty}]]></textFieldExpression>
			</textField>
			<textField pattern="###0" isBlankWhenNull="true">
				<reportElement positionType="Float" x="735" y="0" width="65" height="13"/>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{stkallocqty}]]></textFieldExpression>
			</textField>
			<textField pattern="###0" isBlankWhenNull="true">
				<reportElement positionType="Float" x="580" y="0" width="27" height="13"/>
				<textElement textAlignment="Right">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{no_of_cases}]]></textFieldExpression>
			</textField>
			<textField pattern="MM/dd/yy" isBlankWhenNull="true">
				<reportElement positionType="Float" x="513" y="0" width="56" height="13"/>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10"/>
				</textElement>
				<textFieldExpression class="java.util.Date"><![CDATA[$F{stkexp_date}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band height="110" splitType="Stretch">
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="23" y="0" width="798" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[------------------------------------------------------------------------------------------------------------------------------------]]></text>
			</staticText>
			<staticText>
				<reportElement positionType="Float" mode="Transparent" x="17" y="13" width="634" height="13">
					<printWhenExpression><![CDATA[new Boolean(true)]]></printWhenExpression>
				</reportElement>
				<textElement textAlignment="Left">
					<font fontName="Courier New" size="10" isBold="false"/>
				</textElement>
				<text><![CDATA[* Stock Allocated - Please Deallocate & Proceed with the Stock Transfer]]></text>
			</staticText>
		</band>
	</pageFooter>
</jasperReport>
