ONT Credit Order Discrepancy

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