DROP VIEW ADR.ACTIVEAGG_VW;

/* Formatted on 9/29/2017 11:07:51 AM (QP5 v5.269.14213.34769) */
CREATE OR REPLACE FORCE VIEW ADR.ACTIVEAGG_VW
(
   PERID,
   ICN,
   EDIPI,
   SSN,
   LN,
   FN,
   MN,
   SUF,
   DOB,
   DOD,
   GENDER,
   ENR_DTE,
   CV_ELIG,
   ELIG_VERIFIED,
   ENR_CODE,
   PRIORITY,
   ENR_CRE_DTE,
   ADD_TYPE,
   BAI,
   ADDRESS_ID,
   ST1,
   ST2,
   ST3,
   CITY,
   STATE,
   ZIP,
   ZIP4,
   COUNTRY,
   ADT,
   PFA,
   PCF,
   HPN,
   CPN,
   WPN,
   APN,
   TSTART,
   TEND,
   PF_NO,
   PF_NAME,
   VCE,
   SCI,
   SC_PCT,
   SAI,
   SALIST,
   NSCI,
   NLE_ES,
   STA_VISN,
   STA_NO,
   STA_NAME,
   EE_FLAG,
   DISTANCE,
   OPP_FLAG,
   ADR_LON,
   ADR_LAT,
   STA_ST1,
   STA_ST2,
   STA_ST3,
   STA_CITY,
   STA_STATE,
   STA_ZIP,
   STA_ZIP4,
   STA_LON,
   STA_LAT,
   LOC_NAME,
   GEO_DATE,
   SOFTWARE_VER,
   STATUS,
   STATUS_DATE,
   WT_INFO_CONCATTED,
   MAILING_EXCEPTION,
   TIME_COHORT,
   BASIC_ELIG,
   GEO_TIME_FRAME_BEGIN,
   GEO_TIME_FRAME_END,
   WT_RUN_TIME,
   VBA_DOD,
   EVER_GEO_CODED,
   CALC_TYPE,
   DD_BASIC_ELIG,
   DD_NLE_ES,
   VISN,
   URH,
   FIPS,
   MARKET,
   SUBMARKET,
   SECTOR,
   STATE_FIPS,
   S_CLASS_NAME,
   OUTPATIENTRATING,
   PAR_STA_NO,
   PARENTSTATIONCL,
   SH_PAR_STA,
   VA_SITE,
   ST,
   POD,
   SHAR,
   HCC,
   MSCBOC,
   NEWVAMC,
   PCCBOC,
   COCCLASSIFICATIONID,
   VAH,
   CD,
   CONT,
   PROV,
   VACAA_LIST_DATE,
   NLE_ES_DATE,
   COUNTY_NAME,
   MARKET_NAME,
   DATASOURCE,
   DISTRICT,
   STA_DISTRICT_VISN,
   RECORD_CREATED_TS
)
AS
   SELECT PERID,
          ICN,
          EDIPI,
          SSN,
          LN,
          FN,
          MN,
          SUF,
          DOB,
          DOD,
          GENDER,
          ENR_DTE,
          CV_ELIG,
          ELIG_VERIFIED,
          ENR_CODE,
          PRIORITY,
          ENR_CRE_DTE,
          ADD_TYPE,
          BAI,
          ADDRESS_ID,
          ST1,
          ST2,
          ST3,
          CITY,
          STATE,
          ZIP,
          ZIP4,
          COUNTRY,
          ADT,
          PFA,
          PCF,
          HPN,
          CPN,
          WPN,
          APN,
          TSTART,
          TEND,
          PF_NO,
          PF_NAME,
          VCE,
          SCI,
          SC_PCT,
          SAI,
          SALIST,
          NSCI,
          NLE_ES,
          STA_VISN,
          STA_NO,
          STA_NAME,
          EE_FLAG,
          DISTANCE,
          OPP_FLAG,
          ADR_LON,
          ADR_LAT,
          STA_ST1,
          STA_ST2,
          STA_ST3,
          STA_CITY,
          STA_STATE,
          STA_ZIP,
          STA_ZIP4,
          STA_LON,
          STA_LAT,
          LOC_NAME,
          GEO_DATE,
          SOFTWARE_VER,
          STATUS,
          STATUS_DATE,
          WT_INFO_CONCATTED,
          MAILING_EXCEPTION,
          TIME_COHORT,
          BASIC_ELIG,
          GEO_TIME_FRAME_BEGIN,
          GEO_TIME_FRAME_END,
          WT_RUN_TIME,
          VBA_DOD,
          EVER_GEO_CODED,
          CALC_TYPE,
          DD_BASIC_ELIG,
          DD_NLE_ES,
          VISN,
          URH,
          FIPS,
          MARKET,
          SUBMARKET,
          SECTOR,
          STATE_FIPS,
          S_CLASS_NAME,
          OUTPATIENTRATING,
          PAR_STA_NO,
          PARENTSTATIONCL,
          SH_PAR_STA,
          VA_SITE,
          ST,
          POD,
          SHAR,
          HCC,
          MSCBOC,
          NEWVAMC,
          PCCBOC,
          COCCLASSIFICATIONID,
          VAH,
          CD,
          CONT,
          PROV,
          VACAA_LIST_DATE,
          NLE_ES_DATE,
          COUNTY_NAME,
          MARKET_NAME,
          DATASOURCE,
          DISTRICT,
          STA_DISTRICT_VISN,
          s.RECORD_CREATED_TS
     FROM ADR.ACTIVEAGG s,
          (SELECT CASE
                     WHEN LOADING_STATUS = 'YES' THEN CURRENT_ACTIVE_DATE
                     WHEN LOADING_STATUS = 'FAIL' THEN PREVIOUS_ACTIVE_DATE
                     ELSE NULL
                  END
                     active_dt
             FROM ADR.ActiveAGG_TRACKER) t
    WHERE TRUNC (s.RECORD_CREATED_TS) = TRUNC (t.active_dt);


CREATE OR REPLACE PUBLIC SYNONYM ACTIVEAGG_VW FOR ADR.ACTIVEAGG_VW;


GRANT SELECT ON ADR.ACTIVEAGG_VW TO ADR_R;

GRANT SELECT ON ADR.ACTIVEAGG_VW TO EEUSR;
