Search Results cp_report_date

Overview

APPS.PQH_NR_ALIEN_PKG is a PL/SQL package body belonging to the Oracle E-Business Suite HR/Payroll product family, specifically the Public Sector HR (PQH) module. The prefix "PQH" identifies it as part of the Oracle Public Sector/Position Management and related HR legislative extensions, while the "NR_ALIEN" designation indicates its purpose: identifying and reporting on non-resident alien (NRA) employees for tax and immigration compliance purposes. This package serves a narrow, specialized business function—determining whether a given person qualifies as a non-resident alien on a specified report date, based on visa information stored in the person's HR records. The package was originally shipped under version 115.1 (header file pqhnrinf.pkb, dated 2002), and it has been carried forward into the EBS 12.1.1 and 12.2.2 environments as part of the standard legislative data model.

Key Procedures and Functions

• GET_COUNT_NR_ALIEN — The single documented function in this package. It returns a VARCHAR2 value indicating whether the specified person qualifies as a non-resident alien as of a given report date. The function accepts a person identifier and a report date, then opens an implicit cursor that queries the person's visa detail information. The cursor logic checks whether the report date falls within the range defined by the visa's start and end date attributes (information7 and information8 in the extract) and whether the visa type code falls within the set ('04','05','06','07','12'), which correspond to qualifying non-resident alien visa categories. If a qualifying record is found, the function returns a match indicator; otherwise it returns NULL or an empty result. The function's date comparison is performed using fnd_date.date_to_canonical, converting the supplied report date to the canonical format for reliable comparison against the stored information attributes.

Tables Accessed

• PER_PEOPLE_EXTRA_INFO — Accessed via the APPS synonym. This table stores person-level descriptive flexfield and extra information records, keyed by PERSON_ID and INFORMATION_TYPE. The package reads records where INFORMATION_TYPE is 'PER_US_VISA_DETAILS', using the information7 and information8 columns as the visa validity start and end dates and information9 as the visa type code. This is the sole documented table referenced by the package, and it is read-only—the package performs a SELECT only and does not insert, update, or delete data.

Usage Notes

Because the package exposes only a single query function, it is typically invoked as a helper routine from larger payroll or legislative reporting processes rather than directly by end users. ETRM metadata indicates that the package is referenced by two other packages, confirming its role as a shared utility. Common invocation paths include legislative compliance reports, payroll tax processing for non-resident alien withholding, and custom extensions that need to determine NRA status for a person over a reporting period.

The cp_report_date parameter visible in the source corresponds to the report date supplied by the caller—this is the "as of" date against which visa validity is evaluated. Callers should supply a valid DATE value, as the function converts it through fnd_date.date_to_canonical before comparison. Because the function returns a VARCHAR2 rather than a BOOLEAN, callers must test for a non-null return value to confirm NRA status. In EBS 12.1.1 and 12.2.2 the package requires no configuration or setup beyond the presence of PER_US_VISA_DETAILS extra information records on the person record; where those records are absent, the function returns no result. No concurrent program is registered directly against this package; it is consumed programmatically by dependent packages and forms-based logic.