Search Results i9_expiry_date




Overview
Explore in AI Chat

Overview

APPS.HRFV_US_PERSONAL_DETAILS is a read-only reporting view in Oracle E-Business Suite that consolidates United States-specific personal and regulatory attributes for persons (employees and other person records) defined within a US-payroll business group. It is part of the HR foundation ("HRFV" naming convention) family of flexfield views and is intended to expose a denormalized, user-friendly projection of the PER_PEOPLE_X record joined to the owning business group. Because the view resolves coded lookup identifiers into descriptive values and converts the canonical date storage format into a proper DATE, it is well suited to ad hoc reporting, extracts, and integration interfaces that must present human-readable US personal details without the developer having to re-implement decode logic. The object is owned by APPS and is defined WITH READ ONLY, so it cannot be used as an update target. The view is restricted to business groups whose legislation_code is 'US', and it further constrains rows by the business group security profile in effect at run time.

Underlying Base Objects

The view text selects from two objects: PER_BUSINESS_GROUPS (aliased BGR) and PER_PEOPLE_X (aliased PEO). PER_PEOPLE_X is the datetracked, effective-dated view over the PER_PEOPLE_F/PER_PEOPLE base tables and supplies all personnel attributes. PER_BUSINESS_GROUPS is joined on organization_id = business_group_id and filtered to legislation_code = 'US'. In addition, the SELECT list invokes several PL/SQL packages: HR_BIS.BIS_DECODE_L lookup translates stored lookup codes into their meaning for ethnic group, I-9 state, visa type, veteran status, yes/no flags, and new-hire status; FND_DATE.CANONICAL_TO_DATE converts the canonical (internal) date form of PER_INFORMATION3 into an Oracle DATE. The documented metadata also references HR_GENERAL, HR_PERSON_NAME and HR_SECURITY packages, which are used for security filtering and name handling. The final predicate peo.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, peo.business_group_id) enforces business-group-level row security.

Key Columns

Common Use Cases and Queries

Typical uses include producing US new-hire and I-9 audit listings, extracting SSN and veteran/ethnic data for regulatory or payroll feeds, and validation reports that reconcile the stored lookup codes against their meanings. Because the view honors security profiles, it is safe to expose to reporting users within their authorized business group.

Retrieve personal details for a specific person by SSN:

SELECT person_name, employee_number, social_security_number,
       i9, i9_expiry_date, visa_type
FROM   apps.hrfv_us_personal_details
WHERE  social_security_number = :p_ssn;

List persons with an expiring I-9:

SELECT person_name, i9, i9_expiry_date
FROM   apps.hrfv_us_personal_details
WHERE  i9_expiry_date < SYSDATE + 30
ORDER BY i9_expiry_date;

Aggregate headcount by new-hire status:

SELECT new_hire_status, COUNT(*)
FROM   apps.hrfv_us_personal_details
GROUP BY new_hire_status;