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.

Symptoms

ACTUAL BEHAVIOR
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.

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

EXPECTED BEHAVIOR
Expect to see ending balance 0 instead of 11.23.

Responsibility:  Receivables Manager
Navigation:   Transactions > Transactions
Steps:
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'';
commit;

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.


BUSINESS IMPACT
-----------------------
Generate Balance Forward Bill Program produces wrong ending balance

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