Search This Blog

Tuesday, October 2, 2018

Assigned User Roles Query in Fusion

SELECT pu.person_id,

  hz.party_id,

  'Person'           AS Type,

  ppn.display_name,

  pu.username,

  DECODE(pu.suspended, 'Y', 'Yes', 'No') AS Suspended,

  prdv.role_common_name,

  prdv.role_name,

  prdv.description,

  prdv.abstract_role,

  prdv.job_role,

  prdv.data_role,
  pur.last_updated_by,
  pur.last_update_date

FROM fusion.per_users pu,

  fusion.hz_parties hz,

  fusion.per_user_roles pur,

  fusion.per_roles_dn_vl prdv,

  fusion.per_person_names_f ppn

WHERE pu.user_id      = pur.user_id(+)

AND pu.user_guid      = hz.user_guid

AND prdv.role_guid(+) = pur.role_guid

AND pu.person_id     IS NOT NULL

AND ppn.name_type     = 'GLOBAL'

AND ppn.person_id     = pu.person_id

AND (sysdate BETWEEN ppn.effective_start_date AND ppn.effective_end_date)

and PU.ACTIVE_FLAG = 'Y'

and upper(pu.username) IN ('96040')