DROP TABLE OMALERT.OM_ALERT_LOG CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_LOG (
  TEXT_LOG     VARCHAR2 (2000),
  LOG_DATE     DATE,
  LOG_TRIGGER  VARCHAR2 (100),
  LOG_ACTION   VARCHAR2 (30) ) ;


DROP TABLE OMALERT.OM_ALERT_PARAMETERS CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_PARAMETERS (
  PARAM_ID     NUMBER (10)   NOT NULL,
  PARAM_NAME   VARCHAR2 (100)  NOT NULL,
  PARAM_VALUE  VARCHAR2 (100)  NOT NULL,
  PARAM_DESC   VARCHAR2 (100),
  CONSTRAINT PK_OMALERT_PARAM
  PRIMARY KEY ( PARAM_ID ) ) ;


ALTER TABLE OMALERT.OM_ALERT_PARAMETERS ADD  CONSTRAINT UK_OMALERT_PARAM
 UNIQUE (PARAM_NAME) ;




GRANT ALTER ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_PARAMETERS TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_SENDER CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_SENDER (
  SENDER_ID            NUMBER (5)    NOT NULL,
  SENDER_NAME          VARCHAR2 (50)  NOT NULL,
  SENDER_ADDRESS_TYPE  VARCHAR2 (10)  NOT NULL,
  SENDER_ADDRESS       VARCHAR2 (100)  NOT NULL,
  SENDER_DESCRIPTION   VARCHAR2 (100),
  CONSTRAINT PK_OMALERT_SENDER
  PRIMARY KEY ( SENDER_ID ) ) ;


ALTER TABLE OMALERT.OM_ALERT_SENDER ADD  CONSTRAINT UK_OMALERT_SENDER
 UNIQUE (SENDER_NAME, SENDER_ADDRESS_TYPE, SENDER_ADDRESS) ;




GRANT ALTER ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_SENDER TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_TEXT CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_TEXT (
  TEXT_ID          NUMBER (10)   NOT NULL,
  TEXT_TITLE       VARCHAR2 (100)  NOT NULL,
  TEXT_BODY        VARCHAR2 (800)  NOT NULL,
  TEXT_TABLE_NAME  VARCHAR2 (100),
  CONSTRAINT OM_ALERT_TXT_PK
  PRIMARY KEY ( TEXT_ID ) ) ;


GRANT ALTER ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_TEXT TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_TEXT_VARIABLES CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_TEXT_VARIABLES (
  VAR_ID             NUMBER (10)   NOT NULL,
  VAR_VALUE          VARCHAR2 (1000)  NOT NULL,
  VAR_NAME           VARCHAR2 (50)  NOT NULL,
  VAR_DISPLAY_TABLE  VARCHAR2 (100),
  VAR_JOIN_TABLE     VARCHAR2 (100),
  CONSTRAINT OM_ALERT_TXT_VAR_PK
  PRIMARY KEY ( VAR_ID ) ) ;


ALTER TABLE OMALERT.OM_ALERT_TEXT_VARIABLES ADD  CONSTRAINT OM_ALERT_TXT_VAR_UK
 UNIQUE (VAR_NAME) ;




GRANT ALTER ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_TEXT_VARIABLES TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_TRIGGER CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_TRIGGER (
  TRIGGER_ID              NUMBER (10)   NOT NULL,
  TRIGGER_NAME            VARCHAR2 (30)  NOT NULL,
  TRIGGER_DESCRIPTION     VARCHAR2 (100),
  TEXT_ID                 NUMBER (10)   NOT NULL,
  SENDER_ID               NUMBER (10)   NOT NULL,
  TRIGGER_SCHEMA          VARCHAR2 (30)  NOT NULL,
  TRIGGER_INSERT          NUMBER (1),
  TRIGGER_UPDATE          NUMBER (1),
  TRIGGER_DELETE          NUMBER (1),
  TRIGGER_EACHROW         NUMBER (1),
  TRIGGER_WHEN            VARCHAR2 (1000),
  TRIGGER_AFTERBEFORE     NUMBER (1),
  TRIGGER_BODY            VARCHAR2 (4000),
  TRIGGER_COLUMN_SHUTTLE  VARCHAR2 (1000),
  CONSTRAINT OM_ALERT_TRIGGER_PK
  PRIMARY KEY ( TRIGGER_ID ) ) ;


ALTER TABLE OMALERT.OM_ALERT_TRIGGER ADD  CONSTRAINT OM_ALERT_TRIGGER_NAME_UK
 UNIQUE (TRIGGER_SCHEMA, TRIGGER_NAME) ;




GRANT ALTER ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_TRIGGER TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_TRIGGER_COLUMN CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_TRIGGER_COLUMN (
  TRIGGER_COLUMN_ID        NUMBER (10),
  TRIGGER_COLUMN_NAME      VARCHAR2 (30),
  TRIGGER_COLUMN_COND_SEQ  NUMBER (3),
  TRIGGER_TABLE_ID         NUMBER (10) ) ;


GRANT ALTER ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_TRIGGER_COLUMN TO PUBLIC;


DROP TABLE OMALERT.OM_ALERT_TRIGGER_TABLE CASCADE CONSTRAINTS ;

CREATE TABLE OMALERT.OM_ALERT_TRIGGER_TABLE (
  TRIGGER_TABLE_ID      NUMBER (10)   NOT NULL,
  TRIGGER_TABLE_NAME    VARCHAR2 (30)  NOT NULL,
  TRIGGER_TABLE_SCHEMA  VARCHAR2 (30)  NOT NULL,
  TRIGGER_TABLE_TYPE    VARCHAR2 (20)  NOT NULL,
  TRIGGER_ID            NUMBER (10)   NOT NULL,
  CONSTRAINT OM_ALERT_TRG_TABLE_PK
  PRIMARY KEY ( TRIGGER_TABLE_ID ) ) ;


ALTER TABLE OMALERT.OM_ALERT_TRIGGER_TABLE ADD  CONSTRAINT OM_ALERT_TRG_UK
 UNIQUE (TRIGGER_TABLE_SCHEMA, TRIGGER_TABLE_NAME, TRIGGER_TABLE_TYPE, TRIGGER_ID) ;




GRANT ALTER ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT DELETE ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT INDEX ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT INSERT ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT SELECT ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT UPDATE ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


GRANT REFERENCES ON OMALERT.OM_ALERT_TRIGGER_TABLE TO PUBLIC;


ALTER TABLE OMALERT.OM_ALERT_TRIGGER ADD  CONSTRAINT OM_ALERT_TRIGGER_SENDER
 FOREIGN KEY (SENDER_ID)
  REFERENCES OMALERT.OM_ALERT_SENDER (SENDER_ID) ;


ALTER TABLE OMALERT.OM_ALERT_TRIGGER ADD  CONSTRAINT OM_ALERT_TRIGGER_TEXT
 FOREIGN KEY (TEXT_ID)
  REFERENCES OMALERT.OM_ALERT_TEXT (TEXT_ID) ;


ALTER TABLE OMALERT.OM_ALERT_TRIGGER_TABLE ADD  CONSTRAINT OM_ALERT_TRG_TRIGGER
 FOREIGN KEY (TRIGGER_ID)
  REFERENCES OMALERT.OM_ALERT_TRIGGER (TRIGGER_ID) ;





DROP PUBLIC SYNONYM OM_ALERT_LOG;

DROP PUBLIC SYNONYM OM_ALERT_PARAMETERS;

DROP PUBLIC SYNONYM OM_ALERT_SENDER;

DROP PUBLIC SYNONYM OM_ALERT_TEXT;

DROP PUBLIC SYNONYM OM_ALERT_TEXT_VARIABLES;

DROP PUBLIC SYNONYM OM_ALERT_TRIGGER;

DROP PUBLIC SYNONYM OM_ALERT_TRIGGER_COLUMN;

DROP PUBLIC SYNONYM OM_ALERT_TRIGGER_TABLE;



CREATE PUBLIC SYNONYM OM_ALERT_LOG FOR OMALERT.OM_ALERT_LOG;

CREATE PUBLIC SYNONYM OM_ALERT_PARAMETERS FOR OMALERT.OM_ALERT_PARAMETERS;

CREATE PUBLIC SYNONYM OM_ALERT_SENDER FOR OMALERT.OM_ALERT_SENDER;

CREATE PUBLIC SYNONYM OM_ALERT_TEXT FOR OMALERT.OM_ALERT_TEXT;

CREATE PUBLIC SYNONYM OM_ALERT_TEXT_VARIABLES FOR OMALERT.OM_ALERT_TEXT_VARIABLES;

CREATE PUBLIC SYNONYM OM_ALERT_TRIGGER FOR OMALERT.OM_ALERT_TRIGGER;

CREATE PUBLIC SYNONYM OM_ALERT_TRIGGER_COLUMN FOR OMALERT.OM_ALERT_TRIGGER_COLUMN;

CREATE PUBLIC SYNONYM OM_ALERT_TRIGGER_TABLE FOR OMALERT.OM_ALERT_TRIGGER_TABLE;

