text: /**************************************************************************************************
  * Planio #6146 - Create tables on CASPRS01 for full prescriptions data
  **************************************************************************************************/
  
  /* ROLLBACK :-
  
  DROP TABLE PRSFULL.PPATIENTS;
  DROP TABLE PRSFULL.PRESCRIPTION_PATIENTIDS;
  DROP TABLE PRSFULL.PRESCRIPTION_DATA;
  DROP TABLE PRSFULL.ZBNF;
  */
  
  
  CREATE TABLE PRSFULL.PPATIENTS
    ( PPATIENTS_ID        NUMBER(12,0)   NOT NULL
    , E_BATCH_ID          NUMBER(12,0)
    , PPATIENT_RAWDATA_ID NUMBER(12,0)   NOT NULL
    , TYPE                VARCHAR2(32)
    , PSEUDO_ID1          VARCHAR2(64)
    , PSEUDO_ID2          VARCHAR2(64)
    )
    COMPRESS BASIC
    NOLOGGING
    TABLESPACE PRESCRIPTIONDATA;
  
  ALTER TABLE PRSFULL.PPATIENTS ADD CONSTRAINT PPATIENTS_PK PRIMARY KEY (PPATIENTS_ID) USING INDEX NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  CREATE INDEX PRSFULL.PPATIENTS_PSEUDO_ID1_IX ON PRSFULL.PPATIENTS(PSEUDO_ID1) NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  CREATE TABLE PRSFULL.PRESCRIPTION_PATIENTIDS
    ( PSEUDO_ID1        VARCHAR2(64)
    , PATIENTID         NUMBER(19,0)    NOT NULL
    )
    COMPRESS BASIC
    NOLOGGING
    TABLESPACE PRESCRIPTIONDATA;
  
  COMMENT ON TABLE PRSFULL.PRESCRIPTION_PATIENTIDS IS 'List of matched pseudo_id1s with patients in CASREF database';
  CREATE UNIQUE INDEX PRSFULL.PRESC_PATIENTS_PSEUDO_ID1_IX ON PRSFULL.PRESCRIPTION_PATIENTIDS(PSEUDO_ID1, PATIENTID) NOLOGGING COMPRESS 1 TABLESPACE PRESCRIPTIONDATA_IX;
  CREATE UNIQUE INDEX PRSFULL.PRESC_PATIENTS_PATIDPSEUDOID ON PRSFULL.PRESCRIPTION_PATIENTIDS(PATIENTID, PSEUDO_ID1) NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  CREATE TABLE PRSFULL.PRESCRIPTION_DATA
    ( PRESCRIPTION_DATAID NUMBER(12,0)  NOT NULL
    , PPATIENT_ID         NUMBER(12,0)
    , PRESC_DATE          DATE
    , PART_MONTH          CHAR(6)
    , PRESC_POSTCODE      VARCHAR2(8)
    , PCO_CODE            VARCHAR2(8)
    , PCO_NAME            VARCHAR2(64)
    , PRACTICE_CODE       VARCHAR2(32)
    , PRACTICE_NAME       VARCHAR2(64)
    , NIC                 VARCHAR2(64)
    , PRESC_QUANTITY      VARCHAR2(9)
    , ITEM_NUMBER         NUMBER(3,0)
    , UNIT_OF_MEASURE     VARCHAR2(64)
    , PAY_QUANTITY        NUMBER(6,0)
    , DRUG_PAID           VARCHAR2(256)
    , BNF_CODE            VARCHAR2(32)
    , PAT_AGE             NUMBER(3,0)
    , PF_EXEMPT_CAT       CHAR(1)
    , ETP_EXEMPT_CAT      CHAR(1)
    , ETP_INDICATOR       CHAR(1)
    )
    COMPRESS BASIC
    NOLOGGING
    TABLESPACE PRESCRIPTIONDATA;
  
  ALTER TABLE PRSFULL.PRESCRIPTION_DATA ADD CONSTRAINT PRESCRIPTION_DATA_PK PRIMARY KEY (PRESCRIPTION_DATAID) USING INDEX NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  CREATE INDEX PRSFULL.PRESCRIPTION_DATA_BNF_CODE_IX ON PRSFULL.PRESCRIPTION_DATA (BNF_CODE) NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  CREATE INDEX PRSFULL.PRESCRIPTION_DATA_PPAT_ID_IX ON PRSFULL.PRESCRIPTION_DATA (PPATIENT_ID) NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  
  GRANT SELECT ON PRSFULL.PPATIENTS TO PRESCRIPTIONTEST;
  GRANT SELECT ON PRSFULL.PRESCRIPTION_PATIENTIDS TO PRESCRIPTIONTEST;
  GRANT SELECT ON PRSFULL.PRESCRIPTION_DATA TO PRESCRIPTIONTEST;
  
  GRANT SELECT, INSERT, UPDATE, DELETE ON PRSFULL.PPATIENTS TO ANALYSISKELVINHUNTER;
  GRANT SELECT, INSERT, UPDATE, DELETE ON PRSFULL.PRESCRIPTION_PATIENTIDS TO ANALYSISKELVINHUNTER;
  GRANT SELECT, INSERT, UPDATE, DELETE ON PRSFULL.PRESCRIPTION_DATA TO ANALYSISKELVINHUNTER;
  
  -- BNF codes
  CREATE TABLE PRSFULL.ZBNF
    (ZBNF_ID              NUMBER(12,0)  NOT NULL
    , CHAPTER             VARCHAR2(40)
    , CHAPTER_CODE        VARCHAR2(2)
    , SECTION             VARCHAR2(40)
    , SECTION_CODE        VARCHAR2(4)
    , PARAGRAPH           VARCHAR2(40)
    , PARAGRAPH_CODE      VARCHAR2(6)
    , SUBPARAGRAPH        VARCHAR2(40)
    , SUBPARAGRAPH_CODE   VARCHAR2(7)
    , CHEMICAL_SUBSTANCE  VARCHAR2(40)
    , CHEMICAL_SUBSTANCE_CODE   VARCHAR2(9)
    , PRODUCT             VARCHAR2(40)
    , PRODUCT_CODE        VARCHAR2(11)
    , PRESENTATION        VARCHAR2(60)
    , PRESENTATION_CODE   VARCHAR2(15)
    )
    COMPRESS BASIC
    NOLOGGING
    TABLESPACE PRESCRIPTIONDATA;
  
  ALTER TABLE PRSFULL.ZBNF ADD CONSTRAINT ZBNF_PK PRIMARY KEY (ZBNF_ID) USING INDEX NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  CREATE INDEX PRSFULL.ZBNF_PRES_CODE_IX ON PRSFULL.ZBNF (PRESENTATION_CODE) NOLOGGING TABLESPACE PRESCRIPTIONDATA_IX;
  
  
  GRANT SELECT ON PRSFULL.ZBNF TO PRESCRIPTIONTEST;
  GRANT SELECT, INSERT, UPDATE, DELETE ON PRSFULL.ZBNF TO ANALYSISKELVINHUNTER;
  