Search Results get_formula_value

Overview

BEN_EVALUATE_ELIG_CRITERIA is a PL/SQL package body owned by the APPS schema that provides the eligibility engine used by Oracle Advanced Benefits (OAB) in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose is to determine whether a person satisfies general eligibility criteria, or rate-by-criteria conditions, defined against a benefit program, plan, or option. The package evaluates the criteria rows stored for a given eligibility profile and returns a boolean-style outcome indicating whether the participant qualifies.

The header comment identifies the object as the "Profile Evaluation Package." The maintained history shows the package has been extended repeatedly to handle assignment-level nuances, exclusion flags, hierarchy position validation, and the combination of eligibility "sets" through the SET1 and SET2 logic. Later fixes noted in the header address null handling within evaluation sets, confirming the package was tuned to treat unresolved or null outcomes conservatively.

Key Procedures and Functions

The ETRM metadata documents a single entry point, MAIN, which serves as the primary driver of the eligibility evaluation. MAIN orchestrates the comparison of a participant's derived data against the configured criteria and determines the pass/fail result. Internally, the package body also contains helper routines visible in the source excerpt, including GET_OVERRIDE_VALUE, which inspects whether an override value has been defined for a given criteria short code and returns the override value along with its data type; this supports the override mechanism that allows manually specified eligibility results to supersede calculated ones. The package further relies on criteria-value comparison logic, such as the is_ok boolean structures referenced in the history, to combine multiple criteria outcomes.

Tables Accessed

The package reads and writes the following documented tables through APPS synonyms:

• BEN_ELIGY_CRITERIA and BEN_ELIGY_CRIT_VALUES_F — the eligibility criteria definitions and their effective-dated criteria values, which supply the rules being evaluated.
• PER_ORG_STRUCTURE_ELEMENTS and PER_ORG_STRUCTURE_VERSIONS — organization hierarchy definitions and their dated versions, used to resolve organization-based criteria.
• PER_POS_STRUCTURE_ELEMENTS and PER_POS_STRUCTURE_VERSIONS — position hierarchy definitions and versions, used for position-based criteria and hierarchy position validation.
• PLITBLM — a generic PL/SQL index-by table used for in-memory data handling during evaluation.

These tables support the package's need to match a person's assignment, organization, and position context against the structured criteria configured in OAB.

Usage Notes

BEN_EVALUATE_ELIGIBILITY_CRITERIA is invoked during benefit eligibility processing, typically from the eligibility determination flow that runs when a participant is evaluated for a program, plan, or option. It may be called from Oracle Forms-based setup and participant screens, from concurrent eligibility batch processes, and from other PL/SQL packages — the ETRM metadata notes it is referenced by one other package. In the context of the search term "get_formula_value," administrators investigating how eligibility overrides and formula-driven criteria resolve should recognize that GET_OVERRIDE_VALUE provides the override path, while the MAIN routine performs the primary criteria comparison. Custom code should call MAIN rather than reimplementing the evaluation, since the package encapsulates the effective-dated and hierarchy-aware logic required for correct results across 12.1.1 and 12.2.2.