Search Results insert_line

Overview

APPS.HR_INDEX_CHECK is a diagnostic and remediation package body within the Oracle E-Business Suite Human Resources (HR) product family. Its principal business function is to audit the physical database index structures that support the PER, HR, BEN, and HRI application tables, then identify indexes or constraints that are missing as a result of incomplete database object installation or upgrade activity. The package compares the expected indexing footprint (as reflected by the naming conventions applied to Human Resources tables) against the actual contents of the data dictionary, and provides utilities that emit runnable DDL scaffolding to recreate the absent definitions. This makes HR_INDEX_CHECK a post-install validation and corrective tool rather than a transactional business API.

The package is classified as OTHER under the ETRM metadata for 12.2.2, confirming it is not a public, supported business API but an internal maintenance utility. Its header ($Header: hrindchk.pkb 115.2 2004/02/13) illustrates a long-lived lineage retained across the 12.1.1 and 12.2.2 releases.

Key Procedures and Functions

The documented API surface comprises nine procedures, each serving a discrete step in the index audit workflow.

• get_schema — A function that resolves the Oracle schema owning a given product by delegating to FND_INSTALLATION.GET_APP_INFO. It returns the owning schema name so subsequent lookups can be scoped correctly.
• build_index_list — Populates an in-memory index list by iterating cursors that select PER_%, HR_% (excluding HRI_%), BEN_%, and HRI_% table indexes from ALL_INDEXES, excluding SYS_%-prefixed entries.
• build_fk_index_list — An analogous builder that assembles the list of indexes associated with foreign key definitions, used to detect missing FK-support indexes.
• find_missing_indexes — The core comparison routine that identifies indexes present in the expected baseline but absent from the database.
• find_missing_cons_indexes — Identifies missing constraints and their supporting indexes, extending the audit to declarative constraint definitions.
• add_case_index — Emits the DDL text for a case-insensitive (function-based) index definition.
• add_case_constraint — Emits the DDL text for a case-insensitive constraint.
• add_header and add_fk_header — Produce the header preamble for the generated SQL script (general and foreign-key variants).
• add_footer — Produces the closing text of the generated remediation script.

Tables Accessed

The package reads three documented data sources. ALL_INDEXES is the primary dictionary view queried by the build_index_list and build_fk_index_list cursors to enumerate existing indexes by owner, table name, and index name. FND_APPLICATION is consulted to map product short names to application identifiers during schema resolution. HR_API_USER_HOOK_REPORTS is referenced in connection with reporting the diagnostic findings, consistent with the package's role in surfacing missing-object information to the DBA or installer.

Usage Notes

HR_INDEX_CHECK is normally invoked outside of application transactions — typically by DBAs performing post-install or post-upgrade validation of a 12.1.1 or 12.2.2 environment, or by Oracle Support during troubleshooting of corruption or performance symptoms traced to missing HR indexes. The output of the add_* routines is a script of CREATE INDEX and constraint DDL that must be reviewed and executed manually; the package does not itself apply changes. Custom code may call the find_missing_indexes and find_missing_cons_indexes routines to drive bespoke diagnostics, but because the package is classified as OTHER and had no documented consumers among other packages, direct references in customer extensions should be treated as unsupported and re-validated after each patch or upgrade.