View: JTF_RS_EMP_ADDRESS_VL

Product:	JTF - CRM Foundation
Description:	This view has employee address.
Implementation/DBA Data:	APPS.JTF_RS_EMP_ADDRESS_VL

View Text

SELECT DISTINCT PER.PERSON_ID
, PER.INTERNAL_LOCATION OFFICE_LOCATION
, PER.WORK_TELEPHONE OFFICE_NUMBER
, PHONE1.PHONE_NUMBER OFFICE_FAX_NUMBER
, ADDR.ADDRESS_LINE1 || ' ' || ADDR.ADDRESS_LINE2 || ' ' || ADDR.ADDRESS_LINE3 HOME_STREET_ADDRESS
, ADDR.TOWN_OR_CITY HOME_CITY
, ADDR.COUNTRY HOME_COUNTRY
, ADDR.POSTAL_CODE HOME_POSTAL_CODE
, ADDR.TELEPHONE_NUMBER_1 HOME_PHONE_NUMBER
, PHONE2.PHONE_NUMBER HOME_FAX
, PHONE3.PHONE_NUMBER PAGER_NUMBER
, PHONE4.PHONE_NUMBER MOBILE_NUMBER
, PER.EMAIL_ADDRESS
, PER.BUSINESS_GROUP_ID
FROM PER_PEOPLE_F PER
, PER_PHONES PHONE1
, PER_PHONES PHONE2
, PER_PHONES PHONE3
, PER_PHONES PHONE4
, PER_ADDRESSES ADDR
WHERE PER.PERSON_ID = ADDR.PERSON_ID(+)
AND ADDR.PRIMARY_FLAG(+)= 'Y'
AND TRUNC(SYSDATE) BETWEEN ADDR.DATE_FROM(+)
AND NVL(ADDR.DATE_TO(+)
, TRUNC(SYSDATE))
AND PHONE1.PARENT_ID(+) = PER.PERSON_ID
AND PHONE1.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PHONE1.PHONE_TYPE(+) = 'WF'
AND TRUNC(SYSDATE) BETWEEN PHONE1.DATE_FROM(+)
AND NVL(PHONE1.DATE_TO(+)
, TRUNC(SYSDATE))
AND PHONE2.PARENT_ID(+) = PER.PERSON_ID
AND PHONE2.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PHONE2.PHONE_TYPE(+) = 'HF'
AND TRUNC(SYSDATE) BETWEEN PHONE2.DATE_FROM(+)
AND NVL(PHONE2.DATE_TO(+)
, TRUNC(SYSDATE))
AND PHONE3.PARENT_ID(+) = PER.PERSON_ID
AND PHONE3.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PHONE3.PHONE_TYPE(+) = 'P'
AND TRUNC(SYSDATE) BETWEEN PHONE3.DATE_FROM(+)
AND NVL(PHONE3.DATE_TO(+)
, TRUNC(SYSDATE))
AND PHONE4.PARENT_ID(+) = PER.PERSON_ID
AND PHONE4.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PHONE4.PHONE_TYPE(+) = 'M'
AND TRUNC(SYSDATE) BETWEEN PHONE4.DATE_FROM(+)
AND NVL(PHONE4.DATE_TO(+)
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE

Columns