Search Results as_opportunity_orders_v




Overview
Explore in AI Chat

Overview

The AS_OPPORTUNITY_ORDERS_V view is an Oracle Applications (APPS) schema object belonging to the AS – Sales Foundation product family. It resides in the APPS schema with a status of VALID and is documented in the E-Business Suite Technical Reference Manual (ETRM) for releases 12.1.1 and 12.2.2. The view exposes the linkage between sales leads (opportunities) and the order headers created against them, providing a denormalized reporting surface that joins lead-to-order association records with core order header attributes.

Its principal role is to support reporting and integration scenarios in which a user needs to identify, for a given sales opportunity, the order number, the order date, and the order type associated with that opportunity. Because the view joins the lead-order relationship with the transactional order header, it allows a single query to traverse from opportunity context into order context without requiring the caller to resolve foreign keys manually.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through synonyms:

  • AS_LEAD_ORDERS — the source of the lead-to-order association, lead identifiers, order header identifiers, descriptive flexfield columns, and standard WHO/audit columns.
  • OE_ORDER_HEADERS_ALL — the source of order number, ordered date, order type, and transactional currency code.

The join predicate is ASLORD.ORDER_HEADER_ID = ASOHEADERSV.HEADER_ID, an inner join that returns only those lead-order association rows for which a corresponding order header exists. Records in AS_LEAD_ORDERS that reference a header not present in OE_ORDER_HEADERS_ALL are excluded. Audit columns such as ROW_ID, CREATION_DATE, and the LAST_UPDATE_* columns are drawn from AS_LEAD_ORDERS, not from the order header, which is significant when tracing the provenance of a row.

Key Columns

  • LEAD_ORDER_ID — primary identifier of the lead-order association row.
  • LEAD_ID — the sales lead/opportunity identifier.
  • ORDER_HEADER_ID — foreign key to OE_ORDER_HEADERS_ALL.
  • ORDER_NUMBER — the user-visible order number from the header.
  • DATE_ORDERED — aliased from ASOHEADERSV.ORDERED_DATE; this is the column returned when users search for "date_ordered". It represents the ordered date of the header.
  • ORDER_TYPE_ID — the order type of the header.
  • CURRENCY_CODE — aliased from TRANSACTIONAL_CURR_CODE, the transactional currency of the order.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segments carried from the lead-order association.

Common Use Cases and Queries

Typical scenarios include pipeline reporting (opportunities that have converted to orders), reconciliation between lead activity and booked orders, and integration feeds that must publish order details against a CRM opportunity. Because the user search referenced "date_ordered", a common query filters on the ordered date range.

SELECT lead_id,
       order_number,
       date_ordered,
       order_type_id,
       currency_code
FROM   apps.as_opportunity_orders_v
WHERE  date_ordered BETWEEN :p_from_date AND :p_to_date
ORDER  BY date_ordered;

To retrieve all orders for a specific opportunity:

SELECT order_number, date_ordered, currency_code
FROM   apps.as_opportunity_orders_v
WHERE  lead_id = :p_lead_id;

Flexfield segments and audit tracking may be added as required. Since the view performs an inner join, callers should not assume every lead-order row is represented; leads without a matching order header must be queried directly from AS_LEAD_ORDERS.