Search Results price_list_start_date




Overview
Explore in AI Chat

Overview

The OTFV_PRICE_LISTS view is a business view template delivered in the APPS schema as part of the OTA – Learning Management (Oracle Training Administration) module. Its documented purpose is to serve as a flexfield view template from which a descriptive flexfield (DFF) view is generated. In Oracle EBS 12.1.1 and 12.2.2, this construct follows the standard OTA pattern in which a database view is built over a transactional base table and then exposed as a business view for self-service or external reporting consumers.

The view presents price list header information for training and learning catalog items, including the business group, price list identity, currency, validity dates, list type, and the unit price. Business views of this type are commonly surfaced through Oracle iLearning/learning management self-service pages or exposed to external consumers such as web interfaces or integration processes that read learning catalog pricing. Because the view is defined WITH READ ONLY, it is intended strictly for query access and cannot be used for DML.

Underlying Base Objects

OTFV_PRICE_LISTS is defined over the following documented objects:

The view therefore joins a transactional table to a translated organization units view and two PL/SQL packages for currency and lookup decoding. It is the template from which a generated flexfield view inherits its descriptive flexfield context, identified in the literal column "_DF" (value '_DF:OTA:OTA_PRICE_LISTS:TPL'), which maps the view to the OTA_PRICE_LISTS descriptive flexfield structure.

Key Columns

  • BUSINESS_GROUP_NAME – name of the business group from HR_ALL_ORGANIZATION_UNITS_TL.
  • PRICE_LIST_NAME / PRICE_LIST_COMMENTS / DESCRIPTION – identifying and descriptive attributes of the price list.
  • CURRENCY – currency display name derived via OTA_GENERAL.FND_CURRENCY_NAME.
  • DEFAULT_PRICE_LIST – decode of the DEFAULT_FLAG lookup using HR_BIS.BIS_DECODE_LOOKUP('YES_NO',...); note the documented column name contains a typographic variant (DEAFULT_PRICE_LIST).
  • PRICE_LIST_START_DATE / PRICE_LIST_END_DATE – effective date range of the price list.
  • PRICE_LIST_TYPE – decoded value of the PRICE_LIST_TYPE lookup.
  • UNIT_PRICE – price for a single unit, sourced from SINGLE_UNIT_PRICE.
  • TRAINING_UNIT – the training unit type associated with the price list; this corresponds directly to the user search term "training_unit" and is the column most relevant to that query.
  • "_DF", PRICE_LIST_ID, BUSINESS_GROUP_ID – flexfield context literal, primary key, and business group identifier used for security filtering.

Common Use Cases and Queries

Typical uses include validating price list configuration per business group, reporting training unit pricing, and feeding catalog pricing to integration layers. Because the view enforces business group security through NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID, TPL.BUSINESS_GROUP_ID), query results are restricted to the caller's business group when one is set.

To list price lists and their training units:

  • SELECT price_list_name, training_unit, unit_price, currency, price_list_start_date, price_list_end_date FROM apps.otfv_price_lists WHERE business_group_id = 101;
  • SELECT price_list_name, unit_price FROM apps.otfv_price_lists WHERE training_unit = '&training_unit' ORDER BY price_list_name;

Additional filters on PRICE_LIST_TYPE, DEFAULT_PRICE_LIST, or the effective date range allow reporting of currently active price lists. As the view is read-only, all such usage is limited to SELECT statements.