Search Results po_commodities_uk1

Overview

The PO_COMMODITIES_B table is a core master data table within the Oracle E-Business Suite Purchasing (PO) module. It serves as the base table for storing the definition of commodities, which are categories or classifications used to group and manage purchased items, particularly for purposes of reporting, analysis, and control. This table is fundamental for organizations that need to track procurement spend and manage supplier relationships by commodity type, such as IT Hardware, Office Supplies, or Professional Services. Its role is to provide a standardized, reusable list of commodity codes that can be associated with items, purchase orders, and agreements throughout the procurement lifecycle.

Key Information Stored

The table's structure centers on a unique commodity identifier and code. The primary technical identifier is the COMMODITY_ID column, which is the system-generated primary key (PO_COMMODITIES_B_PK). The primary business identifier is the COMMODITY_CODE column, which holds the unique, user-defined alphanumeric code for the commodity and is enforced by a unique key constraint (PO_COMMODITIES_UK1). While the ETRM excerpt does not list all columns, standard practice indicates this base table typically contains core attributes like ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE for controlling record availability, and CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY for auditing. Descriptive, translatable text (e.g., COMMODITY_NAME) is stored in the related PO_COMMODITIES_TL table.

Common Use Cases and Queries

A primary use case is generating spend analysis reports segmented by commodity. Administrators also use this table to maintain the commodity catalog. Common SQL patterns include querying active commodities for use in list of values (LOVs) within forms or identifying commodities used on recent purchase documents.

• Listing Active Commodities: SELECT commodity_id, commodity_code FROM po_commodities_b WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1) AND enabled_flag = 'Y' ORDER BY commodity_code;
• Finding Commodities on Purchase Orders: SELECT DISTINCT pc.commodity_code FROM po_headers_all poh, po_lines_all pol, po_commodities_b pc WHERE poh.po_header_id = pol.po_header_id AND pol.commodity_id = pc.commodity_id AND poh.segment1 = '<PO_NUMBER>';

Related Objects

As indicated by the foreign key relationships in the metadata, PO_COMMODITIES_B is central to several related objects. The PO_COMMODITIES_TL table provides translated descriptions for the commodities. The PO_COMMODITY_CATEGORIES table links commodities to item categories, enabling detailed classification. The PO_COMMODITY_GRANTS table manages funding source (grant) associations with specific commodities. In application logic, this base table is also referenced by key purchasing entities like purchase order lines (PO_LINES_ALL.COMMODITY_ID) and blanket agreement lines, making it integral to transactional data integrity and reporting.