Payment Distrubution Oracle Sql Query

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 a

Solution 1:

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 ( 
SELECT1, 1256, 'N'FROM DUAL UNIONALLSELECT2, 1134, 'N'FROM DUAL UNIONALLSELECT3, 800, 'N'FROM DUAL UNIONALLSELECT4, 200, 'Y'FROM DUAL UNIONALLSELECT5, 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 
( SELECTdense_rank() OVER ( PARTITIONBYNULLORDERBY item_order ), item_order, inv_amount, partial_pmt_allowed FROM inv_raw ),
-- Give us a way to input the payment amount
param as ( SELECT1100 p_payment_amount FROM DUAL )
-- The actual query starts hereSELECT 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] =CASEWHEN remaining_in[CV()] >= inv_amount[CV()] OR partial_pmt_allowed[CV()] ='Y'THEN LEAST(inv_amount[CV()], remaining_in[CV()]) ELSE0END,
-- The amount we carry out is the amount we brought in minus what we applied
remaining_out[ANY] = remaining_in[CV()] - applied[CV()] 
)
ORDERBY 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           |

Solution 2:

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/SQLprocedure 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 (
       select1, 1256, 'N'from dual unionallselect2, 1134, 'N'from dual unionallselect3,  800, 'N'from dual unionallselect4,  200, 'Y'from dual unionallselect5,  156, 'Y'from dual unionallselect6,   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 (
       select0, 0, '', 0, :pmt
         from dual
       unionallselect t.item_order, t.inv_amt, t.partial_pmt_allowed,
              casewhen r.balance >= t.inv_amt then t.inv_amt
                   when t.partial_pmt_allowed ='Y'then r.balance
                   else0end,
              casewhen r.balance >= t.inv_amt then r.balance - t.inv_amt
                   when t.partial_pmt_allowed ='Y'then0else r.balance endfrom   test_data t join r on t.item_order =1+ r.item_order
     )
select*from     r
where    item_order !=0orderby 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_ORDERINV_AMTPARTIAL_PMT_ALLOWEDDIST_AMTBALANCE------------------------------------------------------------11256 N0110021134 N011003800N8003004200Y2001005156Y1000630N006rowsselected.