| |
| |
| |
| |
| |
| CREATE SEQUENCE public.HTBL_RESP_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 354 |
| CACHE 1; |
| ALTER TABLE public.HTBL_RESPONSIBILITY_ID_SEQ |
| OWNER TO btbservice; |
| |
| |
| CREATE SEQUENCE public.REF_BRANCH_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 5 |
| CACHE 1; |
| ALTER TABLE public.REF_BRANCH_ID_SEQ |
| OWNER TO btbservice; |
| |
| CREATE SEQUENCE public.REF_GRID_TERRITORY_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 2 |
| CACHE 1; |
| ALTER TABLE public.REF_GRID_TERRITORY_ID_SEQ |
| OWNER TO btbservice; |
| |
| CREATE SEQUENCE public.REF_NOTIF_STATUS_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 5 |
| CACHE 1; |
| ALTER TABLE public.REF_NOTIF_STATUS_ID_SEQ |
| OWNER TO btbservice; |
| |
| CREATE SEQUENCE public.TBL_NOTIF_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 1395 |
| CACHE 1; |
| ALTER TABLE public.TBL_NOTIF_ID_SEQ |
| OWNER TO btbservice; |
| |
| CREATE SEQUENCE public.TBL_NOTIF_INCIDENT_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 81 |
| CACHE 1; |
| ALTER TABLE public.TBL_NOTIF_INCIDENT_ID_SEQ |
| OWNER TO btbservice; |
| |
| CREATE SEQUENCE public.TBL_RESP_ID_SEQ |
| INCREMENT 1 |
| MINVALUE 1 |
| MAXVALUE 9223372036854775807 |
| START 21 |
| CACHE 1; |
| ALTER TABLE public.TBL_RESP_ID_SEQ |
| OWNER TO btbservice; |
| |
| |
| CREATE TABLE public.REF_BRANCH |
| ( |
| id integer NOT NULL DEFAULT nextval('ref_branch_id_seq'::regclass), |
| name character varying(50) NOT NULL, |
| description character varying(255), |
| CONSTRAINT REF_BRANCH_PKEY PRIMARY KEY (id) |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.REF_BRANCH |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.REF_BRANCH TO btbservice; |
| |
| |
| CREATE TABLE public.REF_GRID_TERRITORY |
| ( |
| id integer NOT NULL DEFAULT nextval('ref_grid_territory_id_seq'::regclass), |
| name character varying(50) NOT NULL, |
| description character varying(255), |
| fk_ref_master integer NOT NULL, |
| CONSTRAINT REF_GRID_TERRITORY_PKEY PRIMARY KEY (id), |
| CONSTRAINT FK_REF_GRID_TERRITORY_SELF FOREIGN KEY (fk_ref_master) |
| REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE CASCADE |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.REF_GRID_TERRITORY |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.REF_GRID_TERRITORY TO btbservice; |
| |
| -- Index: public.fki_fk_grid_territory_self |
| |
| -- DROP INDEX public.fki_fk_grid_territory_self; |
| |
| CREATE INDEX FKI_FK_GRID_TERRITORY_SELF |
| ON public.REF_GRID_TERRITORY |
| USING btree |
| (fk_ref_master); |
| |
| -- Index: public.ref_grid_territory_description_unique |
| |
| -- DROP INDEX public.ref_grid_territory_description_unique; |
| |
| CREATE UNIQUE INDEX REF_GRID_TERRITORY_DESCRIPTION_UNIQUE |
| ON public.REF_GRID_TERRITORY |
| USING btree |
| (description COLLATE pg_catalog."default"); |
| |
| |
| CREATE TABLE public.REF_NOTIFICATION_STATUS |
| ( |
| id integer NOT NULL DEFAULT nextval('ref_notif_status_id_seq'::regclass), |
| name character varying(50) NOT NULL, |
| CONSTRAINT REF_NOTIFICATION_STATUS_PKEY PRIMARY KEY (id) |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.REF_NOTIFICATION_STATUS |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.REF_NOTIFICATION_STATUS TO btbservice; |
| |
| |
| CREATE TABLE public.REF_VERSION |
| ( |
| id integer NOT NULL, |
| version character varying(100) NOT NULL, |
| CONSTRAINT ref_version_pkey PRIMARY KEY (id) |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.REF_VERSION |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.REF_VERSION TO btbservice; |
| |
| |
| CREATE TABLE public.TBL_NOTIFICATION |
| ( |
| id integer NOT NULL DEFAULT nextval('tbl_notif_id_seq'::regclass), |
| incident_id integer DEFAULT nextval('tbl_notif_incident_id_seq'::regclass), |
| version integer NOT NULL DEFAULT 0, |
| fk_ref_branch integer, |
| notification_text character varying(200) NOT NULL, |
| free_text character varying(1000), |
| free_text_extended character varying(1000), |
| fk_ref_notification_status integer NOT NULL, |
| responsibility_forwarding character varying(100), |
| reminder_date timestamp without time zone, |
| expected_finished_date timestamp without time zone, |
| responsibility_control_point character varying(100), |
| begin_date timestamp without time zone NOT NULL, |
| finished_date timestamp without time zone, |
| create_user character varying(100) NOT NULL, |
| create_date timestamp without time zone NOT NULL, |
| mod_user character varying(100), |
| mod_date timestamp without time zone, |
| fk_ref_grid_territory integer, |
| admin_flag boolean NOT NULL DEFAULT false, |
| CONSTRAINT TBL_NOTIFICATION_PKEY PRIMARY KEY (id), |
| CONSTRAINT FK_NOTIFICATION_FK_BRANCH FOREIGN KEY (fk_ref_branch) |
| REFERENCES public.REF_BRANCH (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION, |
| CONSTRAINT FK_NOTIFICATION_FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory) |
| REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE CASCADE, |
| CONSTRAINT FK_NOTIFICATION_FK_STATUS FOREIGN KEY (fk_ref_notification_status) |
| REFERENCES public.REF_NOTIFICATION_STATUS (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.TBL_NOTIFICATION |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.TBL_NOTIFICATION TO btbservice; |
| |
| -- Index: public.create_date_idx |
| |
| -- DROP INDEX public.create_date_idx; |
| |
| CREATE INDEX CREATE_DATE_IDX |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (create_date); |
| |
| -- Index: public.fki_notification_fk_branch |
| |
| -- DROP INDEX public.fki_notification_fk_branch; |
| |
| CREATE INDEX FKI_NOTIFICATION_FK_BRANCH |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (fk_ref_branch); |
| |
| -- Index: public.fki_notification_fk_grid_territory |
| |
| -- DROP INDEX public.fki_notification_fk_grid_territory; |
| |
| CREATE INDEX FKI_NOTIFICATION_FK_GRID_TERRITORY |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (fk_ref_grid_territory); |
| |
| -- Index: public.fki_notification_fk_status |
| |
| -- DROP INDEX public.fki_notification_fk_status; |
| |
| CREATE INDEX FKI_NOTIFICATION_FK_STATUS |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (fk_ref_notification_status); |
| |
| -- Index: public.mod_date_idx |
| |
| -- DROP INDEX public.mod_date_idx; |
| |
| CREATE INDEX MOD_DATE_IDX |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (mod_date); |
| |
| -- Index: public.responsibility_forwarding_idx |
| |
| -- DROP INDEX public.responsibility_forwarding_idx; |
| |
| CREATE INDEX RESPONSIBILITY_FORWARDING_IDX |
| ON public.TBL_NOTIFICATION |
| USING btree |
| (responsibility_forwarding COLLATE pg_catalog."default"); |
| |
| -- Index: public.tbl_notification_incident_version_unique |
| |
| -- DROP INDEX public.tbl_notification_incident_version_unique; |
| |
| CREATE UNIQUE INDEX TBL_NOTIFICATION_INCIDENT_VERSION_UNIQUE |
| ON public.tbl_notification |
| USING btree |
| (incident_id, version); |
| |
| |
| CREATE OR REPLACE FUNCTION public.TBL_NOTIFICATION_INCIDENT_TRG() |
| RETURNS trigger AS |
| $BODY$ |
| BEGIN |
| |
| IF NEW.incident_id IS NULL THEN |
| NEW.incident_id := NEW.id; |
| END IF; |
| |
| RETURN NEW; |
| END; |
| $BODY$ |
| LANGUAGE plpgsql VOLATILE |
| COST 100; |
| ALTER FUNCTION public.TBL_NOTIFICATION_INCIDENT_TRG() |
| OWNER TO btbservice; |
| |
| |
| -- Trigger: tbl_notification_incident_trg on public.tbl_notification |
| |
| -- DROP TRIGGER tbl_notification_incident_trg ON public.tbl_notification; |
| |
| CREATE TRIGGER TBL_NOTIFICATION_INCIDENT_TRG |
| BEFORE INSERT |
| ON public.tbl_notification |
| FOR EACH ROW |
| EXECUTE PROCEDURE public.TBL_NOTIFICATION_INCIDENT_TRG(); |
| |
| |
| CREATE TABLE public.TBL_RESPONSIBILITY |
| ( |
| id integer NOT NULL DEFAULT nextval('tbl_resp_id_seq'::regclass), |
| fk_ref_grid_territory integer NOT NULL, |
| fk_ref_branch integer NOT NULL, |
| responsible_user character varying(100) NOT NULL, |
| new_responsible_user character varying(100), |
| create_user character varying(100) NOT NULL, |
| create_date timestamp without time zone NOT NULL, |
| mod_user character varying(100), |
| mod_date timestamp without time zone, |
| CONSTRAINT TBL_RESPONSIBILITY_PKEY PRIMARY KEY (id), |
| CONSTRAINT FK_BRANCH FOREIGN KEY (fk_ref_branch) |
| REFERENCES public.REF_BRANCH (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION, |
| CONSTRAINT FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory) |
| REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.TBL_RESPONSIBILITY |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.TBL_RESPONSIBILITY TO btbservice; |
| |
| -- Index: public.tbl_responsibility_territory_branch_user_unique |
| |
| -- DROP INDEX public.tbl_responsibility_territory_branch_user_unique; |
| |
| CREATE UNIQUE INDEX TBL_RESPONSIBILITY_TERRITORY_BRANCH_USER_UNIQUE |
| ON public.TBL_RESPONSIBILITY |
| USING btree |
| (fk_ref_grid_territory, fk_ref_branch); |
| |
| |
| CREATE TABLE public.HTBL_RESPONSIBILITY |
| ( |
| id integer NOT NULL DEFAULT nextval('htbl_resp_id_seq'::regclass), |
| fk_ref_grid_territory integer NOT NULL, |
| fk_ref_branch integer NOT NULL, |
| transfer_date timestamp without time zone NOT NULL, |
| transaction_id integer NOT NULL, |
| responsible_user character varying(100) NOT NULL, |
| former_responsible_user character varying(100), |
| create_user character varying(100) NOT NULL, |
| create_date timestamp without time zone NOT NULL, |
| mod_user character varying(100), |
| mod_date timestamp without time zone, |
| CONSTRAINT HTBL_RESPONSIBILITY_PKEY PRIMARY KEY (id), |
| CONSTRAINT fk_branch FOREIGN KEY (fk_ref_branch) |
| REFERENCES public.REF_BRANCH (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION, |
| CONSTRAINT FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory) |
| REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE |
| ON UPDATE NO ACTION ON DELETE NO ACTION |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.HTBL_RESPONSIBILITY |
| OWNER TO btbservice; |
| |
| -- Index: public.htbl_responsibility_territory_branch_user_transaction_id_unique |
| |
| -- DROP INDEX public.htbl_responsibility_territory_branch_user_transaction_id_unique; |
| |
| CREATE UNIQUE INDEX HTBL_RESPONSIBILITY_TERRITORY_BRANCH_USER_TRANSACTION_ID_UNIQUE |
| ON public.HTBL_RESPONSIBILITY |
| USING btree |
| (fk_ref_grid_territory, fk_ref_branch, transaction_id); |
| |
| |
| CREATE OR REPLACE VIEW public.VIEW_ACTIVE_NOTIFICATION AS |
| SELECT s.id, |
| s.incident_id, |
| s.version, |
| s.fk_ref_branch, |
| s.notification_text, |
| s.free_text, |
| s.free_text_extended, |
| s.fk_ref_notification_status, |
| s.responsibility_forwarding, |
| s.reminder_date, |
| s.expected_finished_date, |
| s.responsibility_control_point, |
| s.begin_date, |
| s.finished_date, |
| s.create_user, |
| s.create_date, |
| s.mod_user, |
| s.mod_date, |
| s.fk_ref_grid_territory, |
| s.admin_flag |
| FROM ( SELECT tbl_notification.id, |
| tbl_notification.incident_id, |
| tbl_notification.version, |
| tbl_notification.fk_ref_branch, |
| tbl_notification.notification_text, |
| tbl_notification.free_text, |
| tbl_notification.free_text_extended, |
| tbl_notification.fk_ref_notification_status, |
| tbl_notification.responsibility_forwarding, |
| tbl_notification.reminder_date, |
| tbl_notification.expected_finished_date, |
| tbl_notification.responsibility_control_point, |
| tbl_notification.begin_date, |
| tbl_notification.finished_date, |
| tbl_notification.create_user, |
| tbl_notification.create_date, |
| tbl_notification.mod_user, |
| tbl_notification.mod_date, |
| tbl_notification.fk_ref_grid_territory, |
| tbl_notification.admin_flag, |
| rank() OVER (PARTITION BY tbl_notification.incident_id ORDER BY tbl_notification.version DESC) AS rank |
| FROM TBL_NOTIFICATION) s |
| WHERE s.rank = 1; |
| |
| ALTER TABLE public.VIEW_ACTIVE_NOTIFICATION |
| OWNER TO btbservice; |
| GRANT ALL ON TABLE public.VIEW_ACTIVE_NOTIFICATION TO btbservice; |
| |
| |
| |
| |
| |
| |
| |
| |
| |