Bills Receivable Quick Assign Preview Very Slow (Doc ID 2010771.1)

Last updated on NOVEMBER 01, 2016

Applies to:

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

Symptoms

Bills Receivable - Quick Assign - Preview is very slow and takes minutes on our test server and also extremely long on our production server.

Long time spent on query
 
PARSING IN CURSOR #140456558650864 len=6369 dep=1 uid=173 oct=3 lid=173 tim=1416227829957692 hv=3841341076 ad='8c58cc08' sqlid='4d6vgazkgcdnn'
Select
TRX.trx_number,
TRX.doc_sequence_value,
TRX.trx_date,
TRX.comments,
TRX.purchase_order,
TRX.invoice_currency_code,
......................
(select 1
  from ra_customer_trx_lines linesub,
  ar_payment_schedules paysub ,
  ra_customer_trx billsub
 where linesub.br_ref_customer_trx_id = TRX.customer_trx_id
  and linesub.customer_trx_id = billsub.customer_trx_id
  and billsub.customer_trx_id = paysub.customer_trx_id
  and (paysub.reserved_type is not null OR billsub.br_on_hold_flag = 'Y'))
and trx.invoice_currency_code = 'EUR' and trx.trx_date SELECT 'X'
FROM hz_cust_acct_relate rr
WHERE rr.cust_account_id = 125304 and rr.related_cust_account_id = trx.bill_to_customer_id
  and rr.bill_to_flag = 'Y' UNION ALL SELECT 'X'
  FROM ar_paying_relationships_v rel,
  hz_cust_accounts acc
  WHERE rel.party_id = acc.party_id
  AND rel.related_cust_account_id = trx.bill_to_customer_id
  AND acc.cust_account_id = 125304 AND to_date('30-SEP-14') BETWEEN effective_start_date
  AND effective_end_date)) AND TYPES.type in ( 'INV','CM','DM') ORDER BY 3 /* TRX.TRX_DATE */ ASC

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 2.19 2.19 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22 752.21 751.67 34991 139576 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 754.41 753.86 34991 139576 0 21

The AR System Option: Allow Payment of Unrelated Transactions = No

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