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

[..hidden..]

Revision: 3103
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3103&view=rev
Author:   einhverfr
Date:     2010-10-20 18:29:17 +0000 (Wed, 20 Oct 2010)

Log Message:
-----------
Fixing number signing issues for tax form reports

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

Modified: trunk/sql/modules/1099_reports.sql
===================================================================
--- trunk/sql/modules/1099_reports.sql	2010-10-19 17:36:16 UTC (rev 3102)
+++ trunk/sql/modules/1099_reports.sql	2010-10-20 18:29:17 UTC (rev 3103)
@@ -34,14 +34,17 @@
               SELECT company.legal_name, company.entity_id, 
                      entity_credit_account.entity_class, entity.control_code, 
                      entity_credit_account.meta_number, 
-                     sum(CASE WHEN relation = 'acc_trans' THEN ac.amount 
+                     sum(CASE WHEN relation = 'acc_trans' THEN 
+                                   CASE WHEN gl.class = 'ar' then ac.amount
+                                   ELSE ac.amount * -1
+                                   END
                               ELSE 0 END), 
                      sum(CASE WHEN relation = 'invoice' THEN ac.amount 
                               ELSE 0 END), 
                      sum(ac.amount)
-		FROM (select id, transdate, entity_credit_account FROM ar 
+		FROM (select id, transdate, entity_credit_account, 'ar' as class FROM ar 
                        UNION 
-                      select id, transdate, entity_credit_account from ap
+                      select id, transdate, entity_credit_account, 'ap' as class from ap
                      ) gl
                 JOIN (select trans_id, 'acc_trans' as relation, amount as amount, 
                              atf.reportable

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2010-10-19 17:36:16 UTC (rev 3102)
+++ trunk/sql/modules/Payment.sql	2010-10-20 18:29:17 UTC (rev 3103)
@@ -537,7 +537,7 @@
 		$E$ || t_voucher_id || $E$, $E$|| quote_literal(in_payment_date) 
 		||$E$ , $E$ ||COALESCE(quote_literal(in_source), 'NULL') || 
 		$E$ , $E$ || coalesce(quote_literal(in_payment_type), 'NULL') || $E$
-		FROM bulk_payments_in $E$;
+		FROM bulk_payments_in  where amount <> 0 $E$;
 
 	EXECUTE $E$ 
 		INSERT INTO acc_trans 
@@ -556,7 +556,7 @@
 		$E$ || t_voucher_id || $E$, $E$|| quote_literal(in_payment_date) 
 		||$E$ , $E$ ||COALESCE(quote_literal(in_source), 'null') 
 		||$E$ , $E$ || coalesce(quote_literal(in_payment_type), 'NULL') || $E$ 
-		FROM bulk_payments_in $E$;
+		FROM bulk_payments_in where amount <> 0 $E$;
 
         IF in_account_class = 1 THEN
         	EXECUTE $E$


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