Search Results person_type_fk




Overview
Explore in AI Chat

Overview

EDW_HR_PRSN_TYP_PKG is a small utility package in the Oracle E-Business Suite HRMS schema (APPS) that supports the Oracle HRMS Intelligence / Enterprise Data Warehouse (EDW) dimensional model. Its specific responsibility is to generate the surrogate foreign key value for the person type dimension, allowing HR transactional and analytical data to be joined consistently against the person type dimension table used by HRMS reporting and analytics.

The package body carries the header identifier hriekpty.pkb 120.0, indicating it belongs to the HR Intelligence (HRI) product family and was last revised during the 11i/12.0 development lifecycle. In EBS 12.1.1 and 12.2.2 it remains available largely unchanged, since the underlying dimensional model for HR analytics was not rearchitected in the 12.x releases. The package is classified as OTHER, meaning it is not part of a formally published public API and should be treated as an internal component of the HRMS EDW layer.

Key Procedures and Functions

The package exposes a single documented function: PERSON_TYPE_FK. Its purpose is to return the concatenated foreign key string that uniquely identifies a person type record for a given person and effective date, combining the person type primary key produced by the HRI_EDW_DIM_PERSON_TYPE dimension with an instance code retrieved from the EDW local instance configuration.

Internally, the function delegates primary key construction to hri_edw_dim_person_type.construct_person_type_pk, then appends the EDW instance code separated by a hyphen. This composite pattern is standard in Oracle HRMS EDW dimensions, where the instance code differentiates data originating from separate local instances when multiple EBS installations are consolidated into a single warehouse.

The function also contains a defensive exception handler: if any error occurs during key construction or instance lookup, it returns the constructed person type key (or the literal 'NA_EDW' if that key is null) rather than propagating the exception. This ensures downstream ETL processes do not abort on partial or missing person type data.

Tables Accessed

One table is referenced in the documented source: EDW_LOCAL_INSTANCE. The function performs a SELECT instance_code INTO l_instance_code FROM edw_local_instance to obtain the identifier of the current local instance. This value is concatenated with the person type primary key to form the returned foreign key, so that a single consolidated EDW repository can distinguish otherwise identical person type keys originating from different source instances.

Usage Notes

Because it is an internal EDW helper, EDW_HR_PRSN_TYP_PKG is not called directly from Oracle Forms or standard concurrent program parameters. It is instead invoked by the HRMS EDW extraction and dimensional loading routines, and the metadata records that it is referenced by eight other packages, confirming its role as a shared building block in the person type dimension pipeline.

Searching for the string "person_type_fk" in EBS typically surfaces this package when tracing how a person type foreign key is derived for analytics tables. Custom reports, BI Publisher data models, or interfaces that join HR data to the person type dimension should replicate or call this logic rather than reconstructing the key independently, since the concatenation with the local instance code is essential for multi-instance integrity. Because the function is not a published API, callers should expect no formal compatibility guarantee across patches or releases and should validate behavior after any HRMS or EDW patch application.