Search Results wsh_purge_source_numbers_v

Overview

WSH_PURGE_SOURCE_NUMBERS_V is a shipped Oracle E-Business Suite view owned by the APPS schema and registered against the WSH (Shipping Execution) product. Its purpose is narrow and operational: it exposes the distinct set of external source system codes and source document numbers that exist on delivery detail records in WSH_DELIVERY_DETAILS, restricted to source systems that are still enabled in the SOURCE_SYSTEM lookup and are not internal Shipping Execution or Order Capture codes. The view therefore acts as a driver or candidate list for purge and archival processes that operate against delivery details originating from external feeder systems such as Order Management, inventory interfaces, or third-party integrations.

Because the view filters out the OKE and WSH lookup codes, it deliberately surfaces only externally sourced delivery detail lines. This makes it useful for identifying which source systems contribute data that may be subject to purge criteria, and for presenting that set in a user-facing concurrent program or form without requiring the caller to write the lookup join manually. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2, and the definition is consistent across those releases.

Underlying Base Objects

The view is defined over two referenced objects, both resolved through APPS synonyms:

• WSH_DELIVERY_DETAILS (SYNONYM) — the primary fact object. The view selects from its SOURCE_CODE and SOURCE_HEADER_NUMBER columns.
• WSH_LOOKUPS (VIEW) — the lookup validation object. The view joins the lookup code in WSH_DELIVERY_DETAILS.SOURCE_CODE against WSH_LOOKUPS.LOOKUP_CODE where the lookup type is SOURCE_SYSTEM and the lookup is enabled, and then excludes the OKE and WSH codes.

The view text uses a DISTINCT projection over the join, so the result set is a de-duplicated list of source system / source number combinations rather than a row-per-delivery-detail listing. It does not expose delivery identifiers, item attributes, or quantities; its relationship to the base objects is strictly that of a lookup-enriched distinct extract.

Key Columns

• SOURCE_SYSTEM — the source code from WSH_DELIVERY_DETAILS.SOURCE_CODE, aliased from the delivery details table. Identifies the originating application or feeder system.
• SOURCE_ID — the source header number from WSH_DELIVERY_DETAILS.SOURCE_HEADER_NUMBER. In the view text SOURCE_ID and SOURCE_NUMBER are both mapped to SOURCE_HEADER_NUMBER, so these two columns carry identical values.
• SOURCE_NUMBER — the source header number, presented under a number-oriented alias for reporting convenience.
• MEANING — the descriptive meaning of the source code, taken from WSH_LOOKUPS.MEANING, providing the user-friendly label for SOURCE_SYSTEM.

Common Use Cases and Queries

Typical usage is to drive or restrict purge and archival logic to externally sourced delivery details, or to present a validated pick list of source systems in a concurrent program parameter.

• Listing all external source systems and numbers present on delivery details:

SELECT source_system, meaning, source_number FROM apps.wsh_purge_source_numbers_v ORDER BY source_system, source_number;

• Retrieving the descriptive meaning for a specific source system:

SELECT DISTINCT source_system, meaning FROM apps.wsh_purge_source_numbers_v WHERE source_system = :p_source_code;

• Counting the distinct source numbers per external system to size a purge batch:

SELECT source_system, COUNT(DISTINCT source_number) src_cnt FROM apps.wsh_purge_source_numbers_v GROUP BY source_system;

Because the view already applies the SOURCE_SYSTEM lookup filter, excludes OKE and WSH, and enforces DISTINCT, consumers should avoid re-implementing those joins in their own SQL. For reconciliation against the source of record, the view can be joined back to WSH_DELIVERY_DETAILS on SOURCE_CODE and SOURCE_HEADER_NUMBER to recover the underlying delivery detail rows for the selected source numbers.