ONT Credit Order Discrepancy

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Credit Order Discrepancy Report
Application: Order Management
Source: Credit Order Discrepancy Report (XML)
Short Name: OEXCRDIS_XML
DB package: ONT_OEXCRDIS_XMLP_PKG
SELECT
	c.name			oe_customer_name
,	c.customer_number	oe_customer_number
,	oeh.header_id		oe_header_id
,	oeh.order_number	oe_number
,	OEOT.name		oe_order_type
,	to_char(oeh.ordered_date,'DD-MON-YY')	oe_order_date
,	oel.line_id		oe_line_id
,	oel.inventory_item_id	oe_inventory_item_id
,	oel.ship_from_org_id	oe_ship_from_org_id
,	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
,OEL.INVENTORY_ITEM_ID
,OEL.ORDERED_ITEM_ID
,OEL.ORDERED_ITEM
/*,	&RP_ITEM_FLEX_ALL_SEG ITEM_FLEX*/
,	oel.item_revision	oe_item_revision
,	oel.ordered_quantity - nvl(oel.cancelled_quantity, 0) 	oe_quantity
,	OELT.name		oe_line_type
,	oeship.location_code	customer_ship_location
,	om_reports_common_pkg.oexoeors_get_workflow_date(oel.line_id)          Receipt_Date
, 	oel.end_item_unit_number	oe_unit, 
	&item_dsp item_dsp,
	ONT_OEXCRDIS_XMLP_PKG.rp_dummy_item_p rp_dummy_item,
	ONT_OEXCRDIS_XMLP_PKG.ITEM_DSPFORMULA(oel.item_identifier_type,oel.inventory_item_id ,:rp_dummy_item , :item_dsp, :ORDERED_ITEM_ID, :ordered_item ,MSI.ORGANIZATION_ID,MSI.INVENTORY_ITEM_ID) CP_ITEM_DSP
FROM
	oe_ship_from_orgs_v	oeship 
,	mtl_system_items_vl	msi 
,	oe_order_lines_all	oel
,	OE_TRANSACTION_TYPES_TL OEOT
,	OE_TRANSACTION_TYPES_TL OELT
,	oe_order_headers_all	oeh
,	oe_sold_to_orgs_v	c 
WHERE 
	oeh.sold_to_org_id 	= c.customer_id
and    	oeh.header_id		= oel.header_id
and	OEOT.TRANSACTION_TYPE_ID= oeh.order_type_id
and	OELT.TRANSACTION_TYPE_ID= oel.line_type_id
and	msi.inventory_item_id  	= oel.inventory_item_id
and	nvl(msi.organization_id,0)  	= nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
and	nvl(oel.org_id,0) 	= nvl(oeh.org_id,0)
and 	nvl(oeh.org_id,0) 	= nvl(:p_org_id	,0)
and     OEOT.language = userenv('LANG')             
and     OELT.language = userenv('LANG')              
and	oeship.organization_id(+)  = oel.ship_to_org_id
and    (         (om_reports_common_pkg.oexcrdis_get_lot_serial_ctl(oel.inventory_item_id, oel.ship_from_org_id) = 2
	        and  EXISTS
		(SELECT	 'X'
		 FROM  	
		              	mtl_material_transactions invls,
			mtl_transaction_lot_numbers invlot
		WHERE
			invls.transaction_source_id =
				 oe_order_sch_util.get_mtl_sales_order_id(oeh.header_id)
		and 	invls.trx_source_line_id = oel.line_id
		and	invls.transaction_source_type_id = 12
		and	nvl(invls.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and 	nvl(invlot.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and	invls.transaction_id = invlot.transaction_id	
		and 	not exists
			(select 'X'
			from oe_lot_serial_numbers oels
			where oels.line_id = oel.line_id
			and oels.quantity  = invls.transaction_quantity
			and oels.lot_number  = invlot.lot_number  )
		  )
	       )
	or  	
	        (       om_reports_common_pkg.oexcrdis_get_lot_serial_ctl(oel.inventory_item_id, oel.ship_from_org_id) = 1
	        and  EXISTS
		(SELECT	 'X'
		 FROM  	mtl_material_transactions invls,
			mtl_unit_transactions invser
		WHERE
			invls.transaction_source_id =
				 oe_order_sch_util.get_mtl_sales_order_id(oeh.header_id)
		and 	invls.trx_source_line_id = oel.line_id
		and	invls.transaction_source_type_id = 12
		and	invls.transaction_id = invser.transaction_id	
		and	nvl(invls.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and	nvl(invser.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and 	not exists
			(select 'X'
			from    oe_lot_serial_numbers oels
			where  oels.line_id = oel.line_id
		and      rtrim( oels.from_serial_number, '0123456789' ) = 
			    rtrim( invser.serial_number, '0123456789' )
	             	and      to_number( nvl( substr( invser.serial_number,
       			nvl( length( rtrim( invser.serial_number, '0123456789' ))
            			+ 1, 0 ), length( invser.serial_number )), 0 ))
		             between
		             to_number( nvl( substr( oels.from_serial_number,
       			nvl( length( rtrim( oels.from_serial_number, '0123456789' ))
            				+ 1, 0 ), length( oels.from_serial_number )), 0 ))
			             and
		              to_number( nvl( substr( oels.to_serial_number,
      			 nvl( length( rtrim( oels.to_serial_number, '0123456789' ))
            				+ 1, 0 ), length( oels.to_serial_number )), 0 ))
		       )  
		   )
	         )
	or  	
	        (       om_reports_common_pkg.oexcrdis_get_lot_serial_ctl(oel.inventory_item_id, oel.ship_from_org_id) = 3
	        and  EXISTS
		(SELECT	 'X'
		 FROM  	
		              	mtl_material_transactions invls,
			mtl_transaction_lot_numbers invlot,
			mtl_unit_transactions invser
		WHERE
			invls.transaction_source_id =
				 oe_order_sch_util.get_mtl_sales_order_id(oeh.header_id)
		and 	invls.trx_source_line_id = oel.line_id
		and	invls.transaction_source_type_id = 12
		and	invls.transaction_id = invlot.transaction_id	
		and	invlot.serial_transaction_id = invser.transaction_id
		and 	nvl(invls.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and 	nvl(invlot.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and 	nvl(invser.organization_id,0) = nvl(oel.ship_from_org_id,0)
		and 	not exists
			(select 'X'
			from    oe_lot_serial_numbers oels
			where  oels.line_id = oel.line_id
			and      oels.lot_number  = invlot.lot_number
		and      rtrim( oels.from_serial_number, '0123456789' ) = 
			    rtrim( invser.serial_number, '0123456789' )
	             	and      to_number( nvl( substr( invser.serial_number,
       			nvl( length( rtrim( invser.serial_number, '0123456789' ))
            			+ 1, 0 ), length( invser.serial_number )), 0 ))
		           between
		            to_number( nvl( substr( oels.from_serial_number,
       			nvl( length( rtrim( oels.from_serial_number, '0123456789' ))
            				+ 1, 0 ), length( oels.from_serial_number )), 0 ))
			           and
		            to_number( nvl( substr( oels.to_serial_number,
      			 nvl( length( rtrim( oels.to_serial_number, '0123456789' ))
            				+ 1, 0 ), length( oels.to_serial_number )), 0 ))
		           	 )  
		)
	         )  )
--&lp_open_order_only
&lp_order_num_low
&lp_order_num_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_order_date_low
&lp_order_date_high
&lp_order_type_low
&lp_order_type_high
--
&lp_item1
--
&lp_item
--
&lp_receipt_date_low
&lp_receipt_date_high
&lp_order_by
Parameter NameSQL textValidation
Operating Unit
 
LOV
Sort By
 
LOV Oracle
Order Numbers From
 
Number
Order Numbers To
 
Number
Order Date From
 
Date
Order Date To
 
Date
Order Types From
 
LOV Oracle
Order Types To
 
LOV Oracle
Customers From
 
LOV Oracle
Customers To
 
LOV Oracle
Customer Numbers From
 
LOV Oracle
Customer Numbers To
 
LOV Oracle
Sales Order Items From
 
Char
Sales Order Items To
 
Char
Receipt Dates From
 
Date
Receipt Dates To
 
Date
Item Display
 
LOV Oracle
Download
Related Blitz Reports
CAC Internal Order Shipment Margin EC: INV Item Upload INV Items INV Item Upload PO Receiving Transactions Register ECC Genealogy and Trace, Sales Orders CAC Purchase Price Variance ECC Process Genealogy and Trace, Sales Orders PO Receipt Adjustments
Blitz Report™