My Oracle Support Banner

Wrong BFB Ending Balance When Customer Has Different Locations (Doc ID 2121860.1)

Last updated on AUGUST 25, 2017

Applies to:

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


Generate Balance Forward Bill Program produces wrong ending balance in the following scenario:

Customer Location A has 1 new Invoice for $11.23, which is closed by the Location B’s receipt, therefore their ending balance is 0.
Customer Location B does not have any new activity, creates a check and applies it to location A, so their ending balance should be 0
and the BFB reflects 11.23, which is the problem.
Customer account has 2 locations, 1 of which has 11.23 in invoices, another -11.23 in receipts, so their ending balance is 0.

It is only a problem when the invoice record’s payment schedule id equals a value on the
ar_cons_inv_trx_all table.

Expect to see ending balance 0 instead of 11.23.

Responsibility:  Receivables Manager
Navigation:   Transactions > Transactions
The issue can be reproduced at will with the following steps:
1. Find a "adj_ps_id" value greater than the current maximum payment_schedule_id:

select adj_ps_id
from ar_cons_inv_trx_all
where customer_trx_id is null
AND adj_ps_id > (select max (payment_schedule_id)
from ar_payment_schedules_all);

Most of the time, these values are for cross site receipt application, which occur often.

2. Create a new Invoice  for a Customer Location belonging to a customer with multiple locations.

3. Obtain the payment_schedule_id for the transaction created:

select payment_schedule_id
from ar_payment_schedules_all
where creation_date = (select max (creation_date)
from ar_payment_schedules_all);

4. Update the record on the payment schedule table to have a payment schedule id equal to the adj_ps_id value obtained in step 1:

update ar_payment_schedules_all
set payment_schedule_id=''adj_ps_id value obtained in step 1'';

This step is NEVER executed in a production environment. It is only done to force a scenario that can only occur per coincidence.

5. Create a new Receipt for customer location for the same customer used in step 2, make sure it is a different Location however.

6. Apply part or the entire receipt to the transaction created in step 2.

7. Print a new BFB by submitting a Generate Balance Forward Bill request, using only the location from step 5.

8. Confirm that the cross site receipt application appears on the ar_cons_inv_trx_all table
as both a XSITE RECREV  and a RECEIPT ADJUST type records.

Confirm that the ending balance on the ar_cons_inv_all for the BFB is higher than the location’s balance,
the difference being the amount of the cross site receipt application.

Generate Balance Forward Bill Program produces wrong ending balance


To view full details, 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 a vibrant support community of peers and Oracle experts.