Search Results cn_calc_formulas_pvt




Overview
Explore in AI Chat

Overview

CN_CALC_FORMULAS_PVT is a private PL/SQL package in the Oracle EBS Trade Management (formerly Oracle Advanced Pricing/ETRM) schema, owned by APPS and declared with AUTHID CURRENT_USER. It encapsulates the core business logic for defining, persisting, and generating calculation formulas used by Oracle Trade Management's accrual, compensation, and deduction engine. Calculation formulas drive how trade promotions, rebates, and channel compensation amounts are computed across transactions, so the package forms the persistence backbone for the formula definition subsystem.

The package exposes several PL/SQL record types, including input_rec_type (based on CN_FORMULA_INPUTS and CN_CALC_SQL_EXPS), rt_assign_rec_type (based on CN_RT_FORMULA_ASGNS and CN_RATE_SCHEDULES), and formula_rec_type (based on CN_CALC_FORMULAS). These typed records mirror the attribute structure of the underlying formula tables — including flags such as cumulative_flag, itd_flag, split_flag, threshold_all_tier_flag, and modeling_flag — and are used to pass complete formula definitions between the private package body and its public callers. The header reveals the object dates to at least Release 12.0 (header revision 120.4, dated 2006), and it remains present in EBS 12.1.1 and 12.2.2 as part of the Trade Management codebase.

Key Procedures and Functions

  • CREATE_FORMULA — Inserts a new calculation formula definition into the Trade Management formula tables. It resolves formula inputs and SQL expressions, validates the associated record attributes, and returns the newly created formula identifier to the caller.
  • UPDATE_FORMULA — Modifies an existing formula definition, including its inputs, SQL expressions, and behavioral flags. It enforces optimistic locking through the OBJECT_VERSION_NUMBER attribute carried in the record types.
  • DELETE_FORMULA — Removes a calculation formula and its dependent definition rows. It is invoked when a formula is no longer required, and typically verifies that the formula is not referenced by active rate schedule assignments before removal.
  • GENERATE_FORMULA — Produces or materializes the effective computation logic for a formula. This is the run-time generation step that translates the stored formula definition into an executable expression against the transaction or SRP period data.

All four procedures are private (PVT) APIs, meaning they are not intended for direct external invocation; they are wrapped by a companion public package and invoked from the Trade Management forms and concurrent programs.

Tables Accessed

  • CN_CALC_FORMULAS — Master formula definition table; stores name, description, formula type, status, transaction group, dimension count, and behavioral flags.
  • CN_FORMULA_INPUTS and CN_CALC_SQL_EXPS — Define each formula's input parameters and their associated SQL expressions, together with cumulative and split behavior.
  • CN_RT_FORMULA_ASGNS and CN_RATE_SCHEDULES — Link formulas to rate schedules, including effective start/end dates and schedule type.
  • CN_RATE_DIMENSIONS and CN_RATE_SCH_DIMS — Store the rate dimension definitions and their association to rate schedules, used when building formula dimensions.
  • CN_CALC_EDGES — Preserves edge relationships between formulas during generation and validation.
  • CN_QUOTA_ASSIGNS, CN_ROLE_QUOTA_CATES, CN_SRP_PERIOD_QUOTAS and its extension tables — Provide quota and SRP period context used when generating period-based formulas (ITD, cumulative).
  • DBMS_LOB and DUAL — Utility access for large-object handling and single-row expressions.

Usage Notes

CN_CALC_FORMULAS_PVT is invoked internally by the Trade Management Setup forms (notably the Calculation Formulas and Rate Schedules setup windows) and by the public wrapper API that exposes the four procedures to concurrent programs and other application modules. Three other packages reference it, confirming it as a service layer within the Trade Management formula framework rather than a standalone module. Customizations that need to create or modify formulas programmatically should call the corresponding public API rather than this PVT package, so that validation, versioning, and dependent-row handling remain intact. Direct calls are only appropriate for Oracle-patched internal code or carefully controlled extension scenarios.