ALTER TABLE PATS.COMPS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.COMPS CASCADE CONSTRAINTS;

CREATE TABLE PATS.COMPS
(
  ID                 NUMBER(10),
  COMP_NAME          VARCHAR2(30 BYTE)          NOT NULL,
  INSTITUTION_FK     NUMBER(20)                 NOT NULL,
  INACTIVATION_DATE  DATE,
  VER                DATE                       NOT NULL,
  UPPER_CASE_NAME    VARCHAR2(30 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.COMPS.INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';

COMMENT ON COLUMN PATS.COMPS.UPPER_CASE_NAME IS 'Used to sort output';


CREATE INDEX PATS.FK_COMPS_INST ON PATS.COMPS
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_COMPS ON PATS.COMPS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_COMP_NAME_INST ON PATS.COMPS
(UPPER_CASE_NAME, INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_COMP_ID BEFORE
INSERT ON PATS.COMPS FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
    SELECT pats.comps_seq.NEXTVAL
      INTO :new.id
      FROM DUAL;
 END IF;
END generate_comp_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_COMP_NAME BEFORE
INSERT
OR UPDATE ON PATS.COMPS FOR EACH ROW
BEGIN
  IF (:old.comp_name IS NULL) OR (:old.comp_name != :new.comp_name) THEN
   :new.upper_case_name := UPPER(:new.comp_name);
  END IF;
 END update_uppercase_comp_name;
/


CREATE OR REPLACE SYNONYM PATS.SYN_COMPS FOR PATS.COMPS;


ALTER TABLE PATS.COMPS ADD (
  CONSTRAINT PK_COMPS
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_COMPS
  ENABLE VALIDATE,
  CONSTRAINT U_COMP_NAME_INST
  UNIQUE (UPPER_CASE_NAME, INSTITUTION_FK)
  USING INDEX PATS.U_COMP_NAME_INST
  ENABLE VALIDATE);

ALTER TABLE PATS.COMPS ADD (
  CONSTRAINT FK_COMPS_INST 
  FOREIGN KEY (INSTITUTION_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PATS.COMPS TO PATSRPTS;

GRANT SELECT ON PATS.COMPS TO PATS_R;

GRANT SELECT ON PATS.COMPS TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.COMPS TO PATS_RW;

GRANT SELECT ON PATS.COMPS TO TIER_PATS_ROLE;
ALTER TABLE PATS.CONGRESSIONAL_CONTACT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.CONGRESSIONAL_CONTACT CASCADE CONSTRAINTS;

CREATE TABLE PATS.CONGRESSIONAL_CONTACT
(
  ID                     NUMBER(10)             NOT NULL,
  OFFICE_OR_PERSON_NAME  VARCHAR2(60 BYTE)      NOT NULL,
  INSTITUTION_FK         NUMBER(20)             NOT NULL,
  INACTIVATION_DATE      DATE,
  VER                    DATE                   NOT NULL,
  UPPER_CASE_NAME        VARCHAR2(60 BYTE)      NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.CONGRESSIONAL_CONTACT.INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';


CREATE INDEX PATS.FK_CC_INST ON PATS.CONGRESSIONAL_CONTACT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_CC_ID ON PATS.CONGRESSIONAL_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_CCNAME_INSTITUTION ON PATS.CONGRESSIONAL_CONTACT
(INSTITUTION_FK, OFFICE_OR_PERSON_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_CC_ID BEFORE
INSERT ON PATS.CONGRESSIONAL_CONTACT FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
    SELECT pats.congressional_contact_seq.NEXTVAL
      INTO :new.id
      FROM DUAL;
 END IF;
END generate_cc_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_CC_NAME BEFORE
INSERT
OR UPDATE ON PATS.CONGRESSIONAL_CONTACT FOR EACH ROW
BEGIN
  IF (:old.office_or_person_name IS NULL) OR (:old.office_or_person_name != :new.office_or_person_name) THEN
   :new.upper_case_name := UPPER(:new.office_or_person_name);
  END IF;
 END update_uppercase_cc_name;
/


ALTER TABLE PATS.CONGRESSIONAL_CONTACT ADD (
  CONSTRAINT PK_CC_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_CC_ID
  ENABLE VALIDATE,
  CONSTRAINT U_CCNAME_INSTITUTION
  UNIQUE (INSTITUTION_FK, OFFICE_OR_PERSON_NAME)
  USING INDEX PATS.U_CCNAME_INSTITUTION
  ENABLE VALIDATE);

ALTER TABLE PATS.CONGRESSIONAL_CONTACT ADD (
  CONSTRAINT FK_CC_INST 
  FOREIGN KEY (INSTITUTION_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PATS.CONGRESSIONAL_CONTACT TO PATSRPTS;

GRANT SELECT ON PATS.CONGRESSIONAL_CONTACT TO PATS_R;

GRANT SELECT ON PATS.CONGRESSIONAL_CONTACT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.CONGRESSIONAL_CONTACT TO PATS_RW;

GRANT SELECT ON PATS.CONGRESSIONAL_CONTACT TO TIER_PATS_ROLE;
ALTER TABLE PATS.CONTACTING_ENTITY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.CONTACTING_ENTITY CASCADE CONSTRAINTS;

CREATE TABLE PATS.CONTACTING_ENTITY
(
  ID                      NUMBER(10)            NOT NULL,
  CONTACTING_ENTITY_NAME  VARCHAR2(50 BYTE)     NOT NULL,
  INACTIVATION_DATE       DATE,
  VER                     DATE                  NOT NULL,
  SORT_ORDER              NUMBER(10)            NOT NULL,
  ROLLUP_CODE             VARCHAR2(2 BYTE)      NOT NULL,
  UPPER_CASE_NAME         VARCHAR2(50 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.CONTACTING_ENTITY.ROLLUP_CODE IS 'Abbreviation used to designate this contacting entity when rolling the code up to Austin for National Reporting';


CREATE UNIQUE INDEX PATS.PK_CE_ID ON PATS.CONTACTING_ENTITY
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_CE_NAME ON PATS.CONTACTING_ENTITY
(CONTACTING_ENTITY_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_CE_ROLLUP_CODE ON PATS.CONTACTING_ENTITY
(ROLLUP_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_CE_UPPERCASE_NAME ON PATS.CONTACTING_ENTITY
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_CONTACTING_ENTITY_ID BEFORE
INSERT ON PATS.CONTACTING_ENTITY FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.contacting_entity_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_contacting_entity_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_CE_NAME BEFORE
INSERT
OR UPDATE ON PATS.CONTACTING_ENTITY FOR EACH ROW
BEGIN
  IF (:old.contacting_entity_name IS NULL) OR (:old.contacting_entity_name != :new.contacting_entity_name) THEN
   :new.upper_case_name := UPPER(:new.contacting_entity_name);
  END IF;
 END update_uppercase_ce_name;
/


ALTER TABLE PATS.CONTACTING_ENTITY ADD (
  CONSTRAINT PK_CE_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_CE_ID
  ENABLE VALIDATE,
  CONSTRAINT U_CE_NAME
  UNIQUE (CONTACTING_ENTITY_NAME)
  USING INDEX PATS.U_CE_NAME
  ENABLE VALIDATE,
  CONSTRAINT U_CE_ROLLUP_CODE
  UNIQUE (ROLLUP_CODE)
  USING INDEX PATS.U_CE_ROLLUP_CODE
  ENABLE VALIDATE,
  CONSTRAINT U_CE_UPPERCASE_NAME
  UNIQUE (UPPER_CASE_NAME)
  USING INDEX PATS.U_CE_UPPERCASE_NAME
  ENABLE VALIDATE);

GRANT SELECT ON PATS.CONTACTING_ENTITY TO PATSRPTS;

GRANT SELECT ON PATS.CONTACTING_ENTITY TO PATS_R;

GRANT SELECT ON PATS.CONTACTING_ENTITY TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.CONTACTING_ENTITY TO PATS_RW;

GRANT SELECT ON PATS.CONTACTING_ENTITY TO TIER_PATS_ROLE;
ALTER TABLE PATS.DMLOG
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.DMLOG CASCADE CONSTRAINTS;

CREATE TABLE PATS.DMLOG
(
  STATIONNUMBER  VARCHAR2(7 BYTE),
  MESSAGE        VARCHAR2(4000 BYTE),
  ACTIONDATE     TIMESTAMP(6),
  ID             NUMBER(10)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_DMLOG_ID ON PATS.DMLOG
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_DMLOG_ID BEFORE
INSERT ON PATS.DMLOG FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
    SELECT pats.dmlog_seq.NEXTVAL
      INTO :new.id
      FROM DUAL;
 END IF;
END generate_dmlog_id;
/


ALTER TABLE PATS.DMLOG ADD (
  CONSTRAINT PK_DMLOG_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_DMLOG_ID
  ENABLE VALIDATE);

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.DMLOG TO PATSHOST_ROLE;

GRANT SELECT ON PATS.DMLOG TO PATS_R;

GRANT SELECT ON PATS.DMLOG TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.DMLOG TO PATS_RW;

GRANT SELECT ON PATS.DMLOG TO TIER_PATS_ROLE;
ALTER TABLE PATS.FACILITY_SERVICE_OR_SECTION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.FACILITY_SERVICE_OR_SECTION CASCADE CONSTRAINTS;

CREATE TABLE PATS.FACILITY_SERVICE_OR_SECTION
(
  ID                       NUMBER(10)           NOT NULL,
  SERVICE_OR_SECTION_NAME  VARCHAR2(50 BYTE)    NOT NULL,
  VISN_FK                  NUMBER(20)           NOT NULL,
  INACTIVATION_DATE        DATE,
  VER                      DATE                 NOT NULL,
  UPPER_CASE_NAME          VARCHAR2(50 BYTE)    NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.FACILITY_SERVICE_OR_SECTION.VISN_FK IS 'References id on SDSADM.STD_INSTITUTION table';

COMMENT ON COLUMN PATS.FACILITY_SERVICE_OR_SECTION.UPPER_CASE_NAME IS 'Used to sort entries for display.';


CREATE INDEX PATS.FK_FSOS_VISN ON PATS.FACILITY_SERVICE_OR_SECTION
(VISN_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.SYS_C003094 ON PATS.FACILITY_SERVICE_OR_SECTION
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_FSOS_NAME_VISN ON PATS.FACILITY_SERVICE_OR_SECTION
(UPPER_CASE_NAME, VISN_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_FACILITY_SERV_SECT_ID BEFORE
INSERT ON PATS.FACILITY_SERVICE_OR_SECTION FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.facility_serv_or_sect_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_facility_serv_sect_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_FSS_NAME BEFORE
INSERT
OR UPDATE ON PATS.FACILITY_SERVICE_OR_SECTION FOR EACH ROW
BEGIN
  IF (:old.service_or_section_name IS NULL) OR (:old.service_or_section_name != :new.service_or_section_name) THEN
   :new.upper_case_name := UPPER(:new.service_or_section_name);
  END IF;
 END update_uppercase_fss_name;
/


ALTER TABLE PATS.FACILITY_SERVICE_OR_SECTION ADD (
  CONSTRAINT PK_FSOS_ID
  PRIMARY KEY
  (ID)
  USING INDEX
    TABLESPACE M_PATS_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
  ENABLE VALIDATE,
  CONSTRAINT U_FSOS_NAME_VISN
  UNIQUE (UPPER_CASE_NAME, VISN_FK)
  USING INDEX PATS.U_FSOS_NAME_VISN
  ENABLE VALIDATE);

ALTER TABLE PATS.FACILITY_SERVICE_OR_SECTION ADD (
  CONSTRAINT FK_FSOS_VISN 
  FOREIGN KEY (VISN_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PATS.FACILITY_SERVICE_OR_SECTION TO PATSRPTS;

GRANT SELECT ON PATS.FACILITY_SERVICE_OR_SECTION TO PATS_R;

GRANT SELECT ON PATS.FACILITY_SERVICE_OR_SECTION TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.FACILITY_SERVICE_OR_SECTION TO PATS_RW;

GRANT SELECT ON PATS.FACILITY_SERVICE_OR_SECTION TO TIER_PATS_ROLE;
ALTER TABLE PATS.HOSPITAL_LOCATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.HOSPITAL_LOCATION CASCADE CONSTRAINTS;

CREATE TABLE PATS.HOSPITAL_LOCATION
(
  ID                 NUMBER(10)                 NOT NULL,
  LOCATION_NAME      VARCHAR2(30 BYTE)          NOT NULL,
  INACTIVATION_DATE  DATE                       DEFAULT NULL,
  INSTITUTION_FK     NUMBER(20)                 NOT NULL,
  VER                DATE                       NOT NULL,
  UPPER_CASE_NAME    VARCHAR2(30 BYTE)          NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.HOSPITAL_LOCATION.ID IS 'Unique identifier for this table.';

COMMENT ON COLUMN PATS.HOSPITAL_LOCATION.INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';


CREATE INDEX PATS.FK_HL_INST ON PATS.HOSPITAL_LOCATION
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_HOSPITAL_LOCATION0 ON PATS.HOSPITAL_LOCATION
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_INST_AND_HLNAME ON PATS.HOSPITAL_LOCATION
(INSTITUTION_FK, LOCATION_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_HOSPITAL_LOCATION_ID BEFORE
INSERT ON PATS.HOSPITAL_LOCATION FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.hospital_location_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_hospital_location_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_HL_NAME BEFORE
INSERT
OR UPDATE ON PATS.HOSPITAL_LOCATION FOR EACH ROW
BEGIN
  IF (:old.location_name IS NULL) OR (:old.location_name != :new.location_name) THEN
   :new.upper_case_name := UPPER(:new.location_name);
  END IF;
 END update_uppercase_hl_name;
/


ALTER TABLE PATS.HOSPITAL_LOCATION ADD (
  CONSTRAINT PK_HL_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_HOSPITAL_LOCATION0
  ENABLE VALIDATE,
  CONSTRAINT U_INST_AND_HLNAME
  UNIQUE (INSTITUTION_FK, LOCATION_NAME)
  USING INDEX PATS.U_INST_AND_HLNAME
  ENABLE VALIDATE);

GRANT SELECT ON PATS.HOSPITAL_LOCATION TO PATSRPTS;

GRANT SELECT ON PATS.HOSPITAL_LOCATION TO PATS_R;

GRANT SELECT ON PATS.HOSPITAL_LOCATION TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.HOSPITAL_LOCATION TO PATS_RW;

GRANT SELECT ON PATS.HOSPITAL_LOCATION TO TIER_PATS_ROLE;
ALTER TABLE PATS.ISSUE_CATEGORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ISSUE_CATEGORY CASCADE CONSTRAINTS;

CREATE TABLE PATS.ISSUE_CATEGORY
(
  ISSUE_CATEGORY_CODE           VARCHAR2(2 BYTE) NOT NULL,
  ISSUE_CATEGORY_NAME           VARCHAR2(50 BYTE) NOT NULL,
  IS_CUSTOMER_SERVICE_STANDARD  NUMBER(1),
  INACTIVATION_DATE             DATE,
  VER                           DATE            NOT NULL,
  SORT_ORDER                    INTEGER         NOT NULL,
  UPPER_CASE_NAME               VARCHAR2(50 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_ISSUE_CATEGORY_CODE ON PATS.ISSUE_CATEGORY
(ISSUE_CATEGORY_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_ISSCAT_UPPERCASE_NAME ON PATS.ISSUE_CATEGORY
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_ISSUE_CATEGORY_NAME ON PATS.ISSUE_CATEGORY
(ISSUE_CATEGORY_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_ISSCAT_NAME BEFORE
INSERT
OR UPDATE ON PATS.ISSUE_CATEGORY FOR EACH ROW
BEGIN
  IF (:old.issue_category_name IS NULL) OR (:old.issue_category_name != :new.issue_category_name) THEN
   :new.upper_case_name := UPPER(:new.issue_category_name);
  END IF;
 END update_uppercase_isscat_name;
/


ALTER TABLE PATS.ISSUE_CATEGORY ADD (
  CONSTRAINT CK_IS_CUST_SERV_STD
  CHECK (is_customer_service_standard=0 OR is_customer_service_standard=1)
  ENABLE VALIDATE,
  CONSTRAINT PK_ISSUE_CATEGORY_CODE
  PRIMARY KEY
  (ISSUE_CATEGORY_CODE)
  USING INDEX PATS.PK_ISSUE_CATEGORY_CODE
  ENABLE VALIDATE,
  CONSTRAINT U_ISSCAT_UPPERCASE_NAME
  UNIQUE (UPPER_CASE_NAME)
  USING INDEX PATS.U_ISSCAT_UPPERCASE_NAME
  ENABLE VALIDATE,
  CONSTRAINT U_ISSUE_CATEGORY_NAME
  UNIQUE (ISSUE_CATEGORY_NAME)
  USING INDEX PATS.U_ISSUE_CATEGORY_NAME
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ISSUE_CATEGORY TO PATSRPTS;

GRANT SELECT ON PATS.ISSUE_CATEGORY TO PATS_R;

GRANT SELECT ON PATS.ISSUE_CATEGORY TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ISSUE_CATEGORY TO PATS_RW;

GRANT SELECT ON PATS.ISSUE_CATEGORY TO TIER_PATS_ROLE;
ALTER TABLE PATS.ISSUE_CODE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ISSUE_CODE CASCADE CONSTRAINTS;

CREATE TABLE PATS.ISSUE_CODE
(
  ISSUE_CODE         VARCHAR2(5 BYTE)           NOT NULL,
  ISSUE_CODE_NAME    VARCHAR2(60 BYTE)          NOT NULL,
  ISSUE_CATEGORY_FK  VARCHAR2(2 BYTE)           NOT NULL,
  DESCRIPTION        VARCHAR2(1000 BYTE),
  INACTIVATION_DATE  DATE,
  VER                DATE                       NOT NULL,
  SORT_CODE          VARCHAR2(5 BYTE),
  UPPER_CASE_NAME    VARCHAR2(60 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.ISSUE_CODE.SORT_CODE IS 'Used for sorting only - triggered from issue code field';


CREATE UNIQUE INDEX PATS.PK_ISSUE_CODE ON PATS.ISSUE_CODE
(ISSUE_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_ISSUE_CODE_NAME_AND_CATEGORY ON PATS.ISSUE_CODE
(ISSUE_CATEGORY_FK, ISSUE_CODE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_ISSUE_CODE_UPPERCASE_NAME ON PATS.ISSUE_CODE
(UPPER_CASE_NAME, ISSUE_CATEGORY_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.UPDATE_ISSUE_CODE_FOR_SORT BEFORE
INSERT
OR UPDATE ON PATS.ISSUE_CODE FOR EACH ROW
BEGIN
  IF (:old.issue_code IS NULL) OR (:old.issue_code != :new.issue_code)
   THEN
   :new.sort_code := :new.issue_category_fk||SUBSTR(TO_CHAR(TO_NUMBER(SUBSTR(:new.issue_code,3,3)),'009'),2,3);
  END IF;
 END update_issue_code_for_sort;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_ISSCODE_NAME BEFORE
INSERT
OR UPDATE ON PATS.ISSUE_CODE FOR EACH ROW
BEGIN
  IF (:old.issue_code_name IS NULL) OR (:old.issue_code_name != :new.issue_code_name) THEN
   :new.upper_case_name := UPPER(:new.issue_code_name);
  END IF;
 END update_uppercase_isscode_name;
/


ALTER TABLE PATS.ISSUE_CODE ADD (
  CONSTRAINT CK_ISSUE_CODE
  CHECK (SUBSTR(ISSUE_CODE,1,LENGTH(ISSUE_CATEGORY_FK))=ISSUE_CATEGORY_FK)
  ENABLE VALIDATE,
  CONSTRAINT PK_ISSUE_CODE
  PRIMARY KEY
  (ISSUE_CODE)
  USING INDEX PATS.PK_ISSUE_CODE
  ENABLE VALIDATE,
  CONSTRAINT U_ISSUE_CODE_NAME_AND_CATEGORY
  UNIQUE (ISSUE_CATEGORY_FK, ISSUE_CODE_NAME)
  USING INDEX PATS.U_ISSUE_CODE_NAME_AND_CATEGORY
  ENABLE VALIDATE,
  CONSTRAINT U_ISSUE_CODE_UPPERCASE_NAME
  UNIQUE (UPPER_CASE_NAME, ISSUE_CATEGORY_FK)
  USING INDEX PATS.U_ISSUE_CODE_UPPERCASE_NAME
  ENABLE VALIDATE);

ALTER TABLE PATS.ISSUE_CODE ADD (
  CONSTRAINT FK_ISSUE_CODE_CATEGORY 
  FOREIGN KEY (ISSUE_CATEGORY_FK) 
  REFERENCES PATS.ISSUE_CATEGORY (ISSUE_CATEGORY_CODE)
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ISSUE_CODE TO PATSRPTS;

GRANT SELECT ON PATS.ISSUE_CODE TO PATS_R;

GRANT SELECT ON PATS.ISSUE_CODE TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ISSUE_CODE TO PATS_RW;

GRANT SELECT ON PATS.ISSUE_CODE TO TIER_PATS_ROLE;
ALTER TABLE PATS.METHOD_OF_CONTACT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.METHOD_OF_CONTACT CASCADE CONSTRAINTS;

CREATE TABLE PATS.METHOD_OF_CONTACT
(
  ID                      NUMBER(10)            NOT NULL,
  METHOD_OF_CONTACT_NAME  VARCHAR2(20 BYTE)     NOT NULL,
  INACTIVATION_DATE       DATE,
  SORT_ORDER              NUMBER(10)            NOT NULL,
  VER                     DATE                  NOT NULL,
  ROLLUP_CODE             CHAR(1 BYTE)          NOT NULL,
  UPPER_CASE_NAME         VARCHAR2(20 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.METHOD_OF_CONTACT.ROLLUP_CODE IS 'Abbreviation used to designate this method of contact when rolling the code up to Austin for National Reporting';


CREATE UNIQUE INDEX PATS.PK_MOC_ID ON PATS.METHOD_OF_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_MOC_NAME ON PATS.METHOD_OF_CONTACT
(METHOD_OF_CONTACT_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_MOC_ROLLUP_CODE ON PATS.METHOD_OF_CONTACT
(ROLLUP_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_MOC_UPPERCASE_NAME ON PATS.METHOD_OF_CONTACT
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_METHOD_OF_CONTACT_ID BEFORE
INSERT ON PATS.METHOD_OF_CONTACT FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.method_of_contact_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_method_of_contact_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_MOC_NAME BEFORE
INSERT
OR UPDATE ON PATS.METHOD_OF_CONTACT FOR EACH ROW
BEGIN
  IF (:old.method_of_contact_name IS NULL) OR (:old.method_of_contact_name != :new.method_of_contact_name) THEN
   :new.upper_case_name := UPPER(:new.method_of_contact_name);
  END IF;
 END update_uppercase_moc_name;
/


ALTER TABLE PATS.METHOD_OF_CONTACT ADD (
  CONSTRAINT PK_MOC_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_MOC_ID
  ENABLE VALIDATE,
  CONSTRAINT U_MOC_NAME
  UNIQUE (METHOD_OF_CONTACT_NAME)
  USING INDEX PATS.U_MOC_NAME
  ENABLE VALIDATE,
  CONSTRAINT U_MOC_ROLLUP_CODE
  UNIQUE (ROLLUP_CODE)
  USING INDEX PATS.U_MOC_ROLLUP_CODE
  ENABLE VALIDATE,
  CONSTRAINT U_MOC_UPPERCASE_NAME
  UNIQUE (UPPER_CASE_NAME)
  USING INDEX PATS.U_MOC_UPPERCASE_NAME
  ENABLE VALIDATE);

GRANT SELECT ON PATS.METHOD_OF_CONTACT TO PATSRPTS;

GRANT SELECT ON PATS.METHOD_OF_CONTACT TO PATS_R;

GRANT SELECT ON PATS.METHOD_OF_CONTACT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.METHOD_OF_CONTACT TO PATS_RW;

GRANT SELECT ON PATS.METHOD_OF_CONTACT TO TIER_PATS_ROLE;
ALTER TABLE PATS.NATIONAL_PATS_PARAMETERS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.NATIONAL_PATS_PARAMETERS CASCADE CONSTRAINTS;

CREATE TABLE PATS.NATIONAL_PATS_PARAMETERS
(
  ID                   NUMBER(1),
  DAYS_TO_PROCESS_ROC  NUMBER(5)                NOT NULL,
  DATE_EDITED          DATE                     NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.NATIONAL_PATS_PARAMETERS.ID IS 'This file has only one record.';


CREATE UNIQUE INDEX PATS.PK_NATIONAL_PATS_PARAMETERS ON PATS.NATIONAL_PATS_PARAMETERS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

ALTER TABLE PATS.NATIONAL_PATS_PARAMETERS ADD (
  CONSTRAINT CK_NATL_PARAM_ID
  CHECK (ID=1)
  ENABLE VALIDATE,
  CONSTRAINT PK_NATIONAL_PATS_PARAMETERS
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_NATIONAL_PATS_PARAMETERS
  ENABLE VALIDATE);

GRANT SELECT ON PATS.NATIONAL_PATS_PARAMETERS TO PATS_R;

GRANT SELECT ON PATS.NATIONAL_PATS_PARAMETERS TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.NATIONAL_PATS_PARAMETERS TO PATS_RW;

GRANT SELECT ON PATS.NATIONAL_PATS_PARAMETERS TO TIER_PATS_ROLE;
ALTER TABLE PATS.PATS_PATIENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.PATS_PATIENT CASCADE CONSTRAINTS;

CREATE TABLE PATS.PATS_PATIENT
(
  ID                          NUMBER(10)        NOT NULL,
  INTEGRATION_CONTROL_NUMBER  VARCHAR2(29 BYTE),
  LAST_NAME                   VARCHAR2(35 BYTE) NOT NULL,
  FIRST_NAME                  VARCHAR2(25 BYTE) NOT NULL,
  MIDDLE_NAME                 VARCHAR2(25 BYTE),
  NAME_PREFIX                 VARCHAR2(10 BYTE),
  NAME_SUFFIX                 VARCHAR2(10 BYTE),
  ACADEMIC_DEGREE             VARCHAR2(10 BYTE),
  GENDER                      CHAR(1 BYTE)      NOT NULL,
  DATE_OF_BIRTH               DATE              NOT NULL,
  SOCIAL_SECURITY_NUMBER      VARCHAR2(9 BYTE),
  IS_PSEUDO_SSN               NUMBER(1),
  ELIGIBILITY_CODE            VARCHAR2(30 BYTE),
  ENROLLMENT_PRIORITY         VARCHAR2(20 BYTE),
  PERIOD_OF_SERVICE           VARCHAR2(25 BYTE),
  IS_SERVICE_CONNECTED        NUMBER(1),
  SERVICE_CONNECTED_PERCENT   NUMBER(3),
  NSSN_LOOKUP_VALUE           VARCHAR2(5 BYTE),
  INSTITUTION_FK              NUMBER(20)        NOT NULL,
  VISTA_IEN                   NUMBER(18,6)      NOT NULL,
  STD_NAME_FOR_LOOKUP         VARCHAR2(72 BYTE) NOT NULL,
  STD_FIRST_NAME              VARCHAR2(25 BYTE) NOT NULL,
  CATEGORY                    VARCHAR2(30 BYTE),
  ETHNICITY_FK                NUMBER(20),
  INACTIVATION_DATE           DATE
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.PATS_PATIENT.IS_PSEUDO_SSN IS 'Boolean set to 1 if social security number is a pseudo ssn (i.e., assigned by computer operator because real ssn is not available.)';

COMMENT ON COLUMN PATS.PATS_PATIENT.ENROLLMENT_PRIORITY IS 'Enrollment Priority based on Patient''s Current Enrollment';

COMMENT ON COLUMN PATS.PATS_PATIENT.IS_SERVICE_CONNECTED IS 'Binary value set to 1 if veteran''s disability is service connected.';

COMMENT ON COLUMN PATS.PATS_PATIENT.SERVICE_CONNECTED_PERCENT IS 'An integer between 0 and 100, indicating the percentage of veteran''s service connected disability.';

COMMENT ON COLUMN PATS.PATS_PATIENT.INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';

COMMENT ON COLUMN PATS.PATS_PATIENT.ETHNICITY_FK IS 'References id on SDSADM.STD_ETHNICITY table';

COMMENT ON COLUMN PATS.PATS_PATIENT.INACTIVATION_DATE IS 'If set, patient is considered inactive and cannot be selected.';


CREATE INDEX PATS.FK_PATIENT_INST ON PATS.PATS_PATIENT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_PATIENT_ID ON PATS.PATS_PATIENT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_PATIENT_INST_AND_VISTAIEN ON PATS.PATS_PATIENT
(INSTITUTION_FK, VISTA_IEN)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_PATIENT_ID BEFORE
INSERT ON PATS.PATS_PATIENT FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.pats_patient_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_patient_id;
/


ALTER TABLE PATS.PATS_PATIENT ADD (
  CONSTRAINT CK_GENDER
  CHECK (gender='M' OR gender='F')
  ENABLE VALIDATE,
  CONSTRAINT PK_PATIENT_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_PATIENT_ID
  ENABLE VALIDATE,
  CONSTRAINT U_PATIENT_INST_AND_VISTAIEN
  UNIQUE (INSTITUTION_FK, VISTA_IEN)
  USING INDEX PATS.U_PATIENT_INST_AND_VISTAIEN
  ENABLE VALIDATE);

ALTER TABLE PATS.PATS_PATIENT ADD (
  CONSTRAINT FK_PATIENT_ETHNICITY 
  FOREIGN KEY (ETHNICITY_FK) 
  REFERENCES SDSADM.STD_ETHNICITY (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT FK_PATIENT_INST 
  FOREIGN KEY (INSTITUTION_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PATS.PATS_PATIENT TO PATSRPTS;

GRANT SELECT ON PATS.PATS_PATIENT TO PATS_R;

GRANT SELECT ON PATS.PATS_PATIENT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.PATS_PATIENT TO PATS_RW;

GRANT SELECT ON PATS.PATS_PATIENT TO TIER_PATS_ROLE;
ALTER TABLE PATS.PATS_PATIENT_RACE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.PATS_PATIENT_RACE CASCADE CONSTRAINTS;

CREATE TABLE PATS.PATS_PATIENT_RACE
(
  ID               NUMBER(12)                   NOT NULL,
  PATIENT_RACE_FK  NUMBER(20)                   NOT NULL,
  PATIENT_FK       NUMBER(12)                   NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.PATS_PATIENT_RACE.PATIENT_RACE_FK IS 'References SDSADM.STD_RACE table';

COMMENT ON COLUMN PATS.PATS_PATIENT_RACE.PATIENT_FK IS 'References PATS.PATS_PATIENT table';


CREATE UNIQUE INDEX PATS.PK_PATS_PATIENT_RACE ON PATS.PATS_PATIENT_RACE
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_PATIENT_RACE_ID BEFORE
INSERT ON PATS.PATS_PATIENT_RACE FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.pats_patient_race_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_patient_race_id;
/


ALTER TABLE PATS.PATS_PATIENT_RACE ADD (
  CONSTRAINT PK_PATS_PATIENT_RACE
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_PATS_PATIENT_RACE
  ENABLE VALIDATE);

ALTER TABLE PATS.PATS_PATIENT_RACE ADD (
  CONSTRAINT FK_PATIENT 
  FOREIGN KEY (PATIENT_FK) 
  REFERENCES PATS.PATS_PATIENT (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_PATIENT_RACE 
  FOREIGN KEY (PATIENT_RACE_FK) 
  REFERENCES SDSADM.STD_RACE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PATS.PATS_PATIENT_RACE TO PATS_R;

GRANT SELECT ON PATS.PATS_PATIENT_RACE TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.PATS_PATIENT_RACE TO PATS_RW;

GRANT SELECT ON PATS.PATS_PATIENT_RACE TO TIER_PATS_ROLE;
ALTER TABLE PATS.PATS_ROLLUP_NATL_DATA
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.PATS_ROLLUP_NATL_DATA CASCADE CONSTRAINTS;

CREATE TABLE PATS.PATS_ROLLUP_NATL_DATA
(
  ID                  NUMBER(10)                NOT NULL,
  ROC_NUMBER          VARCHAR2(16 BYTE)         NOT NULL,
  OUTPUT_DATA_STRING  VARCHAR2(1000 BYTE)       NOT NULL,
  DATE_GENERATED      DATE                      NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_ROLLUP_ID ON PATS.PATS_ROLLUP_NATL_DATA
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_PATS_ROLLUP_ID BEFORE
INSERT ON PATS.PATS_ROLLUP_NATL_DATA FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
    SELECT pats_rollup_to_natl_seq.NEXTVAL
      INTO :new.id
      FROM DUAL;
 END IF;
END generate_pats_rollup_id;
/


ALTER TABLE PATS.PATS_ROLLUP_NATL_DATA ADD (
  CONSTRAINT PK_ROLLUP_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ROLLUP_ID
  ENABLE VALIDATE);

GRANT SELECT ON PATS.PATS_ROLLUP_NATL_DATA TO PATSROLLUP;

GRANT SELECT ON PATS.PATS_ROLLUP_NATL_DATA TO PATS_R;

GRANT SELECT ON PATS.PATS_ROLLUP_NATL_DATA TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.PATS_ROLLUP_NATL_DATA TO PATS_RW;

GRANT SELECT ON PATS.PATS_ROLLUP_NATL_DATA TO TIER_PATS_ROLE;

GRANT SELECT ON PATS.PATS_ROLLUP_NATL_DATA TO VSCCUSR_R;
ALTER TABLE PATS.PATS_USER
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.PATS_USER CASCADE CONSTRAINTS;

CREATE TABLE PATS.PATS_USER
(
  USER_IDENTIFIER        VARCHAR2(60 BYTE)      NOT NULL,
  LAST_NAME              VARCHAR2(35 BYTE)      NOT NULL,
  FIRST_NAME             VARCHAR2(25 BYTE)      NOT NULL,
  MIDDLE_NAME            VARCHAR2(25 BYTE),
  NAME_PREFIX            VARCHAR2(10 BYTE),
  NAME_SUFFIX            VARCHAR2(10 BYTE),
  ACADEMIC_DEGREE        VARCHAR2(10 BYTE),
  STD_NAME_FOR_LOOKUP    VARCHAR2(72 BYTE)      NOT NULL,
  STD_FIRST_NAME         VARCHAR2(25 BYTE)      NOT NULL,
  ID                     NUMBER(10)             NOT NULL,
  INACTIVATION_DATE      DATE,
  E_MAIL_ADDRESS         VARCHAR2(50 BYTE),
  DISPLAY_EXPIRED_ARNS   NUMBER(1),
  MAIL_CODE              VARCHAR2(10 BYTE),
  TITLE                  VARCHAR2(30 BYTE),
  PARENT_INSTITUTION_FK  NUMBER(20)             NOT NULL,
  ADVOCATE_PHONE_NUMBER  VARCHAR2(50 BYTE),
  IS_508_ACCESSIBLE      NUMBER(1),
  VPID                   VARCHAR2(29 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.PATS_USER IS 'Local list of users who either have access to the PATS applications, who have been involved in a ROC, or who have been sent a Notification from PATS.';

COMMENT ON COLUMN PATS.PATS_USER.USER_IDENTIFIER IS 'KAAJEE User Name - Unique identifier containing IEN in VistA NEW PERSON file, and Station Number.';

COMMENT ON COLUMN PATS.PATS_USER.INACTIVATION_DATE IS 'If set, person is considered inactive and cannot be selected.';

COMMENT ON COLUMN PATS.PATS_USER.DISPLAY_EXPIRED_ARNS IS '1 if true, 0 if false';

COMMENT ON COLUMN PATS.PATS_USER.PARENT_INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';

COMMENT ON COLUMN PATS.PATS_USER.IS_508_ACCESSIBLE IS '1 if true, 0 if false';

COMMENT ON COLUMN PATS.PATS_USER.VPID IS 'Unique person identifier across the VA.';


CREATE INDEX PATS.FK_USER_INST ON PATS.PATS_USER
(PARENT_INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_USER_ID ON PATS.PATS_USER
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_USER_IDENTIFIER ON PATS.PATS_USER
(USER_IDENTIFIER)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_USER_ID BEFORE
INSERT ON PATS.PATS_USER FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.pats_user_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_user_id;
/


ALTER TABLE PATS.PATS_USER ADD (
  CONSTRAINT CHK_IS_508_ACCESSIBLE
  CHECK ((is_508_accessible IS NULL) OR (is_508_accessible=0) OR (is_508_accessible=1))
  ENABLE VALIDATE,
  CONSTRAINT CK_USER_DISPLAY_EXP_ARN
  CHECK ((display_expired_arns IS NULL) OR (display_expired_arns=1) OR (display_expired_arns=0))
  ENABLE VALIDATE,
  CONSTRAINT PK_USER_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_USER_ID
  ENABLE VALIDATE,
  CONSTRAINT U_USER_IDENTIFIER
  UNIQUE (USER_IDENTIFIER)
  USING INDEX PATS.U_USER_IDENTIFIER
  ENABLE VALIDATE);

GRANT SELECT ON PATS.PATS_USER TO PATSRPTS;

GRANT SELECT ON PATS.PATS_USER TO PATS_R;

GRANT SELECT ON PATS.PATS_USER TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.PATS_USER TO PATS_RW;

GRANT SELECT ON PATS.PATS_USER TO TIER_PATS_ROLE;
ALTER TABLE PATS.TREATMENT_STATUS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.TREATMENT_STATUS CASCADE CONSTRAINTS;

CREATE TABLE PATS.TREATMENT_STATUS
(
  ID                 NUMBER(5)                  NOT NULL,
  TREATMENT_STATUS   VARCHAR2(30 BYTE)          NOT NULL,
  INACTIVATION_DATE  DATE,
  SORT_ORDER         NUMBER(5)                  NOT NULL,
  VER                DATE                       NOT NULL,
  ROLLUP_CODE        CHAR(1 BYTE),
  UPPER_CASE_NAME    VARCHAR2(30 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            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 PATS.TREATMENT_STATUS.ROLLUP_CODE IS 'Abbreviation used to designate this treatment status when rolling the code up to Austin for National Reporting';


CREATE UNIQUE INDEX PATS.PK_ID ON PATS.TREATMENT_STATUS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_TS_TREATMENT_STATUS ON PATS.TREATMENT_STATUS
(TREATMENT_STATUS)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_TS_UPPERCASE_NAME ON PATS.TREATMENT_STATUS
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_TREATMENT_STATUS_ID BEFORE
INSERT ON PATS.TREATMENT_STATUS FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.treatment_status_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
END generate_treatment_status_id;
/


CREATE OR REPLACE TRIGGER PATS.UPDATE_UPPERCASE_TS_NAME BEFORE
INSERT
OR UPDATE ON PATS.TREATMENT_STATUS FOR EACH ROW
BEGIN
  IF (:old.treatment_status IS NULL) OR (:old.treatment_status != :new.treatment_status) THEN
   :new.upper_case_name := UPPER(:new.treatment_status);
  END IF;
 END update_uppercase_ts_name;
/


ALTER TABLE PATS.TREATMENT_STATUS ADD (
  CONSTRAINT PK_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ID
  ENABLE VALIDATE,
  CONSTRAINT U_TS_TREATMENT_STATUS
  UNIQUE (TREATMENT_STATUS)
  USING INDEX PATS.U_TS_TREATMENT_STATUS
  ENABLE VALIDATE,
  CONSTRAINT U_TS_UPPERCASE_NAME
  UNIQUE (UPPER_CASE_NAME)
  USING INDEX PATS.U_TS_UPPERCASE_NAME
  ENABLE VALIDATE);

GRANT SELECT ON PATS.TREATMENT_STATUS TO PATSRPTS;

GRANT SELECT ON PATS.TREATMENT_STATUS TO PATS_R;

GRANT SELECT ON PATS.TREATMENT_STATUS TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.TREATMENT_STATUS TO PATS_RW;

GRANT SELECT ON PATS.TREATMENT_STATUS TO TIER_PATS_ROLE;
ALTER TABLE PATS.BOILERPLATE_RESOLUTION_TEXT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.BOILERPLATE_RESOLUTION_TEXT CASCADE CONSTRAINTS;

CREATE TABLE PATS.BOILERPLATE_RESOLUTION_TEXT
(
  ID              NUMBER(10)                    NOT NULL,
  INSTITUTION_FK  NUMBER(20)                    NOT NULL,
  ISSUE_CODE_FK   VARCHAR2(5 BYTE)              NOT NULL,
  TEXT            VARCHAR2(4000 BYTE)           NOT NULL,
  VER             DATE                          NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_BOILER_ID ON PATS.BOILERPLATE_RESOLUTION_TEXT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_BOILER_ISSCODE_INST ON PATS.BOILERPLATE_RESOLUTION_TEXT
(INSTITUTION_FK, ISSUE_CODE_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_BOILERPLATE_TEXT_ID BEFORE
INSERT ON PATS.BOILERPLATE_RESOLUTION_TEXT FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
    SELECT pats.boilerplate_text_seq.NEXTVAL
      INTO :new.id
      FROM DUAL;
 END IF;
END generate_boilerplate_text_id;
/


ALTER TABLE PATS.BOILERPLATE_RESOLUTION_TEXT ADD (
  CONSTRAINT PK_BOILER_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_BOILER_ID
  ENABLE VALIDATE,
  CONSTRAINT U_BOILER_ISSCODE_INST
  UNIQUE (INSTITUTION_FK, ISSUE_CODE_FK)
  USING INDEX PATS.U_BOILER_ISSCODE_INST
  ENABLE VALIDATE);

ALTER TABLE PATS.BOILERPLATE_RESOLUTION_TEXT ADD (
  CONSTRAINT FK_BOILER_INST 
  FOREIGN KEY (INSTITUTION_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT FK_BOILER_ISSUE_CODE 
  FOREIGN KEY (ISSUE_CODE_FK) 
  REFERENCES PATS.ISSUE_CODE (ISSUE_CODE)
  ENABLE VALIDATE);

GRANT SELECT ON PATS.BOILERPLATE_RESOLUTION_TEXT TO PATS_R;

GRANT SELECT ON PATS.BOILERPLATE_RESOLUTION_TEXT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.BOILERPLATE_RESOLUTION_TEXT TO PATS_RW;

GRANT SELECT ON PATS.BOILERPLATE_RESOLUTION_TEXT TO TIER_PATS_ROLE;
ALTER TABLE PATS.REPORT_OF_CONTACT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.REPORT_OF_CONTACT CASCADE CONSTRAINTS;

CREATE TABLE PATS.REPORT_OF_CONTACT
(
  ROC_NUMBER                     VARCHAR2(16 BYTE) NOT NULL,
  DATE_OF_CONTACT                DATE           NOT NULL,
  INFO_TAKEN_BY_USER_FK          NUMBER(10)     NOT NULL,
  ENTERED_BY_USER_FK             NUMBER(10)     NOT NULL,
  PATIENT_FK                     NUMBER(10),
  TREATMENT_STATUS_FK            NUMBER(5)      NOT NULL,
  CONGRESSIONAL_CONTACT_FK       NUMBER(10),
  IS_INTERNAL_APPEAL             NUMBER(1)      NOT NULL,
  STATUS                         CHAR(1 BYTE)   NOT NULL,
  INSTITUTION_FK                 NUMBER(20)     NOT NULL,
  VER                            DATE           NOT NULL,
  ISSUE_TEXT                     VARCHAR2(4000 BYTE) NOT NULL,
  COMP_FK                        NUMBER(10),
  DATE_CLOSED                    DATE,
  DATE_OVERDUE                   DATE,
  ELIGIBILITY_STATUS             VARCHAR2(30 BYTE),
  CATEGORY                       VARCHAR2(30 BYTE),
  ROLLUP_TO_NATL_REPORTS_STATUS  NUMBER(1)      NOT NULL,
  RESOLUTION_TEXT1               VARCHAR2(4000 BYTE),
  RESOLUTION_TEXT2               VARCHAR2(4000 BYTE)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.ROC_NUMBER IS 'Station Number, a period, 4 digit fiscal year, then a 5 digit integer. Ex. 605.200200123';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.DATE_OF_CONTACT IS 'Defaults to current date on server.';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.INFO_TAKEN_BY_USER_FK IS 'Foreign Key referencing user table.';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.ENTERED_BY_USER_FK IS 'Used for auditing purposes only. Tracks who entered a complaint to the PATS computer system.';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.IS_INTERNAL_APPEAL IS 'Now called ''Clinical Appeal''  (Jan.15,2004)';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.INSTITUTION_FK IS 'References id on SDSADM.STD_INSTITUTION table';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.RESOLUTION_TEXT1 IS 'First 4000 characters of Resolution Text';

COMMENT ON COLUMN PATS.REPORT_OF_CONTACT.RESOLUTION_TEXT2 IS 'Characters 4001 through 8000 of Resolution Text';


CREATE INDEX PATS.DATE_OF_CONTACT ON PATS.REPORT_OF_CONTACT
(DATE_OF_CONTACT)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.DATE_OVERDUE ON PATS.REPORT_OF_CONTACT
(DATE_OVERDUE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_COMP ON PATS.REPORT_OF_CONTACT
(COMP_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_CONGRESSIONAL_CONTACT ON PATS.REPORT_OF_CONTACT
(CONGRESSIONAL_CONTACT_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_ENTBY ON PATS.REPORT_OF_CONTACT
(ENTERED_BY_USER_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_INFOBY ON PATS.REPORT_OF_CONTACT
(INFO_TAKEN_BY_USER_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_INSTITUTION ON PATS.REPORT_OF_CONTACT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_PATIENT ON PATS.REPORT_OF_CONTACT
(PATIENT_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.FK_ROC_TREATMENT_STATUS ON PATS.REPORT_OF_CONTACT
(TREATMENT_STATUS_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.PK_ROC_NUMBER ON PATS.REPORT_OF_CONTACT
(ROC_NUMBER)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.STATUS ON PATS.REPORT_OF_CONTACT
(STATUS)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

ALTER TABLE PATS.REPORT_OF_CONTACT ADD (
  CONSTRAINT CK_IS_INTERNAL_APPEAL
  CHECK (is_internal_appeal=0 OR is_internal_appeal=1)
  ENABLE VALIDATE,
  CONSTRAINT CK_ROC_DATE_CLOSED
  CHECK ((DATE_CLOSED IS NULL) OR (DATE_CLOSED >= DATE_OF_CONTACT))
  ENABLE VALIDATE,
  CONSTRAINT CK_ROC_DATE_OVERDUE
  CHECK ((DATE_OVERDUE IS NULL) OR (DATE_OVERDUE >= DATE_OF_CONTACT))
  ENABLE VALIDATE,
  CONSTRAINT CK_ROC_ROLLUP_STATUS
  CHECK ((rollup_to_natl_reports_status=0) OR (rollup_to_natl_reports_status=1))
  ENABLE VALIDATE,
  CONSTRAINT CK_STATUS
  CHECK ((status='O') OR (status='C'))
  ENABLE VALIDATE,
  CONSTRAINT PK_ROC_NUMBER
  PRIMARY KEY
  (ROC_NUMBER)
  USING INDEX PATS.PK_ROC_NUMBER
  ENABLE VALIDATE);

ALTER TABLE PATS.REPORT_OF_CONTACT ADD (
  CONSTRAINT FK_ROC_COMP 
  FOREIGN KEY (COMP_FK) 
  REFERENCES PATS.COMPS (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_CONGRESSIONAL_CONTACT 
  FOREIGN KEY (CONGRESSIONAL_CONTACT_FK) 
  REFERENCES PATS.CONGRESSIONAL_CONTACT (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_ENTERED_BY_USER 
  FOREIGN KEY (ENTERED_BY_USER_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_INFO_TAKEN_BY_USER 
  FOREIGN KEY (INFO_TAKEN_BY_USER_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_INST 
  FOREIGN KEY (INSTITUTION_FK) 
  REFERENCES SDSADM.STD_INSTITUTION (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_PATIENT 
  FOREIGN KEY (PATIENT_FK) 
  REFERENCES PATS.PATS_PATIENT (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROC_TREATMENT_STATUS 
  FOREIGN KEY (TREATMENT_STATUS_FK) 
  REFERENCES PATS.TREATMENT_STATUS (ID)
  ENABLE VALIDATE);

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO PATSROLLUP;

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO PATSRPTS;

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO PATS_R;

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.REPORT_OF_CONTACT TO PATS_RW;

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO TIER_PATS_ROLE;

GRANT SELECT ON PATS.REPORT_OF_CONTACT TO VSCCUSR_R;
ALTER TABLE PATS.ROC_CONTACTING_ENTITY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ROC_CONTACTING_ENTITY CASCADE CONSTRAINTS;

CREATE TABLE PATS.ROC_CONTACTING_ENTITY
(
  ID                    NUMBER(12)              NOT NULL,
  ROC_FK                VARCHAR2(16 BYTE)       NOT NULL,
  CONTACTING_ENTITY_FK  NUMBER(10)              NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX PATS.PK_ROCCE_ID ON PATS.ROC_CONTACTING_ENTITY
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.ROC_NUMBER_ROC_CONT_ENT ON PATS.ROC_CONTACTING_ENTITY
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_ROC_CE_ID
    BEFORE
INSERT ON PATS.ROC_CONTACTING_ENTITY FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.roc_contacting_entity_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
END GENERATE_ROC_CE_ID;
/


ALTER TABLE PATS.ROC_CONTACTING_ENTITY ADD (
  CONSTRAINT PK_ROCCE_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ROCCE_ID
  ENABLE VALIDATE);

ALTER TABLE PATS.ROC_CONTACTING_ENTITY ADD (
  CONSTRAINT FK_ROCCE_CE_ID 
  FOREIGN KEY (CONTACTING_ENTITY_FK) 
  REFERENCES PATS.CONTACTING_ENTITY (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCCE_ROC_NUMBER 
  FOREIGN KEY (ROC_FK) 
  REFERENCES PATS.REPORT_OF_CONTACT (ROC_NUMBER)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ROC_CONTACTING_ENTITY TO PATSRPTS;

GRANT SELECT ON PATS.ROC_CONTACTING_ENTITY TO PATS_R;

GRANT SELECT ON PATS.ROC_CONTACTING_ENTITY TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ROC_CONTACTING_ENTITY TO PATS_RW;

GRANT SELECT ON PATS.ROC_CONTACTING_ENTITY TO TIER_PATS_ROLE;
ALTER TABLE PATS.ROC_ISSUE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ROC_ISSUE CASCADE CONSTRAINTS;

CREATE TABLE PATS.ROC_ISSUE
(
  ID                    NUMBER(12)              NOT NULL,
  ROC_FK                VARCHAR2(16 BYTE)       NOT NULL,
  ISSUE_CODE_FK         VARCHAR2(5 BYTE)        NOT NULL,
  HOSPITAL_LOCATION_FK  NUMBER(10),
  FACILITY_SERVSECT_FK  NUMBER(10)              NOT NULL,
  EMPLOYEE_INVOLVED_FK  NUMBER(12)
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX PATS.PK_ROCISS_ID ON PATS.ROC_ISSUE
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.ROC_NUMBER_ISSUE ON PATS.ROC_ISSUE
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_ROC_ISSUE_ID
    BEFORE
INSERT ON PATS.ROC_ISSUE FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.roc_issue_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
END GENERATE_ROC_ISSUE_ID;
/


ALTER TABLE PATS.ROC_ISSUE ADD (
  CONSTRAINT PK_ROCISS_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ROCISS_ID
  ENABLE VALIDATE);

ALTER TABLE PATS.ROC_ISSUE ADD (
  CONSTRAINT FK_ROCISS_EMPINV 
  FOREIGN KEY (EMPLOYEE_INVOLVED_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCISS_FSOS 
  FOREIGN KEY (FACILITY_SERVSECT_FK) 
  REFERENCES PATS.FACILITY_SERVICE_OR_SECTION (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCISS_HL 
  FOREIGN KEY (HOSPITAL_LOCATION_FK) 
  REFERENCES PATS.HOSPITAL_LOCATION (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCISS_ISSUE_CODE 
  FOREIGN KEY (ISSUE_CODE_FK) 
  REFERENCES PATS.ISSUE_CODE (ISSUE_CODE)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCISS_ROC 
  FOREIGN KEY (ROC_FK) 
  REFERENCES PATS.REPORT_OF_CONTACT (ROC_NUMBER)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ROC_ISSUE TO PATSRPTS;

GRANT SELECT ON PATS.ROC_ISSUE TO PATS_R;

GRANT SELECT ON PATS.ROC_ISSUE TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ROC_ISSUE TO PATS_RW;

GRANT SELECT ON PATS.ROC_ISSUE TO TIER_PATS_ROLE;
ALTER TABLE PATS.ROC_METHOD_OF_CONTACT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ROC_METHOD_OF_CONTACT CASCADE CONSTRAINTS;

CREATE TABLE PATS.ROC_METHOD_OF_CONTACT
(
  ID                    NUMBER(12)              NOT NULL,
  METHOD_OF_CONTACT_FK  NUMBER(10)              NOT NULL,
  ROC_FK                VARCHAR2(16 BYTE)       NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX PATS.PK_ROCMOC_ID ON PATS.ROC_METHOD_OF_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.ROC_NUMBER_ROC_METHOD ON PATS.ROC_METHOD_OF_CONTACT
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_ROC_MOC_ID
    BEFORE
INSERT ON PATS.ROC_METHOD_OF_CONTACT FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.roc_method_of_contact_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
END GENERATE_ROC_MOC_ID;
/


ALTER TABLE PATS.ROC_METHOD_OF_CONTACT ADD (
  CONSTRAINT PK_ROCMOC_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ROCMOC_ID
  ENABLE VALIDATE);

ALTER TABLE PATS.ROC_METHOD_OF_CONTACT ADD (
  CONSTRAINT FK_ROCMOC_MOC 
  FOREIGN KEY (METHOD_OF_CONTACT_FK) 
  REFERENCES PATS.METHOD_OF_CONTACT (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_ROCMOC_ROC 
  FOREIGN KEY (ROC_FK) 
  REFERENCES PATS.REPORT_OF_CONTACT (ROC_NUMBER)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ROC_METHOD_OF_CONTACT TO PATSRPTS;

GRANT SELECT ON PATS.ROC_METHOD_OF_CONTACT TO PATS_R;

GRANT SELECT ON PATS.ROC_METHOD_OF_CONTACT TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ROC_METHOD_OF_CONTACT TO PATS_RW;

GRANT SELECT ON PATS.ROC_METHOD_OF_CONTACT TO TIER_PATS_ROLE;
ALTER TABLE PATS.ROC_PHONE_FAX
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.ROC_PHONE_FAX CASCADE CONSTRAINTS;

CREATE TABLE PATS.ROC_PHONE_FAX
(
  ID                   NUMBER(12)               NOT NULL,
  NAME_OR_DESCRIPTION  VARCHAR2(50 BYTE),
  PHONE_FAX_NUMBER     VARCHAR2(50 BYTE)        NOT NULL,
  ROC_FK               VARCHAR2(16 BYTE)        NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX PATS.PK_ROCPF_ID ON PATS.ROC_PHONE_FAX
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX PATS.ROC_NUMBER_PHONE_FAX ON PATS.ROC_PHONE_FAX
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_ROC_PHONE_FAX_ID
    BEFORE
INSERT ON PATS.ROC_PHONE_FAX FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.roc_phone_fax_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
END GENERATE_ROC_PHONE_FAX_ID;
/


ALTER TABLE PATS.ROC_PHONE_FAX ADD (
  CONSTRAINT PK_ROCPF_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_ROCPF_ID
  ENABLE VALIDATE);

ALTER TABLE PATS.ROC_PHONE_FAX ADD (
  CONSTRAINT FK_ROCPF_ROC 
  FOREIGN KEY (ROC_FK) 
  REFERENCES PATS.REPORT_OF_CONTACT (ROC_NUMBER)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PATS.ROC_PHONE_FAX TO PATSRPTS;

GRANT SELECT ON PATS.ROC_PHONE_FAX TO PATS_R;

GRANT SELECT ON PATS.ROC_PHONE_FAX TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.ROC_PHONE_FAX TO PATS_RW;

GRANT SELECT ON PATS.ROC_PHONE_FAX TO TIER_PATS_ROLE;
ALTER TABLE PATS.NOTIFICATION_MASTER
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.NOTIFICATION_MASTER CASCADE CONSTRAINTS;

CREATE TABLE PATS.NOTIFICATION_MASTER
(
  ID                        NUMBER(12),
  ROC_FK                    VARCHAR2(16 BYTE)   NOT NULL,
  PATIENT_ADVOCATE_USER_FK  NUMBER(10)          NOT NULL,
  DATE_INITIATED            DATE                NOT NULL,
  STATUS                    CHAR(1 BYTE),
  EXPIRATION_DATE           DATE,
  NOTIFICATION_TYPE         VARCHAR2(3 BYTE)    NOT NULL,
  QUERYSTRING               VARCHAR2(50 BYTE)   NOT NULL,
  EMPLOYEE_NOTIFIED_FK      NUMBER(10)          NOT NULL,
  LAST_RECIPIENT_DATE_READ  TIMESTAMP(6) WITH TIME ZONE
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_NOTIFICATION_MASTER ON PATS.NOTIFICATION_MASTER
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX PATS.U_NOTIFICATION_QUERYSTRING ON PATS.NOTIFICATION_MASTER
(QUERYSTRING)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_NOTIFICATION_MAS_ID BEFORE
INSERT ON PATS.NOTIFICATION_MASTER FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.notification_master_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_notification_mas_id;
/


ALTER TABLE PATS.NOTIFICATION_MASTER ADD (
  CONSTRAINT CK_NOTIFICATION_STATUS
  CHECK ((status IS NULL) OR (status='P') OR (status='C'))
  ENABLE VALIDATE,
  CONSTRAINT CK_NOTIFICATION_TYPE
  CHECK ("NOTIFICATION_TYPE"='IN' OR "NOTIFICATION_TYPE"='ARN')
  ENABLE VALIDATE,
  CONSTRAINT PK_NOTIFICATION_MASTER
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_NOTIFICATION_MASTER
  ENABLE VALIDATE,
  CONSTRAINT U_NOTIFICATION_QUERYSTRING
  UNIQUE (QUERYSTRING)
  USING INDEX PATS.U_NOTIFICATION_QUERYSTRING
  ENABLE VALIDATE);

ALTER TABLE PATS.NOTIFICATION_MASTER ADD (
  CONSTRAINT FK_NOTIFICATION_EMP_NOTIFIED 
  FOREIGN KEY (EMPLOYEE_NOTIFIED_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_NOTIFICATION_PA 
  FOREIGN KEY (PATIENT_ADVOCATE_USER_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_NOTIFICATION_ROC 
  FOREIGN KEY (ROC_FK) 
  REFERENCES PATS.REPORT_OF_CONTACT (ROC_NUMBER)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PATS.NOTIFICATION_MASTER TO PATSRPTS;

GRANT SELECT ON PATS.NOTIFICATION_MASTER TO PATS_R;

GRANT SELECT ON PATS.NOTIFICATION_MASTER TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.NOTIFICATION_MASTER TO PATS_RW;

GRANT SELECT ON PATS.NOTIFICATION_MASTER TO TIER_PATS_ROLE;
ALTER TABLE PATS.NOTIFICATION_DETAIL
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.NOTIFICATION_DETAIL CASCADE CONSTRAINTS;

CREATE TABLE PATS.NOTIFICATION_DETAIL
(
  ID                      NUMBER(12)            NOT NULL,
  NOTIFICATION_MASTER_FK  NUMBER(12)            NOT NULL,
  TO_USER_FK              NUMBER(10)            NOT NULL,
  DATE_SENT               TIMESTAMP(6) WITH TIME ZONE,
  DATE_READ               TIMESTAMP(6) WITH TIME ZONE,
  MESSAGE                 VARCHAR2(4000 BYTE),
  FROM_USER_FK            NUMBER(10)            NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_NOTIFICATION_DETAIL_ID ON PATS.NOTIFICATION_DETAIL
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.GENERATE_NOTIFICATION_DET_ID BEFORE
INSERT ON PATS.NOTIFICATION_DETAIL FOR EACH ROW
BEGIN
 IF :new.id IS NULL THEN
   SELECT pats.notification_detail_seq.NEXTVAL
     INTO :new.id
     FROM DUAL;
 END IF;
 END generate_notification_det_id;
/


ALTER TABLE PATS.NOTIFICATION_DETAIL ADD (
  CONSTRAINT PK_NOTIFICATION_DETAIL_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_NOTIFICATION_DETAIL_ID
  ENABLE VALIDATE);

ALTER TABLE PATS.NOTIFICATION_DETAIL ADD (
  CONSTRAINT FK_NOTIFICATION_DETAIL_MASTER 
  FOREIGN KEY (NOTIFICATION_MASTER_FK) 
  REFERENCES PATS.NOTIFICATION_MASTER (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE,
  CONSTRAINT FK_NOTIFICATION_DETAIL_TO_USER 
  FOREIGN KEY (TO_USER_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ENABLE VALIDATE,
  CONSTRAINT FK_NOTIFICATION_DETL_FROM_USER 
  FOREIGN KEY (FROM_USER_FK) 
  REFERENCES PATS.PATS_USER (ID)
  ENABLE VALIDATE);

GRANT SELECT ON PATS.NOTIFICATION_DETAIL TO PATS_R;

GRANT SELECT ON PATS.NOTIFICATION_DETAIL TO PATS_READONLY;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.NOTIFICATION_DETAIL TO PATS_RW;

GRANT SELECT ON PATS.NOTIFICATION_DETAIL TO TIER_PATS_ROLE;
ALTER TABLE PATS.PAD_LOCATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PATS.PAD_LOCATION CASCADE CONSTRAINTS;

CREATE TABLE PATS.PAD_LOCATION
(
  ID             NUMBER(10)                     NOT NULL,
  IRIS_LOCATION  VARCHAR2(50 BYTE),
  PATS_STATION   VARCHAR2(10 BYTE)              NOT NULL
)
TABLESPACE M_PATS_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PATS.PK_PADLOC_ID ON PATS.PAD_LOCATION
(ID)
LOGGING
TABLESPACE M_PATS_DATA_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PATS.PAD_LOCATION_TRG
BEFORE INSERT on PATS.PAD_LOCATION
FOR EACH ROW
BEGIN
SELECT PAD_LOCATION_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END pad_location_trg;
/


ALTER TABLE PATS.PAD_LOCATION ADD (
  CONSTRAINT PK_PADLOC_ID
  PRIMARY KEY
  (ID)
  USING INDEX PATS.PK_PADLOC_ID
  ENABLE VALIDATE);

GRANT SELECT ON PATS.PAD_LOCATION TO PATS_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PATS.PAD_LOCATION TO PATS_RW;
