R12: Invoice Price Variance Report Performance Issue - Never Completes - Remains as Running (Doc ID 1999535.1)

Last updated on APRIL 13, 2017

Applies to:

Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On Application Release 12.1.3 and 12.2.X
POXRCIPV module: Invoice Price Variance Report performing poorly and here is below the expensive SQL statement :

SELECT /*+ push_pred(apipv) leading(glps msi)*/ apipv . invoice_num Invoice_Num , apipv . invoice_date Invoice_Date , decode ( decode ( apipv . quantity_invoiced , 0 , 0 , null , 0 , nvl ( apipv . quantity_invoiced , 1 ) / abs ( nvl ( apipv . quantity_invoiced , 1 ) ) ) , 0 , plc1 . displayed_field , 1 , plc2 . displayed_field , - 1 , plc3 . displayed_field ) Entry_Type , DECODE(MCA.STRUCTURE_ID, 2,(MCA.SEGMENT1), 3,(MCA.SEGMENT1), 4,(MCA.SEGMENT1), 5,(MCA.SEGMENT1), 6,(MCA.SEGMENT1), 101,(MCA.SEGMENT1), 201,(MCA.SEGMENT1), 50135,(MCA.SEGMENT1||'

....

....

....

FROM gl_code_combinations gcc1 , gl_code_combinations gcc , gl_period_statuses glps , po_distributions pod , po_line_locations pll , po_lines pol , po_releases por , po_headers poh , po_vendors pov , ap_invoice_price_var_v apipv , mtl_system_items msi , mtl_categories mca , po_lookup_codes plc1 , po_lookup_codes plc2 , po_lookup_codes plc3 , hr_locations_all_tl lot , rcv_transactions rct WHERE apipv.po_distribution_id = pod.po_distribution_id AND pod.line_location_id = pll.line_location_id AND pll.po_line_id = pol.po_line_id AND pol.po_header_id = poh.po_header_id AND pll.po_release_id = por.po_release_id (+) AND poh.vendor_id = pov.vendor_id (+) AND pol.item_id = msi.inventory_item_id (+) AND msi.organization_id = : c_organization_id AND pol.category_id = mca.category_id AND gcc.code_combination_id = pod.code_combination_id AND gcc1.code_combination_id = pod.variance_account_id AND lot.location_id (+) = pll.ship_to_location_id AND pll.ship_to_location_id is not null AND LOT.LANGUAGE (+) = USERENV ( 'LANG' ) AND pod.destination_type_code in ( 'INVENTORY' , 'SHOP FLOOR' ) AND plc1.lookup_type = 'POXRCIPV' AND plc1.lookup_code = 'ADJUSTMENT' AND plc2.lookup_type = 'POXRCIPV' AND plc2.lookup_code = 'ENTRY' AND plc3.lookup_type = 'POXRCIPV' AND plc3.lookup_code = 'REVERSAL' AND nvl ( pov.vendor_name , 'A' ) BETWEEN nvl ( : P_vendor_from , nvl ( pov.vendor_name , 'A' ) ) AND nvl ( : P_vendor_to , nvl ( pov.vendor_name , 'A' ) ) AND glps.application_id = 201 AND glps.set_of_books_id = : set_of_books AND glps.period_name = nvl ( : P_Period_Name , glps.period_name ) AND apipv.accounting_date BETWEEN glps.start_date AND glps.end_date AND apipv.rcv_transaction_id = rct.transaction_id (+) AND poh.type_lookup_code in ( 'STANDARD' , 'BLANKET' , 'PLANNED' ) AND pll.shipment_type in ( 'STANDARD' , 'BLANKET' , 'SCHEDULED' ) AND 1 = 1 ORDER BY 4 ASC,20 ASC,5 ASC,16 ASC,17 ASC,18 ASC,21 ASC , 2 , 12

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms