Search Results search_delimiter_select

Overview

CN_SCA_RULES_ONLINE_GEN_PVT is a private (PVT) PL/SQL package body owned by APPS in Oracle E-Business Suite. It forms part of the Credit-to-Cash / Collections component delivered under the Advanced Collections and Credit Management modules, where "SCA" refers to the scoring and credit-rule evaluation architecture (likely "Scoring and Credit Analysis"). The package is responsible for the online, dynamic generation and evaluation of credit rules — that is, rules that determine credit-worthiness, exposure thresholds, and hold decisions for customers and transactions.

Unlike stored (precompiled) rule evaluation, this package builds SQL statements dynamically at runtime from rule metadata stored in configuration tables. The source header confirms this pattern: it defines a set of helper functions that scan generated SQL text for delimiters. The search_delimiter_select function, for example, is a private utility that scans a dynamically assembled select-list string for the next occurrence of arithmetic and grouping operators (* - + / ( ) ,) and returns the character position of whichever occurs first. Companion functions search_delimiter_from and search_delimiter_where perform the equivalent scan for the FROM and WHERE portions using different delimiter sets. These routines support the package's core dynamic-SQL generation logic, which parses rule expressions token by token to build valid, executable queries. Dynamic statement execution is performed through DBMS_SQL, and DBMS_APPLICATION_INFO is used to register package and module context for monitoring and diagnostics.

Key Procedures and Functions

• GEN_SCA_RULES_ONLN_DYN — Drives the online dynamic generation of credit rules. It reads rule definitions and their associated attributes and conditions, assembles the corresponding SQL expression, and prepares the rule for runtime evaluation.
• GET_WINNING_RULE — Evaluates the set of candidate credit rules and determines which rule "wins" for a given entity or transaction. This supports priority resolution when multiple rules apply, so that the correct credit decision is returned.
• CREATE_SCA_RULES_ONLINE_DYN — Persists or materializes the online dynamic rule constructs. It likely writes generated rule artifacts back to configuration tables so that subsequent evaluations or consumers can reference them.

Supporting private functions include search_delimiter_select, search_delimiter_from, and search_delimiter_where, plus the get_min helper used to select the earliest delimiter position when comparing two candidates. These are implementation details of the dynamic SQL builder and are not intended for external invocation.

Tables Accessed

The package reads rule and condition metadata from CN_SCA_CREDIT_RULES, CN_SCA_RULE_ATTRIBUTES, CN_SCA_CONDITIONS, and CN_SCA_COND_DETAILS. Object and repository information is obtained from CN_OBJECTS and CN_REPOSITORIES, allowing the generator to resolve table and column references for the SQL it builds. CN_SOURCE supplies source-related metadata. DBMS_APPLICATION_INFO, DBMS_SQL, PLITBLM, and USER_ERRORS are Oracle-supplied or internal utilities used for context registration, dynamic execution, internal list processing, and compile error inspection respectively.

Usage Notes

As a _PVT package, it is not part of the public API surface of the Credit Management / Advanced Collections application. It is invoked indirectly — typically through public wrapper packages or concurrent programs that trigger online credit rule generation and evaluation — and is referenced by one other package. The header identifies itself as "ONLINE_GEN," confirming it is the runtime, dynamic counterpart to any batch or stored rule-generation package. Customizations should not call this package directly; instead, integrators should rely on the supported public APIs. Because it constructs and executes dynamic SQL, deployments should verify that valid rule metadata exists in the CN_SCA_* tables, as malformed condition or attribute definitions will cause generation errors surfaced through USER_ERRORS and registered application context.