Search Results add_fk_header

Overview

HR_INDEX_CHECK is a diagnostic utility package owned by APPS in the Oracle E-Business Suite. Its purpose is to verify the physical database index landscape against the index definitions expected by the HRMS (Human Resources Management System) product family. In effect, the package performs a consistency audit between what is defined in the CASE/HRMS metadata and what actually exists in the running Oracle database.

The package is relevant to EBS 12.1.1 and 12.2.2 because database index drift—where indexes expected by the application are missing, or orphan indexes exist that are absent from supplied definitions—can cause query degradation, constraint failures, or unexpected optimizer behavior. HR_INDEX_CHECK provides a programmatic way to surface these discrepancies and to emit a report describing them.

The package is declared with AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking user rather than the definer, which is appropriate for a diagnostic utility that reads data dictionary views on behalf of the caller.

Key Procedures and Functions

ETRM documents nine procedures and functions. Their purposes are as follows:

• BUILD_INDEX_LIST — Constructs an in-memory PL/SQL table of indexes that exist in the current database for a given product code. It identifies indexes belonging to a specific application/product scope.
• FIND_MISSING_INDEXES — Compares a list of indexes from the current database against the list defined by HRMS, and generates output identifying indexes present in one source but absent in the other.
• ADD_CASE_INDEX — Adds an individual CASE-defined index entry into the working index list.
• ADD_CASE_CONSTRAINT — Adds a CASE-defined constraint entry into the working list, supporting the association between constraints and their backing indexes.
• ADD_HEADER — Writes the header portion of the diagnostic report output.
• ADD_FK_HEADER — Writes a section header specific to foreign key related findings in the report.
• BUILD_FK_INDEX_LIST — Builds a list of indexes associated with foreign key constraints, enabling targeted verification of FK-supporting indexes.
• FIND_MISSING_CONS_INDEXES — Compares constraint-related indexes, reporting those defined in CASE metadata but missing in the database, and vice versa.
• ADD_FOOTER — Writes the closing footer portion of the diagnostic report. This is the procedure the user most likely searched for; it finalizes the report output produced by the preceding procedures.

Tables Accessed

The package references three objects through APPS synonyms:

• FND_APPLICATION — Used to resolve product codes to application identifiers, enabling per-product scoping of the index checks. The product code passed into BUILD_INDEX_LIST is validated and mapped here.
• ALL_INDEXES — The primary data dictionary source. The package queries ALL_INDEXES to determine which indexes physically exist on the current database, which is then compared against the CASE-defined list.
• HR_API_USER_HOOK_REPORTS — Referenced in connection with the reporting/output mechanism used by the ADD_HEADER, ADD_FK_HEADER, and ADD_FOOTER routines to emit findings.

Usage Notes

HR_INDEX_CHECK is a support and diagnostic tool rather than an end-user business API. It is not referenced by any other package in the documented dependency set, confirming it is invoked directly rather than called from routine application flow.

Typical invocation is by DBAs or technical consultants running the procedures from SQL*Plus or a similar PL/SQL environment in response to performance or integrity investigations. The pattern is to call BUILD_INDEX_LIST (and BUILD_FK_INDEX_LIST) to populate lists, then FIND_MISSING_INDEXES and FIND_MISSING_CONS_INDEXES to produce findings. The ADD_HEADER, ADD_FK_HEADER, and ADD_FOOTER procedures bracket the report output, with ADD_FOOTER closing it. Because the package is classified as OTHER and is AUTHID CURRENT_USER, it should be executed by a user with sufficient privileges to read the referenced dictionary and report tables.