/* Query to fetch PO Number and the Procurement BU and data is restricted based on the user's Procurement Agent Access */
\SELECT P.SEGMENT1 PO_NUMBER,
HR.NAME PROCUREMENT_BU
FROM PO_HEADERS_ALL P,
HR_ALL_ORGANIZATION_UNITS HR
WHERE P.PRC_BU_ID=HR.ORGANIZATION_ID
AND EXISTS --Exists condition to check user access as Procurement Agent
(SELECT 0
FROM PO_AGENT_ACCESSES PoAgentAccess
WHERE PoAgentAccess.agent_id IN (SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL)
AND PoAgentAccess.PRC_BU_ID = P.PRC_BU_ID
AND (P.TYPE_LOOKUP_CODE = 'STANDARD'
AND PoAgentAccess.ACCESS_ACTION_CODE = 'MANAGE_PURCHASE_ORDERS')
AND PoAgentAccess.ACTIVE_FLAG = 'Y'
AND PoAgentAccess.ALLOWED_FLAG = 'Y'
AND (PoAgentAccess.ACCESS_OTHERS_LEVEL_CODE IN ('VIEW','MODIFY','FULL')
OR P.AGENT_ID = PoAgentAccess.AGENT_ID)
)
