Manufacturing Plant LOV — Security-Filtered Report Parameter

Lists the inventory organizations flagged as manufacturing plants that the current user has access to — used as the LOV source for a "Manufacturing Plant" report parameter.

/* Manufacturing Plant list that a user has access to */
SELECT iodv.organization_name,
       iodv.organization_id
FROM   fusion.inv_organization_definitions_v iodv
WHERE  (iodv.disable_date IS NULL OR iodv.disable_date > sysdate)
AND    iodv.manufacturing_flag = 'Y'
AND    iodv.organization_id IN (
           SELECT DISTINCT furda.mfg_organization_id
           FROM   fusion.per_users                   pu,
                  fusion.fun_user_role_data_asgnmnts furda
           WHERE  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 iodv.organization_name;