Results for “ams_oe_returns_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_OE_RETURNS_V is a reporting view classified under the AMS (Marketing) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is "Order Returns," indicating that it exposes order line records whose line category has been classified as a return within the Oracle Order Management schema. The view is designed to present a flattened, denormalized projection that joins order header attributes to their corresponding order line attributes, filtered exclusively to return-type lines. This makes it useful to Marketing and downstream reporting consumers who require a consolidated list of customer returns without navigating the full complexity of the OE_ORDER_HEADERS and OE_ORDER_LINES transactional model.

Notably, the ETRM metadata records the implementation status as "Not implemented in this database." This signifies that the object is defined in the application dictionary or reference documentation but may not exist as a physical database object in every environment. Consequently, reports or integrations that reference AMS_OE_RETURNS_V may fail at runtime unless the view has been created in the target instance. It is also relevant to note that the user's search term, "oe_order_headers," reflects the fact that the view is derived directly from the Order Management transactional tables, and the underlying header table is the primary anchor of the join.

Underlying Base Objects

The view text is defined explicitly over two base tables:

• OE_ORDER_HEADERS — aliased as HEAD, providing header-level attributes such as ORDER_NUMBER, INVOICE_TO_ORG_ID, and SOLD_TO_ORG_ID.
• OE_ORDER_LINES — aliased as LINE, providing line-level attributes such as LINE_ID, LINE_NUMBER, INVENTORY_ITEM_ID, and LINE_CATEGORY_CODE.

The two tables are correlated on the primary-to-foreign key relationship HEAD.HEADER_ID = LINE.HEADER_ID. A restrictive predicate, LINE.LINE_CATEGORY_CODE = 'RETURN', limits the result set to return lines only. The metadata's "Referenced base objects" field lists none explicitly, but the view text unambiguously identifies both OE tables as the governing sources. No other AMS-specific or staging tables participate in the definition, so the view is a pure join over Order Management data rather than a Marketing-owned structure.

Key Columns

• ORDER_NUMBER — The user-facing order number from OE_ORDER_HEADERS, used to identify the return order in business terms.
• LINE_ID — The unique surrogate identifier of the order line from OE_ORDER_LINES; useful as a join key to other line-level objects.
• LINE_NUMBER — The visible line sequence within the order, indicating which line of the return is represented.
• INVENTORY_ITEM_ID — The internal identifier of the returned inventory item, joinable to MTL_SYSTEM_ITEMS_B.
• INVOICE_TO_ORG_ID — The organization identifier for the invoice-to party, referencing the customer or organizational entity.
• SOLD_TO_ORG_ID — The organization identifier for the sold-to party, the primary customer on the transaction.

Common Use Cases and Queries

Typical usage involves reporting return volumes by customer, item, or order. A basic retrieval follows:

• SELECT order_number, line_number, inventory_item_id, sold_to_org_id FROM ams_oe_returns_v WHERE sold_to_org_id = :org_id; — returns all return lines for a given sold-to party.
• SELECT inventory_item_id, COUNT(*) FROM ams_oe_returns_v GROUP BY inventory_item_id; — ranks returned items by frequency.
• SELECT h.order_number, h.line_number, m.segment1 FROM ams_oe_returns_v h, mtl_system_items_b m WHERE h.inventory_item_id = m.inventory_item_id; — augments return lines with item names.

Because the view is documented as not implemented in all databases, administrators should verify its existence before deploying dependent reports, and consider recreating it from the documented view text where absent.