Search Results delete_tier




Overview
Explore in AI Chat

Overview

APPS.CN_RATE_DIMENSIONS_PVT is the private implementation package for the Oracle E-Business Suite Advanced Pricing rate dimension framework delivered through the ETRM (Enterprise Trade and Rate Management) module in releases 12.1.1 and 12.2.2. A rate dimension defines a pricing variable—such as distance, weight, or equipment type—that drives tiered rate calculations in transportation and trade agreements. The package encapsulates the validation and persistence logic required to create, maintain, and remove those dimension definitions and their associated tier structures. As a private (PVT) package, it is not intended to be called directly by external integrators; consumers invoke the corresponding public API, and this body performs the underlying validation, DDL checks, and DML against the CN_RATE_DIMENSIONS schema objects. The header revision string shows the file at version 120.9 and explicitly documents R12 Multi-Org Access Control (MOAC) changes, reflected in the ORG_ID parameter threaded through the validation and lookup logic. Referenced by three other packages, it functions as a shared internal service layer within the pricing stack.

Key Procedures and Functions

The package exposes six documented entry points, organized as paired create, update, and delete operations at the dimension and tier levels:

  • CREATE_DIMENSION — Inserts a new rate dimension record, validating the dimension name, dimensional unit code, tier count, and the supplied tier collection before committing to CN_RATE_DIMENSIONS.
  • UPDATE_DIMENSION — Modifies an existing dimension definition, re-running the same validation suite while excluding the current record from uniqueness checks.
  • DELETE_DIMENSION — Removes a rate dimension and performs the referential and dependency checks required before the row can be purged.
  • CREATE_TIER — Adds a tier row to an existing dimension, supporting the ordered threshold structure used by the rate engine.
  • UPDATE_TIER — Adjusts tier attributes such as bounds or sequence within an existing dimension.
  • DELETE_TIER — Removes a single tier from a dimension, revalidating that the remaining tier configuration stays consistent with the parent dimension's NUMBER_TIER value.

Tables Accessed

The package reads and writes the following objects through APPS synonyms:

  • CN_RATE_DIMENSIONS — master table for dimension definitions; the target of create, update, and delete dimension operations and the source for name-uniqueness validation scoped by ORG_ID.
  • CN_RATE_DIM_TIERS — stores the individual tier rows belonging to each dimension; manipulated by the three tier procedures.
  • CN_CALC_SQL_EXPS — holds calculation SQL expressions referenced by a dimension; the validate_dimension routine inspects EXP_TYPE_CODE to determine whether an expression is DDT-typed.
  • CN_RATE_SCH_DIMS — associates rate schedules with dimensions; consulted for dependency checks.
  • CN_RT_FORMULA_ASGNS — rate formula assignments that may reference dimensions, relevant to deletion integrity.
  • DUAL and PLITBLM — utility objects used for cursor evaluation and PL/SQL table handling.

Usage Notes

CN_RATE_DIMENSIONS_PVT is invoked indirectly from the Oracle Pricing and Transportation setup forms and from the public wrapper package during dimension maintenance. Concurrent programs and custom extensions should not call this body directly; they should use the public API, which delegates validation and DML to these procedures. The private status means signatures may change between patches, so any custom code depending on it carries upgrade risk. Typical invocation scenarios include defining tiered rate structures in trade agreements, applying MOAC-scoped validation where ORG_ID filters uniqueness, and cascading tier cleanup when a dimension is redefined. Error conditions are raised through FND_MSG_PUB with messages such as CN_CANNOT_NULL, so callers must be prepared to handle FND_API.g_exc_error.