Search Results ams_ds_orders_v




Overview
Explore in AI Chat

Overview

AMS_DS_ORDERS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the AMS (Marketing) product family. It is a denormalized, read-only projection that joins order header, order line, inventory item, and customer account data into a single flattened row set. Its designation as a "DS" (data source) view indicates that it is intended to feed marketing analytics, territory and campaign attribution, and downstream data extraction rather than to support transactional processing. In release 12.1.1 the view is documented as VALID, and the 12.2.2 metadata confirms the same owner and a stable set of referenced base objects. Because the view consolidates sales order activity with customer, item, and pricing attributes, it allows marketing and business intelligence consumers to analyze booked, shipped, and fulfilled demand without navigating the normalized OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables directly.

Underlying Base Objects

The view is defined over four principal sources in the documented 12.1.1 text: OE_ORDER_LINES_ALL (aliased OL), OE_ORDER_HEADERS_ALL (aliased OH), MTL_SYSTEM_ITEMS_VL (aliased MSI), and HZ_CUST_ACCOUNTS (aliased CUST). The join conditions are explicit: OL.HEADER_ID equals OH.HEADER_ID, OL.INVENTORY_ITEM_ID equals MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID equals OL.ORG_ID, and CUST.CUST_ACCOUNT_ID equals OH.SOLD_TO_ORG_ID. The 12.2.2 metadata expands the referenced object list to include MTL_DEFAULT_CATEGORY_SETS, MTL_ITEM_CATEGORIES, and MTL_SYSTEM_ITEMS_B_KFV, reflecting the key flexfield view used to resolve item concatenated segments and category assignments in later releases. All base objects are exposed through APPS synonyms, so the view resolves against the standard EBS schema.

Key Columns

The view exposes header-level attributes such as ORG_ID, SOLD_TO_ORG_ID, PARTY_ID, ORDERED_DATE, BOOKED_DATE, TRANSACTIONAL_CURR_CODE, SOLD_FROM_ORG_ID, SALES_CHANNEL_CODE, and MARKETING_SOURCE_CODE_ID, along with twenty header-level descriptive flexfield columns (ATTRIBUTE1 through ATTRIBUTE20). Line-level attributes include LINE_ID, HEADER_ID, LINE_NUMBER, LINE_CATEGORY_CODE, ORDERED_QUANTITY, ORDER_QUANTITY_UOM, SHIPPED_QUANTITY, SHIPPING_QUANTITY, CANCELLED_QUANTITY, PRICING_QUANTITY, and the column the user searched for, FULFILLED_QUANTITY, which captures the quantity of the ordered line that has been fulfilled. Pricing columns UNIT_SELLING_PRICE and UNIT_LIST_PRICE support a derived EXTENDED_PRICE computed as NVL(UNIT_SELLING_PRICE,0) multiplied by the difference between ordered and cancelled quantity. Scheduling and logistics columns include SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE, ACTUAL_SHIPMENT_DATE, ACTUAL_ARRIVAL_DATE, PROMISE_DATE, REQUEST_DATE, SHIPPING_METHOD_CODE, and SHIPMENT_PRIORITY_CODE. Status flags CANCELLED_FLAG, OPEN_FLAG, and BOOKED_FLAG, together with item identification columns ITEM_NUMBER, ITEM_DESCRIPTION, CONCATENATED_SEGMENTS, and INVENTORY_ITEM_ID, complete the projection.

Common Use Cases and Queries

Typical consumers use this view to reconcile ordered versus fulfilled quantities by customer, item, or sales channel, and to attribute revenue-bearing order lines to marketing sources. A representative query filtering on the searched column is:

  • SELECT order_number, line_number, item_number, ordered_quantity, fulfilled_quantity, shipped_quantity FROM ams_ds_orders_v WHERE fulfilled_quantity > 0 AND ordered_date BETWEEN :start_date AND :end_date;
  • Aggregation by customer party for campaign attribution: SELECT party_id, SUM(extended_price) FROM ams_ds_orders_v WHERE marketing_source_code_id = :source GROUP BY party_id;
  • Fulfillment shortfall analysis: SELECT line_id, ordered_quantity, fulfilled_quantity, (ordered_quantity - fulfilled_quantity) unfulfilled FROM ams_ds_orders_v WHERE open_flag = 'Y';

Because the view performs multi-table joins without an explicit partition or organization filter, queries should constrain ORG_ID and date ranges to control execution cost. It should not be used for transactional DML, as it is a read-only reporting construct.