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

Lists the Business Units the current user has procurement agent access to, via their PO agent assignment — used as the LOV source for a procurement report's "Business Unit" parameter.

/* Business Units list that a procurement user has access */
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
            pass.PRC_BU_ID
        from
            PO_AGENT_ASSIGNMENTS pass
          , per_users            pu
        where
            1=1
            -- and pu.USER_GUID = FND_GLOBAL.USER_GUID
            and pu.person_id       = pass.agent_id
            and pass.active_flag   = 'Y'
            and upper(pu.username) = upper(:xdo_user_name)
    )
order by
    1