diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/modules/Payment.sql | 54 | ||||
| -rw-r--r-- | sql/modules/Voucher.sql | 32 |
2 files changed, 52 insertions, 34 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 48c3b7f3..0dabd003 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -113,7 +113,8 @@ CREATE TYPE payment_contact_invoice AS ( contact_name text, account_number text, total_due numeric, - invoices text[] + invoices text[], + has_vouchers int ); CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices @@ -142,25 +143,30 @@ BEGIN THEN 0 ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100 END))::text]]), + sum(case when v.batch_id = in_batch_id then 1 + else 0 END), bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = ( select id from users WHERE username = SESSION_USER)))) + FROM entity e JOIN entity_credit_account c ON (e.id = c.entity_id) JOIN (SELECT id, invnumber, transdate, amount, entity_id, paid, curr, 1 as invoice_class, entity_credit_account, on_hold FROM ap + WHERE in_account_class = 1 UNION SELECT id, invnumber, transdate, amount, entity_id, paid, curr, 2 as invoice_class, entity_credit_account, on_hold FROM ar + WHERE in_account_class = 2 ORDER BY transdate ) a USING (entity_id) JOIN transactions t ON (a.id = t.id) - WHERE a.id IN (select voucher.trans_id FROM voucher - WHERE batch_id = in_batch_id) + LEFT JOIN voucher v ON (v.trans_id = a.id) + WHERE v.batch_id = in_batch_id OR (a.invoice_class = in_account_class AND c.business_id = coalesce(in_business_id, c.business_id) @@ -184,6 +190,8 @@ BEGIN )) GROUP BY c.id, e.name, c.meta_number, c.threshold HAVING sum(a.amount - a.paid) > c.threshold + OR sum(case when v.batch_id = in_batch_id then 1 + else 0 END) > 0 LOOP RETURN NEXT payment_item; END LOOP; @@ -334,7 +342,7 @@ BEGIN LOOP INSERT INTO acc_trans (trans_id, chart_id, amount, approved, voucher_id, - transdate) + transdate, source) VALUES (in_transactions[out_count][1], case when in_account_class = 1 THEN t_cash_id @@ -345,7 +353,7 @@ BEGIN CASE WHEN t_voucher_id IS NULL THEN true ELSE false END, - t_voucher_id, in_payment_date), + t_voucher_id, in_payment_date, in_source), (in_transactions[out_count][1], case when in_account_class = 1 THEN t_ar_ap_id @@ -356,7 +364,7 @@ BEGIN CASE WHEN t_voucher_id IS NULL THEN true ELSE false END, - t_voucher_id, in_payment_date); + t_voucher_id, in_payment_date, in_source); UPDATE ap set paid = paid +in_transactions[out_count][2] where id =in_transactions[out_count][1]; @@ -565,28 +573,18 @@ BEGIN max(cc.description), max(ac.id), max(ac.accno), max(ac.description) FROM acc_trans at - JOIN entity_credit_account ec ON - (at.trans_id IN - (select id FROM ar - WHERE in_account_class = 2 - AND entity_credit_account = - (SELECT id - FROM entity_credit_account - WHERE meta_number - = in_meta_number - AND entity_class = - in_account_class) - UNION - SELECT id FROM ap - WHERE in_account_class = 1 AND - entity_credit_account = - (select id - FROM entity_credit_account - WHERE meta_number - = in_meta_number - AND entity_class = - in_account_class))) - + JOIN (select id, entity_credit_account + FROM ar + WHERE in_account_class = 2 + UNION + SELECT id, entity_credit_account + FROM ap + WHERE in_account_class = 1) arap + ON (arap.id = at.trans_id) + + JOIN entity_credit_account ec ON ( + ec.entity_class = in_account_class + AND arap.entity_credit_account = ec.id) JOIN company c ON (ec.entity_id = c.entity_id) LEFT JOIN chart cc ON (at.chart_id = cc.id AND cc.link LIKE '%paid%') diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index 7abc65e7..b5dc76b1 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -71,25 +71,45 @@ BEGIN WHERE class = 'ar') UNION -- TODO: Add the class labels to the class table. - SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id, + SELECT v.id, a.source, + cr.meta_number || '--' || co.legal_name , + v.batch_id, v.trans_id, CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1 - ELSE amount END, a.transdate, + ELSE a.amount END, a.transdate, CASE WHEN bc.class = 'payment' THEN 'Payment' - WHEN bc.class = 'receipt' THEN 'Receipt' WHEN bc.class = 'payment_reversal' THEN 'Payment Reversal' + END + FROM voucher v + JOIN acc_trans a ON (v.trans_id = a.trans_id) + JOIN batch_class bc ON (bc.id = v.batch_class) + JOIN chart c ON (a.chart_id = c.id) + JOIN ap ON (ap.id = a.trans_id) + JOIN entity_credit_account cr + ON (ap.entity_credit_account = cr.id) + JOIN company co ON (cr.entity_id = co.entity_id) + WHERE v.batch_id = in_batch_id + AND a.voucher_id = v.id + AND (bc.class like 'payment%' AND c.link = 'AP') + UNION + SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id, + CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1 + ELSE a.amount END, a.transdate, + CASE WHEN bc.class = 'receipt' THEN 'Receipt' WHEN bc.class = 'receipt_reversal' THEN 'Receipt Reversal' - ELSE 'UNKNOWN' END FROM voucher v JOIN acc_trans a ON (v.trans_id = a.trans_id) JOIN batch_class bc ON (bc.id = v.batch_class) JOIN chart c ON (a.chart_id = c.id) + JOIN ar ON (ar.id = a.trans_id) + JOIN entity_credit_account cr + ON (ar.entity_credit_account = cr.id) + JOIN company co ON (cr.entity_id = co.entity_id) WHERE v.batch_id = in_batch_id AND a.voucher_id = v.id - AND (bc.class like 'payment%' AND c.link = 'AP') - OR (bc.class like 'receipt%' AND c.link = 'AR') + AND (bc.class like 'receipt%' AND c.link = 'AR') UNION SELECT v.id, g.reference, g.description, v.batch_id, v.trans_id, sum(a.amount), g.transdate, 'gl' |