Search Results ora_int




Overview
Explore in AI Chat

Overview

The view CPG_CP_BLAN_PO_HDR_MCH_V is an APPS-owned database view within the GML - Process Manufacturing Logistics product family. Its documented description is "Blanket PO Header view," and it functions as a reconciliation and matching utility that compares Oracle Purchasing blanket purchase order structures against their corresponding Process Manufacturing Logistics (GML) representations. In Oracle EBS 12.1.1 and 12.2.2, this view supports the integration between the standard Oracle Purchasing module and Oracle Process Manufacturing by surfacing records that either exist on only one side of the boundary or that disagree between the two systems.

The view consolidates the outcome of four set operations (a UNION of four SELECT statements), each tagged with a distinct MSG_CODE. Each branch represents a specific reconciliation condition, allowing downstream audit reports to identify blanket PO header discrepancies between the Purchasing tables (PO_HEADERS_ALL, PO_RELEASES_ALL) and the GML purchasing tables (PO_ORDR_HDR). Because the view uses SELECT DISTINCT in every branch, it returns a de-duplicated set of PO header identifiers and associated metadata.

Underlying Base Objects

The view is defined over the following documented base objects:

  • PO_HEADERS_ALL (SYNONYM) — the Oracle Purchasing blanket purchase order header table, providing SEGMENT1, PO_HEADER_ID, and APPROVED_DATE.
  • PO_RELEASES_ALL (SYNONYM) — the release/agreement schedule table, joined to PO_HEADERS_ALL on PO_HEADER_ID in several branches.
  • PO_ORDR_HDR (SYNONYM) — the GML purchasing order header, supplying GML-side identifiers such as PO_NO, PO_ID, and BPO_ID.
  • CPG_ORAGEMS_MAPPING (SYNONYM) — the cross-reference mapping that links Oracle PO_HEADER_ID values to GML PO_ID values.
  • GML_AUDIT_REPORTS (PACKAGE) — a PL/SQL package exposing helper functions such as GET_PO_NUM, GET_OFI_BLINE_COUNT, GET_GEMMS_BLINE_COUNT, and GET_OFI_BTOTAL_COST, used to normalize and compare values across the two schemas.

Together these objects allow the view to correlate standard Purchasing blanket PO data with equivalent GML data by way of the mapping table and the audit package's normalization functions.

Key Columns

  • PO_NO — the blanket purchase order number. For Oracle-side rows it is sourced from PO_HEADERS_ALL.SEGMENT1; for GML-side rows it is derived through GML_AUDIT_REPORTS.GET_PO_NUM(PO_NO).
  • ORA_LINE — a placeholder literal (single space) reserved for line-level detail; always blank at this header level.
  • GMS_LINE — a placeholder literal (single space) reserved for GML line-level detail.
  • ORA_INT — the Oracle internal identifier, populated with PO_HEADER_ID where an Oracle-side header exists, otherwise 0.
  • GMS_INT — the GML internal identifier, populated with PO_ID or BPO_ID where a GML-side record exists, otherwise 0.
  • PO_DATE — the blanket PO approval date (APPROVED_DATE) on the Oracle side, or PO_DATE on the GML side.
  • MSG_CODE — a numeric reconciliation code indicating which branch produced the row: 1 for Oracle-only headers, 2 for GML-only headers, 3 for line-count mismatches, and 4 for total-cost mismatches.

Common Use Cases and Queries

Typical use cases include auditing blanket PO synchronization between Purchasing and GML during migration or post-upgrade validation, and troubleshooting discrepancies in release line counts and cumulative costs. The MSG_CODE allows users to categorize the exact nature of each mismatch.

A representative query listing all GML-only blanket headers is:

  • SELECT PO_NO, GMS_INT, PO_DATE FROM APPS.CPG_CP_BLAN_PO_HDR_MCH_V WHERE MSG_CODE = 2;

To isolate cost mismatches between the two systems:

  • SELECT PO_NO, ORA_INT, GMS_INT FROM APPS.CPG_CP_BLAN_PO_HDR_MCH_V WHERE MSG_CODE = 4 ORDER BY PO_NO;

To produce a consolidated discrepancy tally by category:

  • SELECT MSG_CODE, COUNT(*) FROM APPS.CPG_CP_BLAN_PO_HDR_MCH_V GROUP BY MSG_CODE;

Because the view resolves join keys and normalization through the GML_AUDIT_REPORTS package and the CPG_ORAGEMS_MAPPING table, queries against it are best targeted at reconciliation reporting rather than high-volume transactional access.