Search Results hr_edw

Overview

APPS.HR_CONSTRAINT_CHECK is a diagnostic and utilities package body in the Oracle E-Business Suite Human Resources (HR) product family. Its primary business function is to verify that the database constraints which enforce the integrity of HR, Payroll, and Benefits tables are present and correctly defined. The package compares the set of constraints that exist in the database against an expected list, and reports any constraints that are missing. This is essential for environments where upgrades, patches, or data migrations may have inadvertently dropped or renamed constraints, which can lead to data corruption or application errors. The package is classified in ETRM as "OTHER" (not a public API), indicating it is an internal utility rather than a supported integration point. The package variable g_number is declared at the top of the body and serves as a package-level counter used internally throughout the constraint-checking logic, for example to tally the number of constraints found, missing, or processed.

Key Procedures and Functions

• BUILD_CONSTRAINT_LIST — Populates an in-memory list (the ConsList type) of expected constraints by querying the data dictionary. It discovers constraints on HR, PER, BEN, and related tables and builds the reference set against which actual database constraints are compared.
• FIND_MISSING_CONSTRAINTS — Compares the expected constraint list built by BUILD_CONSTRAINT_LIST against the constraints that actually exist in the database, and identifies those that are missing. This is the core diagnostic routine of the package.
• ADD_CASE_CONSTRAINT — Adds a specific constraint definition to the internal collection, typically used to register case-specific or conditional constraints that must be checked in addition to the standard set.
• ADD_HEADER — Writes a formatted header to the output report produced by the package, providing context such as the product, date, or run parameters.
• ADD_FOOTER — Writes a formatted footer to the output report, typically summarizing totals such as the number of constraints checked and the number missing.

Tables Accessed

• ALL_CONSTRAINTS — The primary data dictionary view queried to discover the constraints that exist on HR, PER, and BEN tables. The package filters out system-generated constraints (SYS_%) and selects by owner and table-name pattern.
• FND_APPLICATION — Used indirectly through the get_schema function's call to FND_INSTALLATION.GET_APP_INFO to resolve the Oracle schema (owner) associated with a given product, ensuring the correct owner is used when querying constraints.
• HR_API_USER_HOOK_REPORTS — Referenced to integrate constraint-check output with the HR API user-hook reporting mechanism, allowing diagnostic results to be surfaced through the standard HR reporting infrastructure.

Usage Notes

HR_CONSTRAINT_CHECK is an internal utility package and is not designed for direct invocation by end users or as a public API. It is typically executed through concurrent programs or diagnostic scripts during implementation, upgrade validation, and post-patch verification of an HR/Payroll environment. The package may also be called by Oracle's own patch and diagnostic routines to confirm that constraints remain intact after a patch is applied. Custom code should not depend on this package, as its procedures and package variables (including g_number) are subject to change without notice. The package does not appear in the referenced-by list of other documented packages, confirming its role as a standalone diagnostic tool. When investigating missing-constraint errors in HR or Benefits, DBAs and technical consultants can invoke this package to obtain a report of constraints that are absent from the database.