[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4252] branches/1.3/sql/modules/Payment.sql
- Subject: SF.net SVN: ledger-smb:[4252] branches/1.3/sql/modules/Payment.sql
- From: ..hidden..
- Date: Sat, 07 Jan 2012 22:05:27 +0000
Revision: 4252
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4252&view=rev
Author: ehuelsmann
Date: 2012-01-07 22:05:27 +0000 (Sat, 07 Jan 2012)
Log Message:
-----------
Payment.sql: Since the AR.paid and AP.paid fields aren't being
updated correctly, don't depend on them as a query criterion.
Since we don't use them anyway, don't update them anymore either.
Modified Paths:
--------------
branches/1.3/sql/modules/Payment.sql
Modified: branches/1.3/sql/modules/Payment.sql
===================================================================
--- branches/1.3/sql/modules/Payment.sql 2012-01-07 12:34:21 UTC (rev 4251)
+++ branches/1.3/sql/modules/Payment.sql 2012-01-07 22:05:27 UTC (rev 4252)
@@ -81,13 +81,21 @@
JOIN company cp ON (cp.entity_id = e.id)
WHERE ec.entity_class = in_account_class
AND CASE WHEN in_account_class = 1 THEN
- ec.id IN (SELECT entity_credit_account FROM ap
- WHERE amount <> paid
- GROUP BY entity_credit_account)
+ ec.id IN (SELECT entity_credit_account
+ FROM ap
+ JOIN acc_trans ac ON ap.id = ac.trans_id
+ JOIN account_link al ON acc.chart_id = al.account_id
+ WHERE al.description = 'AP'
+ GROUP BY entity_credit_account
+ HAVING SUM(ac.amount) <> 0)
WHEN in_account_class = 2 THEN
- ec.id IN (SELECT entity_credit_account FROM ar
- WHERE amount <> paid
- GROUP BY entity_credit_account)
+ ec.id IN (SELECT entity_credit_account
+ FROM ar
+ JOIN acc_trans ac ON ar.id = ac.trans_id
+ JOIN account_link al ON acc.chart_id = al.account_id
+ WHERE al.description = 'AR'
+ GROUP BY entity_credit_account
+ HAVING SUM(ac.amount) <> 0)
END
LOOP
RETURN NEXT out_entity;
@@ -200,13 +208,13 @@
--TODO HV prepare drop entity_id from ap,ar
--FROM (SELECT id, invnumber, transdate, amount, entity_id,
FROM (SELECT id, invnumber, invoice, transdate, amount,
- 1 as invoice_class, paid, curr,
+ 1 as invoice_class, curr,
entity_credit_account, department_id, approved
FROM ap
UNION
--SELECT id, invnumber, transdate, amount, entity_id,
SELECT id, invnumber, invoice, transdate, amount,
- 2 AS invoice_class, paid, curr,
+ 2 AS invoice_class, curr,
entity_credit_account, department_id, approved
FROM ar
) a
@@ -226,7 +234,6 @@
AND c.entity_class = in_account_class
AND c.id = in_entity_credit_id
--### short term: ignore fractional cent differences
- AND ABS(a.amount - a.paid) > 0.005
AND a.curr = in_curr
AND (a.transdate >= in_datefrom
OR in_datefrom IS NULL)
@@ -349,7 +356,7 @@
FROM entity e
JOIN entity_credit_account c ON (e.id = c.entity_id)
JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id,
- paid, curr, 1 as invoice_class,
+ curr, 1 as invoice_class,
entity_credit_account, on_hold, v.batch_id,
approved
FROM ap
@@ -359,7 +366,7 @@
AND (v.batch_class = 1 or v.batch_id IS NULL)
UNION
SELECT ar.id, invnumber, transdate, amount, entity_id,
- paid, curr, 2 as invoice_class,
+ curr, 2 as invoice_class,
entity_credit_account, on_hold, v.batch_id,
approved
FROM ar
@@ -398,7 +405,6 @@
AND a.entity_credit_account = c.id
AND p.due <> 0
--### short term: ignore fractional differences
- AND ABS(a.amount - a.paid) > 0.005
AND NOT a.on_hold
AND EXISTS (select trans_id FROM acc_trans
WHERE trans_id = a.id AND
@@ -536,21 +542,6 @@
||$E$
FROM bulk_payments_in where amount <> 0 $E$;
- IF in_account_class = 1 THEN
- EXECUTE $E$
- UPDATE ap
- set paid = paid + (select amount from bulk_payments_in b
- where b.id = ap.id),
- datepaid = $E$ || quote_literal(in_payment_date) || $E$
- where id in (select id from bulk_payments_in) $E$;
- ELSE
- EXECUTE $E$
- UPDATE ar
- set paid = paid + (select amount from bulk_payments_in b
- where b.id = ar.id),
- datepaid = $E$ || quote_literal(in_payment_date) || $E$
- where id in (select id from bulk_payments_in) $E$;
- END IF;
EXECUTE $E$ DROP TABLE bulk_payments_in $E$;
perform unlock_all();
return out_count;
@@ -1087,30 +1078,6 @@
COALESCE(in_source, ''),
case when in_batch_id is not null then false
else true end, t_voucher_id);
- IF in_account_class = 1 THEN
- UPDATE ap SET paid = amount -
- (SELECT sum(a.amount)
- FROM acc_trans a
- JOIN chart c ON (a.chart_id = c.id)
- WHERE c.link = 'AP'
- AND trans_id = pay_row.trans_id
- ),
- force_closed = false
- WHERE id = pay_row.trans_id;
- ELSIF in_account_class = 2 THEN
- update ar SET paid = amount -
- (SELECT sum(a.amount)
- FROM acc_trans a
- JOIN chart c ON (a.chart_id = c.id)
- WHERE c.link = 'AR'
- AND trans_id = pay_row.trans_id
- ) * -1,
- force_closed = false
- WHERE id = pay_row.trans_id;
- ELSE
- RAISE EXCEPTION 'Unknown account class for payments %',
- in_account_class;
- END IF;
END LOOP;
RETURN 1;
END;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.