ONT Internal Order and Purchasing Requisition Discrepancy

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Internal Order and Purchasing Requisition Discrepancy Report (XML)
Short Name: OEXIODIS_XML
DB package: ONT_OEXIODIS_XMLP_PKG
SELECT	c.name	om_customer_name
,	c.customer_number	om_customer_number
,	oeh.header_id	om_header_id
,	oeh.order_number	om_order_number
,	oet.name	om_order_type
,	oeh.ordered_date	om_order_date
,	oel.line_id		om_line_id
,	lk.meaning    	om_open_order
,	oel.line_number 
		|| decode(oel.shipment_number, null, null, '.'||oel.shipment_number)
		|| decode(oel.option_number, null, null, '.'||oel.option_number) 
			line_shipment_option_number
,oel.item_identifier_type
 --  &Item_dsp item_flex
,    oel.inventory_item_id
,    oel.ORDERED_ITEM_ID
,    oel.ordered_item
	--&RP_ITEM_FLEX2		rq_item_flex
,	porh.segment1		requisition_number
,	porl.line_num		requisition_line_number
,	msi2.description		rq_item_description
,   oel.ordered_quantity om_quantity
,    porl.quantity             rq_quantity
,	oel.order_quantity_uom		om_unit
,	muom2.uom_code		rq_unit
,	oel.schedule_arrival_date om_schedule_date  
,	porl.need_by_date rq_needby_date
,	rasu.location_code		om_ship_to_location
,	rasu2.location		rq_ship_to_location, 
	&CF_so_item_display CF_so_item_display, 
	ONT_OEXIODIS_XMLP_PKG.cf_so_holdformula(oel.line_id, oeh.header_id) CF_so_hold, 
--	&item_dsp item_dsp, 
	&CF_item_flex CF_item_flex, 
	ONT_OEXIODIS_XMLP_PKG.cf_po_item_displayformula(:CF_item_flex, msi2.description) CF_item_display,
	ONT_OEXIODIS_XMLP_PKG.item_dspFormula(oel.item_identifier_type,oel.ordered_item_id,oel.ordered_item,msi.ORGANIZATION_ID,msi.INVENTORY_ITEM_ID) Item_dsp
FROM
	oe_ship_to_orgs_v		rasu 
,              HZ_CUST_SITE_USES_ALL        rasu2 
,	mtl_system_items   		msi 
,	mtl_system_items_b_kfv		msi2
,               mtl_system_items_tl                msit
,	mtl_units_of_measure		muom2
,	oe_lookups			lk
,	po_location_associations	pola2
,	po_requisition_lines_all	porl
,	po_requisition_headers_all 	porh
,	oe_order_lines_all		oel
,	oe_transaction_types_tl		oet
,	oe_order_headers_all		oeh
,	oe_sold_to_orgs_v		c 
WHERE 	oeh.order_source_id	= 10
and	oeh.sold_to_org_id 	= c.customer_id(+)
and	oet.transaction_type_id(+) = oeh.order_type_id 
and     oet.language (+) = userenv('LANG') 
and	msi.inventory_item_id (+)= oel.inventory_item_id
and	nvl(msi.organization_id(+),0) 	= :RP_DUMMY_ITEM
and	lk.lookup_type	= 'YES_NO'
and	lk.lookup_code 	= nvl(oeh.open_flag,'N')
and	msi2.inventory_item_id(+)	= porl.item_id
and	nvl(msi2.organization_id(+),0)	= :RP_DUMMY_ITEM
and     msi2.INVENTORY_ITEM_ID = msit.INVENTORY_ITEM_ID
and     msi2.ORGANIZATION_ID = msit.ORGANIZATION_ID 
AND     msit.LANGUAGE = userenv('LANG')
and 	porh.requisition_header_id   = porl.requisition_header_id
and 	porh.segment1 = oel.orig_sys_document_ref
and 	porh.segment1 = oeh.orig_sys_document_ref
and 	to_char(porl.line_num) = oel.orig_sys_line_ref
and 	oeh.header_id = oel.header_id
and	rasu.organization_id(+)	= oel.ship_to_org_id
and	rasu2.site_use_id(+)	= pola2.site_use_id
and	pola2.location_id(+)	= porl.deliver_to_location_id
and	muom2.unit_of_measure(+)	= porl.unit_meas_lookup_code
and    ( nvl(oel.org_id,0) = nvl(:p_organization_id,0) 
	or nvl(porl.org_id,0) = nvl(:p_organization_id,0))
and     porl.requisition_header_id = oel.source_document_id  -- Added for 8702891
and     porl.requisition_line_id = oel.source_document_line_id  -- Added for 8702891
and	nvl(rasu2.org_id,0) = nvl(:p_organization_id,0)
and	( 	porl.item_id 	 	!= oel.inventory_item_id
      	OR  trunc(porl.need_by_date) != trunc(oel.schedule_arrival_date) 
		OR  pola2.site_use_id   != oel.ship_to_org_id 
		OR  muom2.uom_code 	 != oel.order_quantity_uom
		OR	porl.quantity  	  != oel.ordered_quantity - oel.cancelled_quantity
		OR	0
		NOT IN	( select 	decode( count(order_hold_id), 0, 0, 1)
			  from 	oe_order_holds_all
			  where  	header_id = oel.header_id
			  and	hold_release_id is null	
			  and	(line_id = oel.line_id or line_id is null)  )				
		) 
&lp_open_order_only
&lp_order_num_low
&lp_order_num_high
&lp_order_date_low
&lp_order_date_high
&lp_order_type_low
&lp_order_type_high
and &lp_item
&lp_requisition_num_low
&lp_requisition_num_high
ORDER BY 3 ASC,4 ASC,5 ASC,6 ASC,1 ASC,2 ASC,7 ASC,13 ASC,10 ASC,11 ASC,14 ASC,12 ASC,9 ASC,19 ASC,21 ASC,23 ASC,25 ASC , :CF_order_by
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Item To
 
From Item
 
To Requisition Number
 
LOV Oracle
From Requisition Number
 
LOV Oracle
Item Display
 
LOV Oracle
To Order Type
 
LOV Oracle
From Order Type
 
LOV Oracle
To Order Date
 
Date
From Order Date
 
Date
To Order Number
 
Number
From Order Number
 
Number
Open Orders Only
 
LOV Oracle
Sort By
 
LOV Oracle