Subversion Repositories public

ALTER TABLE kgroup DROP CONSTRAINT "Relationship39";

ALTER TABLE kgroup DROP COLUMN kg_kunum;

ALTER TABLE kgroup

   ALTER COLUMN kg_prnum DROP NOT NULL;

ALTER TABLE kgroup ALTER COLUMN kg_prnum SET STATISTICS -1;

COMMENT ON COLUMN kgroup.kg_prnum IS 'Projektnummer';

Create table "public"."kgsammel"

(

	"ks_num" Int4 Not Null UNIQUE,

	"ks_kgnum" Int4 Not Null,

	"ks_kunum" Int4 Not Null,

constraint "pk_kgsammel" primary key ("ks_num")

);

Comment on table "public"."kgsammel" is 'Dient als Kontainer fuer mehrere Kunden.';

COMMENT ON COLUMN "public"."kgsammel"."ks_num" IS 'Interne laufende Nummer';

COMMENT ON COLUMN "public"."kgsammel"."ks_kgnum" IS 'Verknuepfung zur Kundengruppe';

COMMENT ON COLUMN "public"."kgsammel"."ks_kunum" IS 'Verknuepfung zum Kunden';

Create index "IX_Relationship57" on "public"."kgsammel" ("ks_kunum");

Alter table "kgsammel" add Constraint "Relationship57" foreign key ("ks_kunum") references "public"."kunde" ("ku_num") on update restrict on delete restrict;

Create index "IX_Relationship58" on "public"."kgsammel" ("ks_kgnum");

Alter table "kgsammel" add Constraint "Relationship58" foreign key ("ks_kgnum") references "public"."kgroup" ("kg_num") on update restrict on delete restrict;

ALTER TABLE histanteil DROP CONSTRAINT "Relationship31";

ALTER TABLE histanteil RENAME hi_kunum  TO hi_ksnum;

ALTER TABLE histanteil ADD CONSTRAINT "Relationship31" FOREIGN KEY (hi_ksnum) REFERENCES kgsammel (ks_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE histanteil ALTER COLUMN hi_ksnum SET STATISTICS -1;

COMMENT ON COLUMN histanteil.hi_ksnum IS 'Kontainernummer';

alter table histanteil drop column hi_valid;

alter table histanteil add column hi_valid_from date;

alter table histanteil add column hi_valid_to date;

alter table histanteil add column hi_proz float8;

alter table histanteil alter column hi_valid_from set not null;

alter table histanteil alter column hi_proz set not null;

comment on column histanteil.hi_valid_from is 'Datum ab dem der Eintrag gilt';

comment on column histanteil.hi_valid_to is 'Datum bis zu dem der Eintrag gilt';

comment on column histanteil.hi_proz is 'Prozentsatz des Anteils';

ALTER TABLE charge DROP COLUMN ch_date;

ALTER TABLE charge

   ADD COLUMN ch_date date;

ALTER TABLE charge

   ALTER COLUMN ch_date SET NOT NULL;

COMMENT ON COLUMN charge.ch_date IS 'Datum an dem der Aufwand angefallen ist.';

COMMENT ON COLUMN charge.ch_date IS 'Datum an dem der Aufwand angefallen ist.';

ALTER TABLE counter DROP COLUMN co_anteil;

ALTER TABLE dration DROP COLUMN dr_valid_from;

ALTER TABLE dration

   ADD COLUMN dr_valid_from date;

ALTER TABLE dration

   ALTER COLUMN dr_valid_from SET NOT NULL;

COMMENT ON COLUMN dration.dr_valid_from IS 'Datum gueltig von. Notwendig um eine Historie aufzubauen';

Create table "public"."fixanteil"

(

	"fx_num" Int4 Not Null UNIQUE,

	"fx_valid_from" Date Not Null,

	"fx_valid_to" Date,

	"fx_kunum" Int4 Not Null,

	"fx_proz" Float8 Not Null,

constraint "pk_fixanteil" primary key ("fx_num")

);

Comment on column "public"."fixanteil"."fx_num" is 'Automatisch generierte interne laufende Nummer';

Comment on column "public"."fixanteil"."fx_valid_from" is 'Datum ab dem der Eintrag gilt.';

Comment on column "public"."fixanteil"."fx_valid_to" is 'Datum bis zu dem der Eintrag gilt.';

Comment on column "public"."fixanteil"."fx_kunum" is 'Kundennummer';

Comment on column "public"."fixanteil"."fx_proz" is 'Anteile in Prozent';

Create index "IX_Relationship32" on "fixanteil" ("fx_kunum");

Alter table "fixanteil" add Constraint "Relationship32" foreign key ("fx_kunum") references "kunde" ("ku_num") on update restrict on delete restrict;

CREATE TABLE key_whg

(

   wh_num int4 NOT NULL, 

   wh_whg char(5) NOT NULL, 

   wh_bez varchar(60), 

   CONSTRAINT pk_whg PRIMARY KEY (wh_num)

) WITHOUT OIDS;

ALTER TABLE key_whg OWNER TO "www-data";

COMMENT ON COLUMN key_whg.wh_num IS 'Interne laufende Nummer';

COMMENT ON COLUMN key_whg.wh_whg IS 'ISO-Code der Waehrung';

COMMENT ON COLUMN key_whg.wh_bez IS 'Bezeichnung der Waehrung';

COMMENT ON TABLE key_whg IS 'Waehrungsschluessel';

GRANT ALL ON TABLE key_whg TO public;

GRANT ALL ON TABLE key_whg TO andreas;

ALTER TABLE dration

   ADD COLUMN dr_whg int4;

ALTER TABLE dration

   ALTER COLUMN dr_whg SET NOT NULL;

COMMENT ON COLUMN dration.dr_whg IS 'Waehrung';

ALTER TABLE dration ADD CONSTRAINT "Relationship42" FOREIGN KEY (dr_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;

COMMENT ON COLUMN dration.dr_whg IS 'Waehrung';

ALTER TABLE charge

   ADD COLUMN ch_whg int4;

ALTER TABLE charge

   ALTER COLUMN ch_whg SET NOT NULL;

COMMENT ON COLUMN charge.ch_whg IS 'Waehrung';

ALTER TABLE charge ADD CONSTRAINT "Relationship39" FOREIGN KEY (ch_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;

COMMENT ON COLUMN charge.ch_whg IS 'Waehrung';

CREATE TABLE tagsatz

(

   tg_num int4 NOT NULL, 

   tg_valid_from date NOT NULL, 

   tg_valid_to date, 

   tg_amount float8, 

   tg_nightr float8, 

   tg_ration float8, 

   tg_kmcar float8, 

   tg_kmdrain float8, 

   tg_kmplain float8, 

   tg_whg int4 NOT NULL, 

   tg_minum int4 NOT NULL, 

   CONSTRAINT pk_tgnum PRIMARY KEY (tg_num), 

   CONSTRAINT fk_whg_10 FOREIGN KEY (tg_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT, 

   CONSTRAINT fk_minum_10 FOREIGN KEY (tg_minum) REFERENCES mitarbeiter (mi_num)    ON UPDATE RESTRICT ON DELETE RESTRICT

) WITHOUT OIDS;

ALTER TABLE tagsatz OWNER TO "www-data";

COMMENT ON COLUMN tagsatz.tg_num IS 'Interne laufende Nummer';

COMMENT ON COLUMN tagsatz.tg_valid_from IS 'Datum gueltig ab';

COMMENT ON COLUMN tagsatz.tg_valid_to IS 'Datum gueltig bis';

COMMENT ON COLUMN tagsatz.tg_amount IS 'Tagsatz';

COMMENT ON COLUMN tagsatz.tg_nightr IS 'Naechtigungspauschale';

COMMENT ON COLUMN tagsatz.tg_ration IS 'Tagesdiaeten';

COMMENT ON COLUMN tagsatz.tg_kmcar IS 'Kilometerpauschale PKW';

COMMENT ON COLUMN tagsatz.tg_kmdrain IS 'Kilometerpauschale Zug';

COMMENT ON COLUMN tagsatz.tg_kmplain IS 'Kilometerpauschale Flugzeug';

COMMENT ON COLUMN tagsatz.tg_whg IS 'Verknuepfung zur Waehrung';

COMMENT ON COLUMN tagsatz.tg_minum IS 'Verknuepfung zum Mitarbeiter';

COMMENT ON TABLE tagsatz IS 'Beinhaltet die Tagsaetze zu jedem einzelnen Mitarbeiter';

GRANT ALL ON TABLE tagsatz TO public;

GRANT ALL ON TABLE tagsatz TO andreas;

ALTER TABLE dration DROP COLUMN dr_ration;

ALTER TABLE dration DROP COLUMN dr_kmcar;

ALTER TABLE dration DROP COLUMN dr_kmdrain;

ALTER TABLE dration

   ADD COLUMN dr_date_start timestamp;

COMMENT ON COLUMN dration.dr_date_start IS 'Startdatum und Zeit der Reise';

ALTER TABLE dration

   ADD COLUMN dr_date_end timestamp;

COMMENT ON COLUMN dration.dr_date_end IS 'Endedatum und Zeit der Reise';

ALTER TABLE dration

   ADD COLUMN dr_target varchar(60);

COMMENT ON COLUMN dration.dr_target IS 'Zielort / Land';

ALTER TABLE dration

   ADD COLUMN dr_purpose varchar(60);

   COMMENT ON COLUMN dration.dr_purpose IS 'Zweck der Reise';

ALTER TABLE dration

   ADD COLUMN dr_pnightr bool;

COMMENT ON COLUMN dration.dr_pnightr IS 'True = Naechtigungspauschale, False = Sonderbetrag';

ALTER TABLE dration

   ADD COLUMN dr_kmcar int2;

COMMENT ON COLUMN dration.dr_kmcar IS 'Gefahrene Kilometer';

ALTER TABLE dration

   ADD COLUMN dr_kfzkenn char(16);

COMMENT ON COLUMN dration.dr_kfzkenn IS 'KFZ Kennzeichen';

ALTER TABLE dration

   ADD COLUMN dr_kmdrain int2;

COMMENT ON COLUMN dration.dr_kmdrain IS 'Gefahrene Zug-Kilometer';

ALTER TABLE dration

   ADD COLUMN dr_pkmplain bool;

COMMENT ON COLUMN dration.dr_pkmplain IS 'True = Flugpauschale, False = Einzelpreis fuer Flug';

ALTER TABLE dration

   ADD COLUMN dr_remark varchar(4096);

COMMENT ON COLUMN dration.dr_remark IS 'Bemerkung / Kommentar';

ALTER TABLE dration

   ADD COLUMN dr_costloc int4;

COMMENT ON COLUMN dration.dr_costloc IS 'Kostenstelle';

ALTER TABLE dration

   ADD COLUMN dr_permit bool;

COMMENT ON COLUMN dration.dr_permit IS 'True = Abrechnung ist genehmigt';

ALTER TABLE dration

   ADD COLUMN dr_minum int4;

COMMENT ON COLUMN dration.dr_minum IS 'Verweis auf Mitarbeiter der die Genehmigung erteilt hat';

ALTER TABLE dration ADD CONSTRAINT fk_costloc_11 FOREIGN KEY (dr_costloc) REFERENCES key_costloc (kc_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE dration ADD CONSTRAINT fk_minum_12 FOREIGN KEY (dr_minum) REFERENCES mitarbeiter (mi_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;