VIEW: APPS.CS_JTF_RESOURCES_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'