Search Results modification_number

Overview

APPS.PO_EDA_CLM_DOC_NBR_V is a lightweight Oracle EBS view that consolidates document number identifiers used within the Procurement (Purchasing) module's contract management and draft workflow. Its purpose is to produce a single, unified list of document numbers drawn from two distinct sources: finalized purchasing documents held in PO_HEADERS_ALL that carry a user-defined attribute (UDA) template, and draft documents held in PO_DRAFTS. The view exposes exactly two columns — a document number and a flag indicating whether the row originates from the header table or from the drafts table.

This view is significant because it directly addresses the concept of the modification number. The second arm of the UNION returns MODIFICATION_NUMBER from PO_DRAFTS, aliased as ISMOD = 'Y'. Practically speaking, the view allows a caller to enumerate both the canonical CLM_DOCUMENT_NUMBER of a live purchasing document and the MODIFICATION_NUMBER generated for each draft change, distinguished by a simple one-character indicator. The view is typically consumed by EDA (Enterprise Data Archive), personalizations, or integration routines that need to reconcile or reference document and modification numbering without joining the underlying base tables directly.

Underlying Base Objects

The view is defined over two synonyms, which resolve to the following base objects:

• PO_HEADERS_ALL — the core purchasing document header table. The view filters this table with WHERE uda_template_id IS NOT NULL, restricting output to headers associated with a UDA template (the contract/CLM-enabled documents). The selected column is CLM_DOCUMENT_NUMBER.
• PO_DRAFTS — the table storing draft purchasing documents and their change proposals. The view selects MODIFICATION_NUMBER from this table without additional filtering.

The two result sets are combined with a UNION, and a literal flag column is added to each arm: 'N' ismod for rows from PO_HEADERS_ALL and 'Y' ismod for rows from PO_DRAFTS. Because it is a UNION (not UNION ALL), duplicate document numbers appearing in both arms are collapsed; callers should be aware of this behavior when relying on the ismod flag for reconciliation.

Key Columns

• CLM_DOCUMENT_NUMBER — the document number from PO_HEADERS_ALL for UDA-template headers. This is the first column position returned by the view.
• ISMOD — a single-character literal flag. A value of 'N' indicates the row originates from PO_HEADERS_ALL (a base/header document). A value of 'Y' indicates the row originates from PO_DRAFTS (a draft or modification record).

Note that in the union, the second column position is populated by MODIFICATION_NUMBER in the drafts arm but is aliased to ISMOD in the outer result set. This means the column carries dual semantic meaning depending on the row source: for header rows it is always 'N', while for draft rows the ISMOD alias masks the actual modification number value from PO_DRAFTS. Consumers should verify how this is projected at runtime when building queries against the live view.

Common Use Cases and Queries

Typical scenarios include validating that a modification number exists as a draft, listing all document and modification identifiers for a reconciliation report, or driving integrations that must distinguish live headers from drafts.

List all identifiers and their source:

• SELECT CLM_DOCUMENT_NUMBER, ISMOD FROM APPS.PO_EDA_CLM_DOC_NBR_V ORDER BY ISMOD, CLM_DOCUMENT_NUMBER;

Retrieve only draft modification numbers:

• SELECT CLM_DOCUMENT_NUMBER FROM APPS.PO_EDA_CLM_DOC_NBR_V WHERE ISMOD = 'Y';

Check whether a specific modification number is present:

• SELECT COUNT(*) FROM APPS.PO_EDA_CLM_DOC_NBR_V WHERE CLM_DOCUMENT_NUMBER = :modification_number;

Because the view performs a UNION over the full PO_HEADERS_ALL and PO_DRAFTS result sets, queries should always be filtered to remain performant. The UNION also removes duplicates, so the same identifier appearing in both sources will be returned once.