Search Results get_count_nr_alien
Overview
APPS.PQH_NR_ALIEN_PKG is a public PL/SQL package in the Oracle E-Business Suite HR/Payroll family, delivered under the APPS schema with the AUTHID CURRENT_USER privilege model. The package name encodes its two domains of responsibility: the PQH prefix identifies it as belonging to the Public Sector/Federal HR component of Oracle HRMS, while the NR_ALIEN fragment identifies its subject matter as non-resident alien (NRA) regulatory reporting. Its principal business purpose is to determine whether a given person holds non-resident alien status as of a specific reporting date, which is a prerequisite for U.S. federal tax and immigration reporting obligations placed on employers — most notably IRS Form 1042-S reporting of amounts paid to foreign persons and the accompanying substantial-presence and tax-residency determinations.
The package header is version-dated 2002, with an RCS identifier of pqhnrinf.pkh 115.1, indicating that the interface has remained stable across many releases and is functionally identical in EBS 12.1.1 and 12.2.2. It is a small, single-purpose package containing exactly one documented function. This narrow scope is characteristic of Oracle's practice of isolating frequently reused eligibility checks into standalone utility packages that can be called from multiple concurrent programs and business flows without duplicating the underlying logic.
Key Procedures and Functions
The ETRM metadata documents a single public function, GET_COUNT_NR_ALIEN, which returns a VARCHAR2 value. Because the return type is a character string rather than a BOOLEAN or NUMBER, the function is designed to return a status/indicator token — typically a count or a Y/N-style flag — that can be consumed directly by a report query, a fast formula, or a host language that cannot accept PL/SQL BOOLEAN types. The function accepts two inputs: a person identifier and a report date, so the non-resident alien determination is evaluated as of a caller-supplied effective date rather than the current system date. This date-parameterised design is essential for retroactive reporting, where a payment period or tax year must be assessed against the alien status in force at that time.
Callers should note that because the function is the sole documented entry point, all NRA logic in this package is encapsulated behind it; no standalone procedures for maintenance or setup are exposed. The package is classified as API type OTHER, meaning it is not an OPEN interface or a business-entity API but a query-oriented utility.
Tables Accessed
The package reads the table PER_PEOPLE_EXTRA_INFO through an APPS synonym. This table is the standard repository for descriptive-flexfield and user-defined attribute data attached to a person record in Oracle HRMS. Non-resident alien indicators — visa classification, residency status, treaty eligibility, and similar attributes — are not core columns of PER_PEOPLE but are stored as extra information records keyed by person and information type. GET_COUNT_NR_ALIEN therefore resolves the person and date arguments against the person's extra information rows to determine the count or status of NRA-designated records. Because the table is date-tracked via effective start and end dates, the report-date argument is translated directly into the effective-date predicate applied to that query. No writes to the table are documented, consistent with the package's read-only reporting role.
Usage Notes
The package is referenced by two other APPS packages, confirming that it is invoked programmatically rather than through a Oracle Forms UI or a dedicated concurrent program. Typical invocation patterns include: report-generation packages that must label or segregate payments to non-resident aliens; payroll and tax-reporting routines that drive 1042-S or year-end processing; and custom client code that needs a lightweight, reusable residency test. Because the function returns VARCHAR2, it can be safely embedded in SQL statements, used within fast formulas, and called from SQL*Plus or BI Publisher data models without type-conversion issues. Developers extending HRMS reporting should call PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN rather than querying PER_PEOPLE_EXTRA_INFO directly, since the packaged function centralises the effective-dating and information-type filter logic and insulates custom code from changes to the underlying extra information configuration.