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

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



Revision: 4887
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4887&view=rev
Author:   einhverfr
Date:     2012-06-11 02:47:14 +0000 (Mon, 11 Jun 2012)
Log Message:
-----------
Initial 1.2-1.4 upgrade script.  Removing pre-1.3.0 svn snapshot upgrade scripts and rc related upgrade scripts

Added Paths:
-----------
    trunk/sql/upgrade/1.2-1.4.sql

Removed Paths:
-------------
    trunk/sql/upgrade/1.2-1.3.sql
    trunk/sql/upgrade/3308-menu_update.sql
    trunk/sql/upgrade/rc1-rc2.sql
    trunk/sql/upgrade/rc2-rc3.sql
    trunk/sql/upgrade/rc3-rc4.sql
    trunk/sql/upgrade/rc4-1.3.0.sql
    trunk/sql/upgrade/svn/3206-invoice_order.sql
    trunk/sql/upgrade/svn/3252-uniques.sql
    trunk/sql/upgrade/svn/3263-menu_generate.sql
    trunk/sql/upgrade/svn/3286-qty_type.sql
    trunk/sql/upgrade/svn/3290-tax-account.sql
    trunk/sql/upgrade/svn/3293-recon_payee.sql
    trunk/sql/upgrade/svn/3297-employee_changes.sql
    trunk/sql/upgrade/svn/3299-person_functions.sql
    trunk/sql/upgrade/svn/3302-menu_changes.sql
    trunk/sql/upgrade/svn/3308-menu_update.sql
    trunk/sql/upgrade/svn/3310-taxform-function.sql
    trunk/sql/upgrade/svn/3313-list_taxform_menu.sql
    trunk/sql/upgrade/svn/3313-role-updates.sql
    trunk/sql/upgrade/svn/3319-taxtable.-change.sql
    trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql
    trunk/sql/upgrade/svn/3328-drop-location_result.sql
    trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql
    trunk/sql/upgrade/svn/3333-location-function-drop.sql
    trunk/sql/upgrade/svn/3335-drop-location_saves.sql
    trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql
    trunk/sql/upgrade/svn/3355-yearend-fkey.sql
    trunk/sql/upgrade/svn/3356-fkeys.sql
    trunk/sql/upgrade/svn/3372-drop-location-delete.sql
    trunk/sql/upgrade/svn/3383-menu-attributes.sql
    trunk/sql/upgrade/svn/3386-drop-type.sql
    trunk/sql/upgrade/svn/3410-menu-functions.sql
    trunk/sql/upgrade/svn/3421-tax_tables.sql
    trunk/sql/upgrade/svn/3422-tax_tables.sql
    trunk/sql/upgrade/svn/3441-periods-view.sql
    trunk/sql/upgrade/svn/3445-drop-save-user.sql
    trunk/sql/upgrade/svn/3448-drop-location-search.sql
    trunk/sql/upgrade/svn/3450-drop-user-function.sql
    trunk/sql/upgrade/svn/3455-asset-schema.sql
    trunk/sql/upgrade/svn/3467-assets.sql
    trunk/sql/upgrade/svn/3470-asset-menu.sql
    trunk/sql/upgrade/svn/3476-drop-unused-fields.sql
    trunk/sql/upgrade/svn/3478-files.sql
    trunk/sql/upgrade/svn/3479-inheritance_pkeys.sql
    trunk/sql/upgrade/svn/3480-pkeys-2.sql
    trunk/sql/upgrade/svn/3482-file_view_catelog.sql
    trunk/sql/upgrade/svn/3495_drop_mime_func.sql
    trunk/sql/upgrade/svn/3497-schema-changes.sql
    trunk/sql/upgrade/svn/3509-drop-function.sql
    trunk/sql/upgrade/svn/3532-drop-used-function.sql
    trunk/sql/upgrade/svn/3534-drop_unused_function.sql
    trunk/sql/upgrade/svn/3536-menu-update.sql
    trunk/sql/upgrade/svn/3537-note-table.sql
    trunk/sql/upgrade/svn/3538-asset-menu-fix.sql
    trunk/sql/upgrade/svn/3547-constraints-upgrade.sql
    trunk/sql/upgrade/svn/3551-asset-approval-menu.sql
    trunk/sql/upgrade/svn/3560-settings.sql
    trunk/sql/upgrade/svn/3572-letterhead-template-editing.sql
    trunk/sql/upgrade/svn/3585-mime-type-data.sql
    trunk/sql/upgrade/svn/3594-drop-batch-update.sql
    trunk/sql/upgrade/svn/3613-drop-file_type.sql
    trunk/sql/upgrade/svn/3626-more-mime-types.sql
    trunk/sql/upgrade/svn/3637-dbversion-upgrade.sql
    trunk/sql/upgrade/svn/3640-new-file-type.sql
    trunk/sql/upgrade/svn/3679-drop-payment-function.sql
    trunk/sql/upgrade/svn/3686-batch_post.sql
    trunk/sql/upgrade/svn/3694-update_version.sql
    trunk/sql/upgrade/svn/3703-grant-select-perm.sql
    trunk/sql/upgrade/svn/3714-drop-recon-delete.sql
    trunk/sql/upgrade/svn/3738-drop-session-transaction-id.sql
    trunk/sql/upgrade/svn/3760-alter-asset_reports.sql
    trunk/sql/upgrade/svn/3764-control_code_index.sql
    trunk/sql/upgrade/svn/3808-language-table.sql

Deleted: trunk/sql/upgrade/1.2-1.3.sql
===================================================================
--- trunk/sql/upgrade/1.2-1.3.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/1.2-1.3.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,660 +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 ?>'''
-
-BEGIN;
-
--- adding mapping info for import.
-
-ALTER TABLE lsmb12.vendor ADD COLUMN entity_id int;
-ALTER TABLE lsmb12.vendor ADD COLUMN company_id int;
-ALTER TABLE lsmb12.vendor ADD COLUMN credit_id int;
-
-ALTER TABLE lsmb12.customer ADD COLUMN entity_id int;
-ALTER TABLE lsmb12.customer ADD COLUMN company_id int;
-ALTER TABLE lsmb12.customer ADD COLUMN credit_id int;
-
-
---Accounts
-INSERT INTO account_heading(id, accno, description)
-SELECT id, accno, description
-  FROM lsmb12.chart WHERE charttype = 'H';
-
-SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
-                    CASE WHEN link like '%tax%' THEN true ELSE false END, 
-                    string_to_array(link,':'))
-  FROM lsmb12.chart 
- WHERE charttype = 'A';
---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 lsmb12.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 lsmb12.customer
-GROUP BY name, customernumber;
-
-UPDATE lsmb12.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
-
-UPDATE lsmb12.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 = :ap), 
-	NULL, startdate, enddate, 0, 1
-FROM lsmb12.vendor WHERE entity_id IS NOT NULL;
-
-UPDATE lsmb12.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 = :ar),
-	NULL, startdate, enddate, 0, 2
-FROM lsmb12.customer WHERE entity_id IS NOT NULL;
-
-UPDATE lsmb12.customer SET credit_id = 
-	(SELECT id FROM entity_credit_account e 
-	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id and entity_class = 2);
-
---Company
-
-INSERT INTO company (entity_id, legal_name, tax_id)
-SELECT entity_id, name, max(taxnumber) FROM lsmb12.vendor 
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE lsmb12.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 lsmb12.customer
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 = '' 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
-     lsmb12.vendor 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 entity_credit_account eca ON (oa.credit_id = eca.id)
-GROUP BY eca.id;
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 1,
-    min(location_save(NULL,
-
-    case 
-        when oa.address1 = '' 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
-     lsmb12.customer 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 entity_credit_account eca ON (oa.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 = '' 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
-     lsmb12.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 lsmb12.vendor ov ON (oa.trans_id = ov.id)
-JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
-GROUP BY eca.id;
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 2,
-    min(location_save(NULL,
-
-    case 
-        when oa.shiptoaddress1 = '' 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
-     lsmb12.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 lsmb12.customer ov ON (oa.trans_id = ov.id)
-JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
-GROUP BY eca.id;
- 
-INSERT INTO eca_note(note_class, ref_key, note, vector)
-SELECT 3, credit_id, notes, '' FROM lsmb12.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 lsmb12.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 lsmb12.pricegroup;
-
-ALTER TABLE lsmb12.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 lsmb12.employee;
-
-UPDATE lsmb12.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 lsmb12.employee;
-
-INSERT INTO users (entity_id, username)
-     SELECT entity_id, login FROM lsmb12.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 lsmb12.employee where id = em.managerid)
-  FROM lsmb12.employee em;
-
-
-
--- must rebuild this table due to changes since 1.2
-
-INSERT INTO makemodel
-SELECT * FROM lsmb12.makemodel;
-
-INSERT INTO gifi
-SELECT * FROM lsmb12.gifi;
-
-UPDATE defaults 
-   SET value = (select value from lsmb12.defaults src 
-                 WHERE src.setting_key = defaults.setting_key)
- WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
-
-
-INSERT INTO parts SELECT * FROM lsmb12.parts;
-
-INSERT INTO assembly SELECT * FROM lsmb12.assembly;
-
-ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
-
-INSERT INTO gl(id, reference, description, transdate, person_id, notes, 
-               department_id)
-    SELECT gl.id, reference, description, transdate, p.id, gl.notes, 
-           department_id
-      FROM lsmb12.gl 
- LEFT JOIN lsmb12.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(id, invnumber, transdate, taxincluded, amount, 
-            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
-            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
-            department_id, shipvia, language_code, ponumber, 
-            entity_credit_account)
-     SELECT ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, 
-            paid, datepaid, duedate, invoice, shippingpoint, ar.terms, ar.notes,
-            ar.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
-            department_id, shipvia, ar.language_code, ponumber, credit_id
-       FROM lsmb12.ar
-       JOIN lsmb12.customer c ON c.id = ar.customer_id
-  LEFT JOIN lsmb12.employee em ON em.id = ar.employee_id;
-
-ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
-
-ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
-
-INSERT INTO ap(id, invnumber, transdate, taxincluded, amount, 
-            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
-            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
-            department_id, shipvia, language_code, ponumber, 
-            entity_credit_account)
-     SELECT ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, 
-            paid, datepaid, duedate, invoice, shippingpoint, ap.terms, ap.notes,
-            ap.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
-            department_id, shipvia, ap.language_code, ponumber, credit_id
-       FROM lsmb12.ap
-       JOIN lsmb12.vendor c ON c.id = ap.vendor_id
-  LEFT JOIN lsmb12.employee em ON em.id = ap.employee_id;
-
-ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
-
-INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, cleared,
-            fx_transaction, project_id, memo, invoice_id, entry_id)
-     SELECT trans_id, a.id, amount, transdate, source, cleared,
-            fx_transaction, project_id, memo, invoice_id, entry_id
-       FROM lsmb12.acc_trans
-       JOIN lsmb12.chart ON acc_trans.chart_id = chart.id
-       JOIN account a ON chart.accno = a.accno; 
-
-INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes)
-    SELECT  id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes
-       FROM lsmb12.invoice;
-
-INSERT INTO partstax (parts_id, chart_id)
-     SELECT parts_id, a.id
-       FROM lsmb12.partstax pt
-       JOIN lsmb12.chart ON chart.id = pt.chart_id
-       JOIN account a ON chart.accno = a.accno;
-
-INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
-     SELECT a.id, t.rate, t.taxnumber, 
-            coalesce(t.validto::timestamp, 'infinity'), pass, taxmodule_id
-       FROM lsmb12.tax t
-       JOIN lsmb12.chart c ON (t.chart_id = c.id)
-       JOIN account a ON (a.accno = c.accno);
-
-INSERT INTO customertax (customer_id, chart_id)
-     SELECT c.credit_id,  a.id
-       FROM lsmb12.customertax pt
-       JOIN lsmb12.customer c ON (pt.customer_id = c.id)
-       JOIN lsmb12.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 lsmb12.vendortax pt       
-       JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
-       JOIN lsmb12.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, department_id, 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, department_id, 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 lsmb12.oe
-  LEFT JOIN lsmb12.customer c ON c.id = oe.customer_id
-  LEFT JOIN lsmb12.vendor v ON v.id = oe.vendor_id
-  LEFT JOIN lsmb12.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, project_id, reqdate, ship, serialnumber, notes)
-     SELECT id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, project_id, reqdate, ship, serialnumber, notes
-       FROM lsmb12.orderitems;
-
-INSERT INTO exchangerate select * from lsmb12.exchangerate;
-
-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 lsmb12.project p
-       JOIN lsmb12.customer c ON p.customer_id = c.id;
-
-INSERT INTO partsgroup SELECT * FROM lsmb12.partsgroup;
-
-INSERT INTO status SELECT * FROM lsmb12.status;
-
-INSERT INTO department SELECT * FROM lsmb12.department;
-
-INSERT INTO business SELECT * FROM lsmb12.business;
-
-INSERT INTO sic SELECT * FROM lsmb12.sic;
-
-INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
-
-INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate, entry_id)
-     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate, i.entry_id
-       FROM lsmb12.inventory i
-       JOIN lsmb12.employee e ON i.employee_id = e.id;
-
-INSERT INTO yearend (trans_id, transdate) SELECT * FROM lsmb12.yearend;
-
-INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
-            curr, entry_id)
-     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
-            pv.curr, entry_id
-       FROM lsmb12.partsvendor pv
-       JOIN lsmb12.vendor v ON v.id = pv.vendor_id;
-
-INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, curr, entry_id)
-     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, pv.curr, entry_id
-       FROM lsmb12.partscustomer pv
-       JOIN lsmb12.customer v ON v.id = pv.customer_id;
-
-INSERT INTO language SELECT * FROM lsmb12.language;
-
-INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
-            transdate, person_id, entry_id)
-     SELECT trans_id, tablename, reference, formname, action,
-            transdate, p.entity_id, entry_id
-       FROM lsmb12.audittrail a
-       JOIN lsmb12.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 SELECT * FROM lsmb12.recurring;
-
-INSERT INTO recurringemail SELECT * FROM lsmb12.recurringemail;
-
-INSERT INTO recurringprint SELECT * FROM lsmb12.recurringprint;
-
-INSERT INTO jcitems(id, project_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            person_id, notes)
-     SELECT j.id,  project_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            p.id, j.notes
-       FROM lsmb12.jcitems j
-       JOIN lsmb12.employee e ON j.employee_id = e.id
-       JOIN person p ON e.entity_id = p.entity_id;
-
-INSERT INTO  custom_table_catalog  SELECT * FROM lsmb12. custom_table_catalog;
-
-INSERT INTO  custom_field_catalog  SELECT * FROM lsmb12. custom_field_catalog;
-
-INSERT INTO parts_translation SELECT * FROM lsmb12.translation where trans_id in (select id from parts);
-
-INSERT INTO partsgroup_translation SELECT * FROM lsmb12.translation where trans_id in
- (select id from partsgroup);
-
-INSERT INTO project_translation SELECT * FROM lsmb12.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('project_id_seq', max(id)) FROM project;
- SELECT setval('department_id_seq', max(id)) FROM department;
- 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;
-
-UPDATE defaults SET value = '1.3.0' WHERE setting_key = 'version';
-
-
-COMMIT;
---TODO:  Translation migratiion.  Partsgroups?
--- TODO:  User/password Migration

Copied: trunk/sql/upgrade/1.2-1.4.sql (from rev 4878, trunk/sql/upgrade/1.2-1.3.sql)
===================================================================
--- trunk/sql/upgrade/1.2-1.4.sql	                        (rev 0)
+++ trunk/sql/upgrade/1.2-1.4.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -0,0 +1,685 @@
+--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 ?>'''
+
+BEGIN;
+
+-- adding mapping info for import.
+
+ALTER TABLE lsmb12.vendor ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN company_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN credit_id int;
+
+ALTER TABLE lsmb12.customer ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN company_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN credit_id int;
+
+-- Buisness Reporting Units
+
+INSERT INTO business_unit (class_id, id, control_code, description
+     SELECT 1, id, role || id::text, description FROM lsmb12.department;
+
+
+--Accounts
+INSERT INTO account_heading(id, accno, description)
+SELECT id, accno, description
+  FROM lsmb12.chart WHERE charttype = 'H';
+
+SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
+                    CASE WHEN link like '%tax%' THEN true ELSE false END, 
+                    string_to_array(link,':'))
+  FROM lsmb12.chart 
+ WHERE charttype = 'A';
+--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 lsmb12.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 lsmb12.customer
+GROUP BY name, customernumber;
+
+UPDATE lsmb12.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
+
+UPDATE lsmb12.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 = :ap), 
+	NULL, startdate, enddate, 0, 1
+FROM lsmb12.vendor WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.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 = :ar),
+	NULL, startdate, enddate, 0, 2
+FROM lsmb12.customer WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.customer SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id and entity_class = 2);
+
+--Company
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM lsmb12.vendor 
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.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 lsmb12.customer
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 lsmb12.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 = '' 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
+     lsmb12.vendor 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 entity_credit_account eca ON (oa.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 = '' 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
+     lsmb12.customer 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 entity_credit_account eca ON (oa.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 = '' 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
+     lsmb12.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 lsmb12.vendor ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 = '' 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
+     lsmb12.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 lsmb12.customer ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+ 
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM lsmb12.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 lsmb12.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 lsmb12.pricegroup;
+
+ALTER TABLE lsmb12.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 lsmb12.employee;
+
+UPDATE lsmb12.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 lsmb12.employee;
+
+INSERT INTO users (entity_id, username)
+     SELECT entity_id, login FROM lsmb12.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 lsmb12.employee where id = em.managerid)
+  FROM lsmb12.employee em;
+
+
+
+-- must rebuild this table due to changes since 1.2
+
+INSERT INTO makemodel
+SELECT * FROM lsmb12.makemodel;
+
+INSERT INTO gifi
+SELECT * FROM lsmb12.gifi;
+
+UPDATE defaults 
+   SET value = (select value from lsmb12.defaults src 
+                 WHERE src.setting_key = defaults.setting_key)
+ WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
+
+
+INSERT INTO parts SELECT * FROM lsmb12.parts;
+
+INSERT INTO assembly SELECT * FROM lsmb12.assembly;
+
+ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
+
+INSERT INTO gl(id, reference, description, transdate, person_id, notes)
+    SELECT gl.id, reference, description, transdate, p.id, gl.notes
+      FROM lsmb12.gl 
+ LEFT JOIN lsmb12.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(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ar.terms, ar.notes,
+            ar.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            shipvia, ar.language_code, ponumber, credit_id
+       FROM lsmb12.ar
+       JOIN lsmb12.customer c ON c.id = ar.customer_id
+  LEFT JOIN lsmb12.employee em ON em.id = ar.employee_id;
+
+ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
+
+ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
+
+INSERT INTO ap(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ap.terms, ap.notes,
+            ap.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            shipvia, ap.language_code, ponumber, credit_id
+       FROM lsmb12.ap
+       JOIN lsmb12.vendor c ON c.id = ap.vendor_id
+  LEFT JOIN lsmb12.employee em ON em.id = ap.employee_id;
+
+ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
+
+INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, cleared,
+            fx_transaction, memo, invoice_id, entry_id)
+     SELECT trans_id, a.id, amount, transdate, source, cleared,
+            fx_transaction, memo, invoice_id, entry_id
+       FROM lsmb12.acc_trans
+       JOIN lsmb12.chart ON acc_trans.chart_id = chart.id
+       JOIN account a ON chart.accno = a.accno; 
+
+INSERT INTO business_unit_ac (entry_id, bu_class_id, bu_id) 
+     SELECT ac.entry_id, 1, gl.department_id
+       FROM acc_trans ac
+       JOIN (select id, department_id from gl
+              UNION
+             SELECT id, department_id FROM ar
+              UNION
+             SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+      UNION
+     SELECT ac.entry_id, 2, ac.project_id + 1000
+       FROM lsmb12.acc_trans ac;
+
+INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit,
+            deliverydate, serialnumber, notes)
+    SELECT  id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit,
+            deliverydate, serialnumber, notes
+       FROM lsmb12.invoice;
+
+INSERT INTO partstax (parts_id, chart_id)
+     SELECT parts_id, a.id
+       FROM lsmb12.partstax pt
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT INTO business_unit_inv (entry_id, bu_class_id, bu_id) 
+     SELECT inv.id, 1, gl.department_id
+       FROM invoice inv
+       JOIN (select id, department_id from gl
+              UNION
+             SELECT id, department_id FROM ar
+              UNION
+             SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+      UNION
+     SELECT inv.id, 2, ac.project_id + 1000
+       FROM lsmb12.invoice inv;
+
+INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
+     SELECT a.id, t.rate, t.taxnumber, 
+            coalesce(t.validto::timestamp, 'infinity'), pass, taxmodule_id
+       FROM lsmb12.tax t
+       JOIN lsmb12.chart c ON (t.chart_id = c.id)
+       JOIN account a ON (a.accno = c.accno);
+
+INSERT INTO customertax (customer_id, chart_id)
+     SELECT c.credit_id,  a.id
+       FROM lsmb12.customertax pt
+       JOIN lsmb12.customer c ON (pt.customer_id = c.id)
+       JOIN lsmb12.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 lsmb12.vendortax pt       
+       JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
+       JOIN lsmb12.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, department_id, 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, department_id, 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 lsmb12.oe
+  LEFT JOIN lsmb12.customer c ON c.id = oe.customer_id
+  LEFT JOIN lsmb12.vendor v ON v.id = oe.vendor_id
+  LEFT JOIN lsmb12.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, project_id, reqdate, ship, serialnumber, notes)
+     SELECT id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, project_id, reqdate, ship, serialnumber, notes
+       FROM lsmb12.orderitems;
+
+INSERT INTO exchangerate select * from lsmb12.exchangerate;
+
+INSERT INTO business_unit (id,control_code, description, startdate, enddate,
+            credit_id)
+     SELECT p.id, projectnumber, description, p.startdate, p.enddate,
+            c.credit_id
+       FROM lsmb12.project p
+       JOIN lsmb12.customer c ON p.customer_id = c.id;
+
+INSERT INTO partsgroup SELECT * FROM lsmb12.partsgroup;
+
+INSERT INTO status SELECT * FROM lsmb12.status;
+
+INSERT INTO business SELECT * FROM lsmb12.business;
+
+INSERT INTO sic SELECT * FROM lsmb12.sic;
+
+INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
+
+INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, entry_id)
+     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, i.entry_id
+       FROM lsmb12.inventory i
+       JOIN lsmb12.employee e ON i.employee_id = e.id;
+
+INSERT INTO yearend (trans_id, transdate) SELECT * FROM lsmb12.yearend;
+
+INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
+            curr, entry_id)
+     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
+            pv.curr, entry_id
+       FROM lsmb12.partsvendor pv
+       JOIN lsmb12.vendor v ON v.id = pv.vendor_id;
+
+INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, curr, entry_id)
+     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, pv.curr, entry_id
+       FROM lsmb12.partscustomer pv
+       JOIN lsmb12.customer v ON v.id = pv.customer_id;
+
+INSERT INTO language SELECT * FROM lsmb12.language;
+
+INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
+            transdate, person_id, entry_id)
+     SELECT trans_id, tablename, reference, formname, action,
+            transdate, p.entity_id, entry_id
+       FROM lsmb12.audittrail a
+       JOIN lsmb12.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 SELECT * FROM lsmb12.recurring;
+
+INSERT INTO recurringemail SELECT * FROM lsmb12.recurringemail;
+
+INSERT INTO recurringprint SELECT * FROM lsmb12.recurringprint;
+
+INSERT INTO jcitems(id, business_unit_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            person_id, notes)
+     SELECT j.id,  project_id + 1000, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            p.id, j.notes
+       FROM lsmb12.jcitems j
+       JOIN lsmb12.employee e ON j.employee_id = e.id
+       JOIN person p ON e.entity_id = p.entity_id;
+
+INSERT INTO  custom_table_catalog  SELECT * FROM lsmb12. custom_table_catalog;
+
+INSERT INTO  custom_field_catalog  SELECT * FROM lsmb12. custom_field_catalog;
+
+INSERT INTO parts_translation SELECT * FROM lsmb12.translation where trans_id in (select id from parts);
+
+INSERT INTO partsgroup_translation SELECT * FROM lsmb12.translation where trans_id in
+ (select id from partsgroup);
+
+INSERT INTO project_translation SELECT * FROM lsmb12.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('project_id_seq', max(id)) FROM project;
+ SELECT setval('department_id_seq', max(id)) FROM department;
+ 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;
+
+UPDATE defaults SET value = '1.3.0' WHERE setting_key = 'version';
+
+
+COMMIT;
+--TODO:  Translation migratiion.  Partsgroups?
+-- TODO:  User/password Migration

Deleted: trunk/sql/upgrade/3308-menu_update.sql
===================================================================
--- trunk/sql/upgrade/3308-menu_update.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/3308-menu_update.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,9 +0,0 @@
-
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_first_name', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_id', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_startdate', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_enddate', '1'); 

Deleted: trunk/sql/upgrade/rc1-rc2.sql
===================================================================
--- trunk/sql/upgrade/rc1-rc2.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc1-rc2.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-GRANT SELECT ON partsgroup_translation TO public;

Deleted: trunk/sql/upgrade/rc2-rc3.sql
===================================================================
--- trunk/sql/upgrade/rc2-rc3.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc2-rc3.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-DROP FUNCTION reconciliation__delete_report(in_report_id int);
-ALTER TABLE session DROP transaction_id;

Deleted: trunk/sql/upgrade/rc3-rc4.sql
===================================================================
--- trunk/sql/upgrade/rc3-rc4.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc3-rc4.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,5 +0,0 @@
-alter table asset_report alter column entered_by 
-set default person__get_my_entity_id();
-
-DROP TYPE asset_nbv_line CASCADE;
-ALTER TABLE entity ADD UNIQUE (control_code);

Deleted: trunk/sql/upgrade/rc4-1.3.0.sql
===================================================================
--- trunk/sql/upgrade/rc4-1.3.0.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc4-1.3.0.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,40 +0,0 @@
-INSERT INTO language (code, description)
-VALUES ('ar_EG', 'Arabic (Egypt)'),
-       ('bg',    'Bulgarian'), 
-       ('ca',    'Catalan'),
-       ('cs',    'Czech'),
-       ('da',    'Danish'),
-       ('de',    'German'),
-       ('de_CH', 'German (Switzerland)'),
-       ('el',    'Greek'),
-       ('en',    'English'),
-       ('es',    'Spanish'),
-       ('es_CO', 'Spanish (Colombia)'),
-       ('es_EC', 'Spanish (Ecuador)'),
-       ('es_MX', 'Spanish (Mexico)'),
-       ('es_PA', 'Spanish (Panama)'),
-       ('es_PY', 'Spanish (Paraguay)'),
-       ('es_VE', 'Spanish (Venezuela)'),
-       ('et',    'Estonian'),
-       ('fi',    'Finnish'),
-       ('fr',    'French'),
-       ('fr_BE', 'French (Belgium)'),
-       ('fr_CA', 'French (Canada)'),
-       ('hu',    'Hungarian'),
-       ('id',    'Indonesian'),
-       ('is',    'Icelandic'),
-       ('it',    'Italian'),
-       ('lt',    'Latvian'),
-       ('nb',    'Norwegian'),
-       ('nl',    'Dutch'),
-       ('nl_BE', 'Dutch (Belgium)'),
-       ('pl',    'Polish'),
-       ('pt',    'Portuguese'),
-       ('pt_BR', 'Portuguese (Brazil)'),
-       ('ru',    'Russian'),
-       ('sv',    'Swedish'),
-       ('tr',    'Turkish'),
-       ('uk',    'Ukranian'),
-       ('zh_CN', 'Chinese (China)'),
-       ('zh_TW', 'Chinese (Taiwan)');
-

Deleted: trunk/sql/upgrade/svn/3206-invoice_order.sql
===================================================================
--- trunk/sql/upgrade/svn/3206-invoice_order.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3206-invoice_order.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE invoice ADD precision int;
-ALTER TABLE orderitems ADD precision int;

Deleted: trunk/sql/upgrade/svn/3252-uniques.sql
===================================================================
--- trunk/sql/upgrade/svn/3252-uniques.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3252-uniques.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE ar ADD unique(invnumber);
-ALTER TABLE person ADD UNIQUE (entity_id);

Deleted: trunk/sql/upgrade/svn/3263-menu_generate.sql
===================================================================
--- trunk/sql/upgrade/svn/3263-menu_generate.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3263-menu_generate.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,118 +0,0 @@
-
-
-CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS 
-$$
-DECLARE 
-	item menu_item;
-	arg menu_attribute%ROWTYPE;
-BEGIN
-	FOR item IN 
-		SELECT n.position, n.id, c.level, n.label, c.path, 
-                       to_args(array[ma.attribute, ma.value])
-		FROM connectby('menu_node', 'id', 'parent', 'position', '0', 
-				0, ',') 
-			c(id integer, parent integer, "level" integer, 
-				path text, list_order integer)
-		JOIN menu_node n USING(id)
-                JOIN menu_attribute ma ON (n.id = ma.node_id)
-               WHERE n.id IN (select node_id FROM menu_acl
-                               WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                            GROUP BY node_id
-                              HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
-                                                   THEN FALSE
-                                                   WHEN acl_type ilike 'ALLOW'
-                                                   THEN TRUE
-                                                END))
-                    or exists (select cn.id, cc.path
-                                 FROM connectby('menu_node', 'id', 'parent', 
-                                                'position', '0', 0, ',')
-                                      cc(id integer, parent integer, 
-                                         "level" integer, path text,
-                                         list_order integer)
-                                 JOIN menu_node cn USING(id)
-                                WHERE cn.id IN 
-                                      (select node_id FROM menu_acl
-                                        WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                                     GROUP BY node_id
-                                       HAVING bool_and(CASE WHEN acl_type 
-                                                                 ilike 'DENY'
-                                                            THEN false
-                                                            WHEN acl_type 
-                                                                 ilike 'ALLOW'
-                                                            THEN TRUE
-                                                         END))
-                                       and cc.path like c.path || '%')
-            GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
-            ORDER BY c.list_order
-                             
-	LOOP
-		RETURN NEXT item;
-	END LOOP;
-END;
-$$ language plpgsql;
-
-CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
-AS $$
-declare 
-	item menu_item;
-	arg menu_attribute%ROWTYPE;
-begin
-        FOR item IN
-		SELECT n.position, n.id, c.level, n.label, c.path, 
-                       to_args(array[ma.attribute, ma.value])
-		FROM connectby('menu_node', 'id', 'parent', 'position', 
-				in_parent_id, 1, ',') 
-			c(id integer, parent integer, "level" integer, 
-				path text, list_order integer)
-		JOIN menu_node n USING(id)
-                JOIN menu_attribute ma ON (n.id = ma.node_id)
-               WHERE n.id IN (select node_id FROM menu_acl
-                               WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                            GROUP BY node_id
-                              HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
-                                                   THEN FALSE
-                                                   WHEN acl_type ilike 'ALLOW'
-                                                   THEN TRUE
-                                                END))
-                    or exists (select cn.id, cc.path
-                                 FROM connectby('menu_node', 'id', 'parent', 
-                                                'position', '0', 0, ',')
-                                      cc(id integer, parent integer, 
-                                         "level" integer, path text,
-                                         list_order integer)
-                                 JOIN menu_node cn USING(id)
-                                WHERE cn.id IN 
-                                      (select node_id FROM menu_acl
-                                        WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                                     GROUP BY node_id
-                                       HAVING bool_and(CASE WHEN acl_type 
-                                                                 ilike 'DENY'
-                                                            THEN false
-                                                            WHEN acl_type 
-                                                                 ilike 'ALLOW'
-                                                            THEN TRUE
-                                                         END))
-                                       and cc.path like c.path || '%')
-            GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
-            ORDER BY c.list_order
-        LOOP
-                return next item;
-        end loop;
-end;
-$$ language plpgsql;

Deleted: trunk/sql/upgrade/svn/3286-qty_type.sql
===================================================================
--- trunk/sql/upgrade/svn/3286-qty_type.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3286-qty_type.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-alter table invoice alter qty type numeric;

Deleted: trunk/sql/upgrade/svn/3290-tax-account.sql
===================================================================
--- trunk/sql/upgrade/svn/3290-tax-account.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3290-tax-account.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,107 +0,0 @@
-
-ALTER TABLE account ADD tax bool not null default false;
-
-UPDATE account
-   SET tax = true
- WHERE id IN (SELECT account_id
-              FROM account_link
-              WHERE description LIKE '%_tax'
-              UNION
-              SELECT chart_id
-              FROM tax);
-
-DROP FUNCTION account_save
-(in_id int, in_accno text, in_description text, in_category char(1),
-in_gifi_accno text, in_heading int, in_contra bool, in_link text[]);
-
-CREATE OR REPLACE FUNCTION account_save
-(in_id int, in_accno text, in_description text, in_category char(1),
-in_gifi_accno text, in_heading int, in_contra bool, in_tax bool,
-in_link text[])
-RETURNS int AS $$
-DECLARE
-        t_heading_id int;
-        t_link record;
-        t_id int;
-BEGIN
-        -- check to ensure summary accounts are exclusive
-        -- necessary for proper handling by legacy code
-    FOR t_link IN SELECT description FROM account_link_description
-    WHERE summary='t'
-        LOOP
-                IF t_link.description = ANY (in_link)
-		   and array_upper(in_link, 1) > 1 THEN
-                        RAISE EXCEPTION 'Invalid link settings:  Summary';
-                END IF;
-        END LOOP;
-        -- heading settings
-        IF in_heading IS NULL THEN
-                SELECT id INTO t_heading_id FROM account_heading
-                WHERE accno < in_accno order by accno desc limit 1;
-        ELSE
-                t_heading_id := in_heading;
-        END IF;
-
-    -- don't remove custom links.
-        DELETE FROM account_link
-        WHERE account_id = in_id
-              and description in ( select description
-                                    from  account_link_description
-                                    where custom = 'f');
-
-        UPDATE account
-        SET accno = in_accno,
-                description = in_description,
-                category = in_category,
-                gifi_accno = in_gifi_accno,
-                heading = t_heading_id,
-                contra = in_contra,
-                tax = in_tax
-        WHERE id = in_id;
-
-        IF FOUND THEN
-                t_id := in_id;
-        ELSE
-                INSERT INTO account (accno, description, category, gifi_accno,
-                        heading, contra, tax)
-                VALUES (in_accno, in_description, in_category, in_gifi_accno,
-                        t_heading_id, in_contra, in_tax);
-
-                t_id := currval('account_id_seq');
-        END IF;
-
-        FOR t_link IN
-                select in_link[generate_series] AS val
-                FROM generate_series(array_lower(in_link, 1),
-                        array_upper(in_link, 1))
-        LOOP
-                INSERT INTO account_link (account_id, description)
-                VALUES (t_id, t_link.val);
-        END LOOP;
-
-
-        RETURN t_id;
-END;
-$$ language plpgsql;
-
-
-DROP VIEW chart CASCADE;
-
-CREATE VIEW chart AS
-SELECT id, accno, description, 'H' as charttype, NULL as category, NULL as link, NULL as account_heading, null as gifi_accno, false as contra, false as tax from account_heading UNION
-select c.id, c.accno, c.description, 'A' as charttype, c.category, concat_colon(l.description) as link, heading, gifi_accno, contra, tax from account c left join account_link l ON (c.id = l.account_id) group by c.id, c.accno, c.description, c.category, c.heading, c.gifi_accno, c.contra, c.tax;
-
-GRANT SELECT ON chart TO public;
-
-CREATE OR REPLACE RULE chart_i AS ON INSERT TO chart
-DO INSTEAD
-SELECT CASE WHEN new.charttype='H' THEN 
- account_heading_save(new.id, new.accno, new.description, NULL)
-ELSE
- account_save(new.id, new.accno, new.description, new.category,
-  new.gifi_accno, NULL,
-  CASE WHEN new.contra IS NULL THEN FALSE ELSE new.contra END,
-  CASE WHEN new.tax IS NULL THEN FALSE ELSE new.tax END,
-  string_to_array(new.link, ':'))
-END;
-

Deleted: trunk/sql/upgrade/svn/3293-recon_payee.sql
===================================================================
--- trunk/sql/upgrade/svn/3293-recon_payee.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3293-recon_payee.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,32 +0,0 @@
-
-CREATE OR REPLACE VIEW recon_payee AS
- SELECT n.name AS payee, rr.id, rr.report_id, rr.scn, rr.their_balance, rr.our_balance, rr.errorcode, rr."user", rr.clear_time, rr.insert_time, rr.trans_type, rr.post_date, rr.ledger_id, rr.voucher_id, rr.overlook, rr.cleared
-   FROM cr_report_line rr
-   LEFT JOIN acc_trans ac ON rr.ledger_id = ac.entry_id
-   LEFT JOIN gl ON ac.trans_id = gl.id
-   LEFT JOIN (( SELECT ap.id, e.name
-   FROM ap
-   JOIN entity_credit_account eca ON ap.entity_credit_account = eca.id
-   JOIN entity e ON eca.entity_id = e.id
-UNION 
- SELECT ar.id, e.name
-   FROM ar
-   JOIN entity_credit_account eca ON ar.entity_credit_account = eca.id
-   JOIN entity e ON eca.entity_id = e.id)
-UNION 
- SELECT gl.id, gl.description
-   FROM gl) n ON n.id = ac.trans_id;
-
-
-CREATE OR REPLACE FUNCTION reconciliation__report_details_payee (in_report_id INT) RETURNS setof recon_payee as $$
-   DECLARE
-        row recon_payee;
-    BEGIN    
-        FOR row IN 
-        	select * from recon_payee where report_id = in_report_id 
-        	order by scn, post_date
-        LOOP
-          RETURN NEXT row;
-        END LOOP;    
-    END;
-$$ language 'plpgsql';
\ No newline at end of file

Deleted: trunk/sql/upgrade/svn/3297-employee_changes.sql
===================================================================
--- trunk/sql/upgrade/svn/3297-employee_changes.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3297-employee_changes.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,36 +0,0 @@
-drop function employee__get(int);
-
-CREATE TYPE employee_result AS (
-    entity_id int,
-    person_id int,
-    salutation text,
-    first_name text,
-    middle_name text,
-    last_name text,
-    startdate date,
-    enddate date,
-    role varchar(20),
-    ssn text,
-    sales bool,
-    manager_id int,
-    manager_first_name text,
-    manager_last_name text,
-    employeenumber varchar(32),
-    dob date
-);
-
-CREATE OR REPLACE FUNCTION employee__get
-(in_entity_id integer)
-returns employee_result as
-$$
-   SELECT p.entity_id, p.id, s.salutation,
-          p.first_name, p.middle_name, p.last_name,
-          ee.startdate, ee.enddate, ee.role, ee.ssn, ee.sales, ee.manager_id,
-          mp.first_name, mp.last_name, ee.employeenumber, ee.dob
-     FROM person p
-     JOIN entity_employee ee on (ee.entity_id = p.entity_id)
-LEFT JOIN salutation s on (p.salutation_id = s.id)
-LEFT JOIN person mp ON ee.manager_id = p.entity_id
-    WHERE p.entity_id = $1;
-$$ language sql;
-

Deleted: trunk/sql/upgrade/svn/3299-person_functions.sql
===================================================================
--- trunk/sql/upgrade/svn/3299-person_functions.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3299-person_functions.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,30 +0,0 @@
-CREATE OR REPLACE FUNCTION person__list_bank_account(in_entity_id int)
-RETURNS SETOF entity_bank_account AS
-$$
-DECLARE out_row entity_bank_account%ROWTYPE;
-BEGIN
-        FOR out_row IN
-                SELECT * from entity_bank_account where entity_id = in_entity_id
-        LOOP    
-                RETURN NEXT out_row;
-        END LOOP;
-END;            
-$$ LANGUAGE PLPGSQL;
-
-CREATE OR REPLACE FUNCTION person__list_notes(in_entity_id int)
-RETURNS SETOF entity_note AS
-$$
-DECLARE out_row record;
-BEGIN
-        FOR out_row IN
-                SELECT *
-                FROM entity_note
-                WHERE ref_key = in_entity_id
-                ORDER BY created
-        LOOP
-                RETURN NEXT out_row;
-        END LOOP;
-END;
-$$ LANGUAGE PLPGSQL;
-
-

Deleted: trunk/sql/upgrade/svn/3302-menu_changes.sql
===================================================================
--- trunk/sql/upgrade/svn/3302-menu_changes.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3302-menu_changes.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,18 +0,0 @@
-update menu_attribute 
-   set node_id = 49,
-       attribute = 'l_last_name',
-       value = '1'
- WHERE id = 115;
-update menu_attribute 
-   set node_id = 49,
-       attribute = 'l_employeenumber',
-       value = '1'
- WHERE id = 116;
-update menu_attribute 
-   set node_id = 49,
-       attribute = 'module',
-       value = 'employee.pl'
- WHERE id = 118;
-
-
-

Deleted: trunk/sql/upgrade/svn/3308-menu_update.sql
===================================================================
--- trunk/sql/upgrade/svn/3308-menu_update.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3308-menu_update.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,9 +0,0 @@
-
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_first_name', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_id', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_startdate', '1'); 
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_enddate', '1'); 

Deleted: trunk/sql/upgrade/svn/3310-taxform-function.sql
===================================================================
--- trunk/sql/upgrade/svn/3310-taxform-function.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3310-taxform-function.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,3 +0,0 @@
-DROP FUNCTION tax_form__save(in_country_id int, 
-                          in_form_name text, in_default_reportable bool);
-

Deleted: trunk/sql/upgrade/svn/3313-list_taxform_menu.sql
===================================================================
--- trunk/sql/upgrade/svn/3313-list_taxform_menu.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3313-list_taxform_menu.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,11 +0,0 @@
-SELECT menu_insert(217, 2, 'List Tax Forms');
-INSERT INTO menu_attribute(node_id, attribute, value)
-    VALUES (currval('menu_node_id_seq')::int, 'module', 'taxform.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-    VALUES (currval('menu_node_id_seq')::int, 'action', 'list_all');
-
-SELECT menu_insert(217, 3, 'Reports');
-INSERT INTO menu_attribute(node_id, attribute, value)
-    VALUES (currval('menu_node_id_seq')::int, 'module', 'taxform.pl');
-
-

Deleted: trunk/sql/upgrade/svn/3313-role-updates.sql
===================================================================
--- trunk/sql/upgrade/svn/3313-role-updates.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3313-role-updates.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,4 +0,0 @@
-
-INSERT INTO menu_acl (node_id, acl_type, role_name)
-SELECT id, 'allow', 'lsmb_' || current_database() ||'__tax_form_save'
-  FROM menu_node WHERE parent = 217 and position in (2,3);

Deleted: trunk/sql/upgrade/svn/3319-taxtable.-change.sql
===================================================================
--- trunk/sql/upgrade/svn/3319-taxtable.-change.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3319-taxtable.-change.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-ALTER TABLE tax alter column validto set not null;

Deleted: trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql
===================================================================
--- trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,53 +0,0 @@
-
-
-CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-new_field_name ALIAS FOR $2;
-field_datatype ALIAS FOR $3;
-
-BEGIN
-	perform TABLE_ID FROM custom_table_catalog 
-		WHERE extends = table_name;
-	IF NOT FOUND THEN
-		BEGIN
-			INSERT INTO custom_table_catalog (extends) 
-				VALUES (table_name);
-			EXECUTE ''CREATE TABLE '' || 
-                               quote_ident(''custom_'' ||table_name) ||
-				'' (row_id INT PRIMARY KEY)'';
-		EXCEPTION WHEN duplicate_table THEN
-			-- do nothing
-		END;
-	END IF;
-	INSERT INTO custom_field_catalog (field_name, table_id)
-	values (new_field_name, (SELECT table_id 
-                                        FROM custom_table_catalog
-		WHERE extends = table_name));
-	EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) || 
-                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' || 
-                  quote_ident(field_datatype);
-	RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-custom_field_name ALIAS FOR $2;
-BEGIN
-	DELETE FROM custom_field_catalog 
-	WHERE field_name = custom_field_name AND 
-		table_id = (SELECT table_id FROM custom_table_catalog 
-			WHERE extends = table_name);
-	EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) || 
-		'' DROP COLUMN '' || quote_ident(custom_field_name);
-	RETURN TRUE;	
-END;
-' LANGUAGE PLPGSQL;
--- end function

Deleted: trunk/sql/upgrade/svn/3328-drop-location_result.sql
===================================================================
--- trunk/sql/upgrade/svn/3328-drop-location_result.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3328-drop-location_result.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-drop type location_result cascade;

Deleted: trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql
===================================================================
--- trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,6 +0,0 @@
-DROP FUNCTION eca__save_bank_account
-(in_entity_id int, in_credit_id int, in_bic text, in_iban text);
-
-DROP FUNCTION entity__save_bank_account 
-(in_entity_id int, in_bic text, in_iban text);
-

Deleted: trunk/sql/upgrade/svn/3333-location-function-drop.sql
===================================================================
Deleted: trunk/sql/upgrade/svn/3335-drop-location_saves.sql
===================================================================
--- trunk/sql/upgrade/svn/3335-drop-location_saves.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3335-drop-location_saves.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,17 +0,0 @@
-DROP function eca__location_save(
-    in_credit_id int, in_location_id int,
-    in_location_class int, in_line_one text, in_line_two text,
-    in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
-    in_country_code int);
-
-DROP function person__save_location(
-    in_entity_id int,
-    in_location_id int,
-    in_location_class int,
-    in_line_one text,
-    in_line_two text,
-    in_line_three text,
-    in_city TEXT,
-    in_state TEXT,
-    in_mail_code text,
-    in_country_code int);

Deleted: trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql
===================================================================
--- trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE assembly ADD foreign key (id) REFERENCES parts(id);
-ALTER TABLE assembly ADD foreign key (parts_id) REFERENCES parts(id);

Deleted: trunk/sql/upgrade/svn/3355-yearend-fkey.sql
===================================================================
--- trunk/sql/upgrade/svn/3355-yearend-fkey.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3355-yearend-fkey.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-ALTER TABLE yearend ADD FOREIGN KEY (trans_id) REFERENCES gl(id);

Deleted: trunk/sql/upgrade/svn/3356-fkeys.sql
===================================================================
--- trunk/sql/upgrade/svn/3356-fkeys.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3356-fkeys.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,11 +0,0 @@
-ALTER TABLE partscustomer ALTER COLUMN credit_id drop not null;
-
-ALTER TABLE partscustomer 
-ADD foreign key (pricegroup_id) references pricegroup(id);
-
-CREATE TABLE parts_translation () INHERITS (translation);
-ALTER TABLE parts_translation ADD foreign key (trans_id) REFERENCES parts(id);
-    
-CREATE TABLE project_translation () INHERITS (translation);
-ALTER TABLE project_translation 
-ADD foreign key (trans_id) REFERENCES project(id);

Deleted: trunk/sql/upgrade/svn/3372-drop-location-delete.sql
===================================================================
--- trunk/sql/upgrade/svn/3372-drop-location-delete.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3372-drop-location-delete.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP FUNCTION person__delete_location (in_entity_id INT, in_location_id INT);

Deleted: trunk/sql/upgrade/svn/3383-menu-attributes.sql
===================================================================
--- trunk/sql/upgrade/svn/3383-menu-attributes.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3383-menu-attributes.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,8 +0,0 @@
-insert into menu_attribute(node_id, attribute, value) 
-values (87, 'module', 'ic.pl');
-
-insert into menu_attribute(node_id, attribute, value) 
-values (86, 'searchitems', 'all');
-
-insert into menu_attribute(node_id, attribute, value) 
-values (86, 'action', 'search');

Deleted: trunk/sql/upgrade/svn/3386-drop-type.sql
===================================================================
--- trunk/sql/upgrade/svn/3386-drop-type.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3386-drop-type.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP TYPE tax_form_report_detail_item CASCADE;

Deleted: trunk/sql/upgrade/svn/3410-menu-functions.sql
===================================================================
--- trunk/sql/upgrade/svn/3410-menu-functions.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3410-menu-functions.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,117 +0,0 @@
-CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS 
-$$
-DECLARE 
-	item menu_item;
-	arg menu_attribute%ROWTYPE;
-BEGIN
-	FOR item IN 
-		SELECT n.position, n.id, c.level, n.label, c.path, 
-                       to_args(array[ma.attribute, ma.value])
-		FROM connectby('menu_node', 'id', 'parent', 'position', '0', 
-				0, ',') 
-			c(id integer, parent integer, "level" integer, 
-				path text, list_order integer)
-		JOIN menu_node n USING(id)
-                JOIN menu_attribute ma ON (n.id = ma.node_id)
-               WHERE n.id IN (select node_id FROM menu_acl
-                               WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                            GROUP BY node_id
-                              HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
-                                                   THEN FALSE
-                                                   WHEN acl_type ilike 'ALLOW'
-                                                   THEN TRUE
-                                                END))
-                    or exists (select cn.id, cc.path
-                                 FROM connectby('menu_node', 'id', 'parent', 
-                                                'position', '0', 0, ',')
-                                      cc(id integer, parent integer, 
-                                         "level" integer, path text,
-                                         list_order integer)
-                                 JOIN menu_node cn USING(id)
-                                WHERE cn.id IN 
-                                      (select node_id FROM menu_acl
-                                        WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                                     GROUP BY node_id
-                                       HAVING bool_and(CASE WHEN acl_type 
-                                                                 ilike 'DENY'
-                                                            THEN false
-                                                            WHEN acl_type 
-                                                                 ilike 'ALLOW'
-                                                            THEN TRUE
-                                                         END))
-                                       and cc.path like c.path || ',%')
-            GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
-            ORDER BY c.list_order
-                             
-	LOOP
-		RETURN NEXT item;
-	END LOOP;
-END;
-$$ language plpgsql;
-
-CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
-AS $$
-declare 
-	item menu_item;
-	arg menu_attribute%ROWTYPE;
-begin
-        FOR item IN
-		SELECT n.position, n.id, c.level, n.label, c.path, 
-                       to_args(array[ma.attribute, ma.value])
-		FROM connectby('menu_node', 'id', 'parent', 'position', 
-				in_parent_id, 1, ',') 
-			c(id integer, parent integer, "level" integer, 
-				path text, list_order integer)
-		JOIN menu_node n USING(id)
-                JOIN menu_attribute ma ON (n.id = ma.node_id)
-               WHERE n.id IN (select node_id FROM menu_acl
-                               WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                            GROUP BY node_id
-                              HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
-                                                   THEN FALSE
-                                                   WHEN acl_type ilike 'ALLOW'
-                                                   THEN TRUE
-                                                END))
-                    or exists (select cn.id, cc.path
-                                 FROM connectby('menu_node', 'id', 'parent', 
-                                                'position', '0', 0, ',')
-                                      cc(id integer, parent integer, 
-                                         "level" integer, path text,
-                                         list_order integer)
-                                 JOIN menu_node cn USING(id)
-                                WHERE cn.id IN 
-                                      (select node_id FROM menu_acl
-                                        WHERE pg_has_role(CASE WHEN role_name 
-                                                           ilike 'public'
-                                                      THEN current_user
-                                                      ELSE role_name
-                                                   END, 'USAGE')
-                                     GROUP BY node_id
-                                       HAVING bool_and(CASE WHEN acl_type 
-                                                                 ilike 'DENY'
-                                                            THEN false
-                                                            WHEN acl_type 
-                                                                 ilike 'ALLOW'
-                                                            THEN TRUE
-                                                         END))
-                                       and cc.path like c.path || ',%')
-            GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
-            ORDER BY c.list_order
-        LOOP
-                return next item;
-        end loop;
-end;
-$$ language plpgsql;
-

Deleted: trunk/sql/upgrade/svn/3421-tax_tables.sql
===================================================================
--- trunk/sql/upgrade/svn/3421-tax_tables.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3421-tax_tables.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,13 +0,0 @@
-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.$$;
-

Deleted: trunk/sql/upgrade/svn/3422-tax_tables.sql
===================================================================
--- trunk/sql/upgrade/svn/3422-tax_tables.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3422-tax_tables.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,7 +0,0 @@
-ALTER TABLE tax_extended DROP COLUMN account_id;
-ALTER TABLE tax_extended DROP COLUMN tx_id;
-ALTER TABLE tax_extended DROP COLUMN reference;
-ALTER TABLE tax_extended DROP COLUMN tax_amount;
-ALTER TABLE tax_extended ADD entry_id int primary key;
-ALTER TABLE tax_extended 
-ADD FOREIGN KEY(entry_id) references acc_trans(entry_id);

Deleted: trunk/sql/upgrade/svn/3441-periods-view.sql
===================================================================
--- trunk/sql/upgrade/svn/3441-periods-view.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3441-periods-view.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,12 +0,0 @@
-
-CREATE OR REPLACE VIEW periods AS
-SELECT 'ytd' as id, 'Year to Date' as label, now()::date as date_to, 
-       (extract('year' from now())::text || '-01-01')::date as date_from
-UNION
-SELECT 'last_year', 'Last Year', 
-       ((extract('YEAR' from now()) - 1)::text || '-12-31')::date as date_to,
-       ((extract('YEAR' from now()) - 1)::text || '-01-01')::date as date_from
-;
-
-GRANT SELECT ON periods TO public;
-

Deleted: trunk/sql/upgrade/svn/3445-drop-save-user.sql
===================================================================
--- trunk/sql/upgrade/svn/3445-drop-save-user.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3445-drop-save-user.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-DROP FUNCTION admin__save_user
-(in_id int,in_entity_id INT, in_username text, in_password TEXT);

Deleted: trunk/sql/upgrade/svn/3448-drop-location-search.sql
===================================================================
--- trunk/sql/upgrade/svn/3448-drop-location-search.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3448-drop-location-search.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,7 +0,0 @@
-DROP FUNCTION location_search
-(in_companyname varchar, in_address1 varchar, in_address2 varchar,
-        in_city varchar, in_state varchar, in_zipcode varchar,
-        in_country varchar);
-
-DROP TYPE location_result CASCADE;
-

Deleted: trunk/sql/upgrade/svn/3450-drop-user-function.sql
===================================================================
--- trunk/sql/upgrade/svn/3450-drop-user-function.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3450-drop-user-function.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP FUNCTION admin__delete_user (in_username TEXT);

Deleted: trunk/sql/upgrade/svn/3455-asset-schema.sql
===================================================================
--- trunk/sql/upgrade/svn/3455-asset-schema.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3455-asset-schema.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,135 +0,0 @@
-
-CREATE TABLE asset_unit_class (
-	id int not null unique,
-	class text primary key
-);
-
-INSERT INTO asset_unit_class (id, class) values (1, 'time');
-INSERT INTO asset_unit_class (id, class) values (2, 'production'); 
--- production-based depreciation is unlikely to be supported initially
-
-CREATE TABLE asset_dep_method(
-	id serial unique not null,
-	method text primary key,
-        sproc text not null unique,
-        unit_label text not null,
-        short_name text not null unique,
-	unit_class int not null references asset_unit_class(id) 
-);
-
-comment on column asset_dep_method.method IS 
-$$ These are keyed to specific stored procedures.  Currently only "straight_line" is supported$$;
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name) 
-values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_d', 'in years', 'SLYD');
-
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name) 
-values ('Whole Month Straight Line', 1, 'asset_dep_straight_line_whl_m', 
-'in months', 'SLMM');
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name) 
-values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_m', 'in years', 'SLYM');
-
-CREATE TABLE asset_class (
-	id serial not null unique,
-	label text primary key,
-	asset_account_id int references account(id),
-	dep_account_id int references account(id),
-	method int references asset_dep_method(id)
-);
-
-COMMENT ON TABLE asset_class IS $$
-The account fields here set the defaults for the individual asset items.  They
-are non-authoritative.
-$$;
-
-CREATE TABLE asset_disposal_method (
-       label text primary key,
-       id serial unique,
-       multiple int check (multiple in (1, 0, -1)),
-       short_label char(1)
-);
-
-INSERT INTO asset_disposal_method (label, multiple, short_label)
-values ('Abandonment', '0', 'A');
-INSERT INTO asset_disposal_method (label, multiple, short_label)
-values ('Sale', '1', 'S');
-
-CREATE TABLE asset_rl_to_disposal_method (
-       report_id int references asset_report(id),
-       asset_id int references asset_item(id),
-       disposal_method_id int references asset_disposal_method(id),
-       percent_disposed numeric,
-       primary key (report_id, asset_id, disposal_method_id)
-);
-
-CREATE TABLE asset_item (
-	id serial primary key, -- needed due to possible null in natural key
-	description text,
-	tag text not null,
-	purchase_value numeric,
-	salvage_value numeric,
-	usable_life numeric,
-	purchase_date date  not null,
-        start_depreciation date not null,
-	location_id int references warehouse(id),
-	department_id int references department(id),
-	invoice_id int references ap(id),
-	asset_account_id int references account(id),
-	dep_account_id int references account(id),
-	exp_account_id int references account(id),
-        obsolete_by int references asset_item(id),
-	asset_class_id int references asset_class(id),
-        unique (tag, obsolete_by) -- part 1 of natural key enforcement
-);
-
-CREATE UNIQUE INDEX asset_item_active_tag_u ON asset_item(tag) 
-              WHERE obsolete_by is null; -- part 2 of natural key enforcement
-
-COMMENT ON column asset_item.tag IS $$ This can be plugged into other routines to generate it automatically via ALTER TABLE .... SET DEFAULT.....$$;
-
-CREATE TABLE asset_note (
-    foreign key (ref_key) references asset_item(id),
-    check (note_class = 4)
-) inherits (note);
-
-INSERT INTO note_class (id, class) values (4, 'Asset');
-ALTER TABLE asset_note alter column note_class set default 4;
-
-CREATE TABLE asset_report_class (
-	id int not null unique,
-	class text primary key
-);
-
-INSERT INTO asset_report_class (id, class) values (1, 'depreciation');
-INSERT INTO asset_report_class (id, class) values (2, 'disposal');
-INSERT INTO asset_report_class (id, class) values (3, 'import');
-INSERT INTO asset_report_class (id, class) values (4, 'partial disposal');
-
-CREATE TABLE asset_report (
-	id serial primary key,
-	report_date date,
-	gl_id bigint references gl(id) unique,
-	asset_class bigint references asset_class(id),
-	report_class int references asset_report_class(id),
-	entered_by bigint not null references entity(id),
-	approved_by bigint references entity(id),
-	entered_at timestamp default now(),
-	approved_at timestamp,
-	depreciated_qty numeric,
-        dont_approve bool default false,
-	submitted bool not null default false
-);
-
-CREATE TABLE asset_report_line(
-	asset_id bigint references asset_item(id),
-        report_id bigint references asset_report(id),
-	amount numeric,
-	department_id int references department(id),
-	warehouse_id int references warehouse(id),
-	PRIMARY KEY(asset_id, report_id)
-);
-
-COMMENT ON COLUMN asset_report_line.department_id IS
-$$ In case assets are moved between departments, we have to store this here.$$;

Deleted: trunk/sql/upgrade/svn/3467-assets.sql
===================================================================
--- trunk/sql/upgrade/svn/3467-assets.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3467-assets.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,10 +0,0 @@
-BEGIN;
-INSERT INTO account_link_description  (description, summary, custom)
-VALUES 
-('Asset_Dep',            FALSE, FALSE),
-('Fixed_Asset',          FALSE, FALSE),
-('asset_expense',        FALSE, FALSE),
-('asset_gain',           FALSE, FALSE),
-('asset_loss',           FALSE, FALSE);
-
-

Deleted: trunk/sql/upgrade/svn/3470-asset-menu.sql
===================================================================
--- trunk/sql/upgrade/svn/3470-asset-menu.sql	2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3470-asset-menu.sql	2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,87 +0,0 @@
-BEGIN;
-
-CREATE TEMPORARY TABLE menu_track (token text, node_id int);
-
-INSERT INTO menu_track(node_id, token)
-values (menu_insert(0, 17, 'Fixed Assets'), 'asset_top');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-INSERT INTO menu_track values ('asset_class', menu_insert((SELECT node_id from menu_track where token = 'asset_top'), 1, 'Asset Classes'));
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-INSERT INTO menu_track values ('asset_item', menu_insert((SELECT node_id from menu_track where token = 'asset_top'), 2, 'Assets'));
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_class'), 1, 'Add Class');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_category_screen');
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_class'), 2, 'List Classes');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_category_search');
-
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_item'), 1, 'Add Assets');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_screen');
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_items'), 2, 'Search Assets');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_search');
-
-SELECT menu_insert(
-       (SELECT id FROM menu_node WHERE parent = (select id from menu_node
-                                                 where position = 17 
-                                                       and parent = 0)
-                  AND position=2), 3, 'Depreciate');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'module', 'asset.pl');
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'action', 'new_report');
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'depreciate', '1');
-
-SELECT menu_insert(
-    (select id FROM menu_node 
-      where parent = (select id from menu_node 
-                       where parent = 0 and position = 17 
-                             and label = 'Fixed Assets')
-            and position = 2), 
-    2, 'Import');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'module', 'asset.pl');
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'action', 'import');
-
-SELECT menu_insert(
-       (SELECT id FROM menu_node WHERE parent = (select id from menu_node
-                                                 where position = 17
-                                                       and parent = 0)
-                  AND position=2), 10, 'Reports');
-

@@ Diff output truncated at 100000 characters. @@
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.