Query to check if an employee is a buyer

Check if an Employee is a Buyer in Oracle Apps R12

A SQL query to verify the link between an employee and their buyer setup.

Query Rationale

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.

The SQL Query

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.

SQL Query

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 = 100

Key Table: PER_ALL_PEOPLE_F

This 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.

Key Table: PO_AGENTS

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`.

Key Takeaway

The core logic of this query is the join condition: 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)`.

The commented-out lines are useful filters. You can uncomment them to search for a specific employee by email address or to filter by a specific business group.