[Home] [Help]
SELECT par.person_name candidate_name ,addr.style address_style ,hr_bis.bis_decode_lookup('ADDRESS_TYPE',addr.address_type) address_type ,hr_bis.bis_decode_lookup('YES_NO',addr.primary_flag) primary_flag ,addr.date_from addr_date_from ,addr.date_to addr_date_to ,NVL(addr.date_to, hr_general.end_of_time) addr_date_to_nn ,addr.address_line1 address_line1 ,addr.address_line2 address_line2 ,addr.address_line3 address_line3 ,addr.comments addr_comments ,ftr.territory_short_name country ,addr.postal_code postal_code ,addr.region_1 region_1 ,addr.region_2 region_2 ,addr.region_3 region_3 ,addr.town_or_city city ,'_DF:PER:PER_ADDRESSES:addr' ,'_DF:PER:Address Structure:addr' ,addr.address_type address_type_code ,addr.primary_flag primary_flag_code ,addr.country country_code ,addr.business_group_id business_group_id ,addr.party_id party_id ,addr.address_id address_id FROM hz_person_profiles par ,irc_notification_preferences inp ,per_addresses addr ,fnd_territories_tl ftr WHERE par.party_id = addr.party_id AND inp.party_id = par.party_id AND inp.allow_access = 'Y' AND addr.country = ftr.territory_code (+) AND ftr.language (+) = USERENV('LANG') AND trunc(sysdate) between NVL(par.effective_start_date, hr_general.start_of_time) and NVL(par.effective_end_date , hr_general.end_of_time) WITH READ ONLY
SELECT PAR.PERSON_NAME CANDIDATE_NAME
, ADDR.STYLE ADDRESS_STYLE
, HR_BIS.BIS_DECODE_LOOKUP('ADDRESS_TYPE'
, ADDR.ADDRESS_TYPE) ADDRESS_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ADDR.PRIMARY_FLAG) PRIMARY_FLAG
, ADDR.DATE_FROM ADDR_DATE_FROM
, ADDR.DATE_TO ADDR_DATE_TO
, NVL(ADDR.DATE_TO
, HR_GENERAL.END_OF_TIME) ADDR_DATE_TO_NN
, ADDR.ADDRESS_LINE1 ADDRESS_LINE1
, ADDR.ADDRESS_LINE2 ADDRESS_LINE2
, ADDR.ADDRESS_LINE3 ADDRESS_LINE3
, ADDR.COMMENTS ADDR_COMMENTS
, FTR.TERRITORY_SHORT_NAME COUNTRY
, ADDR.POSTAL_CODE POSTAL_CODE
, ADDR.REGION_1 REGION_1
, ADDR.REGION_2 REGION_2
, ADDR.REGION_3 REGION_3
, ADDR.TOWN_OR_CITY CITY
, '_DF:PER:PER_ADDRESSES:ADDR'
, '_DF:PER:ADDRESS STRUCTURE:ADDR'
, ADDR.ADDRESS_TYPE ADDRESS_TYPE_CODE
, ADDR.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ADDR.COUNTRY COUNTRY_CODE
, ADDR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ADDR.PARTY_ID PARTY_ID
, ADDR.ADDRESS_ID ADDRESS_ID
FROM HZ_PERSON_PROFILES PAR
, IRC_NOTIFICATION_PREFERENCES INP
, PER_ADDRESSES ADDR
, FND_TERRITORIES_TL FTR
WHERE PAR.PARTY_ID = ADDR.PARTY_ID
AND INP.PARTY_ID = PAR.PARTY_ID
AND INP.ALLOW_ACCESS = 'Y'
AND ADDR.COUNTRY = FTR.TERRITORY_CODE (+)
AND FTR.LANGUAGE (+) = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN NVL(PAR.EFFECTIVE_START_DATE
, HR_GENERAL.START_OF_TIME)
AND NVL(PAR.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME) WITH READ ONLY
|
|
|
|