| |
| -- --------------------------------------------- |
| -- TABLE VERSION |
| -- --------------------------------------------- |
| -- TABLE: PUBLIC.REF_VERSION |
| CREATE TABLE VERSION |
| ( |
| "ID" INTEGER NOT NULL, |
| "VERSION" VARCHAR2(500) NOT NULL, |
| CONSTRAINT VERSION_PKEY PRIMARY KEY ("ID") |
| ); |
| |
| INSERT INTO VERSION (ID, VERSION) VALUES ( 1, '1.0-ORA' ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_CONTACT |
| -- --------------------------------------------- |
| |
| CREATE SEQUENCE TBL_CONTACT_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_CONTACT |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "CONTACT_TYPE" VARCHAR2(3), |
| "NOTE" VARCHAR2(255), |
| "ANONYMIZED" NUMBER, |
| CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY ("ID") |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_CONTACT_BIR" |
| BEFORE INSERT ON TBL_CONTACT |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_CONTACT_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_CONTACT_BIR" ENABLE; |
| |
| |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e','-','')), '4CO', 'company 1 (id=1)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('fc7f598b-0d51-46bb-9563-99851fe6a3ad','-','')), '4CO', 'company 2 (id=2)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('556b91be-6d57-432f-93ed-65604dd6e5cd','-','')), '1CP', 'contact person 1 (id=3)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('116380e3-25c5-4179-b40a-8abebe10fe07','-','')), '1CP', 'contact person 2 (id=4)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('7782179b-fb79-4370-8f71-f4c71470d006','-','')), '3IP', 'internal person 1 (id=5)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('8963aa38-d021-4dc9-bd70-d3734ccd20c4','-','')), '3IP', 'internal person 2 (id=6)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('c862d604-5766-43d6-a7e8-a4bac2bd01e1','-','')), '2EP', 'external person 1 (id=7)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('fa3d981b-a7d6-4965-a623-cdbc69404153','-','')), '2EP', 'external person 2 (id=8)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ceba21e9-e685-483b-840e-ad167860a696','-','')), '4CO', 'anonymous company A (id=9)', 1 ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('7889c814-9752-4e4e-a9fe-b46f36a38ccd','-','')), '4CO', 'anonymous company B (id=10)', 1 ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('618a01a3-f348-44cc-9ddd-c9df946b0212','-','')), '1CP', 'anoymous contact person A1 (id=11)', 1 ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('4ed82474-3878-457a-baef-c28b8e486f25','-','')), '1CP', 'anoymous contact persion A2 (id=12)', 1 ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('8fe41b90-d10c-4a70-8fde-0990286ad3c6','-','')), '3IP', 'internal person 3 (id=13)', null ); |
| INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ab804610-d6a4-4803-a4a1-3f6cb742b2a4','-','')), '3IP', 'internal person 4 (id=14)', null ); |
| |
| |
| CREATE UNIQUE INDEX IDX_CNTCT_UUID ON TBL_CONTACT (UUID); |
| CREATE INDEX IDX_CNTCT_ANONYM ON TBL_CONTACT (ANONYMIZED); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE REF_ADDRESS_TYPE |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_ADDRESS_TYPE_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_ADDRESS_TYPE |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "TYPE" VARCHAR2(30), |
| "DESCRIPTION" VARCHAR2(255), |
| CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY ("ID") |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_ADDRESS_TYPE_BIR" |
| BEFORE INSERT ON REF_ADDRESS_TYPE |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_ADDRESS_TYPE_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_ADDRESS_TYPE_BIR" ENABLE; |
| |
| CREATE UNIQUE INDEX IDX_REF_ADDRESS_TYPE_UUID ON REF_ADDRESS_TYPE ( UUID ASC ); |
| |
| INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('3802e681-9396-434e-b19c-5fedcec40ba7','-','')), 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' ); |
| INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79','-','')), 'Privatadresse', 'private Anschrift' ); |
| INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('70fd0811-f674-4f3a-96a7-7ae29fc95188','-','')), 'Lieferadresse', 'Adresse für Lieferungen' ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE REF_PERSON_TYPE |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_PERSON_TYPE_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_PERSON_TYPE |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "TYPE" VARCHAR2(30), |
| "DESCRIPTION" VARCHAR2(255), |
| CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_PERSON_TYPE_BIR" |
| BEFORE INSERT ON REF_PERSON_TYPE |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_PERSON_TYPE_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_PERSON_TYPE_BIR" ENABLE; |
| |
| CREATE UNIQUE INDEX IDX_REF_PERSON_TYPE_UUID ON REF_PERSON_TYPE ( UUID ASC ); |
| |
| INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('47ce68b7-6d44-453e-b421-19020fd791b5','-','')), 'Rechtsanwalt', '' ); |
| INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('a7522c72-14d0-4e9d-afe3-bfcb3ffbec10','-','')), 'Geschäftsführer', '' ); |
| INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('2eb4885e-7363-4918-90ed-b7d5d84cfd3f','-','')), 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE REF_COMMUNICATION_TYPE |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_COMMUNICATION_TYPE_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE REF_COMMUNICATION_TYPE |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "TYPE" VARCHAR2(30), |
| "DESCRIPTION" VARCHAR2(255), |
| "TYPE_EMAIL" NUMBER, |
| CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_COMMUNICATION_TYPE_BIR" |
| BEFORE INSERT ON REF_COMMUNICATION_TYPE |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_COMMUNICATION_TYPE_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_COMMUNICATION_TYPE_BIR" ENABLE; |
| |
| |
| CREATE UNIQUE INDEX idx_ref_comm_type_uuid ON REF_COMMUNICATION_TYPE ( UUID ASC ); |
| |
| INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('4757ca3a-72c2-4f13-a2f6-ce092e3eadf4','-','')), 'E-Mail', 'E-Mail Adresse', 1 ); |
| INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('77028572-ff57-4c1d-999a-78fa3fcbc1cd','-','')), 'Festnetz', '', 0 ); |
| INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('f7d5b343-00c2-4d7f-8e03-009aad3d90f7','-','')), 'Mobil', '', 0 ); |
| INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('2bfe40f9-c4eb-4d2e-855f-6b0883912846','-','')), 'Fax', '', 0 ); |
| INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('d00d1a61-c8e7-43b2-959f-66e986731441','-','')), 'WhatsApp', '', 0 ); |
| |
| -- --------------------------------------------- |
| -- TABLE REF_SALUTATION |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_SALUTATION_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_SALUTATION |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "TYPE" VARCHAR2(30), |
| "DESCRIPTION" VARCHAR2(255), |
| CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_SALUTATION_BIR" |
| BEFORE INSERT ON REF_SALUTATION |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_SALUTATION_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_SALUTATION_BIR" ENABLE; |
| |
| |
| CREATE UNIQUE INDEX idx_ref_salutation_uuid ON REF_SALUTATION ( UUID ASC ); |
| |
| INSERT INTO REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('90119f18-5562-425d-9a36-3dd58ea125e5','-','')), 'Herr', 'Anrede männlich' ); |
| INSERT INTO REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('4e873baa-e4f5-4585-8b16-2db8fac66538','-','')), 'Frau', 'Anrede weiblich' ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_ADDRESS |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_ADDRESS_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_ADDRESS |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| "FK_ADDRESS_TYPE" INTEGER, |
| "IS_MAIN_ADDRESS" NUMBER, |
| "POSTCODE" VARCHAR2(30), |
| "COMMUNITY" VARCHAR2(255), |
| "COMMUNITY_SUFFIX" VARCHAR2(255), |
| "STREET" VARCHAR2(255), |
| "HOUSENUMBER" VARCHAR2(30), |
| "WGS_84_ZONE" VARCHAR2(255), |
| "LATITUDE" VARCHAR2(255), |
| "LONGITUDE" VARCHAR2(255), |
| "URL_MAP" VARCHAR2(255), |
| "NOTE" VARCHAR2(255), |
| |
| CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID), |
| CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT(ID), |
| CONSTRAINT TBL_ADDRESS_ADDR_TYPE_FKEY FOREIGN KEY (FK_ADDRESS_TYPE) |
| REFERENCES REF_ADDRESS_TYPE (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_ADDRESS_BIR" |
| BEFORE INSERT ON TBL_ADDRESS |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_ADDRESS_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_ADDRESS_BIR" ENABLE; |
| |
| |
| CREATE UNIQUE INDEX idx_tbl_address_uuid ON TBL_ADDRESS ( UUID ASC ); |
| |
| INSERT INTO TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( hextoraw(replace('37e800fe-64f0-4834-8b83-8453cbb936a5','-','')), 2, 1, 1, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar'); |
| INSERT INTO TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( hextoraw(replace('8a1202ae-2532-474e-8367-a1f0e13e9fbd','-','')), 1, 2, 0, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr'); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_COMMUNICATION |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_COMMUNICATION_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_COMMUNICATION |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| "FK_COMMUNICATION_TYPE" INTEGER, |
| "COMMUNICATION_DATA" VARCHAR2(1024), |
| "NOTE" VARCHAR2(255), |
| |
| CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID), |
| CONSTRAINT TBL_COMM__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT(ID), |
| CONSTRAINT TBL_COMM__COMM_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE) |
| REFERENCES REF_COMMUNICATION_TYPE (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_COMMUNICATION_BIR" |
| BEFORE INSERT ON TBL_COMMUNICATION |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_COMMUNICATION_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_COMMUNICATION_BIR" ENABLE; |
| |
| |
| CREATE UNIQUE INDEX idx_tbl_communication_uuid ON TBL_COMMUNICATION ( UUID ASC ); |
| |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('25f6d7cc-b168-4dd5-a36d-6f14b2f956e9','-','')), 2, 2, 'bitte melden Sie sich bei uns', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('a5fa380e-8f33-4ea7-9416-e03d11b91cae','-','')), 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('c0dcef80-ca07-48b7-a3ed-2c99c4388928','-','')), 1, 1, 'info@bigbang.com', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('d19d9e10-d2dd-4383-84ec-2fe96421c0a3','-','')), 2, 1, 'info@pharmapeekltd.com', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('234b63e4-d8db-48ab-899f-0320903c01af','-','')), 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('11e25c02-de00-430d-b6cd-f02f7c60e026','-','')), 5, 1, 'PaulineF@gmx.net', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('9ca29c3b-e189-4ce9-9401-15001c769627','-','')), 6, 1, 'mo@gmail.com', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('05505b1a-61df-45c0-b006-64165cbadfa2','-','')), 7, 1, 'MGruebelSport@fogger.mil', 'Info...'); |
| INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('b52dabaf-d156-4fd0-a07c-510673112a15','-','')), 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...'); |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_COMPANY |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_COMPANY_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE TBL_COMPANY |
| ( |
| "ID" INTEGER NOT NULL, |
| "COMPANY_NAME" VARCHAR2(255), |
| "COMPANY_TYPE" VARCHAR2(30), |
| "HR_NUMBER" VARCHAR2(255), |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id), |
| CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT(ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_COMPANY_BIR" |
| BEFORE INSERT ON TBL_COMPANY |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_COMPANY_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_COMPANY_BIR" ENABLE; |
| |
| |
| INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 ); |
| INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 ); |
| INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 ); |
| INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_CONTACT_PERSON |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_CONTACT_PERSON_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE TBL_CONTACT_PERSON |
| ( |
| "ID" INTEGER NOT NULL, |
| "FIRST_NAME" VARCHAR2(255), |
| "LAST_NAME" VARCHAR2(255), |
| "TITLE" VARCHAR2(255), |
| "FK_SALUTATION_ID" INTEGER, |
| "FK_REF_PERSON_TYPE_ID" INTEGER NULL, |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| "FK_COMPANY_ID" INTEGER NOT NULL, |
| CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID), |
| CONSTRAINT TBL_CP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT (ID), |
| CONSTRAINT TBL_CP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) |
| REFERENCES REF_PERSON_TYPE (ID), |
| CONSTRAINT TBL_CP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) |
| REFERENCES REF_SALUTATION (ID), |
| CONSTRAINT TBL_CP__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID) |
| REFERENCES TBL_COMPANY (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_CONTACT_PERSON_BIR" |
| BEFORE INSERT ON TBL_CONTACT_PERSON |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_CONTACT_PERSON_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_CONTACT_PERSON_BIR" ENABLE; |
| |
| |
| INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2); |
| INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2); |
| INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3); |
| INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2); |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_EXTERNAL_PERSON |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_EXTERNAL_PERSON_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE TBL_EXTERNAL_PERSON |
| ( |
| "ID" INTEGER NOT NULL, |
| "FIRST_NAME" VARCHAR2(255), |
| "LAST_NAME" VARCHAR2(255), |
| "TITLE" VARCHAR2(255), |
| "FK_SALUTATION_ID" INTEGER, |
| "FK_REF_PERSON_TYPE_ID" INTEGER, |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id), |
| CONSTRAINT TBL_EP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT (ID), |
| CONSTRAINT TBL_EP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) |
| REFERENCES REF_SALUTATION (ID), |
| CONSTRAINT TBL_EP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) |
| REFERENCES REF_PERSON_TYPE (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_EXTERNAL_PERSON_BIR" |
| BEFORE INSERT ON TBL_EXTERNAL_PERSON |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_EXTERNAL_PERSON_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_EXTERNAL_PERSON_BIR" ENABLE; |
| |
| |
| INSERT INTO TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7); |
| INSERT INTO TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_INTERNAL_PERSON |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_INTERNAL_PERSON_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_INTERNAL_PERSON |
| ( |
| "ID" INTEGER NOT NULL, |
| "FIRST_NAME" VARCHAR2(255), |
| "LAST_NAME" VARCHAR2(255), |
| "TITLE" VARCHAR2(255), |
| "FK_SALUTATION_ID" INTEGER, |
| "FK_REF_PERSON_TYPE_ID" INTEGER, |
| "DEPARTMENT" VARCHAR2(255), |
| "UID_IDENT" VARCHAR2(255), |
| "USER_REF" VARCHAR2(255), |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID), |
| CONSTRAINT TBL_IP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) |
| REFERENCES TBL_CONTACT (ID), |
| CONSTRAINT TBL_IP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) |
| REFERENCES REF_SALUTATION (ID), |
| CONSTRAINT TBL_IP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) |
| REFERENCES REF_PERSON_TYPE (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_INTERNAL_PERSON_BIR" |
| BEFORE INSERT ON TBL_INTERNAL_PERSON |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_INTERNAL_PERSON_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_INTERNAL_PERSON_BIR" ENABLE; |
| |
| |
| CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON TBL_INTERNAL_PERSON ( "UID_IDENT" ASC ); |
| CREATE UNIQUE INDEX idx_tbl_ip_user_ref ON TBL_INTERNAL_PERSON ( "USER_REF" ASC ); |
| |
| INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5); |
| INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6); |
| INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13); |
| INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14); |
| -- --------------------------------------------- |
| -- TABLE TBL_ASSIGNMENT_MODUL_CONTACT |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_ASSIGN_MODUL_CNTCT_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE TBL_ASSIGNMENT_MODUL_CONTACT |
| ( |
| "ID" INTEGER NOT NULL, |
| "UUID" RAW(16) NOT NULL, |
| "FK_CONTACT_ID" INTEGER NOT NULL, |
| "MODUL_NAME" VARCHAR2(255), |
| "ASSIGNMENT_DATE" timestamp, |
| "EXPIRING_DATE" timestamp, |
| "DELETION_LOCK_UNTIL" timestamp, |
| "ASSIGNMENT_NOTE" VARCHAR2(2048), |
| CONSTRAINT TBL_ASS_MOD_CONTACT_PKEY PRIMARY KEY (id), |
| CONSTRAINT TBL_ASS_MOD_CONTACT_ID_FKEY FOREIGN KEY (fk_contact_id) |
| REFERENCES TBL_CONTACT (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_ASSIGN_MODUL_CNTCT_BIR" |
| BEFORE INSERT ON TBL_ASSIGNMENT_MODUL_CONTACT |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_ASSIGN_MODUL_CNTCT_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_ASSIGN_MODUL_CNTCT_BIR" ENABLE; |
| |
| |
| INSERT INTO tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( hextoraw(replace('dfc2979c-40e3-11ea-b77f-2e728ce88125','-','')), 1, 'Betriebstagebuch', CURRENT_TIMESTAMP, null, null, 'seit Januar zugeordnet'); |
| INSERT INTO tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( hextoraw(replace('4009bce2-40e5-11ea-b77f-2e728ce88125','-','')), 1, 'Störinfos', CURRENT_TIMESTAMP, null, null, 'seit Februar zugeordnet'); |
| |
| -- ------------------------------------- |
| -- VIEWS ------------------------------- |
| -- ------------------------------------- |
| CREATE VIEW VW_GENERAL_CONTACT |
| AS |
| SELECT g.id, |
| c.uuid, |
| g.name, |
| c.contact_type, |
| g.fk_contact_id, |
| g.company_name, |
| g.company_type, |
| g.company_id, |
| g.fk_salutation_id, |
| g.fk_ref_person_type_id, |
| g.title, |
| g.first_name, |
| g.last_name, |
| g.department, |
| c.note, |
| c.anonymized |
| FROM tbl_contact c |
| JOIN ( |
| |
| SELECT company.id, |
| company_name as name, |
| fk_contact_id, |
| company_name, |
| company_type, |
| cntct.uuid as company_id, |
| null as fk_salutation_id, |
| null as fk_ref_person_type_id, |
| null as title, |
| null as first_name, |
| null as last_name, |
| null as department |
| FROM tbl_company company |
| JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id |
| |
| UNION |
| |
| SELECT p.id, |
| COALESCE(p.last_name, '') |
| || CASE WHEN LENGTH(COALESCE(p.last_name, '')) > 0 AND LENGTH(COALESCE(p.first_name, '')) > 0 THEN ', ' ELSE '' END |
| || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name, |
| p.fk_contact_id, |
| c.company_name, |
| c.company_type, |
| company_contact.uuid as company_id, |
| p.fk_salutation_id, |
| p.fk_ref_person_type_id, |
| p.title, |
| p.first_name, |
| p.last_name, |
| null as department |
| FROM tbl_contact_person p |
| JOIN tbl_company c ON c.id = p.fk_company_id |
| JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id |
| |
| UNION |
| |
| SELECT id, |
| COALESCE(last_name, '') |
| || CASE WHEN LENGTH(COALESCE(last_name, '')) > 0 AND LENGTH(COALESCE(first_name, '')) > 0 THEN ', ' ELSE '' END |
| || COALESCE(first_name, '') as name, |
| fk_contact_id, |
| null as company_name, |
| null as company_type, |
| null as company_id, |
| fk_salutation_id, |
| fk_ref_person_type_id, |
| title, |
| first_name, |
| last_name, |
| department |
| FROM tbl_internal_person |
| |
| UNION |
| |
| SELECT id, |
| COALESCE(last_name, '') |
| || CASE WHEN LENGTH(COALESCE(last_name, '')) > 0 AND LENGTH(COALESCE(first_name, '')) > 0 THEN ', ' ELSE '' END |
| || COALESCE(first_name, '') as name, |
| fk_contact_id, |
| null as company_name, |
| null as company_type, |
| null as company_id, |
| fk_salutation_id, |
| fk_ref_person_type_id, |
| title, |
| first_name, |
| last_name, |
| null as department |
| FROM tbl_external_person |
| ) g |
| ON g.fk_contact_id = c.ID; |
| |
| |
| |
| CREATE VIEW VW_DETAILED_CONTACT |
| AS |
| SELECT c.id, |
| c.uuid, |
| c.name, |
| c.contact_type, |
| c.fk_contact_id, |
| c.company_name, |
| c.company_type, |
| c.company_id, |
| c.anonymized, |
| s.uuid as salutation_uuid, |
| t.uuid as person_type_uuid, |
| c.title, |
| c.first_name, |
| c.last_name, |
| c.department, |
| c.note, |
| s.type as salutation_type, |
| t.type as person_type, |
| COALESCE(a.community, '') || |
| CASE WHEN LENGTH(COALESCE(a.street, '') || COALESCE(a.housenumber, '')) = 0 THEN '' ELSE ', ' END || |
| COALESCE(a.street, '') || |
| CASE WHEN LENGTH(COALESCE(a.housenumber, '')) = 0 THEN '' ELSE ' ' END || |
| COALESCE(a.housenumber, '') as main_address, |
| com.communication_data as email, |
| |
| UPPER( |
| COALESCE(c.name, '') || '|@|' |
| || COALESCE(company_name, '') || '|@|' |
| || COALESCE(c.company_type, '') || '|@|' |
| || COALESCE(c.title, '') || '|@|' |
| || COALESCE(c.first_name, '') || '|@|' |
| || COALESCE(c.last_name, '') || '|@|' |
| || COALESCE(c.department, '') || '|@|' |
| || COALESCE(c.note, '') || '|@|' |
| || COALESCE(s.type, '') || '|@|' |
| || COALESCE(t.type, '') || '|@|' |
| || COALESCE(a.street, '') || '|@|' |
| || COALESCE(a.housenumber, '') || '|@|' |
| || COALESCE(a.community, '') || '|@|' |
| || COALESCE(com.communication_data, '') |
| )as searchfield |
| FROM VW_GENERAL_CONTACT c |
| LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id |
| LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = 1 |
| LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id |
| LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id ) |
| LEFT OUTER JOIN ref_communication_type rct ON (rct.id = com.fk_communication_type) |
| WHERE COALESCE( rct.type_email, 1) = 1; |
| |
| |
| |
| |
| |
