ALTER TABLE VOA.AUTHENTICATION_LEVEL
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.AUTHENTICATION_LEVEL CASCADE CONSTRAINTS;

CREATE TABLE VOA.AUTHENTICATION_LEVEL
(
  CODE                     VARCHAR2(10 BYTE)    NOT NULL,
  NAME                     VARCHAR2(30 BYTE)    NOT NULL,
  DESCRIPTION              VARCHAR2(250 BYTE)   NOT NULL,
  AUTHENTICATION_LEVEL_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(10)           NOT NULL
)
TABLESPACE S_VOA_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 VOA.AUTHENTICATION_LEVEL IS 'Lookup table containing Authentication Level codes';

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL.AUTHENTICATION_LEVEL_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 VOA.AUTHENTICATION_LEVEL.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX VOA.ATHLVLCOD_UK ON VOA.AUTHENTICATION_LEVEL
(CODE)
LOGGING
TABLESPACE S_VOA_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 VOA.ATHLVLNAM_UK ON VOA.AUTHENTICATION_LEVEL
(NAME)
LOGGING
TABLESPACE S_VOA_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 VOA.ATHLVL_PK ON VOA.AUTHENTICATION_LEVEL
(AUTHENTICATION_LEVEL_ID)
LOGGING
TABLESPACE S_VOA_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 TRIGGER VOA.AUTHENTICATION_LEVEL_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON VOA.AUTHENTICATION_LEVEL
  REFERENCING OLD AS OLD NEW AS NEW
  for each row
declare
   	mDML char(1);
begin
  if DELETING then
	INSERT INTO AUTHENTICATION_LEVEL_H (
      AUTHENTICATION_LVL_H_ID,
      AUTHENTICATION_LEVEL_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      AUTHENTICATION_LEVEL_H_S.nextval
     ,:OLD.AUTHENTICATION_LEVEL_ID,
      :OLD.CODE,
      :OLD.NAME,
      :OLD.DESCRIPTION,
      :OLD.RECORD_CREATED_BY,
      :OLD.RECORD_CREATED_DATE,
      :OLD.RECORD_MODIFIED_BY,
      :OLD.RECORD_MODIFIED_DATE,
      :OLD.RECORD_MODIFIED_COUNT,
      'D',
      SYSDATE );
    else
	if UPDATING then
		mDML := 'U';
	else
		mDML := 'I';
	end if;

	INSERT INTO AUTHENTICATION_LEVEL_H (
      AUTHENTICATION_LVL_H_ID,
      AUTHENTICATION_LEVEL_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      AUTHENTICATION_LEVEL_H_S.nextval,
      :NEW.AUTHENTICATION_LEVEL_ID,
      :NEW.CODE,
      :NEW.NAME,
      :NEW.DESCRIPTION,
      :NEW.RECORD_CREATED_BY,
      :NEW.RECORD_CREATED_DATE,
      :NEW.RECORD_MODIFIED_BY,
      :NEW.RECORD_MODIFIED_DATE,
      :NEW.RECORD_MODIFIED_COUNT,
      mDML,
      SYSDATE );
  end if;

end AUTHENTICATION_LEVEL_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM AUTHENTICATION_LEVEL FOR VOA.AUTHENTICATION_LEVEL;


ALTER TABLE VOA.AUTHENTICATION_LEVEL ADD (
  CONSTRAINT ATHLVL_PK
  PRIMARY KEY
  (AUTHENTICATION_LEVEL_ID)
  USING INDEX VOA.ATHLVL_PK
  ENABLE VALIDATE,
  CONSTRAINT ATHLVLCOD_UK
  UNIQUE (CODE)
  USING INDEX VOA.ATHLVLCOD_UK
  ENABLE VALIDATE,
  CONSTRAINT ATHLVLNAM_UK
  UNIQUE (NAME)
  USING INDEX VOA.ATHLVLNAM_UK
  ENABLE VALIDATE);

GRANT SELECT ON VOA.AUTHENTICATION_LEVEL TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.AUTHENTICATION_LEVEL TO VOA_RW;
ALTER TABLE VOA.AUTHENTICATION_LEVEL_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.AUTHENTICATION_LEVEL_H CASCADE CONSTRAINTS;

CREATE TABLE VOA.AUTHENTICATION_LEVEL_H
(
  CODE                     VARCHAR2(10 BYTE)    NOT NULL,
  NAME                     VARCHAR2(30 BYTE)    NOT NULL,
  DESCRIPTION              VARCHAR2(250 BYTE)   NOT NULL,
  AUTHENTICATION_LEVEL_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(10)           NOT NULL,
  HISTORY_CREATED_DATE     DATE                 NOT NULL,
  TRANSACTION_TYPE_CODE    VARCHAR2(1 BYTE)     NOT NULL,
  AUTHENTICATION_LVL_H_ID  NUMBER(20)           NOT NULL
)
TABLESPACE S_VOA_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 VOA.AUTHENTICATION_LEVEL_H IS 'Lookup table containing Authentication Level codes';

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL_H.AUTHENTICATION_LEVEL_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 VOA.AUTHENTICATION_LEVEL_H.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL_H.HISTORY_CREATED_DATE IS 'Date history record created.';

COMMENT ON COLUMN VOA.AUTHENTICATION_LEVEL_H.TRANSACTION_TYPE_CODE IS 'Whether this row was written because of a Create, Update, or Delete.';

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



CREATE UNIQUE INDEX VOA.ATHLVL_H_PK ON VOA.AUTHENTICATION_LEVEL_H
(AUTHENTICATION_LVL_H_ID)
LOGGING
TABLESPACE S_VOA_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 AUTHENTICATION_LEVEL_H FOR VOA.AUTHENTICATION_LEVEL_H;


ALTER TABLE VOA.AUTHENTICATION_LEVEL_H ADD (
  CONSTRAINT ATHLVL_H_PK
  PRIMARY KEY
  (AUTHENTICATION_LVL_H_ID)
  USING INDEX VOA.ATHLVL_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON VOA.AUTHENTICATION_LEVEL_H TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.AUTHENTICATION_LEVEL_H TO VOA_RW;
ALTER TABLE VOA.ENROLLMENT_FORM_TYPE
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.ENROLLMENT_FORM_TYPE CASCADE CONSTRAINTS;

CREATE TABLE VOA.ENROLLMENT_FORM_TYPE
(
  ENROLLMENT_FORM_TYPE_ID  NUMBER(20)           NOT NULL,
  CODE                     VARCHAR2(10 BYTE)    NOT NULL,
  NAME                     VARCHAR2(30 BYTE)    NOT NULL,
  DESCRIPTION              VARCHAR2(250 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_VOA_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 VOA.ENROLLMENT_FORM_TYPE IS 'Lookup table containing Form Type codes';

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE.ENROLLMENT_FORM_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 VOA.ENROLLMENT_FORM_TYPE.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX VOA.ENRFRMTYPCOD_UK ON VOA.ENROLLMENT_FORM_TYPE
(CODE)
LOGGING
TABLESPACE S_VOA_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 VOA.ENRFRMTYPNAM_UK ON VOA.ENROLLMENT_FORM_TYPE
(NAME)
LOGGING
TABLESPACE S_VOA_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 VOA.ENRFRMTYP_PK ON VOA.ENROLLMENT_FORM_TYPE
(ENROLLMENT_FORM_TYPE_ID)
LOGGING
TABLESPACE S_VOA_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 TRIGGER VOA.ENROLLMENT_FORM_TYPE_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON VOA.ENROLLMENT_FORM_TYPE
  REFERENCING OLD AS OLD NEW AS NEW
  for each row
declare
   	mDML char(1);
begin
  if DELETING then
	INSERT INTO ENROLLMENT_FORM_TYPE_H (
      ENROLLMENT_FORM_TYP_H_ID,
      ENROLLMENT_FORM_TYPE_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      ENROLLMENT_FORM_TYPE_H_S.nextval,
      :OLD.ENROLLMENT_FORM_TYPE_ID,
      :OLD.CODE,
      :OLD.NAME,
      :OLD.DESCRIPTION,
      :OLD.RECORD_CREATED_BY,
      :OLD.RECORD_CREATED_DATE,
      :OLD.RECORD_MODIFIED_BY,
      :OLD.RECORD_MODIFIED_DATE,
      :OLD.RECORD_MODIFIED_COUNT,
      'D',
      SYSDATE );
    else
	if UPDATING then
		mDML := 'U';
	else
		mDML := 'I';
	end if;

	INSERT INTO ENROLLMENT_FORM_TYPE_H (
      ENROLLMENT_FORM_TYP_H_ID,
      ENROLLMENT_FORM_TYPE_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      ENROLLMENT_FORM_TYPE_H_S.nextval,
      :NEW.ENROLLMENT_FORM_TYPE_ID,
      :NEW.CODE,
      :NEW.NAME,
      :NEW.DESCRIPTION,
      :NEW.RECORD_CREATED_BY,
      :NEW.RECORD_CREATED_DATE,
      :NEW.RECORD_MODIFIED_BY,
      :NEW.RECORD_MODIFIED_DATE,
      :NEW.RECORD_MODIFIED_COUNT,
      mDML,
      SYSDATE );
  end if;

end ENROLLMENT_FORM_TYPE_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM ENROLLMENT_FORM_TYPE FOR VOA.ENROLLMENT_FORM_TYPE;


ALTER TABLE VOA.ENROLLMENT_FORM_TYPE ADD (
  CONSTRAINT ENRFRMTYP_PK
  PRIMARY KEY
  (ENROLLMENT_FORM_TYPE_ID)
  USING INDEX VOA.ENRFRMTYP_PK
  ENABLE VALIDATE,
  CONSTRAINT ENRFRMTYPCOD_UK
  UNIQUE (CODE)
  USING INDEX VOA.ENRFRMTYPCOD_UK
  ENABLE VALIDATE,
  CONSTRAINT ENRFRMTYPNAM_UK
  UNIQUE (NAME)
  USING INDEX VOA.ENRFRMTYPNAM_UK
  ENABLE VALIDATE);

GRANT REFERENCES ON VOA.ENROLLMENT_FORM_TYPE TO ADR;

GRANT SELECT ON VOA.ENROLLMENT_FORM_TYPE TO BIZFLOW_R;

GRANT SELECT ON VOA.ENROLLMENT_FORM_TYPE TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.ENROLLMENT_FORM_TYPE TO VOA_RW;
ALTER TABLE VOA.ENROLLMENT_FORM_TYPE_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.ENROLLMENT_FORM_TYPE_H CASCADE CONSTRAINTS;

CREATE TABLE VOA.ENROLLMENT_FORM_TYPE_H
(
  ENROLLMENT_FORM_TYPE_ID   NUMBER(20)          NOT NULL,
  CODE                      VARCHAR2(10 BYTE)   NOT NULL,
  NAME                      VARCHAR2(30 BYTE)   NOT NULL,
  DESCRIPTION               VARCHAR2(250 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,
  HISTORY_CREATED_DATE      DATE                NOT NULL,
  TRANSACTION_TYPE_CODE     VARCHAR2(1 BYTE)    NOT NULL,
  ENROLLMENT_FORM_TYP_H_ID  NUMBER(20)          NOT NULL
)
TABLESPACE S_VOA_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 VOA.ENROLLMENT_FORM_TYPE_H IS 'Lookup table containing Form Type codes';

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE_H.ENROLLMENT_FORM_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 VOA.ENROLLMENT_FORM_TYPE_H.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE_H.HISTORY_CREATED_DATE IS 'Date history record created.';

COMMENT ON COLUMN VOA.ENROLLMENT_FORM_TYPE_H.TRANSACTION_TYPE_CODE IS 'Whether this row was written because of a Create, Update, or Delete.';

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



CREATE UNIQUE INDEX VOA.ENRFRMTYP_H_PK ON VOA.ENROLLMENT_FORM_TYPE_H
(ENROLLMENT_FORM_TYP_H_ID)
LOGGING
TABLESPACE S_VOA_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 ENROLLMENT_FORM_TYPE_H FOR VOA.ENROLLMENT_FORM_TYPE_H;


ALTER TABLE VOA.ENROLLMENT_FORM_TYPE_H ADD (
  CONSTRAINT ENRFRMTYP_H_PK
  PRIMARY KEY
  (ENROLLMENT_FORM_TYP_H_ID)
  USING INDEX VOA.ENRFRMTYP_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON VOA.ENROLLMENT_FORM_TYPE_H TO BIZFLOW_R;

GRANT SELECT ON VOA.ENROLLMENT_FORM_TYPE_H TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.ENROLLMENT_FORM_TYPE_H TO VOA_RW;
ALTER TABLE VOA.VOA_ENROLLMENT_APPLICATION
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_ENROLLMENT_APPLICATION CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_ENROLLMENT_APPLICATION
(
  VOA_ENROLLMENT_APP_ID         NUMBER(20)      NOT NULL,
  PERSON_ID                     NUMBER(20),
  VOA_ENROLLMENT_APP_STATUS_ID  NUMBER(20)      NOT NULL,
  ENROLLMENT_FORM_TYPE_ID       NUMBER(20)      NOT NULL,
  AUTHENTICATION_LEVEL_ID       NUMBER(20)      NOT NULL,
  STD_GENDER_ID                 NUMBER(20),
  RESPONSE_TIMESTAMP            TIMESTAMP(6),
  ERROR_TEXT                    VARCHAR2(2000 BYTE),
  FULLY_QUALIFIED_ID            VARCHAR2(65 BYTE),
  PREFERRED_FACILITY_ID         NUMBER(20),
  REQUEST_RECEIVED_TIMESTAMP    TIMESTAMP(6),
  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,
  APPLICANT_LAST_NAME           VARCHAR2(35 BYTE) NOT NULL,
  APPLICANT_FIRST_NAME          VARCHAR2(25 BYTE),
  APPLICANT_MIDDLE_NAME         VARCHAR2(25 BYTE),
  APPLICANT_DOB                 DATE            NOT NULL,
  APPLICANT_SSN                 VARCHAR2(30 BYTE) NOT NULL,
  VETERAN_IDENTIFIER_TYPE_ID    NUMBER(20),
  BF_PROCESSED                  NUMBER(1)
)
TABLESPACE M_VOA_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 VOA.VOA_ENROLLMENT_APPLICATION IS 'The VOA_ENROLLMENT_APPLICATION table contains the VOA enrollment application record containing information about the applicant, the forms submitted, and processing information. Foreign Keys present in table: PERSON_ID STD_ENROLLMENTAPPSTATUS_ID (The status of the form processing) STD_ENROLLMENTFORMTYPE_ID (The form type name of document) STD_AUTHENTICATIONLEVEL_ID (Assurance level of the document) VETERAN_IDENTIFIER_TYPE_ID (The veteran identify type)
On 11/1012 expected 11,500 rows to be added to table each month';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.VOA_ENROLLMENT_APP_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 VOA.VOA_ENROLLMENT_APPLICATION.PERSON_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 VOA.VOA_ENROLLMENT_APPLICATION.VOA_ENROLLMENT_APP_STATUS_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 VOA.VOA_ENROLLMENT_APPLICATION.ENROLLMENT_FORM_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 VOA.VOA_ENROLLMENT_APPLICATION.AUTHENTICATION_LEVEL_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 VOA.VOA_ENROLLMENT_APPLICATION.STD_GENDER_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 VOA.VOA_ENROLLMENT_APPLICATION.RESPONSE_TIMESTAMP IS 'Timestamp when the VOA response was sent.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.ERROR_TEXT IS 'Error text message(s) generated during processing of VOA message';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.PREFERRED_FACILITY_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 VOA.VOA_ENROLLMENT_APPLICATION.REQUEST_RECEIVED_TIMESTAMP IS 'Timestamp when the VOA request was received.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.APPLICANT_DOB IS 'Date of birth of the applicant.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION.APPLICANT_SSN IS 'Social Security Number of the applicant.';



CREATE INDEX VOA.VOAENRAPP_ATHLVL_FK_I ON VOA.VOA_ENROLLMENT_APPLICATION
(AUTHENTICATION_LEVEL_ID)
LOGGING
TABLESPACE M_VOA_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 VOA.VOAENRAPP_ENRFRMTYP_FK_I ON VOA.VOA_ENROLLMENT_APPLICATION
(ENROLLMENT_FORM_TYPE_ID)
LOGGING
TABLESPACE M_VOA_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 VOA.VOAENRAPP_PK ON VOA.VOA_ENROLLMENT_APPLICATION
(VOA_ENROLLMENT_APP_ID)
LOGGING
TABLESPACE S_VOA_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 VOA.VOAENRAPP_PRSN_FK_I ON VOA.VOA_ENROLLMENT_APPLICATION
(PERSON_ID)
LOGGING
TABLESPACE M_VOA_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 VOA.VOAENRAPP_VETIDTYP_FK_I ON VOA.VOA_ENROLLMENT_APPLICATION
(VETERAN_IDENTIFIER_TYPE_ID)
LOGGING
TABLESPACE S_VBR_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 VOA.VOAENRAPP_VOAENRAPPSTA_FK_I ON VOA.VOA_ENROLLMENT_APPLICATION
(VOA_ENROLLMENT_APP_STATUS_ID)
LOGGING
TABLESPACE M_VOA_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 VOA.VOA_ENROLL_APP_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON VOA.VOA_ENROLLMENT_APPLICATION
  REFERENCING OLD AS OLD NEW AS NEW
  for each row
declare
    mDML char(1);
begin
  if DELETING then
  INSERT INTO VOA_ENROLLMENT_APPLICATION_H (
      VOA_ENROLLMENT_APP_H_ID,
      VOA_ENROLLMENT_APP_ID,
      PERSON_ID,
      VOA_ENROLLMENT_APP_STATUS_ID,
      ENROLLMENT_FORM_TYPE_ID,
      AUTHENTICATION_LEVEL_ID,
      VETERAN_IDENTIFIER_TYPE_ID,
      APPLICANT_LAST_NAME,
      APPLICANT_FIRST_NAME,
      APPLICANT_MIDDLE_NAME,
      APPLICANT_DOB,
      APPLICANT_SSN,
      STD_GENDER_ID,
      RESPONSE_TIMESTAMP,
      ERROR_TEXT,
      FULLY_QUALIFIED_ID,
      PREFERRED_FACILITY_ID,
      REQUEST_RECEIVED_TIMESTAMP,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE,
      BF_PROCESSED)
    VALUES (
      VOA_ENROLLMENT_APPLICATION_H_S.nextval,
      :OLD.VOA_ENROLLMENT_APP_ID,
      :OLD.PERSON_ID,
      :OLD.VOA_ENROLLMENT_APP_STATUS_ID,
      :OLD.ENROLLMENT_FORM_TYPE_ID,
      :OLD.AUTHENTICATION_LEVEL_ID,
      :OLD.VETERAN_IDENTIFIER_TYPE_ID,
      :OLD.APPLICANT_LAST_NAME,
      :OLD.APPLICANT_FIRST_NAME,
      :OLD.APPLICANT_MIDDLE_NAME,
      :OLD.APPLICANT_DOB,
      :OLD.APPLICANT_SSN,
      :OLD.STD_GENDER_ID,
      :OLD.RESPONSE_TIMESTAMP,
      :OLD.ERROR_TEXT,
      :OLD.FULLY_QUALIFIED_ID,
      :OLD.PREFERRED_FACILITY_ID,
      :OLD.REQUEST_RECEIVED_TIMESTAMP,
      :OLD.RECORD_CREATED_BY,
      :OLD.RECORD_CREATED_DATE,
      :OLD.RECORD_MODIFIED_BY,
      :OLD.RECORD_MODIFIED_DATE,
      :OLD.RECORD_MODIFIED_COUNT,
      'D',
      SYSDATE,
      :OLD.BF_PROCESSED);
    else
  if UPDATING then
    mDML := 'U';
  else
    mDML := 'I';
  end if;

  INSERT INTO VOA_ENROLLMENT_APPLICATION_H (
      VOA_ENROLLMENT_APP_H_ID,
      VOA_ENROLLMENT_APP_ID,
      PERSON_ID,
      VOA_ENROLLMENT_APP_STATUS_ID,
      ENROLLMENT_FORM_TYPE_ID,
      AUTHENTICATION_LEVEL_ID,
      VETERAN_IDENTIFIER_TYPE_ID,
      APPLICANT_LAST_NAME,
      APPLICANT_FIRST_NAME,
      APPLICANT_MIDDLE_NAME,
      APPLICANT_DOB,
      APPLICANT_SSN,
      STD_GENDER_ID,
      RESPONSE_TIMESTAMP,
      ERROR_TEXT,
      FULLY_QUALIFIED_ID,
      PREFERRED_FACILITY_ID,
      REQUEST_RECEIVED_TIMESTAMP,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE,
      BF_PROCESSED)
    VALUES (
      VOA_ENROLLMENT_APPLICATION_H_S.nextval,
      :NEW.VOA_ENROLLMENT_APP_ID,
      :NEW.PERSON_ID,
      :NEW.VOA_ENROLLMENT_APP_STATUS_ID,
      :NEW.ENROLLMENT_FORM_TYPE_ID,
      :NEW.AUTHENTICATION_LEVEL_ID,
      :NEW.VETERAN_IDENTIFIER_TYPE_ID,
      :NEW.APPLICANT_LAST_NAME,
      :NEW.APPLICANT_FIRST_NAME,
      :NEW.APPLICANT_MIDDLE_NAME,
      :NEW.APPLICANT_DOB,
      :NEW.APPLICANT_SSN,
      :NEW.STD_GENDER_ID,
      :NEW.RESPONSE_TIMESTAMP,
      :NEW.ERROR_TEXT,
      :NEW.FULLY_QUALIFIED_ID,
      :NEW.PREFERRED_FACILITY_ID,
      :NEW.REQUEST_RECEIVED_TIMESTAMP,
      :NEW.RECORD_CREATED_BY,
      :NEW.RECORD_CREATED_DATE,
      :NEW.RECORD_MODIFIED_BY,
      :NEW.RECORD_MODIFIED_DATE,
      :NEW.RECORD_MODIFIED_COUNT,
      mDML,
      SYSDATE,
      :NEW.BF_PROCESSED);
  end if;

end VOA_ENROLL_APP_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM VOA_ENROLLMENT_APPLICATION FOR VOA.VOA_ENROLLMENT_APPLICATION;


ALTER TABLE VOA.VOA_ENROLLMENT_APPLICATION ADD (
  CONSTRAINT VOAENRAPP_PK
  PRIMARY KEY
  (VOA_ENROLLMENT_APP_ID)
  USING INDEX VOA.VOAENRAPP_PK
  ENABLE VALIDATE);

ALTER TABLE VOA.VOA_ENROLLMENT_APPLICATION ADD (
  CONSTRAINT VOAENRAPP_ATHLVL_FK 
  FOREIGN KEY (AUTHENTICATION_LEVEL_ID) 
  REFERENCES VOA.AUTHENTICATION_LEVEL (AUTHENTICATION_LEVEL_ID)
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPP_ENRFRMTYP_FK 
  FOREIGN KEY (ENROLLMENT_FORM_TYPE_ID) 
  REFERENCES VOA.ENROLLMENT_FORM_TYPE (ENROLLMENT_FORM_TYPE_ID)
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPP_PRSN_FK 
  FOREIGN KEY (PERSON_ID) 
  REFERENCES ADR.PERSON (PERSON_ID)
  ON DELETE SET NULL
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPP_VETIDTYP_FK 
  FOREIGN KEY (VETERAN_IDENTIFIER_TYPE_ID) 
  REFERENCES VBR.VETERAN_IDENTIFIER_TYPE (VETERAN_IDENTIFIER_TYPE_ID)
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPP_VOAENRAPPSTA_FK 
  FOREIGN KEY (VOA_ENROLLMENT_APP_STATUS_ID) 
  REFERENCES VOA.VOA_ENROLLMENT_APP_STATUS (VOA_ENROLLMENT_APP_STATUS_ID)
  ENABLE VALIDATE);

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APPLICATION TO BIZFLOW_RW;

GRANT REFERENCES ON VOA.VOA_ENROLLMENT_APPLICATION TO LOBR;

GRANT SELECT ON VOA.VOA_ENROLLMENT_APPLICATION TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APPLICATION TO VOA_RW;
ALTER TABLE VOA.VOA_ENROLLMENT_APPLICATION_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_ENROLLMENT_APPLICATION_H CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_ENROLLMENT_APPLICATION_H
(
  VOA_ENROLLMENT_APP_ID         NUMBER(20)      NOT NULL,
  PERSON_ID                     NUMBER(20),
  VOA_ENROLLMENT_APP_STATUS_ID  NUMBER(20)      NOT NULL,
  ENROLLMENT_FORM_TYPE_ID       NUMBER(20)      NOT NULL,
  AUTHENTICATION_LEVEL_ID       NUMBER(20)      NOT NULL,
  VETERAN_IDENTIFIER_TYPE_ID    NUMBER(20),
  STD_GENDER_ID                 NUMBER(20),
  RESPONSE_TIMESTAMP            TIMESTAMP(6),
  ERROR_TEXT                    VARCHAR2(2000 BYTE),
  FULLY_QUALIFIED_ID            VARCHAR2(65 BYTE),
  PREFERRED_FACILITY_ID         NUMBER(20),
  REQUEST_RECEIVED_TIMESTAMP    TIMESTAMP(6),
  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,
  APPLICANT_LAST_NAME           VARCHAR2(35 BYTE) NOT NULL,
  APPLICANT_FIRST_NAME          VARCHAR2(25 BYTE),
  APPLICANT_MIDDLE_NAME         VARCHAR2(25 BYTE),
  APPLICANT_DOB                 DATE            NOT NULL,
  APPLICANT_SSN                 VARCHAR2(30 BYTE) NOT NULL,
  HISTORY_CREATED_DATE          DATE            NOT NULL,
  TRANSACTION_TYPE_CODE         VARCHAR2(1 BYTE) NOT NULL,
  VOA_ENROLLMENT_APP_H_ID       NUMBER(20)      NOT NULL,
  BF_PROCESSED                  NUMBER(1)
)
TABLESPACE M_VOA_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 VOA.VOA_ENROLLMENT_APPLICATION_H IS 'History table for the VOA_ENROLLMENT APP table.  Contains all changes made to the VOA_ENROLLMENT APP table.  Records inserted into the table via a trigger.
On 11/1012 expected 11,500 rows to be added to table each month';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.VOA_ENROLLMENT_APP_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 VOA.VOA_ENROLLMENT_APPLICATION_H.VOA_ENROLLMENT_APP_STATUS_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 VOA.VOA_ENROLLMENT_APPLICATION_H.ENROLLMENT_FORM_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 VOA.VOA_ENROLLMENT_APPLICATION_H.AUTHENTICATION_LEVEL_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 VOA.VOA_ENROLLMENT_APPLICATION_H.VETERAN_IDENTIFIER_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 VOA.VOA_ENROLLMENT_APPLICATION_H.STD_GENDER_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 VOA.VOA_ENROLLMENT_APPLICATION_H.RESPONSE_TIMESTAMP IS 'Timestamp when the VOA response was sent.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.ERROR_TEXT IS 'Error text message(s) generated during processing of VOA message';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.PREFERRED_FACILITY_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 VOA.VOA_ENROLLMENT_APPLICATION_H.REQUEST_RECEIVED_TIMESTAMP IS 'Timestamp when the VOA request was received.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.APPLICANT_DOB IS 'Date of birth of the applicant.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.APPLICANT_SSN IS 'Social Security Number of the applicant.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.HISTORY_CREATED_DATE IS 'Date history record created.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APPLICATION_H.TRANSACTION_TYPE_CODE IS 'Whether this row was written because of a Create, Update, or Delete.';

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



CREATE UNIQUE INDEX VOA.VOAENRAPP_H_PK ON VOA.VOA_ENROLLMENT_APPLICATION_H
(VOA_ENROLLMENT_APP_H_ID)
LOGGING
TABLESPACE S_VOA_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 VOA_ENROLLMENT_APPLICATION_H FOR VOA.VOA_ENROLLMENT_APPLICATION_H;


ALTER TABLE VOA.VOA_ENROLLMENT_APPLICATION_H ADD (
  CONSTRAINT VOAENRAPP_H_PK
  PRIMARY KEY
  (VOA_ENROLLMENT_APP_H_ID)
  USING INDEX VOA.VOAENRAPP_H_PK
  ENABLE VALIDATE);

GRANT INSERT, SELECT ON VOA.VOA_ENROLLMENT_APPLICATION_H TO BIZFLOW_RW;

GRANT SELECT ON VOA.VOA_ENROLLMENT_APPLICATION_H TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APPLICATION_H TO VOA_RW;
ALTER TABLE VOA.VOA_ENROLLMENT_APP_DATA
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_ENROLLMENT_APP_DATA CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_ENROLLMENT_APP_DATA
(
  VOA_ENROLLMENT_APP_DATA_ID  NUMBER(20)        NOT NULL,
  APPLICATION_DATA            CLOB              NOT NULL,
  VOA_ENROLLMENT_APP_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(10)        NOT NULL
)
LOB (APPLICATION_DATA) STORE AS SECUREFILE (
  TABLESPACE  L_VOA_DATA_01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  DEDUPLICATE
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          128M
                  NEXT             128M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE M_VOA_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 VOA.VOA_ENROLLMENT_APP_DATA IS 'The VOA_ENROLLMENT_APP_DATA table contain the VOA data in CLOB format that was sent to be processed.
On 11/1012 expected 11,500 rows to be added to table each month The size of data in the application_data field will be similar to the size of data in the adr aap.application_in_process_id.application_context_xml field.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_DATA.VOA_ENROLLMENT_APP_DATA_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 VOA.VOA_ENROLLMENT_APP_DATA.VOA_ENROLLMENT_APP_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 VOA.VOA_ENROLLMENT_APP_DATA.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_DATA.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_DATA.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX VOA.VOAENRAPPDATA_PK ON VOA.VOA_ENROLLMENT_APP_DATA
(VOA_ENROLLMENT_APP_DATA_ID)
LOGGING
TABLESPACE S_VOA_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 VOA.VOAENRAPPDAT_VOAENRAPP_FK_I ON VOA.VOA_ENROLLMENT_APP_DATA
(VOA_ENROLLMENT_APP_ID)
LOGGING
TABLESPACE M_VOA_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 VOA_ENROLLMENT_APP_DATA FOR VOA.VOA_ENROLLMENT_APP_DATA;


ALTER TABLE VOA.VOA_ENROLLMENT_APP_DATA ADD (
  CONSTRAINT VOAENRAPPDATA_PK
  PRIMARY KEY
  (VOA_ENROLLMENT_APP_DATA_ID)
  USING INDEX VOA.VOAENRAPPDATA_PK
  ENABLE VALIDATE);

ALTER TABLE VOA.VOA_ENROLLMENT_APP_DATA ADD (
  CONSTRAINT VOAENRAPPDAT_VOAENRAPP_FK 
  FOREIGN KEY (VOA_ENROLLMENT_APP_ID) 
  REFERENCES VOA.VOA_ENROLLMENT_APPLICATION (VOA_ENROLLMENT_APP_ID)
  ENABLE VALIDATE);

GRANT SELECT ON VOA.VOA_ENROLLMENT_APP_DATA TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APP_DATA TO VOA_RW;
ALTER TABLE VOA.VOA_ENROLLMENT_APP_STATUS
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_ENROLLMENT_APP_STATUS CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_ENROLLMENT_APP_STATUS
(
  VOA_ENROLLMENT_APP_STATUS_ID  NUMBER(20)      NOT NULL,
  DESCRIPTION                   VARCHAR2(250 BYTE) NOT NULL,
  NAME                          VARCHAR2(30 BYTE) NOT NULL,
  CODE                          VARCHAR2(10 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_VOA_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 VOA.VOA_ENROLLMENT_APP_STATUS IS 'Lookup table containing VOA Application Status codes.
This is a VOA owned table and different from the ADRREF.STD_ENROLL_APPLICATION_STATUS.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS.VOA_ENROLLMENT_APP_STATUS_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 VOA.VOA_ENROLLMENT_APP_STATUS.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX VOA.VOAENRAPPSTATCOD_UK ON VOA.VOA_ENROLLMENT_APP_STATUS
(CODE)
LOGGING
TABLESPACE S_VOA_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 VOA.VOAENRAPPSTATNAM_UK ON VOA.VOA_ENROLLMENT_APP_STATUS
(NAME)
LOGGING
TABLESPACE S_VOA_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 VOA.VOAENRAPPSTAT_PK ON VOA.VOA_ENROLLMENT_APP_STATUS
(VOA_ENROLLMENT_APP_STATUS_ID)
LOGGING
TABLESPACE S_VOA_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 TRIGGER VOA.VOA_ENROLLMENT_APP_STATUS_TRGR
  AFTER DELETE OR INSERT OR UPDATE
  ON VOA.VOA_ENROLLMENT_APP_STATUS
  REFERENCING OLD AS OLD NEW AS NEW
  for each row
declare
   	mDML char(1);
begin
  if DELETING then
	INSERT INTO VOA_ENROLLMENT_APP_STATUS_H (
      VOA_ENROLLMENT_APP_STAT_H_ID,
      VOA_ENROLLMENT_APP_STATUS_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      VOA_ENROLLMENT_APP_STATUS_H_S.nextval,
      :OLD.VOA_ENROLLMENT_APP_STATUS_ID,
      :OLD.CODE,
      :OLD.NAME,
      :OLD.DESCRIPTION,
      :OLD.RECORD_CREATED_BY,
      :OLD.RECORD_CREATED_DATE,
      :OLD.RECORD_MODIFIED_BY,
      :OLD.RECORD_MODIFIED_DATE,
      :OLD.RECORD_MODIFIED_COUNT,
      'D',
      SYSDATE );
    else
	if UPDATING then
		mDML := 'U';
	else
		mDML := 'I';
	end if;

	INSERT INTO VOA_ENROLLMENT_APP_STATUS_H (
      VOA_ENROLLMENT_APP_STAT_H_ID,
      VOA_ENROLLMENT_APP_STATUS_ID,
      CODE,
      NAME,
      DESCRIPTION,
      RECORD_CREATED_BY,
      RECORD_CREATED_DATE,
      RECORD_MODIFIED_BY,
      RECORD_MODIFIED_DATE,
      RECORD_MODIFIED_COUNT,
      TRANSACTION_TYPE_CODE,
      HISTORY_CREATED_DATE )
    VALUES (
      VOA_ENROLLMENT_APP_STATUS_H_S.nextval,
      :NEW.VOA_ENROLLMENT_APP_STATUS_ID,
      :NEW.CODE,
      :NEW.NAME,
      :NEW.DESCRIPTION,
      :NEW.RECORD_CREATED_BY,
      :NEW.RECORD_CREATED_DATE,
      :NEW.RECORD_MODIFIED_BY,
      :NEW.RECORD_MODIFIED_DATE,
      :NEW.RECORD_MODIFIED_COUNT,
      mDML,
      SYSDATE );
  end if;

end VOA_ENROLLMENT_APP_STATUS_TRGR;
/


CREATE OR REPLACE PUBLIC SYNONYM VOA_ENROLLMENT_APP_STATUS FOR VOA.VOA_ENROLLMENT_APP_STATUS;


ALTER TABLE VOA.VOA_ENROLLMENT_APP_STATUS ADD (
  CONSTRAINT VOAENRAPPSTAT_PK
  PRIMARY KEY
  (VOA_ENROLLMENT_APP_STATUS_ID)
  USING INDEX VOA.VOAENRAPPSTAT_PK
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPPSTATCOD_UK
  UNIQUE (CODE)
  USING INDEX VOA.VOAENRAPPSTATCOD_UK
  ENABLE VALIDATE,
  CONSTRAINT VOAENRAPPSTATNAM_UK
  UNIQUE (NAME)
  USING INDEX VOA.VOAENRAPPSTATNAM_UK
  ENABLE VALIDATE);

GRANT SELECT ON VOA.VOA_ENROLLMENT_APP_STATUS TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APP_STATUS TO VOA_RW;
ALTER TABLE VOA.VOA_ENROLLMENT_APP_STATUS_H
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_ENROLLMENT_APP_STATUS_H CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_ENROLLMENT_APP_STATUS_H
(
  VOA_ENROLLMENT_APP_STATUS_ID  NUMBER(20)      NOT NULL,
  DESCRIPTION                   VARCHAR2(250 BYTE) NOT NULL,
  NAME                          VARCHAR2(30 BYTE) NOT NULL,
  CODE                          VARCHAR2(10 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,
  HISTORY_CREATED_DATE          DATE            NOT NULL,
  TRANSACTION_TYPE_CODE         VARCHAR2(1 BYTE) NOT NULL,
  VOA_ENROLLMENT_APP_STAT_H_ID  NUMBER(20)      NOT NULL
)
TABLESPACE S_VOA_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 VOA.VOA_ENROLLMENT_APP_STATUS_H IS 'Lookup table containing VOA Application Status codes.
This is a VOA owned table and different from the ADRREF.STD_ENROLL_APPLICATION_STATUS.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS_H.VOA_ENROLLMENT_APP_STATUS_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 VOA.VOA_ENROLLMENT_APP_STATUS_H.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS_H.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS_H.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS_H.HISTORY_CREATED_DATE IS 'Date history record created.';

COMMENT ON COLUMN VOA.VOA_ENROLLMENT_APP_STATUS_H.TRANSACTION_TYPE_CODE IS 'Whether this row was written because of a Create, Update, or Delete.';

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



CREATE UNIQUE INDEX VOA.VOAENRAPPSTAT_H_PK ON VOA.VOA_ENROLLMENT_APP_STATUS_H
(VOA_ENROLLMENT_APP_STAT_H_ID)
LOGGING
TABLESPACE S_VOA_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 VOA_ENROLLMENT_APP_STATUS_H FOR VOA.VOA_ENROLLMENT_APP_STATUS_H;


ALTER TABLE VOA.VOA_ENROLLMENT_APP_STATUS_H ADD (
  CONSTRAINT VOAENRAPPSTAT_H_PK
  PRIMARY KEY
  (VOA_ENROLLMENT_APP_STAT_H_ID)
  USING INDEX VOA.VOAENRAPPSTAT_H_PK
  ENABLE VALIDATE);

GRANT SELECT ON VOA.VOA_ENROLLMENT_APP_STATUS_H TO VOA_R;

GRANT INSERT, SELECT, UPDATE ON VOA.VOA_ENROLLMENT_APP_STATUS_H TO VOA_RW;
ALTER TABLE VOA.VOA_RESUBMISSION
 DROP PRIMARY KEY CASCADE;

DROP TABLE VOA.VOA_RESUBMISSION CASCADE CONSTRAINTS;

CREATE TABLE VOA.VOA_RESUBMISSION
(
  VOA_RESUBMISSION_ID       NUMBER(20)          NOT NULL,
  VOA_ENROLLMENT_APP_ID     NUMBER(20)          NOT NULL,
  PENDING_APPLICATION_TEXT  CLOB,
  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
)
LOB (PENDING_APPLICATION_TEXT) STORE AS SECUREFILE (
  TABLESPACE  L_VOA_DATA_01
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  DEDUPLICATE
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          128M
                  NEXT             128M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 ))
TABLESPACE M_VOA_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 VOA.VOA_RESUBMISSION IS 'While processing the application form submitted by the user, some scenarios require manual intervention by HEC users. In these scenarios, form data in progress is saved to continue processing once the issue has been manually resolved  by the HEC user.';

COMMENT ON COLUMN VOA.VOA_RESUBMISSION.VOA_RESUBMISSION_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 VOA.VOA_RESUBMISSION.VOA_ENROLLMENT_APP_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 VOA.VOA_RESUBMISSION.PENDING_APPLICATION_TEXT IS ' Once the form is successfully processed, we can delete the CLOB in order to avoid unnecessary storage.';

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

COMMENT ON COLUMN VOA.VOA_RESUBMISSION.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN VOA.VOA_RESUBMISSION.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX VOA.VOARSB_PK ON VOA.VOA_RESUBMISSION
(VOA_RESUBMISSION_ID)
LOGGING
TABLESPACE L_VOA_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 VOA.VOARSB_VOAENRAPP_FK_I ON VOA.VOA_RESUBMISSION
(VOA_ENROLLMENT_APP_ID)
LOGGING
TABLESPACE L_VOA_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 VOA_RESUBMISSION FOR VOA.VOA_RESUBMISSION;


ALTER TABLE VOA.VOA_RESUBMISSION ADD (
  CONSTRAINT VOARSB_PK
  PRIMARY KEY
  (VOA_RESUBMISSION_ID)
  USING INDEX VOA.VOARSB_PK
  ENABLE VALIDATE);

ALTER TABLE VOA.VOA_RESUBMISSION ADD (
  CONSTRAINT VOARSB_VOAENRAPP_FK 
  FOREIGN KEY (VOA_ENROLLMENT_APP_ID) 
  REFERENCES VOA.VOA_ENROLLMENT_APPLICATION (VOA_ENROLLMENT_APP_ID)
  DEFERRABLE INITIALLY DEFERRED
  ENABLE VALIDATE);

GRANT SELECT ON VOA.VOA_RESUBMISSION TO VOA_R;

GRANT DELETE, INSERT, SELECT, UPDATE ON VOA.VOA_RESUBMISSION TO VOA_RW;
