Search Results p_session_date

Overview

HR_NL_CALC_TARGET_GROUP is an Oracle EBS localization package that supports Dutch (Netherlands) statutory reporting requirements within Oracle HRMS. Its primary business function is to determine the "target group" classification for an employee by evaluating the country of birth of the employee and related contacts, and then applying a configurable formula to derive the resulting target group indicator. This classification is used for Dutch social security and labour-market reporting, where employers must identify employees belonging to designated target groups such as ethnic minorities or the long-term unemployed.

The package body is a private implementation of the HR_NL_CALC_TARGET_GROUP specification. It encapsulates the derivation logic so that the calculation is performed consistently wherever a target group code is required, whether during data entry, batch processing, or reporting.

Key Procedures and Functions

• GET_COUNTRY_CODE — Resolves the country of birth associated with a person and a contact relationship type as of a supplied session date. It first attempts to match the person record using its effective date range together with the contact relationship date range. If no row is found, a second query relaxes the person-level date restriction and selects the most recent contact relationship by date start. The function returns a VARCHAR2 country code, or NULL when no matching data exists. This function is the direct consumer of the p_session_date parameter and is the entry point most relevant to date-effective lookups.
• RUN_FORMULA — Evaluates the relevant Oracle FastFormula definition to convert the derived country-of-birth values (for the father, mother, and employee) plus the business group and session date into a target group result. The formula identifier and effective date are resolved from the formula definition, allowing the classification rules to be maintained declaratively rather than in code.
• GET_TARGET_GROUP — The public-facing wrapper that orchestrates the calculation. It retrieves the necessary country codes, invokes the formula, and returns the final target group classification for the person.

Tables Accessed

• PER_ALL_PEOPLE_F — Supplies the person record, the country_of_birth attribute, and the effective start and end dates used to anchor the dated lookup for the session date.
• PER_CONTACT_RELATIONSHIPS — Provides the contact relationships for the person, including the contact type and its own start and end dates, enabling the package to identify the relevant contacts (for example, father and mother) at a given point in time.
• FF_FORMULAS_F — Holds the FastFormula definitions that are resolved and executed by RUN_FORMULA to derive the target group from the country-of-birth inputs.

Usage Notes

This package is an internal HRMS localization component. It is typically invoked indirectly rather than called directly by end users. The p_session_date parameter is central to its behaviour: it drives date-effective filtering in GET_COUNTRY_CODE and is passed through to RUN_FORMULA so that the correct version of the formula and the correct dated person data are used. In EBS 12.1.1 and 12.2.2 the package operates under the APPS schema and reads the three documented tables through APPS synonyms.

Because the package is referenced by one other database object, modifications should be treated cautiously as part of the Dutch HRMS localization baseline. Customizations that require target group calculation should call the public entry point rather than reproducing the country-of-birth or formula logic, ensuring consistency with the delivered statutory rules. Testing should include boundary conditions around effective dates, since the package falls back to an alternative query when no record matches the session date exactly.