 COMMIT;
 
 -- ******************************************************
+
+CREATE MATERIALIZED VIEW COHORT_SELECTOR_ENCOUNTERS AS
+  (
+    SELECT 
+      DISTINCT PATIENT_ID
+    FROM 
+      COHORT_SELECTOR_FSTLST_ENCNTR CSFE
+    WHERE
+     (
+        -- INCLUDE FILTER (ENABLE)
+        (
+          'Y' IN (SELECT PARAMETER_VALUE 
+          FROM CURRENT_RUN_YESNO_PARAMS 
+          WHERE 
+              CRITERIA_ID = 'ENCOUNTER_SELECTION' 
+          AND PARAMETER_TYPE = 'FILTER_ENABLING'
+          )
+        )
+      AND
+        (
+        -- FILTER SELECTED GENDERS
+        PATIENT_ID   IN (
+                          SELECT PATIENT_ID FROM AUX_ENCOUNTERS
+                        )
+        )
+      OR
+        -- EXCLUDE FILTER (DISABLE)
+      (
+            'N' IN (SELECT PARAMETER_VALUE 
+            FROM CURRENT_RUN_YESNO_PARAMS 
+            WHERE 
+              CRITERIA_ID = 'ENCOUNTER_SELECTION' 
+            AND PARAMETER_TYPE = 'FILTER_ENABLING'
+            )
+      )
+    )
+  );
+/
+
+CREATE INDEX CSE_PATID ON COHORT_SELECTOR_ENCOUNTERS(PATIENT_ID) /* tablespace index placeholder */ ;
+
+COMMIT;
+
+--------------------------------------------------------
+--
+--  CREATE SELECTED_PATIENT
+--
+-------------------------------------------------------- 
+-- 25-MAR-2014 07:56:23
+--             RAW_TOTAL_  DN_NOT_NULL NO_DUPLICATES WITH_DIAGNOSIS  WITH_DCVA     ADULTS_   EFFECTIVE_CASES
+--             PATIENTS                                                           WITH_DCVA
+-- TOTALS OLD: 1127845        1106712          790997        206859      61409        60352             60041
+-- TOTALS NEW: 1127844        1106847          991443        328369      
+
+CREATE MATERIALIZED VIEW SELECTED_PATIENT AS 
+(
+  SELECT 
+        CSPG.PATIENT_ID
+      , CSPG.DEIDENTIFIED_ID
+      , CSPG.GENDER 
+      , CSPG.BIRTH_DATE
+      , CSPG.AGE_AT_STUDY_BEGIN
+      , CSPG.AGE_AT_STUDY_END
+  FROM 
+    COHORT_SELECTOR_PATIENT_GENDER CSPG
+  INNER JOIN 
+    COHORT_SELECTOR_DIAGNOSIS CSD
+  ON
+    CSPG.PATIENT_ID = CSD.PATIENT_ID
+  INNER JOIN
+    COHORT_SELECTOR_ENCOUNTERS CSE
+  ON
+    CSPG.PATIENT_ID = CSE.PATIENT_ID
+
+);
+
+COMMIT;
+
+CREATE INDEX PAUAC_PATIENT_ID ON SELECTED_PATIENT (PATIENT_ID) /* tablespace index placeholder */ ;
+
+-- ******************************************************
+CREATE MATERIALIZED VIEW SELECTED_DIAGNOSIS AS 
+(
+  SELECT 
+          SP.DEIDENTIFIED_ID
+        , SP.BIRTH_DATE
+        , BPD.*
+  FROM 
+    BP_DIAGNOSIS BPD
+  INNER JOIN
+    SELECTED_PATIENT SP
+  ON
+    BPD.PATIENT_ID = SP.PATIENT_ID
+);
+
+COMMIT;
+
+CREATE INDEX SD_PATIENT_ID ON SELECTED_DIAGNOSIS (PATIENT_ID) /* tablespace index placeholder */ ;
+
+-- ******************************************************
+CREATE MATERIALIZED VIEW SELECTED_PCI AS 
+(
+  SELECT 
+          SP.DEIDENTIFIED_ID
+        , SP.BIRTH_DATE
+        , TTP.*
+  FROM 
+    TRANSFORM_TRANSPOSE_PCI TTP
+  INNER JOIN
+    SELECTED_PATIENT SP
+  ON
+    TTP.PATIENT_ID = SP.PATIENT_ID
+);
+
+COMMIT;
+
+CREATE INDEX SP_PATIENT_ID ON SELECTED_PCI (PATIENT_ID) /* tablespace index placeholder */ ;
+
+-- ******************************************************
+CREATE MATERIALIZED VIEW SELECTED_DEATH AS 
+(
+  SELECT 
+          SP.DEIDENTIFIED_ID
+        , SP.BIRTH_DATE
+        , BPD.*
+  FROM 
+    BP_DEATH BPD
+  INNER JOIN
+    SELECTED_PATIENT SP
+  ON
+    BPD.PATIENT_ID = SP.PATIENT_ID
+);
+
+COMMIT;
+
+CREATE INDEX SD_PATI_ID ON SELECTED_DEATH (PATIENT_ID) /* tablespace index placeholder */ ;
+
+-- ******************************************************
+CREATE MATERIALIZED VIEW SELECTED_DRUGS AS 
+(
+  SELECT 
+        SP.DEIDENTIFIED_ID
+      , BPD.PRESCRIPTION_ID
+      , BPD.DRUG_ID
+      , BPD.DRUG_NAME
+      , BPD.DRG_QUANTITY
+      , BPD.DRG_UNIT
+      , BPD.OUTPATIENT_INPATIENT
+      , FLOOR(BPD.PRESCRIPTION_DATE   - SP.BIRTH_DATE) AS AGE_AT_PRESCRIPTION
+      , FLOOR(BPD.NEXT_ENCOUNTER_DATE - SP.BIRTH_DATE) AS AGE_AT_NEXT_ENCOUNTER
+      , FLOOR(BPD.ERA_BEGIN_DATE      - SP.BIRTH_DATE) AS AGE_AT_ERA_BEGIN
+      , FLOOR(BPD.ERA_END_DATE        - SP.BIRTH_DATE) AS AGE_AT_ERA_END        
+      , SD.GROUP_ID
+      , SD.GROUP_NAME
+      , NVL(SD.INTERVENTION_DRUG,0) AS INTERVENTION_DRUG
+  FROM 
+    SELECTED_PATIENT SP
+  LEFT OUTER JOIN
+    BP_DRUGS BPD
+  ON
+    BPD.PATIENT_ID = SP.PATIENT_ID
+
+  LEFT OUTER JOIN
+    STUDY_DRUG SD
+  ON
+    BPD.DRUG_ID = SD.DRUG_ID
+
+  LEFT OUTER JOIN
+    (SELECT RESEARCH_ID,RESEARCHER_ID FROM CURRENT_RUN_YESNO_PARAMS GROUP BY RESEARCH_ID,RESEARCHER_ID) CRRR
+  ON
+        SD.RESEARCH_ID = CRRR.RESEARCH_ID
+    AND SD.RESEARCHER_ID = CRRR.RESEARCHER_ID
+
+);
+
+COMMIT;
+
+CREATE INDEX SDRG_PATI_ID ON SELECTED_DRUGS (DEIDENTIFIED_ID) /* tablespace index placeholder */ ;
+
+-- ******************************************************
+CREATE OR REPLACE VIEW COHORT_LABTEST_TRANSPOSED AS
+(
+SELECT A.* 
+FROM 
+  TRANSFORM_TRANSPOSE_LABTST A
+INNER JOIN
+  SELECTED_PATIENT B
+ON
+  A.DEIDENTIFIED_ID = B.DEIDENTIFIED_ID
+);
+-- ******************************************************
+-- ******************************************************
+-- ******************************************************
 -- MVIEW xx - INPATIENT_COUNT
 --
 CREATE OR REPLACE VIEW TRANSFORM_ENCOUNTER_PROFILE


COMMIT;
-- ******************************************************
CREATE OR REPLACE VIEW COHORT_OVERALL_DBPOP_GENDER AS
(
  SELECT 
        'OVERALL' AS POPULATION
      , GENDER
      , TRUNC(COUNT(GENDER) * 100 / (SELECT COUNT(*) FROM STUDY_OVERALL_POPULATION)) AS PERCENT
      , COUNT(GENDER) AS TOTAL 
  FROM 
    STUDY_OVERALL_POPULATION 
  GROUP BY 
    GENDER
  UNION
  SELECT 
        'STUDY'  as POPULATION
      , GENDER
      , TRUNC(COUNT (GENDER) * 100 / (SELECT COUNT(*) AS PERCENT FROM COHORT_PATIENT)) AS PERCENT
      , COUNT(GENDER) AS TOTAL 
  FROM 
    COHORT_PATIENT 
  GROUP BY 
    GENDER
);

-- ******************************************************
CREATE OR REPLACE VIEW COHORT_ENCOUNTER AS
(
SELECT 


BEGIN
   EXECUTE IMMEDIATE 'DROP VIEW COHORT_DRUGS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP VIEW COHORT_DRUGS_DETAIL';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
