Search Results generate_formula

Overview

APPS.CN_FORMULA_GEN_PKG is a private PL/SQL package within the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 release, belonging to the Oracle Enterprise Trade and Revenue Management (ETRM) product family that governs channel revenue management and related calculation engines. Its principal responsibility is to generate executable PL/SQL formula packages from the metadata-driven formula definitions stored in the channel management calculation tables. When a user or background process requests generation of a formula, this package builds the source code for a formula package, persists that source into the CN_SOURCES repository, submits a concurrent request to spool the code to an operating-system file, and drives compilation of the resulting package against the database. This mechanism allows business users to define calculation logic declaratively without hand-coding PL/SQL, while retaining the performance and reuse benefits of compiled database code. The package declares AUTHID CURRENT_USER, meaning that all dynamic SQL and DDL it issues executes under the privileges of the calling schema rather than the package owner.

Key Procedures and Functions

• GENERATE_FORMULA — The primary public API (documented as API name generate_formula). It follows the standard EBS API parameter conventions, accepting the API version, initialization message list flag, commit flag, and validation level as inputs, together with the target p_formula_id and p_org_id. It returns the standard x_return_status, x_msg_count, and x_msg_data outputs, plus an x_process_audit_id that enables callers to audit and trace the generation request. It is the entry point for programmatic formula generation.
• CREATE_FORMULA — A local helper function, deliberately exposed for debugging purposes. It invokes the various formula component constructors to assemble the complete formula definition and returns a BOOLEAN indicating success. It accepts the target formula identifier as its only parameter. This routine encapsulates the internal object-construction logic that the public API relies upon.
• GENERATE_FORMULA_CONC — A concurrent-program wrapper that adapts the generation logic to the Oracle EBS concurrent manager interface. It exposes the standard errbuf and retcode OUT parameters expected of concurrent executable PL/SQL, along with an optional p_org_id, allowing the generation process to be scheduled and launched as a background request.

Tables Accessed

The package reads and writes across the channel management calculation schema through APPS synonyms. The principal data holder is CN_SOURCE_ALL, where generated formula source code is persisted. Formula metadata is drawn from CN_CALC_FORMULAS and CN_CALC_FORMULAS_ALL, with supporting expressions in CN_CALC_SQL_EXPS and CN_CALC_SQL_EXPS_ALL. Formula inputs are retrieved from CN_FORMULA_INPUTS_ALL, and dimension context from CN_RATE_DIMENSIONS_ALL and CN_RATE_SCH_DIMS_ALL. External calculation table registration is handled via CN_CALC_EXT_TABLES_ALL and CN_CALC_EXT_TBL_DTLS_ALL. Repository and object metadata reside in CN_REPOSITORIES, CN_REPOSITORIES_ALL, CN_OBJECTS, and CN_OBJECTS_ALL, while runtime formula assignments are captured in CN_RT_FORMULA_ASGNS_ALL.

Usage Notes

This package is most commonly invoked indirectly through the ETRM setup and formula maintenance user interfaces, and through the GENERATE_FORMULA_CONC concurrent program when formulas must be regenerated in bulk following metadata changes. Because GENERATE_FORMULA conforms to the EBS API standard, custom code can call it directly to trigger generation for a specific formula and organization, using the returned audit identifier for traceability. Two other packages within the application reference this package, reinforcing that it is an internal dependency of the broader calculation framework rather than an end-user facing API. Developers should preserve the standard initialization and commit flag semantics to ensure proper transaction handling.