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

Last updated on DECEMBER 01, 2016

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

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