aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
fn_itemizer
Parameters
Name
Type
Mode
balance
t_money
IN
itemizer_codes
text[]
IN
Definition
DECLARE item_amount t_money; i int; item record; BEGIN --balance = base_amount; --FOR i IN reverse fn_length(itemizer_codes)..1 LOOP FOR item IN ( SELECT i.code, i.rate, i.amount FROM unnest(itemizer_codes) WITH ORDINALITY a(code, num) LEFT JOIN itemizer i ON i.code = a.code ORDER BY num desc ) LOOP --SELECT rate, amount FROM itemizer WHERE code = itemizer_codes[i] INTO _rate, _amount; IF item.rate IS NOT NULL THEN item_amount = (balance-fn_itemizer(balance, itemizer_codes[:fn_length(itemizer_codes)-1])) * item.rate / (100 + item.rate); ELSE item_amount = item.amount; END IF; balance = balance - item_amount; RAISE NOTICE '(%,%,%): %, %', item.code, item.rate, item.amount,item_amount, balance; END LOOP; RETURN balance; -- SELECT i.code, i.rate, iif(i.rate IS NULL, i.amount, (total_amount * i.rate / (100.00 + i.rate)))::t_money AS amount -- FROM itemizer i -- WHERE code = ANY(itemizer_codes) END;