Search Results cpg_cp_po_hdr_mch_v




Overview
Explore in AI Chat

Overview

CPG_CP_PO_HDR_MCH_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, registered under the product GML - Process Manufacturing Logistics (Process Manufacturing Logistics). The view is described in the ETRM repository simply as a "PO Header view." Its functional purpose is to reconcile and compare purchase order header data originating in Oracle Purchasing (Oracle Financials) against the corresponding purchase order records that were created or migrated into the Process Manufacturing Logistics (GML) schema, which stores its own PO header information in PO_ORDR_HDR.

The view is essentially a mismatch and discrepancy report generator. Rather than presenting a single clean list of purchase orders, it returns the union of four distinct result sets, each tagged with a numeric MSG_CODE that identifies the nature of the problem or condition detected. This makes the view useful for data migration validation, integration troubleshooting, and audit reporting between the Oracle Purchasing and Process Manufacturing Logistics applications after an upgrade or migration.

Underlying Base Objects

The documented base objects referenced by this view are:

The view text joins these objects through a series of NOT EXISTS and EXISTS subqueries plus an inner join on CPG_ORAGEMS_MAPPING, and uses UNION (not UNION ALL) to combine the four selection branches.

Key Columns

  • PO_NO — the purchase order number. For Oracle Purchasing rows it is SEGMENT1; for GML-only rows the leading zeros are stripped with LTRIM.
  • ORA_LINE / GMS_LINE — placeholders, both returned as a single blank space, reserved for line-level reporting.
  • ORA_INT / GMS_INT — the Oracle Purchasing PO_HEADER_ID and the GML PO_ID respectively. A value of 0 indicates the record is absent from that system.
  • PO_DATE — the order date. For Oracle Purchasing rows this is APPS.PO_HEADERS_ALL.APPROVED_DATE. The search term "approved_date" maps directly to this column, which is the Oracle Purchasing approval timestamp. For GML-originated rows the column is instead populated from PO_ORDR_HDR.PO_DATE.
  • MSG_CODE — a numeric indicator of the discrepancy category: 1, 2, 3, or 4.

Common Use Cases and Queries

Typical scenarios include post-migration validation, reconciliation of purchase orders between Oracle Purchasing and GML, and locating orders whose approval date or line/cost totals diverge. MSG_CODE 1 flags standard PO headers in Oracle Purchasing with a CPG mapping but no matching GML record; MSG_CODE 2 flags GML records with no matching Oracle Purchasing header; MSG_CODE 3 flags matched records whose line counts differ (via GML_AUDIT_REPORTS.GET_OFI_LINE_COUNT vs. GET_GEMMS_LINE_COUNT); MSG_CODE 4 flags matched records whose total costs differ.

Sample query listing all mismatches, ordered by the approval-based PO_DATE:

  • SELECT po_no, ora_int, gms_int, po_date, msg_code FROM apps.cpg_cp_po_hdr_mch_v ORDER BY msg_code, po_date;
  • SELECT po_no, po_date FROM apps.cpg_cp_po_hdr_mch_v WHERE msg_code = 1 AND po_date >= SYSDATE - 30;
  • SELECT msg_code, COUNT(*) FROM apps.cpg_cp_po_hdr_mch_v GROUP BY msg_code;

The first two queries leverage the APPROVED_DATE-derived PO_DATE column exposed by the view, directly serving users searching on "approved_date."