ALTER TABLE LOBR.FILE_IMAGE
 DROP PRIMARY KEY CASCADE;

DROP TABLE LOBR.FILE_IMAGE CASCADE CONSTRAINTS;

CREATE TABLE LOBR.FILE_IMAGE
(
  FILE_IMAGE_ID          NUMBER(20)             NOT NULL,
  FILE_ATTACHMENT_ID     NUMBER(20)             NOT NULL,
  FILE_IMAGE             BLOB                   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 (FILE_IMAGE) STORE AS (
  TABLESPACE  L_LOBR_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_LOBR_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 LOBR.FILE_IMAGE IS 'On 11/2012 the average data stored in the file_image field is 2 MB and each applicant will have at most 5 documents.
Didnt know how many documents will be stored each month.';

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

COMMENT ON COLUMN LOBR.FILE_IMAGE.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN LOBR.FILE_IMAGE.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX LOBR.FILEIMG_FILEATCH_FK_I ON LOBR.FILE_IMAGE
(FILE_ATTACHMENT_ID)
LOGGING
TABLESPACE L_LOBR_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 UNIQUE INDEX LOBR.FILEIMG_PK ON LOBR.FILE_IMAGE
(FILE_IMAGE_ID)
LOGGING
TABLESPACE L_LOBR_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 FILE_IMAGE FOR LOBR.FILE_IMAGE;


ALTER TABLE LOBR.FILE_IMAGE ADD (
  CONSTRAINT FILEIMG_PK
  PRIMARY KEY
  (FILE_IMAGE_ID)
  USING INDEX LOBR.FILEIMG_PK
  ENABLE VALIDATE);

ALTER TABLE LOBR.FILE_IMAGE ADD (
  CONSTRAINT FILEIMG_FILEATCH_FK 
  FOREIGN KEY (FILE_ATTACHMENT_ID) 
  REFERENCES LOBR.FILE_ATTACHMENT (FILE_ATTACHMENT_ID)
  ENABLE VALIDATE);

GRANT SELECT ON LOBR.FILE_IMAGE TO LOBR_R;

GRANT INSERT, SELECT, UPDATE ON LOBR.FILE_IMAGE TO LOBR_RW;
ALTER TABLE LOBR.FILE_ATTACHMENT_DETAIL
 DROP PRIMARY KEY CASCADE;

DROP TABLE LOBR.FILE_ATTACHMENT_DETAIL CASCADE CONSTRAINTS;

CREATE TABLE LOBR.FILE_ATTACHMENT_DETAIL
(
  FILE_ATTACHMENT_DETAIL_ID  NUMBER(20)         NOT NULL,
  FILE_ATTACHMENT_ID         NUMBER(20)         NOT NULL,
  STD_ELIGDOCTYPE_ID         NUMBER(20),
  RECORD_CREATED_BY          VARCHAR2(30 BYTE)  NOT NULL,
  RECORD_CREATED_DATE        DATE               NOT NULL,
  RECORD_MODIFIED_BY         VARCHAR2(30 BYTE)  NOT NULL,
  RECORD_MODIFIED_DATE       DATE               NOT NULL,
  RECORD_MODIFIED_COUNT      NUMBER(10)         NOT NULL
)
TABLESPACE L_LOBR_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 COLUMN LOBR.FILE_ATTACHMENT_DETAIL.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN LOBR.FILE_ATTACHMENT_DETAIL.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN LOBR.FILE_ATTACHMENT_DETAIL.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE INDEX LOBR.FILEATCHDTL_ELGDOCTYP_UK_I ON LOBR.FILE_ATTACHMENT_DETAIL
(STD_ELIGDOCTYPE_ID)
LOGGING
TABLESPACE L_LOBR_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 LOBR.FILEATCHDTL_FILEATCH_FK_I ON LOBR.FILE_ATTACHMENT_DETAIL
(FILE_ATTACHMENT_ID)
LOGGING
TABLESPACE L_LOBR_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 UNIQUE INDEX LOBR.FILEATCHDT_PK ON LOBR.FILE_ATTACHMENT_DETAIL
(FILE_ATTACHMENT_DETAIL_ID)
LOGGING
TABLESPACE L_LOBR_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 FILE_ATTACHMENT_DETAIL FOR LOBR.FILE_ATTACHMENT_DETAIL;


ALTER TABLE LOBR.FILE_ATTACHMENT_DETAIL ADD (
  CONSTRAINT FILEATCHDT_PK
  PRIMARY KEY
  (FILE_ATTACHMENT_DETAIL_ID)
  USING INDEX LOBR.FILEATCHDT_PK
  ENABLE VALIDATE);

ALTER TABLE LOBR.FILE_ATTACHMENT_DETAIL ADD (
  CONSTRAINT FILEATCHDTL_FILEATCH_FK 
  FOREIGN KEY (FILE_ATTACHMENT_ID) 
  REFERENCES LOBR.FILE_ATTACHMENT (FILE_ATTACHMENT_ID)
  ENABLE VALIDATE);

GRANT SELECT ON LOBR.FILE_ATTACHMENT_DETAIL TO LOBR_R;

GRANT INSERT, SELECT, UPDATE ON LOBR.FILE_ATTACHMENT_DETAIL TO LOBR_RW;
ALTER TABLE LOBR.FILE_ATTACHMENT
 DROP PRIMARY KEY CASCADE;

DROP TABLE LOBR.FILE_ATTACHMENT CASCADE CONSTRAINTS;

CREATE TABLE LOBR.FILE_ATTACHMENT
(
  FILE_ATTACHMENT_ID          NUMBER(20)        NOT NULL,
  PERSON_ID                   NUMBER(20),
  FILE_NAME                   VARCHAR2(100 BYTE) NOT NULL,
  FILE_MIME_TYPE              VARCHAR2(50 BYTE) NOT NULL,
  ATTACHMENT_ACCEPTABLE_IND   VARCHAR2(1 BYTE),
  ATTACHMENT_REVIEW_DATE      DATE,
  ATTACHMENT_REVIEW_USERNAME  VARCHAR2(30 BYTE),
  VOA_ENROLLMENT_APP_ID       NUMBER(20),
  RECORD_CREATED_BY           VARCHAR2(30 BYTE) NOT NULL,
  RECORD_CREATED_DATE         DATE              NOT NULL,
  RECORD_MODIFIED_BY          VARCHAR2(30 BYTE) NOT NULL,
  RECORD_MODIFIED_DATE        DATE              NOT NULL,
  RECORD_MODIFIED_COUNT       NUMBER(10)        NOT NULL
)
TABLESPACE L_LOBR_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 COLUMN LOBR.FILE_ATTACHMENT.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 LOBR.FILE_ATTACHMENT.RECORD_CREATED_BY IS 'Account or user ID that created the record';

COMMENT ON COLUMN LOBR.FILE_ATTACHMENT.RECORD_CREATED_DATE IS 'Date the record was created.';

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

COMMENT ON COLUMN LOBR.FILE_ATTACHMENT.RECORD_MODIFIED_DATE IS 'Date of this modification to the record.';

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



CREATE UNIQUE INDEX LOBR.FILEATT_PK ON LOBR.FILE_ATTACHMENT
(FILE_ATTACHMENT_ID)
LOGGING
TABLESPACE L_LOBR_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 LOBR.FILEATT_VOAENRAPP_FK_I ON LOBR.FILE_ATTACHMENT
(VOA_ENROLLMENT_APP_ID)
LOGGING
TABLESPACE L_LOBR_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 FILE_ATTACHMENT FOR LOBR.FILE_ATTACHMENT;


ALTER TABLE LOBR.FILE_ATTACHMENT ADD (
  CONSTRAINT FILEATT_PK
  PRIMARY KEY
  (FILE_ATTACHMENT_ID)
  USING INDEX LOBR.FILEATT_PK
  ENABLE VALIDATE);

ALTER TABLE LOBR.FILE_ATTACHMENT ADD (
  CONSTRAINT FILEATT_VOAENRAPP_FK 
  FOREIGN KEY (VOA_ENROLLMENT_APP_ID) 
  REFERENCES VOA.VOA_ENROLLMENT_APPLICATION (VOA_ENROLLMENT_APP_ID)
  ON DELETE SET NULL
  ENABLE VALIDATE);

GRANT SELECT ON LOBR.FILE_ATTACHMENT TO LOBR_R;

GRANT INSERT, SELECT, UPDATE ON LOBR.FILE_ATTACHMENT TO LOBR_RW;
