ONT Internal Order and Purchasing Requisition Discrepancy

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Internal Order and Purchasing Requisition Discrepancy Report
Application: Order Management
Source: Internal Order and Purchasing Requisition Discrepancy Report (XML)
Short Name: OEXIODIS_XML
DB package: ONT_OEXIODIS_XMLP_PKG
Run ONT Internal Order and Purchasing Requisition Discrepancy and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit 
 
 LOV
Sort By 
 
 LOV Oracle
Open Orders Only 
 
 LOV Oracle
From Order Number 
 
 Number
To Order Number 
 
 Number
From Order Date 
 
 Date
To Order Date 
 
 Date
From Order Type 
 
 LOV Oracle
To Order Type 
 
 LOV Oracle
Item Display 
 
 LOV Oracle
From Requisition Number 
 
 LOV Oracle
To Requisition Number 
 
 LOV Oracle
From Item 
 
 Char
Item To 
 
 Char
Download
Related Blitz Reports
GL Account Analysis (Distributions) INV Intercompany Invoice Reconciliation CAC Internal Order Shipment Margin GL Account Analysis ONT Orders and Lines CAC Open Internal ISO-IRO PPF_WP3_OM_DETAILS Yanmar - Discount on order lines check ONT DIFOT
Blitz Report™

Blitz Report™ provides multiple benefits: