1 create or replace function xt.cust_outstanding_credit(cust_id integer, applicable_currency integer, applicable_date date) returns numeric stable as $$
2 -- sum of all unallocated credits for a customer
3 -- TODO: subtract cash receipts pending
5 -- second convert the currency of the receivable to the currency of the invoice
6 SELECT COALESCE(SUM(currToCurr(aropen_curr_id, $2,
7 unallocated, $3)),0) AS amount
9 -- select distinct on allows us to add the aggregated aropenalloc_amount column
10 -- without having to inner join a temporarily grouped table
11 select distinct on (aropen_id) aropen_id, aropen_cust_id, aropen_curr_id,
12 coalesce(aropen_amount, 0) -
13 -- first convert the currency of the allocation to the currency of the receivable
14 COALESCE(SUM(currToCurr(aropenalloc_curr_id, aropen_curr_id,
15 aropenalloc_amount, aropen_duedate)),0) AS unallocated
17 left join aropenalloc on aropen_id = aropenalloc_aropen_id
18 where aropen_cust_id = $1
20 --and aropen_posted = false
21 group by aropen_id, aropen_cust_id, aropen_curr_id, aropen_amount