Search Results cpg_oragems_mapping




Overview
Explore in AI Chat

Overview

CPG_ORAGEMS_MAPPING is a table owned by the GML schema within the Oracle Process Manufacturing Logistics (OPM Logistics) module. Its purpose is to maintain the association between a standard or planned purchase order originating in Oracle Purchasing and the corresponding purchase order or blanket purchase order (BPO) document created inside Oracle Process Manufacturing. This cross-referencing mechanism is required because OPM performs its own purchasing and receipt processing for process-manufactured goods, yet Oracle Purchasing remains the entry point for many procurement transactions. Without a mapping table of this kind, reconciliation between the two purchasing models would be ambiguous.

The table is physically documented with 19 columns in the ETRM 12.2.2 schema, and it is marked VALID. The heuristic Data Vault classification mined from the foreign-key structure is standalone. In modeling terms, this suggests the object behaves as a link-style association entity that ties together multiple business keys rather than as a pure hub or satellite. Its distinguishing characteristic is a composite unique index named CPG_ORAGEMS_MAPPING_U1, built on the columns PO_HEADER_ID, PO_LINE_ID, and PO_LINE_LOCATION_ID, which together identify a unique purchasing line-location distribution.

Key Information Stored

The primary key is the composite business key formed by PO_HEADER_ID, PO_LINE_ID, and PO_LINE_LOCATION_ID through the unique index CPG_ORAGEMS_MAPPING_U1. These three columns represent the Oracle Purchasing header, line, and line-location identifiers of the originating purchase order.

The most significant remaining columns include:

  • PO_ID — foreign key referencing PO_ORDR_HDR, identifying the OPM purchase order header side of the mapping.
  • LINE_ID — the corresponding OPM purchase order line identifier.
  • BPO_ID — foreign key referencing PO_BPOS_HDR, identifying the OPM blanket purchase order header.
  • BPO_LINE_ID — the OPM blanket purchase order line identifier.
  • PO_RELEASE_ID — foreign key referencing PO_RELEASES_ALL, linking the mapping to a purchasing release when the source document is a release against a BPO.
  • PO_NO — the human-readable purchase order number.
  • RELEASE_NUM — the release number associated with the purchasing document.
  • PO_STATUS — the current status of the mapped purchase order.
  • TRANSACTION_TYPE — indicates the nature of the mapping transaction (for example, standard purchase order versus blanket order creation).
  • MIGRATION_IND — a flag indicating whether the record was generated through a data migration rather than live processing.
  • TIME_STAMP, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard EBS audit columns tracking who created or last modified the row and when.

Common Use Cases and Queries

Typical use cases center on reconciling OPM purchase orders back to their Oracle Purchasing source. A frequent query pattern joins the mapping to PO_ORDR_HDR to recover the full OPM PO header, or to PO_BPOS_HDR to trace blanket order origins. Reporting often filters on PO_STATUS or MIGRATION_IND to isolate migrated versus organically created records.

  • Verifying that every OPM PO has a corresponding Oracle Purchasing entry, detecting orphaned or missing mappings.
  • Auditing releases by joining PO_RELEASE_ID to PO_RELEASES_ALL to confirm release-level traceability.
  • Identifying migrated records via MIGRATION_IND for data quality review after upgrades or conversions.
  • Reconciling blanket purchase order usage across the two purchasing engines.
  • Producing purchasing traceability reports for process manufacturing procurement.

A representative query might select PO_NO, PO_STATUS, and BPO_ID from CPG_ORAGEMS_MAPPING filtered by PO_STATUS, or join on PO_HEADER_ID and PO_LINE_ID to retrieve line-level detail from Oracle Purchasing tables.

Related Objects

The mapping table's documented foreign-key relationships and its primary key link it to the following significant objects:

  • PO_ORDR_HDR — joined via PO_ID; supplies the OPM purchase order header.
  • PO_BPOS_HDR — joined via BPO_ID; supplies the OPM blanket purchase order header.
  • PO_RELEASES_ALL — joined via PO_RELEASE_ID; supplies purchasing release details.
  • PO_HEADERS_ALL — the Oracle Purchasing header referenced by the PO_HEADER_ID business key column.
  • PO_LINES_ALL — the corresponding Oracle Purchasing line referenced by PO_LINE_ID.
  • PO_LINE_LOCATIONS_ALL — the ship-to or line-location record referenced by PO_LINE_LOCATION_ID.
  • PO_BPOS_HDR and internal OPM line tables referenced through LINE_ID and BPO_LINE_ID round out the OPM-side dependencies.

Together these relationships make CPG_ORAGEMS_MAPPING the central bridge between Oracle Purchasing and OPM Logistics purchasing documents.