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

[..hidden..]

Revision: 3421
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3421&view=rev
Author:   einhverfr
Date:     2011-07-04 08:15:12 +0000 (Mon, 04 Jul 2011)

Log Message:
-----------
Database changes for manual tax entry for invoices.

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Roles.sql

Added Paths:
-----------
    trunk/sql/upgrade/3421-tax_tables.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2011-07-04 01:15:27 UTC (rev 3420)
+++ trunk/sql/Pg-database.sql	2011-07-04 08:15:12 UTC (rev 3421)
@@ -3427,4 +3427,17 @@
 COMMENT ON TABLE new_shipto IS
 $$ Tracks ship_to information for orders and invoices.$$;
 
+CREATE TABLE tax_extended (
+    account_id int references account(id),
+    tx_id int references transactions(id),
+    reference text not null,
+    tax_basis numeric,
+    rate numeric,
+    tax_amount numeric,
+    check (tax_amount = rate*tax_basis/100)
+);
+
+COMMENT ON TABLE tax_extended IS 
+$$ This stores extended information for manual tax calculations.$$;
+
 commit;

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2011-07-04 01:15:27 UTC (rev 3420)
+++ trunk/sql/modules/Roles.sql	2011-07-04 08:15:12 UTC (rev 3421)
@@ -177,7 +177,6 @@
 GRANT ALL ON id TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
 GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
 GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
-
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (1, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_create');
 INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -208,12 +207,13 @@
 GRANT ALL ON invoice_id_seq TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
 GRANT INSERT ON inventory TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
 GRANT ALL ON inventory_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
+GRANT INSERT ON tax_extended TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
 
 
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (3, 'allow', 'lsmb_<?lsmb dbname ?>__ar_invoice_create');
 INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (195, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_create');
+values (195, 'allow', 'lsmb_<?lsmb dbname ?>__ar_invoice_create');
 
 
 --CREATE ROLE "lsmb_<?lsmb dbname ?>__ar_invoice_create_voucher"
@@ -237,6 +237,7 @@
 GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
 GRANT SELECT ON invoice TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
 GRANT SELECT ON inventory TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
+GRANT SELECT ON tax_extended TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
 
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (1, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_list');
@@ -382,6 +383,7 @@
 GRANT INSERT ON inventory TO "lsmb_<?lsmb dbname ?>__ap_invoice_create";
 GRANT ALL ON invoice_id_seq TO "lsmb_<?lsmb dbname ?>__ap_invoice_create";
 GRANT ALL ON inventory_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ap_invoice_create";
+GRANT INSERT ON tax_extended TO "lsmb_<?lsmb dbname ?>__ap_invoice_create";
 
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (23, 'allow', 'lsmb_<?lsmb dbname ?>__ap_invoice_create');
@@ -410,6 +412,7 @@
 GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
 GRANT SELECT ON invoice TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
 GRANT SELECT ON inventory TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
+GRANT SELECT ON tax_extended TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
 
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (21, 'allow', 'lsmb_<?lsmb dbname ?>__ap_transaction_list');

Added: trunk/sql/upgrade/3421-tax_tables.sql
===================================================================
--- trunk/sql/upgrade/3421-tax_tables.sql	                        (rev 0)
+++ trunk/sql/upgrade/3421-tax_tables.sql	2011-07-04 08:15:12 UTC (rev 3421)
@@ -0,0 +1,13 @@
+CREATE TABLE tax_extended (
+    account_id int references account(id),
+    tx_id int references transactions(id),
+    reference text not null,
+    tax_basis numeric,
+    rate numeric,
+    tax_amount numeric,
+    check (tax_amount = rate*tax_basis/100)
+);
+
+COMMENT ON TABLE tax_extended IS 
+$$ This stores extended information for manual tax calculations.$$;
+


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