Results for “current_npw_flag”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HR_FNDSCAUS_PERSON_LOV_V view is an APPS-owned database object residing in the PER (Human Resources) product module of Oracle E-Business Suite. Its documented purpose is to support the Person List of Values (LOV) displayed within the AOL Define User form, FNDSCAUS. When an administrator creates or updates an application user and associates that user with a person record, the form invokes this view to populate the person LOV with valid, searchable person entries.

The view is delivered in VALID status within the APPS schema and functions as a presentation-layer object rather than a transactional one. Its central design concern is the effective-dating model of Oracle HRMS: person records in PER_ALL_PEOPLE_F are date-tracked, so a single person can have multiple historical and future-dated rows. The view consolidates this complexity by applying date logic internally and returning a single, de-normalised row per qualifying person, together with a visual indicator of current employment status. This makes it suitable for direct binding to an LOV region without additional filtering logic in the form or report.

Underlying Base Objects

The documented view text defines the object over two base objects:

  • PER_ALL_PEOPLE_F — the primary HRMS person table, supplying name, employee number, e-mail address, person identifier, business group, and the effective date range used for filtering.
  • PER_BUSINESS_GROUPS — supplying the business group name associated with each person record.

The ETRM metadata lists HR_ALL_ORGANIZATION_UNITS_TL and PER_ALL_PEOPLE_F as referenced base objects. PER_BUSINESS_GROUPS is itself a synonym or view defined over HR_ALL_ORGANIZATION_UNITS_TL, which reconciles the two perspectives.

The view body is a UNION of two SELECT statements. The first returns all persons whose effective date range brackets the current system date, using TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE. The second returns persons whose earliest effective-dated record begins in the future, joining on a correlated subquery that selects MIN(EFFECTIVE_START_DATE) per PERSON_ID. Together, the branches present both currently effective and not-yet-effective persons, ensuring the LOV does not omit newly hired or future-dated assignments.

Key Columns

  • FULL_NAME — the person's complete display name, sourced from PER_ALL_PEOPLE_F; this is the value typically shown in the LOV.
  • CURRENT_EMPLOYEE_FLAG — derived via DECODE(P.CURRENT_EMPLOYEE_FLAG, 'Y', '*', 'Y', '*', ''). Because the underlying column is defined as a VARCHAR2(1) rather than a boolean Y/N value, this construct normalises any value beginning with or equal to Y into an asterisk, and returns null otherwise. The asterisk visually marks current employees in the LOV list, allowing the administrator to distinguish active staff from contingent workers, applicants, or ex-employees.
  • BUSINESS_GROUP_NAME — projected from H.NAME in PER_BUSINESS_GROUPS, identifying the enterprise or legislative business group to which the person belongs.
  • EMPLOYEE_NUMBER — the system-generated or manually assigned person number, useful for disambiguation where names are duplicated.
  • EMAIL_ADDRESS — the work e-mail address held on the person record.
  • PERSON_ID — the primary key of the person, used as the hidden return value of the LOV and stored against the FND_USER record.

Common Use Cases and Queries

The principal consumer is the Define User form itself, which queries the view through the standard LOV mechanism. Developers extending the form, building concurrent programs, or constructing custom self-service pages may query the view directly, benefiting from its built-in effective-date handling. Typical patterns include locating persons by partial name, restricting to current employees, and resolving PERSON_ID values from an e-mail address.

  • List all current employees: SELECT person_id, full_name, employee_number FROM apps.hr_fndscaus_person_lov_v WHERE current_employee_flag = '*' ORDER BY full_name;
  • Fuzzy name search: SELECT person_id, full_name, email_address FROM apps.hr_fndscaus_person_lov_v WHERE UPPER(full_name) LIKE UPPER('%:search%');
  • Resolve a person by e-mail: SELECT person_id, full_name FROM apps.hr_fndscaus_person_lov_v WHERE UPPER(email_address) = UPPER(:email);

Because the view performs the date-range filtering on PER_ALL_PEOPLE_F, callers should not apply their own effective-date predicates, which could reproduce rows or exclude future-dated entries unexpectedly. For high-volume reporting, joining directly to the base tables with explicit date criteria is preferable, as the UNION and correlated subquery carry a cost not suited to bulk extraction.