 
 CREATE INDEX AUXE_PATID ON AUX_ENCOUNTERS(PATIENT_ID) /* tablespace index placeholder */ ;
 
--- ******************************************************
-
-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;
 
--- **********************************************
-
-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 - TRANSFORM_FIRSTLAST_ENCOUNTER
 --

COMMIT;

-- ******************************************************
-- MVIEW xx - INPATIENT_COUNT
--
CREATE OR REPLACE VIEW TRANSFORM_ENCOUNTER_PROFILE

  SELECTED_PATIENT SP
ON 
  BPP.DEIDENTIFIED_ID = SP.DEIDENTIFIED_ID
);

-- ******************************************************
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
);

-- ******************************************************
