Business Unit LOV (Non-Procurement) — Security-Filtered Report Parameter

Lists the Business Units the current BI Publisher user has data access to, based on their user role data assignments — used as the LOV source for a non-procurement "Business Unit" report parameter so users only see BUs they're allowed to report on.

select
    bu.bu_name
  , bu.bu_id
from
    FUN_ALL_BUSINESS_UNITS_V bu
where
    1             =1
    and bu.status = 'A'
    and bu.bu_id in
    (
        select distinct
            furda.org_id
        from
            per_users                   pu
          , FUN_USER_ROLE_DATA_ASGNMNTS furda
        where
            1                              =1
            and pu.USER_GUID               = furda.USER_GUID
            and nvl(furda.active_flag,'N') = 'Y'
            and sysdate between furda.start_date_active and nvl(furda.end_date_active,sysdate)
            and upper(pu.username) = upper(:xdo_user_name)
    )
order by
    1