Search Results get_sqlerrm

Overview

The APPS.HR_JP_DATA_MIGRATION_PKG package body is a Japanese localization utility that supports the migration of payroll element data during the April 2007 statutory change to the Standard Monthly Remuneration (SMR) and related social insurance thresholds. The header comment (hrjpdtmg.pkb 120.0.12000000.6) and the migration date constant c_mig_smr_sd set to 2007/04/01 confirm the package was written to transition existing employee assignment data from legacy values to the newly legislated SMR ranges defined by c_new_smr_min_high and c_new_smr_max_low.

Operationally the package performs two parallel migration streams: one for the health insurance Standard Monthly Remuneration (HI_SMR) and one for the dependents information. Both streams copy or replace element entry values, validate eligibility based on assignment criteria, and record the outcome into FND messages and session-tracking rows. The package is internally organized into qualify, migrate, validate, and run procedures, allowing a caller to preview qualifying assignments before committing data.

Key Procedures and Functions

• ELEMENT_RUN_RESULT_COPY — copies PAY_RUN_RESULTS and PAY_RUN_RESULT_VALUES records from an existing element to the new element, preserving historical payroll results during the migration.
• ADD_NEW_INPUT_VALUE — inserts a new input value definition for the migrated element, based on PAY_INPUT_VALUES_F.
• END_ELEMENT_ENTRY — date-tracks the close of a migrated element entry.
• GET_ASS_INFO — retrieves assignment-level information used later for validation and migration.
• GET_MIG_DATE — returns the migration effective date (the April 2007 boundary).
• INSERT_SESSION / DELETE_SESSION — manage FND_SESSIONS rows so a migration run can track or clean up its working session.
• QUALIFY_HI_SMR_HD / MIGRATE_HI_SMR_HD / INIT_DEF_HI_SMR_DATA — handle the health insurance SMR header stream: identify qualifying assignments, migrate their SMR element entries, and initialize default data.
• VAL_MIG_SMR_ASSACT / MIG_SMR_ASSACT / RUN_MIG_SMR — validate, migrate, and orchestrate the SMR assignment-activity records, with RUN_MIG_SMR serving as the top-level driver.
• QUALIFY_DEP_HD / MIGRATE_DEP_HD / INIT_DEF_DEP_DATA — apply the same qualify/migrate/initialize pattern to dependent information.
• VAL_MIG_DEP_ASSACT / MIG_DEP_ASSACT / RUN_MIG_DEP — validate, migrate, and orchestrate the dependent assignment-activity records.

The package also declares private helper records and tables (t_ass_hi_smr_rec, t_ass_hi_smr_tbl) and message text variables used to report already-updated and future-date conditions.

Tables Accessed

Reads and writes span the core payroll datamodel plus localization and diagnostic tables:

• PAY_ELEMENT_TYPES_F, PAY_INPUT_VALUES_F, PAY_ELEMENT_LINKS_F, PAY_LINK_INPUT_VALUES_F/S — resolve the migrated element and its input values (COM_HI_SMR_INFO, APPLY_MTH, REVISED_MR, PRIOR_MR, REVISED_SMR, PRIOR_SMR, APPLY_TYPE).
• PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_ENTRY_VALUES_F/S, PAY_RUN_RESULTS, PAY_RUN_RESULTS_S, PAY_RUN_RESULT_VALUES — the primary data targets for element entry and run-result migration.
• PER_ALL_ASSIGNMENTS_F — supplies assignment data for qualification and validation.
• HR_JP_PARAMETERS — holds Japanese localization parameters.
• FND_NEW_MESSAGES, FND_SESSIONS — persist warning/error text and track the migration session.

Usage Notes

The package is invoked as part of a one-time Japanese payroll localization migration, typically through a concurrent program or a controlled SQL*Plus session executed by a payroll administrator during a cutoff window. Because it performs DML against live element entries and run results with commits batched at c_commit_num (1000) rows, it should be run only after backups and in coordination with the April 2007 effective date. The qualification and validation procedures (QUALIFY_*, VAL_MIG_*) are intended to be run first in a dry-run or review mode so administrators can inspect the FND message output before the migrate and run procedures commit changes. This is a reference case for the get_sqlerrm search pattern: the package reports failures through SQLERRM captured into its message variables and FND_NEW_MESSAGES, which is the standard mechanism custom migration packages use to surface Oracle error text.