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

[..hidden..]

Revision: 2436
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2436&view=rev
Author:   einhverfr
Date:     2008-12-29 22:34:59 +0000 (Mon, 29 Dec 2008)

Log Message:
-----------
Correcting voucher issues for staging

Modified Paths:
--------------
    trunk/sql/modules/Inventory.sql
    trunk/sql/modules/Voucher.sql
    trunk/sql/modules/test/Voucher.sql

Modified: trunk/sql/modules/Inventory.sql
===================================================================
--- trunk/sql/modules/Inventory.sql	2008-12-26 00:08:55 UTC (rev 2435)
+++ trunk/sql/modules/Inventory.sql	2008-12-29 22:34:59 UTC (rev 2436)
@@ -7,7 +7,8 @@
 BEGIN
 	SELECT id INTO t_parts_id 
 	FROM parts 
-	WHERE partnumber = in_partnumber
+	WHERE (partnumber like in_partnumber|| ' %'
+		or partnumber = in_partnumber)
 		and obsolete is not true
 		and assembly is not true;
 
@@ -17,7 +18,7 @@
                 AS onhand, p.notes, p.makemodel, p.assembly, p.alternate, 
 		p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
 		p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id, 
-		p.project_id, p.avgcost
+		p.project_id
 	INTO out_row
         FROM parts p
 	LEFT JOIN (  SELECT product(qty) as multiplier, t_parts_id  as part_used
@@ -35,7 +36,8 @@
 	JOIN (select id, transdate from ar
 		UNION select id, transdate from ap) a ON (i.trans_id = a.id)
 
-        WHERE p.partnumber = in_partnumber
+        WHERE (p.partnumber = in_partnumber 
+		or p.partnumber like in_partnumber || ' %')
 		AND a.transdate <= in_transdate
                 AND assembly IS FALSE AND obsolete IS NOT TRUE
         GROUP BY p.id, p.partnumber, p.description, p.unit, p.listprice,

Modified: trunk/sql/modules/Voucher.sql
===================================================================
--- trunk/sql/modules/Voucher.sql	2008-12-26 00:08:55 UTC (rev 2435)
+++ trunk/sql/modules/Voucher.sql	2008-12-29 22:34:59 UTC (rev 2436)
@@ -80,8 +80,8 @@
 		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 a.amount  END, a.transdate, 
+			sum(CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1
+			     ELSE a.amount  END), a.transdate, 
 			CASE WHEN bc.class = 'payment' THEN 'Payment'
 			     WHEN bc.class = 'payment_reversal' 
 			     THEN 'Payment Reversal'
@@ -97,6 +97,9 @@
 		WHERE v.batch_id = in_batch_id 
 			AND a.voucher_id = v.id
 			AND (bc.class like 'payment%' AND c.link = 'AP')
+		GROUP BY v.id, a.source, cr.meta_number, co.legal_name ,
+                        v.batch_id, v.trans_id, a.transdate, bc.class
+
 		UNION ALL
 		SELECT v.id, a.source, a.memo, 
 			v.batch_id, v.trans_id, 
@@ -394,7 +397,7 @@
 		DELETE FROM ap WHERE id = voucher_row.trans_id;
 		DELETE FROM gl WHERE id = voucher_row.trans_id;
 		DELETE FROM voucher WHERE id = voucher_row.id;
-		DELETE FROM transactions WHERE id = voucher_row.trans_id;
+		-- DELETE FROM transactions WHERE id = voucher_row.trans_id;
 	ELSE 
 		update ar set paid = amount + 
 			(select sum(amount) from acc_trans 

Modified: trunk/sql/modules/test/Voucher.sql
===================================================================
--- trunk/sql/modules/test/Voucher.sql	2008-12-26 00:08:55 UTC (rev 2435)
+++ trunk/sql/modules/test/Voucher.sql	2008-12-29 22:34:59 UTC (rev 2436)
@@ -19,6 +19,11 @@
 INSERT INTO acc_trans(trans_id, chart_id, amount, approved)
 values (-5, -5, 1000, true);
 
+INSERT INTO ap (id, invnumber, amount, curr, approved, entity_credit_account)
+VALUES (-6, 'test1', '1000', 'USD', false, -1);
+
+INSERT INTO acc_trans(trans_id, chart_id, amount, approved)
+values (-6, -5, 1000, true);
 INSERT INTO voucher (trans_id, batch_id, batch_class)
 values (-5, currval('batch_id_seq'), 1);
 INSERT INTO voucher (trans_id, batch_id, batch_class)


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