SF.net SVN: ledger-smb:[5666] branches/1.3/sql/upgrade/sl2.8-1.3.sql

[..hidden..]

Revision: 5666
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5666&view=rev
Author:   einhverfr
Date:     2013-02-21 08:32:00 +0000 (Thu, 21 Feb 2013)
Log Message:
-----------
More refinements to SL28-1.3 migration scripts

Modified Paths:
--------------
    branches/1.3/sql/upgrade/sl2.8-1.3.sql

Modified: branches/1.3/sql/upgrade/sl2.8-1.3.sql
===================================================================
--- branches/1.3/sql/upgrade/sl2.8-1.3.sql	2013-02-15 08:44:12 UTC (rev 5665)
+++ branches/1.3/sql/upgrade/sl2.8-1.3.sql	2013-02-21 08:32:00 UTC (rev 5666)
@@ -19,6 +19,9 @@
 
 
 --Accounts
+
+insert into account_link_description values ('CT_tax', false, false);
+
 INSERT INTO account_heading(id, accno, description)
 SELECT id, accno, description
   FROM sl28.chart WHERE charttype = 'H';
@@ -28,6 +31,9 @@
                     string_to_array(link,':'))
   FROM sl28.chart 
  WHERE charttype = 'A';
+
+delete from account_link where description = 'CT_tax';
+
 --Entity
 
 INSERT INTO entity (name, control_code, entity_class, country_id)
@@ -53,11 +59,12 @@
 INSERT INTO entity_credit_account
 (entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
 	cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, vendornumber, business_id, creditlimit, :ap, 
+SELECT entity_id, vendornumber, business_id, creditlimit, aa.id, 
 	a.id, startdate, enddate, threshold, 1
 FROM sl28.vendor 
 LEFT JOIN sl28.chart c on payment_accno_id = c.id
 LEFT JOIN account a on c.accno = a.accno 
+     JOIN account aa ON aa.accno = :ap
     WHERE entity_id IS NOT NULL;
 
 UPDATE sl28.vendor SET credit_id = 
@@ -68,11 +75,12 @@
 INSERT INTO entity_credit_account
 (entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
 	cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, customernumber, business_id, creditlimit, :ar, 
+SELECT entity_id, customernumber, business_id, creditlimit, aa.id, 
 	a.id, startdate, enddate, threshold, 2
 FROM sl28.customer
 LEFT JOIN sl28.chart c on payment_accno_id = c.id
 LEFT JOIN account a on c.accno = a.accno 
+     JOIN account aa ON aa.accno = :ar
     WHERE entity_id IS NOT NULL;
 
 UPDATE sl28.customer SET credit_id = 
@@ -170,7 +178,7 @@
     min(location_save(NULL,
 
     case 
-        when oa.address1 = '' then 'Null' 
+        when oa.address1 !~ '[[:alnum:]_]' then 'Null'
         when oa.address1 is null then 'Null'
         else oa.address1 
     end,
@@ -214,7 +222,7 @@
     min(location_save(NULL,
 
     case 
-        when oa.shiptoaddress1 = '' then 'Null' 
+        when oa.shiptoaddress1 !~ '[[:alnum:]_]' then 'Null'
         when oa.shiptoaddress1 is null then 'Null'
         else oa.shiptoaddress1 
     end,
@@ -414,10 +422,10 @@
 
 INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
             sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes)
+            deliverydate, serialnumber)
     SELECT  id, trans_id, parts_id, description, qty, allocated,
             sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes
+            deliverydate, serialnumber
        FROM sl28.invoice;
 
 INSERT INTO partstax (parts_id, chart_id)
@@ -483,9 +491,9 @@
        FROM sl28.project p
        JOIN sl28.customer c ON p.customer_id = c.id;
 
-INSERT INTO partsgroup SELECT * FROM sl28.partsgroup;
+INSERT INTO partsgroup (id, partsgroup) SELECT id, partsgroup FROM sl28.partsgroup;
 
-INSERT INTO status SELECT * FROM sl28.status;
+INSERT INTO status SELECT * FROM sl28.status; -- may need to comment this one out sometimes
 
 INSERT INTO department SELECT * FROM sl28.department;
 

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