From 2712d387e93a1698243e04f9ec1ae42472cdbde9 Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Wed, 2 Apr 2008 17:26:28 +0000
Subject: payments_get_all_contact_invoices now does not use general summary
data for determining whether a voucher has been paid.
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2123 4979c152-3d1c-0410-bac9-87ea11338e46
---
sql/modules/Payment.sql | 27 +++++++++++++++++++--------
1 file changed, 19 insertions(+), 8 deletions(-)
(limited to 'sql/modules')
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index cab7a1de..7f82a88e 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -150,20 +150,20 @@ BEGIN
FOR payment_item IN
SELECT c.id AS contact_id, e.name AS contact_name,
c.meta_number AS account_number,
- sum(a.amount - a.paid) AS total_due,
+ sum(p.due) AS total_due,
compound_array(ARRAY[[
a.id::text, a.invnumber, a.transdate::text,
- a.amount::text, a.paid::text,
+ a.amount::text, (a.amount - p.due)::text,
(CASE WHEN c.discount_terms
> extract('days' FROM age(a.transdate))
THEN 0
- ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
+ ELSE (a.amount - coalesce((a.amount - p.due), 0)) * coalesce(c.discount, 0) / 100
END)::text,
- (a.amount - coalesce(a.paid, 0) -
+ (coalesce(p.due, 0) -
(CASE WHEN c.discount_terms
> extract('days' FROM age(a.transdate))
THEN 0
- ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
+ ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
END))::text]]),
sum(case when v.batch_id = in_batch_id then 1
else 0 END),
@@ -174,7 +174,7 @@ BEGIN
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,
+ paid, curr, 1 as invoice_class,
entity_credit_account, on_hold
FROM ap
WHERE in_account_class = 1
@@ -187,6 +187,16 @@ BEGIN
ORDER BY transdate
) a ON (a.entity_credit_account = c.id)
JOIN transactions t ON (a.id = t.id)
+ JOIN (SELECT trans_id,
+ sum(CASE WHEN in_account_class = 1 THEN amount
+ WHEN in_account_class = 2
+ THEN amount * -1
+ END) AS due
+ FROM acc_trans
+ JOIN chart ON (chart.id = acc_trans.chart_id)
+ WHERE ((chart.link = 'AP' AND in_account_class = 1)
+ OR (chart.link = 'AR' AND in_account_class = 2))
+ GROUP BY trans_id) p ON (a.id = p.trans_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
@@ -197,7 +207,8 @@ BEGIN
AND c.entity_class = in_account_class
AND a.curr = in_currency
AND a.entity_credit_account = c.id
- AND a.amount - a.paid <> 0
+ AND p.due <> 0
+ AND a.amount <> a.paid
AND NOT a.on_hold
AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN
(select "session_id" FROM "session"
@@ -211,7 +222,7 @@ BEGIN
= in_ar_ap_accno)
))
GROUP BY c.id, e.name, c.meta_number, c.threshold
- HAVING sum(a.amount - a.paid) > c.threshold
+ HAVING sum(p.due) > c.threshold
OR sum(case when v.batch_id = in_batch_id then 1
else 0 END) > 0
LOOP
--
cgit v1.2.3