ALTER TABLE EESRV.SERVICE_USER_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_USER_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_USER_H
(
  SERVICE_USER_H_ID      NUMBER(20)             NOT NULL,
  SERVICE_USER_ID        NUMBER(20)             NOT NULL,
  USERNAME               VARCHAR2(30 BYTE)      NOT NULL,
  PASSWORD               VARCHAR2(128 BYTE),
  ACTIVE_FLAG            NUMBER(1)              DEFAULT 0                     NOT NULL,
  CONTACT_NAME           VARCHAR2(30 BYTE),
  CONTACT_PHONE          VARCHAR2(30 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_USER_H IS 'Credentials of the service user (application login info). ';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.SERVICE_USER_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.SERVICE_USER_ID IS 'Primary key from base table.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.ACTIVE_FLAG IS 'Whether this user is active.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.SERVICE_USER_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.SRVUSERH_PK ON EESRV.SERVICE_USER_H
(SERVICE_USER_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM SERVICE_USER_H FOR EESRV.SERVICE_USER_H;


ALTER TABLE EESRV.SERVICE_USER_H ADD (
  CONSTRAINT SERV_USR_H_CK
  CHECK (ACTIVE_FLAG BETWEEN 0 AND 0 OR ACTIVE_FLAG BETWEEN 1 AND 1)
  ENABLE VALIDATE,
  CONSTRAINT SRVUSERH_PK
  PRIMARY KEY
  (SERVICE_USER_H_ID)
  USING INDEX EESRV.SRVUSERH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_USER_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_USER_H TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_USER
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_USER CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_USER
(
  SERVICE_USER_ID        NUMBER(20)             NOT NULL,
  USERNAME               VARCHAR2(30 BYTE)      NOT NULL,
  PASSWORD               VARCHAR2(128 BYTE),
  ACTIVE_FLAG            NUMBER(1)              DEFAULT 0                     NOT NULL,
  CONTACT_NAME           VARCHAR2(30 BYTE),
  CONTACT_PHONE          VARCHAR2(30 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_USER IS 'Credentials of the service user (application login info). ';

COMMENT ON COLUMN EESRV.SERVICE_USER.ACTIVE_FLAG IS 'Whether this user is active.';

COMMENT ON COLUMN EESRV.SERVICE_USER.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_USER.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_USER.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_USER.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_USER.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE UNIQUE INDEX EESRV.SERVICE_USER_USERNAME_UK ON EESRV.SERVICE_USER
(USERNAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX EESRV.SRVUSER_PK ON EESRV.SERVICE_USER
(SERVICE_USER_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.SERVICE_USER_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.SERVICE_USER
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into SERVICE_USER_H
        (
        SERVICE_USER_H_ID,
        SERVICE_USER_ID,
        USERNAME,
        PASSWORD,
        ACTIVE_FLAG,
        CONTACT_NAME,
        CONTACT_PHONE,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_USER_H_S.nextval,
        :old.SERVICE_USER_ID,
        :old.USERNAME,
        :old.PASSWORD,
        :old.ACTIVE_FLAG,
        :old.CONTACT_NAME,
        :old.CONTACT_PHONE,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into SERVICE_USER_H
        (
        SERVICE_USER_H_ID,
        SERVICE_USER_ID,
        USERNAME,
        PASSWORD,
        ACTIVE_FLAG,
        CONTACT_NAME,
        CONTACT_PHONE,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_USER_H_S.nextval,
        :new.SERVICE_USER_ID,
        :new.USERNAME,
       :new.PASSWORD,
       :new.ACTIVE_FLAG,
       :new.CONTACT_NAME,
       :new.CONTACT_PHONE,
       :new.RECORD_CREATED_BY,
       :new.RECORD_CREATED_DATE,
      :new.RECORD_MODIFIED_BY,
       :new.RECORD_MODIFIED_DATE,
       :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end SERVICE_USER_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM SERVICE_USER FOR EESRV.SERVICE_USER;


ALTER TABLE EESRV.SERVICE_USER ADD (
  CONSTRAINT SERV_USR_CK
  CHECK (ACTIVE_FLAG BETWEEN 0 AND 0 OR ACTIVE_FLAG BETWEEN 1 AND 1)
  ENABLE VALIDATE,
  CONSTRAINT SRVUSER_PK
  PRIMARY KEY
  (SERVICE_USER_ID)
  USING INDEX EESRV.SRVUSER_PK
  ENABLE VALIDATE,
  CONSTRAINT SERVICE_USER_USERNAME_UK
  UNIQUE (USERNAME)
  USING INDEX EESRV.SERVICE_USER_USERNAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_USER TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_USER TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_REQUEST_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_REQUEST_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_REQUEST_H
(
  SERVICE_REQUEST_H_ID   NUMBER(20)             NOT NULL,
  SERVICE_REQUEST_ID     NUMBER(20)             NOT NULL,
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_REQUEST_H IS 'Role of a service account';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.SERVICE_REQUEST_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.SERVICE_REQUEST_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.NAME IS 'Name of the SERVICE / ROLE combination';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.DESCRIPTION IS 'Descvription of the SERVICE / ROLE combination';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.SRVROLH_PK ON EESRV.SERVICE_REQUEST_H
(SERVICE_REQUEST_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM SERVICE_REQUEST_H FOR EESRV.SERVICE_REQUEST_H;


ALTER TABLE EESRV.SERVICE_REQUEST_H ADD (
  CONSTRAINT SRVROLH_PK
  PRIMARY KEY
  (SERVICE_REQUEST_H_ID)
  USING INDEX EESRV.SRVROLH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_REQUEST_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_REQUEST_H TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT_H
(
  SERVICE_REQ_ASSIGN_H_ID        NUMBER(20)     NOT NULL,
  SERVICE_REQUEST_ASSIGNMENT_ID  NUMBER(20)     NOT NULL,
  SERVICE_REQUEST_ID             NUMBER(20),
  SERVICE_USER_ID                NUMBER(20),
  RECORD_CREATED_BY              VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE            DATE           NOT NULL,
  RECORD_MODIFIED_BY             VARCHAR2(30 BYTE) NOT NULL,
  RECORD_MODIFIED_DATE           DATE           NOT NULL,
  RECORD_MODIFIED_COUNT          NUMBER(10)     NOT NULL,
  HISTORY_CREATED_DATE           DATE,
  TRANSACTION_TYPE_CODE          VARCHAR2(1 BYTE) NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT_H IS 'Mapping of the service user to the roles and context. ';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.SERVICE_REQ_ASSIGN_H_ID IS 'The primary key, of the base table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.SERVICE_REQUEST_ASSIGNMENT_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.SRVROLASSH_PK ON EESRV.SERVICE_REQUEST_ASSIGNMENT_H
(SERVICE_REQ_ASSIGN_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM SERVICE_REQUEST_ASSIGNMENT_H FOR EESRV.SERVICE_REQUEST_ASSIGNMENT_H;


ALTER TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT_H ADD (
  CONSTRAINT SRVROLASSH_PK
  PRIMARY KEY
  (SERVICE_REQ_ASSIGN_H_ID)
  USING INDEX EESRV.SRVROLASSH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_REQUEST_ASSIGNMENT_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_REQUEST_ASSIGNMENT_H TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT
(
  SERVICE_REQUEST_ASSIGNMENT_ID  NUMBER(20)     NOT NULL,
  SERVICE_USER_ID                NUMBER(20),
  SERVICE_REQUEST_ID             NUMBER(20),
  RECORD_CREATED_BY              VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE            DATE           NOT NULL,
  RECORD_MODIFIED_BY             VARCHAR2(30 BYTE) NOT NULL,
  RECORD_MODIFIED_DATE           DATE           NOT NULL,
  RECORD_MODIFIED_COUNT          NUMBER(10)     NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT IS 'Mapping of the service user to the roles and context. ';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.SERVICE_REQUEST_ASSIGNMENT_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST_ASSIGNMENT.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE INDEX EESRV.IX_RELATIONSHIP11 ON EESRV.SERVICE_REQUEST_ASSIGNMENT
(SERVICE_REQUEST_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.IX_RELATIONSHIP9 ON EESRV.SERVICE_REQUEST_ASSIGNMENT
(SERVICE_USER_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX EESRV.SRVROLASS_PK ON EESRV.SERVICE_REQUEST_ASSIGNMENT
(SERVICE_REQUEST_ASSIGNMENT_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.SERV_REQUEST_ASSIGN_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.SERVICE_REQUEST_ASSIGNMENT
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into SERVICE_REQUEST_ASSIGNMENT_H
        (
        SERVICE_REQ_ASSIGN_H_ID,
        SERVICE_REQUEST_ASSIGNMENT_ID,
        SERVICE_REQUEST_ID,
        SERVICE_USER_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_REQUEST_ASSIGNMENT_H_S.nextval,
        :old.SERVICE_REQUEST_ASSIGNMENT_ID,
        :old.SERVICE_REQUEST_ID,
        :old.SERVICE_USER_ID,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into SERVICE_REQUEST_ASSIGNMENT_H
        (
        SERVICE_REQ_ASSIGN_H_ID,
        SERVICE_REQUEST_ASSIGNMENT_ID,
        SERVICE_REQUEST_ID,
        SERVICE_USER_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_REQUEST_ASSIGNMENT_H_S.nextval,
        :new.SERVICE_REQUEST_ASSIGNMENT_ID,
        :new.SERVICE_REQUEST_ID,
        :new.SERVICE_USER_ID,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end SERV_REQUEST_ASSIGN_AUDIR_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM SERVICE_REQUEST_ASSIGNMENT FOR EESRV.SERVICE_REQUEST_ASSIGNMENT;


ALTER TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT ADD (
  CONSTRAINT SRVROLASS_PK
  PRIMARY KEY
  (SERVICE_REQUEST_ASSIGNMENT_ID)
  USING INDEX EESRV.SRVROLASS_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.SERVICE_REQUEST_ASSIGNMENT ADD (
  CONSTRAINT RELATIONSHIP11 
  FOREIGN KEY (SERVICE_REQUEST_ID) 
  REFERENCES EESRV.SERVICE_REQUEST (SERVICE_REQUEST_ID)
  ENABLE VALIDATE,
  CONSTRAINT RELATIONSHIP9 
  FOREIGN KEY (SERVICE_USER_ID) 
  REFERENCES EESRV.SERVICE_USER (SERVICE_USER_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_REQUEST_ASSIGNMENT TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_REQUEST_ASSIGNMENT TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_REQUEST
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_REQUEST CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_REQUEST
(
  SERVICE_REQUEST_ID     NUMBER(20)             NOT NULL,
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_REQUEST IS 'Role of a service account';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.SERVICE_REQUEST_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.NAME IS 'Name of the SERVICE / ROLE combination';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.DESCRIPTION IS 'Descvription of the SERVICE / ROLE combination';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_REQUEST.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE UNIQUE INDEX EESRV.SERVICE_REQUEST_NAME_UK ON EESRV.SERVICE_REQUEST
(NAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX EESRV.SRVROL_PK ON EESRV.SERVICE_REQUEST
(SERVICE_REQUEST_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.SERVICE_REQUEST_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.SERVICE_REQUEST
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into SERVICE_REQUEST_H
        (
        SERVICE_REQUEST_H_ID,
        SERVICE_REQUEST_ID,
        NAME,
        DESCRIPTION,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_REQUEST_H_S.nextval,
        :old.SERVICE_REQUEST_ID,
        :old.NAME,
        :old.DESCRIPTION,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into SERVICE_REQUEST_H
        (
        SERVICE_REQUEST_H_ID,
        SERVICE_REQUEST_ID,
        NAME,
        DESCRIPTION,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        SERVICE_REQUEST_H_S.nextval,
        :new.SERVICE_REQUEST_ID,
        :new.NAME,
        :new.DESCRIPTION,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end SERVICE_REQUEST_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM SERVICE_REQUEST FOR EESRV.SERVICE_REQUEST;


ALTER TABLE EESRV.SERVICE_REQUEST ADD (
  CONSTRAINT SRVROL_PK
  PRIMARY KEY
  (SERVICE_REQUEST_ID)
  USING INDEX EESRV.SRVROL_PK
  ENABLE VALIDATE,
  CONSTRAINT SERVICE_REQUEST_NAME_UK
  UNIQUE (NAME)
  USING INDEX EESRV.SERVICE_REQUEST_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_REQUEST TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_REQUEST TO EESRV_RW;
ALTER TABLE EESRV.SERVICE_LOG
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.SERVICE_LOG CASCADE CONSTRAINTS;

CREATE TABLE EESRV.SERVICE_LOG
(
  SERVICE_LOG_ID         NUMBER(20)             NOT NULL,
  SERVICE_USER_ID        NUMBER(20),
  REQUEST                VARCHAR2(4000 BYTE),
  RESPONSE_TIME          NUMBER(10,2),
  REQUEST_DATE           DATE,
  ERROR_TEXT             VARCHAR2(2000 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
COMPRESS FOR OLTP 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.SERVICE_LOG IS 'Log of a call to the service.

Implementation Note:
This will not be as big as the HL7_TRANSACTION_LOG table as there is no business logic on older records. Old records can be deleted periodically.';

COMMENT ON COLUMN EESRV.SERVICE_LOG.SERVICE_LOG_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.SERVICE_LOG.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.SERVICE_LOG.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.SERVICE_LOG.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.SERVICE_LOG.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.SERVICE_LOG.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE INDEX EESRV.SRVLOG_PK ON EESRV.SERVICE_LOG
(SERVICE_LOG_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.SRVUSER_FK_I ON EESRV.SERVICE_LOG
(SERVICE_USER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM SERVICE_LOG FOR EESRV.SERVICE_LOG;


ALTER TABLE EESRV.SERVICE_LOG ADD (
  CONSTRAINT SRVLOG_PK
  PRIMARY KEY
  (SERVICE_LOG_ID)
  USING INDEX EESRV.SRVLOG_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.SERVICE_LOG ADD (
  CONSTRAINT RELATIONSHIP16 
  FOREIGN KEY (SERVICE_USER_ID) 
  REFERENCES EESRV.SERVICE_USER (SERVICE_USER_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.SERVICE_LOG TO ADR;

GRANT SELECT ON EESRV.SERVICE_LOG TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.SERVICE_LOG TO EESRV_RW;
ALTER TABLE EESRV.REQUEST_GROUP_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.REQUEST_GROUP_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.REQUEST_GROUP_H
(
  REQUEST_GROUP_H_ID     NUMBER(20)             NOT NULL,
  REQUEST_GROUP_ID       NUMBER(20)             NOT NULL,
  SERVICE_REQUEST_ID     NUMBER(20),
  FIELD_GROUP_ID         NUMBER(20),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.REQUEST_GROUP_H IS 'Associates a ROLE with a GROUP of FIELDs.  ';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.REQUEST_GROUP_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.REQUEST_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.ROLGRPH_PK ON EESRV.REQUEST_GROUP_H
(REQUEST_GROUP_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM REQUEST_GROUP_H FOR EESRV.REQUEST_GROUP_H;


ALTER TABLE EESRV.REQUEST_GROUP_H ADD (
  CONSTRAINT ROLGRPH_PK
  PRIMARY KEY
  (REQUEST_GROUP_H_ID)
  USING INDEX EESRV.ROLGRPH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.REQUEST_GROUP_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.REQUEST_GROUP_H TO EESRV_RW;
ALTER TABLE EESRV.REQUEST_GROUP
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.REQUEST_GROUP CASCADE CONSTRAINTS;

CREATE TABLE EESRV.REQUEST_GROUP
(
  REQUEST_GROUP_ID       NUMBER(20)             NOT NULL,
  SERVICE_REQUEST_ID     NUMBER(20),
  FIELD_GROUP_ID         NUMBER(20),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.REQUEST_GROUP IS 'Associates a ROLE with a GROUP of FIELDs.  ';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.REQUEST_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.REQUEST_GROUP.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE INDEX EESRV.IX_RELATIONSHIP13 ON EESRV.REQUEST_GROUP
(SERVICE_REQUEST_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.IX_RELATIONSHIP14 ON EESRV.REQUEST_GROUP
(FIELD_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX EESRV.ROLGRP_PK ON EESRV.REQUEST_GROUP
(REQUEST_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.REQUEST_GROUP_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.REQUEST_GROUP
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into REQUEST_GROUP_H
        (
        REQUEST_GROUP_H_ID,
        REQUEST_GROUP_ID,
        SERVICE_REQUEST_ID,
        FIELD_GROUP_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        REQUEST_GROUP_H_S.nextval,
        :old.REQUEST_GROUP_ID,
        :old.SERVICE_REQUEST_ID,
        :old.FIELD_GROUP_ID,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into REQUEST_GROUP_H
        (
        REQUEST_GROUP_H_ID,
        REQUEST_GROUP_ID,
        SERVICE_REQUEST_ID,
        FIELD_GROUP_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        REQUEST_GROUP_H_S.nextval,
        :new.REQUEST_GROUP_ID,
        :new.SERVICE_REQUEST_ID,
        :new.FIELD_GROUP_ID,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end REQUEST_GROUP_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM REQUEST_GROUP FOR EESRV.REQUEST_GROUP;


ALTER TABLE EESRV.REQUEST_GROUP ADD (
  CONSTRAINT ROLGRP_PK
  PRIMARY KEY
  (REQUEST_GROUP_ID)
  USING INDEX EESRV.ROLGRP_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.REQUEST_GROUP ADD (
  CONSTRAINT RELATIONSHIP13 
  FOREIGN KEY (SERVICE_REQUEST_ID) 
  REFERENCES EESRV.SERVICE_REQUEST (SERVICE_REQUEST_ID)
  ENABLE VALIDATE,
  CONSTRAINT RELATIONSHIP14 
  FOREIGN KEY (FIELD_GROUP_ID) 
  REFERENCES EESRV.FIELD_GROUP (FIELD_GROUP_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.REQUEST_GROUP TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.REQUEST_GROUP TO EESRV_RW;
ALTER TABLE EESRV.REQUEST_FIELD_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.REQUEST_FIELD_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.REQUEST_FIELD_H
(
  REQUEST_FIELD_H_ID     NUMBER(20)             NOT NULL,
  REQUEST_FIELD_ID       NUMBER(20)             NOT NULL,
  FIELD_ID               NUMBER(20),
  SERVICE_REQUEST_ID     NUMBER(20),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.REQUEST_FIELD_H IS 'Associates a data element/field with a role.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.REQUEST_FIELD_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.REQUEST_FIELD_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.ROLFLD_H_PK ON EESRV.REQUEST_FIELD_H
(REQUEST_FIELD_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM REQUEST_FIELD_H FOR EESRV.REQUEST_FIELD_H;


ALTER TABLE EESRV.REQUEST_FIELD_H ADD (
  CONSTRAINT ROLFLD_H_PK
  PRIMARY KEY
  (REQUEST_FIELD_H_ID)
  USING INDEX EESRV.ROLFLD_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.REQUEST_FIELD_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.REQUEST_FIELD_H TO EESRV_RW;
ALTER TABLE EESRV.REQUEST_FIELD
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.REQUEST_FIELD CASCADE CONSTRAINTS;

CREATE TABLE EESRV.REQUEST_FIELD
(
  REQUEST_FIELD_ID       NUMBER(20)             NOT NULL,
  SERVICE_REQUEST_ID     NUMBER(20),
  FIELD_ID               NUMBER(20),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.REQUEST_FIELD IS 'Associates a data element/field with a role.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.REQUEST_FIELD_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.REQUEST_FIELD.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE INDEX EESRV.IX_RELATIONSHIP6 ON EESRV.REQUEST_FIELD
(SERVICE_REQUEST_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.IX_RELATIONSHIP7 ON EESRV.REQUEST_FIELD
(FIELD_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX EESRV.ROLFLD_PK ON EESRV.REQUEST_FIELD
(REQUEST_FIELD_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.REQUEST_FIELD_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.REQUEST_FIELD
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into REQUEST_FIELD_H
        (
        REQUEST_FIELD_H_ID,
        REQUEST_FIELD_ID,
        FIELD_ID,
        SERVICE_REQUEST_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
          )
        values
        (
        REQUEST_FIELD_H_S.nextval,
        :old.REQUEST_FIELD_ID,
        :old.FIELD_ID,
        :old.SERVICE_REQUEST_ID,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into REQUEST_FIELD_H
        (
        REQUEST_FIELD_H_ID,
        REQUEST_FIELD_ID,
        FIELD_ID,
        SERVICE_REQUEST_ID,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        REQUEST_FIELD_H_S.nextval,
        :new.REQUEST_FIELD_ID,
        :new.FIELD_ID,
        :new.SERVICE_REQUEST_ID,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end REQUEST_FIELD_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM REQUEST_FIELD FOR EESRV.REQUEST_FIELD;


ALTER TABLE EESRV.REQUEST_FIELD ADD (
  CONSTRAINT ROLFLD_PK
  PRIMARY KEY
  (REQUEST_FIELD_ID)
  USING INDEX EESRV.ROLFLD_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.REQUEST_FIELD ADD (
  CONSTRAINT RELATIONSHIP6 
  FOREIGN KEY (SERVICE_REQUEST_ID) 
  REFERENCES EESRV.SERVICE_REQUEST (SERVICE_REQUEST_ID)
  ENABLE VALIDATE,
  CONSTRAINT RELATIONSHIP7 
  FOREIGN KEY (FIELD_ID) 
  REFERENCES EESRV.FIELD (FIELD_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.REQUEST_FIELD TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.REQUEST_FIELD TO EESRV_RW;
ALTER TABLE EESRV.FIELD_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.FIELD_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.FIELD_H
(
  FIELD_H_ID             NUMBER(20)             NOT NULL,
  FIELD_ID               NUMBER(20)             NOT NULL,
  FIELD_GROUP_ID         NUMBER(20)             NOT NULL,
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  DEFAULT_FIELD_FLAG     NUMBER(1),
  TABLE_NAME             VARCHAR2(32 BYTE),
  COLUMN_NAME            VARCHAR2(32 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN EESRV.FIELD_H.FIELD_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.FIELD_H.FIELD_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.FIELD_H.FIELD_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.FIELD_H.NAME IS 'Name of the FIELD.';

COMMENT ON COLUMN EESRV.FIELD_H.DESCRIPTION IS 'Description of the FIELD.';

COMMENT ON COLUMN EESRV.FIELD_H.DEFAULT_FIELD_FLAG IS 'Default FIELD_ID for this field.';

COMMENT ON COLUMN EESRV.FIELD_H.TABLE_NAME IS 'Name of the table in the database.';

COMMENT ON COLUMN EESRV.FIELD_H.COLUMN_NAME IS 'Name of the column in the database. ';

COMMENT ON COLUMN EESRV.FIELD_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.FIELD_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.FIELD_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.FIELD_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.FIELD_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.FIELD_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.FLDH_PK ON EESRV.FIELD_H
(FIELD_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM FIELD_H FOR EESRV.FIELD_H;


ALTER TABLE EESRV.FIELD_H ADD (
  CONSTRAINT FLDH_PK
  PRIMARY KEY
  (FIELD_H_ID)
  USING INDEX EESRV.FLDH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.FIELD_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.FIELD_H TO EESRV_RW;
ALTER TABLE EESRV.FIELD_GROUP_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.FIELD_GROUP_H CASCADE CONSTRAINTS;

CREATE TABLE EESRV.FIELD_GROUP_H
(
  FIELD_GROUP_H_ID       NUMBER(20)             NOT NULL,
  FIELD_GROUP_ID         NUMBER(20)             NOT NULL,
  PARENT_GROUP_ID        NUMBER(20),
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  DEFAULT_GROUP_FLAG     NUMBER(1),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL,
  HISTORY_CREATED_DATE   DATE,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.FIELD_GROUP_H IS 'A hierarchy of GROUPs. Each GROUP  is a collection of FIELDs for a functional area.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.FIELD_GROUP_H_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.FIELD_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.PARENT_GROUP_ID IS 'This Group''s Parent Group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.NAME IS 'Name of the group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.DESCRIPTION IS 'Description of the group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.DEFAULT_GROUP_FLAG IS 'Specifies if the default group is used.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

COMMENT ON COLUMN EESRV.FIELD_GROUP_H.TRANSACTION_TYPE_CODE IS 'Code for the action performed on the record in the base table (followed by it being copied into this history table). I for insert, U for update and D for delete.';


CREATE UNIQUE INDEX EESRV.GRPH_PK ON EESRV.FIELD_GROUP_H
(FIELD_GROUP_H_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PUBLIC SYNONYM FIELD_GROUP_H FOR EESRV.FIELD_GROUP_H;


ALTER TABLE EESRV.FIELD_GROUP_H ADD (
  CONSTRAINT GRPH_PK
  PRIMARY KEY
  (FIELD_GROUP_H_ID)
  USING INDEX EESRV.GRPH_PK
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.FIELD_GROUP_H TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.FIELD_GROUP_H TO EESRV_RW;
ALTER TABLE EESRV.FIELD_GROUP
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.FIELD_GROUP CASCADE CONSTRAINTS;

CREATE TABLE EESRV.FIELD_GROUP
(
  FIELD_GROUP_ID         NUMBER(20)             NOT NULL,
  PARENT_GROUP_ID        NUMBER(20),
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  DEFAULT_GROUP_FLAG     NUMBER(1),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.FIELD_GROUP IS 'A hierarchy of GROUPs. Each GROUP  is a collection of FIELDs for a functional area.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.FIELD_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.  Primary Key generated by max(field_group_id) +1.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.PARENT_GROUP_ID IS 'This Groups Parent Group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.NAME IS 'Name of the group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.DESCRIPTION IS 'Description of the group.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.DEFAULT_GROUP_FLAG IS 'Specifies if the default group is used.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.FIELD_GROUP.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.FIELD_GROUP.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.FIELD_GROUP.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE UNIQUE INDEX EESRV.FLDGRP_PK ON EESRV.FIELD_GROUP
(FIELD_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.IX_RELATIONSHIP12 ON EESRV.FIELD_GROUP
(PARENT_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.FIELD_GROUP_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.FIELD_GROUP
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into FIELD_GROUP_H
        (
        FIELD_GROUP_H_ID,
        FIELD_GROUP_ID,
        PARENT_GROUP_ID,
        NAME,
        DESCRIPTION,
        DEFAULT_GROUP_FLAG,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        FIELD_GROUP_H_S.nextval,
        :old.FIELD_GROUP_ID,
        :old.PARENT_GROUP_ID,
        :old.NAME,
        :old.DESCRIPTION,
        :old.DEFAULT_GROUP_FLAG,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into FIELD_GROUP_H
        (
        FIELD_GROUP_H_ID,
        FIELD_GROUP_ID,
        PARENT_GROUP_ID,
        NAME,
        DESCRIPTION,
        DEFAULT_GROUP_FLAG,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
          )
        values
        (
        FIELD_GROUP_H_S.nextval,
        :new.FIELD_GROUP_ID,
        :new.PARENT_GROUP_ID,
        :new.NAME,
        :new.DESCRIPTION,
        :new.DEFAULT_GROUP_FLAG,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end FIELD_GROUP_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM FIELD_GROUP FOR EESRV.FIELD_GROUP;


ALTER TABLE EESRV.FIELD_GROUP ADD (
  CONSTRAINT FLDGRP_PK
  PRIMARY KEY
  (FIELD_GROUP_ID)
  USING INDEX EESRV.FLDGRP_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.FIELD_GROUP ADD (
  CONSTRAINT RELATIONSHIP12 
  FOREIGN KEY (PARENT_GROUP_ID) 
  REFERENCES EESRV.FIELD_GROUP (FIELD_GROUP_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.FIELD_GROUP TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.FIELD_GROUP TO EESRV_RW;
ALTER TABLE EESRV.FIELD
 DROP PRIMARY KEY CASCADE;

DROP TABLE EESRV.FIELD CASCADE CONSTRAINTS;

CREATE TABLE EESRV.FIELD
(
  FIELD_ID               NUMBER(20)             NOT NULL,
  FIELD_GROUP_ID         NUMBER(20),
  NAME                   VARCHAR2(500 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE),
  DEFAULT_FIELD_FLAG     NUMBER(1),
  TABLE_NAME             VARCHAR2(32 BYTE),
  COLUMN_NAME            VARCHAR2(32 BYTE),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE EESRV.FIELD IS 'A data elelments/field that will be exposed by the using applications. ';

COMMENT ON COLUMN EESRV.FIELD.FIELD_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identify a data record in this table.  Primary Key generated by max(field_id) +1';

COMMENT ON COLUMN EESRV.FIELD.NAME IS 'Name of the FIELD.';

COMMENT ON COLUMN EESRV.FIELD.DESCRIPTION IS 'Description of the FIELD.';

COMMENT ON COLUMN EESRV.FIELD.DEFAULT_FIELD_FLAG IS 'Default FIELD_ID for this field.';

COMMENT ON COLUMN EESRV.FIELD.TABLE_NAME IS 'Name of the table in the database.';

COMMENT ON COLUMN EESRV.FIELD.COLUMN_NAME IS 'Name of the column in the database. ';

COMMENT ON COLUMN EESRV.FIELD.RECORD_CREATED_BY IS 'Account/user ID that created the record';

COMMENT ON COLUMN EESRV.FIELD.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN EESRV.FIELD.RECORD_MODIFIED_BY IS 'Account/user ID for this modification to the record';

COMMENT ON COLUMN EESRV.FIELD.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN EESRV.FIELD.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';


CREATE UNIQUE INDEX EESRV.FLD_PK ON EESRV.FIELD
(FIELD_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX EESRV.IX_RELATIONSHIP17 ON EESRV.FIELD
(FIELD_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER EESRV.FIELD_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON EESRV.FIELD
 REFERENCING  OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare
       mDML char(1);
begin

    if DELETING then
        insert into FIELD_H
        (
        FIELD_H_ID,
        FIELD_ID,
        FIELD_GROUP_ID,
        NAME,
        DESCRIPTION,
        DEFAULT_FIELD_FLAG,
        TABLE_NAME,
        COLUMN_NAME,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        FIELD_H_S.nextval,
        :old.FIELD_ID,
        :old.FIELD_GROUP_ID,
        :old.NAME,
        :old.DESCRIPTION,
        :old.DEFAULT_FIELD_FLAG,
        :old.TABLE_NAME,
        :old.COLUMN_NAME,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        'D'
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;

        insert into FIELD_H
        (
        FIELD_H_ID,
        FIELD_ID,
        FIELD_GROUP_ID,
        NAME,
        DESCRIPTION,
        DEFAULT_FIELD_FLAG,
        TABLE_NAME,
        COLUMN_NAME,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        FIELD_H_S.nextval,
        :new.FIELD_ID,
        :new.FIELD_GROUP_ID,
        :new.NAME,
        :new.DESCRIPTION,
        :new.DEFAULT_FIELD_FLAG,
        :new.TABLE_NAME,
        :new.COLUMN_NAME,
        :new.RECORD_CREATED_BY,
        :new.RECORD_CREATED_DATE,
        :new.RECORD_MODIFIED_BY,
        :new.RECORD_MODIFIED_DATE,
        :new.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    end if;

end FIELD_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM FIELD FOR EESRV.FIELD;


ALTER TABLE EESRV.FIELD ADD (
  CONSTRAINT FLD_PK
  PRIMARY KEY
  (FIELD_ID)
  USING INDEX EESRV.FLD_PK
  ENABLE VALIDATE);

ALTER TABLE EESRV.FIELD ADD (
  CONSTRAINT RELATIONSHIP17 
  FOREIGN KEY (FIELD_GROUP_ID) 
  REFERENCES EESRV.FIELD_GROUP (FIELD_GROUP_ID)
  ENABLE VALIDATE);

GRANT SELECT ON EESRV.FIELD TO EESRV_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON EESRV.FIELD TO EESRV_RW;
