Search Results po_commodity_grants

Overview

The PO_COMMODITY_GRANTS table is a core data object within the Oracle E-Business Suite Purchasing (PO) module, specifically for versions 12.1.1 and 12.2.2. It functions as a security and authorization control table, governing user access to specific commodity classifications within the procurement process. As indicated by its description, "Roles granted to employees for Commodities," this table establishes and stores the explicit permissions that link individual employees (or system users) to defined commodities, thereby enabling role-based access control over purchasing activities. Its existence is critical for enforcing business rules that restrict which buyers or procurement personnel can create or manage purchase orders for particular types of goods or services.

Key Information Stored

The table's structure is defined by a composite primary key, ensuring a unique combination of three essential identifiers for each grant record. The critical columns are:

• COMMODITY_ID: A foreign key referencing PO_COMMODITIES_B. This column identifies the specific commodity code or classification to which access is being granted.
• PERSON_ID: This column holds the identifier for the employee (system user) who is receiving the access grant. It typically corresponds to the PERSON_ID in the PER_ALL_PEOPLE_F table.
• MENU_ID: A foreign key referencing FND_MENUS. This column specifies the particular menu or functional role being granted in the context of the commodity, defining the scope of allowed actions (e.g., view, create, approve).

Together, these columns create a definitive record stating that a given employee (PERSON_ID) has been assigned a specific functional role (MENU_ID) for a particular commodity (COMMODITY_ID).

Common Use Cases and Queries

The primary use case is managing and auditing commodity-specific security. A common operational query would retrieve all grants for a specific employee to understand their purchasing authority, or list all employees authorized for a critical commodity. For reporting and administration, one might join this table with employee and commodity descriptions. Sample SQL patterns include:

• Identifying authorized employees for a commodity: SELECT p.person_id, p.full_name FROM po_commodity_grants g, per_all_people_f p WHERE g.person_id = p.person_id AND g.commodity_id = <commodity_id>;
• Auditing all commodity grants for compliance: SELECT c.commodity_code, p.full_name, m.user_menu_name FROM po_commodity_grants g, po_commodities_b c, per_all_people_f p, fnd_menus_vl m WHERE g.commodity_id = c.commodity_id AND g.person_id = p.person_id AND g.menu_id = m.menu_id ORDER BY c.commodity_code;

These queries are foundational for security audits, role provisioning, and troubleshooting access issues within Purchasing.

Related Objects

PO_COMMODITY_GRANTS is centrally linked to several key EBS objects, as defined by its foreign key constraints:

• PO_COMMODITIES_B: The master table for commodity definitions. The COMMODITY_ID foreign key ensures that every grant corresponds to a valid, existing commodity.
• FND_MENUS: The Oracle Applications core table storing menu definitions. The MENU_ID foreign key ties the commodity grant to a specific set of application functions and responsibilities.
• PER_ALL_PEOPLE_F: While not a formal foreign key in the provided metadata, the PERSON_ID column is logically related to this Human Resources table to obtain employee names and details.

This table is typically accessed via the standard Oracle Purchasing user interface for security administration, and its data integrity is maintained through the referenced parent tables.