|
-- Lizenzverwaltung
|
|
CREATE TABLE "license" (
|
|
"id" integer DEFAULT nextval('id'::text),
|
|
"parts_id" integer,
|
|
"customer_id" integer,
|
|
"comment" text,
|
|
"validuntil" date,
|
|
"issuedate" date DEFAULT date ('now'::text),
|
|
"quantity" integer,
|
|
"licensenumber" text
|
|
);
|
|
CREATE INDEX "license_id_key" ON "license" (id);
|
|
|
|
CREATE TABLE "licenseinvoice" (
|
|
trans_id integer,
|
|
license_id integer
|
|
);
|
|
|
|
--Datev-Erg?nzungen
|
|
alter table chart add column datevautomatik boolean;
|
|
alter table chart alter column datevautomatik set default 'false';
|
|
update chart set datevautomatik='false';
|
|
|
|
alter table customer add column datevexport integer;
|
|
alter table vendor add column datevexport integer;
|
|
update customer set datevexport=1;
|
|
update vendor set datevexport=1;
|
|
|
|
create table datev (
|
|
"beraternr" character varying(7),
|
|
"beratername" character varying(9),
|
|
"mandantennr" character varying(5),
|
|
"dfvkz" character varying(2),
|
|
"datentraegernr" character varying(3),
|
|
"abrechnungsnr" character varying(6)
|
|
);
|
|
|
|
CREATE FUNCTION set_datevexport() RETURNS trigger AS '
|
|
BEGIN
|
|
IF OLD.datevexport IS NULL THEN
|
|
NEW.datevexport := 1;
|
|
END IF;
|
|
IF OLD.datevexport = 0 THEN
|
|
NEW.datevexport := 2;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
' LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER customer_datevexport BEFORE UPDATE ON customer
|
|
FOR EACH ROW EXECUTE PROCEDURE set_datevexport();
|
|
|
|
CREATE TRIGGER vendor_datevexport BEFORE UPDATE ON vendor
|
|
FOR EACH ROW EXECUTE PROCEDURE set_datevexport();
|
|
|
|
alter table customer add column language varchar(5);
|
|
alter table vendor add column language varchar(5);
|
|
|
|
alter table oe add column cusordnumber text;
|
|
alter table ar add column cusordnumber text;
|
|
|
|
alter table parts rename column unit to unit_old;
|
|
alter table parts add column unit varchar(10);
|
|
update parts set unit=unit_old;
|
|
alter table parts drop column unit_old;
|
|
|
|
--
|
|
--LINET-SK: add column account_number, bank code number and bank to customer and vendor
|
|
alter table customer add column account_number varchar(10);
|
|
alter table customer add column bank_code varchar(10);
|
|
alter table customer add column bank text;
|
|
--
|
|
alter table vendor add column account_number varchar(10);
|
|
alter table vendor add column bank_code varchar(10);
|
|
alter table vendor add column bank text;
|
|
|
|
--LINET-SK: add colum cp_id at oe, ar and ap
|
|
alter table ap add column cp_id integer;
|
|
alter table ar add column cp_id integer;
|
|
alter table oe add column cp_id integer;
|
|
|
|
--deafult-Werte f?r Kunden, Lieferanten und Artikelnummern
|
|
alter table defaults add column customernumber text;
|
|
alter table defaults add column vendornumber text;
|
|
alter table defaults add column articlenumber text;
|
|
alter table defaults add column servicenumber text;
|
|
|
|
update defaults set customernumber=(select max(customernumber) from customer);
|
|
update defaults set vendornumber=(select max(vendornumber) from vendor);
|
|
update defaults set articlenumber=(select max(partnumber) from parts where inventory_accno_id notnull);
|
|
update defaults set servicenumber=(select max(partnumber) from parts where inventory_accno_id isnull);
|
|
|
|
--
|
|
update defaults set version = '2.1.0';
|