aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_recalc_folio_balance
Parameters
Name
Type
Mode
folio_item
integer
IN
Definition
WITH SUMM as ( SELECT sum(t.acc_amount) as balance, sum(iif(t.post_type = 'P' and d.vat_effect, t.acc_amount, 0)) as vat_effect_amt, sum(iif(t.post_type = 'S' and t.vat_issued = 'Y', -t.acc_amount, 0)) as vat_issued_amt, sum(iif(t.post_type = 'P' and not d.vat_effect, t.acc_amount, 0)) as vat_nonvat_amt FROM transactions t LEFT JOIN department d on d.id = t.dept_id WHERE t.folio_item = $1 AND t.active ) UPDATE folio_items fi SET balance = s.balance, vat_effect_amt = s.vat_effect_amt, vat_nonvat_amt = s.vat_nonvat_amt, vat_issued_amt = s.vat_issued_amt FROM SUMM S WHERE fi.item_id = $1 RETURNING fi.balance;