Search Results work_telephone




Overview
Explore in AI Chat

Overview

ICX_PO_LIST_USERS_V is a database view owned by the APPS schema and delivered as part of the Oracle iProcurement (ICX) product family. It is documented in ETRM as a "List of Users view," and its purpose is to expose a consolidated, filtered list of Oracle E-Business Suite users who are eligible to act as preparers of purchase requisitions. Rather than presenting the full population of employees or FND users, the view deliberately restricts its output to persons who appear as a PREPARER_ID on at least one record in PO_REQUISITION_HEADERS. This makes it a functional list, not a master list — it answers the question "who has actually prepared requisitions," which is the relevant population for iProcurement list-of-values, workflow routing, and preparer-selection screens.

In EBS 12.1.1 and 12.2.2 the view is used primarily for reporting, integration, and as a source for iProcurement pages that display preparer contact details. Because it joins human resources data (phone, location, mailing stop) with FND user credentials and GL cost center derivations, it serves as a convenient denormalized source for downstream extracts and custom concurrent programs.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects: FND_USER (SYNONYM), HR_GENERAL (PACKAGE), HR_LOCATIONS (VIEW), HR_ORGANIZATION_UNITS (VIEW), HR_PERSON_NAME (PACKAGE), HR_SECURITY (PACKAGE), ICX_GET (PACKAGE), PER_ASSIGNMENTS_F (VIEW), PER_PEOPLE_F (VIEW), and PO_REQUISITION_HEADERS (SYNONYM).

The core FROM clause joins PER_PEOPLE_F (the person/employee record), PER_ASSIGNMENTS_F (the primary assignment), FND_USER (the application login), HR_ORGANIZATION_UNITS (the assignment organization), and HR_LOCATIONS (the assignment location). Effective-dating predicates are applied to both PER_PEOPLE_F and PER_ASSIGNMENTS_F using TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE, so only currently effective rows are returned. The PAF.PRIMARY_FLAG = 'Y' filter ensures only the primary assignment is used, eliminating duplicate person rows. Outer joins to FND_USER and HR_LOCATIONS permit persons without a login or without a location to still appear. The preparer restriction is enforced through a subquery on PO_REQUISITION_HEADERS grouped by PREPARER_ID, and SELECT DISTINCT prevents duplication when a person has multiple requisitions. HR_GENERAL.GET_WORK_PHONE, ICX_GET.GET_GL_VALUE, and HR_PERSON_NAME are PL/SQL packages invoked inline.

Key Columns

  • PERSON_ID — The PER_PEOPLE_F surrogate key, the primary linkage to HR and requisition data.
  • FIRST_NAME, LAST_NAME, FULL_NAME — Person name attributes exposed from PER_PEOPLE_F.
  • WORK_TELEPHONE — Populated by HR_GENERAL.GET_WORK_PHONE(PERSON_ID); this is the column surfaced when users search for "work_telephone." It returns the person's current work phone number as maintained in HR.
  • USER_NAME — The FND_USER login, outer-joined on FNU.EMPLOYEE_ID = PPF.PERSON_ID; may be null for persons without an application account.
  • MAILSTOP — The person's internal mail stop from PER_PEOPLE_F.
  • ORGANIZATION_NAME — From HR_ORGANIZATION_UNITS via the primary assignment; labeled HOU.NAME in the view text.
  • ATTRIBUTE1–ATTRIBUTE20, ATTRIBUTE_CATEGORY — The descriptive flexfield (DFF) columns from PER_PEOPLE_F, enabling customer-specific person attributes to be reported alongside standard data.
  • LOCATION_CODE (LOC) — The location code from HR_LOCATIONS.
  • DEFAULT_CODE_COMBINATION_ID — The default GL code combination from the primary assignment.
  • COST_CENTER — Derived via ICX_GET.GET_GL_VALUE(101, 'GL#', 101, DEFAULT_CODE_COMB_ID, 'COST CENTER'), resolving the assignment's default account to its cost center segment.

Common Use Cases and Queries

Typical uses include validating preparer contact information, feeding downstream reporting extracts, and populating preparer selection lists. To retrieve work telephone numbers for known preparers:

  • SELECT PERSON_ID, FULL_NAME, USER_NAME, WORK_TELEPHONE FROM APPS.ICX_PO_LIST_USERS_V WHERE WORK_TELEPHONE IS NOT NULL ORDER BY FULL_NAME;
  • SELECT FULL_NAME, ORGANIZATION_NAME, LOCATION_CODE, COST_CENTER FROM APPS.ICX_PO_LIST_USERS_V WHERE USER_NAME = :p_user_name;
  • SELECT ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2 FROM APPS.ICX_PO_LIST_USERS_V WHERE PERSON_ID = :p_person_id;

Because WORK_TELEPHONE is generated by a PL/SQL function call per row, queries filtering on it should be limited to specific persons where possible. Note also that the view is implicitly subject to HR_SECURITY through the underlying HR views, so results may be restricted by the querying user's security profile.