Results for “icpurch_class”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CPG_PURCHASING_INTERFACE is a table owned by the GML schema (Process Manufacturing Logistics) in Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to serve as an interface table for OPM Common Purchasing, bridging purchasing documents originating in Oracle Purchasing with the Process Manufacturing Logistics and OPM inventory/receiving layers. The table physically contains 96 columns and carries purchase order, blanket purchase order, release, vendor, item, quantity, pricing, currency, contract, hold, and status attributes, along with standard WHO columns such as CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, and LAST_UPDATED_BY. The presence of DELETE_MARK and INVALID_IND flags, plus IN_USE and PRINT_COUNT, indicates the table supports concurrent processing and record lifecycle management.

The metadata provides a heuristic Data Vault classification of standalone, meaning the table is not a native star-schema construct but functions as an operational interface/staging object. Where modeling is considered, it can be treated as a satellite-like staging table that records the current state of purchasing interface transactions, with business keys supplied by the purchasing document identifiers rather than a classic warehouse hub.

Key Information Stored

The table's documented columns include a surrogate transaction key and multiple business-key candidates representing purchasing documents. The most significant columns include:

• TRANSACTION_ID — the surrogate primary key for each interface record.
• TRANSACTION_TYPE — classifies the interface transaction being processed.
• PO_HEADER_ID, PO_LINE_ID, PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID — the purchasing document identifiers linking the interface record to PO header, line, shipment, and distribution levels.
• PO_NO, LINE_NO, RELEASE_NUM — human-readable purchasing document numbers.
• BPO_NO, BPO_ID, BPO_RELEASE_NUMBER — blanket purchase order references.
• PO_ID, PO_RELEASE_ID — additional purchasing identifiers.
• PO_STATUS, LINE_STATUS, BPO_STATUS — status of the purchasing document and line.
• ITEM_NO, ITEM_DESC, ORDER_QTY1, ORDER_UM1, NET_PRICE, EXTENDED_PRICE — item, quantity, and pricing details.
• ORGN_CODE, FROM_WHSE, TO_WHSE — inventory organization and warehouse routing.
• BILLING_CURRENCY, PURCHASE_EXCHANGE_RATE, MUL_DIV_SIGN — currency conversion attributes.
• VENDOR_NO via OF_PAYVEND_SITE_ID, OF_SHIPVEND_SITE_ID — vendor and vendor site references.
• EXPORTED_DATE and IN_USE — indicate whether the record has been exported downstream and whether it is currently being processed.

Common Use Cases and Queries

Typical scenarios include reconciliation of OPM purchasing documents with Oracle Purchasing, tracking of interface processing status, and reporting on purchase order quantities, prices, and receiving expectations. A common starting query retrieves outstanding, non-exported records:

• SELECT TRANSACTION_ID, PO_NO, LINE_NO, PO_STATUS, ITEM_NO, ORDER_QTY1 FROM CPG_PURCHASING_INTERFACE WHERE EXPORTED_DATE IS NULL AND NVL(DELETE_MARK,0) = 0;
• Join to PO_ORDR_HDR on PO_ID to enrich with header details.
• Aggregate by ORGN_CODE and ITEM_NO to report purchased quantities versus received quantities.
• Filter on PO_STATUS, LINE_STATUS, or BPO_STATUS for open-versus-closed analysis.
• Use IN_USE and LAST_UPDATE_DATE to identify stale or stuck interface rows.

Related Objects

The documented foreign-key relationships identify the primary parent objects. The most significant related tables include:

• PO_RELEASES_ALL — joined via CPG_PURCHASING_INTERFACE.PO_RELEASE_ID = PO_RELEASES_ALL.PO_RELEASE_ID.
• PO_ORDR_HDR — joined via CPG_PURCHASING_INTERFACE.PO_ID = PO_ORDR_HDR.PO_ID.
• PO_BPOS_HDR — joined via CPG_PURCHASING_INTERFACE.BPO_ID = PO_BPOS_HDR.BPO_ID.

In practice, these parent tables and the OPM Common Purchasing concurrent programs that populate and consume CPG_PURCHASING_INTERFACE form the broader integration surface. Reporting or troubleshooting work should begin from these joins, using TRANSACTION_ID as the unique record identifier and the PO/BPO identifiers as business keys.