WMS ASN DISCREPANCY

Description
Categories: BI Publisher
Columns: Shipment Num, Item Id, Quantity Expected, Quantity Actual, Unit Of Measure Expected, Unit Of Measure Actual, Lpn Expected, Lpn Actual, Item Revision Expected, Item Revision Actual ...
Application: Warehouse Management
Source: WMS ASN DISCREPANCY REPORT (XML)
Short Name: WMSASNI_XML
DB package: WMS_WMSASNI_XMLP_PKG
select 
shipment_num,
item_id,
nvl(to_char(quantity_expected),' ') quantity_expected,
nvl(to_char(quantity_actual),' ')   quantity_actual,
nvl(unit_of_measure_expected,' ') unit_of_measure_expected,
nvl(unit_of_measure_actual,' ') unit_of_measure_actual,
lpn_expected,
lpn_actual,
nvl(item_revision_expected,' ') item_revision_expected ,
nvl(item_revision_actual,' ' ) item_revision_actual ,
lot_number_expected,
lot_number_actual,
vendor_id, 
	WMS_WMSASNI_XMLP_PKG.cf_2formula(item_id) CF_2, 
	WMS_WMSASNI_XMLP_PKG.cf_9formula(vendor_id) CF_9
from
  wms_asn_details asn
where  asn.organization_id = :ORG_ID
  and  asn.item_id = nvl(:P_ITEM_ID,asn.item_id)
  and  asn.shipment_num = nvl(:P_SHIPMENT_NUMBER,asn.shipment_num)
  and  (
             ( asn.vendor_id = :P_VENDOR_ID and :P_VENDOR_ID is not null)
             or 
             (:P_VENDOR_ID is null)
          ) 
 and  (
            ( asn.vendor_site_id = :P_VENDOR_SITE_ID and :P_VENDOR_SITE_ID is not null)
            or 
           (:P_VENDOR_SITE_ID is null)
          )
 and  (
             (  (  trunc(asn.TRANSACTION_DATE)  between  trunc(:P_FROM_DATE)
 and trunc(:P_END_DATE)  )
               and 
            (:P_FROM_DATE is not null or :P_END_DATE is not null)
              )
             or 
             (:P_FROM_DATE is null and :P_END_DATE is null)
          )
 and   (    
            ( nvl (:P_SHOW_ONLY_DISC,'N') = 'N' and discrepancy_status = discrepancy_status ) 
            or
           ( nvl (:P_SHOW_ONLY_DISC,'N') = 'Y' and discrepancy_status in ('E' ,'F')  ) 
        )
  and  discrepancy_reporting_context = 'I'
Parameter Name SQL text Validation
Organization
 
LOV Oracle
Shipment Num
 
LOV Oracle
Item
 
LOV Oracle
From Date
 
Date
To Date
 
Date
Supplier
 
LOV Oracle
Supplier Site
 
LOV Oracle
Check LPN
 
LOV Oracle
Check Revision
 
LOV Oracle
Check Lot Number
 
LOV Oracle
Check Serial Number
 
LOV Oracle
Show Only Discrepancies
 
LOV Oracle