Search Results document_version

Overview

OE_ORDER_VERSIONS_V is a read-only database view owned by the APPS schema within the ONT – Order Management module of Oracle E-Business Suite (documented for 12.1.1 and 12.2.2). It presents a consolidated, version-aware list of sales order headers together with a signature indicator and an archival flag. The view is designed to answer a single, recurring business question: which versions of a given sales order exist, has each version been signed by the customer, and is that version still live or has it been superseded and archived?

By abstracting the union of the current order header table and the historical header table into one flat record set, the view provides a stable integration and reporting surface. Extension developers, Discoverer/BI Publisher reports, and outbound interfaces can query a single object rather than performing the union logic themselves, which reduces divergence between reporting and functional behavior. Because it originates from the ETRM (E-Business Suite Technical Reference Manual), it is treated as a documented, supported object rather than a private implementation detail.

Underlying Base Objects

The view is defined over two documented base objects, referenced in the APPS schema as synonyms:

• OE_ORDER_HEADERS_ALL – the transactional header table holding current (non-archived) sales orders. Rows from this table are emitted with a literal ARCHIVED_YN of 'N'.
• OE_ORDER_HEADER_HISTORY – the header history table that stores prior versions of an order header. Rows are included only when VERSION_FLAG = 'Y', and are emitted with a literal ARCHIVED_YN of 'Y'.

The two branches are combined with UNION (not UNION ALL), so duplicate projections across current and historical rows collapse to a single row. The final result set is ordered by VERSION_NUMBER, descending. In practice this means the most recent version appears first, and the current header generally represents the highest version number.

Key Columns

The column list documented in ETRM is DOCUMENT_ID, DOCUMENT_TYPE, DOCUMENT_VERSION, SIGNED_YN, and ARCHIVED_YN. These are aliases mapped from the underlying select list, and the mapping is important when writing queries:

• DOCUMENT_ID – aliases HEADER_ID, the unique identifier of the sales order header.
• DOCUMENT_TYPE – aliases NVL(SALES_DOCUMENT_TYPE_CODE,'O'); it defaults to 'O' when the source value is null.
• DOCUMENT_VERSION – aliases VERSION_NUMBER, the sequential version of the header.
• SIGNED_YN – derives from CUSTOMER_SIGNATURE_DATE. The expression DECODE(NVL(TO_CHAR(CUSTOMER_SIGNATURE_DATE),'N'),'N','N','Y') returns 'N' when the signature date is null and 'Y' when any date value exists. This is the column most relevant to users searching on "signed_yn", and it is the sole indicator of customer sign-off exposed by the view.
• ARCHIVED_YN – a literal flag: 'N' for rows originating from OE_ORDER_HEADERS_ALL and 'Y' for rows originating from OE_ORDER_HEADER_HISTORY.

Common Use Cases and Queries

Typical scenarios include signature tracking for order acceptance, version comparison and audit, and identifying archived versus active versions in downstream interfaces.

List all versions of a specific order with their status:

• SELECT document_id, document_type, document_version, signed_yn, archived_yn FROM oe_order_versions_v WHERE document_id = :header_id ORDER BY document_version DESC;

Find orders still awaiting customer signature:

• SELECT document_id, document_version, archived_yn FROM oe_order_versions_v WHERE signed_yn = 'N' AND archived_yn = 'N';

Count signed versus archived versions for a range of orders:

• SELECT document_id, signed_yn, archived_yn, COUNT(*) FROM oe_order_versions_v GROUP BY document_id, signed_yn, archived_yn;

Because SIGNED_YN is a derived character flag, it should be filtered with character literals and never treated as a date column. ARCHIVED_YN reliably partitions current from historical rows regardless of version number, making it the recommended predicate for active-order reporting.