Search Results order_category_code

Overview

The QA_SALES_ORDERS_LOV_V view is an APPS-owned database object within the Oracle E-Business Suite Quality (QA) module. Its designated purpose is to serve as the "Sales Order LOV record group" — that is, the underlying query used to populate a List of Values (LOV) that allows QA users to select a sales order. In Oracle EBS, LOV record groups feed descriptive flexfields, concurrent program parameters, and custom forms, so this view functions as a controlled, pre-filtered projection of sales order data rather than a transactional entity in its own right.

The view addresses a practical integration concern: the Quality module frequently needs to reference sales orders (for example, when linking quality results, collection plans, or specifications to a customer order), but the authoritative order data resides in the Order Management (ONT) schema. The view therefore mediates between QA and ONT by exposing a simplified, LOV-friendly result set drawn from order tables. It is classified as VALID in the ETRM reference metadata for 12.1.1 and 12.2.2.

Underlying Base Objects

The view text joins several documented objects:

• MTL_SALES_ORDERS (SYNONYM) — the legacy order-number source table, supplying SEGMENT1 (order number), SEGMENT2 (order type), SEGMENT3 (order source), and the SALES_ORDER_ID primary key.
• OE_ORDER_HEADERS_ALL (SYNONYM) — the Order Management order header, supplying status flags (CANCELLED_FLAG, OPEN_FLAG, BOOKED_FLAG) and ORDER_CATEGORY_CODE.
• OE_TRANSACTION_TYPES_TL (SYNONYM) — the translated transaction type table, joined on ORDER_TYPE_ID and constrained to the installed base language via FND_LANGUAGES.
• QA_CUSTOMERS_LOV_V (VIEW) — a QA-defined customer LOV view supplying CUSTOMER_NAME through an outer join on SOLD_TO_ORG_ID.
• FND_LANGUAGES (SYNONYM) — restricts results to the base (installed) language.
• FND_PROFILE (PACKAGE) — invoked as FND_PROFILE.VALUE('ONT_SOURCE_CODE') to obtain the current order source profile value.

The defining query is a UNION ALL. The first branch joins MTL_SALES_ORDERS, OE_ORDER_HEADERS_ALL, QA_CUSTOMERS_LOV_V, OE_TRANSACTION_TYPES_TL, and FND_LANGUAGES, and restricts orders to those with SEGMENT3 equal to the ONT_SOURCE_CODE profile value and ORDER_CATEGORY_CODE in ('MIXED','ORDER'). The second branch returns only MTL_SALES_ORDERS rows where SEGMENT3 differs from the profile value, projecting NULLs for customer name, status flags, and category. This two-branch design ensures that orders without a matching Order Management header are still presented, while populated attributes are supplied where available.

Key Columns

• ORDER_NUMBER — the order number (MTL_SALES_ORDERS.SEGMENT1); the primary display value in the LOV.
• SALES_ORDER_ID — the unique identifier from MTL_SALES_ORDERS, used as the LOV return value.
• ORDER_TYPE — the order type (SEGMENT2), matched against the transaction type name.
• ORDER_SOURCE — the order source (SEGMENT3); equals ONT_SOURCE_CODE for the populated branch.
• CUSTOMER_NAME — sold-to customer name, via QA_CUSTOMERS_LOV_V outer join; NULL in the second branch.
• CANCELLED_FLAG, OPEN_FLAG, BOOKED_FLAG — order header status indicators; useful for excluding cancelled or unbooked orders.
• ORDER_CATEGORY_CODE — limited to 'MIXED' or 'ORDER' in the primary branch.

Common Use Cases and Queries

Typical usage includes binding the view to a QA form LOV, filtering out cancelled orders, and resolving an order number to its SALES_ORDER_ID. For example:

SELECT order_number, customer_name, order_type, order_source FROM apps.qa_sales_orders_lov_v WHERE NVL(cancelled_flag,'N') = 'N' ORDER BY order_number;

Because the second UNION ALL branch emits NULLs for status and customer, queries relying on those columns should account for such rows. Accessing the view directly requires APPS-level or synonym-granted privileges.