|
|
|
--Update der Numeric-Spalten von 5 auf 10 Vorkommastellen
|
|
--
|
|
--TABLE ap
|
|
alter table ap rename column paid to paidold;
|
|
alter table ap add column paid numeric(15,5);
|
|
update ap set paid=paidold;
|
|
alter table ap drop column paidold;
|
|
--
|
|
alter table ap rename column netamount to netamountold;
|
|
alter table ap add column netamount numeric(15,5);
|
|
update ap set netamount=netamountold;
|
|
alter table ap drop column netamountold;
|
|
--
|
|
alter table ap rename column amount to amountold;
|
|
alter table ap add column amount numeric(15,5);
|
|
update ap set amount=amountold;
|
|
alter table ap drop column amountold;
|
|
--
|
|
--TABLE acc_trans
|
|
alter table acc_trans rename column amount to amountold;
|
|
alter table acc_trans add column amount numeric(15,5);
|
|
update acc_trans set amount=amountold;
|
|
alter table acc_trans drop column amountold;
|
|
--
|
|
--TABLE ar
|
|
alter table ar rename column amount to amountold;
|
|
alter table ar add column amount numeric(15,5);
|
|
update ar set amount=amountold;
|
|
alter table ar drop column amountold;
|
|
--
|
|
alter table ar rename column netamount to netamountold;
|
|
alter table ar add column netamount numeric(15,5);
|
|
update ar set netamount=netamountold;
|
|
alter table ar drop column netamountold;
|
|
--
|
|
alter table ar rename column paid to paidold;
|
|
alter table ar add column paid numeric(15,5);
|
|
update ar set paid=paidold;
|
|
alter table ar drop column paidold;
|
|
--
|
|
--TABLE customer
|
|
alter table customer rename column creditlimit to creditlimitold;
|
|
alter table customer add column creditlimit numeric(15,5);
|
|
update customer set creditlimit=creditlimitold;
|
|
alter table customer drop column creditlimitold;
|
|
--
|
|
--TABLE exchangerate
|
|
alter table exchangerate rename column buy to buyold;
|
|
alter table exchangerate add column buy numeric(15,5);
|
|
update exchangerate set buy=buyold;
|
|
alter table exchangerate drop column buyold;
|
|
--
|
|
alter table exchangerate rename column sell to sellold;
|
|
alter table exchangerate add column sell numeric(15,5);
|
|
update exchangerate set sell=sellold;
|
|
alter table exchangerate drop column sellold;
|
|
--
|
|
--TABLE invoice
|
|
alter table invoice rename column sellprice to sellpriceold;
|
|
alter table invoice add column sellprice numeric(15,5);
|
|
update invoice set sellprice=sellpriceold;
|
|
alter table invoice drop column sellpriceold;
|
|
--
|
|
alter table invoice rename column fxsellprice to fxsellpriceold;
|
|
alter table invoice add column fxsellprice numeric(15,5);
|
|
update invoice set fxsellprice=fxsellpriceold;
|
|
alter table invoice drop column fxsellpriceold;
|
|
--
|
|
--TABLE oe
|
|
alter table oe rename column amount to amountold;
|
|
alter table oe add column amount numeric(15,5);
|
|
update oe set amount=amountold;
|
|
alter table oe drop column amountold;
|
|
--
|
|
alter table oe rename column netamount to netamountold;
|
|
alter table oe add column netamount numeric(15,5);
|
|
update oe set netamount=netamountold;
|
|
alter table oe drop column netamountold;
|
|
--
|
|
--TABLE orderitems
|
|
alter table orderitems rename column sellprice to sellpriceold;
|
|
alter table orderitems add column sellprice numeric(15,5);
|
|
update orderitems set sellprice=sellpriceold;
|
|
alter table orderitems drop column sellpriceold;
|
|
--
|
|
--TABLE parts
|
|
alter table parts rename column listprice to listpriceold;
|
|
alter table parts add column listprice numeric(15,5);
|
|
update parts set listprice=listpriceold;
|
|
alter table parts drop column listpriceold;
|
|
--
|
|
alter table parts rename column sellprice to sellpriceold;
|
|
alter table parts add column sellprice numeric(15,5);
|
|
update parts set sellprice=sellpriceold;
|
|
alter table parts drop column sellpriceold;
|
|
--
|
|
alter table parts rename column lastcost to lastcostold;
|
|
alter table parts add column lastcost numeric(15,5);
|
|
update parts set lastcost=lastcostold;
|
|
alter table parts drop column lastcostold;
|
|
--
|
|
--TABLE tax
|
|
alter table tax rename column rate to rateold;
|
|
alter table tax add column rate numeric(15,5);
|
|
update tax set rate=rateold;
|
|
alter table tax drop column rateold;
|
|
--
|
|
--TABLE vendor
|
|
alter table vendor rename column creditlimit to creditlimitold;
|
|
alter table vendor add column creditlimit numeric(15,5);
|
|
update vendor set creditlimit=creditlimitold;
|
|
alter table vendor drop column creditlimitold;
|
|
--
|
|
|
|
--New Fields for customer and vendor
|
|
alter table vendor add column obsolete boolean;
|
|
alter table vendor alter column obsolete set default 'false';
|
|
alter table customer add column obsolete boolean;
|
|
alter table customer alter column obsolete set default 'false';
|
|
alter table customer add column ustid varchar(12);
|
|
alter table vendor add column ustid varchar(12);
|
|
|
|
alter table customer add column username varchar(50);
|
|
alter table vendor add column username varchar(50);
|
|
alter table customer add column user_password varchar(12);
|
|
alter table vendor add column user_password varchar(12);
|
|
alter table customer add column salesman_id integer;
|
|
alter table vendor add column salesman_id integer;
|
|
|
|
-- Shipto
|
|
alter table shipto add column shiptodepartment_1 varchar(75);
|
|
alter table shipto add column shiptodepartment_2 varchar(75);
|
|
|
|
|
|
|
|
-- Addon for business
|
|
alter table business add column salesman boolean;
|
|
alter table business alter column salesman set default 'false';
|
|
alter table business add column customernumberinit text;
|
|
|
|
alter table parts add column ve integer;
|
|
alter table parts add column gv numeric(15,5);
|
|
--
|
|
|
|
-- Add table contrains
|
|
alter table customer alter name SET NOT NULL;
|
|
alter table vendor alter name set NOT NULL;
|
|
alter table chart alter accno set NOT NULL;
|
|
alter table parts alter partnumber set NOT NULL;
|
|
alter table ar alter invnumber set NOT NULL;
|
|
alter table ap alter invnumber set NOT NULL;
|
|
alter table oe alter ordnumber set NOT NULL;
|
|
|
|
alter table gl add primary key (id);
|
|
alter table chart add primary key (id);
|
|
alter table parts add primary key (id);
|
|
alter table invoice add primary key (id);
|
|
alter table vendor add primary key (id);
|
|
alter table customer add primary key (id);
|
|
alter table contacts add primary key (cp_id);
|
|
alter table ar add primary key (id);
|
|
alter table ap add primary key (id);
|
|
alter table oe add primary key (id);
|
|
alter table employee add primary key (id);
|
|
alter table warehouse add primary key (id);
|
|
alter table business add primary key (id);
|
|
alter table license add primary key (id);
|
|
alter table orderitems add primary key (id);
|
|
|
|
alter table acc_trans add foreign key (chart_id) references chart (id);
|
|
alter table invoice add foreign key (parts_id) references parts (id);
|
|
alter table ar add foreign key (customer_id) references customer (id);
|
|
alter table ap add foreign key (vendor_id) references vendor (id);
|
|
alter table orderitems add foreign key (parts_id) references parts (id);
|
|
|
|
--Modify the possible length of bank account numbers
|
|
alter table customer add column temp_account_number character varying(15);
|
|
update customer set temp_account_number=account_number;
|
|
alter table customer drop column account_number;
|
|
alter table customer rename temp_account_number to account_number;
|
|
|
|
alter table vendor add column temp_account_number character varying(15);
|
|
update vendor set temp_account_number=account_number;
|
|
alter table vendor drop column account_number;
|
|
alter table vendor rename temp_account_number to account_number;
|
|
|
|
-- audit
|
|
alter table defaults add column audittrail bool;
|
|
CREATE TABLE audittrail (
|
|
trans_id int,
|
|
tablename text,
|
|
reference text,
|
|
formname text,
|
|
action text,
|
|
transdate timestamp default current_timestamp,
|
|
employee_id int
|
|
);
|
|
|
|
-- pricegroups
|
|
|
|
CREATE TABLE "pricegroup" (
|
|
"id" integer DEFAULT nextval('id'::text),
|
|
"pricegroup" text not null,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE "prices" (
|
|
"parts_id" integer REFERENCES parts(id),
|
|
"pricegroup_id" integer REFERENCES pricegroup(id),
|
|
"price" numeric(15,5)
|
|
);
|
|
|
|
ALTER TABLE customer ADD column klass integer;
|
|
ALTER TABLE customer ALTER column klass set default 0;
|
|
|
|
--
|
|
ALTER TABLE invoice ADD column pricegroup_id integer;
|
|
ALTER TABLE orderitems ADD column pricegroup_id integer;
|
|
|
|
update defaults set version = '2.1.2', audittrail = 't';
|
|
--
|
|
|