My Oracle Support Banner

Querying Credit Memos In iReceivables is Very Slow (Doc ID 1202604.1)

Last updated on JUNE 27, 2018

Applies to:

Oracle iReceivables - Version 12.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 01-DEC-2016***

Symptoms

Querying credit memos in the Account TAB in iReceivables is very slow. It is taking about 40 seconds when querying an invoice takes 2-3 seconds.

Top query in tkprof is the following:

SELECT * FROM (SELECT temp.TRX_NUMBER,
temp.TRX_DATE,
temp.INVOICE_CURRENCY_CODE,
temp.CUSTOMER_ID,
temp.CUSTOMER_SITE_USE_ID,
temp.CUSTOMER_TRX_ID,
temp.CUST_TRX_TYPE_ID,
temp.CLASS1,
temp.STATUS,
temp.TERM_ID,
ct.PURCHASE_ORDER AS CT_PURCHASE_ORDER,
ct.previous_customer_trx_id as CT_PREVIOUS_CUSTOMER_TRX_ID,
temp.AMOUNT_DUE_ORIGINAL,
temp.AMOUNT_DUE_REMAINING,
temp.PAYMENT_SCHEDULE_ID,
temp.TERMS_SEQUENCE_NUMBER,
temp.DUE_DATE,
temp.CLASS2,
temp.AR_LOOKUP_CODE_STATUS_MEANING,
ct.INTERFACE_HEADER_CONTEXT,
ct.INTERFACE_HEADER_ATTRIBUTE1,
ct.INTERFACE_HEADER_ATTRIBUTE2,
ct.INTERFACE_HEADER_ATTRIBUTE3,
ct.INTERFACE_HEADER_ATTRIBUTE4,
ct.INTERFACE_HEADER_ATTRIBUTE5,
ct.INTERFACE_HEADER_ATTRIBUTE6,
ct.INTERFACE_HEADER_ATTRIBUTE7,
ct.INTERFACE_HEADER_ATTRIBUTE8,
ct.INTERFACE_HEADER_ATTRIBUTE9,
ct.INTERFACE_HEADER_ATTRIBUTE10,
ct.INTERFACE_HEADER_ATTRIBUTE11,
ct.INTERFACE_HEADER_ATTRIBUTE12,
ct.INTERFACE_HEADER_ATTRIBUTE13,
ct.INTERFACE_HEADER_ATTRIBUTE14,
ct.INTERFACE_HEADER_ATTRIBUTE15,
ct.ATTRIBUTE_CATEGORY,
ct.ATTRIBUTE1,
ct.ATTRIBUTE2,
ct.ATTRIBUTE3,
ct.ATTRIBUTE4,
ct.ATTRIBUTE5,
ct.ATTRIBUTE6,
ct.ATTRIBUTE7,
ct.ATTRIBUTE8,
ct.ATTRIBUTE9,
ct.ATTRIBUTE10,
ct.ATTRIBUTE11,
ct.ATTRIBUTE12,
ct.ATTRIBUTE13,
ct.ATTRIBUTE14,
ct.ATTRIBUTE15,
acct.account_number,
acct.account_name,
null selected_flag,
null ERROR_EXISTS,
ct.printing_option PRINTING_OPTION,
to_char(temp.AMOUNT_IN_DISPUTE) AMOUNT_IN_DISPUTE,
to_char(temp.AMOUNT_ADJUSTED_PENDING) AMOUNT_ADJUSTED_PENDING,
decode(ct.PRINTING_PENDING, 'N', fnd_message.get_string('AR', 'AR_ALL_YES'), fnd_message.get_string('AR', 'AR_ALL_NO')) PRINTED,
mo_global.get_ou_name(temp.org_id) as ORG_NAME,
to_char(ct.paying_customer_id) PAYING_CUSTOMER_ID,
to_char(ct.paying_site_use_id) PAYING_SITE_USE_ID,
ct.SHIP_DATE_ACTUAL SHIP_DATE,
temp.FREIGHT_AMOUNT,
temp.TAX_AMOUNT,
temp.PAYMENT_APPROVAL,
temp.APPROVAL_STATUS,
ct.BILL_TO_SITE_USE_ID,
ct.SHIP_TO_SITE_USE_ID,
billto_uses.Location bill_to_location,
shipto_uses.Location ship_to_location,
DECODE(temp.count_applied_to_trx,1,temp.APPLIED_TO_TRX_NUMBER,0,null,-1) as APPLIED_TO_TRX_NUMBER,
temp.count_applied_to_trx,
-- mulitple
ARI_UTILITIES.get_lookup_meaning('ARI_POPLIST_PROMPTS','POPLIST_PROMPTS_MULTIPLE') multiple,
decode(:1,'Y','Y','N', ari_utilities.check_external_user_access(:2 , applSched.customer_id , applSched.customer_site_use_id)) show_trx_number_link

FROM
(SELECT ArPaymentSchedulesV.TRX_NUMBER,
ArPaymentSchedulesV.TRX_DATE,
ArPaymentSchedulesV.INVOICE_CURRENCY_CODE,
ArPaymentSchedulesV.CUSTOMER_ID,
ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID,
ArPaymentSchedulesV.CUSTOMER_TRX_ID,
ArPaymentSchedulesV.CUST_TRX_TYPE_ID,
ArPaymentSchedulesV.CLASS AS CLASS1,
ArPaymentSchedulesV.STATUS,
ArPaymentSchedulesV.TERM_ID,
ArPaymentSchedulesV.AMOUNT_DUE_ORIGINAL,
ArPaymentSchedulesV.AMOUNT_DUE_REMAINING,
ArPaymentSchedulesV.PAYMENT_SCHEDULE_ID,
ArPaymentSchedulesV.TERMS_SEQUENCE_NUMBER,
ArPaymentSchedulesV.DUE_DATE,
ArPaymentSchedulesV.CLASS AS CLASS2,
ARI_UTILITIES.get_lookup_meaning('PAYMENT_SCHEDULE_STATUS', ArPaymentSchedulesV.STATUS) as AR_LOOKUP_CODE_STATUS_MEANING,
to_char(ArPaymentSchedulesV.AMOUNT_IN_DISPUTE) AMOUNT_IN_DISPUTE,
to_char(ArPaymentSchedulesV.AMOUNT_ADJUSTED_PENDING) AMOUNT_ADJUSTED_PENDING,

(select activity_number from ar_cm_activities_v
where payment_schedule_id = ArPaymentSchedulesV.payment_schedule_id
and rownum = 1) as APPLIED_TO_TRX_NUMBER,
-- number of applied_to_trx_number
(SELECT count(*) FROM
(SELECT APP.CUSTOMER_TRX_ID
FROM ar_payment_schedules ps, AR_RECEIVABLE_APPLICATIONS APP
WHERE APP.STATUS = 'APP' AND APP.APPLICATION_TYPE = 'CM'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY='Y'
UNION ALL
SELECT ADJ.CUSTOMER_TRX_ID
FROM AR_ADJUSTMENTS ADJ, AR_PAYMENT_SCHEDULES PS
WHERE ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
UNION ALL
SELECT APP.APPLIED_CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES PS, AR_RECEIVABLE_APPLICATIONS APP
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.STATUS = 'APP'
AND APP.DISPLAY='Y')
WHERE customer_trx_id = ArPaymentSchedulesV.CUSTOMER_TRX_ID) as count_applied_to_trx,
(SELECT activity_ps_id FROM (SELECT activity_ps_id, customer_trx_id FROM ar_cm_activities_v where rownum = 1
ORDER BY activity_date DESC) WHERE ArPaymentSchedulesV.customer_trx_id = customer_trx_id ) as last_activity_ps_id,
ArPaymentSchedulesV.PAYMENT_APPROVAL,
ArPaymentSchedulesV.freight_original as FREIGHT_AMOUNT,
ArPaymentSchedulesV.tax_original as TAX_AMOUNT,
al.meaning APPROVAL_STATUS,
ArPaymentSchedulesV.org_id
FROM AR_PAYMENT_SCHEDULES ArPaymentSchedulesV, ar_lookups al
WHERE ArPaymentSchedulesV.CLASS = 'CM'
AND al.lookup_type='ARI_PMT_APPROVAL_STATUS' and al.lookup_code=nvl(ArPaymentSchedulesV.PAYMENT_APPROVAL,'PENDING')
AND ( trunc( ArPaymentSchedulesV.TRX_DATE ) >= nvl2( :3 , trunc ( add_months(SYSDATE, :4 ) ) , trunc ( ArPaymentSchedulesV.TRX_DATE )))) temp, ra_customer_trx ct, hz_cust_accounts acct,ar_irec_user_acct_sites_all auasa,
AR_PAYMENT_SCHEDULES applSched, ar_cm_activities_v appl,
hz_cust_site_uses billto_uses,
hz_cust_site_uses shipto_uses
WHERE temp.customer_trx_id = ct.customer_trx_id
AND billto_uses.site_use_id = ct.bill_to_site_use_id
AND shipto_uses.site_use_id (+)=ct.ship_to_site_use_id
AND shipto_uses.site_use_code(+)='SHIP_TO'
AND acct.cust_account_id = temp.customer_id
AND appl.payment_schedule_id = applsched.payment_schedule_id(+)
AND temp.last_activity_ps_id = appl.activity_ps_id(+)
AND temp.customer_id = auasa.customer_id
AND temp.CUSTOMER_SITE_USE_ID = auasa.CUSTOMER_SITE_USE_ID
AND auasa.USER_ID = FND_GLOBAL.USER_ID
AND(TRUNC(temp.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, temp.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option)
AND auasa.session_id = :5) QRSLT WHERE (( customer_id = :6 ) AND ( invoice_currency_code = :7 ) AND ( status = nvl ( :8 , status )))

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
 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.