Search Results clm_document_number

Overview

PO_EDA_CLM_DOC_NBR_V is an APPS-owned database view in the Oracle E-Business Suite Purchasing (PO) module. It presents a consolidated list of document numbers relevant to Enterprise Data Auditing (EDA) and Contract Lifecycle Management (CLM) processing. The view exists to serve as a reference source of "document numbers" that EDA-related processes, integrations, or reports can enumerate and cross-reference. Rather than exposing full document detail, the view returns a lightweight two-column projection: the document number itself and a flag indicating whether that number originates from a standard purchasing document header or from a draft (modification) record.

Its role is best understood as a lookup or feed view rather than a transactional or detail view. In Oracle EBS reporting and integration contexts, such a view is typically used to drive audit scanning, to identify which documents have associated user-defined attribute (UDA) templates, and to unify two otherwise separate numbering schemes—standard purchase order document numbers and draft modification numbers—into a single list.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is owned by APPS and is defined over two base objects, both referenced through synonyms in the APPS schema:

• PO_HEADERS_ALL — the Purchasing document header table, one of the core transactional tables in the PO module.
• PO_DRAFTS — the table that stores draft purchasing documents and their modification records.

The view text combines these via a UNION of two SELECT statements. The first branch selects CLM_DOCUMENT_NUMBER from PO_HEADERS_ALL and restricts rows with the predicate UDA_TEMPLATE_ID IS NOT NULL, meaning only headers that have been associated with a UDA (user-defined attribute) template contribute to the result. The second branch selects MODIFICATION_NUMBER from PO_DRAFTS. In both branches a literal flag is projected as the ISMOD column. Because the view is built with UNION (not UNION ALL), duplicate document numbers appearing across both branches are eliminated.

Key Columns

• CLM_DOCUMENT_NUMBER — The document number. In the first branch this is drawn from the PO_HEADERS_ALL document number corresponding to headers that carry a UDA template; in the second branch it is the modification number from PO_DRAFTS. The column therefore acts as a harmonized document identifier across the two sources.
• ISMOD — A single-character indicator distinguishing the origin of the row. A value of 'N' is emitted for records sourced from PO_HEADERS_ALL, and a value of 'Y' is emitted for records sourced from PO_DRAFTS. This flag allows consumers to filter or branch their logic based on whether a document number belongs to a baseline header or a draft/modification.

The view exposes no additional descriptive columns such as vendor, date, or amount; its scope is deliberately narrow, limited to the document number and the source indicator.

Common Use Cases and Queries

Typical usage involves enumerating candidate documents for EDA/CLM auditing or populating a validation list of document numbers. A simple retrieval of all rows follows:

• SELECT CLM_DOCUMENT_NUMBER, ISMOD FROM APPS.PO_EDA_CLM_DOC_NBR_V;
• To isolate standard header-based document numbers: SELECT CLM_DOCUMENT_NUMBER FROM APPS.PO_EDA_CLM_DOC_NBR_V WHERE ISMOD = 'N';
• To isolate draft/modification numbers: SELECT CLM_DOCUMENT_NUMBER FROM APPS.PO_EDA_CLM_DOC_NBR_V WHERE ISMOD = 'Y';
• To test existence of a specific document number: SELECT 1 FROM APPS.PO_EDA_CLM_DOC_NBR_V WHERE CLM_DOCUMENT_NUMBER = :doc_num;

Because the view performs a UNION and reads from potentially large transactional tables, queries benefit from filtering on CLM_DOCUMENT_NUMBER wherever possible. When broader document context is required, the view is generally joined back to PO_HEADERS_ALL on the document number for header-level details.