DROP TABLE ADRMGT.STATS_TABLE_100111_095852 CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.STATS_TABLE_100111_095852
(
  STATID   VARCHAR2(30 BYTE),
  TYPE     CHAR(1 BYTE),
  VERSION  NUMBER,
  FLAGS    NUMBER,
  C1       VARCHAR2(30 BYTE),
  C2       VARCHAR2(30 BYTE),
  C3       VARCHAR2(30 BYTE),
  C4       VARCHAR2(30 BYTE),
  C5       VARCHAR2(30 BYTE),
  N1       NUMBER,
  N2       NUMBER,
  N3       NUMBER,
  N4       NUMBER,
  N5       NUMBER,
  N6       NUMBER,
  N7       NUMBER,
  N8       NUMBER,
  N9       NUMBER,
  N10      NUMBER,
  N11      NUMBER,
  N12      NUMBER,
  D1       DATE,
  R1       RAW(32),
  R2       RAW(32),
  CH1      VARCHAR2(1000 BYTE)
)
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;


CREATE INDEX ADRMGT.STATS_TABLE_100111_095852 ON ADRMGT.STATS_TABLE_100111_095852
(STATID, TYPE, C5, C1, C2, 
C3, C4, VERSION)
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;

GRANT SELECT ON ADRMGT.STATS_TABLE_100111_095852 TO ADRMGT_DBA_R;
DROP TABLE ADRMGT.STATS_TAB CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.STATS_TAB
(
  STATID   VARCHAR2(30 BYTE),
  TYPE     CHAR(1 BYTE),
  VERSION  NUMBER,
  FLAGS    NUMBER,
  C1       VARCHAR2(30 BYTE),
  C2       VARCHAR2(30 BYTE),
  C3       VARCHAR2(30 BYTE),
  C4       VARCHAR2(30 BYTE),
  C5       VARCHAR2(30 BYTE),
  N1       NUMBER,
  N2       NUMBER,
  N3       NUMBER,
  N4       NUMBER,
  N5       NUMBER,
  N6       NUMBER,
  N7       NUMBER,
  N8       NUMBER,
  N9       NUMBER,
  N10      NUMBER,
  N11      NUMBER,
  N12      NUMBER,
  D1       DATE,
  R1       RAW(32),
  R2       RAW(32),
  CH1      VARCHAR2(1000 BYTE),
  CL1      CLOB
)
LOB (CL1) STORE AS (
  TABLESPACE  M_DATA_01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          4M
                  NEXT             4M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX ADRMGT.STATS_TAB ON ADRMGT.STATS_TAB
(STATID, TYPE, C5, C1, C2, 
C3, C4, VERSION)
LOGGING
TABLESPACE M_DATA_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 OR REPLACE PUBLIC SYNONYM STATS_TAB FOR ADRMGT.STATS_TAB;


GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADR;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADRAAP;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADREXT;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADRFILE;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADRREF;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ADRTMP;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO BENS;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO CIS;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO EESRV;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ESRJMS;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO IDHADM02;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO LOBR;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO ODM;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO PSIM;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO VBR;

GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON ADRMGT.STATS_TAB TO VOA;
ALTER TABLE ADRMGT.RELEASE_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.RELEASE_H CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.RELEASE_H
(
  RELEASE_H_ID           NUMBER(20)             NOT NULL,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE),
  RELEASE_ID             NUMBER(20)             NOT NULL,
  DEPLOYMENT_ID          NUMBER(20),
  OWNING_APP_NAME        VARCHAR2(20 BYTE),
  RELEASE_NUMBER         VARCHAR2(20 BYTE)      NOT NULL,
  DESCRIPTION            VARCHAR2(4000 BYTE),
  PACKAGE_NAME           VARCHAR2(40 BYTE),
  STATUS_NAME            VARCHAR2(20 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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                   NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.RELEASE_H IS 'Distribution of an upgraded version of the product to environments when RELEASE is performed by the home organization
or preparation to pass said information to another organization for DEPLOYMENT.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.RELEASE_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 ADRMGT.RELEASE_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.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.RELEASE_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 ADRMGT.RELEASE_H.DEPLOYMENT_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 ADRMGT.RELEASE_H.OWNING_APP_NAME IS 'The owning application.
Examples;\
SDS, ADR, VOA';

COMMENT ON COLUMN ADRMGT.RELEASE_H.RELEASE_NUMBER IS 'Numeric part of this RELEASE.
Note - the application  is identified separately in the APPLICATION_CODE (to allow sorting by application).
For Example; \
for ADR=      1.6.0.0\
for SDS=       IR13 du12';

COMMENT ON COLUMN ADRMGT.RELEASE_H.DESCRIPTION IS 'Description of this RELEASE.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.PACKAGE_NAME IS 'Name of this RELEASE delivery package.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.STATUS_NAME IS 'The status of this RELEASE.
Possible values;\
1. Under Review\
2, Active\
3. Awaiting Signoff\
4. Deployed \
5. Reverted - This environment was restored to the condition it was in before the package was applied. Two possible ways to do this;\
      A. A problem was discovered after the script(s) ran and we are flagging this record as being reverted. In this case, another release was/will be created to roll back the changes. or\
      B. When it is know ahead of time that implementation time for this release is critical, the rollback sql can be written, tested by SQA and included as part of the package with instructions on how to run it if needed. In this case it could be reverted by this deployment and no additional deployment is required.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this RELEASE.';

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

COMMENT ON COLUMN ADRMGT.RELEASE_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.RELEASE_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 to "n" time(s) after initial creation.';

COMMENT ON COLUMN ADRMGT.RELEASE_H.HISTORY_CREATED_DATE IS 'SYSDATE when the record was created from the base table.';


CREATE UNIQUE INDEX ADRMGT.RELEASE_ID_H_PK ON ADRMGT.RELEASE_H
(RELEASE_H_ID)
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;

ALTER TABLE ADRMGT.RELEASE_H ADD (
  CONSTRAINT REL_TRANS_CHK
  CHECK (TRANSACTION_TYPE_CODE IN ('U', 'I', 'D'))
  ENABLE VALIDATE,
  CONSTRAINT RELEASE_ID_H_PK
  PRIMARY KEY
  (RELEASE_H_ID)
  USING INDEX ADRMGT.RELEASE_ID_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.RELEASE_H TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.RELEASE_H TO ADRMGT_CM_RW;
ALTER TABLE ADRMGT.RELEASE
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.RELEASE CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.RELEASE
(
  RELEASE_ID             NUMBER(20)             NOT NULL,
  DEPLOYMENT_ID          NUMBER(20),
  OWNING_APP_NAME        VARCHAR2(20 BYTE),
  RELEASE_NUMBER         VARCHAR2(20 BYTE)      NOT NULL,
  DESCRIPTION            VARCHAR2(4000 BYTE),
  PACKAGE_NAME           VARCHAR2(40 BYTE),
  STATUS_NAME            VARCHAR2(20 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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 M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.RELEASE IS 'Distribution of an upgraded version of the product to environments when RELEASE is performed by the home organization
or preparation to pass said information to another organization for DEPLOYMENT.';

COMMENT ON COLUMN ADRMGT.RELEASE.RELEASE_ID IS 'TheB primaryB key, generatedB byB theB database systemB inB numericB form, usedB toB uniquelyB identify aB dataB recordB inB this table.';

COMMENT ON COLUMN ADRMGT.RELEASE.DEPLOYMENT_ID IS 'TheB primaryB key, generatedB byB theB database systemB inB numericB form, usedB toB uniquelyB identify aB dataB recordB inB this table.';

COMMENT ON COLUMN ADRMGT.RELEASE.OWNING_APP_NAME IS 'The owning application.\
Examples;\
SDS, ADR, VOA';

COMMENT ON COLUMN ADRMGT.RELEASE.RELEASE_NUMBER IS 'Numeric part of this RELEASE.
Note - the application  is identified separately in the APPLICATION_CODE (to allow sorting by application).
For Example;\
for ADR=      1.6.0.0\
for SDS=       IR13 du12';

COMMENT ON COLUMN ADRMGT.RELEASE.DESCRIPTION IS 'Description of this RELEASE.';

COMMENT ON COLUMN ADRMGT.RELEASE.PACKAGE_NAME IS 'Name of this RELEASE delivery package.';

COMMENT ON COLUMN ADRMGT.RELEASE.STATUS_NAME IS 'The status of this RELEASE.
Possible values;\
1. Under Review\
2, Active\
3. Awaiting Signoff\
4. Deployed\
5. Reverted - This environment was restored to the condition it was in before the package was applied. Two possible ways to do this;\
      A. A problem was discovered after the script(s) ran and we are flagging this record as being reverted. In this case, another release was/will be created to roll back the changes.or\
      B. When it is know ahead of time that implementation time for this release is critical, the rollback sql can be written, tested by SQA and included as part of the package with instructions on how to run it if needed. In this case it could be reverted by this deployment and no additional deployment is required.';

COMMENT ON COLUMN ADRMGT.RELEASE.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this RELEASE.';

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

COMMENT ON COLUMN ADRMGT.RELEASE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.RELEASE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.RELEASE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1b &n time(s) after initial creation.';


CREATE UNIQUE INDEX ADRMGT.RELEASE_ID_PK ON ADRMGT.RELEASE
(RELEASE_ID)
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 ADRMGT.RELEASE_NUMBER_UK ON ADRMGT.RELEASE
(RELEASE_NUMBER)
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 OR REPLACE TRIGGER ADRMGT.RELEASE_AUDIR_TRGR
after Update or Delete or Insert
ON ADRMGT.RELEASE for each row
declare
   	mDML char(1);
begin

	if DELETING then
		insert into RELEASE_H
		(
		RELEASE_H_ID,
		RELEASE_ID ,
                DEPLOYMENT_ID ,
                OWNING_APP_NAME,
                RELEASE_NUMBER,
                DESCRIPTION,
                PACKAGE_NAME,
                STATUS_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
		)
		values
		(
		RELEASE_H_S.nextval,
		:old.RELEASE_ID,
                :old.DEPLOYMENT_ID,
		:old.OWNING_APP_NAME,
                :old.RELEASE_NUMBER,
                :old.DESCRIPTION,
                :old.PACKAGE_NAME,
		:old.STATUS_NAME,
		:old.DEPLOYMENT_NOTE,
		:old.RECORD_MODIFIED_DATE,
		:old.RECORD_MODIFIED_COUNT,
                :old.RECORD_MODIFIED_BY,
                :old.RECORD_CREATED_DATE,
                :old.RECORD_CREATED_BY,
                SYSDATE,
		'D'
		);
	else
		if UPDATING then
			mDML := 'U';
		else
			mDML := 'I';
		end if;

		insert into RELEASE_H
        (
                RELEASE_H_ID,
		RELEASE_ID ,
                DEPLOYMENT_ID ,
                OWNING_APP_NAME,
                RELEASE_NUMBER,
                DESCRIPTION,
                PACKAGE_NAME,
                STATUS_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
		)
		values
		(
		RELEASE_H_S.nextval,
		:new.RELEASE_ID,
                :new.DEPLOYMENT_ID,
		:new.OWNING_APP_NAME,
                :new.RELEASE_NUMBER,
                :new.DESCRIPTION,
                :new.PACKAGE_NAME,
		:new.STATUS_NAME,
		:new.DEPLOYMENT_NOTE,
		:new.RECORD_MODIFIED_DATE,
		:new.RECORD_MODIFIED_COUNT,
                :new.RECORD_MODIFIED_BY,
                :new.RECORD_CREATED_DATE,
                :new.RECORD_CREATED_BY,
                SYSDATE,
		mDML
		);

	end if;
end RELEASE_audir_trgr;
/


ALTER TABLE ADRMGT.RELEASE ADD (
  CONSTRAINT RELEASE_ID_PK
  PRIMARY KEY
  (RELEASE_ID)
  USING INDEX ADRMGT.RELEASE_ID_PK
  ENABLE VALIDATE,
  CONSTRAINT RELEASE_NUMBER_UK
  UNIQUE (RELEASE_NUMBER)
  USING INDEX ADRMGT.RELEASE_NUMBER_UK
  ENABLE VALIDATE);

ALTER TABLE ADRMGT.RELEASE ADD (
  CONSTRAINT RLS_DPLMNT_FK 
  FOREIGN KEY (DEPLOYMENT_ID) 
  REFERENCES ADRMGT.DEPLOYMENT (DEPLOYMENT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.RELEASE TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.RELEASE TO ADRMGT_CM_RW;
DROP TABLE ADRMGT.JOB_PROCESS_LOGS CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.JOB_PROCESS_LOGS
(
  JOB_PROCESS_LOGS_ID  NUMBER                   NOT NULL,
  JOB_NAME             VARCHAR2(60 BYTE)        NOT NULL,
  JOB_LOG_DATE         DATE                     NOT NULL,
  JOB_STATUS           VARCHAR2(60 BYTE),
  JOB_LOG_MESSAGE      VARCHAR2(2000 BYTE),
  DATE1                DATE,
  DATE2                DATE,
  DATE3                DATE,
  NUMERIC1             NUMBER,
  NUMERIC2             NUMBER,
  NUMERIC3             NUMBER,
  TEXT1                VARCHAR2(255 BYTE),
  TEXT2                VARCHAR2(255 BYTE),
  TEXT3                VARCHAR2(255 BYTE),
  RECORD_CREATED_DATE  DATE                     NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
COMPRESS FOR OLTP 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.JOB_PROCESS_LOGS IS 'Generic columns to hold details from select ADR database job runs in this database instance.';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.JOB_PROCESS_LOGS_ID IS 'Standard table ID';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.JOB_NAME IS 'Unique Name for each job using this table';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.JOB_LOG_DATE IS 'Date/time of log write request';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.JOB_STATUS IS 'Current status of the job';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.JOB_LOG_MESSAGE IS 'User defined job message';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.DATE1 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.DATE2 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.DATE3 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.NUMERIC1 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.NUMERIC2 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.NUMERIC3 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.TEXT1 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.TEXT2 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.TEXT3 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_LOGS.RECORD_CREATED_DATE IS 'Standard record creation date';


CREATE INDEX ADRMGT.JOB_PROCESS_LOGS_NAME_NU ON ADRMGT.JOB_PROCESS_LOGS
(JOB_NAME, JOB_LOG_DATE)
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 ADRMGT.JOB_PROCESS_LOGS_PK ON ADRMGT.JOB_PROCESS_LOGS
(JOB_PROCESS_LOGS_ID)
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 OR REPLACE PUBLIC SYNONYM JOB_PROCESS_LOGS FOR ADRMGT.JOB_PROCESS_LOGS;


GRANT DELETE, INSERT, SELECT ON ADRMGT.JOB_PROCESS_LOGS TO ADR;

GRANT SELECT ON ADRMGT.JOB_PROCESS_LOGS TO ADRMGT_APP_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.JOB_PROCESS_LOGS TO ADRMGT_APP_RW;

GRANT SELECT ON ADRMGT.JOB_PROCESS_LOGS TO EEUSR;
DROP TABLE ADRMGT.JOB_PROCESS_ERRS CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.JOB_PROCESS_ERRS
(
  JOB_PROCESS_ERRS_ID  NUMBER                   NOT NULL,
  JOB_NAME             VARCHAR2(60 BYTE)        NOT NULL,
  JOB_ERROR_DATE       DATE                     NOT NULL,
  JOB_ERROR_CODE       VARCHAR2(60 BYTE),
  JOB_ERROR_MESSAGE    VARCHAR2(2000 BYTE),
  DATE1                DATE,
  DATE2                DATE,
  DATE3                DATE,
  NUMERIC1             NUMBER,
  NUMERIC2             NUMBER,
  NUMERIC3             NUMBER,
  TEXT1                VARCHAR2(255 BYTE),
  TEXT2                VARCHAR2(255 BYTE),
  TEXT3                VARCHAR2(255 BYTE),
  RECORD_CREATED_DATE  DATE                     NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.JOB_PROCESS_ERRS IS 'Generic columns to hold details of errors from select ADR database job runs in this database instance.';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.JOB_PROCESS_ERRS_ID IS 'Standard table ID';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.JOB_NAME IS 'Unique Name for each job using this table';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.JOB_ERROR_DATE IS 'Date/time of error write request';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.JOB_ERROR_CODE IS 'Error code from the job';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.JOB_ERROR_MESSAGE IS 'User defined error message';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.DATE1 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.DATE2 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.DATE3 IS 'Generic date field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.NUMERIC1 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.NUMERIC2 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.NUMERIC3 IS 'Generic numeric field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.TEXT1 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.TEXT2 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.TEXT3 IS 'Generic text field for logging user defined information';

COMMENT ON COLUMN ADRMGT.JOB_PROCESS_ERRS.RECORD_CREATED_DATE IS 'Standard record creation date';


CREATE INDEX ADRMGT.JOB_PROCESS_ERRS_NAME_NU ON ADRMGT.JOB_PROCESS_ERRS
(JOB_NAME, JOB_ERROR_DATE)
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 ADRMGT.JOB_PROCESS_ERRS_PK ON ADRMGT.JOB_PROCESS_ERRS
(JOB_PROCESS_ERRS_ID)
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 OR REPLACE PUBLIC SYNONYM JOB_PROCESS_ERRS FOR ADRMGT.JOB_PROCESS_ERRS;


GRANT DELETE, INSERT, SELECT ON ADRMGT.JOB_PROCESS_ERRS TO ADR;

GRANT INSERT, SELECT ON ADRMGT.JOB_PROCESS_ERRS TO ADRFILE;

GRANT SELECT ON ADRMGT.JOB_PROCESS_ERRS TO ADRMGT_APP_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.JOB_PROCESS_ERRS TO ADRMGT_APP_RW;

GRANT SELECT ON ADRMGT.JOB_PROCESS_ERRS TO EEUSR;
ALTER TABLE ADRMGT.DEPLOYMENT_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.DEPLOYMENT_H CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.DEPLOYMENT_H
(
  DEPLOYMENT_H_ID        NUMBER(20)             NOT NULL,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE),
  DEPLOYMENT_ID          NUMBER(20)             NOT NULL,
  TICKET_NUMBER          VARCHAR2(20 BYTE),
  DESCRIPTION            VARCHAR2(4000 BYTE),
  STATUS_NAME            VARCHAR2(20 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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                   NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.DEPLOYMENT_H IS 'Implementation of a set of changes in this Instance of the database.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.DEPLOYMENT_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 ADRMGT.DEPLOYMENT_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.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.DEPLOYMENT_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 ADRMGT.DEPLOYMENT_H.TICKET_NUMBER IS 'Identifies deployment of changes to this environment. \
For Example;  \
CO for PreProd     = CO31174FY10 \
CO for Reporting  = CO31175FY10 \
CO for Production = CO31176FY10 \
SR for Staging      = R211669FY10';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.DESCRIPTION IS 'Description of this deployment.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.STATUS_NAME IS 'The status of this deployment.\
Possible values; \
1. Successful\
2. Withdrawn';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this DEPLOYMENT.';

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

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 to "n" time(s) after initial creation.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT_H.HISTORY_CREATED_DATE IS 'SYSDATE when the record was created from the base table.';


CREATE UNIQUE INDEX ADRMGT.DEPLOYMENT_H_ID_PK ON ADRMGT.DEPLOYMENT_H
(DEPLOYMENT_H_ID)
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;

ALTER TABLE ADRMGT.DEPLOYMENT_H ADD (
  CONSTRAINT DEP_TRANS_CHK
  CHECK (TRANSACTION_TYPE_CODE IN ('U', 'I', 'D'))
  ENABLE VALIDATE,
  CONSTRAINT DEPLOYMENT_H_ID_PK
  PRIMARY KEY
  (DEPLOYMENT_H_ID)
  USING INDEX ADRMGT.DEPLOYMENT_H_ID_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.DEPLOYMENT_H TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.DEPLOYMENT_H TO ADRMGT_CM_RW;
ALTER TABLE ADRMGT.DEPLOYMENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.DEPLOYMENT CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.DEPLOYMENT
(
  DEPLOYMENT_ID          NUMBER(20)             NOT NULL,
  TICKET_NUMBER          VARCHAR2(20 BYTE)      NOT NULL,
  DESCRIPTION            VARCHAR2(4000 BYTE),
  STATUS_NAME            VARCHAR2(20 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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 M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.DEPLOYMENT IS 'Implementation of a set of changes in this Instance of the database.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.DEPLOYMENT_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 ADRMGT.DEPLOYMENT.TICKET_NUMBER IS 'Identifies deployment of changes to this environment. \
For Example;  \
CO for PreProd     = CO31174FY10 \
CO for Reporting  = CO31175FY10 \
CO for Production = CO31176FY10 \
SR for Staging      = R211669FY10';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.DESCRIPTION IS 'Description of this deployment.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.STATUS_NAME IS 'The status of this deployment.\
Possible values; \
1. Successful\
2. Withdrawn';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this DEPLOYMENT.';

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

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.DEPLOYMENT.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 &n time(s) after initial creation.';


CREATE UNIQUE INDEX ADRMGT.DEPLOYMENT_ID_PK ON ADRMGT.DEPLOYMENT
(DEPLOYMENT_ID)
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 OR REPLACE TRIGGER ADRMGT.DEPLOYMENT_AUDIR_TRGR
after Update or Delete or Insert
ON ADRMGT.DEPLOYMENT for each row
declare
   	mDML char(1);
begin

	if DELETING then
		insert into DEPLOYMENT_H
		(
		DEPLOYMENT_H_ID,
		DEPLOYMENT_ID ,
                TICKET_NUMBER ,
                DESCRIPTION,
                STATUS_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
		)
		values
		(
		DEPLOYMENT_H_S.nextval,
		:old.DEPLOYMENT_ID,
		:old.TICKET_NUMBER,
                :old.DESCRIPTION,
		:old.STATUS_NAME,
		:old.DEPLOYMENT_NOTE,
		:old.RECORD_MODIFIED_DATE,
		:old.RECORD_MODIFIED_COUNT,
                :old.RECORD_MODIFIED_BY,
                :old.RECORD_CREATED_DATE,
                :old.RECORD_CREATED_BY,
                SYSDATE,
		'D'
		);
	else
		if UPDATING then
			mDML := 'U';
		else
			mDML := 'I';
		end if;

		insert into DEPLOYMENT_H
        (
                DEPLOYMENT_H_ID,
		DEPLOYMENT_ID ,
                TICKET_NUMBER ,
                DESCRIPTION,
                STATUS_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
        )
		values
		(
		DEPLOYMENT_H_S.nextval,
                :new.DEPLOYMENT_ID,
		:new.TICKET_NUMBER,
                :new.DESCRIPTION,
		:new.STATUS_NAME,
		:new.DEPLOYMENT_NOTE,
		:new.RECORD_MODIFIED_DATE,
		:new.RECORD_MODIFIED_COUNT,
                :new.RECORD_MODIFIED_BY,
                :new.RECORD_CREATED_DATE,
                :new.RECORD_CREATED_BY,
                SYSDATE,
		mDML
		);

	end if;
end DEPLOYMENT_audir_trgr;
/


ALTER TABLE ADRMGT.DEPLOYMENT ADD (
  CONSTRAINT DEPLOYMENT_ID_PK
  PRIMARY KEY
  (DEPLOYMENT_ID)
  USING INDEX ADRMGT.DEPLOYMENT_ID_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.DEPLOYMENT TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.DEPLOYMENT TO ADRMGT_CM_RW;
ALTER TABLE ADRMGT.CCR_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.CCR_H CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.CCR_H
(
  CCR_H_ID               NUMBER(20)             NOT NULL,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE),
  CCR_ID                 NUMBER(20)             NOT NULL,
  RELEASE_ID             NUMBER(20),
  DEPLOYMENT_ID          NUMBER(20),
  SCHEMA_NAME            VARCHAR2(20 BYTE),
  CCR_NUMBER             NUMBER(20)             NOT NULL,
  HEADLINE_TEXT          VARCHAR2(120 BYTE),
  CR_NUMBER              NUMBER(20),
  PACKAGE_NAME           VARCHAR2(40 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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                   NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.CCR_H IS 'Change Control Record describing requested changes.';

COMMENT ON COLUMN ADRMGT.CCR_H.CCR_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 ADRMGT.CCR_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.';

COMMENT ON COLUMN ADRMGT.CCR_H.CCR_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 ADRMGT.CCR_H.RELEASE_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 ADRMGT.CCR_H.DEPLOYMENT_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 ADRMGT.CCR_H.SCHEMA_NAME IS 'The owning schema (ex, IDHADM) or a generalization (ex. IDHUB).\
Might be copied from the Category in the CCR.\
Examples; \
ADR, ADREXT, ESR, ESRJMS, IDHADM, IDHCMM, IDHCMO, PSIM, PSD, PSL, VOA';

COMMENT ON COLUMN ADRMGT.CCR_H.CCR_NUMBER IS 'The numeric part of the implementing database''''s Code Change Request Number.
Ex. 1040';

COMMENT ON COLUMN ADRMGT.CCR_H.HEADLINE_TEXT IS 'Short description of the purpose of the CCR. Intended to be copied from the CCR Headline';

COMMENT ON COLUMN ADRMGT.CCR_H.CR_NUMBER IS 'The numeric part of the implementing database''''s Change Request Number. May be associated with one or more implementing database''''s Code CRs
Ex. 1040';

COMMENT ON COLUMN ADRMGT.CCR_H.PACKAGE_NAME IS 'Name of this CCR delivery package';

COMMENT ON COLUMN ADRMGT.CCR_H.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this CCR';

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

COMMENT ON COLUMN ADRMGT.CCR_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.CCR_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.CCR_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 to "n" time(s) after initial creation.';

COMMENT ON COLUMN ADRMGT.CCR_H.HISTORY_CREATED_DATE IS 'SYSDATE when the record was created from the base table.';


CREATE UNIQUE INDEX ADRMGT.CCR_H_ID ON ADRMGT.CCR_H
(CCR_H_ID)
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;

ALTER TABLE ADRMGT.CCR_H ADD (
  CONSTRAINT CCR_TRANS_CHK
  CHECK (TRANSACTION_TYPE_CODE IN ('U', 'I', 'D'))
  ENABLE VALIDATE,
  CONSTRAINT CCR_H_ID
  PRIMARY KEY
  (CCR_H_ID)
  USING INDEX ADRMGT.CCR_H_ID
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.CCR_H TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.CCR_H TO ADRMGT_CM_RW;
ALTER TABLE ADRMGT.CCR
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.CCR CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.CCR
(
  CCR_ID                 NUMBER(20)             NOT NULL,
  RELEASE_ID             NUMBER(20),
  DEPLOYMENT_ID          NUMBER(20),
  SCHEMA_NAME            VARCHAR2(20 BYTE),
  CCR_NUMBER             NUMBER(20)             NOT NULL,
  HEADLINE_TEXT          VARCHAR2(120 BYTE),
  CR_NUMBER              NUMBER(20),
  PACKAGE_NAME           VARCHAR2(40 BYTE),
  DEPLOYMENT_NOTE        VARCHAR2(4000 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 M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE ADRMGT.CCR IS 'Change Control Record describing requested changes.';

COMMENT ON COLUMN ADRMGT.CCR.CCR_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 ADRMGT.CCR.SCHEMA_NAME IS 'The owning schema (ex, IDHADM) or a generalization (ex. IDHUB).\
Might be copied from the Category in the CCR.\
Examples;\
ADR, ADREXT, ESR, ESRJMS, IDHADM, IDHCMM, IDHCMO, PSIM, PSD, PSL, VOA';

COMMENT ON COLUMN ADRMGT.CCR.CCR_NUMBER IS 'The numeric part of the implementing database''''s Code Change Request Number. Ex. 1040';

COMMENT ON COLUMN ADRMGT.CCR.HEADLINE_TEXT IS 'Short description of the purpose of the CCR. Intended to be copied from the CCR Headline';

COMMENT ON COLUMN ADRMGT.CCR.CR_NUMBER IS 'The numeric part of the implementing database''''s Change Request Number. May be associated with one or more implementing database''''s Code CRs Ex. 1040';

COMMENT ON COLUMN ADRMGT.CCR.PACKAGE_NAME IS 'Name of this CCR delivery package';

COMMENT ON COLUMN ADRMGT.CCR.DEPLOYMENT_NOTE IS 'Notes pertaining to the execution of this CCR';

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

COMMENT ON COLUMN ADRMGT.CCR.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.CCR.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.CCR.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1b &n time(s) after initial creation.';


CREATE INDEX ADRMGT.CCR_DEP_FK_I ON ADRMGT.CCR
(DEPLOYMENT_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 ADRMGT.CCR_ID_PK ON ADRMGT.CCR
(CCR_ID)
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 ADRMGT.CCR_NUMBER_UK ON ADRMGT.CCR
(CCR_NUMBER)
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 INDEX ADRMGT.CCR_REL_FK_I ON ADRMGT.CCR
(RELEASE_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 ADRMGT.CCR_AUDIR_TRGR
after Update or Delete or Insert
ON ADRMGT.CCR for each row
declare
   	mDML char(1);
begin

	if DELETING then
		insert into CCR_H
		(
		CCR_H_ID,
		CCR_ID ,
                DEPLOYMENT_ID,
                RELEASE_ID,
                SCHEMA_NAME ,
                CCR_NUMBER ,
                HEADLINE_TEXT,
                CR_NUMBER,
                PACKAGE_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
		)
		values
		(
		CCR_H_S.nextval,
		:old.CCR_ID,
		:old.DEPLOYMENT_ID,
        :old.RELEASE_ID,
		:old.SCHEMA_NAME,
		:old.CCR_NUMBER,
                :old.HEADLINE_TEXT,
		:old.CR_NUMBER,
                :old.PACKAGE_NAME,
		:old.DEPLOYMENT_NOTE,
		:old.RECORD_MODIFIED_DATE,
		:old.RECORD_MODIFIED_COUNT,
                :old.RECORD_MODIFIED_BY,
                :old.RECORD_CREATED_DATE,
                :old.RECORD_CREATED_BY,
                SYSDATE,
		'D'
		);
	else
		if UPDATING then
			mDML := 'U';
		else
			mDML := 'I';
		end if;

		insert into CCR_H
        (
        CCR_H_ID,
		CCR_ID ,
                DEPLOYMENT_ID,
                RELEASE_ID,
                SCHEMA_NAME ,
                CCR_NUMBER ,
                HEADLINE_TEXT,
                CR_NUMBER,
                PACKAGE_NAME,
                DEPLOYMENT_NOTE,
                RECORD_MODIFIED_DATE,
                RECORD_MODIFIED_COUNT,
                RECORD_MODIFIED_BY,
                RECORD_CREATED_DATE,
                RECORD_CREATED_BY,
                HISTORY_CREATED_DATE,
		TRANSACTION_TYPE_CODE
		)
		values
		(
		CCR_H_S.nextval,
		:new.CCR_ID,
		:new.DEPLOYMENT_ID,
        :new.RELEASE_ID,
		:new.SCHEMA_NAME,
		:new.CCR_NUMBER,
                :new.HEADLINE_TEXT,
		:new.CR_NUMBER,
                :new.PACKAGE_NAME,
		:new.DEPLOYMENT_NOTE,
		:new.RECORD_MODIFIED_DATE,
		:new.RECORD_MODIFIED_COUNT,
                :new.RECORD_MODIFIED_BY,
                :new.RECORD_CREATED_DATE,
                :new.RECORD_CREATED_BY,
                SYSDATE,
		mDML
		);

	end if;
end CCR_audir_trgr;
/


ALTER TABLE ADRMGT.CCR ADD (
  CONSTRAINT CCR_ID_PK
  PRIMARY KEY
  (CCR_ID)
  USING INDEX ADRMGT.CCR_ID_PK
  ENABLE VALIDATE,
  CONSTRAINT CCR_NUMBER_UK
  UNIQUE (CCR_NUMBER)
  USING INDEX ADRMGT.CCR_NUMBER_UK
  ENABLE VALIDATE);

ALTER TABLE ADRMGT.CCR ADD (
  CONSTRAINT CCR_DEP_FK 
  FOREIGN KEY (DEPLOYMENT_ID) 
  REFERENCES ADRMGT.DEPLOYMENT (DEPLOYMENT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT CCR_REL_FK 
  FOREIGN KEY (RELEASE_ID) 
  REFERENCES ADRMGT.RELEASE (RELEASE_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.CCR TO ADRMGT_CM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.CCR TO ADRMGT_CM_RW;
ALTER TABLE ADRMGT.ADR_USER_CONTACT_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.ADR_USER_CONTACT_H CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.ADR_USER_CONTACT_H
(
  ADR_USER_CONTACT_H_ID    NUMBER(20)           NOT NULL,
  ADR_USER_CONTACT_ID      NUMBER(20)           NOT NULL,
  TRANSACTION_TYPE_CODE    VARCHAR2(1 BYTE),
  EMAIL_ADDRESS_LIST       VARCHAR2(4000 BYTE),
  PROJECT_NAME             VARCHAR2(200 BYTE),
  CONTACT_EMPLOYER         VARCHAR2(50 BYTE),
  CONTACT_EMAIL_USER_NAME  VARCHAR2(30 BYTE),
  CONTACT_LAST_NAME        VARCHAR2(50 BYTE),
  CONTACT_FIRST_NAME       VARCHAR2(50 BYTE),
  REQUESTOR_LAST_NAME      VARCHAR2(50 BYTE),
  REQUESTOR_FIRST_NAME     VARCHAR2(50 BYTE),
  APPROVER_LAST_NAME       VARCHAR2(50 BYTE),
  APPROVER_FIRST_NAME      VARCHAR2(50 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                 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 ADRMGT.ADR_USER_CONTACT_H IS 'Names and Email addresses used to reach users of the ADR Repository or their associates.
On 6/2010, the initial process intends to;\
1. Capture initial authorizations of permission to use the database instance this record resides in\
2. Maintain contact info to reach a user when their accounts have been suspended due to not signing-in for more than 90 days.\
3. De-activate user permissions when the user is no longer entitled to them\
The process is envisioned as;\
An email will be sent to the group_email, supervisors email, requesting_officials email and approving_officials email for verification that the contact is still on the project and still needs access to the system before the account is deactivated.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.ADR_USER_CONTACT_H_ID IS 'Unique number to identify the row detailed information.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.ADR_USER_CONTACT_ID IS 'Unique number to identify the row detailed information.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_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.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.EMAIL_ADDRESS_LIST IS 'A list of comma delimited email addresses to contact any person, group or project thus providing a method of contacting a specific person or their management team.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.PROJECT_NAME IS 'Name and/or acronym for the project the contact is performing tasks.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.CONTACT_EMPLOYER IS 'Contractor or other government organization, name of employer for the person requesting database access';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.CONTACT_EMAIL_USER_NAME IS 'Contains the DNS       USER_ID for the contact.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.CONTACT_LAST_NAME IS 'The contact''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.CONTACT_FIRST_NAME IS 'The contact''s first name.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.REQUESTOR_LAST_NAME IS 'Requestor''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.REQUESTOR_FIRST_NAME IS 'Requestor''s first name.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.APPROVER_LAST_NAME IS 'Approver''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.APPROVER_FIRST_NAME IS 'Approver''s first name.';

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

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 to n time(s) after initial creation.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT_H.HISTORY_CREATED_DATE IS 'SYSDATE when the record was created from the base table.';


CREATE UNIQUE INDEX ADRMGT.CONTACT_ID_H_PK ON ADRMGT.ADR_USER_CONTACT_H
(ADR_USER_CONTACT_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;

ALTER TABLE ADRMGT.ADR_USER_CONTACT_H ADD (
  CONSTRAINT CONTACT_ID_H_PK
  PRIMARY KEY
  (ADR_USER_CONTACT_H_ID)
  USING INDEX ADRMGT.CONTACT_ID_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.ADR_USER_CONTACT_H TO ADRMGT_DBA_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_USER_CONTACT_H TO ADRMGT_DBA_RW;
ALTER TABLE ADRMGT.ADR_USER_CONTACT
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.ADR_USER_CONTACT CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.ADR_USER_CONTACT
(
  ADR_USER_CONTACT_ID      NUMBER(20)           NOT NULL,
  EMAIL_ADDRESS_LIST       VARCHAR2(4000 BYTE),
  PROJECT_NAME             VARCHAR2(200 BYTE),
  CONTACT_EMPLOYER         VARCHAR2(50 BYTE),
  CONTACT_EMAIL_USER_NAME  VARCHAR2(30 BYTE),
  CONTACT_LAST_NAME        VARCHAR2(50 BYTE),
  CONTACT_FIRST_NAME       VARCHAR2(50 BYTE),
  REQUESTOR_LAST_NAME      VARCHAR2(50 BYTE),
  REQUESTOR_FIRST_NAME     VARCHAR2(50 BYTE),
  APPROVER_LAST_NAME       VARCHAR2(50 BYTE),
  APPROVER_FIRST_NAME      VARCHAR2(50 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 ADRMGT.ADR_USER_CONTACT IS 'Names and Email addresses used to reach users of the ADR Repository or their associates.
On 6/2010, the initial process intends to;\
1. Capture initial authorizations of permission to use the database instance this record resides in\
2. Maintain contact info to reach a user when their accounts have been suspended due to not signing-in for more than 90 days.\
3. De-activate user permissions when the user is no longer entitled to them\
The process is envisioned as;\
An email will be sent to the group_email, supervisors email, requesting_officials email and approving_officials email for verification that the contact is still on the project and still needs access to the system before the account is deactivated.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.ADR_USER_CONTACT_ID IS 'Unique number to identify the row detailed information.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.EMAIL_ADDRESS_LIST IS 'A list of comma delimited email addresses to contact any person, group or project thus providing a method of contacting a specific person or their management team.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.PROJECT_NAME IS 'Name and/or acronym for the project the contact is performing tasks.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.CONTACT_EMPLOYER IS 'Contractor or other government organization, name of employer for the person requesting database access';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.CONTACT_EMAIL_USER_NAME IS 'Contains the DNS       USER_ID for the contact.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.CONTACT_LAST_NAME IS 'The contact''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.CONTACT_FIRST_NAME IS 'The contact''s first name.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.REQUESTOR_LAST_NAME IS 'Requestor''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.REQUESTOR_FIRST_NAME IS 'Requestor''s first name.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.APPROVER_LAST_NAME IS 'Approver''s last name  ';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.APPROVER_FIRST_NAME IS 'Approver''s first name.';

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

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.ADR_USER_CONTACT.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1 to n time(s) after initial creation.';


CREATE UNIQUE INDEX ADRMGT.CONTACT_ID_PK ON ADRMGT.ADR_USER_CONTACT
(ADR_USER_CONTACT_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 ADRMGT.ADR_USER_CONTACT_AUDIR_TRGR
after Update or Delete or Insert
ON ADRMGT.ADR_USER_CONTACT for each row
declare
       mDML char(1);
begin

    mDML := 'D';
    if DELETING then
        insert into ADRMGT.ADR_USER_CONTACT_H
        (
        ADR_USER_CONTACT_H_ID,
        ADR_USER_CONTACT_ID,
        EMAIL_ADDRESS_LIST,
        PROJECT_NAME,
        CONTACT_EMPLOYER,
        CONTACT_EMAIL_USER_NAME,
        CONTACT_LAST_NAME,
        CONTACT_FIRST_NAME,
        REQUESTOR_LAST_NAME,
        REQUESTOR_FIRST_NAME,
        APPROVER_LAST_NAME,
        APPROVER_FIRST_NAME,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        ADR_USER_CONTACT_H_s.nextval,
        :old.ADR_USER_CONTACT_ID,
        :old.EMAIL_ADDRESS_LIST,
        :old.PROJECT_NAME,
        :old.CONTACT_EMPLOYER,
        :old.CONTACT_EMAIL_USER_NAME,
        :old.CONTACT_LAST_NAME,
        :old.CONTACT_FIRST_NAME,
        :old.REQUESTOR_LAST_NAME,
        :old.REQUESTOR_FIRST_NAME,
        :old.APPROVER_LAST_NAME,
        :old.APPROVER_FIRST_NAME,
        :old.RECORD_CREATED_BY,
        :old.RECORD_CREATED_DATE,
        :old.RECORD_MODIFIED_BY,
        :old.RECORD_MODIFIED_DATE,
        :old.RECORD_MODIFIED_COUNT,
        SYSDATE,
        mDML
        );
    else
        if UPDATING then
            mDML := 'U';
        else
            mDML := 'I';
        end if;
        insert into ADRMGT.ADR_USER_CONTACT_H
        (
        ADR_USER_CONTACT_H_ID,
        ADR_USER_CONTACT_ID,
        EMAIL_ADDRESS_LIST,
        PROJECT_NAME,
        CONTACT_EMPLOYER,
        CONTACT_EMAIL_USER_NAME,
        CONTACT_LAST_NAME,
        CONTACT_FIRST_NAME,
        REQUESTOR_LAST_NAME,
        REQUESTOR_FIRST_NAME,
        APPROVER_LAST_NAME,
        APPROVER_FIRST_NAME,
        RECORD_CREATED_BY,
        RECORD_CREATED_DATE,
        RECORD_MODIFIED_BY,
        RECORD_MODIFIED_DATE,
        RECORD_MODIFIED_COUNT,
        HISTORY_CREATED_DATE,
        TRANSACTION_TYPE_CODE
        )
        values
        (
        ADR_USER_CONTACT_H_s.nextval,
        :new.ADR_USER_CONTACT_ID,
        :new.EMAIL_ADDRESS_LIST,
        :new.PROJECT_NAME,
        :new.CONTACT_EMPLOYER,
        :new.CONTACT_EMAIL_USER_NAME,
        :new.CONTACT_LAST_NAME,
        :new.CONTACT_FIRST_NAME,
        :new.REQUESTOR_LAST_NAME,
        :new.REQUESTOR_FIRST_NAME,
        :new.APPROVER_LAST_NAME,
        :new.APPROVER_FIRST_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 ADR_USER_CONTACT_audir_trgr;
/


ALTER TABLE ADRMGT.ADR_USER_CONTACT ADD (
  CONSTRAINT CONTACT_ID_PK
  PRIMARY KEY
  (ADR_USER_CONTACT_ID)
  USING INDEX ADRMGT.CONTACT_ID_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.ADR_USER_CONTACT TO ADR;

GRANT SELECT ON ADRMGT.ADR_USER_CONTACT TO ADRMGT_DBA_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_USER_CONTACT TO ADRMGT_DBA_RW;
ALTER TABLE ADRMGT.ADR_PARAMETER_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.ADR_PARAMETER_H CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.ADR_PARAMETER_H
(
  ADR_PARAMETER_H_ID     NUMBER                 NOT NULL,
  TRANSACTION_TYPE_CODE  VARCHAR2(1 BYTE)       NOT NULL,
  ADR_PARAMETER_ID       NUMBER                 NOT NULL,
  TYPE                   VARCHAR2(30 BYTE),
  UNIT                   VARCHAR2(30 BYTE),
  APPLICATION_NAME       VARCHAR2(80 BYTE),
  PARAMETER_NAME         VARCHAR2(50 BYTE),
  DESCRIPTION            VARCHAR2(250 BYTE),
  VALUE                  VARCHAR2(250 BYTE),
  DATA_LENGTH            NUMBER,
  FORMAT_MASK            VARCHAR2(50 BYTE),
  INACTIVE_FLAG          VARCHAR2(1 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                 NOT NULL,
  HISTORY_CREATED_DATE   DATE                   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 ADRMGT.ADR_PARAMETER_H IS 'Parameters used by ADR support processes and scripts';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.ADR_PARAMETER_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 ADRMGT.ADR_PARAMETER_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.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.ADR_PARAMETER_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 ADRMGT.ADR_PARAMETER_H.TYPE IS 'Scalar type/data type of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.UNIT IS 'Grouping units of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.APPLICATION_NAME IS 'Name of the application that uses the parameter.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.PARAMETER_NAME IS 'The name of the parameter. This is the primary lookup column in the table.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.DESCRIPTION IS 'User friendly description of the parameter.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.VALUE IS 'The value for the parameter that is used by the processes and scripts.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.DATA_LENGTH IS 'Length of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.FORMAT_MASK IS 'Formatting mask to be used on the parameter''s value. This could be a date format like DD-MON-YYYY or a numeric format like 9,999.99.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.INACTIVE_FLAG IS 'Specifies if the parameter can be used.';

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

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1b to "n" time(s) after initial creation.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER_H.HISTORY_CREATED_DATE IS 'SYSDATE when the record was created from the base table.';


CREATE UNIQUE INDEX ADRMGT.ADR_PARAMETER_H_ID_PK ON ADRMGT.ADR_PARAMETER_H
(ADR_PARAMETER_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 INDEX ADRMGT.ADR_PARAM_H_APP_PARMNAME_NK ON ADRMGT.ADR_PARAMETER_H
(APPLICATION_NAME, PARAMETER_NAME, HISTORY_CREATED_DATE)
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;

ALTER TABLE ADRMGT.ADR_PARAMETER_H ADD (
  CONSTRAINT ADR_PARAMETER_H_ID_PK
  PRIMARY KEY
  (ADR_PARAMETER_H_ID)
  USING INDEX ADRMGT.ADR_PARAMETER_H_ID_PK
  ENABLE VALIDATE);

GRANT SELECT ON ADRMGT.ADR_PARAMETER_H TO ADRMGT_APP_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_PARAMETER_H TO ADRMGT_APP_RW;
ALTER TABLE ADRMGT.ADR_PARAMETER
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADRMGT.ADR_PARAMETER CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.ADR_PARAMETER
(
  ADR_PARAMETER_ID       NUMBER                 NOT NULL,
  TYPE                   VARCHAR2(30 BYTE)      NOT NULL,
  UNIT                   VARCHAR2(30 BYTE),
  APPLICATION_NAME       VARCHAR2(80 BYTE),
  PARAMETER_NAME         VARCHAR2(50 BYTE)      NOT NULL,
  DESCRIPTION            VARCHAR2(250 BYTE)     NOT NULL,
  VALUE                  VARCHAR2(250 BYTE)     NOT NULL,
  DATA_LENGTH            NUMBER,
  FORMAT_MASK            VARCHAR2(50 BYTE),
  INACTIVE_FLAG          VARCHAR2(1 BYTE)       NOT NULL,
  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                 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 ADRMGT.ADR_PARAMETER IS 'Parameters used by ADR support processes and scripts';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.ADR_PARAMETER_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 ADRMGT.ADR_PARAMETER.TYPE IS 'Scalar type/data type of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.UNIT IS 'Grouping units of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.APPLICATION_NAME IS 'Name of the application that uses the parameter.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.PARAMETER_NAME IS 'The name of the parameter. This is the primary lookup column in the table.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.DESCRIPTION IS 'User friendly description of the parameter.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.VALUE IS 'The value for the parameter that is used by the processes and scripts.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.DATA_LENGTH IS 'Length of the parameter''s value.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.FORMAT_MASK IS 'Formatting mask to be used on the parameter''s value. This could be a date format like DD-MON-YYYY or a numeric format like 9,999.99.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.INACTIVE_FLAG IS 'Specifies if the parameter can be used.';

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

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN ADRMGT.ADR_PARAMETER.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n indicating changed 1b to "n" time(s) after initial creation.';


CREATE UNIQUE INDEX ADRMGT.ADR_PARAMETER_ID_PK ON ADRMGT.ADR_PARAMETER
(ADR_PARAMETER_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 ADRMGT.ADR_PARAM_APP_PARMNAME_UK ON ADRMGT.ADR_PARAMETER
(APPLICATION_NAME, PARAMETER_NAME)
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 ADRMGT.ADR_PARAMETER_AUDIR_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON ADRMGT.ADR_PARAMETER
  FOR EACH ROW
DECLARE

    mDML   CHAR (1);

  BEGIN

    IF DELETING THEN

      INSERT
      INTO      ADRMGT.ADR_PARAMETER_H
                (Adr_parameter_h_id,
                 Transaction_type_code,
                 Adr_parameter_id,
                 Type,
                 Unit,
                 Application_name,
                 Parameter_name,
                 description,
                 Value,
                 Data_length,
                 Format_mask,
                 Inactive_flag,
                 Record_created_by,
                 Record_created_date,
                 Record_modified_by,
                 Record_modified_date,
                 Record_modified_count,
                 History_created_date)
      VALUES    (ADRMGT.Adr_Parameter_H_Id_S.NEXTVAL,
                 'D',
                 :OLD.Adr_parameter_id,
                 :OLD.Type,
                 :OLD.Unit,
                 :OLD.Application_name,
                 :OLD.Parameter_name,
                 :OLD.Description,
                 :OLD.Value,
                 :OLD.Data_length,
                 :OLD.Format_mask,
                 :OLD.Inactive_flag,
                 :OLD.Record_created_by,
                 :OLD.Record_created_date,
                 :OLD.Record_modified_by,
                 :OLD.Record_modified_date,
                 :OLD.Record_modified_count,
                 SYSDATE);

    ELSE

      IF UPDATING THEN

         mDML := 'U';

      ELSE

         mDML := 'I';

      END IF;

      INSERT
      INTO      ADRMGT.ADR_PARAMETER_H
                (Adr_parameter_h_id,
                 Transaction_type_code,
                 Adr_parameter_id,
                 Type,
                 Unit,
                 Application_name,
                 Parameter_name,
                 Description,
                 Value,
                 Data_length,
                 Format_mask,
                 Inactive_flag,
                 Record_created_by,
                 Record_created_date,
                 Record_modified_by,
                 Record_modified_date,
                 Record_modified_count,
                 History_created_date)
      VALUES    (ADRMGT.Adr_Parameter_H_Id_S.NEXTVAL,
                 mDML,
                 :NEW.Adr_parameter_id,
                 :NEW.type,
                 :NEW.unit,
                 :NEW.Application_name,
                 :NEW.Parameter_name,
                 :NEW.description,
                 :NEW.value,
                 :NEW.data_length,
                 :NEW.format_mask,
                 :NEW.Inactive_flag,
                 :NEW.Record_created_by,
                 :NEW.Record_created_date,
                 :NEW.Record_modified_by,
                 :NEW.Record_modified_date,
                 :NEW.Record_modified_count,
                 SYSDATE);

    END IF;

END Parameter_Audir_Trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM ADR_PARAMETER FOR ADRMGT.ADR_PARAMETER;


ALTER TABLE ADRMGT.ADR_PARAMETER ADD (
  CONSTRAINT ADR_PARAM_INACTIVE_FLAG_CK
  CHECK (Inactive_flag IN ('Y','N'))
  ENABLE VALIDATE,
  CONSTRAINT ADR_PARAM_TYPE_CK
  CHECK (Type IN ('NUMERIC','ALPHANUMERIC'))
  ENABLE VALIDATE,
  CONSTRAINT ADR_PARAM_UNITS_CK
  CHECK (Unit IN ('YEAR','MONTH','WEEK','DAY','HOUR',                      'MINUTE','COUNT','DATE','TIME'))
  ENABLE VALIDATE,
  CONSTRAINT ADR_PARAMETER_ID_PK
  PRIMARY KEY
  (ADR_PARAMETER_ID)
  USING INDEX ADRMGT.ADR_PARAMETER_ID_PK
  ENABLE VALIDATE);

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_PARAMETER TO ADR;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_PARAMETER TO ADREXT;

GRANT SELECT ON ADRMGT.ADR_PARAMETER TO ADRMGT_APP_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_PARAMETER TO ADRMGT_APP_RW;

GRANT SELECT ON ADRMGT.ADR_PARAMETER TO EEUSR;

GRANT DELETE, INSERT, SELECT, UPDATE ON ADRMGT.ADR_PARAMETER TO PSIM;
DROP TABLE ADRMGT.TEST_STATS1 CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.TEST_STATS1
(
  STATID   VARCHAR2(30 BYTE),
  TYPE     CHAR(1 BYTE),
  VERSION  NUMBER,
  FLAGS    NUMBER,
  C1       VARCHAR2(30 BYTE),
  C2       VARCHAR2(30 BYTE),
  C3       VARCHAR2(30 BYTE),
  C4       VARCHAR2(30 BYTE),
  C5       VARCHAR2(30 BYTE),
  N1       NUMBER,
  N2       NUMBER,
  N3       NUMBER,
  N4       NUMBER,
  N5       NUMBER,
  N6       NUMBER,
  N7       NUMBER,
  N8       NUMBER,
  N9       NUMBER,
  N10      NUMBER,
  N11      NUMBER,
  N12      NUMBER,
  D1       DATE,
  R1       RAW(32),
  R2       RAW(32),
  CH1      VARCHAR2(1000 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
DROP TABLE ADRMGT.TEST_STATS CASCADE CONSTRAINTS;

CREATE TABLE ADRMGT.TEST_STATS
(
  STATID   VARCHAR2(30 BYTE),
  TYPE     CHAR(1 BYTE),
  VERSION  NUMBER,
  FLAGS    NUMBER,
  C1       VARCHAR2(30 BYTE),
  C2       VARCHAR2(30 BYTE),
  C3       VARCHAR2(30 BYTE),
  C4       VARCHAR2(30 BYTE),
  C5       VARCHAR2(30 BYTE),
  N1       NUMBER,
  N2       NUMBER,
  N3       NUMBER,
  N4       NUMBER,
  N5       NUMBER,
  N6       NUMBER,
  N7       NUMBER,
  N8       NUMBER,
  N9       NUMBER,
  N10      NUMBER,
  N11      NUMBER,
  N12      NUMBER,
  D1       DATE,
  R1       RAW(32),
  R2       RAW(32),
  CH1      VARCHAR2(1000 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
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
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
