R12.1.3: Invoice Price Variance Report Performance Issue (Doc ID 2834426.1)

Last updated on JANUARY 19, 2022

Applies to:

Symptoms

On : 12.1.3 version, Reports - PO and RCV

ACTUAL BEHAVIOR
---------------
Invoice Price Variance report is taking a long time to complete

EXPECTED BEHAVIOR
-----------------------
Program should not take long to finish

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to request submission form
2. Select Invoice Price Variance report
3. Submit report

Observe program taking long hours to complete.

Following is identified as costliest SQL from Trace.

================================
SELECT /*+ leading(plc1,plc2,plc3,glps,msi,pol) */ 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,NULL, 3,(MCA.SEGMENT1), 4,(MCA.SEGMENT1), 5,(MCA.SEGMENT1), 6,(MCA.SEGMENT1), 101,(MCA.SEGMENT1||'

....

....

....

NULL) C_CHARGE_ACCT , round ( ( apipv . quantity_invoiced / ( decode ( pll . match_option , 'R' , po_uom_s . po_uom_convert_p ( pol . unit_meas_lookup_code , rct . unit_of_measure , pol . item_id ) , 1 ) ) ) , 20 ) QTY_INVOICED , nvl ( apipv . invoice_rate , 1 ) Invoice_rate , apipv . invoice_amount Invoice_amount , apipv . invoice_price Invoice_price , nvl ( pod . rate , nvl ( poh . rate , 1 ) ) Po_rate , pll . price_override Po_price , rct . unit_of_measure receipt_Unit , pol . item_id item_id , msi . description Item_Description , pov . vendor_name Vendor , decode ( poh . type_lookup_code , 'BLANKET' , poh . segment1 || ' - ' || por . release_num , 'PLANNED' , poh . segment1 || ' - ' || por . release_num , poh . segment1 ) PO_Number_Release , poh . currency_code Currency , apipv . invoice_currency Invoice_currency , pol . line_num Line_Num , pol . unit_meas_lookup_code Unit , lot . location_code Location , round ( decode ( apipv . quantity_invoiced , 0 , decode ( apipv . price_var , null , decode ( apipv . invoice_rate , null , apipv . invoice_amount , apipv . invoice_base_amount ) , apipv . price_var * nvl ( apipv . invoice_rate , 1 ) ) , null , decode ( apipv . price_var , null , decode ( apipv . invoice_rate , null , apipv . invoice_amount , apipv . invoice_base_amount ) , apipv . price_var * nvl ( apipv . invoice_rate , 1 ) ) , apipv . invoice_price * nvl ( apipv . invoice_rate , 1 ) ) * ( decode ( pll . match_option , 'R' , po_uom_s . po_uom_convert_p ( pol . unit_meas_lookup_code , rct . unit_of_measure , pol . item_id ) , 1 ) ) , : c_ext_precision ) Invoice_Base_Price , round ( pll . price_override * decode ( pll . match_option , 'R' , decode ( rct . transaction_id , null , nvl ( poh . rate , 1 ) , nvl ( rct . currency_conversion_rate , 1 ) ) , nvl ( poh . rate , 1 ) ) , : c_ext_precision ) PO_Base_Price , decode ( apipv . invoice_rate , null , apipv . price_var , apipv . base_price_var ) base_inv_price_var , apipv . exch_rate_var ex_rate_vari 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

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.