CREATE OR REPLACE FORCE VIEW "PPRS_APP"."CART_ABS_QUARTER_COUNTS_VW" ("CASE_ID", "PROVIDER_ID", "DISCHARGE_DATE", "TOPI
C_ID") AS
  select
  c.case_id,
  c.provider_id,
  c.discharge_date,
  a.topic_id
from
  SYN_CASES c,
  SYN_ABSTRACTION a
where c.case_id = a.case_id


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."CART_MEASURE_VW" ("TOPIC_ID", "TOPIC", "TOPIC_DESCRIPTION", "INDICATOR_ID", "T
IME_PERIOD_ID", "START_DATE", "END_DATE", "CMS_CD", "INDICATOR", "INDICATOR_DESC") AS
  select distinct
  qr.topic_id,
  t.topic,
  t.topic_description,
  qr.indicator_id,
  qr.time_period_id,
  tp.start_date,
  tp.end_date,
  i.cms_cd,
  i.indicator,
  i.cms_cd || '  ' || i.indicator as indicator_desc
from
  cart.question_rules qr,
  cart.indicator i,
  cart.topic t,
  cart.time_period tp
where qr.indicator_id = i.indicator_id
  and qr.topic_id = t.topic_id
  and qr.time_period_id = tp.time_period_id


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."CD_DISCHARGE_SUBMSN_DATE_VW" ("DISCHARGE_START_DATE", "DISCHARGE_END_DATE", "Q
UARTER", "SUBMISSION_DEADLINE", "CALCULATED_DEADLINE", "CREATE_USER_ID", "CREATE_DATE", "UPDATE_USER_ID", "UPDATE_DATE",
"CD_DISCHARGE_SUBMSN_DATE_ID", "POPULATION_DEADLINE", "POPULATION_DEADLINE_DATETIME", "SUBMISSION_DEADLINE_DATETIME") AS
  select
  t.qtr_dschrg_strt_dt as discharge_start_date,
  t.qtr_dschrg_end_dt + 86399/86400 as discharge_end_date,
  t.qtr_desc as quarter,
  coalesce(se.time_prd_qtr_dtl_dt,sd.time_prd_qtr_dtl_dt) + 86399/86400 as submission_deadline,
  coalesce(pe.time_prd_qtr_dtl_dt,pd.time_prd_qtr_dtl_dt) + 86399/86400 as calculated_deadline,
  t.creat_user_id as create_user_id,
  t.creat_dt as create_date,
  t.updt_user_id as update_user_id,
  t.updt_dt as update_date,
  t.time_prd_qtr_id as cd_discharge_submsn_date_id,
  coalesce(pe.time_prd_qtr_dtl_dt,pd.time_prd_qtr_dtl_dt) as population_deadline,
  --dm, HR 4.0, 02/28/2013, added sys_dttm
  coalesce(NVL(pe.time_prd_qtr_dtl_sys_dttm, pe.time_prd_qtr_dtl_dt+(86399/86400)),
           NVL(pd.time_prd_qtr_dtl_sys_dttm, pd.time_prd_qtr_dtl_dt+(86399/86400))) AS population_deadline_datetime,
  coalesce(NVL(se.time_prd_qtr_dtl_sys_dttm, se.time_prd_qtr_dtl_dt+(86399/86400)),
           NVL(sd.time_prd_qtr_dtl_sys_dttm, sd.time_prd_qtr_dtl_dt+(86399/86400))) AS submission_deadline_datetime
from
  cart.time_period_quarter t,
  cart.time_period_quarter_detail sd,  --submission deadline
  cart.time_period_quarter_detail pd,  --population deadline
  cart.time_period_quarter_detail se,  --submission extension
  cart.time_period_quarter_detail pe   --population extension
where t.time_prd_qtr_id = sd.time_prd_qtr_id (+)
  and sd.time_prd_dtl_type_id (+) = 1
  and t.time_prd_qtr_id = pd.time_prd_qtr_id (+)
  and pd.time_prd_dtl_type_id (+) = 3
  and t.time_prd_qtr_id = se.time_prd_qtr_id (+)
  and se.time_prd_dtl_type_id (+) = 6
  and t.time_prd_qtr_id = pe.time_prd_qtr_id (+)
  and pe.time_prd_dtl_type_id (+) = 4
  and t.time_prd_id >= 35


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."HEALTHSERVICEPROVIDER_VW" ("STATE_CODE", "PROVIDER_ID", "PROVIDER_NAME", "RHQD
APU_ELIGIBLE_IND", "NPI", "HSP_MEDICARE_ACCPT_DT", "HSP_FACILITY_CLOSE_DT", "HSP_SETTING") AS
  select distinct
  t.state_code,
  t.hsp_id as provider_id,
  t.hsp_name as provider_name,
  t.rhqdapu_eligible_ind,
  t.npi,
 t.HSP_MEDICARE_ACCPT_DT,
  t.HSP_FACILITY_CLOSE_DT,
  t.HSP_SETTING
from
  SYN_HLTH_SERV_PROVIDER t
where
(HSP_SETTING = '0' and substr(hsp_id,3,1) = '0') or
      (HSP_SETTING = 'N' and substr(hsp_id,3,2) = '13')


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."HSP_RHQDAPU_AFFECTED_VW" ("STATE_CODE", "HSP_ID", "FISCAL_YEAR", "APU_NOTICE_D
T", "APU_NONPART_WD_DT") AS
  select t1.state_code, t1.hsp_id, t1.fiscal_year, t1.apu_notice_dt, t1.apu_nonpart_wd_dt
from SYN_PRS_HSP_RHQDAPU t1
where t1.fiscal_year = (
       select max(fiscal_year)
       from SYN_PRS_HSP_RHQDAPU t2
       where t2.hsp_id = t1.hsp_id and t2.state_code = t1.state_code)


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."MEASURE_SET_VW" ("MSR_SET_ID", "MSR_SET_NAME", "MSR_SET_DESC", "CREAT_DT", "CR
EAT_USER_ID", "UPDT_DT", "UPDT_USER_ID") AS
  select
  MSR_SET_ID,
 replace(MSR_SET_NAME,'HOP', 'OQR') MSR_SET_NAME,
  MSR_SET_DESC,
  CREAT_DT,
  CREAT_USER_ID,
  UPDT_DT,
  UPDT_USER_ID
  from opps_import.measure_set


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."PPRS_TOPIC_VW" ("TOPIC_ID", "TOPIC_TYPE_ID", "TOPIC", "TOPIC_DESCRIPTION", "ES
TIMATED_TIME", "CREATE_DATE", "CREATE_USER_ID", "UPDATE_DATE", "UPDATE_USER_ID", "QUARTER_START_DATE", "QUARTER_END_DATE"
) AS
  select TOPIC_ID,
       TOPIC_TYPE_ID,
       'IQR-'|| Topic Topic,
      --('IQR - ' + TOPIC) as TOPIC,
       TOPIC_DESCRIPTION,
       ESTIMATED_TIME,
       CREATE_DATE,
       CREATE_USER_ID,
       UPDATE_DATE,
       UPDATE_USER_ID,
       QUARTER_START_DATE,
       t.QUARTER_END_DATE + 86399 / 86400 as QUARTER_END_DATE
  from cart.topic t
 where t.topic_type_id = '1'


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."RHQDAPU_PLEDGE_VW" ("RHQDAPU_PLEDGE_ID", "STATE_CODE", "HSP_ID", "APU_NTC_DT",
 "APU_NONPART_WITHDRAW_DT", "APU_DRAFT_NTC_DT", "APU_DRAFT_NONPART_WITHDRAW_DT", "FSCL_YEAR", "CREAT_DT", "CREAT_USR_ID",
 "UPDT_DT", "UPDT_USR_ID") AS
  select t1.rhqdapu_pledge_id, t1.state_code, t1.hsp_id, t1.apu_ntc_dt, t1.apu_nonpart_withdraw_dt,
t1.apu_draft_ntc_dt, t1.apu_draft_nonpart_withdraw_dt, t1.fscl_year, t1.creat_dt, t1.creat_usr_id, t1.updt_dt,
t1.updt_usr_id
from ipledge_app.rhqdapu_pledge t1
where  t1.fscl_year = (
       select max(fscl_year)
       from ipledge_app.rhqdapu_pledge t2
       where t2.hsp_id = t1.hsp_id and t2.state_code = t1.state_code)


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."SUB_TOPIC_VW" ("SUB_TOPIC_ID", "TOPIC_ID", "SUB_TOPIC_DESCRIPTION", "SUB_TOPIC
_DEFINITION", "SUB_TOPIC_START_DATE", "SUB_TOPIC_END_DATE") AS
  select
  sd.sub_topic_id,  --mc, HR 3.0, 02/01/2013, change strata_detail_id to sub_topic_id
  sd.topic_id,
  sd.strata_short_description as sub_topic_description,
  sd.strata_description as sub_topic_definition,
  sd.strata_start_date as sub_topic_start_date,
  sd.strata_end_date as sub_topic_end_date
from
  cart.strata_detail sd


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."VW_GET_OARS_EMAIL_PROV" ("USR_ID", "DETAIL_ITEM_ID", "DETAIL_VALUE", "DEFAULT_
GRP", "STATUS_CD", "GRP_TYPE_ID", "EMAIL") AS
  select
      u.usr_id,
		gd.detail_item_id,
		gd.detail_value,
		ug.default_grp,
		ug.status_cd,
		g.grp_type_id,
		u.email
      from
        user_admin.grp_detail gd,
        user_admin.usr_grp ug,
        user_admin.usr u,
		  user_admin.grp g
      where gd.grp_id = ug.grp_id
      -- User Group is Active or Approved
      and ug.usr_id = u.usr_id
		and gd.grp_id = g.grp_id
      -- User has the PPRS Email Role
      -- and is Active or Approved
 and exists (
        select 1
        from
          user_admin.usr_role ur
        where ur.usr_id = u.usr_id
        and (ur.role_id = 284 -- PPRS Measure Designation Read Update
          or ur.role_id = 285)
        and (ur.status_cd = 2 -- Active
          or ur.status_cd = 5 -- Approved
        ))


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."VW_GET_OARS_EMAIL_QIO" ("USR_ID", "EMAIL", "GRP_TYPE_ID", "DETAIL_VALUE") AS
  select distinct
     t.usr_id,
     t.email,
     t.grp_type_id,
	 gd.detail_value
--     gd.detail_value
   FROM
    user_admin.grp_detail gd,
    table( cast (fn_get_oars_email_qio(gd.detail_value)
	   AS pprs_app.tpt_get_oars_email_qio )) t


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."VW_TOPIC_RULES" ("QUESTION_RULES_ID", "TOPIC_ID", "INDICATOR_ID", "TOPIC_DESCR
IPTION", "TIME_PERIOD_ID") AS
  select distinct t2.question_rules_id, t2.topic_id,t2.indicator_id, t1.topic_description, t2.time_period_id
from cart.topic t1, cart.question_rules t2


  CREATE OR REPLACE FORCE VIEW "PPRS_APP"."VW_VENDOR_CONTRACTS" ("VENDOR_CONTRACT_KEY", "STATE_CODE", "HSP_ID", "MEAS_SET
_ID", "TRANSMISSION_START_DATE", "TRANSMISSION_END_DATE", "CARE_START_DATE", "CARE_END_DATE", "VENDOR_ID", "ACTIVE_DATE",
 "INACTIVE_DATE", "ORGANIZATION_NAME", "MEASUREMENT_NAME") AS
  SELECT VC.VENDOR_CONTRACT_KEY,
VC.STATE_CODE,
VC.HSP_ID,
VC.MEAS_SET_ID,
           VC.TRANSMISSION_START_DATE,
VC.TRANSMISSION_END_DATE,
VC.CARE_START_DATE,
           VC.CARE_END_DATE,
           VT.VENDOR_ID,
           VT.ACTIVE_DATE,
VT.INACTIVE_DATE,
           V.ORGANIZATION_NAME,
           V.MEASUREMENT_NAME
    FROM SYN_VENDOR_CONTRACT VC,
         SYN_VENDOR_TYPE VT,
         SYN_VENDORS V
   WHERE ( VT.VENDOR_TYPE_KEY = VC.VENDOR_TYPE_KEY ) and
         ( V.VENDOR_ID = VT.VENDOR_ID )


13 rows selected