Search Results ota_price_list_entries




Overview
Explore in AI Chat

Overview

The OTA_PRICE_LIST_ENTRIES table is a core transactional data object within the Oracle Learning Management (OTA) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the definitive repository for pricing information associated with training activities. Each record in this table defines a specific price for a specific activity version, within a defined date range, and for a particular price list. This structure enables flexible and time-sensitive pricing models, allowing organizations to manage different rates for different customer segments, geographic regions, or promotional periods. The table is central to the financial configuration and transactional integrity of the learning management system, directly impacting enrollment costs, invoicing, and revenue recognition.

Key Information Stored

The table's primary purpose is to store the price definition for an activity. Its key columns include the surrogate primary key, PRICE_LIST_ENTRY_ID. The business key is a composite of ACTIVITY_VERSION_ID, START_DATE, PRICE_LIST_ID, VENDOR_SUPPLY_ID, and PRICE_BASIS, ensuring uniqueness for this combination. The ACTIVITY_VERSION_ID links to the specific training offering, while PRICE_LIST_ID associates the entry with a master price list. The START_DATE (and implied end date, typically derived from the next entry's start date) defines the effective period. VENDOR_SUPPLY_ID is used when pricing is tied to a specific supplier or resource. PRICE_BASIS indicates the unit of pricing (e.g., per person, per event). The MINIMUM_ATTENDEES column can be used to define pricing tiers or prerequisites for a specific rate to apply.

Common Use Cases and Queries

A primary use case is determining the applicable price for a learner during enrollment. A typical query retrieves the valid price list entry for a given activity, date, and price list. For example, to find the current price for activity version 1000 in price list 500, one might query: SELECT * FROM ota_price_list_entries WHERE activity_version_id = 1000 AND price_list_id = 500 AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE). Reporting use cases include analyzing price history, comparing rates across different price lists, and auditing pricing configurations. Data fixes often involve updating or inserting records to correct pricing errors or implement new rates, requiring careful management of start dates to avoid overlapping periods.

Related Objects

The OTA_PRICE_LIST_ENTRIES table maintains critical foreign key relationships with other master and transactional tables in the OTA schema, as documented in the ETRM metadata. These relationships are:

  • OTA_PRICE_LISTS: Joined via PRICE_LIST_ID. A price list contains multiple price list entries.
  • OTA_ACTIVITY_VERSIONS: Joined via ACTIVITY_VERSION_ID. An activity version can have multiple pricing entries over time.
  • OTA_VENDOR_SUPPLIES: Joined via VENDOR_SUPPLY_ID. This links the price to a specific supplier contract or resource cost.
These relationships ensure referential integrity, meaning a price list entry cannot exist without a valid parent price list and activity version. Understanding these joins is essential for constructing accurate reports on training costs and profitability.