A SQL query to verify the link between an employee and their buyer setup.
In Oracle E-Business Suite, an employee record is distinct from a buyer record. However, a single person can be both. This guide provides a key SQL query to determine if an employee is also set up as a buyer in the Purchasing module. This is essential for user management, approvals, and troubleshooting procurement issues.
This query joins the employee records table with the purchasing agents table to find users who exist in both, indicating they are set up as buyers.
SELECT pa.agent_id,
papf.person_id,
papf.employee_number,
papf.email_address,
pa.category_id,
pa.location_id,
papf.effective_start_date,
papf.effective_end_date
FROM po_agents pa,
per_all_people_f papf,
hr_all_organization_units haou
WHERE pa.agent_id = papf.person_id
AND papf.business_group_id = haou.business_group_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
--AND email_address = 'user@oag.com'
--AND PAPF.EFFECTIVE_END_DATE > SYSDATE
--AND HAOU.BUSINESS_GROUP_ID = 100This is the core Human Resources table that stores information about all people in the system, including employees. The `PERSON_ID` is the unique identifier for an individual.
This table in the Purchasing module stores the list of defined buyers. The critical column here is `AGENT_ID`, which is a foreign key that links back to the `PERSON_ID` in `PER_ALL_PEOPLE_F`.
pa.agent_id = papf.person_id. If a record for a person exists in both tables with a matching ID, it confirms that the employee is also a buyer. The query also filters for currently active employees using `TRUNC(SYSDATE)`.