Search Results add_footer

Overview

HR_CONSTRAINT_CHECK is an Oracle E-Business Suite diagnostic and reconciliation package owned by the APPS schema. Its business purpose is to verify that the set of database constraints defined in the HRMS product definitions (historically held in a CASE repository, reflected in the header comment "a constraint which exists in CASE") matches the constraints actually present in the target database. The package header carries the CVS revision string "hrconchk.pkh 115.1", indicating it is a long-standing component of the HRMS technical stack, unchanged since at least 2002. It is classified as an OTHER API in the ETRM repository, meaning it is not a public business API but an internal utility invoked during upgrade, patching, or health-check routines. The package is declared AUTHID CURRENT_USER, so all references to data dictionary views such as ALL_CONSTRAINTS are resolved with the privileges of the calling user rather than the package owner.

Key Procedures and Functions

Five documented procedures exist in this package.

• BUILD_CONSTRAINT_LIST — Populates an in-memory PL/SQL associative array (the ConsList type, indexed by BINARY_INTEGER and composed of ConsRec records holding constraint_name and table_name) with the constraints defined for a given product. The product identity is supplied as a three- or two-letter product code, which determines which constraints are added to the returned list. The list parameter is an IN OUT NOCOPY parameter, so the procedure appends to or replaces the caller's collection without a copy step.
• FIND_MISSING_CONSTRAINTS — Compares the constraint list built from the CASE or HRMS product definitions against the list of constraints read from the current database. It generates a report identifying two categories of discrepancy: constraints that exist in the CASE definitions but are absent from the database, and constraints that exist in the database but are not present in the CASE definitions. This asymmetry check is the core diagnostic value of the package.
• ADD_CASE_CONSTRAINT — Adds a constraint entry to the CASE-side constraint list. This is the procedure surfaced by the search term "add_case_constraint", and it supports the incremental assembly of the expected-constraint collection prior to comparison.
• ADD_HEADER — Writes the header portion of the generated report output.
• ADD_FOOTER — Writes the footer portion of the generated report output.

The ADD_HEADER and ADD_FOOTER procedures indicate that the reporting output is formatted for a text or file-based report, consistent with the HR_API_USER_HOOK_REPORTS table referenced by the package.

Tables Accessed

• FND_APPLICATION — The Oracle Applications registration table, used to translate or validate the product code passed into BUILD_CONSTRAINT_LIST and to identify the owning application for the constraint set.
• ALL_CONSTRAINTS — The data dictionary view listing constraints visible to the current user; this is the source of the database-side constraint list used by FIND_MISSING_CONSTRAINTS.
• HR_API_USER_HOOK_REPORTS — The HRMS table that stores output from API user hook report processes; the package's ADD_HEADER, ADD_FOOTER, and comparison output are written here for later review.

Usage Notes

HR_CONSTRAINT_CHECK is not intended for direct customer invocation. It is a support and diagnostic utility, typically executed from a SQL*Plus session or a concurrent program during HRMS patching, upgrade assessment, or post-installation validation. Because it is AUTHID CURRENT_USER, it must be run by a user with visibility of the relevant application tables and should generally be invoked as APPS. No other package in the ETRM repository references it, confirming it sits at the leaf of the call graph rather than being a shared dependency. The ADD_CASE_CONSTRAINT procedure is relevant when extending the expected-constraint list programmatically before running FIND_MISSING_CONSTRAINTS to produce a reconciliation report.