Search Results element_type_change

Overview

PAY_DYNDBI_CHANGES_PKG is a PL/SQL package in the APPS schema that supports Oracle Payroll's dynamic database item (DBI) regeneration framework. Database items are the named PL/SQL expressions that Oracle Payroll uses to expose payroll data to formulas, balances, and element processing logic. Because the definition of a database item is generated as static PL/SQL code, any change to an underlying attribute such as an element name, balance name, or input value name can invalidate previously generated code. This package exists to record exactly which database items require regeneration whenever such a name translation changes. It does so by maintaining the PAY_DYNDBI_CHANGES table, a staging table that acts as a work queue for the batch regeneration process.

The package is classified as OTHER in the ETRM metadata for 12.2.2 and is referenced by five other packages, indicating that it functions as a shared utility within the payroll DBI infrastructure rather than as a user-facing API. Its header comments reference pydbichg.pkh at version 120.2, confirming that the package has been stable across the 12.1.1 and 12.2.2 releases.

Key Procedures and Functions

The package exposes eight documented procedures, organized around two themes: low-level queue maintenance and change-specific event handlers.

• INSERT_ROW — Inserts a single row into PAY_DYNDBI_CHANGES for a given identifier, change type, and language. No insert occurs if an identical row already exists, making the procedure idempotent.
• INSERT_ROWS — Performs the same logical insert as INSERT_ROW but accepts a collection of languages (dbms_sql.varchar2s), allowing a change to be recorded simultaneously for every installed language.
• ELEMENT_TYPE_CHANGE — Records database items that must be regenerated after an element name translation has changed. This is the procedure associated with the user's search term and is the primary entry point for element-related DBI invalidation.
• BALANCE_TYPE_CHANGE — Records database items requiring regeneration after a balance name translation has changed.
• INPUT_VALUE_CHANGE — Records database items requiring regeneration after an input value name translation has changed.
• BALANCE_DIMENSION_CHANGE — Records database items affected by a change to a balance dimension.
• DELETE_ROW — Removes a single entry from PAY_DYNDBI_CHANGES, typically after the associated database item has been successfully regenerated.
• DELETE_ROWS — Removes multiple entries from PAY_DYNDBI_CHANGES in a single operation.

Private constants C_ELEMENT_TYPE ('ET'), C_DEFINED_BALANCE ('DB'), and C_INPUT_VALUE ('EIV') define the change-type codes used as the p_type argument throughout the package.

Tables Accessed

• PAY_DYNDBI_CHANGES — The central table maintained by this package. Rows identify a payroll object (by ID), the nature of the change (type code), and the language affected. It is both read (to test for duplicates) and written (inserts and deletes).
• PAY_DEFINED_BALANCES — Referenced in the balance-related change procedures, which must resolve balance definitions to determine which database items depend on them.
• PAY_INPUT_VALUES_F — Referenced by INPUT_VALUE_CHANGE to identify input values whose translated names have changed.
• DBMS_SQL — Supplies the varchar2s collection type used to pass arrays of language codes.
• DUAL — Used for simple singleton queries.
• PLITBLM — The Oracle Applications PL/SQL table utility package, typically used for collection initialization.

Usage Notes

PAY_DYNDBI_CHANGES_PKG is not intended for direct invocation by end users or custom concurrent programs. It is invoked internally by the payroll application whenever element, balance, or input value translations are modified — most commonly through the element and balance definition forms, and through language installation or translation maintenance tasks. The affected rows accumulate in PAY_DYNDBI_CHANGES and are subsequently consumed by the dynamic database item generation process, which reads the queue and regenerates the corresponding PL/SQL. The DELETE_ROW and DELETE_ROWS procedures are then called to clear processed entries.

Custom code should generally treat this package as internal. Developers extending payroll functionality should invoke it only when creating or modifying translation data in a manner that mirrors standard Oracle behaviour, and should observe the duplicate-check semantics of INSERT_ROW and INSERT_ROWS to avoid redundant queue entries. Because the package is referenced by five other packages, direct modification of PAY_DYNDBI_CHANGES outside these procedures risks leaving the regeneration queue inconsistent.