CREATE OR REPLACE FORCE VIEW "OPLEDGE_APP"."VW_GET_OARS_EMAIL_QIO" ("PROVIDER_ID", "PROVIDER_GROUP_ID", "USER_GROUP_ID"
, "USR_ID", "EMAIL") AS
  select
  gd.detail_value as provider_id,
  g.grp_id as provider_group_id,
  gp.grp_id as user_group_id,
  u.usr_id,
  u.email
from
  user_admin.grp_detail gd,
  user_admin.grp g,
  user_admin.grp gp,
  user_admin.usr_grp ug,
  user_admin.usr u
where gd.detail_item_id = 17 /* MPN */
and gd.grp_id = g.grp_id
and g.grp_type_id = 2 /* Provider */
and gp.grp_id in (
  select grp_id from user_admin.grp
  start with grp_id = g.grp_id
  connect by grp_id = prior parent_grp_id )
and gp.grp_id not in (1,2,64) /* IFMC, SDPS, ??? */
and gp.grp_id = ug.grp_id
and ug.status_cd in (2,5) /* Active, Approved */
and ug.usr_id = u.usr_id
and exists (
  select * from user_admin.usr_role
  where usr_id = u.usr_id
  and status_cd in (2,5) /* Active, Approved */
  and role_id in (353,354) /* OPledge Read/Update */ )