Search Results last_ack_code




Overview
Explore in AI Chat

Overview

AMS_OE_ORDER_HEADERS_V is a reporting and integration view belonging to the Oracle Marketing (AMS) product family in Oracle E-Business Suite. Its documented description is simply "Order Headers," and its purpose is to expose the header-level attributes of sales orders originating in Oracle Order Management (OE) in a form usable by marketing and downstream reporting consumers. The view presents a denormalized picture of an order header, joining the base order header record to lookups for order type, payment terms, customer account, and customer party so that consumers do not need to reconstruct those joins themselves.

The stored metadata notes "Not implemented in this database" for the DBA data section, meaning the view text is captured in the ETRM reference but the object was not present in the reference instance at extraction time. Its definition is therefore documented by its SQL text rather than by instance-level DDL. The view is relevant to EBS 12.1.1 and 12.2.2, where the underlying OE and HZ tables follow the same core structures.

Underlying Base Objects

The view text documents a single inline SELECT with no declared view columns, defined over a five-table join:

Because the fourth and fifth joins are required (standard inner joins), orders lacking a resolvable sold-to account or party are excluded. The ETRM metadata lists no separately documented base objects, so the join path above is the authoritative structural reference.

Key Columns

The view exposes the order header identifier and descriptive keys (HEADER_ID, ORDER_NUMBER, ORIG_SYS_DOCUMENT_REF, VERSION_NUMBER), the order type identity and translated NAME, and a comprehensive set of commercial attributes: ORDERED_DATE, REQUEST_DATE, PRICING_DATE, EXPIRATION_DATE, transactional currency and conversion details, price list, tax exemption fields, and shipping controls such as SHIP_TOLERANCE_ABOVE and SHIP_TOLERANCE_BELOW.

Party and party-site columns include SOLD_TO_ORG_ID, SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, DELIVER_TO_ORG_ID, SHIP_FROM_ORG_ID and SOLD_FROM_ORG_ID, plus the four associated contact identifiers. Status flags CANCELLED_FLAG, OPEN_FLAG and BOOKED_FLAG support filtering by order lifecycle state. Payment attributes include PAYMENT_TERM_ID, PAYMENT_TYPE_CODE, PAYMENT_AMOUNT, CHECK_NUMBER, and stored credit card columns. Multi-org filtering is available through ORG_ID.

Regarding the searched term first_ack_code: the view includes FIRST_ACK_CODE together with FIRST_ACK_DATE, LAST_ACK_CODE and LAST_ACK_DATE. These columns carry the first and most recent acknowledgement status codes and timestamps returned by the order acknowledgement cycle, allowing consumers to distinguish orders that have been acknowledged successfully from those that have not.

Common Use Cases and Queries

Typical uses include marketing campaign attribution reporting, order intake dashboards, and integration extracts that require order headers with customer names resolved. Filtering on BOOKED_FLAG = 'Y' or on ORG_ID is common.

  • Orders awaiting or failing acknowledgement: SELECT ORDER_NUMBER, FIRST_ACK_CODE, FIRST_ACK_DATE FROM AMS_OE_ORDER_HEADERS_V WHERE FIRST_ACK_CODE IS NULL;
  • Booked orders by customer: SELECT ORDER_NUMBER, PARTY_NAME, ORDERED_DATE FROM AMS_OE_ORDER_HEADERS_V WHERE BOOKED_FLAG = 'Y' AND ORG_ID = :org_id;
  • Order type distribution: SELECT NAME, COUNT(*) FROM AMS_OE_ORDER_HEADERS_V GROUP BY NAME;

Because the view filters to TRANSACTION_TYPE_CODE = 'ORDER', it should not be expected to return quotes, returns, or mixed transaction types without querying the base tables directly.