Search Results archive_range

Overview

APPS.HR_DYNSQL is a PL/SQL package in the Oracle E-Business Suite APPS schema whose purpose is to build and execute dynamically constructed SQL statements for Oracle Human Resources and Oracle Payroll processing. The name reflects its role as a dynamic SQL generator: rather than relying exclusively on static cursors, members of this package assemble SQL text at runtime and open, parse, and fetch against it. This design allows the HR and Payroll modules to operate over flexible, data-driven structures such as assignment sets, element link criteria, payroll action ranges, and reporting mappings, where the exact query predicates cannot be known at compile time.

The package is classified as a non-API (OTHER) component within the ETRM 12.2.2 metadata. It is a foundational utility rather than an end-user feature; it is consumed internally by higher-level payroll and HR packages to resolve the correct population of assignments, balance dimensions, and recurring element entries. Its status is documented as VALID, and it depends only on SYS.STANDARD among documented dependencies.

Key Procedures and Functions

The ETRM metadata documents sixteen procedures and functions. Runtime SQL construction is performed by members whose names end in SQL: RBSQL, BKPSQL, CBSQL, QPTSQL, and PYRSQL. These build and execute queries against the various HR and payroll structures, typically returning a dynamic cursor or statement handle for the caller to consume. GET_LOCAL_UNIT and GET_TAX_UNIT resolve organizational units associated with local and tax reporting requirements.

• ARCHIVE_RANGE — supports archival processing over a defined range of payroll or assignment records.
• UPDATE_RECURRING_EE — updates recurring element entries for employees.
• STOP_RECURRING_EE — stops (ends) recurring element entries for employees.
• SETINFO — establishes session or package-level context information used by subsequent dynamic operations.
• PERSON_SEQUENCE_LOCKED, PROCESS_GROUP_SEQ_LOCKED, BAL_PERSON_SEQUENCE_LOCKED, RET_PERSON_SEQUENCE_LOCKED — sequence-locking checks for persons, process groups, balances, and retroprocessing respectively, used to serialize concurrent payroll activity.
• RBSQL, BKPSQL, CBSQL, QPTSQL, PYRSQL — dynamic SQL builders for the package's principal query operations.
• GET_LOCAL_UNIT, GET_TAX_UNIT — retrieve local and tax organizational unit identifiers.
• ADV_OVERRIDE_CHECK — validates override conditions for advance processing.

Tables Accessed

The package reads and writes a broad set of HR and Payroll tables through APPS synonyms, including HR_ASSIGNMENT_SETS and HR_ASSIGNMENT_SET_AMENDMENTS (population definition), HR_ORGANIZATION_INFORMATION and HR_SOFT_CODING_KEYFLEX (organizational and coding context), and the payroll core tables PAY_ELEMENT_TYPES_F, PAY_ELEMENT_LINKS_F, PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_ENTRY_VALUES_F, and PAY_INPUT_VALUES_F for element and recurring-entry maintenance. PAY_ASSIGNMENT_ACTIONS, PAY_PAYROLL_ACTIONS, PAY_ACTION_CLASSIFICATIONS, PAY_OBJECT_GROUPS, PAY_LEGISLATION_RULES, and PAY_REPORT_FORMAT_MAPPINGS_F support action processing, grouping, legislative rules, and report format resolution.

Usage Notes

HR_DYNSQL is primarily invoked indirectly. ETRM records that it is referenced by six packages, including HRASSACT, PAY_BALANCE_UPLOAD, PAY_BAL_ADJUST, PAY_POPULATION_RANGES_PKG, PAY_RETRO_PKG, and PAY_RUN_RESULT_PKG. These consumers call HR_DYNSQL when they must resolve assignment sets, build element-entry queries, or lock person and process-group sequences during payroll runs. Because the package is not classified as a public API, customizations should not call it directly; instead, developers should extend the supported HR and Payroll APIs. Direct invocation risks incompatibility during patching or upgrade, since the package's dynamic SQL text is tightly coupled to internal table structures.