Receipts Workbench Performance Issue: ARXRWMAI - Apply To List of Values Hangs After Patch 8640456 (Doc ID 1273339.1)

Last updated on MARCH 09, 2016

Applies to:

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

Symptoms

After applying patch 8640456, the list of values for the Apply To field in the receipts workbench is hanging.

Following is the query causing the problem:

select /*+ leading(cust ot.PS) push_pred(ot.T ) push_pred(ot.CI)
 push_pred(ot.SU) push_pred(ot.CST) push_pred(ot.CT) push_pred(ot.CST_PARTY)
 push_pred(ot.CTT) push_pred(ot.BS) INDEX(ot.PS AR_PAYMENT_SCHEDULES_N6) */
 ot.trx_number , ot.customer_id , ot.customer_number , ot.class ,
 SUBSTRB(ot.class_meaning , 1, 30) class_meaning, ot.trx_type ,
 ot.cust_trx_type_id, ot.trx_due_date trx_due_date,
 SUBSTRB(decode(sign(ot.payment_schedule_id), -1, :1,
 ot.invoice_currency_code), 1, 15) invoice_currency_code ,
 ot.balance_due_curr , decode(sign(ot.payment_schedule_id), -1,
 to_number(null), ot.balance_due_functional) balance_due_functional,
 ot.customer_name, ot.location, decode(sign(ot.payment_schedule_id), -1,
 to_number(null), ot.trx_exchange_rate) trx_exchange_rate, ot.trx_date
 trx_date , decode(sign(ot.payment_schedule_id), -1, to_date(null),
 ot.trx_gl_date) trx_gl_date, ot.calc_discount_on_lines_flag ,
 ot.partial_discount_flag , ot.allow_overapplication_flag ,
 ot.natural_application_only_flag , ot.creation_sign ,
 ot.payment_schedule_id , ot.terms_sequence_number , ot.customer_trx_id ,
 greatest(to_date(:2), ot.trx_gl_date,
 decode(fnd_profile.value('AR_APPLICATION_GL_DATE_DEFAULT'),
 'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date, ot.trx_gl_date))
 gl_date,decode(ot.default_reference, '1',ot.interface_header_attribute1 ,
 '2', ot.interface_header_attribute2 , '3', ot.interface_header_attribute3 ,
 '4', ot.interface_header_attribute4 , '5', ot.interface_header_attribute5 ,
 '6', ot.interface_header_attribute6 , '7', ot.interface_header_attribute7 ,
 '8', ot.interface_header_attribute8 , '9', ot.interface_header_attribute9 ,
 '10', ot.interface_header_attribute10 , '11',
 ot.interface_header_attribute11 , '12', ot.interface_header_attribute12 ,
 '13', ot.interface_header_attribute13 , '14',
 ot.interface_header_attribute14 , '15', ot.interface_header_attribute15,
 null) reference , ot.term_id, decode(sign(ot.payment_schedule_id), -1,
 to_number(null), ot.amount_due_original) amount_due_original,
 ot.amount_line_items_original , ot.balance_due_curr_unformatted ,
 ot.discount_taken_earned, ot.discount_taken_unearned ,
 ot.trx_batch_source_name trx_batch_source_name , ot.amount_adjusted
 amount_adjusted , ot.amount_adjusted_pending amount_adjusted_pending ,
 ot.amount_line_items_remaining amount_line_items_remaining ,
 ot.freight_original freight_original , ot.freight_remaining
 freight_remaining , ot.receivables_charges_remaining
 receivables_charges_remaining , ot.tax_original tax_original ,
 ot.tax_remaining tax_remaining , ot.selected_for_receipt_batch_id
 selected_for_receipt_batch_id, ot.trx_billing_number trx_billing_number,
 ot.purchase_order purchase_order, ot.bill_to_site_use_id
 bill_to_site_use_id, substrb(ot.open_receipt_status_meaning,1,30)
 open_receipt_status_meaning, ot.customer_reference, substrb(ot.reason,1,80)
 reason, ot.receivable_application_id, ot.account_description,
 ot.customer_reason
from
ar_open_trx_v ot, ( SELECT /*+ merge */ rcr.related_cust_account_id FROM
 hz_cust_acct_relate rcr WHERE rcr.status='A' AND rcr.bill_to_flag = 'Y' AND
 rcr.cust_account_id = TO_NUMBER(DECODE(:3, -1, :4, null, :5, :6)) UNION ALL
 SELECT TO_NUMBER(DECODE(:7, -1, :8, null, :9, :10)) related_cust_account_id
 FROM dual UNION ALL SELECT rel.related_cust_account_id FROM
 ar_paying_relationships_v rel, hz_cust_accounts acc WHERE rel.party_id =
 acc.party_id AND acc.cust_account_id = TO_NUMBER(DECODE(:11, -1, :12, null,
 :13, :14)) AND :15 BETWEEN effective_start_date AND effective_end_date
 union all select -1 related_cust_account_id from dual union all select -3
 related_cust_account_id from dual union all select -4
 related_cust_account_id from dual ) cust where ot.status=decode(:16, 'TRUE',
 ot.status,'OP') and ot.customer_id =cust.related_cust_account_id order by
 decode(SIGN(ot.payment_schedule_id), -1,0, 4) , ot.trx_number ,
 ot.customer_name , ot.terms_sequence_number , ot.payment_schedule_id

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