[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[6545] trunk



Revision: 6545
          http://sourceforge.net/p/ledger-smb/code/6545
Author:   ehuelsmann
Date:     2014-01-19 09:57:53 +0000 (Sun, 19 Jan 2014)
Log Message:
-----------
Rename SL2.8 migration script (to indicate it migrates to 1.4).

Modified Paths:
--------------
    trunk/LedgerSMB/Scripts/setup.pm

Added Paths:
-----------
    trunk/sql/upgrade/sl2.8-1.4.sql

Removed Paths:
-------------
    trunk/sql/upgrade/sl2.8-1.3.sql

Modified: trunk/LedgerSMB/Scripts/setup.pm
===================================================================
--- trunk/LedgerSMB/Scripts/setup.pm	2014-01-19 08:01:15 UTC (rev 6544)
+++ trunk/LedgerSMB/Scripts/setup.pm	2014-01-19 09:57:53 UTC (rev 6545)
@@ -986,7 +986,7 @@
     # process_and_run_upgrade_script commits the transaction
 
     process_and_run_upgrade_script($request, $database, "sl28",
-				   'sl2.8-1.3');
+				   'sl2.8-1.4');
 
     create_initial_user($request);
 }

Deleted: trunk/sql/upgrade/sl2.8-1.3.sql
===================================================================
--- trunk/sql/upgrade/sl2.8-1.3.sql	2014-01-19 08:01:15 UTC (rev 6544)
+++ trunk/sql/upgrade/sl2.8-1.3.sql	2014-01-19 09:57:53 UTC (rev 6545)
@@ -1,723 +0,0 @@
---Setup
-
--- When moved to an interface, these will all be specified and preprocessed.
-\set default_country '''<?lsmb default_country ?>'''
-\set ar '''<?lsmb default_ar ?>'''
-\set ap '''<?lsmb default_ap ?>'''
-
-\set default_country '''AF'''
-\set ar '''1310'''
-\set ap '''2210'''
-
-
-BEGIN;
-
--- adding mapping info for import.
-
-ALTER TABLE sl28.vendor ADD COLUMN entity_id int;
-ALTER TABLE sl28.vendor ADD COLUMN company_id int;
-ALTER TABLE sl28.vendor ADD COLUMN credit_id int;
-
-ALTER TABLE sl28.customer ADD COLUMN entity_id int;
-ALTER TABLE sl28.customer ADD COLUMN company_id int;
-ALTER TABLE sl28.customer ADD COLUMN credit_id int;
-
-
---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';
-
-SELECT account__save(id, accno, description, category, gifi_accno, NULL::int,
-                    contra,
-                    CASE WHEN link like '%tax%' THEN true ELSE false END,
-                    string_to_array(link,':'), 'f', 'f')
-  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)
-SELECT name, 'V-' || vendornumber, 1, 
-       (select id from country 
-         where lower(short_name)  = lower(:default_country))
-FROM sl28.vendor
-GROUP BY name, vendornumber;
-
-INSERT INTO entity (name, control_code, entity_class, country_id)
-SELECT name, 'C-' || customernumber, 2, 
-       (select id from country 
-         where lower(short_name)  =  lower(:default_country))
-FROM sl28.customer
-GROUP BY name, customernumber;
-
-UPDATE sl28.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
-
-UPDATE sl28.customer SET entity_id = coalesce((SELECT min(id) FROM entity WHERE 'C-' || customernumber = control_code), entity_id);
-
---Entity Credit Account
-
-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,
-       (select id
-          from account
-         where accno = coalesce((select accno from sl28.chart
-                                  where id = arap_accno_id) ,:ap)),   
-	(select id
-	   from account
-	   where accno = (select accno from sl28.chart
-	                   where id = payment_accno_id)),
-	 startdate, enddate, threshold, 1
-FROM sl28.vendor WHERE entity_id IS NOT NULL;
-
-UPDATE sl28.vendor SET credit_id = 
-	(SELECT id FROM entity_credit_account e 
-	WHERE e.meta_number = vendornumber and entity_class = 1
-        and e.entity_id = vendor.entity_id);
-
-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,
-       (select id
-          from account
-         where accno = coalesce((select accno from sl28.chart
-                                  where id = arap_accno_id) ,:ar)), 
-	(select id
-	   from account
-	   where accno = (select accno from sl28.chart
-	                   where id = payment_accno_id)),
-        startdate, enddate, threshold, 2
-FROM sl28.customer WHERE entity_id IS NOT NULL;
-
-UPDATE sl28.customer SET credit_id = 
-	(SELECT id FROM entity_credit_account e 
-	WHERE e.meta_number = customernumber and entity_class = 2
-        and e.entity_id = customer.entity_id);
-
---Company
-
-INSERT INTO company (entity_id, legal_name, tax_id)
-SELECT entity_id, name, max(taxnumber) FROM sl28.vendor 
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE sl28.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
-
-INSERT INTO company (entity_id, legal_name, tax_id)
-SELECT entity_id, name, max(taxnumber) FROM sl28.customer
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE sl28.customer SET company_id = (select id from company c where entity_id = customer.entity_id);
-
--- Contact
-
-insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
-select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
-from sl28.vendor v 
-where v.company_id is not null and v.phone is not null 
-       and v.phone ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.phone
-UNION
-select v.credit_id, 12, v.email, 
-       'email address: '||max(v.contact) as description 
-from sl28.vendor v 
-where v.company_id is not null and v.email is not null 
-       and v.email ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.email
-UNION
-select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
-from sl28.vendor v 
-where v.company_id is not null and v.cc is not null 
-      and v.cc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.cc
-UNION 
-select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
-from sl28.vendor v 
-where v.company_id is not null and v.bcc is not null 
-       and v.bcc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.bcc
-UNION
-    select v.credit_id, 9, v.fax, 'Fax number' as description 
-from sl28.vendor v 
-where v.company_id is not null and v.fax is not null 
-      and v.fax ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.fax;
-
-insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
-select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
-from sl28.customer v 
-where v.company_id is not null and v.phone is not null 
-       and v.phone ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.phone
-UNION
-select v.credit_id, 12, v.email, 
-       'email address: '||max(v.contact) as description 
-from sl28.customer v 
-where v.company_id is not null and v.email is not null 
-       and v.email ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.email
-UNION
-select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
-from sl28.customer v 
-where v.company_id is not null and v.cc is not null 
-      and v.cc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.cc
-UNION 
-select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
-from sl28.customer v 
-where v.company_id is not null and v.bcc is not null 
-       and v.bcc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.bcc
-UNION
-    select v.credit_id, 9, v.fax, 'Fax number' as description 
-from sl28.customer v 
-where v.company_id is not null and v.fax is not null 
-      and v.fax ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.fax;
-
-
--- addresses
-
-INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 1,
-    min(location_save(NULL,
-
-    case 
-        when oa.address1 !~ '[[:alnum:]_]' then 'Null'
-        when oa.address1 is null then 'Null'
-        else oa.address1 
-    end,
-    oa.address2, 
-    NULL,
-    case 
-        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.city is null then 'Null' 
-        else oa.city 
-    end,
-    case 
-        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.state is null then 'Null' 
-        else oa.state 
-    end,
-    case 
-        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.zipcode is null then 'Null' 
-        else oa.zipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     sl28.address oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
-JOIN (select credit_id, id from sl28.vendor
-          union
-           select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
-JOIN entity_credit_account eca ON (v.credit_id = eca.id)
-GROUP BY eca.id;
-
--- Shipto
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 2,
-    min(location_save(NULL,
-
-    case 
-        when oa.shiptoaddress1 !~ '[[:alnum:]_]' then 'Null'
-        when oa.shiptoaddress1 is null then 'Null'
-        else oa.shiptoaddress1 
-    end,
-    oa.shiptoaddress2, 
-    NULL,
-    case 
-        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptocity is null then 'Null' 
-        else oa.shiptocity 
-    end,
-    case 
-        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptostate is null then 'Null' 
-        else oa.shiptostate 
-    end,
-    case 
-        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptozipcode is null then 'Null' 
-        else oa.shiptozipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     sl28.shipto oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
-JOIN (select credit_id, id from sl28.vendor
-          union
-           select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
-JOIN entity_credit_account eca ON (v.credit_id = eca.id)
-GROUP BY eca.id;
-
-INSERT INTO eca_note(note_class, ref_key, note, vector)
-SELECT 3, credit_id, notes, '' FROM sl28.vendor 
-WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
-
-INSERT INTO eca_note(note_class, ref_key, note, vector)
-SELECT 3, credit_id, notes, '' FROM sl28.customer
-WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
-
-UPDATE entity SET country_id = 
-(select country_id FROM location l 
-   JOIN eca_to_location e2l ON l.id = e2l.location_id
-        AND e2l.location_class = 1
-   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
-  WHERE eca.entity_id = entity_id
-        AND l.country_id > -1
-  LIMIT 1)
-WHERE id IN
-(select eca.entity_id FROM location l 
-   JOIN eca_to_location e2l ON l.id = e2l.location_id
-        AND e2l.location_class = 1
-   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
-  WHERE eca.entity_id = entity_id
-       aND l.country_id > -1);
-
-INSERT INTO pricegroup
-SELECT * FROM sl28.pricegroup;
-
-ALTER TABLE sl28.employee ADD entity_id int;
-
-INSERT INTO entity(control_code, entity_class, name, country_id)
-select 'E-' || employeenumber, 3, name,
-        (select id from country where lower(short_name) = lower(:default_country))
-FROM sl28.employee;
-
-UPDATE sl28.employee set entity_id = 
-       (select id from entity where 'E-'||employeenumber = control_code);
-
-INSERT INTO person (first_name, last_name, entity_id) 
-select name, name, entity_id FROM sl28.employee;
-
-INSERT INTO users (entity_id, username)
-     SELECT entity_id, login FROM sl28.employee em
-      WHERE login IS NOT NULL;
-
-INSERT 
-  INTO entity_employee(entity_id, startdate, enddate, role, ssn, sales,
-       employeenumber, dob, manager_id)
-SELECT entity_id, startdate, enddate, role, ssn, sales, employeenumber, dob,
-       (select entity_id from sl28.employee where id = em.managerid)
-  FROM sl28.employee em;
-
-
-
--- must rebuild this table due to changes since 1.2
-
-INSERT INTO partsgroup (id, partsgroup) SELECT id, partsgroup FROM sl28.partsgroup;
-
-INSERT INTO parts (id, partnumber, description, unit,
-listprice, sellprice, lastcost, priceupdate, weight, onhand, notes,
-makemodel, assembly, alternate, rop, inventory_accno_id,
-income_accno_id, expense_accno_id, bin, obsolete, bom, image,
-drawing, microfiche, partsgroup_id, avgcost)
- SELECT id, partnumber, description, unit,
-listprice, sellprice, lastcost, priceupdate, weight, onhand, notes,
-makemodel, assembly, alternate, rop, (select id
-          from public.account
-         where accno = (select accno from sl28.chart
-                         where id = inventory_accno_id)),
-(select id
-          from public.account
-         where accno = (select accno from sl28.chart
-                         where id = income_accno_id)), (select id
-          from public.account
-         where accno = (select accno from sl28.chart
-                         where id = expense_accno_id)),
- bin, obsolete, bom, image,
-drawing, microfiche, partsgroup_id, avgcost FROM sl28.parts;
-
-
-INSERT INTO makemodel (parts_id, make, model) 
-SELECT parts_id, make, model FROM sl28.makemodel;
-
-INSERT INTO gifi
-SELECT * FROM sl28.gifi;
-
-/* TODO -- can't be solved this easily: a freshly created defaults
-table contains 30 keys, one after having saved the System->Defaults
-screen contains 58. Also, there are account IDs here, which should
-be migrated using queries, not just copied over.
-
-To watch out for: keys which are semantically the same, but have
-different names
-
-UPDATE defaults 
-   SET value = (select fldvalue from sl28.defaults src 
-                 WHERE src.fldname = defaults.setting_key)
- WHERE setting_key IN (select fldvalue FROM sl28.defaults
-                        where );
-*/
-
-INSERT INTO assembly (id, parts_id, qty, bom, adj)
-SELECT id, parts_id, qty, bom, adj  FROM sl28.assembly;
-
-ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
-
-INSERT INTO business_unit (id, class_id, control_code, description)
-SELECT id, 1, id, description
-  FROM sl28.department;
-
-INSERT INTO business_unit (id, class_id, control_code, description,
-       start_date, end_date, credit_id)
-SELECT 1000+id, 2, projectnumber, description, startdate, enddate,
-       (select credit_id
-          from sl28.customer c
-         where c.id = p.customer_id)
-  FROM sl28.project p;
-
-
-INSERT INTO gl(id, reference, description, transdate, person_id, notes)
-    SELECT gl.id, reference, description, transdate, p.id, gl.notes
-      FROM sl28.gl 
- LEFT JOIN sl28.employee em ON gl.employee_id = em.id
- LEFT JOIN person p ON em.entity_id = p.id;
-
-ALTER TABLE gl ENABLE TRIGGER gl_audit_trail;
-
-ALTER TABLE ar DISABLE TRIGGER ar_audit_trail;
-
-insert into ar 
-(entity_credit_account, person_id,
-	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
-	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
-	shipvia, language_code, ponumber, shippingpoint, 
-	on_hold, approved, reverse, terms, description)
-SELECT 
-	customer.credit_id,
-	(select entity_id from sl28.employee 
-		WHERE id = ar.employee_id),
-	ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, paid, 
-	datepaid, duedate, invoice, ordnumber, ar.curr, ar.notes, quonumber, 
-	intnotes,
-	shipvia, ar.language_code, ponumber, shippingpoint, 
-	onhold, approved, case when amount < 0 then true else false end,
-	ar.terms, description
-FROM sl28.ar JOIN sl28.customer ON (ar.customer_id = customer.id) ;
-
-ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
-
-ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
-
-insert into ap 
-(entity_credit_account, person_id,
-	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
-	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
-        shipvia, language_code, ponumber, shippingpoint, 
-	on_hold, approved, reverse, terms, description)
-SELECT 
-	vendor.credit_id,
-	(select entity_id from sl28.employee 
-		WHERE id = ap.employee_id),
-	ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid, 
-	datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber, 
-	intnotes,
-	shipvia, ap.language_code, ponumber, shippingpoint, 
-	onhold, approved, case when amount < 0 then true else false end,
-	ap.terms, description
-FROM sl28.ap JOIN sl28.vendor ON (ap.vendor_id = vendor.id) ;
-
-ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
-
--- ### TODO: there used to be projects here!
--- ### Move those to business_units
--- ### TODO: Reconciled disappeared from the source table...
-
-ALTER TABLE sl28.acc_trans ADD COLUMN entry_id integer;
-
-update sl28.acc_trans
-  set entry_id = nextval('acc_trans_entry_id_seq');
-
-INSERT INTO acc_trans
-(entry_id, trans_id, chart_id, amount, transdate, source, cleared, fx_transaction, 
-	memo, approved, cleared_on, voucher_id)
-SELECT entry_id, trans_id, (select id
-                    from account
-                   where accno = (select accno
-                                    from sl28.chart
-                                   where chart.id = acc_trans.chart_id)),
-                                    amount, transdate, source,
-	CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
-	memo, approved, cleared, vr_id
-	FROM sl28.acc_trans ;
-
-INSERT INTO business_unit_ac (entry_id, class_id, bu_id)
-SELECT ac.entry_id, 1, gl.department_id
-  FROM acc_trans ac 
-  JOIN (SELECT id, department_id FROM sl28.ar UNION ALL
-        SELECT id, department_id FROM sl28.ap UNION ALL
-        SELECT id, department_id FROM sl28.gl) gl ON gl.id = ac.trans_id 
- WHERE department_id > 0;
-
-INSERT INTO business_unit_ac (entry_id, class_id, bu_id)
-SELECT ac.entry_id, 2, slac.project_id+1000
-  FROM acc_trans ac 
-  JOIN sl28.acc_trans slac ON slac.entry_id = ac.entry_id 
- WHERE project_id > 0;
-
-
- INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit,
-            deliverydate, serialnumber)
-    SELECT  id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit,
-            deliverydate, serialnumber
-       FROM sl28.invoice;
-
-INSERT INTO business_unit_inv (entry_id, class_id, bu_id)
-SELECT inv.id, 1, gl.department_id
-  FROM invoice inv 
-  JOIN (SELECT id, department_id FROM sl28.ar UNION ALL
-        SELECT id, department_id FROM sl28.ap UNION ALL
-        SELECT id, department_id FROM sl28.gl) gl ON gl.id = inv.trans_id
- WHERE department_id > 0;
-
-INSERT INTO business_unit_inv (entry_id, class_id, bu_id)
-SELECT id, 2, project_id + 1000 FROM sl28.invoice 
- WHERE project_id > 0 and  project_id in (select id from sl28.project);
-
-
-
-
-INSERT INTO partstax (parts_id, chart_id)
-     SELECT parts_id, a.id
-       FROM sl28.partstax pt
-       JOIN sl28.chart ON chart.id = pt.chart_id
-       JOIN account a ON chart.accno = a.accno;
-
-INSERT INTO tax(chart_id, rate, taxnumber, validto)
-     SELECT a.id, t.rate, t.taxnumber, 
-            coalesce(t.validto::timestamp, 'infinity')
-       FROM sl28.tax t
-       JOIN sl28.chart c ON (t.chart_id = c.id)
-       JOIN account a ON (a.accno = c.accno);
-
-
--- ### TODO: Customer tax does not exist in LSMB. What is its function in SL??
--- ###  LSMB moved these tables jointly to eca_tax
--- INSERT INTO customertax (customer_id, chart_id)
---      SELECT c.credit_id,  a.id
---        FROM sl28.customertax pt
---        JOIN sl28.customer c ON (pt.customer_id = c.id)
---        JOIN sl28.chart ON chart.id = pt.chart_id
---        JOIN account a ON chart.accno = a.accno; 
-
--- INSERT INTO vendortax (vendor_id, chart_id)
---      SELECT c.credit_id,  a.id
---        FROM sl28.vendortax pt       
---        JOIN sl28.vendor c ON (pt.vendor_id = c.id)
---        JOIN sl28.chart ON chart.id = pt.chart_id
---        JOIN account a ON chart.accno = a.accno;
-
-INSERT 
-  INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
-       shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
-       intnotes, shipvia, language_code, ponumber, terms,
-       entity_credit_account, oe_class_id)
-SELECT oe.id,  ordnumber, transdate, amount, netamount, reqdate, oe.taxincluded,
-       shippingpoint, oe.notes, oe.curr, p.id, closed, quotation, quonumber,
-       intnotes, shipvia, oe.language_code, ponumber, oe.terms,
-       coalesce(c.credit_id, v.credit_id),
-       case 
-           when c.id is not null and quotation is not true THEN 1
-           WHEN v.id is not null and quotation is not true THEN 2
-           when c.id is not null and quotation is true THEN 3
-           WHEN v.id is not null and quotation is true THEN 4
-       end
-  FROM sl28.oe
-  LEFT JOIN sl28.customer c ON c.id = oe.customer_id
-  LEFT JOIN sl28.vendor v ON v.id = oe.vendor_id
-  LEFT JOIN sl28.employee e ON oe.employee_id = e.id
-  LEFT JOIN person p ON e.entity_id = p.id;
-
-INSERT INTO orderitems(id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, reqdate, ship, serialnumber)
-     SELECT id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, reqdate, ship, serialnumber
-       FROM sl28.orderitems;
-
-INSERT INTO business_unit_oitem (entry_id, class_id, bu_id)
-SELECT oi.id, 1, oe.department_id 
-  FROM orderitems oi
-  JOIN sl28.oe ON oi.trans_id = oe.id AND department_id > 0;
-
-INSERT INTO business_unit_oitem (entry_id, class_id, bu_id)
-SELECT id, 2, project_id + 1000 FROM sl28.orderitems
- WHERE project_id > 0  and  project_id in (select id from sl28.project);
-
-
-INSERT INTO exchangerate select * from sl28.exchangerate;
-
--- ### TODO: Move to business units!
--- INSERT INTO project (id, projectnumber, description, startdate, enddate,
---             parts_id, production, completed, credit_id)
---      SELECT p.id, projectnumber, description, p.startdate, p.enddate,
---             parts_id, production, completed, c.credit_id
---        FROM sl28.project p
---        JOIN sl28.customer c ON p.customer_id = c.id;
-
-
-INSERT INTO status SELECT * FROM sl28.status; -- may need to comment this one out sometimes
-
--- ### TODO: Move to business units!
--- INSERT INTO department SELECT * FROM sl28.department;
-
-INSERT INTO business SELECT * FROM sl28.business;
-
-INSERT INTO sic SELECT * FROM sl28.sic;
-
-INSERT INTO warehouse SELECT * FROM sl28.warehouse;
-
-INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate)
-     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate
-       FROM sl28.inventory i
-       JOIN sl28.employee e ON i.employee_id = e.id;
-
-INSERT INTO yearend (trans_id, transdate) SELECT * FROM sl28.yearend;
-
-INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
-            curr)
-     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
-            pv.curr
-       FROM sl28.partsvendor pv
-       JOIN sl28.vendor v ON v.id = pv.vendor_id;
-
-INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, curr)
-     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, pv.curr
-       FROM sl28.partscustomer pv
-       JOIN sl28.customer v ON v.id = pv.customer_id;
-
-INSERT INTO language SELECT * FROM sl28.language;
-
-INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
-            transdate, person_id)
-     SELECT trans_id, tablename, reference, formname, action,
-            transdate, p.entity_id
-       FROM sl28.audittrail a
-       JOIN sl28.employee e ON a.employee_id = e.id
-       JOIN person p on e.entity_id = p.entity_id;
-
-INSERT INTO user_preference(id)
-     SELECT id from users;
-
-INSERT INTO recurring(id, reference, startdate, nextdate, enddate,
-            recurring_interval, howmany, payment)
-     SELECT id, reference, startdate, nextdate, enddate, 
-            (repeat || ' ' || unit)::interval,
-            howmany, payment 
-       FROM sl28.recurring;
-
-INSERT INTO recurringemail SELECT * FROM sl28.recurringemail;
-
-INSERT INTO recurringprint SELECT * FROM sl28.recurringprint;
-
-INSERT INTO jcitems(id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            person_id, notes, business_unit_id)
-     SELECT j.id,  parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            p.id, j.notes, j.project_id+1000
-       FROM sl28.jcitems j
-       JOIN sl28.employee e ON j.employee_id = e.id
-       JOIN person p ON e.entity_id = p.entity_id;
-
-INSERT INTO parts_translation SELECT * FROM sl28.translation where trans_id in (select id from parts);
-
-INSERT INTO partsgroup_translation SELECT * FROM sl28.translation where trans_id in
- (select id from partsgroup);
-
---  ### TODO: To translate to business_units
--- INSERT INTO project_translation SELECT * FROM sl28.translation where trans_id in
---  (select id from project);
-
-SELECT setval('id', max(id)) FROM transactions;
-
- SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
- SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
- SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;
- SELECT setval('partscustomer_entry_id_seq', max(entry_id)) FROM partscustomer;
- SELECT setval('audittrail_entry_id_seq', max(entry_id)) FROM audittrail;
- SELECT setval('account_id_seq', max(id)) FROM account;
- SELECT setval('account_heading_id_seq', max(id)) FROM account_heading;
- SELECT setval('account_checkpoint_id_seq', max(id)) FROM account_checkpoint;
- SELECT setval('pricegroup_id_seq', max(id)) FROM pricegroup;
- SELECT setval('country_id_seq', max(id)) FROM country;
- SELECT setval('country_tax_form_id_seq', max(id)) FROM country_tax_form;
- SELECT setval('asset_dep_method_id_seq', max(id)) FROM asset_dep_method;
- SELECT setval('asset_class_id_seq', max(id)) FROM asset_class;
- SELECT setval('entity_class_id_seq', max(id)) FROM entity_class;
- SELECT setval('asset_item_id_seq', max(id)) FROM asset_item;
- SELECT setval('asset_disposal_method_id_seq', max(id)) FROM asset_disposal_method;
- SELECT setval('users_id_seq', max(id)) FROM users;
- SELECT setval('entity_id_seq', max(id)) FROM entity;
- SELECT setval('company_id_seq', max(id)) FROM company;
- SELECT setval('location_id_seq', max(id)) FROM location;
- SELECT setval('open_forms_id_seq', max(id)) FROM open_forms;
- SELECT setval('location_class_id_seq', max(id)) FROM location_class;
- SELECT setval('asset_report_id_seq', max(id)) FROM asset_report;
- SELECT setval('salutation_id_seq', max(id)) FROM salutation;
- SELECT setval('person_id_seq', max(id)) FROM person;
- SELECT setval('contact_class_id_seq', max(id)) FROM contact_class;
- SELECT setval('entity_credit_account_id_seq', max(id)) FROM entity_credit_account;
- SELECT setval('entity_bank_account_id_seq', max(id)) FROM entity_bank_account;
- SELECT setval('note_class_id_seq', max(id)) FROM note_class;
- SELECT setval('note_id_seq', max(id)) FROM note;
- SELECT setval('batch_class_id_seq', max(id)) FROM batch_class;
- SELECT setval('batch_id_seq', max(id)) FROM batch;
- SELECT setval('invoice_id_seq', max(id)) FROM invoice;
- SELECT setval('voucher_id_seq', max(id)) FROM voucher;
- SELECT setval('parts_id_seq', max(id)) FROM parts;
- SELECT setval('taxmodule_taxmodule_id_seq', max(taxmodule_id)) FROM taxmodule;
- SELECT setval('taxcategory_taxcategory_id_seq', max(taxcategory_id)) FROM taxcategory;
- SELECT setval('oe_id_seq', max(id)) FROM oe;
- SELECT setval('orderitems_id_seq', max(id)) FROM orderitems;
- SELECT setval('business_id_seq', max(id)) FROM business;
- SELECT setval('warehouse_id_seq', max(id)) FROM warehouse;
- SELECT setval('partsgroup_id_seq', max(id)) FROM partsgroup;
- SELECT setval('jcitems_id_seq', max(id)) FROM jcitems;
- SELECT setval('payment_type_id_seq', max(id)) FROM payment_type;
- SELECT setval('custom_table_catalog_table_id_seq', max(table_id)) FROM custom_table_catalog;
- SELECT setval('custom_field_catalog_field_id_seq', max(field_id)) FROM custom_field_catalog;
- SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
- SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
- SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
--- SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
- SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
- SELECT setval('payment_id_seq', max(id)) FROM payment;
- SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
- SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
- SELECT setval('business_unit_id_seq', max(id)) FROM business_unit;
-
-UPDATE defaults SET value = 'yes' where setting_key = 'migration_ok';
-
-COMMIT;
---TODO:  Translation migratiion.  Partsgroups?
--- TODO:  User/password Migration

Copied: trunk/sql/upgrade/sl2.8-1.4.sql (from rev 6535, trunk/sql/upgrade/sl2.8-1.3.sql)
===================================================================
--- trunk/sql/upgrade/sl2.8-1.4.sql	                        (rev 0)
+++ trunk/sql/upgrade/sl2.8-1.4.sql	2014-01-19 09:57:53 UTC (rev 6545)
@@ -0,0 +1,723 @@
+--Setup
+
+-- When moved to an interface, these will all be specified and preprocessed.
+\set default_country '''<?lsmb default_country ?>'''
+\set ar '''<?lsmb default_ar ?>'''
+\set ap '''<?lsmb default_ap ?>'''
+
+\set default_country '''AF'''
+\set ar '''1310'''
+\set ap '''2210'''
+
+
+BEGIN;
+
+-- adding mapping info for import.
+
+ALTER TABLE sl28.vendor ADD COLUMN entity_id int;
+ALTER TABLE sl28.vendor ADD COLUMN company_id int;
+ALTER TABLE sl28.vendor ADD COLUMN credit_id int;
+
+ALTER TABLE sl28.customer ADD COLUMN entity_id int;
+ALTER TABLE sl28.customer ADD COLUMN company_id int;
+ALTER TABLE sl28.customer ADD COLUMN credit_id int;
+
+
+--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';
+
+SELECT account__save(id, accno, description, category, gifi_accno, NULL::int,
+                    contra,
+                    CASE WHEN link like '%tax%' THEN true ELSE false END,
+                    string_to_array(link,':'), 'f', 'f')
+  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)
+SELECT name, 'V-' || vendornumber, 1, 
+       (select id from country 
+         where lower(short_name)  = lower(:default_country))
+FROM sl28.vendor
+GROUP BY name, vendornumber;
+
+INSERT INTO entity (name, control_code, entity_class, country_id)
+SELECT name, 'C-' || customernumber, 2, 
+       (select id from country 
+         where lower(short_name)  =  lower(:default_country))
+FROM sl28.customer
+GROUP BY name, customernumber;
+
+UPDATE sl28.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
+
+UPDATE sl28.customer SET entity_id = coalesce((SELECT min(id) FROM entity WHERE 'C-' || customernumber = control_code), entity_id);
+
+--Entity Credit Account
+
+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,
+       (select id
+          from account
+         where accno = coalesce((select accno from sl28.chart
+                                  where id = arap_accno_id) ,:ap)),   
+	(select id
+	   from account
+	   where accno = (select accno from sl28.chart
+	                   where id = payment_accno_id)),
+	 startdate, enddate, threshold, 1
+FROM sl28.vendor WHERE entity_id IS NOT NULL;
+
+UPDATE sl28.vendor SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = vendornumber and entity_class = 1
+        and e.entity_id = vendor.entity_id);
+
+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,
+       (select id
+          from account
+         where accno = coalesce((select accno from sl28.chart
+                                  where id = arap_accno_id) ,:ar)), 
+	(select id
+	   from account
+	   where accno = (select accno from sl28.chart
+	                   where id = payment_accno_id)),
+        startdate, enddate, threshold, 2
+FROM sl28.customer WHERE entity_id IS NOT NULL;
+
+UPDATE sl28.customer SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = customernumber and entity_class = 2
+        and e.entity_id = customer.entity_id);
+
+--Company
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM sl28.vendor 
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE sl28.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM sl28.customer
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE sl28.customer SET company_id = (select id from company c where entity_id = customer.entity_id);
+
+-- Contact
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from sl28.vendor v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from sl28.vendor v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from sl28.vendor v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from sl28.vendor v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from sl28.vendor v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from sl28.customer v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from sl28.customer v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from sl28.customer v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from sl28.customer v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from sl28.customer v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+
+-- addresses
+
+INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 !~ '[[:alnum:]_]' then 'Null'
+        when oa.address1 is null then 'Null'
+        else oa.address1 
+    end,
+    oa.address2, 
+    NULL,
+    case 
+        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.city is null then 'Null' 
+        else oa.city 
+    end,
+    case 
+        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.state is null then 'Null' 
+        else oa.state 
+    end,
+    case 
+        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.zipcode is null then 'Null' 
+        else oa.zipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     sl28.address oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN (select credit_id, id from sl28.vendor
+          union
+           select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
+JOIN entity_credit_account eca ON (v.credit_id = eca.id)
+GROUP BY eca.id;
+
+-- Shipto
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 !~ '[[:alnum:]_]' then 'Null'
+        when oa.shiptoaddress1 is null then 'Null'
+        else oa.shiptoaddress1 
+    end,
+    oa.shiptoaddress2, 
+    NULL,
+    case 
+        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptocity is null then 'Null' 
+        else oa.shiptocity 
+    end,
+    case 
+        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptostate is null then 'Null' 
+        else oa.shiptostate 
+    end,
+    case 
+        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptozipcode is null then 'Null' 
+        else oa.shiptozipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     sl28.shipto oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
+JOIN (select credit_id, id from sl28.vendor
+          union
+           select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
+JOIN entity_credit_account eca ON (v.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM sl28.vendor 
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM sl28.customer
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+UPDATE entity SET country_id = 
+(select country_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+        AND l.country_id > -1
+  LIMIT 1)
+WHERE id IN
+(select eca.entity_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+       aND l.country_id > -1);
+
+INSERT INTO pricegroup
+SELECT * FROM sl28.pricegroup;
+
+ALTER TABLE sl28.employee ADD entity_id int;
+
+INSERT INTO entity(control_code, entity_class, name, country_id)
+select 'E-' || employeenumber, 3, name,
+        (select id from country where lower(short_name) = lower(:default_country))
+FROM sl28.employee;
+
+UPDATE sl28.employee set entity_id = 
+       (select id from entity where 'E-'||employeenumber = control_code);
+
+INSERT INTO person (first_name, last_name, entity_id) 
+select name, name, entity_id FROM sl28.employee;
+
+INSERT INTO users (entity_id, username)
+     SELECT entity_id, login FROM sl28.employee em
+      WHERE login IS NOT NULL;
+
+INSERT 
+  INTO entity_employee(entity_id, startdate, enddate, role, ssn, sales,
+       employeenumber, dob, manager_id)
+SELECT entity_id, startdate, enddate, role, ssn, sales, employeenumber, dob,
+       (select entity_id from sl28.employee where id = em.managerid)
+  FROM sl28.employee em;
+
+
+
+-- must rebuild this table due to changes since 1.2
+
+INSERT INTO partsgroup (id, partsgroup) SELECT id, partsgroup FROM sl28.partsgroup;
+
+INSERT INTO parts (id, partnumber, description, unit,
+listprice, sellprice, lastcost, priceupdate, weight, onhand, notes,
+makemodel, assembly, alternate, rop, inventory_accno_id,
+income_accno_id, expense_accno_id, bin, obsolete, bom, image,
+drawing, microfiche, partsgroup_id, avgcost)
+ SELECT id, partnumber, description, unit,
+listprice, sellprice, lastcost, priceupdate, weight, onhand, notes,
+makemodel, assembly, alternate, rop, (select id
+          from public.account
+         where accno = (select accno from sl28.chart
+                         where id = inventory_accno_id)),
+(select id
+          from public.account
+         where accno = (select accno from sl28.chart
+                         where id = income_accno_id)), (select id
+          from public.account
+         where accno = (select accno from sl28.chart
+                         where id = expense_accno_id)),
+ bin, obsolete, bom, image,
+drawing, microfiche, partsgroup_id, avgcost FROM sl28.parts;
+
+
+INSERT INTO makemodel (parts_id, make, model) 
+SELECT parts_id, make, model FROM sl28.makemodel;
+
+INSERT INTO gifi
+SELECT * FROM sl28.gifi;
+
+/* TODO -- can't be solved this easily: a freshly created defaults
+table contains 30 keys, one after having saved the System->Defaults
+screen contains 58. Also, there are account IDs here, which should
+be migrated using queries, not just copied over.
+
+To watch out for: keys which are semantically the same, but have
+different names
+
+UPDATE defaults 
+   SET value = (select fldvalue from sl28.defaults src 
+                 WHERE src.fldname = defaults.setting_key)
+ WHERE setting_key IN (select fldvalue FROM sl28.defaults
+                        where );
+*/
+
+INSERT INTO assembly (id, parts_id, qty, bom, adj)
+SELECT id, parts_id, qty, bom, adj  FROM sl28.assembly;
+
+ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
+
+INSERT INTO business_unit (id, class_id, control_code, description)
+SELECT id, 1, id, description
+  FROM sl28.department;
+
+INSERT INTO business_unit (id, class_id, control_code, description,
+       start_date, end_date, credit_id)
+SELECT 1000+id, 2, projectnumber, description, startdate, enddate,
+       (select credit_id
+          from sl28.customer c
+         where c.id = p.customer_id)
+  FROM sl28.project p;
+
+
+INSERT INTO gl(id, reference, description, transdate, person_id, notes)
+    SELECT gl.id, reference, description, transdate, p.id, gl.notes
+      FROM sl28.gl 
+ LEFT JOIN sl28.employee em ON gl.employee_id = em.id
+ LEFT JOIN person p ON em.entity_id = p.id;
+
+ALTER TABLE gl ENABLE TRIGGER gl_audit_trail;
+
+ALTER TABLE ar DISABLE TRIGGER ar_audit_trail;
+
+insert into ar 
+(entity_credit_account, person_id,
+	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
+	shipvia, language_code, ponumber, shippingpoint, 
+	on_hold, approved, reverse, terms, description)
+SELECT 
+	customer.credit_id,
+	(select entity_id from sl28.employee 
+		WHERE id = ar.employee_id),
+	ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, ar.curr, ar.notes, quonumber, 
+	intnotes,
+	shipvia, ar.language_code, ponumber, shippingpoint, 
+	onhold, approved, case when amount < 0 then true else false end,
+	ar.terms, description
+FROM sl28.ar JOIN sl28.customer ON (ar.customer_id = customer.id) ;
+
+ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
+
+ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
+
+insert into ap 
+(entity_credit_account, person_id,
+	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
+        shipvia, language_code, ponumber, shippingpoint, 
+	on_hold, approved, reverse, terms, description)
+SELECT 
+	vendor.credit_id,
+	(select entity_id from sl28.employee 
+		WHERE id = ap.employee_id),
+	ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber, 
+	intnotes,
+	shipvia, ap.language_code, ponumber, shippingpoint, 
+	onhold, approved, case when amount < 0 then true else false end,
+	ap.terms, description
+FROM sl28.ap JOIN sl28.vendor ON (ap.vendor_id = vendor.id) ;
+
+ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
+
+-- ### TODO: there used to be projects here!
+-- ### Move those to business_units
+-- ### TODO: Reconciled disappeared from the source table...
+
+ALTER TABLE sl28.acc_trans ADD COLUMN entry_id integer;
+
+update sl28.acc_trans
+  set entry_id = nextval('acc_trans_entry_id_seq');
+
+INSERT INTO acc_trans
+(entry_id, trans_id, chart_id, amount, transdate, source, cleared, fx_transaction, 
+	memo, approved, cleared_on, voucher_id)
+SELECT entry_id, trans_id, (select id
+                    from account
+                   where accno = (select accno
+                                    from sl28.chart
+                                   where chart.id = acc_trans.chart_id)),
+                                    amount, transdate, source,
+	CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
+	memo, approved, cleared, vr_id
+	FROM sl28.acc_trans ;
+
+INSERT INTO business_unit_ac (entry_id, class_id, bu_id)
+SELECT ac.entry_id, 1, gl.department_id
+  FROM acc_trans ac 
+  JOIN (SELECT id, department_id FROM sl28.ar UNION ALL
+        SELECT id, department_id FROM sl28.ap UNION ALL
+        SELECT id, department_id FROM sl28.gl) gl ON gl.id = ac.trans_id 
+ WHERE department_id > 0;
+
+INSERT INTO business_unit_ac (entry_id, class_id, bu_id)
+SELECT ac.entry_id, 2, slac.project_id+1000
+  FROM acc_trans ac 
+  JOIN sl28.acc_trans slac ON slac.entry_id = ac.entry_id 
+ WHERE project_id > 0;
+
+
+ INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit,
+            deliverydate, serialnumber)
+    SELECT  id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit,
+            deliverydate, serialnumber
+       FROM sl28.invoice;
+
+INSERT INTO business_unit_inv (entry_id, class_id, bu_id)
+SELECT inv.id, 1, gl.department_id
+  FROM invoice inv 
+  JOIN (SELECT id, department_id FROM sl28.ar UNION ALL
+        SELECT id, department_id FROM sl28.ap UNION ALL
+        SELECT id, department_id FROM sl28.gl) gl ON gl.id = inv.trans_id
+ WHERE department_id > 0;
+
+INSERT INTO business_unit_inv (entry_id, class_id, bu_id)
+SELECT id, 2, project_id + 1000 FROM sl28.invoice 
+ WHERE project_id > 0 and  project_id in (select id from sl28.project);
+
+
+
+
+INSERT INTO partstax (parts_id, chart_id)
+     SELECT parts_id, a.id
+       FROM sl28.partstax pt
+       JOIN sl28.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT INTO tax(chart_id, rate, taxnumber, validto)
+     SELECT a.id, t.rate, t.taxnumber, 
+            coalesce(t.validto::timestamp, 'infinity')
+       FROM sl28.tax t
+       JOIN sl28.chart c ON (t.chart_id = c.id)
+       JOIN account a ON (a.accno = c.accno);
+
+
+-- ### TODO: Customer tax does not exist in LSMB. What is its function in SL??
+-- ###  LSMB moved these tables jointly to eca_tax
+-- INSERT INTO customertax (customer_id, chart_id)
+--      SELECT c.credit_id,  a.id
+--        FROM sl28.customertax pt
+--        JOIN sl28.customer c ON (pt.customer_id = c.id)
+--        JOIN sl28.chart ON chart.id = pt.chart_id
+--        JOIN account a ON chart.accno = a.accno; 
+
+-- INSERT INTO vendortax (vendor_id, chart_id)
+--      SELECT c.credit_id,  a.id
+--        FROM sl28.vendortax pt       
+--        JOIN sl28.vendor c ON (pt.vendor_id = c.id)
+--        JOIN sl28.chart ON chart.id = pt.chart_id
+--        JOIN account a ON chart.accno = a.accno;
+
+INSERT 
+  INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
+       shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
+       intnotes, shipvia, language_code, ponumber, terms,
+       entity_credit_account, oe_class_id)
+SELECT oe.id,  ordnumber, transdate, amount, netamount, reqdate, oe.taxincluded,
+       shippingpoint, oe.notes, oe.curr, p.id, closed, quotation, quonumber,
+       intnotes, shipvia, oe.language_code, ponumber, oe.terms,
+       coalesce(c.credit_id, v.credit_id),
+       case 
+           when c.id is not null and quotation is not true THEN 1
+           WHEN v.id is not null and quotation is not true THEN 2
+           when c.id is not null and quotation is true THEN 3
+           WHEN v.id is not null and quotation is true THEN 4
+       end
+  FROM sl28.oe
+  LEFT JOIN sl28.customer c ON c.id = oe.customer_id
+  LEFT JOIN sl28.vendor v ON v.id = oe.vendor_id
+  LEFT JOIN sl28.employee e ON oe.employee_id = e.id
+  LEFT JOIN person p ON e.entity_id = p.id;
+
+INSERT INTO orderitems(id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, reqdate, ship, serialnumber)
+     SELECT id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, reqdate, ship, serialnumber
+       FROM sl28.orderitems;
+
+INSERT INTO business_unit_oitem (entry_id, class_id, bu_id)
+SELECT oi.id, 1, oe.department_id 
+  FROM orderitems oi
+  JOIN sl28.oe ON oi.trans_id = oe.id AND department_id > 0;
+
+INSERT INTO business_unit_oitem (entry_id, class_id, bu_id)
+SELECT id, 2, project_id + 1000 FROM sl28.orderitems
+ WHERE project_id > 0  and  project_id in (select id from sl28.project);
+
+
+INSERT INTO exchangerate select * from sl28.exchangerate;
+
+-- ### TODO: Move to business units!
+-- INSERT INTO project (id, projectnumber, description, startdate, enddate,
+--             parts_id, production, completed, credit_id)
+--      SELECT p.id, projectnumber, description, p.startdate, p.enddate,
+--             parts_id, production, completed, c.credit_id
+--        FROM sl28.project p
+--        JOIN sl28.customer c ON p.customer_id = c.id;
+
+
+INSERT INTO status SELECT * FROM sl28.status; -- may need to comment this one out sometimes
+
+-- ### TODO: Move to business units!
+-- INSERT INTO department SELECT * FROM sl28.department;
+
+INSERT INTO business SELECT * FROM sl28.business;
+
+INSERT INTO sic SELECT * FROM sl28.sic;
+
+INSERT INTO warehouse SELECT * FROM sl28.warehouse;
+
+INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate)
+     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate
+       FROM sl28.inventory i
+       JOIN sl28.employee e ON i.employee_id = e.id;
+
+INSERT INTO yearend (trans_id, transdate) SELECT * FROM sl28.yearend;
+
+INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
+            curr)
+     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
+            pv.curr
+       FROM sl28.partsvendor pv
+       JOIN sl28.vendor v ON v.id = pv.vendor_id;
+
+INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, curr)
+     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, pv.curr
+       FROM sl28.partscustomer pv
+       JOIN sl28.customer v ON v.id = pv.customer_id;
+
+INSERT INTO language SELECT * FROM sl28.language;
+
+INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
+            transdate, person_id)
+     SELECT trans_id, tablename, reference, formname, action,
+            transdate, p.entity_id
+       FROM sl28.audittrail a
+       JOIN sl28.employee e ON a.employee_id = e.id
+       JOIN person p on e.entity_id = p.entity_id;
+
+INSERT INTO user_preference(id)
+     SELECT id from users;
+
+INSERT INTO recurring(id, reference, startdate, nextdate, enddate,
+            recurring_interval, howmany, payment)
+     SELECT id, reference, startdate, nextdate, enddate, 
+            (repeat || ' ' || unit)::interval,
+            howmany, payment 
+       FROM sl28.recurring;
+
+INSERT INTO recurringemail SELECT * FROM sl28.recurringemail;
+
+INSERT INTO recurringprint SELECT * FROM sl28.recurringprint;
+
+INSERT INTO jcitems(id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            person_id, notes, business_unit_id)
+     SELECT j.id,  parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            p.id, j.notes, j.project_id+1000
+       FROM sl28.jcitems j
+       JOIN sl28.employee e ON j.employee_id = e.id
+       JOIN person p ON e.entity_id = p.entity_id;
+
+INSERT INTO parts_translation SELECT * FROM sl28.translation where trans_id in (select id from parts);
+
+INSERT INTO partsgroup_translation SELECT * FROM sl28.translation where trans_id in
+ (select id from partsgroup);
+
+--  ### TODO: To translate to business_units
+-- INSERT INTO project_translation SELECT * FROM sl28.translation where trans_id in
+--  (select id from project);
+
+SELECT setval('id', max(id)) FROM transactions;
+
+ SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
+ SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
+ SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;
+ SELECT setval('partscustomer_entry_id_seq', max(entry_id)) FROM partscustomer;
+ SELECT setval('audittrail_entry_id_seq', max(entry_id)) FROM audittrail;
+ SELECT setval('account_id_seq', max(id)) FROM account;
+ SELECT setval('account_heading_id_seq', max(id)) FROM account_heading;
+ SELECT setval('account_checkpoint_id_seq', max(id)) FROM account_checkpoint;
+ SELECT setval('pricegroup_id_seq', max(id)) FROM pricegroup;
+ SELECT setval('country_id_seq', max(id)) FROM country;
+ SELECT setval('country_tax_form_id_seq', max(id)) FROM country_tax_form;
+ SELECT setval('asset_dep_method_id_seq', max(id)) FROM asset_dep_method;
+ SELECT setval('asset_class_id_seq', max(id)) FROM asset_class;
+ SELECT setval('entity_class_id_seq', max(id)) FROM entity_class;
+ SELECT setval('asset_item_id_seq', max(id)) FROM asset_item;
+ SELECT setval('asset_disposal_method_id_seq', max(id)) FROM asset_disposal_method;
+ SELECT setval('users_id_seq', max(id)) FROM users;
+ SELECT setval('entity_id_seq', max(id)) FROM entity;
+ SELECT setval('company_id_seq', max(id)) FROM company;
+ SELECT setval('location_id_seq', max(id)) FROM location;
+ SELECT setval('open_forms_id_seq', max(id)) FROM open_forms;
+ SELECT setval('location_class_id_seq', max(id)) FROM location_class;
+ SELECT setval('asset_report_id_seq', max(id)) FROM asset_report;
+ SELECT setval('salutation_id_seq', max(id)) FROM salutation;
+ SELECT setval('person_id_seq', max(id)) FROM person;
+ SELECT setval('contact_class_id_seq', max(id)) FROM contact_class;
+ SELECT setval('entity_credit_account_id_seq', max(id)) FROM entity_credit_account;
+ SELECT setval('entity_bank_account_id_seq', max(id)) FROM entity_bank_account;
+ SELECT setval('note_class_id_seq', max(id)) FROM note_class;
+ SELECT setval('note_id_seq', max(id)) FROM note;
+ SELECT setval('batch_class_id_seq', max(id)) FROM batch_class;
+ SELECT setval('batch_id_seq', max(id)) FROM batch;
+ SELECT setval('invoice_id_seq', max(id)) FROM invoice;
+ SELECT setval('voucher_id_seq', max(id)) FROM voucher;
+ SELECT setval('parts_id_seq', max(id)) FROM parts;
+ SELECT setval('taxmodule_taxmodule_id_seq', max(taxmodule_id)) FROM taxmodule;
+ SELECT setval('taxcategory_taxcategory_id_seq', max(taxcategory_id)) FROM taxcategory;
+ SELECT setval('oe_id_seq', max(id)) FROM oe;
+ SELECT setval('orderitems_id_seq', max(id)) FROM orderitems;
+ SELECT setval('business_id_seq', max(id)) FROM business;
+ SELECT setval('warehouse_id_seq', max(id)) FROM warehouse;
+ SELECT setval('partsgroup_id_seq', max(id)) FROM partsgroup;
+ SELECT setval('jcitems_id_seq', max(id)) FROM jcitems;
+ SELECT setval('payment_type_id_seq', max(id)) FROM payment_type;
+ SELECT setval('custom_table_catalog_table_id_seq', max(table_id)) FROM custom_table_catalog;
+ SELECT setval('custom_field_catalog_field_id_seq', max(field_id)) FROM custom_field_catalog;
+ SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
+ SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
+ SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
+-- SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
+ SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
+ SELECT setval('payment_id_seq', max(id)) FROM payment;
+ SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
+ SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
+ SELECT setval('business_unit_id_seq', max(id)) FROM business_unit;
+
+UPDATE defaults SET value = 'yes' where setting_key = 'migration_ok';
+
+COMMIT;
+--TODO:  Translation migratiion.  Partsgroups?
+-- TODO:  User/password Migration

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


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits