payment distrubution oracle sql query

Question

I am looking for a query where $1100 gets distributed to each invoice below based on the item_order. Also, if partial_payment_allowed is set to 'N' then distribution of the above amount should only happen if the distributed amount is greater than the invoice_amt else it should skip the row and carry on to next invoice.

Item_order inv_amount   Partial_pmt_allowed
1          1256         N
2          1134         N
3           800         N
4           200         Y
5           156         Y

So, the final result of the query if we pass $1100 would be

Item_order  inv_amount  Partial_pmt_allowed  Dist_amount Balance_amt
1          1256                    N               0          1100
2          1134                    N               0          1100 
3          800                     N              800          300
4          200                     Y              200          100   
5          156                     Y              100          0

We are trying to avoid loops, any comments are highly appreciated.Thank you

Answer 1

I use a bind variable :pmt to input the payment received. Here is the prep needed in SQL*Plus, other front-end apps like SQL Developer and Toad etc. have their own mechanisms. I also prepared for a wide column.

Preparation (SQL*Plus):

SQL> variable pmt number
SQL> exec :pmt := 1100;

PL/SQL procedure successfully completed.
SQL> column partial_pmt_allowed format a20

The solution is a recursive query, so it requires Oracle 11.1 or above. I wrote it in a form that requires Oracle 11.2 or above (I declare column aliases when I declare factored subqueries), but that can be changed very easily to work in 11.1 if needed.

Query:

with
--  begin test data (not part of the solution)
     test_data ( item_order, inv_amt, partial_pmt_allowed ) as (
       select 1, 1256, 'N' from dual union all
       select 2, 1134, 'N' from dual union all
       select 3,  800, 'N' from dual union all
       select 4,  200, 'Y' from dual union all
       select 5,  156, 'Y' from dual union all
       select 6,   30, 'N' from dual
     ),
--  end of test data; the solution (SQL query) includes just 
--      the keyword "with" from above and continues below this line.
     r ( item_order, inv_amt, partial_pmt_allowed, dist_amt, balance ) as (
       select 0, 0, '', 0, :pmt
         from dual
       union all
       select t.item_order, t.inv_amt, t.partial_pmt_allowed,
              case when r.balance >= t.inv_amt then t.inv_amt
                   when t.partial_pmt_allowed = 'Y' then r.balance
                   else 0 end,
              case when r.balance >= t.inv_amt then r.balance - t.inv_amt
                   when t.partial_pmt_allowed = 'Y' then 0
                   else r.balance end
       from   test_data t join r on t.item_order = 1 + r.item_order
     )
select   *
from     r
where    item_order != 0
order by item_order
;

Results (with the input data defined in the query and the "payment received" value of 1100 passed in as the bind variable :pmt):

ITEM_ORDER    INV_AMT PARTIAL_PMT_ALLOWED    DIST_AMT    BALANCE
---------- ---------- -------------------- ---------- ----------
         1       1256 N                             0       1100
         2       1134 N                             0       1100
         3        800 N                           800        300
         4        200 Y                           200        100
         5        156 Y                           100          0
         6         30 N                             0          0

6 rows selected.

Answer 2

This is a good use case for the SQL MODEL clause.

-- Set up test data (since I don't have your table)
with inv_raw (item_order, inv_amount, partial_pmt_allowed) as ( 
SELECT 1, 1256, 'N' FROM DUAL UNION ALL
SELECT 2, 1134, 'N' FROM DUAL UNION ALL
SELECT 3, 800, 'N' FROM DUAL UNION ALL
SELECT 4, 200, 'Y' FROM DUAL UNION ALL
SELECT 5, 156, 'Y' FROM DUAL),
-- Ensure that the column we are ordering by is densely populated
inv_dense (dense_item_order, item_order, inv_amount, partial_pmt_allowed) as 
( SELECT dense_rank() OVER ( PARTITION BY NULL ORDER BY item_order ), item_order, inv_amount, partial_pmt_allowed FROM inv_raw ),
-- Give us a way to input the payment amount
param as ( SELECT 1100 p_payment_amount FROM DUAL )
-- The actual query starts here
SELECT item_order,
 inv_amount,
 partial_pmt_allowed,
--remaining_in,
applied dist_amount,
remaining_out balance_amt
 FROM param, inv_dense
MODEL 
DIMENSION BY ( dense_item_order )
MEASURES ( p_payment_amount, item_order, inv_amount, partial_pmt_allowed, 0 applied, 0 remaining_in, 0 remaining_out )
RULES AUTOMATIC ORDER (
-- The amount carried into the first row is the payment amount
remaining_in[1] = p_payment_amount[1],
-- The amount carried into subsequent rows is the amount we carried out of the prior row
remaining_in[dense_item_order > 1] = remaining_out[CV()-1],
-- The amount applied depends on whether the amount remaining can cover the invoice 
-- and whether partial payments are allowed
applied[ANY] = CASE WHEN remaining_in[CV()] >= inv_amount[CV()] OR partial_pmt_allowed[CV()] = 'Y' THEN LEAST(inv_amount[CV()], remaining_in[CV()]) ELSE 0 END,
-- The amount we carry out is the amount we brought in minus what we applied
remaining_out[ANY] = remaining_in[CV()] - applied[CV()] 
)
ORDER BY item_order;

RESULT

ITEM_ORDER |INV_AMOUNT |PARTIAL_PMT_ALLOWED |DIST_AMOUNT |BALANCE_AMT |
-----------|-----------|--------------------|------------|------------|
1          |1256       |N                   |0           |1100        |
2          |1134       |N                   |0           |1100        |
3          |800        |N                   |800         |300         |
4          |200        |Y                   |200         |100         |
5          |156        |Y                   |100         |0           |