Search Results body_code

Overview

CN_SCA_RULES_BATCH_GEN_PVT is a private PL/SQL package body in the APPS schema that serves as an internal processing engine for Oracle E-Business Suite's Advanced Pricing and credit rule functionality within the Oracle Trade Management (formerly Contracts) module. The package is classified as a Private (PVT) API, meaning it is intended for internal consumption by other packages rather than direct invocation by customers or external integrations. Its stated purpose, as documented in the package header comments, is to process Credit Rules and their associated allocation percentages. The package generates batch definitions for credit rules, dynamically constructing and executing SQL statements against the credit rule repository to evaluate and populate rule matches. Two global variables — g_package_name and g_org_id — track the executing package and the operating unit (ORG_ID), reflecting the multi-org architecture that the package honors. The current source version carries a header identifier indicating it was last revised in August 2011 (120.10), with notable history entries including the June 2003 creation by Rao.Chenna and a November 2005 enhancement by vensrini that added ORG_ID checks to the POPULATE_MATCHES procedure.

Key Procedures and Functions

The package exposes three documented procedures, each contributing to the batch generation lifecycle:

• GEN_SCA_RULES_BATCH_DYN — The namesake driver procedure. It generates the batch of SCA (Sales Credit Allocation) rules dynamically, orchestrating the assembly of rule logic and the construction of executable SQL used to evaluate rule conditions.
• POPULATE_MATCHES — Populates the set of matching credit rules and their allocation outcomes based on the evaluated conditions. This procedure was enhanced to honor ORG_ID checks, ensuring that matches are restricted to the correct operating unit in a multi-org environment.
• CREATE_SCA_RULES_BATCH_DYN — Dynamically creates the SCA rules batch, working in concert with the generation procedure to persist the batch definition. The "DYN" suffix on both GEN and CREATE procedures signals that these routines build SQL statements at runtime rather than relying on static cursors.

Supplementing these are internal helper routines, including GET_MIN (a numeric comparison utility), SEARCH_DELIMITER_SELECT (a parser that locates SQL operators and delimiters such as * , - , + , / , ( , ) , and commas within a dynamically built SELECT string), and related parsing functions. These utilities indicate that the package performs expression parsing to translate stored credit rule conditions into valid SQL predicates.

Tables Accessed

The package reads and writes a broad set of repository and configuration tables, accessed through APPS synonyms:

• CN_SCA_CREDIT_RULES — The primary credit rule repository; also the source of the ORG_ID global variable data type.
• CN_SCA_CONDITIONS and CN_SCA_COND_DETAILS — Store rule conditions and their detailed components used to build match logic.
• CN_SCA_RULE_ATTRIBUTES and CN_SCA_COMBINATIONS — Provide the attribute definitions and combination criteria for rule evaluation.
• CN_SOURCE — Supplies source information referenced by rule definitions.
• CN_OBJECTS, CN_MODULES, and CN_REPOSITORIES — Repository metadata tables defining object, module, and package registration.
• PLITBLM — A PL/SQL table-of-varchar2 type used for bulk collections and dynamic SQL bind handling.
• DBMS_SQL and DBMS_APPLICATION_INFO — Oracle-supplied packages used for dynamic SQL execution and instrumentation/concurrent request context tracking.
• USER_ERRORS — Referenced for compile-error diagnostics of dynamically generated code.

Usage Notes

Because this package is classified as a Private API, it is not intended for direct invocation by end users or external programs. It is referenced by exactly one other package, confirming its role as a subordinate internal engine. Typical invocation paths include the credit rules setup and processing flows within Oracle Trade Management / Advanced Pricing, where a calling public API or concurrent program triggers batch generation and match population. Because the package relies on DBMS_SQL and PLITBLM to assemble and execute SQL dynamically, DBMS_SQL open-cursor limits and bind-variable hygiene are relevant operational considerations. In R12.1.1 and R12.2.2, ORG_ID is enforced by POPULATE_MATCHES to respect multi-org security, so callers must ensure the correct operating unit context is initialized before invocation. The package depends on the CN_ schema tables being intact, and any repopulation of credit rules should be performed through the supported public interfaces rather than by calling this private package directly.