Lockbox Performance Issue: Lockbox Process has Poor Performance When Updating AR_PAYMENTS_INTERFACE (Doc ID 1439993.1)

Last updated on AUGUST 11, 2016

Applies to:

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

Symptoms

Attempting to run the Lockbox program, and the process 'ARLPLB module: Process Lockboxes' is having performance issues and runs for more than 10 hrs to process 9000 receipts

Tkprof shows the following top queries:

update ar_payments_interface pi set status='AR_PLB_INV_OK'
where
(((status='AR_PLB_RECEIPT_OK' and transmission_id=:b0) and (customer_id is
not null or special_type='UNIDENTIFIED')) and not exists (select 'invalid
invoices' from ar_payments_interface where (((transmission_id=:b0 and
item_number=pi.item_number) and ((batch_name=pi.batch_name or
(lockbox_number=pi.lockbox_number and :b2='N')) or :b3='Y')) and
(((((((invoice1_status is not null or invoice2_status is not null ) or
invoice3_status is not null ) or invoice4_status is not null ) or
invoice5_status is not null ) or invoice6_status is not null ) or
invoice7_status is not null ) or invoice8_status is not null ))))

 

update ar_payments_interface pi set status=(select decode(sign((decode(:b0,
'Y',round((pi.remittance_amount/power(10,fc.precision)),fc.precision),
nvl(pi.remittance_amount,0))
-sum((((((((decode(subpi.resolved_matching_number1,null ,0,
decode(subpi.amount_applied_from1,null ,decode(:b1,'Y',
round((nvl(subpi.amount_applied1,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied1,0)),decode(:b2,'Y',
round((nvl(subpi.amount_applied_from1,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from1,0))))
+decode(subpi.resolved_matching_number2,null ,0,
decode(subpi.amount_applied_from2,null ,decode(:b3,'Y',
round((nvl(subpi.amount_applied2,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied2,0)),decode(:b4,'Y',
round((nvl(subpi.amount_applied_from2,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from2,0)))))
+decode(subpi.resolved_matching_number3,null ,0,
decode(subpi.amount_applied_from3,null ,decode(:b5,'Y',
round((nvl(subpi.amount_applied3,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied3,0)),decode(:b6,'Y',
round((nvl(subpi.amount_applied_from3,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from3,0)))))
+decode(subpi.resolved_matching_number4,null ,0,
decode(subpi.amount_applied_from4,null ,decode(:b7,'Y',
round((nvl(subpi.amount_applied4,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied4,0)),decode(:b8,'Y',
round((nvl(subpi.amount_applied_from4,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from4,0)))))
+decode(subpi.resolved_matching_number5,null ,0,
decode(subpi.amount_applied_from5,null ,decode(:b9,'Y',
round((nvl(subpi.amount_applied5,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied5,0)),decode(:b10,'Y',
round((nvl(subpi.amount_applied_from5,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from5,0)))))
+decode(subpi.resolved_matching_number6,null ,0,
decode(subpi.amount_applied_from6,null ,decode(:b11,'Y',
round((nvl(subpi.amount_applied6,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied6,0)),decode(:b12,'Y',
round((nvl(subpi.amount_applied_from6,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from6,0)))))
+decode(subpi.resolved_matching_number7,null ,0,
decode(subpi.amount_applied_from7,null ,decode(:b13,'Y',
round((nvl(subpi.amount_applied7,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied7,0)),decode(:b14,'Y',
round((nvl(subpi.amount_applied_from7,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from7,0)))))
+decode(subpi.resolved_matching_number8,null ,0,
decode(subpi.amount_applied_from8,null ,decode(:b15,'Y',
round((nvl(subpi.amount_applied8,0)/power(10,fc.precision)),fc.precision),
nvl(subpi.amount_applied8,0)),decode(:b16,'Y',
round((nvl(subpi.amount_applied_from8,0)/power(10,fc.precision)),
fc.precision),nvl(subpi.amount_applied_from8,0)))))))),(-1),
'AR_PLB_REMIT_EXCEEDED',pi.status) from ar_payments_interface subpi ,
fnd_currencies fc
where
((((subpi.currency_code=fc.currency_code and subpi.transmission_id=
pi.transmission_id) and (subpi.record_type||'') in (:b17,:b18)) and
subpi.item_number=pi.item_number) and ((subpi.batch_name=pi.batch_name or
(subpi.lockbox_number=pi.lockbox_number and :b19='N')) or :b20='Y')) group
by fc.precision) where (((transmission_id=:b21 and record_type=:b17) and
customer_id is not null ) and status='AR_PLB_INV_OK')

 

NOTE: There might be more updates to ar_payments_interface table showing high elapsed times in the tkprof

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