Search Results get_next_order_number

Overview

APPS.PER_CAGR_UTILITY_PKG is a shared utility package within the Oracle E-Business Suite Human Resources and Benefits footprint. Its name associates it with Collective Agreements (CAGR), and the package supports the configuration, validation, and generation of collective agreement records that drive eligibility processing for benefits plans and programs. The package operates under AUTHID CURRENT_USER, meaning that unqualified references to database objects resolve through the schema of the calling session rather than the APPS schema itself. In practice this requires callers to hold the appropriate synonyms and privileges before invoking its routines.

The package header carries a header revision string dating to 2003, indicating that it is long-established, stable infrastructure code. It is classified as OTHER in the ETRM metadata rather than as a public API, and several of its documented routines carry the annotation "Internal development use only." It is referenced by eleven other packages, which confirms its role as a foundational utility layer rather than a standalone business API.

Key Procedures and Functions

The package exposes twenty-one documented routines. The most relevant for the current search is CHK_SQL_STATEMENT, which validates a SQL statement passed to it. The documented purpose is to allow a form to verify that a value-set SQL statement is syntactically and semantically valid before that SQL is assigned to a record group. The routine raises an error message on failure. The package source contains two separate header blocks naming CHK_SQL_STATEMENT, the second associated with returning the name of an eligibility profile, suggesting the utility is reused across related validation paths.

Other documented routines fall into several groupings:

• Eligibility and configuration lookups: GET_ELIG_SOURCE, MULTIPLE_ENTRIES_ALLOWED, PLAN_NAME, OPTION_NAME, GET_NAME_FROM_VALUE_SET, GET_SQL_FROM_VSET_ID, and GET_COLLECTIVE_AGREEMENT_ID retrieve descriptive or identifying data used to populate collective agreement and eligibility setup.
• Navigation and mode handling: SET_MODE_FROM_NODE_NAME and the HR navigation references support form-driven navigation context.
• Ordering and typing: GET_NEXT_ORDER_NUMBER and GET_COLUMN_TYPE support sequencing and datatype resolution, complementing CONVERT_UOM_TO_DATA_TYPE, which maps an entitlement item's unit of measure to Number, Character, or Date.
• Request lifecycle: CREATE_CAGR_REQUEST, GET_CAGR_REQUEST_ID, and POPULATE_CURRENT_ASG manage the creation and population of collective agreement requests.
• Diagnostics: PUT_LOG, WRITE_LOG_FILE, CREATE_FORMATTED_LOG_FILE, REMOVE_LOG_ENTRIES, and LOG_AND_RAISE_ERROR provide logging and error-raising services used throughout the package.

Tables Accessed

The package reads and writes through APPS synonyms. Collective agreement data resides in PER_CAGR_ENTITLEMENT_ITEMS, PER_CAGR_API_PARAMETERS, and the assignment table PER_ALL_ASSIGNMENTS_F. Benefits configuration is drawn from BEN_OIPL_F, BEN_ELIGY_PRFL_F, BEN_OPT_F, BEN_OPT_F_S, BEN_PGM_F, BEN_PL_F, and BEN_PL_F_S. Setup metadata is sourced from FF_FORMULAS_F, PAY_ELEMENT_TYPES_F, and PAY_INPUT_VALUES_F. Navigation context is resolved through HR_NAVIGATION_NODES and HR_NAVIGATION_NODE_USAGES. These references reflect the package's role in translating collective agreement definitions into eligibility and plan configuration consumed by the Benefits engine.

Usage Notes

The package is typically invoked from Oracle Forms during collective agreement and value-set setup, most notably through CHK_SQL_STATEMENT at the point a record group is created. It is also called from concurrent request processing for collective agreement creation and from other PL/SQL packages that require logging or eligibility lookups. Because the routines carry internal-use annotations and the package is not classified as a public API, customizations should call it only where a supported extension point exists, and should not depend on its internal signatures remaining stable across patches. Support for both 12.1.1 and 12.2.2 follows the standard dual maintenance model.