4 Replies Latest reply on May 20, 2016 1:30 PM by cesar.advincula.o

# Balance Amount calculation based on invoice and receipt (Billing and Receipt)

Hi

I have a requirement in one of the financials report. The customer has a report with details of Invoice like invoice number, amount, receipt transaction, receipt amount etc.

They need to calculate the Balance amount when each receipt paid against the invoice.

For example if the Invoice amount equals 80 and the same invoice has 2 receipts xxx = 75 and xx = 5. When the Balance is calculated for the first receipt the balance amount will be 5 and when the second payment is made the balance amount should be 0. Like shown in the screenshot below. I tried implementing some formulas but it doesnt give me the desired answer. For the second receipt my calculation goes as (80-5=75, where the balance should be 0).

Any suggestions or help would be appreciated.

Regards

Vyshak

• ###### 1. Re: Balance Amount calculation based on invoice and receipt (Billing and Receipt)

Hello,

There is some clients when I implement this and i pass with the same challenge, you could do it use a:

Remaing =   "invoice amount - sum( Receipt Amount ) group by invoice document)"

However it not the best solution, this kind of information has to be calculated in the ETL process.

Kind Regards,

• ###### 2. Re: Balance Amount calculation based on invoice and receipt (Billing and Receipt)

+1 to Cesar ... Balances are inventory-style facts and need to be stored in the appropriate fact structure.  Once you have it right, then you can show increase/decrease in balance over time AND also add value by having an aging balance fact built from the base balance fact.

• ###### 3. Re: Balance Amount calculation based on invoice and receipt (Billing and Receipt)

Hi Cesar

Thanks for your response. Unfortunately our clients approach is to write view out of tables from the trabsactional data and pull the view in BI to create reports. Now I do not have any option in implementing this in the ETL. Do you think I can write this in the view ? Or from the reporting end ?

Regards

Vyshak

• ###### 4. Re: Balance Amount calculation based on invoice and receipt (Billing and Receipt)

Hello,

OK, if you dont have any other solutions, you should use analytics function LAG/LEAD to calculate your report.

Oracle BI EE 11g - Calling Database Analytic Functions - EVALUATE - Rittman Mead Consulting

Kind Regards,