VIEW: APPS.JTF_RS_EMP_ADDRESS_VL

Source

View Text - Preformatted

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

View Text - HTML Formatted

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