Results for “orig_sys_document_ref”

50+ results




Overview
Explore in AI Chat

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

Overview

OE_EM_PURGE_LOV_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the ONT (Order Management) product family. It is a UNION-based view whose name indicates its role as a List of Values (LOV) source for purge-related functionality within the Order Management integration flows, specifically the Enterprise Manager (EM) or electronic messaging tier. The view consolidates identification attributes — order source, originating system document reference, operating unit, and sold-to customer — across four distinct interfacing and acknowledgement objects, presenting them as a single queryable set for purge processing. Rather than storing data itself, OE_EM_PURGE_LOV_V provides a unified projection over interface and acknowledgement tables so that concurrent purge programs, forms, or administrative LOVs can select candidate records for cleanup without querying each base table independently. It is documented as VALID in ETRM for both Oracle EBS 12.1.1 and 12.2.2, and it carries Oracle Proprietary, Confidential Information classification, meaning it is reference metadata for administrators and technical consultants rather than a customer-extensible object.

Underlying Base Objects

The view definition is a four-way UNION of SELECT statements, each drawn from a documented synonym resolved to the following base objects:

Each UNION branch projects the same four key attributes (ORDER_SOURCE_ID, ORIG_SYS_DOCUMENT_REF, ORG_ID, SOLD_TO_ORG_ID) from its respective base table and appends a hard-coded literal as SOURCE_CODE. Because the branches are combined with UNION (not UNION ALL), duplicate combinations of the four attributes appearing in multiple base tables are collapsed into a single row. This design is consistent with an LOV intended to enumerate purge candidates per integration document rather than per underlying record. All four base objects are referenced through SYNONYM objects in the APPS schema, which is standard for EBS applications views covering multiple data origins.

Key Columns

  • ORDER_SOURCE_ID — the order source identifier associated with the interface or acknowledgement record; this is the primary grain of the view's candidate identification logic.
  • ORIG_SYS_DOCUMENT_REF — the original system document reference, i.e., the identifier assigned by the external or upstream system that submitted the transaction; essential for tracing integration records and matching purge requests to source documents.
  • ORG_ID — the operating unit (multi-org) identifier, enabling purge operations to be scoped to a specific business unit.
  • SOLD_TO_ORG_ID — the sold-to customer or party identifier used to scope purge candidates by customer.
  • SOURCE_CODE — a derived literal column that identifies which base object contributed the row; values are 'OE_HEADERS_INTERFACE', 'OE_LINES_INTERFACE', 'OE_HEADER_ACKS', and 'OE_EM_INFORMATION'. This column is the principal discriminator and is not stored in any base table.

Common Use Cases and Queries

Typical use is populating a purge LOV where an administrator selects the source (ORDER_SOURCE_ID or ORIG_SYS_DOCUMENT_REF) whose interface and acknowledgement records are to be removed or reviewed. A frequent pattern is filtering to a single source type:

  • Enumerate all purge candidates for one operating unit:

    SELECT order_source_id, orig_sys_document_ref, sold_to_org_id, source_code FROM oe_em_purge_lov_v WHERE org_id = :p_org_id ORDER BY source_code, order_source_id;

  • Display the distinct originating system references across all four sources for an LOV:

    SELECT DISTINCT orig_sys_document_ref FROM oe_em_purge_lov_v ORDER BY 1;

  • Cross-check counts per source code before running a purge:

    SELECT source_code, COUNT(*) FROM oe_em_purge_lov_v GROUP BY source_code;

Because the view is a UNION of interface and acknowledgement tables, it should be treated strictly as a discovery and selection mechanism; actual deletion remains the responsibility of the corresponding purge programs operating on the individual base tables. Query performance depends on indexes on ORDER_SOURCE_ID, ORG_ID, and ORIG_SYS_DOCUMENT_REF in the underlying interface tables, and the UNION distinct operation can be expensive on high-volume interface data, so WHERE clauses should always constrain at least ORG_ID or SOURCE_CODE when used interactively.