CREATE OR REPLACE FORCE VIEW "IPLEDGE_APP"."HEALTHSERVICEPROVIDER_VW" ("STATE_CODE", "PROVIDER_ID", "PROVIDER_NAME", "R
HQDAPU_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
  np_sdps.hlth_serv_provider@np_sdps_sdol_link t
where ((rhqdapu_eligible_ind = 'Y' or rhqdapu_eligible_ind = 'N' or substr(hsp_id,3,1) = '1') and t.HSP_MEDICARE_ACCPT_DT
<=sysdate)


  CREATE OR REPLACE FORCE VIEW "IPLEDGE_APP"."VW_GET_OARS_EMAIL_QIO" ("USR_ID", "EMAIL", "DETAIL_VALUE") AS
  select
     t.usr_id,
     t.email,
     gd.detail_value
   FROM
    user_admin.grp_detail gd,
    table( cast (fn_get_oars_email_qio(gd.detail_value)
	   AS ipledge_app.tpt_get_oars_email_qio )) t