View: CS_JTF_RESOURCES_V

Product:	CS - Service
Description:	Resource Details
Implementation/DBA Data:	APPS.CS_JTF_RESOURCES_V

View Text

SELECT RSC.RESOURCE_ID
, RSC.RESOURCE_NUMBER
, RSC.CATEGORY
, PER.FULL_NAME
, ADDR.ADDRESS_LINE1 || ' ' || ADDR.ADDRESS_LINE2 || ' ' || ADDR.ADDRESS_LINE3 || '
, ' || ADDR.TOWN_OR_CITY || '
, ' || ADDR.COUNTRY || '
, ' || ADDR.POSTAL_CODE ADDRESS
, ADDR.COUNTRY
, PER.WORK_TELEPHONE WORK_PHONE_NUMBER
, MPHONE.PHONE_NUMBER CELL_PHONE_NUMBER
, PPHONE.PHONE_NUMBER PAGER_PHONE_NUMBER
, PER.EMAIL_ADDRESS
, WPHONE.PHONE_NUMBER FAX_NUMBER
, ADDR.TELEPHONE_NUMBER_1 HOME_PHONE_NUMBER
FROM PER_PEOPLE_F PER
, PER_PHONES WPHONE
, /*WPHONE FOR WORK-PHONE*/ PER_PHONES PPHONE
, /*PPHONE FOR PAGER PHONE*/ PER_PHONES MPHONE
, /*MPHONE FOR MOBILE PHONE*/ PER_ADDRESSES ADDR
, JTF_RS_RESOURCE_EXTNS RSC
WHERE PER.PERSON_ID = ADDR.PERSON_ID(+)
AND ADDR.PRIMARY_FLAG(+)= 'Y'
AND WPHONE.PARENT_ID(+) = PER.PERSON_ID
AND WPHONE.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND WPHONE.PHONE_TYPE(+) = 'WF'
AND SYSDATE BETWEEN NVL(WPHONE.DATE_FROM
, SYSDATE)
AND NVL(WPHONE.DATE_TO
, SYSDATE+1)
AND PPHONE.PARENT_ID(+) = PER.PERSON_ID
AND PPHONE.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PPHONE.PHONE_TYPE(+) = 'P'
AND SYSDATE BETWEEN NVL(PPHONE.DATE_FROM
, SYSDATE)
AND NVL(PPHONE.DATE_TO
, SYSDATE+1)
AND MPHONE.PARENT_ID(+) = PER.PERSON_ID
AND MPHONE.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND MPHONE.PHONE_TYPE(+) = 'M'
AND SYSDATE BETWEEN NVL(MPHONE.DATE_FROM
, SYSDATE)
AND NVL(MPHONE.DATE_TO
, SYSDATE+1)
AND RSC.SOURCE_ID = PER.PERSON_ID
AND RSC.CATEGORY = 'EMPLOYEE' UNION SELECT RSC.RESOURCE_ID
, RSC.RESOURCE_NUMBER
, RSC.CATEGORY
, DECODE(HZP.PARTY_TYPE
, 'ORGANIZATION'
, HZP.PARTY_NAME
, 'PERSON'
, HZP.PERSON_PRE_NAME_ADJUNCT||' '||HZP.PERSON_FIRST_NAME||' '||DECODE(HZP.PERSON_MIDDLE_NAME
, NULL
, NULL
, HZP.PERSON_MIDDLE_NAME||' ')||DECODE(HZP.PERSON_LAST_NAME
, NULL
, NULL
, HZP.PERSON_LAST_NAME||' ')) RESOURCE_NAME
, HZP.ADDRESS1||DECODE(HZP.ADDRESS1
, NULL
, NULL
, '
, '
, '
, ')||HZP.ADDRESS2||DECODE(HZP.ADDRESS2
, NULL
, NULL
, '
, '
, '
, ')||HZP.ADDRESS3||DECODE(HZP.ADDRESS3
, NULL
, NULL
, '
, '
, '
, ')||HZP.ADDRESS4||DECODE(HZP.ADDRESS4
, NULL
, NULL
, '
, '
, '
, ')||HZP.CITY||DECODE(HZP.CITY
, NULL
, NULL
, '
, '
, '
, ')||HZP.STATE||DECODE(HZP.STATE
, NULL
, NULL
, '
, '
, '
, ')||HZP.POSTAL_CODE||DECODE(HZP.POSTAL_CODE
, NULL
, NULL
, '
, '
, '
, ')||HZP.PROVINCE||DECODE(HZP.PROVINCE
, NULL
, NULL
, '
, '
, '
, ')||HZP.COUNTY||DECODE(HZP.COUNTRY
, NULL
, NULL
, '
, '
, '
, ') ADDRESS
, HZP.COUNTRY
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_WORK_PHONE_TYPE')) WORK_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_CELL_PHONE_TYPE')) CELL_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_PAGER_PHONE_TYPE')) PAGER_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_EMAIL(RSC.SOURCE_ID) EMAIL_ADDRESS
, CS_PARTIES_PKG.GET_PARTY_FAX(RSC.SOURCE_ID) FAX_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_HOME_PHONE_TYPE')) HOME_PHONE_NUMBER
FROM HZ_PARTIES HZP
, JTF_RS_RESOURCE_EXTNS RSC
WHERE RSC.SOURCE_ID = HZP.PARTY_ID
AND RSC.CATEGORY = 'PARTY' UNION SELECT RSC.RESOURCE_ID
, RSC.RESOURCE_NUMBER
, RSC.CATEGORY
, DECODE(HZP.PARTY_TYPE
, 'ORGANIZATION'
, HZP.PARTY_NAME
, 'PERSON'
, HZP.PERSON_PRE_NAME_ADJUNCT||' '||HZP.PERSON_FIRST_NAME||' '||DECODE(HZP.PERSON_MIDDLE_NAME
, NULL
, NULL
, HZP.PERSON_MIDDLE_NAME||' ')||DECODE(HZP.PERSON_LAST_NAME
, NULL
, NULL
, HZP.PERSON_LAST_NAME||' ')) RESOURCE_NAME
, HZL.ADDRESS1||DECODE(HZL.ADDRESS1
, NULL
, NULL
, '
, '
, '
, ')||HZL.ADDRESS2||DECODE(HZL.ADDRESS2
, NULL
, NULL
, '
, '
, '
, ')||HZL.ADDRESS3||DECODE(HZL.ADDRESS3
, NULL
, NULL
, '
, '
, '
, ')||HZL.ADDRESS4||DECODE(HZL.ADDRESS4
, NULL
, NULL
, '
, '
, '
, ')||HZL.CITY||DECODE(HZL.CITY
, NULL
, NULL
, '
, '
, '
, ')||HZL.STATE||DECODE(HZL.STATE
, NULL
, NULL
, '
, '
, '
, ')||HZL.POSTAL_CODE||DECODE(HZL.POSTAL_CODE
, NULL
, NULL
, '
, '
, '
, ')||HZL.PROVINCE||DECODE(HZL.PROVINCE
, NULL
, NULL
, '
, '
, '
, ')||HZL.COUNTY||DECODE(HZL.COUNTRY
, NULL
, NULL
, '
, '
, '
, ') ADDRESS
, HZL.COUNTRY
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_WORK_PHONE_TYPE')) WORK_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_CELL_PHONE_TYPE')) CELL_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_PAGER_PHONE_TYPE')) PAGER_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_EMAIL(RSC.SOURCE_ID) EMAIL_ADDRESS
, CS_PARTIES_PKG.GET_PARTY_FAX(RSC.SOURCE_ID) FAX_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(RSC.SOURCE_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_HOME_PHONE_TYPE')) HOME_PHONE_NUMBER
FROM HZ_PARTIES HZP
, HZ_LOCATIONS HZL
, JTF_RS_RESOURCE_EXTNS RSC
WHERE RSC.SOURCE_ID = HZP.PARTY_ID
AND RSC.ADDRESS_ID = HZL.LOCATION_ID
AND RSC.CATEGORY = 'PARTNER'

Columns