ALTER TABLE PSIM.RPT_PSIM_TRAITS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.RPT_PSIM_TRAITS CASCADE CONSTRAINTS;

CREATE TABLE PSIM.RPT_PSIM_TRAITS
(
  VPID_ID                 NUMBER(20)            NOT NULL,
  VPID_VALUE              VARCHAR2(29 BYTE)     NOT NULL,
  FIRST_NAME              VARCHAR2(25 BYTE),
  MIDDLE_NAME             VARCHAR2(25 BYTE),
  LAST_NAME               VARCHAR2(35 BYTE),
  PREFIX                  VARCHAR2(10 BYTE),
  SUFFIX                  VARCHAR2(10 BYTE),
  SSN                     VARCHAR2(20 BYTE),
  SSN_TYPE_CODE           VARCHAR2(32 BYTE),
  PSEUDO_SSN_REASON_CODE  VARCHAR2(50 BYTE),
  SSN_VRFN_STATUS_CODE    VARCHAR2(70 BYTE),
  SSA_SENT_DATE           DATE,
  SSA_RECEIVED_DATE       DATE,
  SSA_VERIFIED_DATE       DATE,
  GENDER_CODE             VARCHAR2(20 BYTE),
  DATE_OF_BIRTH           VARCHAR2(8 BYTE),
  RECORD_CREATED_BY       VARCHAR2(30 BYTE)     NOT NULL,
  RECORD_CREATED_DATE     DATE                  NOT NULL,
  RECORD_MODIFIED_BY      VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE    DATE,
  SSAVERIFCODE_CODE       CHAR(1 BYTE),
  DATE_OF_DEATH           VARCHAR2(14 BYTE),
  SELF_ID_GENDER          VARCHAR2(4 BYTE),
  EMAIL                   VARCHAR2(128 BYTE)
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.RPT_PSIM_TRAITS IS 'Flat table containing primary view used for generating reports - exclusive use for ESR only. Populated using stored procedure on a nightly basis. 8/2010 PSIM updates this table real-time coordinated with identity changes.   ';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.VPID_VALUE IS 'Retrofitted.';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.PREFIX IS 'BR7327 Representative prefix is defined as the prefix of the association type identified.';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.SUFFIX IS 'BR7329 Representative suffix is defined as the suffix of the association type identified.';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.SSN IS 'The verified social security number that has been assigned by the Social Security Agency to the person.';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.SSA_VERIFIED_DATE IS 'BR1590 SSA Verification date is defined as the date the SSA SSN tape was read.';

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

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.RPT_PSIM_TRAITS.EMAIL IS 'Email address of the person';



CREATE UNIQUE INDEX PSIM.RPTPSIMT_PK ON PSIM.RPT_PSIM_TRAITS
(VPID_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_DOB_NU_I ON PSIM.RPT_PSIM_TRAITS
(DATE_OF_BIRTH)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_DOD_NU_I ON PSIM.RPT_PSIM_TRAITS
(DATE_OF_DEATH)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_EMAIL_NU_I ON PSIM.RPT_PSIM_TRAITS
(EMAIL)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX PSIM.RPT_PSIM_GEN_BM_I ON PSIM.RPT_PSIM_TRAITS
(GENDER_CODE)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE INDEX PSIM.RPT_PSIM_LNFNMN_PAR_NU_I ON PSIM.RPT_PSIM_TRAITS
(LAST_NAME, FIRST_NAME, MIDDLE_NAME)
  TABLESPACE M_DATA_01
  PCTFREE    10
  INITRANS   10
  MAXTRANS   255
  STORAGE    (
              INITIAL          128M
              NEXT             128M
              MINEXTENTS       1
              PCTINCREASE      0
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
GLOBAL PARTITION BY HASH (LAST_NAME, FIRST_NAME) (  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I01
    TABLESPACE L_INDEX_02_P01,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I02
    TABLESPACE L_INDEX_02_P02,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I03
    TABLESPACE L_INDEX_02_P03,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I04
    TABLESPACE L_INDEX_02_P04,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I05
    TABLESPACE L_INDEX_02_P01,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I06
    TABLESPACE L_INDEX_02_P02,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I07
    TABLESPACE L_INDEX_02_P03,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I08
    TABLESPACE L_INDEX_02_P04
)
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_SELFGENDER_NU_I ON PSIM.RPT_PSIM_TRAITS
(SELF_ID_GENDER)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_SSN_NU_I ON PSIM.RPT_PSIM_TRAITS
(SSN)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.RPT_PSIM_VPIDVAL_I ON PSIM.RPT_PSIM_TRAITS
(VPID_VALUE)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER PSIM.RPT_PSIM_TRAITS_AUDIR_TRGR
after Update or Delete or Insert
on PSIM.RPT_PSIM_TRAITS
REFERENCING NEW AS NEW OLD AS OLD
for each row
declare
    v_person_id  ADR.PERSON.PERSON_ID%TYPE;
    v_cnt        NUMBER := 0;
begin

	IF :NEW.VPID_ID IS NOT NULL THEN

    SELECT COUNT(*)
    INTO   v_cnt
    FROM   ADR.PERSON
    WHERE  VPID_ID = :NEW.VPID_ID;

    IF v_cnt = 0 THEN
      RETURN;
    END IF;

    SELECT PERSON_ID
    INTO   v_person_id
    FROM   ADR.PERSON
    WHERE  VPID_ID = :NEW.VPID_ID;

    SELECT COUNT(*)
    INTO   v_cnt
    FROM   ADR.ENROLLMENT_DETERMINATION
    WHERE  PERSON_ID = v_person_id;

    IF v_cnt = 0 THEN
      RETURN;
    END IF;

		INSERT INTO ADREXT.ADR_EXTRACT_CHGS
		(
			PERSON_ID,
			CHG_DATE,
			ADR_EXTRACT_CHGS_ID
		)
		VALUES
		(
      v_person_id,
			TRUNC(SYSDATE),
			ADREXT.ADR_EXTRACT_CHGS_S.NEXTVAL
		);
	END IF;

end RPT_PSIM_TRAITS_audir_trgr;
/


CREATE OR REPLACE PUBLIC SYNONYM RPT_PSIM_TRAITS FOR PSIM.RPT_PSIM_TRAITS;


ALTER TABLE PSIM.RPT_PSIM_TRAITS ADD (
  CONSTRAINT RPTPSIMT_PK
  PRIMARY KEY
  (VPID_ID)
  USING INDEX PSIM.RPTPSIMT_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.RPT_PSIM_TRAITS TO ADREXT;

GRANT SELECT ON PSIM.RPT_PSIM_TRAITS TO EE_RPT_R;

GRANT SELECT ON PSIM.RPT_PSIM_TRAITS TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.RPT_PSIM_TRAITS TO PSIM_RW;
ALTER TABLE PSIM.QUEUE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.QUEUE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.QUEUE
(
  ID         NUMBER(9)                          NOT NULL,
  NAME       VARCHAR2(64 BYTE)                  NOT NULL,
  VERSION    NUMBER(9)                          NOT NULL,
  CREATEDBY  VARCHAR2(30 BYTE)                  NOT NULL,
  CREATED    DATE                               NOT NULL,
  UPDATEDBY  VARCHAR2(30 BYTE),
  UPDATED    DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.QUEUE IS 'Used in conjunction with the Event table to specify if an HL7 message is inbound or outbound or being held for some reason.';

COMMENT ON COLUMN PSIM.QUEUE.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 PSIM.QUEUE.CREATEDBY IS 'Account/user ID that created the record';

COMMENT ON COLUMN PSIM.QUEUE.CREATED IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.QUEUE.UPDATEDBY IS 'Account/user ID for this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSQUE_PK ON PSIM.QUEUE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.QUE_NAME_UK ON PSIM.QUEUE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM QUEUE FOR PSIM.QUEUE;


ALTER TABLE PSIM.QUEUE ADD (
  CONSTRAINT PSQUE_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSQUE_PK
  ENABLE VALIDATE,
  CONSTRAINT QUE_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.QUE_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.QUEUE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.QUEUE TO PSIM_RW;
DROP TABLE PSIM.PS_VPID_SEQ CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_VPID_SEQ
(
  NEXT_INT  NUMBER(10)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_VPID_SEQ IS 'Tracking number for sequences';



CREATE OR REPLACE PUBLIC SYNONYM PS_VPID_SEQ FOR PSIM.PS_VPID_SEQ;


GRANT SELECT ON PSIM.PS_VPID_SEQ TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_VPID_SEQ TO PSIM_RW;
ALTER TABLE PSIM.PS_VIRTUAL_TABLE_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_VIRTUAL_TABLE_TYPE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_VIRTUAL_TABLE_TYPE
(
  ID                     NUMBER(20)             NOT NULL,
  JAVA_NAME              VARCHAR2(30 BYTE)      NOT NULL,
  VISTA_NAME             VARCHAR2(10 BYTE)      NOT NULL,
  VISTA_TYPE_CODE        VARCHAR2(10 BYTE)      NOT NULL,
  MAX_LENGTH_TOTAL       NUMBER(10)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_VIRTUAL_TABLE_TYPE IS 'Vista type code - lookup table';

COMMENT ON COLUMN PSIM.PS_VIRTUAL_TABLE_TYPE.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 PSIM.PS_VIRTUAL_TABLE_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_VIRTUAL_TABLE_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_VIRTUAL_TABLE_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSVIRTAB_JAVNAME_UK ON PSIM.PS_VIRTUAL_TABLE_TYPE
(JAVA_NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSVIRTAB_PK ON PSIM.PS_VIRTUAL_TABLE_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSVIRTAB_VISNAME_UK ON PSIM.PS_VIRTUAL_TABLE_TYPE
(VISTA_NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_VIRTUAL_TABLE_TYPE FOR PSIM.PS_VIRTUAL_TABLE_TYPE;


ALTER TABLE PSIM.PS_VIRTUAL_TABLE_TYPE ADD (
  CONSTRAINT PSVIRTAB_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSVIRTAB_PK
  ENABLE VALIDATE,
  CONSTRAINT PSVIRTAB_JAVNAME_UK
  UNIQUE (JAVA_NAME)
  USING INDEX PSIM.PSVIRTAB_JAVNAME_UK
  ENABLE VALIDATE,
  CONSTRAINT PSVIRTAB_VISNAME_UK
  UNIQUE (VISTA_NAME)
  USING INDEX PSIM.PSVIRTAB_VISNAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_VIRTUAL_TABLE_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_VIRTUAL_TABLE_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_VERIFICATION_LEVEL_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_VERIFICATION_LEVEL_TYPE CASCADE CONSTRAINTS;

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

COMMENT ON TABLE PSIM.PS_VERIFICATION_LEVEL_TYPE IS 'Lookup table: FK: ps_verification';

COMMENT ON COLUMN PSIM.PS_VERIFICATION_LEVEL_TYPE.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 PSIM.PS_VERIFICATION_LEVEL_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_VERIFICATION_LEVEL_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_VERIFICATION_LEVEL_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSVERLEV_LEVODR_UK ON PSIM.PS_VERIFICATION_LEVEL_TYPE
(LEVEL_ORDER_NUMBER)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSVERLEV_NAME_UK ON PSIM.PS_VERIFICATION_LEVEL_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSVERLEV_PK ON PSIM.PS_VERIFICATION_LEVEL_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_VERIFICATION_LEVEL_TYPE FOR PSIM.PS_VERIFICATION_LEVEL_TYPE;


ALTER TABLE PSIM.PS_VERIFICATION_LEVEL_TYPE ADD (
  CONSTRAINT PSVERLEV_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSVERLEV_PK
  ENABLE VALIDATE,
  CONSTRAINT PSVERLEV_LEVODR_UK
  UNIQUE (LEVEL_ORDER_NUMBER)
  USING INDEX PSIM.PSVERLEV_LEVODR_UK
  ENABLE VALIDATE,
  CONSTRAINT PSVERLEV_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSVERLEV_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_VERIFICATION_LEVEL_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_VERIFICATION_LEVEL_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_VERIFICATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_VERIFICATION CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_VERIFICATION
(
  PS_VERIFICATION_ID     NUMBER(20)             NOT NULL,
  STATUS_IND             CHAR(1 BYTE)           NOT NULL,
  OUTCOME_TEXT           VARCHAR2(20 BYTE),
  RESULT_DATE            TIMESTAMP(6),
  VERIFICATION_LEVEL_ID  NUMBER(20)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_VERIFICATION IS 'Auditing- What kind of record entry is known through this(from MPI add, MPI update etc)';

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

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

COMMENT ON COLUMN PSIM.PS_VERIFICATION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_VERIFICATION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSVER_PK ON PSIM.PS_VERIFICATION
(PS_VERIFICATION_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSVER_PSVERLEV_FK_I ON PSIM.PS_VERIFICATION
(VERIFICATION_LEVEL_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_VERIFICATION FOR PSIM.PS_VERIFICATION;


ALTER TABLE PSIM.PS_VERIFICATION ADD (
  CONSTRAINT PSVER_PK
  PRIMARY KEY
  (PS_VERIFICATION_ID)
  USING INDEX PSIM.PSVER_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_VERIFICATION ADD (
  CONSTRAINT PSVER_PSVERLEV_FK 
  FOREIGN KEY (VERIFICATION_LEVEL_ID) 
  REFERENCES PSIM.PS_VERIFICATION_LEVEL_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_VERIFICATION TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_VERIFICATION TO PSIM_RW;
ALTER TABLE PSIM.PS_SYSTEM_OF_INTEREST_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_SYSTEM_OF_INTEREST_TYPE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_SYSTEM_OF_INTEREST_TYPE
(
  ID                     NUMBER(20)             NOT NULL,
  NAME                   VARCHAR2(200 BYTE)     NOT NULL,
  DESCRIPTION            VARCHAR2(180 BYTE),
  INSTITUTION_ID         NUMBER(20),
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE,
  AFOID                  VARCHAR2(70 BYTE),
  SITE_ASSOCIATION       VARCHAR2(50 BYTE),
  ASSOCIATION_TYPE       VARCHAR2(50 BYTE)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_SYSTEM_OF_INTEREST_TYPE IS 'Facility of interest to the Veterans Administration
  2  Lookup table. Also links to std_institution for stationnumber, name etc.';

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.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 PSIM.PS_SYSTEM_OF_INTEREST_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.AFOID IS 'Assigning Facility within an International Standards Organization Object Identifier (OID)';

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.SITE_ASSOCIATION IS 'Associated Site for System Of Interest (SOI)';

COMMENT ON COLUMN PSIM.PS_SYSTEM_OF_INTEREST_TYPE.ASSOCIATION_TYPE IS 'Category for Association';



CREATE UNIQUE INDEX PSIM.PSSYSOFINT_NAME_UK ON PSIM.PS_SYSTEM_OF_INTEREST_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSSYSOFINT_PK ON PSIM.PS_SYSTEM_OF_INTEREST_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSYSOFINT_STDINST_FK_I ON PSIM.PS_SYSTEM_OF_INTEREST_TYPE
(INSTITUTION_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_SYSTEM_OF_INTEREST_TYPE FOR PSIM.PS_SYSTEM_OF_INTEREST_TYPE;


ALTER TABLE PSIM.PS_SYSTEM_OF_INTEREST_TYPE ADD (
  CONSTRAINT PSSYSOFINT_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSSYSOFINT_PK
  ENABLE VALIDATE,
  CONSTRAINT PSSYSOFINT_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSSYSOFINT_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_SYSTEM_OF_INTEREST_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_SYSTEM_OF_INTEREST_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_SSN_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_SSN_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_SSN_HISTORY
(
  PS_SSN_HISTORY_ID             NUMBER(20)      NOT NULL,
  OWNER_ID                      NUMBER(20)      NOT NULL,
  STD_SSNTYPE_ID                NUMBER(20)      NOT NULL,
  STD_PSEUDOSSNREASON_ID        NUMBER(20),
  STD_SSNCHANGESOURCE_ID        NUMBER(20),
  STD_SSNVERIFICATIONSTATUS_ID  NUMBER(20),
  STD_SSAVERIFCODE_ID           NUMBER(20),
  SSN_VALUE                     VARCHAR2(20 BYTE),
  SSA_SENT_DATE                 DATE,
  SSA_RECEIVED_DATE             DATE,
  SSA_VERIFIED_DATE             DATE,
  IS_ACTIVE_FLAG                CHAR(1 BYTE)    NOT NULL,
  EFFECTIVE_DATE                DATE            NOT NULL,
  EXPIRATION_DATE               DATE            NOT NULL,
  RECORD_MODIFIED_COUNT         NUMBER          NOT NULL,
  RECORD_CREATED_BY             VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE           DATE            NOT NULL,
  RECORD_MODIFIED_BY            VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE          DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_SSN_HISTORY IS 'Contains a persons SSN value, SSA received and verified date, effective and expiration date. Is_active_flag indicates the active record';

COMMENT ON COLUMN PSIM.PS_SSN_HISTORY.SSA_VERIFIED_DATE IS 'BR1590 SSA Verification date is defined as the date the SSA SSN tape was read.';

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

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

COMMENT ON COLUMN PSIM.PS_SSN_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_SSN_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSSSNHIST_PK ON PSIM.PS_SSN_HISTORY
(PS_SSN_HISTORY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_PSSSN_FK_I ON PSIM.PS_SSN_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_SSN_NU_I ON PSIM.PS_SSN_HISTORY
(SSN_VALUE, OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_STDPSRSN_FK_I ON PSIM.PS_SSN_HISTORY
(STD_PSEUDOSSNREASON_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_STDSCHGSRC_FK_I ON PSIM.PS_SSN_HISTORY
(STD_SSNCHANGESOURCE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_STDSSAVERIF_FK_I ON PSIM.PS_SSN_HISTORY
(STD_SSAVERIFCODE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_STDSSNTYP_FK_I ON PSIM.PS_SSN_HISTORY
(STD_SSNTYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSNHIST_STDSSNVS_FK_I ON PSIM.PS_SSN_HISTORY
(STD_SSNVERIFICATIONSTATUS_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_SSN_HISTORY FOR PSIM.PS_SSN_HISTORY;


ALTER TABLE PSIM.PS_SSN_HISTORY ADD (
  CONSTRAINT PSSSNHIST_PK
  PRIMARY KEY
  (PS_SSN_HISTORY_ID)
  USING INDEX PSIM.PSSSNHIST_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_SSN_HISTORY ADD (
  CONSTRAINT PSSSNHIST_PSSSN_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_SSN (PS_SSN_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_SSN_HISTORY TO ADR;

GRANT SELECT ON PSIM.PS_SSN_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_SSN_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_SSN
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_SSN CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_SSN
(
  PS_SSN_ID              NUMBER(20)             NOT NULL,
  PERSON_TRAIT_ID        NUMBER(20)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_SSN IS 'Link between ps_person_trait and ps_ssn_history table. FK: PS_PERSON_TRAIT';

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

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

COMMENT ON COLUMN PSIM.PS_SSN.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_SSN.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSSSN_PK ON PSIM.PS_SSN
(PS_SSN_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSSSN_PSPERTRA_FK_I ON PSIM.PS_SSN
(PERSON_TRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_SSN FOR PSIM.PS_SSN;


ALTER TABLE PSIM.PS_SSN ADD (
  CONSTRAINT PSSSN_PK
  PRIMARY KEY
  (PS_SSN_ID)
  USING INDEX PSIM.PSSSN_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_SSN ADD (
  CONSTRAINT PSSSN_PSPERTRA_FK 
  FOREIGN KEY (PERSON_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_SSN TO ADR;

GRANT SELECT ON PSIM.PS_SSN TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_SSN TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_VPID
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_VPID CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_VPID
(
  PS_PERSONVPID_ID            NUMBER(20)        NOT NULL,
  VPID_VALUE                  VARCHAR2(29 BYTE) NOT NULL,
  PRIMARY_TRAIT_ID            NUMBER(20),
  SURVIVING_VPID_ID           NUMBER(20),
  ORIGINAL_FACILITY_ID        NUMBER(20),
  ID_STATE_IND                CHAR(1 BYTE),
  RECORD_MODIFIED_COUNT       NUMBER            NOT NULL,
  RECORD_CREATED_BY           VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE         DATE              NOT NULL,
  RECORD_MODIFIED_BY          VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE        DATE,
  IDENTITY_UPDATE_EVENT_DATE  DATE              DEFAULT trunc(sysdate)        NOT NULL,
  IDENTITY_TRANSACTION_TYPE   VARCHAR2(1 BYTE)  DEFAULT 'A'                   NOT NULL,
  LEGACY_EDIT_DATE            DATE,
  LEGACY_EDIT_USER            VARCHAR2(240 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_VPID IS 'Main table within the PSIM schema. Contains VPID_VALUE and VPID_ID that is used to link to the person table in ADR schema. LEGACY_EDIT_DATE, LEGACY_EDIT_USER - These two columns are used to store dates from legacy database. The below two columns are used for RPT_PSIM_TRAITS stored procedure purposes. -IDENTITY_UPDATE_EVENT_DATE IDENTITY_TRANSACTION_TYPE';

COMMENT ON COLUMN PSIM.PS_PERSON_VPID.PS_PERSONVPID_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 PSIM.PS_PERSON_VPID.VPID_VALUE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_VPID.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_VPID.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERVPI_PK ON PSIM.PS_PERSON_VPID
(PS_PERSONVPID_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERVPI_PSPERTRA_FK_I ON PSIM.PS_PERSON_VPID
(PRIMARY_TRAIT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERVPI_PSPERVPI_FK_I ON PSIM.PS_PERSON_VPID
(SURVIVING_VPID_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERVPI_PSSYSOFINT_FK_I ON PSIM.PS_PERSON_VPID
(ORIGINAL_FACILITY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERVPI_UPDDATE_I ON PSIM.PS_PERSON_VPID
(IDENTITY_UPDATE_EVENT_DATE)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSPERVPI_U_VPIDVALUE_UK ON PSIM.PS_PERSON_VPID
(VPID_VALUE)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_VPID FOR PSIM.PS_PERSON_VPID;


ALTER TABLE PSIM.PS_PERSON_VPID ADD (
  CONSTRAINT PSPERVPI_PK
  PRIMARY KEY
  (PS_PERSONVPID_ID)
  USING INDEX PSIM.PSPERVPI_PK
  ENABLE VALIDATE,
  CONSTRAINT PSPERVPI_U_VPIDVALUE_UK
  UNIQUE (VPID_VALUE)
  USING INDEX PSIM.PSPERVPI_U_VPIDVALUE_UK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_VPID ADD (
  CONSTRAINT PSPERVPI_PSPERTRA_FK 
  FOREIGN KEY (PRIMARY_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERVPI_PSPERVPI_FK 
  FOREIGN KEY (SURVIVING_VPID_ID) 
  REFERENCES PSIM.PS_PERSON_VPID (PS_PERSONVPID_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERVPI_PSSYSOFINT_FK 
  FOREIGN KEY (ORIGINAL_FACILITY_ID) 
  REFERENCES PSIM.PS_SYSTEM_OF_INTEREST_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT REFERENCES, SELECT ON PSIM.PS_PERSON_VPID TO ADR;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_VPID TO EEUSR;

GRANT SELECT ON PSIM.PS_PERSON_VPID TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_VPID TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_TRAIT_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_TRAIT_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_TRAIT_HISTORY
(
  PS_PERSONTRAIT_HISTORY_ID  NUMBER(20)         NOT NULL,
  OWNER_ID                   NUMBER(20)         NOT NULL,
  GENDER_ID                  NUMBER(20),
  POB_STATE_ID               NUMBER(20),
  POB_COUNTRY_ID             NUMBER(20),
  VERIFICATION_ID            NUMBER(20),
  EVENT_PERSON_ID            VARCHAR2(30 BYTE),
  CHANGETYPE_ID              NUMBER(20)         NOT NULL,
  EDIT_ID                    NUMBER(20),
  NAME_TEXT                  VARCHAR2(30 BYTE)  NOT NULL,
  MULTIBIRTH_IND             CHAR(1 BYTE),
  POB_CITY_CODE              VARCHAR2(20 BYTE),
  MOTHERS_MAIDEN_NAME        VARCHAR2(35 BYTE),
  EVENT_DATE                 TIMESTAMP(6),
  BIRTH_DATE_TEXT            VARCHAR2(8 BYTE),
  IS_ACTIVE_FLAG             CHAR(1 BYTE)       NOT NULL,
  UPDATE_STATUS_IND          CHAR(1 BYTE),
  EFFECTIVE_DATE             DATE               NOT NULL,
  EXPIRATION_DATE            DATE               NOT NULL,
  RECORD_MODIFIED_COUNT      NUMBER             NOT NULL,
  RECORD_CREATED_BY          VARCHAR2(30 BYTE)  NOT NULL,
  RECORD_CREATED_DATE        DATE               NOT NULL,
  RECORD_MODIFIED_BY         VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE       DATE,
  DEATH_DATE_TEXT            VARCHAR2(150 BYTE),
  MARKER_TEXT                VARCHAR2(100 BYTE),
  TRAIT_TEXT                 VARCHAR2(100 BYTE),
  EMAIL                      VARCHAR2(128 BYTE)
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_TRAIT_HISTORY IS 'A persons trait details (DOB,birth city,state,country, etc) Is_active_flag indicates the active record.';

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.BIRTH_DATE_TEXT IS 'BR7731 The date of birth is defined as the birth date of the veteran or spouse associated with the communication to be mailed.';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.DEATH_DATE_TEXT IS 'Date the Person died.';

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.MARKER_TEXT IS 'Indicates processing from enumeration and or batch services at the Identity Trait level.';

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT_HISTORY.TRAIT_TEXT IS 'Description of the main trait elements.';



CREATE INDEX PSIM.PSPERTRAHI_CITY_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(POB_CITY_CODE)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_DOB_GNDR_NU_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(BIRTH_DATE_TEXT, GENDER_ID, OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_MMN_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(MOTHERS_MAIDEN_NAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSPERTRAHI_PK ON PSIM.PS_PERSON_TRAIT_HISTORY
(PS_PERSONTRAIT_HISTORY_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_PSCHGTYP_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(CHANGETYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_PSEDI_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(EDIT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_PSPERTRA_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_PSVER_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(VERIFICATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_STDCNTRY_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(POB_COUNTRY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_STDGENDER_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(GENDER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERTRAHI_STDSTATE_FK_I ON PSIM.PS_PERSON_TRAIT_HISTORY
(POB_STATE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_TRAIT_HISTORY FOR PSIM.PS_PERSON_TRAIT_HISTORY;


ALTER TABLE PSIM.PS_PERSON_TRAIT_HISTORY ADD (
  CONSTRAINT PSPERTRAHI_PK
  PRIMARY KEY
  (PS_PERSONTRAIT_HISTORY_ID)
  USING INDEX PSIM.PSPERTRAHI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_TRAIT_HISTORY ADD (
  CONSTRAINT PSPERTRAHI_PSCHGTYP_FK 
  FOREIGN KEY (CHANGETYPE_ID) 
  REFERENCES PSIM.PS_CHANGE_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERTRAHI_PSEDI_FK 
  FOREIGN KEY (EDIT_ID) 
  REFERENCES PSIM.PS_EDIT (PS_EDIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERTRAHI_PSPERTRA_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERTRAHI_PSVER_FK 
  FOREIGN KEY (VERIFICATION_ID) 
  REFERENCES PSIM.PS_VERIFICATION (PS_VERIFICATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_TRAIT_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_TRAIT_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_TRAIT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_TRAIT CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_TRAIT
(
  PS_PERSON_TRAIT_ID     NUMBER(20)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_TRAIT IS 'Source table for ps_person_trait_id value. This id is then used to connect to ps_person_trait_history, ps_person_name_history,ps_ssn_history';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_TRAIT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERTRA_PK ON PSIM.PS_PERSON_TRAIT
(PS_PERSON_TRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_TRAIT FOR PSIM.PS_PERSON_TRAIT;


ALTER TABLE PSIM.PS_PERSON_TRAIT ADD (
  CONSTRAINT PSPERTRA_PK
  PRIMARY KEY
  (PS_PERSON_TRAIT_ID)
  USING INDEX PSIM.PSPERTRA_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_TRAIT TO ADR;

GRANT SELECT ON PSIM.PS_PERSON_TRAIT TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_TRAIT TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_PHONE_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_PHONE_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_PHONE_HISTORY
(
  PS_PERSON_PHONE_HISTORY_ID  NUMBER(20)        NOT NULL,
  OWNER_ID                    NUMBER(20)        NOT NULL,
  PHONE_CONTACT_TYPE_ID       NUMBER(20)        NOT NULL,
  PHONE_NUMBER                VARCHAR2(30 BYTE),
  COUNTRY_CODE                VARCHAR2(10 BYTE),
  PHONE_EXTENSION             VARCHAR2(20 BYTE),
  AREA_CODE                   VARCHAR2(10 BYTE),
  VERIFICATION_ID             NUMBER(20),
  EVENT_DATE                  TIMESTAMP(6),
  EVENT_PERSON_ID             VARCHAR2(30 BYTE),
  CHANGE_TYPE_ID              NUMBER(20)        NOT NULL,
  IS_ACTIVE_FLAG              VARCHAR2(1 BYTE)  NOT NULL,
  EFFECTIVE_DATE              DATE              NOT NULL,
  EXPIRATION_DATE             DATE              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(18)        NOT NULL
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_PHONE_HISTORY IS 'Main table to store Correlated phone records in PSIM';

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE_HISTORY.PHONE_NUMBER IS 'Retrofitted.';

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE_HISTORY.COUNTRY_CODE IS 'Retrofitted.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.PSPERPHNHI_PK ON PSIM.PS_PERSON_PHONE_HISTORY
(PS_PERSON_PHONE_HISTORY_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERPHNHI_PSCHGTYP_FK_I ON PSIM.PS_PERSON_PHONE_HISTORY
(CHANGE_TYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERPHNHI_PSPERPHN_FK_I ON PSIM.PS_PERSON_PHONE_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERPHNHI_PSVER_FK_I ON PSIM.PS_PERSON_PHONE_HISTORY
(VERIFICATION_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERPHNHI_STDPHCOTYP_FK_I ON PSIM.PS_PERSON_PHONE_HISTORY
(PHONE_CONTACT_TYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_PHONE_HISTORY FOR PSIM.PS_PERSON_PHONE_HISTORY;


ALTER TABLE PSIM.PS_PERSON_PHONE_HISTORY ADD (
  CONSTRAINT PSPERPHNHI_PK
  PRIMARY KEY
  (PS_PERSON_PHONE_HISTORY_ID)
  USING INDEX PSIM.PSPERPHNHI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_PHONE_HISTORY ADD (
  CONSTRAINT PSPERPHNHI_PSCHGTYP_FK 
  FOREIGN KEY (CHANGE_TYPE_ID) 
  REFERENCES PSIM.PS_CHANGE_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERPHNHI_PSPERPHN_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_PERSON_PHONE (PS_PERSON_PHONE_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERPHNHI_PSVER_FK 
  FOREIGN KEY (VERIFICATION_ID) 
  REFERENCES PSIM.PS_VERIFICATION (PS_VERIFICATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_PHONE_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_PHONE_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_PHONE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_PHONE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_PHONE
(
  PS_PERSON_PHONE_ID     NUMBER(20)             NOT NULL,
  PS_PERSON_TRAIT_ID     NUMBER(20)             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(18)             NOT NULL
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_PHONE IS 'Link between ps_person_trait and ps_person_phone_history FK: PERSON_TRAIT_ID';

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

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_PHONE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.PSPERPHN_PK ON PSIM.PS_PERSON_PHONE
(PS_PERSON_PHONE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERPHN_PSPERTRA_FK_I ON PSIM.PS_PERSON_PHONE
(PS_PERSON_TRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_PHONE FOR PSIM.PS_PERSON_PHONE;


ALTER TABLE PSIM.PS_PERSON_PHONE ADD (
  CONSTRAINT PSPERPHN_PK
  PRIMARY KEY
  (PS_PERSON_PHONE_ID)
  USING INDEX PSIM.PSPERPHN_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_PHONE ADD (
  CONSTRAINT PSPERPHN_PSPERTRA_FK 
  FOREIGN KEY (PS_PERSON_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_PHONE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_PHONE TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_NPI_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_NPI_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_NPI_HISTORY
(
  PS_PERSON_NPI_HISTORY_ID  NUMBER(20)          NOT NULL,
  NPI_VALUE                 VARCHAR2(10 BYTE),
  OWNER_ID                  NUMBER(20)          NOT NULL,
  IS_ACTIVE                 VARCHAR2(1 BYTE)    NOT NULL,
  EFFECTIVE_DATE            DATE                NOT NULL,
  RECORD_MODIFIED_COUNT     NUMBER              NOT NULL,
  RECORD_CREATED_BY         VARCHAR2(30 BYTE)   NOT NULL,
  RECORD_CREATED_DATE       DATE                NOT NULL,
  RECORD_MODIFIED_BY        VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE      DATE,
  EXPIRATION_DATE           DATE                NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_NPI_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_NPI_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERNPIHI_PK ON PSIM.PS_PERSON_NPI_HISTORY
(PS_PERSON_NPI_HISTORY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNPIHI_PSPERNPI_FK_I ON PSIM.PS_PERSON_NPI_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_NPI_HISTORY FOR PSIM.PS_PERSON_NPI_HISTORY;


ALTER TABLE PSIM.PS_PERSON_NPI_HISTORY ADD (
  CONSTRAINT PSPERNPIHI_PK
  PRIMARY KEY
  (PS_PERSON_NPI_HISTORY_ID)
  USING INDEX PSIM.PSPERNPIHI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_NPI_HISTORY ADD (
  CONSTRAINT PSPERNPIHI_PSPERNPI_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_PERSON_NPI (PS_PERSON_NPI_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_NPI_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_NPI_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_NPI
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_NPI CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_NPI
(
  PS_PERSON_NPI_ID       NUMBER(20)             NOT NULL,
  PS_PERSON_TRAIT_ID     NUMBER(20)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_NPI.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_NPI.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERNPI_PK ON PSIM.PS_PERSON_NPI
(PS_PERSON_NPI_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNPI_PSPERTRA_FK_I ON PSIM.PS_PERSON_NPI
(PS_PERSON_TRAIT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_NPI FOR PSIM.PS_PERSON_NPI;


ALTER TABLE PSIM.PS_PERSON_NPI ADD (
  CONSTRAINT PSPERNPI_PK
  PRIMARY KEY
  (PS_PERSON_NPI_ID)
  USING INDEX PSIM.PSPERNPI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_NPI ADD (
  CONSTRAINT PSPERNPI_PSPERTRA_FK 
  FOREIGN KEY (PS_PERSON_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_NPI TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_NPI TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_NAME_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_NAME_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_NAME_HISTORY
(
  PS_PERSON_NAME_HISTORY_ID  NUMBER(20)         NOT NULL,
  OWNER_ID                   NUMBER(20)         NOT NULL,
  NAME_TYPE_ID               NUMBER(20)         NOT NULL,
  LAST_NAME                  VARCHAR2(35 BYTE),
  FIRST_NAME                 VARCHAR2(25 BYTE),
  MIDDLE_NAME                VARCHAR2(25 BYTE),
  PREFIX                     VARCHAR2(10 BYTE),
  SUFFIX                     VARCHAR2(10 BYTE),
  VERIFICATION_ID            NUMBER(20),
  EVENT_DATE                 TIMESTAMP(6),
  EVENT_PERSON_ID            VARCHAR2(30 BYTE),
  CHANGE_TYPE_ID             NUMBER(20)         NOT NULL,
  IS_ACTIVE_FLAG             CHAR(1 BYTE)       NOT NULL,
  EFFECTIVE_DATE             DATE               NOT NULL,
  EXPIRATION_DATE            DATE               NOT NULL,
  RECORD_MODIFIED_COUNT      NUMBER             NOT NULL,
  RECORD_CREATED_BY          VARCHAR2(30 BYTE)  NOT NULL,
  RECORD_CREATED_DATE        DATE               NOT NULL,
  RECORD_MODIFIED_BY         VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE       DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_NAME_HISTORY IS 'Person details (Name,Prefix,Suffix,event_date etc). Is_active_flag column indicates an active record.';

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.LAST_NAME IS 'BR7321 Representative Last Name is defined as the family name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.FIRST_NAME IS 'BR7323 Representative First Name is defined as the given name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.MIDDLE_NAME IS 'BR7325 Representative middle Name is defined as the middle name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.PREFIX IS 'BR7327 Representative prefix is defined as the prefix of the association type identified.';

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.SUFFIX IS 'BR7329 Representative suffix is defined as the suffix of the association type identified.';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_NAME_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.PSPERNAMHI_LNFNMN_PAR_NU_I ON PSIM.PS_PERSON_NAME_HISTORY
(LAST_NAME, FIRST_NAME, MIDDLE_NAME, OWNER_ID)
  TABLESPACE M_DATA_01
  PCTFREE    10
  INITRANS   10
  MAXTRANS   255
  STORAGE    (
              INITIAL          128M
              NEXT             128M
              MINEXTENTS       1
              PCTINCREASE      0
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
GLOBAL PARTITION BY HASH (LAST_NAME, FIRST_NAME) (  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I01
    TABLESPACE L_INDEX_02_P01,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I02
    TABLESPACE L_INDEX_02_P02,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I03
    TABLESPACE L_INDEX_02_P03,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I04
    TABLESPACE L_INDEX_02_P04,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I05
    TABLESPACE L_INDEX_02_P01,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I06
    TABLESPACE L_INDEX_02_P02,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I07
    TABLESPACE L_INDEX_02_P03,  
  PARTITION PSPERNAMHI_LNFNMN_PAR_NU_I08
    TABLESPACE L_INDEX_02_P04
)
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSPERNAMHI_PK ON PSIM.PS_PERSON_NAME_HISTORY
(PS_PERSON_NAME_HISTORY_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNAMHI_PSCHGTYP_FK_I ON PSIM.PS_PERSON_NAME_HISTORY
(CHANGE_TYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNAMHI_PSPERNAM_FK_I ON PSIM.PS_PERSON_NAME_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNAMHI_PSVER_FK_I ON PSIM.PS_PERSON_NAME_HISTORY
(VERIFICATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNAMHI_STDNAMETYPE_FK_I ON PSIM.PS_PERSON_NAME_HISTORY
(NAME_TYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_NAME_HISTORY FOR PSIM.PS_PERSON_NAME_HISTORY;


ALTER TABLE PSIM.PS_PERSON_NAME_HISTORY ADD (
  CONSTRAINT PSPERNAMHI_PK
  PRIMARY KEY
  (PS_PERSON_NAME_HISTORY_ID)
  USING INDEX PSIM.PSPERNAMHI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_NAME_HISTORY ADD (
  CONSTRAINT PSPERNAMHI_PSCHGTYP_FK 
  FOREIGN KEY (CHANGE_TYPE_ID) 
  REFERENCES PSIM.PS_CHANGE_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERNAMHI_PSPERNAM_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_PERSON_NAME (PS_PERSON_NAME_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERNAMHI_PSVER_FK 
  FOREIGN KEY (VERIFICATION_ID) 
  REFERENCES PSIM.PS_VERIFICATION (PS_VERIFICATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_NAME_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_NAME_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_NAME
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_NAME CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_NAME
(
  PS_PERSON_NAME_ID      NUMBER(20)             NOT NULL,
  PERSON_TRAIT_ID        NUMBER(20)             NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_NAME IS 'Link between ps_person_trait and ps_person_name_history FK: PERSON_TRAIT_ID';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_NAME.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_NAME.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERNAM_PK ON PSIM.PS_PERSON_NAME
(PS_PERSON_NAME_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERNAM_PSPERTRA_FK_I ON PSIM.PS_PERSON_NAME
(PERSON_TRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_NAME FOR PSIM.PS_PERSON_NAME;


ALTER TABLE PSIM.PS_PERSON_NAME ADD (
  CONSTRAINT PSPERNAM_PK
  PRIMARY KEY
  (PS_PERSON_NAME_ID)
  USING INDEX PSIM.PSPERNAM_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_NAME ADD (
  CONSTRAINT PSPERNAM_PSPERTRA_FK 
  FOREIGN KEY (PERSON_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_NAME TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_NAME TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_CORRELATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_CORRELATION CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_CORRELATION
(
  PS_PERSON_CORRELATION_ID     NUMBER(20)       NOT NULL,
  PERSON_VPID_ID               NUMBER(20)       NOT NULL,
  PERSONTRAIT_ID               NUMBER(20),
  SURVIVING_CORRELATION_ID     NUMBER(20),
  SYSTEM_OF_INTEREST_TYPE_ID   NUMBER(20)       NOT NULL,
  IDENTIFIER_TYPE_ID           NUMBER(20)       NOT NULL,
  ASSIGNING_AUTHORITY_TYPE_ID  NUMBER(20),
  EXTERNAL_FLAG                CHAR(1 BYTE)     NOT NULL,
  STATUS_IND                   CHAR(1 BYTE)     NOT NULL,
  ALTKEY1_TEXT                 VARCHAR2(150 BYTE),
  ALTKEY2_TEXT                 VARCHAR2(20 BYTE),
  ALTKEY3_TEXT                 VARCHAR2(20 BYTE),
  ALTKEY4_TEXT                 VARCHAR2(20 BYTE),
  ALTKEY5_TEXT                 VARCHAR2(20 BYTE),
  RECORD_MODIFIED_COUNT        NUMBER           NOT NULL,
  RECORD_CREATED_BY            VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE          DATE             NOT NULL,
  RECORD_MODIFIED_BY           VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE         DATE,
  SOURCE_APPLICATION           VARCHAR2(50 BYTE),
  ID_THEFT_RECORD_FLAG         CHAR(1 BYTE),
  ID_TYPE_CODE                 VARCHAR2(5 BYTE),
  AAOID                        VARCHAR2(70 BYTE),
  AFOID                        VARCHAR2(70 BYTE),
  MARKER_TEXT                  VARCHAR2(50 BYTE)
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_CORRELATION IS 'Correlation records for a person.';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.ALTKEY1_TEXT IS 'Fixed use as a correlation identifier.
  2  On 7/14/2010 the field was expanded from varchar2 29 to 150.';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.SOURCE_APPLICATION IS 'The application that invoked the correlation add/update';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.ID_THEFT_RECORD_FLAG IS '1= Indicates if correlation is related in an ID Theft,.0 = not ';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.ID_TYPE_CODE IS 'The id type code representation for the identifier/altkey1_text';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.AAOID IS 'Assigning Authority within an International Standards Organization Object Identifier (OID)';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.AFOID IS 'International Standards Organization Object Identifier (OID)';

COMMENT ON COLUMN PSIM.PS_PERSON_CORRELATION.MARKER_TEXT IS 'Indicates processing from enumeration and or batch services at the Correlation level.';



CREATE INDEX PSIM.PSPERCOR_AAOID_I ON PSIM.PS_PERSON_CORRELATION
(AAOID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_AFOID_I ON PSIM.PS_PERSON_CORRELATION
(AFOID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSPERCOR_PK ON PSIM.PS_PERSON_CORRELATION
(PS_PERSON_CORRELATION_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_PSPERCOR_FK_I ON PSIM.PS_PERSON_CORRELATION
(SURVIVING_CORRELATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_PSPERTRA_FK_I ON PSIM.PS_PERSON_CORRELATION
(PERSONTRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_PSPERVPI_FK_I ON PSIM.PS_PERSON_CORRELATION
(PERSON_VPID_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_STDASSAUT_FK_I ON PSIM.PS_PERSON_CORRELATION
(ASSIGNING_AUTHORITY_TYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_STDIDNTYP_FK_I ON PSIM.PS_PERSON_CORRELATION
(IDENTIFIER_TYPE_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERCOR_SYS_ALT_IDE_I ON PSIM.PS_PERSON_CORRELATION
(SYSTEM_OF_INTEREST_TYPE_ID, IDENTIFIER_TYPE_ID, ALTKEY1_TEXT)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_CORRELATION FOR PSIM.PS_PERSON_CORRELATION;


ALTER TABLE PSIM.PS_PERSON_CORRELATION ADD (
  CONSTRAINT PSPERCOR_PK
  PRIMARY KEY
  (PS_PERSON_CORRELATION_ID)
  USING INDEX PSIM.PSPERCOR_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_CORRELATION ADD (
  CONSTRAINT PSPERCOR_PSPERCOR_FK 
  FOREIGN KEY (SURVIVING_CORRELATION_ID) 
  REFERENCES PSIM.PS_PERSON_CORRELATION (PS_PERSON_CORRELATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERCOR_PSPERTRA_FK 
  FOREIGN KEY (PERSONTRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERCOR_PSPERVPI_FK 
  FOREIGN KEY (PERSON_VPID_ID) 
  REFERENCES PSIM.PS_PERSON_VPID (PS_PERSONVPID_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERCOR_PSSYSOFINT_FK 
  FOREIGN KEY (SYSTEM_OF_INTEREST_TYPE_ID) 
  REFERENCES PSIM.PS_SYSTEM_OF_INTEREST_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_CORRELATION TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_CORRELATION TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_ALIAS_LINK
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_ALIAS_LINK CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_ALIAS_LINK
(
  PS_PERSON_ALIAS_LINK_ID  NUMBER(20)           NOT NULL,
  RECORD_MODIFIED_COUNT    NUMBER               NOT NULL,
  RECORD_CREATED_BY        VARCHAR2(30 BYTE)    NOT NULL,
  RECORD_CREATED_DATE      DATE                 NOT NULL,
  RECORD_MODIFIED_BY       VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE     DATE,
  PERSON_NAME_HISTORY_ID   NUMBER(20)           NOT NULL,
  PS_SSN_HISTORY_ID        NUMBER(20)           NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_ALIAS_LINK IS 'Associative Table : Connects ps_person_name_history and ps_ssn_history';

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

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

COMMENT ON COLUMN PSIM.PS_PERSON_ALIAS_LINK.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ALIAS_LINK.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSPERALIA_PK ON PSIM.PS_PERSON_ALIAS_LINK
(PS_PERSON_ALIAS_LINK_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERALIA_PSPERNAMHI_FK_I ON PSIM.PS_PERSON_ALIAS_LINK
(PERSON_NAME_HISTORY_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERALIA_PSSSNHIST_FK_I ON PSIM.PS_PERSON_ALIAS_LINK
(PS_SSN_HISTORY_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_ALIAS_LINK FOR PSIM.PS_PERSON_ALIAS_LINK;


ALTER TABLE PSIM.PS_PERSON_ALIAS_LINK ADD (
  CONSTRAINT PSPERALIA_PK
  PRIMARY KEY
  (PS_PERSON_ALIAS_LINK_ID)
  USING INDEX PSIM.PSPERALIA_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_ALIAS_LINK ADD (
  CONSTRAINT PSPERALIA_PSPERNAMHI_FK 
  FOREIGN KEY (PERSON_NAME_HISTORY_ID) 
  REFERENCES PSIM.PS_PERSON_NAME_HISTORY (PS_PERSON_NAME_HISTORY_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERALIA_PSSSNHIST_FK 
  FOREIGN KEY (PS_SSN_HISTORY_ID) 
  REFERENCES PSIM.PS_SSN_HISTORY (PS_SSN_HISTORY_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_ALIAS_LINK TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_ALIAS_LINK TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_ADDRESS_HISTORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_ADDRESS_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_ADDRESS_HISTORY
(
  PS_PERSON_ADDRESS_HISTORY_ID  NUMBER(20)      NOT NULL,
  OWNER_ID                      NUMBER(20)      NOT NULL,
  ADDRESS_TYPE_ID               NUMBER(20)      NOT NULL,
  VERIFICATION_ID               NUMBER(20),
  ADDRESS_LINE1                 VARCHAR2(100 BYTE),
  ADDRESS_LINE2                 VARCHAR2(100 BYTE),
  ADDRESS_LINE3                 VARCHAR2(100 BYTE),
  CITY                          VARCHAR2(60 BYTE),
  STATE_ID                      NUMBER(20),
  ZIP_CODE                      VARCHAR2(20 BYTE),
  EVENT_DATE                    TIMESTAMP(6),
  EVENT_PERSON_ID               VARCHAR2(30 BYTE),
  CHANGE_TYPE_ID                NUMBER(20)      NOT NULL,
  IS_ACTIVE_FLAG                VARCHAR2(1 BYTE) NOT NULL,
  EFFECTIVE_DATE                DATE            NOT NULL,
  EXPIRATION_DATE               DATE            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(18)      NOT NULL,
  BAD_ADDRESS_CODE              VARCHAR2(10 BYTE),
  BAD_ADDRESS_TEXT              VARCHAR2(100 BYTE),
  PROVINCE_CODE                 VARCHAR2(50 BYTE),
  POSTAL_CODE                   VARCHAR2(20 BYTE),
  COUNTRY_CODE                  VARCHAR2(50 BYTE),
  COUNTY_CODE                   VARCHAR2(50 BYTE),
  ZIP_PLUS_4                    VARCHAR2(10 BYTE),
  ADDRESS_TEXT                  VARCHAR2(100 BYTE)
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_ADDRESS_HISTORY IS 'Main table to store Correlated address records in PSIM';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ADDRESS_LINE1 IS 'BR3 Address line #1 is defined as the free text form of the number and street or post office box of a mailing address.';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ADDRESS_LINE2 IS 'BR5 Address line #2 is defined as the free text supplemental to the number and street of a mailing address.';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ADDRESS_LINE3 IS 'BR7 Address line #3 is defined as the free text supplemental to the number and street of a mailing address.';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.CITY IS 'BR9 City is defined as the name of a city used for an address.';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.STATE_ID IS 'Uniquely identifies a data record in the STD_STATE table. Foreign Key not implemented.';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ZIP_CODE IS 'Postal code is defined as the mail code used for mail delivery to a country other than the USA only.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.BAD_ADDRESS_CODE IS 'Based on address type explained';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.BAD_ADDRESS_TEXT IS 'Based on address type explained';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.PROVINCE_CODE IS 'Abbreviation for non-US state/province';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.POSTAL_CODE IS 'Abbreviation for non-US ZIP Code/Postal Code';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.COUNTRY_CODE IS 'Abbreviation for country';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.COUNTY_CODE IS 'Abbreviation for county';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ZIP_PLUS_4 IS 'Address Zip Code Extension';

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS_HISTORY.ADDRESS_TEXT IS 'Address free text description';



CREATE UNIQUE INDEX PSIM.PSPERADDH_PK ON PSIM.PS_PERSON_ADDRESS_HISTORY
(PS_PERSON_ADDRESS_HISTORY_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDH_PSCHGTYP_FK_I ON PSIM.PS_PERSON_ADDRESS_HISTORY
(CHANGE_TYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDH_PSPERADDR_FK_I ON PSIM.PS_PERSON_ADDRESS_HISTORY
(OWNER_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDH_PSVER_FK_I ON PSIM.PS_PERSON_ADDRESS_HISTORY
(VERIFICATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDH_STDADDRTYP_FK_I ON PSIM.PS_PERSON_ADDRESS_HISTORY
(ADDRESS_TYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDH_STDST_FK_I ON PSIM.PS_PERSON_ADDRESS_HISTORY
(STATE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_ADDRESS_HISTORY FOR PSIM.PS_PERSON_ADDRESS_HISTORY;


ALTER TABLE PSIM.PS_PERSON_ADDRESS_HISTORY ADD (
  CONSTRAINT PSPERADDH_PK
  PRIMARY KEY
  (PS_PERSON_ADDRESS_HISTORY_ID)
  USING INDEX PSIM.PSPERADDH_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_ADDRESS_HISTORY ADD (
  CONSTRAINT PSPERADDH_PSCHGTYP_FK 
  FOREIGN KEY (CHANGE_TYPE_ID) 
  REFERENCES PSIM.PS_CHANGE_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERADDH_PSPERADDR_FK 
  FOREIGN KEY (OWNER_ID) 
  REFERENCES PSIM.PS_PERSON_ADDRESS (PS_PERSON_ADDRESS_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSPERADDH_PSVER_FK 
  FOREIGN KEY (VERIFICATION_ID) 
  REFERENCES PSIM.PS_VERIFICATION (PS_VERIFICATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_ADDRESS_HISTORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_ADDRESS_HISTORY TO PSIM_RW;
ALTER TABLE PSIM.PS_PERSON_ADDRESS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_PERSON_ADDRESS CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_PERSON_ADDRESS
(
  PS_PERSON_ADDRESS_ID   NUMBER(20)             NOT NULL,
  PS_PERSON_TRAIT_ID     NUMBER(20)             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(18)             NOT NULL
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_PERSON_ADDRESS IS 'Link between ps_person_trait and ps_person_address_history FK: PERSON_TRAIT_ID';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_PERSON_ADDRESS.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.PSPERADDR_PK ON PSIM.PS_PERSON_ADDRESS
(PS_PERSON_ADDRESS_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSPERADDR_PSPERTRA_FK_I ON PSIM.PS_PERSON_ADDRESS
(PS_PERSON_TRAIT_ID)
LOGGING
TABLESPACE L_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_PERSON_ADDRESS FOR PSIM.PS_PERSON_ADDRESS;


ALTER TABLE PSIM.PS_PERSON_ADDRESS ADD (
  CONSTRAINT PSPERADDR_PK
  PRIMARY KEY
  (PS_PERSON_ADDRESS_ID)
  USING INDEX PSIM.PSPERADDR_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_PERSON_ADDRESS ADD (
  CONSTRAINT PSPERADDR_PSPERTRA_FK 
  FOREIGN KEY (PS_PERSON_TRAIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_PERSON_ADDRESS TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_PERSON_ADDRESS TO PSIM_RW;
ALTER TABLE PSIM.PS_MESSAGE_LOG
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_MESSAGE_LOG CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_MESSAGE_LOG
(
  PS_MESSAGE_LOG_ID            NUMBER(20)       NOT NULL,
  PERSON_CORRELATION_ID        NUMBER(20),
  REFERENCE_PS_MESSAGE_LOG_ID  NUMBER(20),
  MESSAGE_NUMBER               VARCHAR2(200 BYTE) NOT NULL,
  MESSAGE_DIRECTION_CODE       VARCHAR2(1 BYTE) NOT NULL,
  RECORD_CREATED_BY            VARCHAR2(30 BYTE) NOT NULL,
  SENT_COUNTER                 NUMBER(10),
  RECORD_CREATED_DATE          DATE             NOT NULL,
  RECORD_MODIFIED_BY           VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE         DATE,
  RECORD_MODIFIED_COUNT        NUMBER           NOT NULL,
  MESSAGE_TEXT                 BLOB,
  SENDER_CODE                  VARCHAR2(10 BYTE),
  MESSAGE_TYPE_CODE            VARCHAR2(10 BYTE)
)
LOB (MESSAGE_TEXT) STORE AS (
  TABLESPACE  L_DATA_01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          128M
                  NEXT             128M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX PSIM.MSGLOG_MSGLOG_FK_I ON PSIM.PS_MESSAGE_LOG
(REFERENCE_PS_MESSAGE_LOG_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.MSGLOG_PK ON PSIM.PS_MESSAGE_LOG
(PS_MESSAGE_LOG_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.MSGLOG_PSPERCOR_FK_I ON PSIM.PS_MESSAGE_LOG
(PERSON_CORRELATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.MSGLOG_PS_MESSAGE_LOG_NU_I ON PSIM.PS_MESSAGE_LOG
(MESSAGE_NUMBER)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          200M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_MESSAGE_LOG FOR PSIM.PS_MESSAGE_LOG;


ALTER TABLE PSIM.PS_MESSAGE_LOG ADD (
  CONSTRAINT AVCON_1179948058_MESSA_000
  CHECK (MESSAGE_DIRECTION_CODE IN ('O', 'I'))
  ENABLE VALIDATE,
  CONSTRAINT MSGLOG_PK
  PRIMARY KEY
  (PS_MESSAGE_LOG_ID)
  USING INDEX PSIM.MSGLOG_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_MESSAGE_LOG ADD (
  CONSTRAINT MSGLOG_PSPERCOR_FK 
  FOREIGN KEY (PERSON_CORRELATION_ID) 
  REFERENCES PSIM.PS_PERSON_CORRELATION (PS_PERSON_CORRELATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_MESSAGE_LOG TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_MESSAGE_LOG TO PSIM_RW;
DROP TABLE PSIM.PS_HIGH_KEY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_HIGH_KEY
(
  NEXT_INT  NUMBER(20)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_HIGH_KEY IS 'Used by the Dataman framework to simulate an Oracle sequence on non-Oracle databases. This table is used with PSIM tables in Cache.';



CREATE OR REPLACE PUBLIC SYNONYM PS_HIGH_KEY FOR PSIM.PS_HIGH_KEY;


GRANT SELECT ON PSIM.PS_HIGH_KEY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_HIGH_KEY TO PSIM_RW;
ALTER TABLE PSIM.PS_FILE_BASED_ENUM
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_FILE_BASED_ENUM CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_FILE_BASED_ENUM
(
  PS_FILE_BASED_ENUM_ID     NUMBER(20)          NOT NULL,
  IEN_TEXT                  VARCHAR2(20 BYTE)   NOT NULL,
  SSN                       VARCHAR2(9 BYTE),
  LAST_NAME                 VARCHAR2(35 BYTE)   NOT NULL,
  FIRST_NAME                VARCHAR2(25 BYTE),
  MIDDLE_NAME               VARCHAR2(25 BYTE),
  SUFFIX                    VARCHAR2(10 BYTE),
  PREFIX                    VARCHAR2(10 BYTE),
  EDUCATIONAL_DEGREE        VARCHAR2(10 BYTE),
  GENDER_IND                VARCHAR2(1 BYTE),
  BIRTH_DATE_TEXT           VARCHAR2(10 BYTE),
  HOME_IEN_TEXT             VARCHAR2(20 BYTE),
  HOME_STATION_NUMBER_TEXT  VARCHAR2(20 BYTE)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_FILE_BASED_ENUM IS 'A table used to hold incoming data files for processing file based enumerations. This table may still be used to enumerate providers from VetPro or VHANE.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.SSN IS 'The verified social security number that has been assigned by the Social Security Agency to the person.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.LAST_NAME IS 'BR7321 Representative Last Name is defined as the family name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.FIRST_NAME IS 'BR7323 Representative First Name is defined as the given name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.MIDDLE_NAME IS 'BR7325 Representative middle Name is defined as the middle name of the association type identified.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.SUFFIX IS 'BR7329 Representative suffix is defined as the suffix of the association type identified.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.PREFIX IS 'BR7327 Representative prefix is defined as the prefix of the association type identified.';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.EDUCATIONAL_DEGREE IS 'Educational Degree attained by person';

COMMENT ON COLUMN PSIM.PS_FILE_BASED_ENUM.BIRTH_DATE_TEXT IS 'BR7731 The date of birth is defined as the birth date of the veteran or spouse associated with the communication to be mailed.';



CREATE UNIQUE INDEX PSIM.PSFILBASEN_PK ON PSIM.PS_FILE_BASED_ENUM
(PS_FILE_BASED_ENUM_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_FILE_BASED_ENUM FOR PSIM.PS_FILE_BASED_ENUM;


ALTER TABLE PSIM.PS_FILE_BASED_ENUM ADD (
  CONSTRAINT PSFILBASEN_PK
  PRIMARY KEY
  (PS_FILE_BASED_ENUM_ID)
  USING INDEX PSIM.PSFILBASEN_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_FILE_BASED_ENUM TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_FILE_BASED_ENUM TO PSIM_RW;
ALTER TABLE PSIM.PS_EDIT_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_EDIT_TYPE CASCADE CONSTRAINTS;

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

COMMENT ON COLUMN PSIM.PS_EDIT_TYPE.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 PSIM.PS_EDIT_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_EDIT_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_EDIT_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSEDITYP_NAME_UK ON PSIM.PS_EDIT_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSEDITYP_PK ON PSIM.PS_EDIT_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_EDIT_TYPE FOR PSIM.PS_EDIT_TYPE;


ALTER TABLE PSIM.PS_EDIT_TYPE ADD (
  CONSTRAINT PSEDITYP_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSEDITYP_PK
  ENABLE VALIDATE,
  CONSTRAINT PSEDITYP_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSEDITYP_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_EDIT_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_EDIT_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_EDIT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_EDIT CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_EDIT
(
  PS_EDIT_ID             NUMBER(20)             NOT NULL,
  STATUS_DESCRIPTION     VARCHAR2(255 BYTE)     NOT NULL,
  REMARKS                VARCHAR2(255 BYTE),
  EXCEPTIONS_TEXT        VARCHAR2(2000 BYTE)    NOT NULL,
  EDIT_TYPE_ID           NUMBER(20)             NOT NULL,
  PRIMARY_TRAIT_EDIT_ID  NUMBER(20)             NOT NULL,
  NAME_TEXT              VARCHAR2(255 BYTE)     NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN PSIM.PS_EDIT.REMARKS IS 'Text including any additional information for the action taken for the particular mailing record';

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

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

COMMENT ON COLUMN PSIM.PS_EDIT.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_EDIT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSEDI_PK ON PSIM.PS_EDIT
(PS_EDIT_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSEDI_PSEDITYP_FK_I ON PSIM.PS_EDIT
(EDIT_TYPE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSEDI_PSPERTRA_FK_I ON PSIM.PS_EDIT
(PRIMARY_TRAIT_EDIT_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_EDIT FOR PSIM.PS_EDIT;


ALTER TABLE PSIM.PS_EDIT ADD (
  CONSTRAINT PSEDI_PK
  PRIMARY KEY
  (PS_EDIT_ID)
  USING INDEX PSIM.PSEDI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_EDIT ADD (
  CONSTRAINT PSEDI_PSEDITYP_FK 
  FOREIGN KEY (EDIT_TYPE_ID) 
  REFERENCES PSIM.PS_EDIT_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSEDI_PSPERTRA_FK 
  FOREIGN KEY (PRIMARY_TRAIT_EDIT_ID) 
  REFERENCES PSIM.PS_PERSON_TRAIT (PS_PERSON_TRAIT_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_EDIT TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_EDIT TO PSIM_RW;
ALTER TABLE PSIM.PS_DUPE_RESOLUTION_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_DUPE_RESOLUTION_TYPE CASCADE CONSTRAINTS;

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

COMMENT ON COLUMN PSIM.PS_DUPE_RESOLUTION_TYPE.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 PSIM.PS_DUPE_RESOLUTION_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_DUPE_RESOLUTION_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_DUPE_RESOLUTION_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSDUPRES_NAME_UK ON PSIM.PS_DUPE_RESOLUTION_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSDUPRES_PK ON PSIM.PS_DUPE_RESOLUTION_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_DUPE_RESOLUTION_TYPE FOR PSIM.PS_DUPE_RESOLUTION_TYPE;


ALTER TABLE PSIM.PS_DUPE_RESOLUTION_TYPE ADD (
  CONSTRAINT PSDUPRES_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSDUPRES_PK
  ENABLE VALIDATE,
  CONSTRAINT PSDUPRES_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSDUPRES_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_DUPE_RESOLUTION_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_DUPE_RESOLUTION_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_DUPE_REASON_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_DUPE_REASON_TYPE CASCADE CONSTRAINTS;

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

COMMENT ON COLUMN PSIM.PS_DUPE_REASON_TYPE.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 PSIM.PS_DUPE_REASON_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_DUPE_REASON_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_DUPE_REASON_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSDUPREA_NAME_UK ON PSIM.PS_DUPE_REASON_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSDUPREA_PK ON PSIM.PS_DUPE_REASON_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_DUPE_REASON_TYPE FOR PSIM.PS_DUPE_REASON_TYPE;


ALTER TABLE PSIM.PS_DUPE_REASON_TYPE ADD (
  CONSTRAINT PSDUPREA_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSDUPREA_PK
  ENABLE VALIDATE,
  CONSTRAINT PSDUPREA_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSDUPREA_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_DUPE_REASON_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_DUPE_REASON_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_DUPE_POTENTIAL
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_DUPE_POTENTIAL CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_DUPE_POTENTIAL
(
  PS_DUPE_POTENTIAL_ID   NUMBER(20)             NOT NULL,
  NAME_TEXT              CHAR(16 BYTE)          NOT NULL,
  PERSON_VPID1_ID        NUMBER(20)             NOT NULL,
  PERSON_VPID2_ID        NUMBER(20)             NOT NULL,
  DUPE_REASON_ID         NUMBER(20)             NOT NULL,
  DUPE_RESOLUTION_ID     NUMBER(20),
  RESOLUTION_DATE        TIMESTAMP(6),
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

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

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

COMMENT ON COLUMN PSIM.PS_DUPE_POTENTIAL.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_DUPE_POTENTIAL.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSDUPPOT_PK ON PSIM.PS_DUPE_POTENTIAL
(PS_DUPE_POTENTIAL_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSDUPPOT_PSDUPREA_FK_I ON PSIM.PS_DUPE_POTENTIAL
(DUPE_REASON_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSDUPPOT_PSDUPRES_FK_I ON PSIM.PS_DUPE_POTENTIAL
(DUPE_RESOLUTION_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSDUPPOT_PSPERVPI1_FK_I ON PSIM.PS_DUPE_POTENTIAL
(PERSON_VPID1_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSDUPPOT_PSPERVPI2_FK_I ON PSIM.PS_DUPE_POTENTIAL
(PERSON_VPID2_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_DUPE_POTENTIAL FOR PSIM.PS_DUPE_POTENTIAL;


ALTER TABLE PSIM.PS_DUPE_POTENTIAL ADD (
  CONSTRAINT PSDUPPOT_PK
  PRIMARY KEY
  (PS_DUPE_POTENTIAL_ID)
  USING INDEX PSIM.PSDUPPOT_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_DUPE_POTENTIAL ADD (
  CONSTRAINT PSDUPPOT_PSDUPREA_FK 
  FOREIGN KEY (DUPE_REASON_ID) 
  REFERENCES PSIM.PS_DUPE_REASON_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSDUPPOT_PSDUPRES_FK 
  FOREIGN KEY (DUPE_RESOLUTION_ID) 
  REFERENCES PSIM.PS_DUPE_RESOLUTION_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSDUPPOT_PSPERVPI1_FK 
  FOREIGN KEY (PERSON_VPID1_ID) 
  REFERENCES PSIM.PS_PERSON_VPID (PS_PERSONVPID_ID)
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT PSDUPPOT_PSPERVPI2_FK 
  FOREIGN KEY (PERSON_VPID2_ID) 
  REFERENCES PSIM.PS_PERSON_VPID (PS_PERSONVPID_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_DUPE_POTENTIAL TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_DUPE_POTENTIAL TO PSIM_RW;
ALTER TABLE PSIM.PS_CORRELATION_CATEGORY
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_CORRELATION_CATEGORY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_CORRELATION_CATEGORY
(
  PS_CORRELATION_CATEGORY_ID  NUMBER(20)        NOT NULL,
  NAME_TEXT                   VARCHAR2(80 BYTE) NOT NULL,
  STATUS_IND                  VARCHAR2(1 BYTE),
  CORRELATION_ID              NUMBER(20)        NOT NULL,
  CATEGORY_ID                 NUMBER(20)        NOT NULL,
  RECORD_MODIFIED_COUNT       NUMBER            NOT NULL,
  RECORD_CREATED_BY           VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE         DATE              NOT NULL,
  RECORD_MODIFIED_BY          VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE        DATE
)
TABLESPACE L_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128M
            NEXT             128M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_CORRELATION_CATEGORY IS 'Links to std_personcaegory to determine if the person is a (employee,patient, IT user,visitor etc.) FK: ps_person_correlation_id';

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

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

COMMENT ON COLUMN PSIM.PS_CORRELATION_CATEGORY.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_CORRELATION_CATEGORY.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSCORCAT_PK ON PSIM.PS_CORRELATION_CATEGORY
(PS_CORRELATION_CATEGORY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSCORCAT_PSPERCOR_FK_I ON PSIM.PS_CORRELATION_CATEGORY
(CORRELATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.PSCORCAT_STDPERCAT_FK_I ON PSIM.PS_CORRELATION_CATEGORY
(CATEGORY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_CORRELATION_CATEGORY FOR PSIM.PS_CORRELATION_CATEGORY;


ALTER TABLE PSIM.PS_CORRELATION_CATEGORY ADD (
  CONSTRAINT PSCORCAT_PK
  PRIMARY KEY
  (PS_CORRELATION_CATEGORY_ID)
  USING INDEX PSIM.PSCORCAT_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.PS_CORRELATION_CATEGORY ADD (
  CONSTRAINT PSCORCAT_PSPERCOR_FK 
  FOREIGN KEY (CORRELATION_ID) 
  REFERENCES PSIM.PS_PERSON_CORRELATION (PS_PERSON_CORRELATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_CORRELATION_CATEGORY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_CORRELATION_CATEGORY TO PSIM_RW;
ALTER TABLE PSIM.PS_CHANGE_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_CHANGE_TYPE CASCADE CONSTRAINTS;

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

COMMENT ON TABLE PSIM.PS_CHANGE_TYPE IS 'Lookup table - (Add,Update,Delete)';

COMMENT ON COLUMN PSIM.PS_CHANGE_TYPE.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 PSIM.PS_CHANGE_TYPE.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.PS_CHANGE_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.PS_CHANGE_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.PSCHGTYP_NAME_UK ON PSIM.PS_CHANGE_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.PSCHGTYP_PK ON PSIM.PS_CHANGE_TYPE
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_CHANGE_TYPE FOR PSIM.PS_CHANGE_TYPE;


ALTER TABLE PSIM.PS_CHANGE_TYPE ADD (
  CONSTRAINT PSCHGTYP_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.PSCHGTYP_PK
  ENABLE VALIDATE,
  CONSTRAINT PSCHGTYP_NAME_UK
  UNIQUE (NAME)
  USING INDEX PSIM.PSCHGTYP_NAME_UK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_CHANGE_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_CHANGE_TYPE TO PSIM_RW;
ALTER TABLE PSIM.PS_ADT_EVENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.PS_ADT_EVENT CASCADE CONSTRAINTS;

CREATE TABLE PSIM.PS_ADT_EVENT
(
  PS_ADT_EVENT_ID  NUMBER(20)                   NOT NULL,
  RECEIVED_DATE    TIMESTAMP(6)                 NOT NULL,
  DATA             LONG RAW                     NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.PS_ADT_EVENT IS 'An old table that will be going away soon. Support has been removed from PSIM for this table.';



CREATE UNIQUE INDEX PSIM.PSADTEVT_PK ON PSIM.PS_ADT_EVENT
(PS_ADT_EVENT_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM PS_ADT_EVENT FOR PSIM.PS_ADT_EVENT;


ALTER TABLE PSIM.PS_ADT_EVENT ADD (
  CONSTRAINT PSADTEVT_PK
  PRIMARY KEY
  (PS_ADT_EVENT_ID)
  USING INDEX PSIM.PSADTEVT_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.PS_ADT_EVENT TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.PS_ADT_EVENT TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TEAM_MEMBER
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TEAM_MEMBER CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TEAM_MEMBER
(
  IMDQ_TEAM_MEMBER_ID    NUMBER(20)             NOT NULL,
  VPID                   VARCHAR2(29 BYTE)      NOT NULL,
  NAME                   VARCHAR2(50 BYTE)      NOT NULL,
  VA_DOMAIN_LOGIN        VARCHAR2(20 BYTE)      NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE,
  PIV_ID                 VARCHAR2(25 BYTE),
  GROUP_LIST             VARCHAR2(50 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TEAM_MEMBER IS 'A client maintained list of team members.';

COMMENT ON COLUMN PSIM.IMDQ_TEAM_MEMBER.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_TEAM_MEMBER.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TEAM_MEMBER.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.IMDQ_TEAM_MEMBER.PIV_ID IS 'Unique ID associated with the Personal Identity Verification (PIV) of Federal Employees and Contractors Background ';

COMMENT ON COLUMN PSIM.IMDQ_TEAM_MEMBER.GROUP_LIST IS 'Groups the member is associated with';



CREATE INDEX PSIM.IMDQMMBR_PIV_I ON PSIM.IMDQ_TEAM_MEMBER
(PIV_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTMMBR_GRPLST_I ON PSIM.IMDQ_TEAM_MEMBER
(GROUP_LIST)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQTMMBR_PK ON PSIM.IMDQ_TEAM_MEMBER
(IMDQ_TEAM_MEMBER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTMMBR_VPID_I ON PSIM.IMDQ_TEAM_MEMBER
(VPID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TEAM_MEMBER FOR PSIM.IMDQ_TEAM_MEMBER;


ALTER TABLE PSIM.IMDQ_TEAM_MEMBER ADD (
  CONSTRAINT IMDQTMMBR_PK
  PRIMARY KEY
  (IMDQ_TEAM_MEMBER_ID)
  USING INDEX PSIM.IMDQTMMBR_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TEAM_MEMBER TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TEAM_MEMBER TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TASK_VPIDS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TASK_VPIDS CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TASK_VPIDS
(
  IMDQ_TASK_VPIDS_ID     NUMBER(20)             NOT NULL,
  VPID                   VARCHAR2(29 BYTE)      NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE,
  IMDQ_TASK_ID           NUMBER(20)             NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TASK_VPIDS IS 'Allows IMDQ to associate multiple VPIDs with Tasks.';

COMMENT ON COLUMN PSIM.IMDQ_TASK_VPIDS.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_VPIDS.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_VPIDS.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQTSKVPD_IMDQTASK_FK_I ON PSIM.IMDQ_TASK_VPIDS
(IMDQ_TASK_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQTSKVPD_PK ON PSIM.IMDQ_TASK_VPIDS
(IMDQ_TASK_VPIDS_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTSKVPD_VPID_I ON PSIM.IMDQ_TASK_VPIDS
(VPID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TASK_VPIDS FOR PSIM.IMDQ_TASK_VPIDS;


ALTER TABLE PSIM.IMDQ_TASK_VPIDS ADD (
  CONSTRAINT IMDQTSKVPD_PK
  PRIMARY KEY
  (IMDQ_TASK_VPIDS_ID)
  USING INDEX PSIM.IMDQTSKVPD_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_TASK_VPIDS ADD (
  CONSTRAINT IMDQTSKVPD_IMDQTASK_FK 
  FOREIGN KEY (IMDQ_TASK_ID) 
  REFERENCES PSIM.IMDQ_TASK (IMDQ_TASK_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TASK_VPIDS TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TASK_VPIDS TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TASK_SOI
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TASK_SOI CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TASK_SOI
(
  IMDQ_TASK_SOI_ID       NUMBER(20)             NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE,
  PSSYSOFINT_ID          NUMBER(20)             NOT NULL,
  IMDQ_TASK_ID           NUMBER(20)             NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TASK_SOI IS 'Allows IMDQ to associate multiple Systems of Interest with Tasks.';

COMMENT ON COLUMN PSIM.IMDQ_TASK_SOI.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_SOI.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_SOI.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQTSKSOI_IMDQTASK_FK_I ON PSIM.IMDQ_TASK_SOI
(IMDQ_TASK_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQTSKSOI_PK ON PSIM.IMDQ_TASK_SOI
(IMDQ_TASK_SOI_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTSKSOI_PSSYSOFINT_FK_I ON PSIM.IMDQ_TASK_SOI
(PSSYSOFINT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TASK_SOI FOR PSIM.IMDQ_TASK_SOI;


ALTER TABLE PSIM.IMDQ_TASK_SOI ADD (
  CONSTRAINT IMDQTSKSOI_PK
  PRIMARY KEY
  (IMDQ_TASK_SOI_ID)
  USING INDEX PSIM.IMDQTSKSOI_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_TASK_SOI ADD (
  CONSTRAINT IMDQTSKSOI_IMDQTASK_FK 
  FOREIGN KEY (IMDQ_TASK_ID) 
  REFERENCES PSIM.IMDQ_TASK (IMDQ_TASK_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQTSKSOI_PSSYSOFINT_FK 
  FOREIGN KEY (PSSYSOFINT_ID) 
  REFERENCES PSIM.PS_SYSTEM_OF_INTEREST_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TASK_SOI TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TASK_SOI TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TASK_NOTES
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TASK_NOTES CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TASK_NOTES
(
  IMDQ_TASK_NOTES_ID     NUMBER(20)             NOT NULL,
  TEXT1                  VARCHAR2(4000 BYTE)    NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE,
  IMDQ_TASK_ID           NUMBER(20)             NOT NULL,
  IMDQ_TEAM_MEMBER_ID    NUMBER(20)             NOT NULL,
  TEXT2                  VARCHAR2(4000 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TASK_NOTES IS 'Allows addition of multiple Notes for a task over time';

COMMENT ON COLUMN PSIM.IMDQ_TASK_NOTES.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_NOTES.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_NOTES.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQTSKNTS_IMDQTASK_FK_I ON PSIM.IMDQ_TASK_NOTES
(IMDQ_TASK_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTSKNTS_IMDQTMMBR_FK_I ON PSIM.IMDQ_TASK_NOTES
(IMDQ_TEAM_MEMBER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQTSKNTS_PK ON PSIM.IMDQ_TASK_NOTES
(IMDQ_TASK_NOTES_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TASK_NOTES FOR PSIM.IMDQ_TASK_NOTES;


ALTER TABLE PSIM.IMDQ_TASK_NOTES ADD (
  CONSTRAINT IMDQTSKNTS_PK
  PRIMARY KEY
  (IMDQ_TASK_NOTES_ID)
  USING INDEX PSIM.IMDQTSKNTS_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_TASK_NOTES ADD (
  CONSTRAINT IMDQTSKNTS_IMDQTASK_FK 
  FOREIGN KEY (IMDQ_TASK_ID) 
  REFERENCES PSIM.IMDQ_TASK (IMDQ_TASK_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQTSKNTS_IMDQTMMBR_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TASK_NOTES TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TASK_NOTES TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TASK_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TASK_H CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TASK_H
(
  IMDQ_TASK_H_ID                NUMBER(20)      NOT NULL,
  TRANSACTION_TYPE_CODE         VARCHAR2(1 BYTE),
  IMDQ_TASK_ID                  NUMBER(20)      NOT NULL,
  IMDQ_EXCEPTION_ID             NUMBER(20),
  STD_IDTASKPROBCATEGORY_ID     NUMBER(20),
  STD_IDTASKSTATUSTYPE_ID       NUMBER(20),
  IMDQ_TEAM_MEMBER_ASSGN_TO_ID  NUMBER(20),
  IMDQ_TEAM_MEMBER_ASSGN_BY_ID  NUMBER(20),
  TRACKING_NUMBER               VARCHAR2(15 BYTE) NOT NULL,
  REMEDY_TICKET                 VARCHAR2(80 BYTE),
  PROBLEM_DESC                  VARCHAR2(60 BYTE),
  ASSGN_DATE                    DATE,
  RECORD_MODIFIED_COUNT         NUMBER          NOT NULL,
  RECORD_CREATED_BY             VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE           DATE            NOT NULL,
  RECORD_MODIFIED_BY            VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE          DATE
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TASK_H IS 'This is the history table for IMDQ TASK to record any changed (insert/update) data.';

COMMENT ON COLUMN PSIM.IMDQ_TASK_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 PSIM.IMDQ_TASK_H.RECORD_MODIFIED_COUNT IS 'Number of times this record has been modified with zero indicating the initial creation, 1...n.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.IMDQTASKH_PK ON PSIM.IMDQ_TASK_H
(IMDQ_TASK_H_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TASK_H FOR PSIM.IMDQ_TASK_H;


ALTER TABLE PSIM.IMDQ_TASK_H ADD (
  CONSTRAINT AVCON_1172516538_TRANS_000
  CHECK (TRANSACTION_TYPE_CODE IN ('U', 'I', 'D'))
  ENABLE VALIDATE,
  CONSTRAINT IMDQTASKH_PK
  PRIMARY KEY
  (IMDQ_TASK_H_ID)
  USING INDEX PSIM.IMDQTASKH_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TASK_H TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TASK_H TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_TASK
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_TASK CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_TASK
(
  IMDQ_TASK_ID                  NUMBER(20)      NOT NULL,
  IMDQ_EXCEPTION_ID             NUMBER(20),
  STD_IDTASKPROBCATEGORY_ID     NUMBER(20),
  STD_IDTASKSTATUSTYPE_ID       NUMBER(20),
  IMDQ_TEAM_MEMBER_ASSGN_TO_ID  NUMBER(20),
  IMDQ_TEAM_MEMBER_ASSGN_BY_ID  NUMBER(20),
  TRACKING_NUMBER               VARCHAR2(15 BYTE) NOT NULL,
  REMEDY_TICKET                 VARCHAR2(80 BYTE),
  PROBLEM_DESC                  VARCHAR2(60 BYTE),
  ASSGN_DATE                    DATE,
  RECORD_MODIFIED_COUNT         NUMBER          NOT NULL,
  RECORD_CREATED_BY             VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE           DATE            NOT NULL,
  RECORD_MODIFIED_BY            VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE          DATE
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_TASK IS 'This is the main table for IMDQ to record data against. IMDQ_EXCEPTION table is assigned to a task in this table All exceptions in This table is edited by the UI. A separate History table is needed to maintain relationships to other tables.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_TASK.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_TASK.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQTASK_IMDQEXPN_FK_I ON PSIM.IMDQ_TASK
(IMDQ_EXCEPTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_IMDQTMMBR_FK_I ON PSIM.IMDQ_TASK
(IMDQ_TEAM_MEMBER_ASSGN_BY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_IMDQTMMBR_HAVE1_FK_I ON PSIM.IMDQ_TASK
(IMDQ_TEAM_MEMBER_ASSGN_TO_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQTASK_PK ON PSIM.IMDQ_TASK
(IMDQ_TASK_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_REMEDY_TICKET_I ON PSIM.IMDQ_TASK
(REMEDY_TICKET)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_STDIMDQTPC_FK_I ON PSIM.IMDQ_TASK
(STD_IDTASKPROBCATEGORY_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_STDIMDQTS_FK_I ON PSIM.IMDQ_TASK
(STD_IDTASKSTATUSTYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQTASK_TRACKING_NUMBER_I ON PSIM.IMDQ_TASK
(TRACKING_NUMBER)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_TASK FOR PSIM.IMDQ_TASK;


ALTER TABLE PSIM.IMDQ_TASK ADD (
  CONSTRAINT IMDQTASK_PK
  PRIMARY KEY
  (IMDQ_TASK_ID)
  USING INDEX PSIM.IMDQTASK_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_TASK ADD (
  CONSTRAINT IMDQTASK_IMDQEXPN_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_ID) 
  REFERENCES PSIM.IMDQ_EXCEPTION (IMDQ_EXCEPTION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQTASK_IMDQTMMBR_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ASSGN_BY_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQTASK_IMDQTMMBR_HAVE1_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ASSGN_TO_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_TASK TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_TASK TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_ROLE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_ROLE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_ROLE
(
  IMDQ_ROLE_ID           NUMBER(20)             NOT NULL,
  CODE                   VARCHAR2(1 BYTE)       NOT NULL,
  NAME                   VARCHAR2(20 BYTE)      NOT NULL,
  DESCRIPTION            VARCHAR2(60 BYTE),
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_ROLE IS 'List of Roles for IMDQ users';

COMMENT ON COLUMN PSIM.IMDQ_ROLE.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_ROLE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_ROLE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE UNIQUE INDEX PSIM.IMDQROLE_PK ON PSIM.IMDQ_ROLE
(IMDQ_ROLE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_ROLE FOR PSIM.IMDQ_ROLE;


ALTER TABLE PSIM.IMDQ_ROLE ADD (
  CONSTRAINT IMDQROLE_PK
  PRIMARY KEY
  (IMDQ_ROLE_ID)
  USING INDEX PSIM.IMDQROLE_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_ROLE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_ROLE TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_MEM_ROLE_REF
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_MEM_ROLE_REF CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_MEM_ROLE_REF
(
  IMDQ_TEAM_MEMBER_ID    NUMBER(20)             NOT NULL,
  IMDQ_MEM_ROLE_REF_ID   NUMBER(20)             NOT NULL,
  IMDQ_ROLE_ID           NUMBER(20)             NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           NOT NULL,
  INACTIVE_DATE          DATE,
  RECORD_MODIFIED_COUNT  NUMBER                 NOT NULL,
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE   DATE
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_MEM_ROLE_REF IS 'Associative entity to link users and roles (M to M)';

COMMENT ON COLUMN PSIM.IMDQ_MEM_ROLE_REF.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_MEM_ROLE_REF.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_MEM_ROLE_REF.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQMRREF_IMDQROLE_FK_I ON PSIM.IMDQ_MEM_ROLE_REF
(IMDQ_ROLE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQMRREF_IMDQTMMBR_FK_I ON PSIM.IMDQ_MEM_ROLE_REF
(IMDQ_TEAM_MEMBER_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQMRREF_PK ON PSIM.IMDQ_MEM_ROLE_REF
(IMDQ_MEM_ROLE_REF_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_MEM_ROLE_REF FOR PSIM.IMDQ_MEM_ROLE_REF;


ALTER TABLE PSIM.IMDQ_MEM_ROLE_REF ADD (
  CONSTRAINT IMDQMRREF_PK
  PRIMARY KEY
  (IMDQ_MEM_ROLE_REF_ID)
  USING INDEX PSIM.IMDQMRREF_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_MEM_ROLE_REF ADD (
  CONSTRAINT IMDQMRREF_IMDQROLE_FK 
  FOREIGN KEY (IMDQ_ROLE_ID) 
  REFERENCES PSIM.IMDQ_ROLE (IMDQ_ROLE_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQMRREF_IMDQTMMBR_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_MEM_ROLE_REF TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_MEM_ROLE_REF TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_MEMBER_SITES
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_MEMBER_SITES CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_MEMBER_SITES
(
  IMDQ_MEMBER_SITES_ID   NUMBER(20)             NOT NULL,
  IMDQ_TEAM_MEMBER_ID    NUMBER(20)             NOT NULL,
  ALLOWABLE_ACTION_ID    NUMBER(20)             NOT NULL,
  SITE_ID                NUMBER(20)             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(18)             NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

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

COMMENT ON COLUMN PSIM.IMDQ_MEMBER_SITES.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_MEMBER_SITES.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX PSIM.IMDQMS_IMDQATNREF_FK_I ON PSIM.IMDQ_MEMBER_SITES
(ALLOWABLE_ACTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQMS_IMDQTMMBR_FK_I ON PSIM.IMDQ_MEMBER_SITES
(IMDQ_TEAM_MEMBER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQMS_PK ON PSIM.IMDQ_MEMBER_SITES
(IMDQ_MEMBER_SITES_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQMS__FK_I ON PSIM.IMDQ_MEMBER_SITES
(SITE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_MEMBER_SITES FOR PSIM.IMDQ_MEMBER_SITES;


ALTER TABLE PSIM.IMDQ_MEMBER_SITES ADD (
  CONSTRAINT IMDQMS_PK
  PRIMARY KEY
  (IMDQ_MEMBER_SITES_ID)
  USING INDEX PSIM.IMDQMS_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_MEMBER_SITES ADD (
  CONSTRAINT IMDQMS_IMDQATNREF_FK 
  FOREIGN KEY (ALLOWABLE_ACTION_ID) 
  REFERENCES PSIM.IMDQ_ACTION_REF (IMDQ_ACTION_REF_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQMS_IMDQTMMBR_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_MEMBER_SITES TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_MEMBER_SITES TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_EXCEPTION_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_EXCEPTION_TYPE CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_EXCEPTION_TYPE
(
  IMDQ_EXCEPTION_TYPE_ID   NUMBER(20)           NOT NULL,
  CODE                     VARCHAR2(20 BYTE)    NOT NULL,
  NAME                     VARCHAR2(80 BYTE)    NOT NULL,
  DESCRIPTION              VARCHAR2(250 BYTE),
  IS_ACTIVE_FLAG           CHAR(1 BYTE)         NOT NULL,
  INACTIVE_DATE            DATE,
  RECORD_MODIFIED_COUNT    NUMBER               NOT NULL,
  RECORD_CREATED_BY        VARCHAR2(30 BYTE)    NOT NULL,
  RECORD_CREATED_DATE      DATE                 NOT NULL,
  RECORD_MODIFIED_BY       VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE     DATE,
  STD_IDEXCEPTYPEGROUP_ID  NUMBER(20)           NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_EXCEPTION_TYPE IS 'Will contain a list of exceptions.';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TYPE.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';



CREATE INDEX PSIM.IMDQEXPNTY_CODE_I ON PSIM.IMDQ_EXCEPTION_TYPE
(CODE)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPNTY_NAME_I ON PSIM.IMDQ_EXCEPTION_TYPE
(NAME)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQEXPNTY_PK ON PSIM.IMDQ_EXCEPTION_TYPE
(IMDQ_EXCEPTION_TYPE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPNTY_STDIMDQETG_FK_I ON PSIM.IMDQ_EXCEPTION_TYPE
(STD_IDEXCEPTYPEGROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_EXCEPTION_TYPE FOR PSIM.IMDQ_EXCEPTION_TYPE;


ALTER TABLE PSIM.IMDQ_EXCEPTION_TYPE ADD (
  CONSTRAINT IMDQEXPNTY_PK
  PRIMARY KEY
  (IMDQ_EXCEPTION_TYPE_ID)
  USING INDEX PSIM.IMDQEXPNTY_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_EXCEPTION_TYPE TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_EXCEPTION_TYPE TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_EXCEPTION_TRAIT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_EXCEPTION_TRAIT CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_EXCEPTION_TRAIT
(
  IMDQ_EXCEPTION_TRAIT_ID  NUMBER(20)           NOT NULL,
  IMDQ_EXCEPTION_ID        NUMBER(20)           NOT NULL,
  FIRST_NAME               VARCHAR2(25 BYTE),
  MIDDLE_NAME              VARCHAR2(25 BYTE),
  LAST_NAME                VARCHAR2(35 BYTE),
  SSN                      VARCHAR2(20 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,
  INTERNAL_IDENTIFIER      VARCHAR2(150 BYTE)   NOT NULL,
  GENDER                   VARCHAR2(20 BYTE),
  DATE_OF_BIRTH            VARCHAR2(8 BYTE),
  ATTRIBUTE                VARCHAR2(200 BYTE)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_EXCEPTION_TRAIT IS 'Set of Traits (ex. Names, SSN) associated with an exception';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.IMDQ_EXCEPTION_TRAIT_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identiry a data record in this table.';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.RECORD_MODIFIED_BY IS 'Account or user ID for this modification to the record';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.INTERNAL_IDENTIFIER IS 'Unknown';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_TRAIT.ATTRIBUTE IS 'Generic field for other traits';



CREATE INDEX PSIM.IMDEXPTRT_DOB_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(DATE_OF_BIRTH)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_FST_NM_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(FIRST_NAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_IMDEXP_FK_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(IMDQ_EXCEPTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_INT_IDN_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(INTERNAL_IDENTIFIER)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_LST_NM_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(LAST_NAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_MDL_NM_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(MIDDLE_NAME)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDEXPTRT_PK ON PSIM.IMDQ_EXCEPTION_TRAIT
(IMDQ_EXCEPTION_TRAIT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDEXPTRT_SSN_I ON PSIM.IMDQ_EXCEPTION_TRAIT
(SSN)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_EXCEPTION_TRAIT FOR PSIM.IMDQ_EXCEPTION_TRAIT;


ALTER TABLE PSIM.IMDQ_EXCEPTION_TRAIT ADD (
  CONSTRAINT IDMEXNTRT_PK
  PRIMARY KEY
  (IMDQ_EXCEPTION_TRAIT_ID)
  USING INDEX PSIM.IMDEXPTRT_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_EXCEPTION_TRAIT ADD (
  CONSTRAINT RELATIONSHIP19 
  FOREIGN KEY (IMDQ_EXCEPTION_ID) 
  REFERENCES PSIM.IMDQ_EXCEPTION (IMDQ_EXCEPTION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_EXCEPTION_TRAIT TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_EXCEPTION_TRAIT TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_EXCEPTION_IDS
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_EXCEPTION_IDS CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_EXCEPTION_IDS
(
  IMDQ_EXCEPTION_IDS_ID  NUMBER(20)             NOT NULL,
  INTERNAL_IDENTIFIER    VARCHAR2(150 BYTE)     NOT NULL,
  IMDQ_EXCEPTION_ID      NUMBER(20)             NOT NULL,
  SOURCE_SYSTEM_ID       NUMBER(20),
  RECORD_CREATED_BY      VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_CREATED_DATE    DATE                   NOT NULL,
  RECORD_MODIFIED_BY     VARCHAR2(30 BYTE)      NOT NULL,
  RECORD_MODIFIED_DATE   DATE                   NOT NULL,
  RECORD_MODIFIED_COUNT  NUMBER(18)             NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_EXCEPTION_IDS IS 'This is the main table for IMDQ to record data against. IMDQ_EXCEPTION table is assigned to a task in this table All exceptions in This table is edited by the UI. A separate History table is needed to maintain relationships to other tables.';

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_IDS.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION_IDS.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX PSIM.IMDQEXNIDS_IMDQEXPN_FK_I ON PSIM.IMDQ_EXCEPTION_IDS
(IMDQ_EXCEPTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQEXNIDS_PK ON PSIM.IMDQ_EXCEPTION_IDS
(IMDQ_EXCEPTION_IDS_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_EXCEPTION_IDS FOR PSIM.IMDQ_EXCEPTION_IDS;


ALTER TABLE PSIM.IMDQ_EXCEPTION_IDS ADD (
  CONSTRAINT IMDQEXNIDS_PK
  PRIMARY KEY
  (IMDQ_EXCEPTION_IDS_ID)
  USING INDEX PSIM.IMDQEXNIDS_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_EXCEPTION_IDS ADD (
  CONSTRAINT IMDQEXNIDS_IMDQEXPN_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_ID) 
  REFERENCES PSIM.IMDQ_EXCEPTION (IMDQ_EXCEPTION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_EXCEPTION_IDS TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_EXCEPTION_IDS TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_EXCEPTION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_EXCEPTION CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_EXCEPTION
(
  IMDQ_EXCEPTION_ID       NUMBER(20)            NOT NULL,
  ADDITIONAL_INFO         VARCHAR2(2000 BYTE),
  MSG_CONTROL_ID          VARCHAR2(30 BYTE),
  VPID1                   VARCHAR2(150 BYTE),
  VPID2                   VARCHAR2(150 BYTE),
  REQ_SITE_PERSON_ID      VARCHAR2(29 BYTE),
  TASKTIMESTAMP           DATE,
  PROB_MTH_STRING         VARCHAR2(2000 BYTE),
  PROB_MTH_VALUE          NUMBER(5),
  IS_ACTIVE_FLAG          CHAR(1 BYTE)          NOT NULL,
  INACTIVE_DATE           DATE,
  RECORD_MODIFIED_COUNT   NUMBER                NOT NULL,
  RECORD_CREATED_BY       VARCHAR2(30 BYTE)     NOT NULL,
  RECORD_CREATED_DATE     DATE                  NOT NULL,
  RECORD_MODIFIED_BY      VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE    DATE,
  IMDQ_EXCEPTION_TYPE_ID  NUMBER(20)            NOT NULL,
  STD_IDEVENTTYPE_ID      NUMBER(20)            NOT NULL,
  STD_IDENTIFIERTYPE_ID   NUMBER(20)            NOT NULL,
  PSSYSOFINT_ID           NUMBER(20)            NOT NULL,
  EXTERNAL_ID             VARCHAR2(30 BYTE),
  SENDING_APP_ID          NUMBER(20),
  SOURCE                  VARCHAR2(25 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_EXCEPTION IS 'Exceptions that affect the unique identification of an individual as noted in the Identity Management Data Quality Tool Kit.
  2  Examples-
  3  The Source Id does not exist in the ADR
  4  Potential duplicate exception - add person
  5  Five leading OS for SSN and there are shared treating facilities
  6  Entries will be auto generated by PSIM based on defined exception types
  7  table assigned to a task in this table All exceptions in This table is edited by the UI. A separate History table is needed to maintain relationships to other tables. ';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION.INACTIVE_DATE IS 'Retrofitted.';

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

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

COMMENT ON COLUMN PSIM.IMDQ_EXCEPTION.SOURCE IS 'Originating source of incoming exception';



CREATE INDEX PSIM.IMDQEXPN_IMDQEXPNTY_FK_I ON PSIM.IMDQ_EXCEPTION
(IMDQ_EXCEPTION_TYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_MSGCONTROLID_I ON PSIM.IMDQ_EXCEPTION
(MSG_CONTROL_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQEXPN_PK ON PSIM.IMDQ_EXCEPTION
(IMDQ_EXCEPTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_PSSYSOFINT_FK_I ON PSIM.IMDQ_EXCEPTION
(PSSYSOFINT_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_SRC_I ON PSIM.IMDQ_EXCEPTION
(SOURCE)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_STDIDNTYP_FK_I ON PSIM.IMDQ_EXCEPTION
(STD_IDENTIFIERTYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_STDIMDQEVT_FK_I ON PSIM.IMDQ_EXCEPTION
(STD_IDEVENTTYPE_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQEXPN_VPID1_I ON PSIM.IMDQ_EXCEPTION
(VPID1)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_EXCEPTION FOR PSIM.IMDQ_EXCEPTION;


ALTER TABLE PSIM.IMDQ_EXCEPTION ADD (
  CONSTRAINT IMDQEXPN_PK
  PRIMARY KEY
  (IMDQ_EXCEPTION_ID)
  USING INDEX PSIM.IMDQEXPN_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_EXCEPTION ADD (
  CONSTRAINT IMDQEXPN_IMDQEXPNTY_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_TYPE_ID) 
  REFERENCES PSIM.IMDQ_EXCEPTION_TYPE (IMDQ_EXCEPTION_TYPE_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQEXPN_PSSYSOFINT_FK 
  FOREIGN KEY (PSSYSOFINT_ID) 
  REFERENCES PSIM.PS_SYSTEM_OF_INTEREST_TYPE (ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_EXCEPTION TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_EXCEPTION TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_ACTION_REF
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_ACTION_REF CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_ACTION_REF
(
  IMDQ_ACTION_REF_ID     NUMBER(20)             NOT NULL,
  ACTION_NAME            VARCHAR2(30 BYTE)      NOT NULL,
  ACTION_TYPE            VARCHAR2(30 BYTE)      NOT NULL,
  ACTION_DESC            VARCHAR2(50 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(18)             NOT NULL
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

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

COMMENT ON COLUMN PSIM.IMDQ_ACTION_REF.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_ACTION_REF.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.IMDQATNREF_PK ON PSIM.IMDQ_ACTION_REF
(IMDQ_ACTION_REF_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_ACTION_REF FOR PSIM.IMDQ_ACTION_REF;


ALTER TABLE PSIM.IMDQ_ACTION_REF ADD (
  CONSTRAINT IMDQATNREF_PK
  PRIMARY KEY
  (IMDQ_ACTION_REF_ID)
  USING INDEX PSIM.IMDQATNREF_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_ACTION_REF TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_ACTION_REF TO PSIM_RW;
ALTER TABLE PSIM.IMDQ_ACTION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IMDQ_ACTION CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IMDQ_ACTION
(
  IMDQ_ACTION_ID          NUMBER(20)            NOT NULL,
  IMDQ_EXCEPTION_ID       NUMBER(20)            NOT NULL,
  IMDQ_ACTION_REF_ID      NUMBER(20)            NOT NULL,
  IMDQ_TEAM_MEMBER_ID     NUMBER(20)            NOT NULL,
  IMDQ_EXCEPTION_IDS_ID1  NUMBER(20)            NOT NULL,
  IMDQ_EXCEPTION_IDS_ID2  NUMBER(20)            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(18)            NOT NULL,
  IS_ACTIVE_FLAG          CHAR(1 BYTE)          DEFAULT 1                     NOT NULL,
  STATUS                  VARCHAR2(30 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IMDQ_ACTION IS 'Activity performed to resolve an EXCEPTION';

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

COMMENT ON COLUMN PSIM.IMDQ_ACTION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IMDQ_ACTION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN PSIM.IMDQ_ACTION.IS_ACTIVE_FLAG IS 'Determines whether the IMDQ action is still active for the application.';

COMMENT ON COLUMN PSIM.IMDQ_ACTION.STATUS IS 'Indicates the actions status, Open, Pending, In-Progress.. Etc  Values to be handled internally within the application.';



CREATE INDEX PSIM.IMDQATN_IMDQATNREF_FK_I ON PSIM.IMDQ_ACTION
(IMDQ_ACTION_REF_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQATN_IMDQEXNIDS_FK_I ON PSIM.IMDQ_ACTION
(IMDQ_EXCEPTION_IDS_ID2)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQATN_IMDQEXNIDS_HAVE1_FK_I ON PSIM.IMDQ_ACTION
(IMDQ_EXCEPTION_IDS_ID1)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQATN_IMDQEXPN_FK_I ON PSIM.IMDQ_ACTION
(IMDQ_EXCEPTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IMDQATN_IMDQTMMBR_FK_I ON PSIM.IMDQ_ACTION
(IMDQ_TEAM_MEMBER_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IMDQATN_PK ON PSIM.IMDQ_ACTION
(IMDQ_ACTION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IMDQ_ACTION FOR PSIM.IMDQ_ACTION;


ALTER TABLE PSIM.IMDQ_ACTION ADD (
  CONSTRAINT IMDQATN_PK
  PRIMARY KEY
  (IMDQ_ACTION_ID)
  USING INDEX PSIM.IMDQATN_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IMDQ_ACTION ADD (
  CONSTRAINT IMDQATN_IMDQATNREF_FK 
  FOREIGN KEY (IMDQ_ACTION_REF_ID) 
  REFERENCES PSIM.IMDQ_ACTION_REF (IMDQ_ACTION_REF_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQATN_IMDQEXNIDS_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_IDS_ID2) 
  REFERENCES PSIM.IMDQ_EXCEPTION_IDS (IMDQ_EXCEPTION_IDS_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQATN_IMDQEXNIDS_HAVE1_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_IDS_ID1) 
  REFERENCES PSIM.IMDQ_EXCEPTION_IDS (IMDQ_EXCEPTION_IDS_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQATN_IMDQEXPN_FK 
  FOREIGN KEY (IMDQ_EXCEPTION_ID) 
  REFERENCES PSIM.IMDQ_EXCEPTION (IMDQ_EXCEPTION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE,
  CONSTRAINT IMDQATN_IMDQTMMBR_FK 
  FOREIGN KEY (IMDQ_TEAM_MEMBER_ID) 
  REFERENCES PSIM.IMDQ_TEAM_MEMBER (IMDQ_TEAM_MEMBER_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IMDQ_ACTION TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IMDQ_ACTION TO PSIM_RW;
ALTER TABLE PSIM.IDM_STATION_MAP
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IDM_STATION_MAP CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IDM_STATION_MAP
(
  IDM_STATION_MAP_ID     NUMBER(20)             NOT NULL,
  STATION_NUMBER         VARCHAR2(7 BYTE)       NOT NULL,
  STATION_NAME           VARCHAR2(50 BYTE)      NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           DEFAULT 1                     NOT NULL,
  ATTRIBUTE              VARCHAR2(200 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
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IDM_STATION_MAP IS 'IdM station map list';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.IDM_STATION_MAP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identiry a data record in this table.';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.STATION_NUMBER IS 'Station number corresponding to STD_INSTITUTION';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.STATION_NAME IS 'Station name for display';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.IS_ACTIVE_FLAG IS 'Determinses whether the notification is active. 1 = active 0= inactive';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.ATTRIBUTE IS 'Configuration or Attributes for the Station Map. Could be a list or XML.';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.RECORD_MODIFIED_BY IS 'Account or user ID for this modification to the record';

COMMENT ON COLUMN PSIM.IDM_STATION_MAP.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.IDMSTMP_PK ON PSIM.IDM_STATION_MAP
(IDM_STATION_MAP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IDMSTNM_I ON PSIM.IDM_STATION_MAP
(STATION_NUMBER)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IDM_STATION_MAP FOR PSIM.IDM_STATION_MAP;


ALTER TABLE PSIM.IDM_STATION_MAP ADD (
  CONSTRAINT IDMSTMP_PK
  PRIMARY KEY
  (IDM_STATION_MAP_ID)
  USING INDEX PSIM.IDMSTMP_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IDM_STATION_MAP TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IDM_STATION_MAP TO PSIM_RW;
ALTER TABLE PSIM.IDM_NOTIFICATION_GROUP
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IDM_NOTIFICATION_GROUP CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IDM_NOTIFICATION_GROUP
(
  IDM_NOTIFICATION_GROUP_ID  NUMBER(20)         NOT NULL,
  NAME                       VARCHAR2(50 BYTE)  NOT NULL,
  DESCRIPTION                VARCHAR2(50 BYTE)  NOT NULL,
  CODE                       VARCHAR2(20 BYTE)  NOT NULL,
  STATUS                     VARCHAR2(20 BYTE)  NOT NULL,
  IS_ACTIVE_FLAG             CHAR(1 BYTE)       DEFAULT 1                     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(10)         NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IDM_NOTIFICATION_GROUP IS 'Notification Groups';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.IDM_NOTIFICATION_GROUP_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identiry a data record in this table.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.NAME IS 'Group Name';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.DESCRIPTION IS 'Description of the grouping.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.CODE IS 'Group Code or mnemonic';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.STATUS IS 'Notification status.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.IS_ACTIVE_FLAG IS 'Determinses whether the notification is active. 1 = active 0= inactive';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.RECORD_MODIFIED_BY IS 'Account or user ID for this modification to the record';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION_GROUP.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX PSIM.IDMNTFGRP_I ON PSIM.IDM_NOTIFICATION_GROUP
(CODE)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IDMNTFGRP_PK ON PSIM.IDM_NOTIFICATION_GROUP
(IDM_NOTIFICATION_GROUP_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IDM_NOTIFICATION_GROUP FOR PSIM.IDM_NOTIFICATION_GROUP;


ALTER TABLE PSIM.IDM_NOTIFICATION_GROUP ADD (
  CONSTRAINT IDMNTFGRP_PK
  PRIMARY KEY
  (IDM_NOTIFICATION_GROUP_ID)
  USING INDEX PSIM.IDMNTFGRP_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IDM_NOTIFICATION_GROUP TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IDM_NOTIFICATION_GROUP TO PSIM_RW;
ALTER TABLE PSIM.IDM_NOTIFICATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IDM_NOTIFICATION CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IDM_NOTIFICATION
(
  IDM_NOTIFICATION_ID    NUMBER(20)             NOT NULL,
  TYPE                   VARCHAR2(20 BYTE)      NOT NULL,
  NOTE                   VARCHAR2(4000 BYTE)    NOT NULL,
  SOURCE                 VARCHAR2(50 BYTE)      NOT NULL,
  STATUS                 VARCHAR2(20 BYTE)      NOT NULL,
  IS_ACTIVE_FLAG         CHAR(1 BYTE)           DEFAULT 1                     NOT NULL,
  EXPIRATION_DATE        DATE,
  TARGET                 VARCHAR2(255 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
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IDM_NOTIFICATION IS 'Identity Management (IdM) UI and System Notifications';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.IDM_NOTIFICATION_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identiry a data record in this table.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.TYPE IS 'The notification type, determined via the application';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.NOTE IS 'Notification text.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.SOURCE IS 'Source of the notification';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.STATUS IS 'Notification status.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.IS_ACTIVE_FLAG IS 'Determinses whether the notification is active. 1 = active 0= inactive';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.EXPIRATION_DATE IS 'Expiration for this notification.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.TARGET IS 'Intended target for notification of an ID Management event. Might be a person, groiup or identity.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.RECORD_MODIFIED_BY IS 'Account or user ID for this modification to the record';

COMMENT ON COLUMN PSIM.IDM_NOTIFICATION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX PSIM.IDMNTF_PK ON PSIM.IDM_NOTIFICATION
(IDM_NOTIFICATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IDM_NOTIFICATION FOR PSIM.IDM_NOTIFICATION;


ALTER TABLE PSIM.IDM_NOTIFICATION ADD (
  CONSTRAINT IDMNTF_PK
  PRIMARY KEY
  (IDM_NOTIFICATION_ID)
  USING INDEX PSIM.IDMNTF_PK
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IDM_NOTIFICATION TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IDM_NOTIFICATION TO PSIM_RW;
ALTER TABLE PSIM.IDM_NOTE_REF
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.IDM_NOTE_REF CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IDM_NOTE_REF
(
  IDM_NOTE_ID            NUMBER(20)             NOT NULL,
  IDM_NOTIFICATION_ID    NUMBER(20)             NOT NULL,
  TYPE                   VARCHAR2(20 BYTE)      NOT NULL,
  LINK_ID                VARCHAR2(20 BYTE)      NOT NULL,
  COMMENTS               VARCHAR2(200 BYTE),
  READ_DATE              DATE,
  STATUS                 VARCHAR2(20 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(10)             NOT NULL
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.IDM_NOTE_REF IS 'Notification reference.';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.IDM_NOTE_ID IS 'The primary key, generated by the database system in numeric form, used to uniquely identiry a data record in this table.';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.TYPE IS 'The notification type, determined via the application';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.LINK_ID IS 'Identifier associated with this reference';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.COMMENTS IS 'User based comment on referenced notification.';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.READ_DATE IS 'Date or Time the Note was read';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.STATUS IS 'Notification status.';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.RECORD_CREATED_DATE IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.RECORD_MODIFIED_BY IS 'Account or user ID for this modification to the record';

COMMENT ON COLUMN PSIM.IDM_NOTE_REF.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX PSIM.IDMNTF_IDMNTRF_FK_I ON PSIM.IDM_NOTE_REF
(IDM_NOTIFICATION_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IDMNTLNK_NU_ID ON PSIM.IDM_NOTE_REF
(LINK_ID)
LOGGING
TABLESPACE M_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IDMNT_PK ON PSIM.IDM_NOTE_REF
(IDM_NOTE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IDM_NOTE_REF FOR PSIM.IDM_NOTE_REF;


ALTER TABLE PSIM.IDM_NOTE_REF ADD (
  CONSTRAINT IDMNT_PK
  PRIMARY KEY
  (IDM_NOTE_ID)
  USING INDEX PSIM.IDMNT_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.IDM_NOTE_REF ADD (
  CONSTRAINT RELATIONSHIP18 
  FOREIGN KEY (IDM_NOTIFICATION_ID) 
  REFERENCES PSIM.IDM_NOTIFICATION (IDM_NOTIFICATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IDM_NOTE_REF TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IDM_NOTE_REF TO PSIM_RW;
DROP TABLE PSIM.IDM_MESSAGE_LOG CASCADE CONSTRAINTS;

CREATE TABLE PSIM.IDM_MESSAGE_LOG
(
  IDM_MESSAGE_LOG_ID            NUMBER(20)      NOT NULL,
  PERSON_CORRELATION_ID         NUMBER(20),
  REFERENCE_IDM_MESSAGE_LOG_ID  NUMBER(20),
  MESSAGE_NUMBER                VARCHAR2(200 BYTE) NOT NULL,
  MESSAGE_DIRECTION_CODE        VARCHAR2(1 BYTE) NOT NULL,
  RECORD_CREATED_BY             VARCHAR2(30 BYTE) NOT NULL,
  SENT_COUNTER                  NUMBER(10),
  RECORD_CREATED_DATE           DATE            NOT NULL,
  RECORD_MODIFIED_BY            VARCHAR2(30 BYTE),
  RECORD_MODIFIED_DATE          DATE,
  RECORD_MODIFIED_COUNT         NUMBER          NOT NULL,
  MESSAGE_TEXT                  BLOB,
  SENDER_CODE                   VARCHAR2(10 BYTE),
  MESSAGE_TYPE_CODE             VARCHAR2(10 BYTE)
)
LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
  TABLESPACE  L_PSIMLOG_INDEX_01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
  NOCACHE
  LOGGING)
NOCOMPRESS 
TABLESPACE L_PSIMLOG_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
PARTITION BY RANGE (RECORD_CREATED_DATE)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(  
  PARTITION IDMMSGLOG_P400 VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING)
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_DATA_01
    LOB (MESSAGE_TEXT) STORE AS SECUREFILE (
      TABLESPACE  L_PSIMLOG_INDEX_01
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      COMPRESS    MEDIUM
      NOCACHE
      LOGGING
          STORAGE    (
                      INITIAL          4M
                      NEXT             4M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          4M
                NEXT             4M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN PSIM.IDM_MESSAGE_LOG.IDM_MESSAGE_LOG_ID IS 'A table used to hold processed HL7 messages. The message is tied to the correlation at this point.';

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

COMMENT ON COLUMN PSIM.IDM_MESSAGE_LOG.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN PSIM.IDM_MESSAGE_LOG.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN PSIM.IDM_MESSAGE_LOG.SENDER_CODE IS 'System or Application that sent the message.';

COMMENT ON COLUMN PSIM.IDM_MESSAGE_LOG.MESSAGE_TYPE_CODE IS 'Type of Message received for processing.';



CREATE INDEX PSIM.IDMMSGLOG_MSGLOG_FK_I ON PSIM.IDM_MESSAGE_LOG
(REFERENCE_IDM_MESSAGE_LOG_ID)
  TABLESPACE L_PSIMLOG_INDEX_01
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              INITIAL          512M
              NEXT             200M
              MINEXTENTS       2
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
LOCAL (  
  PARTITION IDMMSGLOG_P400
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          512M
                NEXT             200M
                MINEXTENTS       2
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.IDMMSGLOG_PK ON PSIM.IDM_MESSAGE_LOG
(IDM_MESSAGE_LOG_ID)
LOGGING
TABLESPACE L_PSIMLOG_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.IDMMSGLOG_PSPERCOR_FK_I ON PSIM.IDM_MESSAGE_LOG
(PERSON_CORRELATION_ID)
  TABLESPACE L_PSIMLOG_INDEX_01
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              INITIAL          512M
              NEXT             200M
              MINEXTENTS       2
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
LOCAL (  
  PARTITION IDMMSGLOG_P400
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          512M
                NEXT             200M
                MINEXTENTS       2
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;


CREATE INDEX PSIM.IDMMSGLOG_PS_MESSAGE_LOG_NU_I ON PSIM.IDM_MESSAGE_LOG
(MESSAGE_NUMBER)
  TABLESPACE L_PSIMLOG_INDEX_01
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              INITIAL          512M
              NEXT             200M
              MINEXTENTS       2
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOGGING
LOCAL (  
  PARTITION IDMMSGLOG_P400
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          512M
                NEXT             200M
                MINEXTENTS       2
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE L_PSIMLOG_INDEX_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          712M
                NEXT             200M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM IDM_MESSAGE_LOG FOR PSIM.IDM_MESSAGE_LOG;


ALTER TABLE PSIM.IDM_MESSAGE_LOG ADD (
  CONSTRAINT IDMMLOG_AVCON_MESSA_20
  CHECK (MESSAGE_DIRECTION_CODE IN ('O', 'I'))
  ENABLE VALIDATE);

ALTER TABLE PSIM.IDM_MESSAGE_LOG ADD (
  CONSTRAINT IDMMLOG_PSPERCOR_20_FK 
  FOREIGN KEY (PERSON_CORRELATION_ID) 
  REFERENCES PSIM.PS_PERSON_CORRELATION (PS_PERSON_CORRELATION_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.IDM_MESSAGE_LOG TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.IDM_MESSAGE_LOG TO PSIM_RW;
ALTER TABLE PSIM.EVENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE PSIM.EVENT CASCADE CONSTRAINTS;

CREATE TABLE PSIM.EVENT
(
  ID         NUMBER(9)                          NOT NULL,
  ISPENDING  CHAR(1 BYTE)                       NOT NULL,
  UPDATED    DATE,
  QUEUE_ID   NUMBER(9)                          NOT NULL,
  VERSION    NUMBER(9)                          NOT NULL,
  CREATED    DATE                               NOT NULL,
  CREATEDBY  VARCHAR2(30 BYTE)                  NOT NULL,
  UPDATEDBY  VARCHAR2(30 BYTE),
  DATA       LONG RAW                           NOT NULL,
  EVENT_KEY  VARCHAR2(100 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.EVENT IS 'Used to hold HL7 messages before processing by the Dataman framework.';

COMMENT ON COLUMN PSIM.EVENT.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 PSIM.EVENT.CREATED IS 'Date the record was created.';

COMMENT ON COLUMN PSIM.EVENT.CREATEDBY IS 'Account/user ID that created the record';

COMMENT ON COLUMN PSIM.EVENT.UPDATEDBY IS 'Account/user ID for this modification to the record.';



CREATE INDEX PSIM.EVT_CREATEDBY_NU_I ON PSIM.EVENT
(CREATEDBY)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.EVT_EVENTKEY_NU_I ON PSIM.EVENT
(EVENT_KEY)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.EVT_ISPENDING_QUEUE_NU_I ON PSIM.EVENT
(QUEUE_ID, ISPENDING)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PSIM.EVT_PK ON PSIM.EVENT
(ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE INDEX PSIM.EVT_QUE_FK_I ON PSIM.EVENT
(QUEUE_ID)
LOGGING
TABLESPACE S_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE PUBLIC SYNONYM EVENT FOR PSIM.EVENT;


ALTER TABLE PSIM.EVENT ADD (
  CONSTRAINT EVT_PK
  PRIMARY KEY
  (ID)
  USING INDEX PSIM.EVT_PK
  ENABLE VALIDATE);

ALTER TABLE PSIM.EVENT ADD (
  CONSTRAINT EVT_QUE_FK 
  FOREIGN KEY (QUEUE_ID) 
  REFERENCES PSIM.QUEUE (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

GRANT SELECT ON PSIM.EVENT TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.EVENT TO PSIM_RW;
DROP TABLE PSIM.DATAMAN_HIGH_KEY CASCADE CONSTRAINTS;

CREATE TABLE PSIM.DATAMAN_HIGH_KEY
(
  NEXT_INT  NUMBER(20)
)
TABLESPACE S_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.DATAMAN_HIGH_KEY IS 'Used by the Dataman framework to simulate an Oracle sequence on non-Oracle databases. This table is only used with Event and Queue tables. `;';



CREATE OR REPLACE PUBLIC SYNONYM DATAMAN_HIGH_KEY FOR PSIM.DATAMAN_HIGH_KEY;


GRANT SELECT ON PSIM.DATAMAN_HIGH_KEY TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.DATAMAN_HIGH_KEY TO PSIM_RW;
DROP TABLE PSIM.ADR_PROCESS_LOG CASCADE CONSTRAINTS;

CREATE TABLE PSIM.ADR_PROCESS_LOG
(
  ADR_PROCESS_LOG_ID   NUMBER(20)               NOT NULL,
  PROCESS_NAME         VARCHAR2(60 BYTE),
  PROCESS_START_DATE   DATE,
  PROCESS_END_DATE     DATE,
  PROCESS_STATUS_CODE  VARCHAR2(50 BYTE),
  PROCESSED_COUNT      NUMBER(10),
  ERROR_COUNT          NUMBER(10)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.ADR_PROCESS_LOG IS 'Stored Procedure - Table used to capture scheduled run of the batch process on RPT_PSIM_TRAITS';

COMMENT ON COLUMN PSIM.ADR_PROCESS_LOG.PROCESS_START_DATE IS 'BR2312 Process Started Date/Time: Holds the date/time that the process to recalculate the enrollment statuses based on the new EGT setting started.';

COMMENT ON COLUMN PSIM.ADR_PROCESS_LOG.PROCESS_END_DATE IS 'BR2316 Process Ended Date/Time is the Date/Time that the process to recalculate the enrollment statuses based on the new EGT setting ended.';



CREATE OR REPLACE PUBLIC SYNONYM ADR_PROCESS_LOG FOR PSIM.ADR_PROCESS_LOG;


GRANT SELECT ON PSIM.ADR_PROCESS_LOG TO EE_RPT_R;

GRANT SELECT ON PSIM.ADR_PROCESS_LOG TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.ADR_PROCESS_LOG TO PSIM_RW;
DROP TABLE PSIM.ADR_ERROR_MESSAGES CASCADE CONSTRAINTS;

CREATE TABLE PSIM.ADR_ERROR_MESSAGES
(
  PRIMARY_KEY_VALUE     VARCHAR2(40 BYTE),
  ADR_ERROR_MESSAGE_ID  NUMBER(20)              NOT NULL,
  PROCESS_NAME          VARCHAR2(60 BYTE),
  ERROR_CODE            VARCHAR2(50 BYTE),
  ERROR_DATE            DATE,
  ERROR_MESSAGE_TEXT    VARCHAR2(250 BYTE),
  ERROR_DESCRIPTION     VARCHAR2(2000 BYTE)
)
TABLESPACE M_DATA_01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE PSIM.ADR_ERROR_MESSAGES IS 'Used by the Dataman framework to simulate an Oracle sequence on non-Oracle databases. This table is only used with Event and Queue tables.
  2  Stored Procedure- Table used to capture error messages on RPT_PSIM_TRAITS and for future use ';



CREATE OR REPLACE PUBLIC SYNONYM ADR_ERROR_MESSAGES FOR PSIM.ADR_ERROR_MESSAGES;


GRANT SELECT ON PSIM.ADR_ERROR_MESSAGES TO EE_RPT_R;

GRANT SELECT ON PSIM.ADR_ERROR_MESSAGES TO PSIM_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON PSIM.ADR_ERROR_MESSAGES TO PSIM_RW;
