Search Results create_ext_where_clause




Overview
Explore in AI Chat

Overview

APPS.BEN_EXT_WHERE_CLAUSE_API is a public PL/SQL package body that provides the programmatic interface for maintaining the extensible where-clause definitions used by Oracle EBS Advanced Benefits (BEN). These where clauses are the rule fragments that determine how the Benefits system selects and restricts the rows of beneficiaries, plans, programs, and other entities that participate in eligibility, enrollment, and rate processing. Each record persisted by this package represents a single condition token — an operator, a value, a conjunction (AND/OR), and the associated extensible data element references — that is later assembled into a complete SQL predicate by the Benefits rules engine.

The package follows the standard Oracle EBS API pattern: a set of DML procedures for create, update, and delete, a locking procedure, and a multi-row processing procedure. It is registered as an API in ETRM, meaning it is a supported entry point for external and internal callers, and it should be used in preference to direct DML against the underlying table. The source header indicates a build lineage at version 120.2 with a 2005 date stamp, confirming that the object has been stable across the 12.1.1 and 12.2.2 releases.

Key Procedures and Functions

  • CREATE_EXT_WHERE_CLAUSE — Creates a new extensible where-clause row. Parameters cover the sequence number, operator code, comparison value, AND/OR conjunction code, the extensible data element identifiers for both the primary and condition operands, record-in-file identifiers, business group, legislation code, effective date, and validation flag. The procedure returns the surrogate identifier and object version number, and issues a savepoint when invoked in validation-only mode.
  • UPDATE_EXT_WHERE_CLAUSE — Modifies an existing where-clause row, applying the same attribute set supplied at creation together with optimistic locking via the object version number.
  • DELETE_EXT_WHERE_CLAUSE — Removes a where-clause row and its associated definition data.
  • LCK — The locking routine, used to serialize concurrent modification of a where-clause row before an update or delete.
  • MULTI_ROWS_EDIT — The bulk processing entry point, allowing a set of the above operations to be applied in a single call. This is the procedure of interest to users searching for "multi_rows_edit".

Tables Accessed

The package reads and writes BEN_EXT_WHERE_CLAUSE through the APPS synonym. This table stores the individual where-clause definition rows, keyed by EXT_WHERE_CLAUSE_ID and versioned by OBJECT_VERSION_NUMBER. All create, update, delete, and lock operations ultimately resolve against this table, and the API is the controlled path by which the Benefits rules engine obtains the condition fragments it compiles into eligibility and enrollment SQL.

Usage Notes

The package is normally invoked indirectly by the Oracle Forms user interface when a user configures extensible where clauses, and by the Benefits configuration and rules- compilation processes. Custom integrations should call the API rather than modify BEN_EXT_WHERE_CLAUSE directly, always passing a valid business group and effective date, and should honor the object version number returned by create and update. Callers performing multiple row changes should prefer MULTI_ROWS_EDIT to reduce round trips and to keep the row set consistent within a single transaction. Because the package references only one table and has no dependents in ETRM, it can be called safely from custom PL/SQL without cascading side effects.