My Oracle Support Banner

Margin Analysis Report With Order Management Performance Deteriorated After Upgrade To R12 (Doc ID 1596289.1)

Last updated on FEBRUARY 21, 2020

Applies to:

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

Symptoms

Margin Analysis Report with Order Management performance deteriorated after upgrade to R12.1.3 from 11.5.10.2
For similar data in 11.5.10.2, report use to take 10 to 11 minutes. In R12, it runs for more than 1 hour.

This is the query below. I had customer upgrade to patch 13489446:R12.BOM.C to deliver CSTROMMA.rdf 120.2.12010000.6

Post patch query still slow as before. I can't seem to find the query in code search...may be a .pls upgrade needed, but not finding much reference at all. Does anybody have any additional information/suggestions. Thx

Query:

SELECT MC.SEGMENT1 Category , MSI.SEGMENT1 Item_Number , MSI . description
 Description , MSI . primary_uom_code Primary_Uom_Code , MSI .
 invoiceable_item_flag Invoiceable_Item_Flag , MSI . shippable_item_flag
 Shippable_Item_Flag , decode ( : P_CUST_OPT , 1 , bill_to_customer_name , 2
 , sold_to_customer_name , 3 , ship_to_customer_name , sold_to_customer_name
 ) Cust_Name , SOT . name Order_Type , DECODE ( : P_sort_option , 4 , temp .
 order_number , NULL ) Order_Num_Sort , DECODE ( : P_sort_option , 4 ,
 decode ( : P_CUST_OPT , 1 , bill_to_customer_name , 2 ,
 sold_to_customer_name , 3 , ship_to_customer_name , sold_to_customer_name )
 , NULL ) Customer_Sort , temp . order_number Order_Num , temp .
 parent_line_number Line_Num , decode ( temp . origin , 1 , 'ORDER' , 2 ,
 'RMA' , 3 , 'CR-MEMO' ) Origin , decode ( origin , 2 , rma_number , 3 ,
 temp . cr_trx_id , NULL ) rma_invoice_number , decode ( origin , 2 ,
 rma_line_number , 3 , temp . cr_trx_line_id , NULL )
 Rma_Invoice_Line_Number , NVL ( SUM ( NVL ( temp . invoice_quantity , 0 ) )
 , 0 ) Invoice_Qty , NVL ( SUM ( NVL ( temp . ship_quantity , 0 ) ) , 0 )
 Ship_Qty , ROUND ( SUM ( NVL ( temp . invoiced_amount , 0 ) ) * :
 P_exchange_rate / : P_round_unit ) * : P_round_unit Sales_Amount , ROUND (
 SUM ( NVL ( NVL( temp.cogs_amount, 0 ) , 0 ) ) * : P_exchange_rate / :
 P_round_unit ) * : P_round_unit Cogs_Amount , msi2 . concatenated_segments
 config_item
FROM
CST_MARGIN_SUMMARY TEMP , mtl_categories MC , mtl_item_categories MIC ,
 mtl_system_items MSI , mtl_system_items_kfv msi2 , oe_order_lines_all oola ,
  so_order_types_all SOT

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


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