My Oracle Support Banner

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

Last updated on DECEMBER 15, 2017

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
  

Changes

 

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
Changes
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
 Still Have Questions?
References

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