eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRFV_US_ADDRESSES

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,peo.full_name person_name ,ftraT.territory_short_name address_style /*bug fix 2649737*/ ,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 ,addr.address_line1 address_line1 ,addr.address_line2 address_line2 ,addr.address_line3 address_line3 ,addr.comments addr_comments ,ftrcT.territory_short_name country /*bug fix 2649737*/ ,addr.postal_code zip_code ,addr.region_1 county ,hr_bis.bis_decode_lookup('US_STATE',addr.region_2) state_name ,addr.region_2 state_code ,addr.telephone_number_1 telephone_number_1 ,addr.telephone_number_2 telephone_number_2 ,addr.telephone_number_3 telephone_number_3 ,addr.town_or_city city ,peo.employee_number employee_number ,hr_bis.bis_decode_lookup('YES_NO', peo.current_employee_flag) current_employee_flag ,hr_bis.bis_decode_lookup('YES_NO', peo.current_applicant_flag) current_applicant_flag ,ptt.user_person_type person_type ,HRI_BPL_PERSON_TYPE.get_emp_user_person_type (peo.effective_start_date ,peo.person_id) employee_person_type ,HRI_BPL_PERSON_TYPE.get_apl_user_person_type (peo.effective_start_date ,peo.person_id) applicant_person_type ,HRI_BPL_PERSON_TYPE.get_concat_user_person_type (peo.effective_start_date ,peo.person_id) user_concat_person_type ,'_DF:PER:PER_ADDRESSES:addr' ,addr.style address_style_code ,addr.address_type address_type_code ,addr.primary_flag primary_flag_code ,addr.region_2 region2_code ,peo.current_employee_flag current_employee_flag_code ,peo.current_applicant_flag current_applicant_flag_code ,addr.business_group_id business_group_id ,addr.person_id person_id ,ptt.person_type_id person_type_id FROM per_addresses addr ,hr_all_organization_units_tl bgrT ,per_person_types_tl ptt ,per_people_x peo ,fnd_territories_tl ftraT ,fnd_territories_tl ftrcT WHERE TRUNC(sysdate) BETWEEN addr.date_from AND NVL(addr.date_to,TRUNC(sysdate)) AND addr.style = 'US' AND peo.person_id = addr.person_id AND peo.person_type_id = ptt.person_type_id AND ptt.language = userenv('LANG') AND addr.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND ftraT.language (+) = userenv('LANG') AND addr.style = ftraT.territory_code (+) AND ftrcT.language (+) = userenv('LANG') AND addr.country = ftrcT.territory_code(+) AND addr.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, addr.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, FTRAT.TERRITORY_SHORT_NAME ADDRESS_STYLE /*BUG FIX 2649737*/
, 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
, ADDR.ADDRESS_LINE1 ADDRESS_LINE1
, ADDR.ADDRESS_LINE2 ADDRESS_LINE2
, ADDR.ADDRESS_LINE3 ADDRESS_LINE3
, ADDR.COMMENTS ADDR_COMMENTS
, FTRCT.TERRITORY_SHORT_NAME COUNTRY /*BUG FIX 2649737*/
, ADDR.POSTAL_CODE ZIP_CODE
, ADDR.REGION_1 COUNTY
, HR_BIS.BIS_DECODE_LOOKUP('US_STATE'
, ADDR.REGION_2) STATE_NAME
, ADDR.REGION_2 STATE_CODE
, ADDR.TELEPHONE_NUMBER_1 TELEPHONE_NUMBER_1
, ADDR.TELEPHONE_NUMBER_2 TELEPHONE_NUMBER_2
, ADDR.TELEPHONE_NUMBER_3 TELEPHONE_NUMBER_3
, ADDR.TOWN_OR_CITY CITY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_EMPLOYEE_FLAG) CURRENT_EMPLOYEE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_APPLICANT_FLAG) CURRENT_APPLICANT_FLAG
, PTT.USER_PERSON_TYPE PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_USER_PERSON_TYPE (PEO.EFFECTIVE_START_DATE
, PEO.PERSON_ID) EMPLOYEE_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_APL_USER_PERSON_TYPE (PEO.EFFECTIVE_START_DATE
, PEO.PERSON_ID) APPLICANT_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CONCAT_USER_PERSON_TYPE (PEO.EFFECTIVE_START_DATE
, PEO.PERSON_ID) USER_CONCAT_PERSON_TYPE
, '_DF:PER:PER_ADDRESSES:ADDR'
, ADDR.STYLE ADDRESS_STYLE_CODE
, ADDR.ADDRESS_TYPE ADDRESS_TYPE_CODE
, ADDR.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ADDR.REGION_2 REGION2_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, ADDR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ADDR.PERSON_ID PERSON_ID
, PTT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM PER_ADDRESSES ADDR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES_TL PTT
, PER_PEOPLE_X PEO
, FND_TERRITORIES_TL FTRAT
, FND_TERRITORIES_TL FTRCT
WHERE TRUNC(SYSDATE) BETWEEN ADDR.DATE_FROM
AND NVL(ADDR.DATE_TO
, TRUNC(SYSDATE))
AND ADDR.STYLE = 'US'
AND PEO.PERSON_ID = ADDR.PERSON_ID
AND PEO.PERSON_TYPE_ID = PTT.PERSON_TYPE_ID
AND PTT.LANGUAGE = USERENV('LANG')
AND ADDR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND FTRAT.LANGUAGE (+) = USERENV('LANG')
AND ADDR.STYLE = FTRAT.TERRITORY_CODE (+)
AND FTRCT.LANGUAGE (+) = USERENV('LANG')
AND ADDR.COUNTRY = FTRCT.TERRITORY_CODE(+)
AND ADDR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ADDR.BUSINESS_GROUP_ID) WITH READ ONLY