Search Results chk_row_exists

Overview

HR_DM_GEN_TUPS is a Data Migration (DM) utility package body in the Oracle E-Business Suite HR (PER) product schema. Its name derives from the "Generate TUP" (Table Update/Insert Package) function within the HR Data Migration framework. The package is responsible for dynamically constructing, and ultimately generating, PL/SQL package text that performs cutover data migration work for HR related tables. It reads migration table definitions and column metadata, then assembles the body of a generated package into a large in-memory container, allowing output that exceeds the standard 32767-character VARCHAR2 limit.

The package is classified under ETRM as OTHER. It is a build-time or setup-time utility rather than a transaction runtime API, and it is invoked programmatically when migration repository definitions must be converted into executable PL/SQL artifacts.

Key Procedures and Functions

The documented interface exposes a single entry point:

• CREATE_TUPS_PACAKGE — The primary driver procedure of the package body. It assembles and generates the TUP (Table Update Package) source for the migration tables held in the HR_DM repository. Because the name contains a typographical variant ("PACAKGE"), it should be referenced exactly as documented. Parameter lists are not documented in the ETRM extract and are therefore intentionally not reproduced here; only the purpose of the routine is asserted.

Supporting internal structures declared in the package body include record types t_chk_row_exists and t_delete_dml, which hold dynamically built WHERE clauses, procedure call text, and parameter strings for row-existence checks and delete DML on non-primary-key tables. A set of global collections (t_varchar2_tbl and t_fk_to_aol_columns_tbl instances) buffers column lists, parameter lists, hierarchy and AOL column mappings, primary-key and foreign-key-to-AOL associations, and exception text. The type t_varchar2_32k_tbl and the global variable used to accumulate package body text exist specifically to overcome the 32767-character limit on individual PL/SQL string variables.

Tables Accessed

The package reads and references the following objects through APPS synonyms:

• HR_DM_TABLES — the migration repository listing of tables to be processed; drives which TUPs are generated.
• HR_DM_HIERARCHIES — hierarchy definitions that determine parent/child processing order and hierarchy column handling.
• ALL_TAB_COLUMNS — supplies the column-level metadata (names, data types, ordering) used to build generated column and parameter lists.
• FND_ORACLE_USERID — provides the Oracle schema/user information associated with the migration environment.
• DBMS_SQL — Oracle supplied package used for dynamic SQL execution and parsing within the generated logic.
• PLITBLM — Oracle supplied package used to store and manipulate large generated source text, supporting the overflow of package body content beyond standard string limits.

Usage Notes

HR_DM_GEN_TUPS is not a user-facing API and is not typically attached to a form or a standard concurrent program request group directly. It is invoked as a generator during HR Data Migration setup, when administrators define migration tables, hierarchies, and column mappings in the HR_DM repository and then request generation of the supporting update/insert packages. The generated TUP packages are subsequently run during cutover to move and reconcile data.

Because the routine emits PL/SQL source text, it is commonly called from migration setup scripts or custom code that orchestrates the data migration build. The package is referenced by one other package in the ETRM inventory, confirming it participates within a coordinated generator suite rather than operating standalone. Practitioners should avoid modifying the package body, since regeneration would overwrite customizations, and should ensure FND_ORACLE_USERID and the HR_DM repository tables are correctly populated before invocation.