Revision cc872430
Von Moritz Bunkus vor mehr als 10 Jahren hinzugefügt
| SL/DB/MetaSetup/DeliveryOrder.pm | ||
|---|---|---|
| 35 | 35 |
shipto_id => { type => 'integer' },
|
| 36 | 36 |
shipvia => { type => 'text' },
|
| 37 | 37 |
taxincluded => { type => 'boolean' },
|
| 38 |
taxzone_id => { type => 'integer' },
|
|
| 38 |
taxzone_id => { type => 'integer', not_null => 1 },
|
|
| 39 | 39 |
terms => { type => 'integer' },
|
| 40 | 40 |
transaction_description => { type => 'text' },
|
| 41 | 41 |
transdate => { type => 'date', default => 'now()' },
|
| ... | ... | |
| 97 | 97 |
key_columns => { shipto_id => 'shipto_id' },
|
| 98 | 98 |
}, |
| 99 | 99 |
|
| 100 |
taxzone => {
|
|
| 101 |
class => 'SL::DB::TaxZone', |
|
| 102 |
key_columns => { taxzone_id => 'id' },
|
|
| 103 |
}, |
|
| 104 |
|
|
| 100 | 105 |
vendor => {
|
| 101 | 106 |
class => 'SL::DB::Vendor', |
| 102 | 107 |
key_columns => { vendor_id => 'id' },
|
| SL/DB/MetaSetup/Invoice.pm | ||
|---|---|---|
| 52 | 52 |
storno => { type => 'boolean', default => 'false' },
|
| 53 | 53 |
storno_id => { type => 'integer' },
|
| 54 | 54 |
taxincluded => { type => 'boolean' },
|
| 55 |
taxzone_id => { type => 'integer' },
|
|
| 55 |
taxzone_id => { type => 'integer', not_null => 1 },
|
|
| 56 | 56 |
terms => { type => 'integer', default => '0' },
|
| 57 | 57 |
transaction_description => { type => 'text' },
|
| 58 | 58 |
transdate => { type => 'date', default => 'now' },
|
| SL/DB/MetaSetup/Order.pm | ||
|---|---|---|
| 44 | 44 |
shipto_id => { type => 'integer' },
|
| 45 | 45 |
shipvia => { type => 'text' },
|
| 46 | 46 |
taxincluded => { type => 'boolean' },
|
| 47 |
taxzone_id => { type => 'integer' },
|
|
| 47 |
taxzone_id => { type => 'integer', not_null => 1 },
|
|
| 48 | 48 |
transaction_description => { type => 'text' },
|
| 49 | 49 |
transdate => { type => 'date', default => 'now' },
|
| 50 | 50 |
vendor_id => { type => 'integer' },
|
| ... | ... | |
| 120 | 120 |
key_columns => { shipto_id => 'shipto_id' },
|
| 121 | 121 |
}, |
| 122 | 122 |
|
| 123 |
taxzone => {
|
|
| 124 |
class => 'SL::DB::TaxZone', |
|
| 125 |
key_columns => { taxzone_id => 'id' },
|
|
| 126 |
}, |
|
| 127 |
|
|
| 123 | 128 |
vendor => {
|
| 124 | 129 |
class => 'SL::DB::Vendor', |
| 125 | 130 |
key_columns => { vendor_id => 'id' },
|
| SL/DB/MetaSetup/PurchaseInvoice.pm | ||
|---|---|---|
| 40 | 40 |
storno => { type => 'boolean', default => 'false' },
|
| 41 | 41 |
storno_id => { type => 'integer' },
|
| 42 | 42 |
taxincluded => { type => 'boolean', default => 'false' },
|
| 43 |
taxzone_id => { type => 'integer' },
|
|
| 43 |
taxzone_id => { type => 'integer', not_null => 1 },
|
|
| 44 | 44 |
transaction_description => { type => 'text' },
|
| 45 | 45 |
transdate => { type => 'date', default => 'now' },
|
| 46 | 46 |
type => { type => 'text' },
|
| sql/Pg-upgrade2/taxzone_id_in_oe_delivery_orders.sql | ||
|---|---|---|
| 1 |
-- @tag: taxzone_id_in_oe_delivery_orders |
|
| 2 |
-- @description: Werte für Inland in Spalte taxzone_id in Tabellen oe und delivery_orders in Foreign Key zu tax_zones konvertieren; NULL-Werte in ap/ar verhindern; Spalten NOT NULL setzen |
|
| 3 |
-- @depends: change_taxzone_id_0 |
|
| 4 |
|
|
| 5 |
UPDATE oe SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); |
|
| 6 |
UPDATE delivery_orders SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); |
|
| 7 |
UPDATE ar SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); |
|
| 8 |
UPDATE ap SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); |
|
| 9 |
|
|
| 10 |
ALTER TABLE oe ALTER COLUMN taxzone_id SET NOT NULL; |
|
| 11 |
ALTER TABLE delivery_orders ALTER COLUMN taxzone_id SET NOT NULL; |
|
| 12 |
ALTER TABLE ar ALTER COLUMN taxzone_id SET NOT NULL; |
|
| 13 |
ALTER TABLE ap ALTER COLUMN taxzone_id SET NOT NULL; |
|
| 14 |
|
|
| 15 |
ALTER TABLE oe ADD CONSTRAINT oe_taxzone_id_fkey FOREIGN KEY (taxzone_id) REFERENCES tax_zones (id); |
|
| 16 |
ALTER TABLE delivery_orders ADD CONSTRAINT delivery_orders_taxzone_id_fkey FOREIGN KEY (taxzone_id) REFERENCES tax_zones (id); |
|
Auch abrufbar als: Unified diff
Bearb. Buchungsgruppen: Spalte taxzone_id in oe/delivery_orders konvertieren
1. Werte 0 und NULL durch neue ID für »Inland« ersetzen (oe,
delivery_orders, ar, ap)
2. Spalte taxzone_id NOT NULL setzen (oe, delivery_orders, ar, ap)
3. Fremdschlüssel auf Tabelle tax_zones setzen (oe, delivery_orders)