I've a table with different products with a range minimum and maximum and I've a discount rate for each product within the ranges.
Basically we make the invoice and based on the amount of the invoice we calculate the discount based on the scale.
My table (with data for one product:)
| PROD_ID | MIN | MAX | RATE |
|---|
| 1 | 2500 | 4999 | 1 | |
| 1 | 5000 | 24999 | 2 | |
| 1 | 25000 | 1000000 | 3 | |
| | | | |
I already have the calculation for calculating the discount based on the given invoice amount.
if amount is less than 2500 then no discount
above 2500 we check if it is between the first range than 1% dc. the amount the remains after the 4999 gets 2% dc uptil 24999. And if there is still an amount after the 24999 the dc is 3% up till 1000000.
This is easily done within a PL/SQL function.
My issue is with the reverse calculation. Given the net amount f.i. 52000, I've to calculate the gross amount. I started working in the same way from gross to net, but offcourse this is not the right way to begin. Could someone assist me on this isse for how to start this in PL/SQL ?
Thanks in advance/