Search Results home_fax

Overview

JTF_RS_EMP_ADDRESS_VL is a CRM Foundation (JTF) view owned by the APPS schema that exposes a consolidated, denormalised record of an employee's address and telephone contact details. It is one of the resource-management views used by the JTF "RS" (Resource) family of objects, which underpins the Oracle CRM resource model shared by Oracle Sales, Oracle Service, Oracle Field Service, and related modules.

The view presents, for each valid person, the primary address together with the individual telephone numbers held against the person record, flattened into a single row. In EBS 12.1.1 and 12.2.2 the object is reported as VALID. Its principal role is to provide a stable, reporting-friendly projection of HR phone and address data so that CRM resource queries do not need to join the underlying PER_PHONES table four times. It is typically consumed by concurrent programs, Oracle Reports, Discoverer/OBIEE workbooks, and custom APIs that need a single row per employee carrying office, home, fax, pager, and mobile numbers.

Underlying Base Objects

The documented view metadata lists the referenced base objects as HR_GENERAL (PACKAGE), HR_PERSON_NAME (PACKAGE), HR_SECURITY (PACKAGE), PER_ADDRESSES (SYNONYM), PER_PEOPLE_F (VIEW), and PER_PHONES (SYNONYM). PER_PEOPLE_F and PER_ADDRESSES are date-tracked (effective-dated) objects, while PER_PHONES supplies the numbered telephone attributes.

The view text confirms the join mechanics. PER_PEOPLE_F is the driving row source, outer-joined on PERSON_ID to PER_ADDRESSES where PRIMARY_FLAG = 'Y'. It is joined a further four times to PER_PHONES — aliased PHONE1 through PHONE4 — each filtered by a distinct PHONE_TYPE. Because every join to PER_ADDRESSES and PER_PHONES is an outer join, a person with no address or no phone record still returns a row, with NULLs in the affected columns. The presence of HR_SECURITY and HR_GENERAL in the dependency list indicates the view participates in HR security and utility logic that govern which employees a responsibility may see.

Key Columns

• PERSON_ID — Primary key linking back to PER_PEOPLE_F and the JTF resource tables.
• OFFICE_LOCATION — Taken from PER.INTERNAL_LOCATION.
• OFFICE_NUMBER — PER.WORK_TELEPHONE, the work number on the person record.
• OFFICE_FAX_NUMBER — PHONE1.PHONE_NUMBER, the phone record with PHONE_TYPE 'WF' (work fax).
• HOME_STREET_ADDRESS — Concatenation of ADDRESS_LINE1, ADDRESS_LINE2 and ADDRESS_LINE3.
• HOME_CITY, HOME_COUNTRY, HOME_POSTAL_CODE — Address attributes from the primary PER_ADDRESSES row.
• HOME_PHONE_NUMBER — ADDR.TELEPHONE_NUMBER_1 from the address record.
• HOME_FAX — PHONE2 (PHONE_TYPE 'HF'), PAGER_NUMBER — PHONE3 (PHONE_TYPE 'P'), MOBILE_NUMBER — PHONE4 (PHONE_TYPE 'M').
• EMAIL_ADDRESS, BUSINESS_GROUP_ID — Person-level contact and organisational context.

Common Use Cases and Queries

The view is most often used when a report must display an employee's full contact profile on one line. The four PER_PHONES joins are date-filtered with TRUNC(SYSDATE) BETWEEN DATE_FROM and NVL(DATE_TO, TRUNC(SYSDATE)), so only currently effective phones are returned. Example:

• SELECT person_id, office_location, office_number, mobile_number, email_address FROM jtf_rs_emp_address_vl WHERE business_group_id = :p_bg_id;
• SELECT person_id, home_city, home_phone_number FROM jtf_rs_emp_address_vl WHERE home_country = 'US';
• Joining the view to JTF_RS_RESOURCE_EXTN_VL or PER_ALL_PEOPLE_F on PERSON_ID to enrich resource listings with phone and address detail.
• Feeding CRM letter and email merge templates that require a formatted work or home address and a preferred telephone number.

Because the view references HR_SECURITY, results are constrained by the operating user's HR security profile; queries run under a broad responsibility may return fewer rows than expected. The DISTINCT keyword protects against duplicate rows when multiple address records exist, but it also means the view is not suitable for row-level update operations.