SF.net SVN: ledger-smb: [1596] trunk/sql/modules/Payment.sql

[..hidden..]

Revision: 1596
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1596&view=rev
Author:   einhverfr
Date:     2007-09-13 21:56:25 -0700 (Thu, 13 Sep 2007)

Log Message:
-----------
Adding multi-vendor searches now require the account number for the AR/AP account

Modified Paths:
--------------
    trunk/sql/modules/Payment.sql

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2007-09-14 04:27:47 UTC (rev 1595)
+++ trunk/sql/modules/Payment.sql	2007-09-14 04:56:25 UTC (rev 1596)
@@ -95,7 +95,8 @@
 
 CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
 (in_account_class int, in_business_type int, in_currency char(3),
-	in_date_from date, in_date_to date, in_batch_id int)
+	in_date_from date, in_date_to date, in_batch_id int, 
+	in_ar_ap_accno text)
 RETURNS SETOF payment_contact_invoice AS
 $$
 DECLARE payment_item payment_contact_invoice;
@@ -135,8 +136,14 @@
 		                          WHERE batch_id = in_batch_id))
 		         AND c.entity_class = in_account_class
 		         AND a.curr = in_currency
+		         AND EXISTS (select trans_id FROM acc_trans
+		                      WHERE trans_id = a.id AND
+		                            chart_id = (SELECT id frOM chart
+		                                         WHERE accno
+		                                               = in_ar_ap_accno)
+		                    )
 		GROUP BY e.id, e.name, c.meta_number, c.threshold
-		  HAVING sum(amount - a.paid) > c.threshold
+		  HAVING sum(a.amount - a.paid) > c.threshold
 	LOOP
 		RETURN NEXT payment_item;
 	END LOOP;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.