diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Voucher.sql | 29 |
1 files changed, 22 insertions, 7 deletions
diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index 6ed8fa7d..d626595f 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -193,7 +193,7 @@ RETURNS BOOL AS $$ DECLARE control_amount NUMERIC; - voucher vouchers%ROWTYPE; + voucher voucher%ROWTYPE; incriment NUMERIC; BEGIN -- CHECK CONTROL NUMBERS @@ -204,7 +204,7 @@ BEGIN SELECT id FROM gl WHERE coalesce(approved, false) != true) AND trans_id IN ( - SELECT transaction_id FROM vouchers + SELECT transaction_id FROM voucher WHERE batch_id = ANY (in_batch_id)) AND coalesce(approved, false) != true AND amount > 0 @@ -214,7 +214,7 @@ BEGIN SELECT sum(ac.amount) INTO control_amount FROM acc_trans ac - JOIN vouchers v ON (v.transaction_id = ac.trans_id) + JOIN voucher v ON (v.transaction_id = ac.trans_id) WHERE v.batch_id = ANY (in_batch_id) AND ac.vr_id = v.id AND coalesce(approved, false) = false @@ -224,7 +224,7 @@ BEGIN SELECT sum(amount) INTO control_amount FROM acc_trans WHERE trans_id IN - (SELECT transaction_id FROM vouchers + (SELECT transaction_id FROM voucher WHERE batch_id = ANY (in_batch_id)) AND trans_id IN (SELECT trans_id FROM ap @@ -245,20 +245,20 @@ BEGIN UPDATE acc_trans SET approved = true WHERE trans_id IN - (SELECT transaction_id FROM vouchers + (SELECT transaction_id FROM voucher WHERE batch_id = ANY (in_batch_id)); IF in_batch = 'gl' THEN UPDATE gl SET approved = true WHERE trans_id IN - (SELECT transaction_id FROM vouchers + (SELECT transaction_id FROM voucher WHERE batch_id = ANY (in_batch_id)); ELSE UPDATE ap SET approved = true WHERE trans_id IN - (SELECT transaction_id FROM vouchers + (SELECT transaction_id FROM voucher WHERE batch_id = ANY (in_batch_id)); END IF; END IF; @@ -266,3 +266,18 @@ BEGIN RETURN TRUE; END; $$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION batch_create( +in_batch_number text, in_description text, in_batch_class text) RETURNS int AS +$$ +BEGIN + INSERT INTO + batch (batch_class_id, description, control_code, created_by) + VALUES ((SELECT id FROM batch_class WHERE class = in_batch_class), + in_description, in_batch_number, + (select id FROM users WHERE username = session_user)); + + return currval('batch_id_seq'); +END; +$$ LANGUAGE PLPGSQL; |