Search Results oe_em_information




Overview
Explore in AI Chat

Overview

APPS.OE_EM_PURGE_LOV_V is a union view in the Oracle E-Business Suite Order Management (ONT) schema, exposed through the APPS synonym layer. Its name reflects its purpose: it supplies the list of values (LOV) used by the Order Management purge and archival programs when operators select records to remove or archive. The view consolidates four distinct data sources that share a common identity tuple — order_source_id, orig_sys_document_ref, org_id, and sold_to_org_id — so that a single query can surface every document reference known to the Order Management interface, acknowledgement, and Enterprise Manager (EM) tables.

The view is read-only and exists purely as a reporting and selection surface. It does not persist data, enforce constraints, or participate in transactional processing. Because the underlying sources span both the open interface tables and the acknowledgement tables, the view is most useful in integration support and cleanup operations, where the originating external document reference (orig_sys_document_ref) is the value most frequently searched for.

Underlying Base Objects

The view text is a four-way UNION ALL-style combination (written as UNION) over the following documented base objects, each referenced in ETRM 12.2.2 as a SYNONYM owned by APPS:

All four objects are referenced through public/APPS synonyms, meaning the view resolves against the base tables during runtime execution. Because the four branches expose identical column lists, Oracle can merge and filter the branches efficiently when predicates are applied to the identity columns.

Key Columns

  • ORDER_SOURCE_ID — identifier of the source system from which the order or document originated; used to segregate records by external application or trading partner.
  • ORIG_SYS_DOCUMENT_REF — the originating system's document reference, the primary business key for externally sourced orders. This is the column most commonly used when searching for a specific imported order.
  • ORG_ID — the operating unit identifier, enabling multi-org security filtering and LOV restriction by organization.
  • SOLD_TO_ORG_ID — the sold-to customer or party identifier, allowing the LOV to be narrowed by customer.
  • The fifth, literal column — a string constant naming the source branch (OE_HEADERS_INTERFACE, OE_LINES_INTERFACE, OE_HEADER_ACKS, or OE_EM_INFORMATION). This discriminator tells the user where the record currently resides, which is essential when deciding whether to purge an interface record or an acknowledgement record.

Common Use Cases and Queries

The principal use case is locating a document in the purge/archival LOV by its external reference. A typical query filters on orig_sys_document_ref to see whether the document exists in the interface, acknowledgement, or EM tables:

  • SELECT order_source_id, orig_sys_document_ref, org_id, sold_to_org_id, source
    FROM apps.oe_em_purge_lov_v
    WHERE orig_sys_document_ref = '&document_ref';
  • SELECT source, COUNT(*)
    FROM apps.oe_em_purge_lov_v
    WHERE org_id = :org_id
    GROUP BY source;     — inventory of where references currently live.
  • SELECT * FROM apps.oe_em_purge_lov_v
    WHERE sold_to_org_id = :customer_id
    AND org_id = :org_id;     — customer-scoped purge candidates.

Because the view spans interface and acknowledgement data, it supports reconciliation before purge: an operator can confirm that a reference is safe to remove, identify duplicate references across sources, and verify operating unit context. Always restrict queries by ORG_ID to respect multi-org access controls, and treat the view as read-only diagnostic tooling rather than a data source for persistent storage.