Search Results get_sequence

Overview

HXC_RESOURCE_RULES_UTILS is a public PL/SQL utility package owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It supports the Time and Labor (HXC) module's resource rules functionality, providing a reusable layer of infrastructure for resolving key flexfield value sets, decoding descriptive meanings, and validating concatenated segment combinations. The package is declared AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking session rather than the definer, which allows Form-based callers to inherit their own application security context.

The package is primarily notable for the get_value_set_sql procedure, which generates dynamic SQL against key flexfield value-set definitions. This is typically used to construct List of Values (LOV) queries at runtime for Key Flexfield (KFF) segments used by Resource Rules criteria. The other documented routines support sequence generation, meaning lookups, criteria validation, and flexfield validation checks.

Key Procedures and Functions

• get_value_set_sql — Accepts a flexfield field name and a legislation code, then assembles the SQL text required to query the corresponding value set. This is the entry point used to drive runtime LOV population for flexfield segments.
• get_sequence — Returns a NUMBER generated for a given type and optional flexfield structure identifier. It supplies surrogate sequence values used internally by the resource rules engine.
• get_meaning — Retrieves the descriptive meaning for a value within a given type, honoring optional business group and legislation code context. Used to translate stored values into display text.
• get_criteria_meaning — Returns the descriptive label for a resource rule criterion, similarly scoped by type and business context.
• chk_flex_valid — Validates a concatenated flexfield combination against the enabled value sets and structure defined for the segment set.
• chk_criteria_exists — Verifies whether a given criterion already exists, supporting duplicate-prevention logic in Resource Rules maintenance.

The package also declares package-level globals and a r_flex_valid record type with thirty segment columns, along with associative array types used to cache flexfield segment values in memory.

Tables Accessed

The package reads predominantly from Flexfields and HR configuration tables through APPS synonyms. FND_ID_FLEXS, FND_ID_FLEX_SEGMENTS, FND_ID_FLEX_SEGMENTS_TL, and FND_ID_FLEX_STRUCTURES supply key flexfield structure and segment metadata needed to build the value-set SQL. FND_FLEX_VALIDATION_TABLES provides validation-table assignments for value sets. On the HR side, HR_ORGANIZATION_INFORMATION, HR_SOFT_CODING_KEYFLEX, PER_GRADES, and PER_GRADE_DEFINITIONS provide organization and grade key flexfield data. PAY_LEGISLATION_RULES and PAY_PEOPLE_GROUPS provide payroll legislative and people-group context. HXC_RESOURCE_RULES holds the actual Time and Labor resource rule definitions, and PLITBLM is referenced for legislative translation support.

Usage Notes

HXC_RESOURCE_RULES_UTILS is invoked internally by Time and Labor setup and maintenance forms, particularly where Resource Rules criteria require dynamic LOVs driven by key flexfields (SCL, People, Grade). Because get_value_set_sql returns generated SQL text, calling forms or concurrent programs typically execute the returned statement via DBMS_SQL or EXECUTE IMMEDIATE to populate their LOVs. The validation routines chk_flex_valid and chk_criteria_exists are called during rule save operations to enforce data integrity. Custom extensions that build Resource Rules interfaces can call these utilities directly, but should be aware that the package caches key flexfield metadata in package globals, so reuse within a single session is generally more efficient than repeated initialization across calls.