CREATE OR REPLACE FORCE VIEW "PCH_APP"."PROVIDER_VW" ("PROVIDER_ID", "PROVIDER_NAME", "CITY", "STATE_CODE", "RHQDAPU_EL
IGIBLE_IND", "HOP_QDRP_ELIGIBLE_IND", "PUBLIC_REPORTING_IND", "PROVIDER_TYPE_ID", "HOSPITAL_TYPE", "NPI", "ELIGIBLE_IND",
 "INITIATIVE_CD", "WAIVER_TYPE_CD", "WAIVER_EFFECTIVE_DATE", "WAIVER_END_DATE", "DISASTER_TYPE_CD", "WAIVER_TYPE_DESC") A
S
  select hsp.provider_id
       ,hsp.provider_name
       ,hsp.city
       ,hsp.state_code
       ,hsp.rhqdapu_eligible_ind
       ,hsp.hop_qdrp_eligible_ind
       ,hsp.public_reporting_ind
       ,hsp.provider_type_id
       ,hsp.hospital_type
       ,hsp.npi
       ,e.eligible_ind
       ,e.initiative_cd
       ,wv.waiver_type_cd
       ,wv.waiver_effective_date
       ,wv.waiver_end_date
       ,wv.disaster_type_cd
       ,wv.waiver_type_desc
        from bi.hospital_vw hsp
             ,pch_app.syn_prvdr_eligible_ind e
         --    ,np_sdps.prvdr_eligible_ind@np_sdps_sdol_link.world e
             ,(select w.*, wt.waiver_type_desc  from pch_app.syn_prs_cd_waiver_type wt
         --    ,(select w.*, wt.waiver_type_desc  from np_sdps.prs_cd_waiver_type@np_sdps_sdol_link.world wt
                               ,PCH_APP.SYN_PRS_NATURAL_DISASTER_WVR w --pch_app.syn_prs_natural_disaster_waive w
         --                    ,np_sdps.prs_natural_disaster_waiver@np_sdps_sdol_link.world w
                                where
                                  w.waiver_type_cd = wt.waiver_type_cd and w.waiver_type_cd in (10,11,12)) wv
  where
    hsp.provider_id = e.hsp_id and e.initiative_cd = 6 and
    hsp.provider_id = wv.provider_id(+)