Search Results mtl_sales_orders_kfv




Overview
Explore in AI Chat

Overview

MTL_SALES_ORDERS_KFV is a key flexfield (KFV) view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the Inventory (INV) product family and exposes the concatenated key flexfield segments of sales orders in a format suitable for display, reporting, and integration. In Oracle EBS, a "_KFV" view is generated automatically for tables that contain a key flexfield, providing both a raw concatenated segment string and a space-padded variant used by Oracle Forms and concurrent programs. MTL_SALES_ORDERS_KFV fulfills this role for the MTL_SALES_ORDERS entity, which underlies the Sales Orders key flexfield structure that qualifies order records used in inventory and order management processing.

The view is documented as VALID and does not store data itself; it is a thin projection over the base table, adding concatenation logic and padding for the SEGMENT1, SEGMENT2, and SEGMENT3 columns. Because the flexfield segments are combined at query time, the view always reflects the current segment values of the underlying sales order record.

Underlying Base Objects

The documented referenced base object is MTL_SALES_ORDERS, accessed through a synonym in the APPS schema. The view text shows a direct SELECT from MTL_SALES_ORDERS, returning all key business columns unchanged and deriving the concatenated flexfield strings from the three segment columns. No joins are present in the view definition, so referential integrity and any foreign key relationships to order headers, order lines, or inventory organizations must be established by joining the SALES_ORDER_ID column to other tables.

The view inherits the standard Oracle EBS audit columns from the base table, including LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. It also passes through the flexfield control columns SUMMARY_FLAG, ENABLED_FLAG, START_DATE_ACTIVE, and END_DATE_ACTIVE, which govern whether a given flexfield combination is enabled and effective for the specified date range. The SOURCE_CODE, ORDER_TYPE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE columns relate to order origination and the concurrent program context that created or updated the record.

Key Columns

  • ROW_ID — the base table ROWID, exposed for direct row addressing where permitted.
  • SALES_ORDER_ID — the unique identifier of the sales order record and the primary key joining the view to related order objects.
  • CONCATENATED_SEGMENTS — the SEGMENT1, SEGMENT2, and SEGMENT3 values separated by periods, representing the flexfield combination in canonical form.
  • PADDED_CONCATENATED_SEGMENTS — the same combination with each segment right-padded to 40 characters using RPAD, matching the storage format used in the base table and by Oracle Forms.
  • ENABLED_FLAG / SUMMARY_FLAG — control flags indicating whether the combination is usable and whether it is a summary (parent) record.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range for the flexfield combination.
  • Audit and program columns — LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, and related fields for change tracking and concurrent program diagnostics.

Common Use Cases and Queries

The view is typically used when a report or interface must present the sales order flexfield combination as a single readable value rather than as separate segment columns. A common pattern is to join it to order and inventory tables using SALES_ORDER_ID.

To retrieve the concatenated flexfield for a specific sales order:

  • SELECT sales_order_id, concatenated_segments FROM mtl_sales_orders_kfv WHERE sales_order_id = :order_id;

To list all enabled combinations effective on a given date:

  • SELECT concatenated_segments, enabled_flag, start_date_active, end_date_active FROM mtl_sales_orders_kfv WHERE enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);

Because the view lacks joins, integration queries frequently combine it with order management tables to resolve descriptive context, and padded values may be used when exact format matching is required for interface uploads or comparisons. The absence of SEGMENT1, SEGMENT2, and SEGMENT3 as individually named columns in the view means that segment-level filtering must be performed against the base MTL_SALES_ORDERS table rather than through this view.