AutoInvoice Performance Issue: Improper Hint in Package AR_REVENUE_MANAGEMENT_PVT (Doc ID 1954235.1)

Last updated on JUNE 08, 2016

Applies to:

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

Symptoms

On : 12.1.3 version, Transactions Related

Find that autoinvoice is running for a long time

See the following as the long running statement:

  
INSERT INTO AR_RDR_PARAMETERS_GT ( SOURCE_LINE_ID, BATCH_SOURCE_ID, 
    PROFILE_CLASS_ID, CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID, CUST_TRX_TYPE_ID, 
    INVENTORY_ITEM_ID, MEMO_LINE_ID, ORG_ID, ACCOUNTING_RULE_ID, 
    SHIP_TO_CUST_ACCT_ID, SHIP_TO_SITE_USE_ID ) 
SELECT
  CTL.CUSTOMER_TRX_LINE_ID,
     CT.BATCH_SOURCE_ID, DECODE(CTL.DEFERRAL_EXCLUSION_FLAG, 'Y','', 
    DECODE(HCP.CUST_ACCOUNT_ID,'','', DECODE(HCP.SITE_USE_ID,'','', 
    HCP.PROFILE_CLASS_ID))), CT.BILL_TO_CUSTOMER_ID, CT.BILL_TO_SITE_USE_ID, 
    CTT.CUST_TRX_TYPE_ID, CTL.INVENTORY_ITEM_ID, CTL.MEMO_LINE_ID, CT.ORG_ID, 
    CTL.ACCOUNTING_RULE_ID, NVL(CTL.SHIP_TO_CUSTOMER_ID,CT.SHIP_TO_CUSTOMER_ID),
     NVL(CTL.SHIP_TO_SITE_USE_ID,CT.SHIP_TO_SITE_USE_ID) 
FROM RA_CUSTOMER_TRX 
    CT, RA_CUSTOMER_TRX_LINES CTL, HZ_CUSTOMER_PROFILES HCP, RA_CUST_TRX_TYPES CTT 
WHERE CT.REQUEST_ID = :B1 
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID 
AND CTT.TYPE = 'INV' 
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID 
AND CTL.LINE_TYPE = 'LINE' 
AND CT.BILL_TO_CUSTOMER_ID = HCP.CUST_ACCOUNT_ID (+) 
AND CT.BILL_TO_SITE_USE_ID = NVL(HCP.SITE_USE_ID, CT.BILL_TO_SITE_USE_ID) 
AND NVL(CTL.DEFERRAL_EXCLUSION_FLAG, 'N') <> 'Y'
  
  
  call     count       cpu    elapsed       disk      query    current      rows
  ------- ------  -------- ---------- ---------- ---------- ----------   ----------
  Parse        1      0.00       0.00          0          0          0        0
  Execute      1     36.06      41.86     340222     343462         84     2488
  Fetch        0      0.00       0.00          0          0          0        0
  ------- ------  -------- ---------- ---------- ---------- ----------   ----------
  total        2     36.06      41.86     340222     343462         84     2488
  
  Misses in library cache during parse: 1
  Misses in library cache during execute: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 173     (recursive depth: 1)
  Number of plan statistics captured: 1
  

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