Results for “price_formula_id”

50+ results




Overview
Explore in AI Chat

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

Overview

QP_PRICE_FORMULAS_TL is the translation (TL) table for pricing formulas in the Oracle Advanced Pricing (QP) module. In Oracle EBS 12.1.1 and 12.2.2, pricing formulas define the mathematical expressions used by pricing engines to derive list prices, adjusted prices, or promotional values. Because these formulas carry user-facing text — a formula name and a description — Oracle separates the translatable attributes into a dedicated TL table so that each seeded or user-defined formula can present language-specific labels across a multi-language (MLS) deployment.

The table resides in the QP schema and is classified as VALID in the ETRM metadata. Its role is purely descriptive: it holds no arithmetic or algorithmic content, only the human-readable NAME and DESCRIPTION associated with a PRICE_FORMULA_ID. The base row (the formula itself, with its expression and bind variables) lives in the companion table QP_PRICE_FORMULAS_B. From a heuristic Data Vault modeling perspective, QP_PRICE_FORMULAS_TL leans toward a satellite classification, since it stores descriptive, language-dependent attributes that are functionally dependent on the base formula key rather than acting as an independent hub or a link between entities.

Key Information Stored

The table comprises eleven documented columns. The most significant are:

  • PRICE_FORMULA_ID — The foreign key to QP_PRICE_FORMULAS_B that identifies the pricing formula being translated.
  • LANGUAGE — The language code for this translated row, in Oracle's NLS language format.
  • SOURCE_LANG — The language in which the source formula text was originally entered; used by MLS to determine whether a row is a translation or the base record.
  • NAME — The translated display name of the pricing formula.
  • DESCRIPTION — The translated description explaining the formula's purpose.
  • ZD_EDITION_NAME — The edition identifier used by Oracle's editioning framework (introduced with 12.2) to support online patching and edition-based redefinition.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Oracle WHO audit columns tracking insert and update metadata.

The surrogate primary key, QP_PRICE_FORMULAS_TL_PK, is documented across PRICE_FORMULA_ID, LANGUAGE, and ZD_EDITION_NAME. The PRICE_FORMULA_ID and LANGUAGE pair functions as the business-key candidate that identifies a translation row, with ZD_EDITION_NAME qualifying it under editioning. The foreign key relationship is PRICE_FORMULA_ID, which references QP_PRICE_FORMULAS_B.PRICE_FORMULA_ID, enforcing that every translation belongs to a valid base formula.

Common Use Cases and Queries

QP_PRICE_FORMULAS_TL is typically accessed through the MLS views generated by the translation system or by joins that combine the base and translation tables. Reporting on formula names in the session language is the most common requirement.

A representative query joining base and translated rows:

  • SELECT b.price_formula_id, t.name, t.description, t.language FROM qp.qp_price_formulas_b b, qp.qp_price_formulas_tl t WHERE b.price_formula_id = t.price_formula_id AND t.language = USERENV('LANG') AND t.source_lang = USERENV('LANG');

Typical scenarios include building a pricing formula lookup (LOV) restricted to the user's language, auditing which formulas have complete translations versus untranslated rows (where SOURCE_LANG differs from LANGUAGE), and extracting formula metadata for downstream price list or promotion reports. Administrators also query this table to verify that seeded formula names appear correctly in each installed language.

Related Objects

  • QP_PRICE_FORMULAS_B — The base table holding the executable formula definition; joined on PRICE_FORMULA_ID.
  • QP_PRICE_FORMULAS_VL — The MLS view that presents base plus translated columns as a single logical entity, the standard access path for applications.
  • QP_PRICE_FORMULAS_V — The view exposing base formula data, frequently joined with the TL table in reports.
  • QP_PRICE_FORMULA_LINES — Stores the individual expression lines composing a formula.
  • QP_PRICING_ATTRIBUTES and QP_PRICE_LIST_LINES — Reference formulas indirectly through pricing setup and are commonly reported against formula names.
  • FND_LANGUAGES — Provides the valid language codes against which LANGUAGE and SOURCE_LANG are interpreted.