Search Results work_telephone

Overview

POS_PO_EMPLOYEE_DETAILS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite (documented here for 12.1.1 and 12.2.2, with ETRM metadata validated under 12.2.2). It is a component of the POS — iSupplier Portal product family and is documented simply as a "View to retrieve employee details." Its role within the iSupplier Portal architecture is to expose a denormalized, single-row-per-employee projection of the human resources person and assignment records so that portal pages, concurrent reports, and internal PL/SQL routines can resolve employee identity, contact, and organizational information without re-implementing the effectivity-date and primary-assignment logic themselves.

Because the view resolves the current effective row of the person and assignment records while filtering to employee-type, primary assignments, it is particularly convenient as a lookup source rather than a transactional table. The user search term "work_telephone" corresponds directly to one of the view's exposed columns, which is populated by the HR_GENERAL.GET_WORK_PHONE function and is therefore an important retrieval point for iSupplier contact and notification scenarios.

Underlying Base Objects

Per the documented view text, POS_PO_EMPLOYEE_DETAILS_V is defined over the following objects:

• PER_ALL_PEOPLE_F — the effective-dated person master from which PERSON_ID, EMPLOYEE_NUMBER, name, e-mail, and the ATTRIBUTE1..ATTRIBUTE20 flex columns are drawn.
• PER_ALL_ASSIGNMENTS_F — the effective-dated assignment table, joined on PERSON_ID with PRIMARY_FLAG = 'Y' and ASSIGNMENT_TYPE = 'E'; it supplies A.LOCATION_ID and the POSITION_ID used in the outer join to positions.
• PER_ALL_POSITIONS — outer-joined via POS.POSITION_ID (+) = A.POSITION_ID, providing the POSITION name.
• HR_ORGANIZATION_UNITS — joined on ORGANIZATION_ID and BUSINESS_GROUP_ID matching the person's BUSINESS_GROUP_ID, yielding BUSINESS_GROUP_NAME.
• HR_GENERAL — the HR package invoked for GET_WORK_PHONE, GET_PHONE_NUMBER ('WF'), and GET_PHONE_NUMBER ('P').
• HR_SECURITY — referenced by the documented dependencies and relevant to row-level security behaviour applied to HR person data.

The WHERE clause enforces TRUNC(SYSDATE) BETWEEN the effective start and end dates on both PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, requires EMPLOYEE_NUMBER IS NOT NULL, and restricts to primary employee assignments. This makes the view strictly a "current state" projection rather than a historical one.

Key Columns

The column list published in the ETRM documentation differs in naming from the aliases used in the view text, so consumers should verify names against the live data dictionary. The salient columns are:

• EMPLOYEE_ID (aliased from P.PERSON_ID) — the HR person identifier, used for joins to HR and iSupplier tables.
• EMPLOYEE_NUM and FULL_NAME, FIRST_NAME, LAST_NAME — core identity attributes.
• EMAIL_ADDRESS — the person's e-mail, used for supplier notifications.
• WORK_TELEPHONE — derived from HR_GENERAL.GET_WORK_PHONE(P.PERSON_ID); the primary work telephone number for the employee.
• FAX_NUMBER and PAGER_NUMBER — derived from HR_GENERAL.GET_PHONE_NUMBER for the 'WF' and 'P' phone types respectively at SYSDATE.
• LOCATION_ID, MAILSTOP — location and internal mail routing data from the assignment and person records.
• BUSINESS_GROUP_NAME and POSITION — organizational context from HR_ORGANIZATION_UNITS and PER_ALL_POSITIONS.
• ATTRIBUTE1 through ATTRIBUTE20 — the descriptive flexfield segments carried through from the person record for site-specific use.

Common Use Cases and Queries

Typical scenarios include resolving an iSupplier portal user to a current work telephone number, listing employee contacts within a business group, or driving notification and correspondence processes. A representative query retrieving contact information is:

• SELECT employee_id, full_name, email_address, work_telephone FROM apps.pos_po_employee_details_v WHERE work_telephone IS NOT NULL;
• SELECT business_group_name, employee_num, full_name, position FROM apps.pos_po_employee_details_v ORDER BY business_group_name, full_name;
• SELECT employee_id, work_telephone, fax_number, pager_number FROM apps.pos_po_employee_details_v WHERE employee_id = :p_person_id;

Because the view performs function calls (HR_GENERAL.GET_WORK_PHONE, GET_PHONE_NUMBER) for every qualifying row, it should be filtered narrowly and is best joined to other queries by EMPLOYEE_ID rather than repeatedly invoked in set-based, high-volume processes. The absence of bind-friendly predicates on the phone functions also argues for restricting usage to targeted lookups and low-cardinality reporting rather than large batch extracts.