[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5654] branches/1.3/sql/upgrade/sl2.8-1.3.sql
- Subject: SF.net SVN: ledger-smb:[5654] branches/1.3/sql/upgrade/sl2.8-1.3.sql
- From: ..hidden..
- Date: Mon, 11 Feb 2013 11:21:15 +0000
Revision: 5654
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5654&view=rev
Author: einhverfr
Date: 2013-02-11 11:21:15 +0000 (Mon, 11 Feb 2013)
Log Message:
-----------
fixing some issues with SL -> LSMB migration
Modified Paths:
--------------
branches/1.3/sql/upgrade/sl2.8-1.3.sql
Modified: branches/1.3/sql/upgrade/sl2.8-1.3.sql
===================================================================
--- branches/1.3/sql/upgrade/sl2.8-1.3.sql 2013-02-08 04:01:38 UTC (rev 5653)
+++ branches/1.3/sql/upgrade/sl2.8-1.3.sql 2013-02-11 11:21:15 UTC (rev 5654)
@@ -53,9 +53,12 @@
INSERT INTO entity_credit_account
(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id,
cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, vendornumber, business_id, creditlimit, arap_accno_id,
- payment_accno_id, startdate, enddate, threshold, 1
-FROM orig.vendor WHERE entity_id IS NOT NULL);
+SELECT entity_id, vendornumber, business_id, creditlimit, :ap,
+ a.id, startdate, enddate, threshold, 1
+FROM sl28.vendor
+LEFT JOIN sl28.chart c on payment_accno_id = c.id
+LEFT JOIN account a on c.accno = a.accno
+ WHERE entity_id IS NOT NULL;
UPDATE sl28.vendor SET credit_id =
(SELECT id FROM entity_credit_account e
@@ -65,14 +68,17 @@
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, arap_accno_id,
- payment_accno_id, startdate, enddate, threshold, 2
-FROM orig.customer WHERE entity_id IS NOT NULL);
+SELECT entity_id, customernumber, business_id, creditlimit, :ar,
+ a.id, startdate, enddate, threshold, 2
+FROM sl28.customer
+LEFT JOIN sl28.chart c on payment_accno_id = c.id
+LEFT JOIN account a on c.accno = a.accno
+ WHERE entity_id IS NOT NULL;
UPDATE sl28.customer SET credit_id =
(SELECT id FROM entity_credit_account e
- WHERE e.meta_number = vendornumber and entity_class = 2
- and e.entity_id = vendor.entity_id);
+ WHERE e.meta_number = customernumber and entity_class = 2
+ and e.entity_id = customer.entity_id);
--Company
@@ -195,10 +201,10 @@
OR
lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN (select credit_id, id from sl28.vendor
+ union
+ select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
JOIN entity_credit_account eca ON (v.credit_id = eca.id)
-JOIN (select credit_id, id from vendor
- union
- select credit_id, id from customer) v ON oa.trans_id = v.id
GROUP BY eca.id;
-- Shipto
@@ -239,9 +245,9 @@
OR
lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
-JOIN (select credit_id, id from vendor
+JOIN (select credit_id, id from sl28.vendor
union
- select credit_id, id from customer) ov ON oa.trans_id = v.id
+ select credit_id, id from sl28.customer) ov ON oa.trans_id = ov.id
JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
GROUP BY eca.id;
@@ -297,7 +303,30 @@
FROM sl28.employee em;
+insert into parts
+ (id, partnumber, description, unit, listprice,
+ sellprice, lastcost, priceupdate, weight, onhand,
+ notes, makemodel, assembly, alternate, rop,
+ bin, obsolete, bom, image, drawing,
+ microfiche, partsgroup_id, project_id, avgcost,
+ inventory_accno_id, income_accno_id, expense_accno_id)
+SELECT p.id, partnumber, p.description, unit, listprice,
+ sellprice, lastcost, priceupdate, weight, onhand,
+ notes, makemodel, assembly, alternate, rop,
+ bin, obsolete, bom, image, drawing,
+ microfiche, partsgroup_id, project_id, avgcost,
+ inv.id, inc.id, exp.id
+ FROM sl28.parts p
+ LEFT JOIN sl28.chart cinv ON inventory_accno_id = cinv.id
+ LEFT JOIN account inv ON cinv.accno = inv.accno
+ LEFT JOIN sl28.chart cinc ON income_accno_id = cinc.id
+ LEFT JOIN account inc ON cinc.accno = inc.accno
+ LEFT JOIN sl28.chart cexp ON expense_accno_id = cexp.id
+ LEFT JOIN account exp ON cexp.accno = exp.accno;
+
+
+
-- must rebuild this table due to changes since 1.2
INSERT INTO makemodel
@@ -307,9 +336,9 @@
SELECT * FROM sl28.gifi;
UPDATE defaults
- SET value = (select value from sl28.defaults src
- WHERE src.setting_key = defaults.setting_key)
- WHERE setting_key IN (select setting_key FROM sl28.defaults);
+ SET value = (select fldvalue from sl28.defaults src
+ WHERE src.fldname = defaults.setting_key)
+ WHERE setting_key IN (select fldname FROM sl28.defaults);
INSERT INTO parts SELECT * FROM sl28.parts;
@@ -338,15 +367,15 @@
on_hold, approved, reverse, terms, description)
SELECT
customer.credit_id,
- (select entity_id from orig.employee
- WHERE id = ap.employee_id),
- ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid,
- datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber,
+ (select entity_id from sl28.employee
+ WHERE id = ar.employee_id),
+ ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, paid,
+ datepaid, duedate, invoice, ordnumber, ar.curr, ar.notes, quonumber,
intnotes,
- department_id, shipvia, ap.language_code, ponumber, shippingpoint,
+ department_id, shipvia, ar.language_code, ponumber, shippingpoint,
onhold, approved, case when amount < 0 then true else false end,
- ap.terms, description
-FROM orig.ar JOIN orig.customer ON (ap.vendor_id = customer.id) ;
+ ar.terms, description
+FROM sl28.ar JOIN sl28.customer ON (ar.customer_id = customer.id) ;
ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
@@ -360,7 +389,7 @@
on_hold, approved, reverse, terms, description)
SELECT
vendor.credit_id,
- (select entity_id from orig.employee
+ (select entity_id from sl28.employee
WHERE id = ap.employee_id),
ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid,
datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber,
@@ -368,7 +397,7 @@
department_id, shipvia, ap.language_code, ponumber, shippingpoint,
onhold, approved, case when amount < 0 then true else false end,
ap.terms, description
-FROM orig.ap JOIN orig.vendor ON (ap.vendor_id = vendor.id) ;
+FROM sl28.ap JOIN sl28.vendor ON (ap.vendor_id = vendor.id) ;
ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
@@ -376,10 +405,12 @@
(trans_id, chart_id, amount, transdate, source, cleared, fx_transaction,
project_id, memo, approved, cleared_on, reconciled_on,
voucher_id)
-SELECT trans_id, chart_id, amount, transdate, source,
+SELECT trans_id, a.id, amount, transdate, source,
CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
- project_id, memo, approved, cleared, reconciled, vr_id
- FROM orig.acc_trans ;
+ project_id, memo, approved, cleared, null, vr_id
+ FROM sl28.acc_trans ac
+ JOIN sl28.chart c on c.id = ac.chart_id
+ join account a on c.accno= a.accno;
INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
@@ -397,7 +428,7 @@
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
+ coalesce(t.validto::timestamp, 'infinity'), 1, 1
FROM sl28.tax t
JOIN sl28.chart c ON (t.chart_id = c.id)
JOIN account a ON (a.accno = c.accno);
@@ -440,7 +471,7 @@
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
+ discount, unit, project_id, reqdate, ship, serialnumber, null
FROM sl28.orderitems;
INSERT INTO exchangerate select * from sl28.exchangerate;
@@ -465,34 +496,34 @@
INSERT INTO warehouse SELECT * FROM sl28.warehouse;
INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
- orderitems_id, qty, shippingdate, entry_id)
+ orderitems_id, qty, shippingdate)
SELECT e.entity_id, warehouse_id, parts_id, trans_id,
- orderitems_id, qty, shippingdate, i.entry_id
+ orderitems_id, qty, shippingdate
FROM sl28.inventory i
JOIN sl28.employee e ON i.employee_id = e.id;
INSERT INTO yearend (trans_id, transdate) SELECT * FROM sl28.yearend;
INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
- curr, entry_id)
+ curr)
SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
- pv.curr, entry_id
+ pv.curr
FROM sl28.partsvendor pv
JOIN sl28.vendor v ON v.id = pv.vendor_id;
INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
- sellprice, validfrom, validto, curr, entry_id)
+ sellprice, validfrom, validto, curr)
SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
- sellprice, validfrom, validto, pv.curr, entry_id
+ sellprice, validfrom, validto, pv.curr
FROM sl28.partscustomer pv
JOIN sl28.customer v ON v.id = pv.customer_id;
INSERT INTO language SELECT * FROM sl28.language;
INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
- transdate, person_id, entry_id)
+ transdate, person_id)
SELECT trans_id, tablename, reference, formname, action,
- transdate, p.entity_id, entry_id
+ transdate, p.entity_id
FROM sl28.audittrail a
JOIN sl28.employee e ON a.employee_id = e.id
JOIN person p on e.entity_id = p.entity_id;
@@ -516,10 +547,6 @@
JOIN sl28.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 sl28. custom_table_catalog;
-
-INSERT INTO custom_field_catalog SELECT * FROM sl28. custom_field_catalog;
-
INSERT INTO parts_translation SELECT * FROM sl28.translation where trans_id in (select id from parts);
INSERT INTO partsgroup_translation SELECT * FROM sl28.translation where trans_id in
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.