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_PERSONAL_DETAILS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,peo.full_name person_name ,peo.employee_number employee_number ,peo.title title ,peo.middle_names middle_names ,peo.known_as preferred_name ,peo.first_name first_name ,peo.last_name last_name ,peo.pre_name_adjunct prefix ,peo.suffix suffix ,hr_bis.bis_decode_lookup('SEX',peo.sex) gender ,peo.previous_last_name previous_last_name ,hr_bis.bis_decode_lookup('YES_NO',peo.registered_disabled_flag) registered_disabled ,peo.date_of_birth date_of_birth ,DECODE(DATE_OF_BIRTH, NULL, NULL, FLOOR(MONTHS_BETWEEN( SYSDATE, DATE_OF_BIRTH)/12)) age ,peo.town_of_birth town_of_birth ,peo.region_of_birth region_of_birth ,ftrT.TERRITORY_SHORT_NAME country_of_birth ,DECODE(peo.marital_status, NULL, NULL, hr_bis.bis_decode_lookup('MAR_STATUS',peo.marital_status)) marital_status ,DECODE(peo.nationality, NULL, NULL, hr_bis.bis_decode_lookup('NATIONALITY',peo.nationality)) nationality ,peo.national_identifier national_identifier ,peo.applicant_number applicant_number ,peo.email_address email_address ,DECODE(peo.expense_check_send_to_address, NULL, NULL, hr_bis.bis_decode_lookup('HOME_OFFICE', peo.expense_check_send_to_address)) mail_destination ,DECODE(peo.second_passport_exists, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.second_passport_exists)) second_passport ,peo.office_number office_number ,peo.internal_location internal_location ,peo.mailstop mailstop ,peo.work_schedule work_schedule ,peo.fte_capacity fte_capacity ,DECODE(peo.resume_exists, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.resume_exists)) resume_exists ,peo.resume_last_updated resume_last_updated ,hr_bis.bis_decode_lookup('YES_NO',NVL(peo.current_applicant_flag,'N')) current_applicant_flag ,hr_bis.bis_decode_lookup('YES_NO',NVL(peo.current_employee_flag,'N')) current_employee_flag ,peo.date_employee_data_verified date_employee_data_verified ,peo.date_of_death date_of_death ,DECODE(peo.fast_path_employee, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.fast_path_employee)) fast_path_employee ,peo.hold_applicant_date_until hold_application_until ,peo.honors honors ,DECODE(peo.on_military_service, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.on_military_service)) on_military_service ,peo.projected_start_date projected_start_date ,DECODE(peo.student_status, NULL, NULL, hr_bis.bis_decode_lookup('STUDENT_STATUS',peo.student_status)) student_status ,decode(peo.current_employee_flag,'Y',pps.date_start,null) hire_date ,peo.original_date_of_hire original_date_of_hire ,peo.sex gender_code ,peo.registered_disabled_flag registered_disabled_flag_code ,peo.marital_status marital_status_code ,peo.nationality nationality_code ,peo.expense_check_send_to_address expense_check_send_addr_code ,peo.second_passport_exists second_passport_exists_code ,peo.resume_exists resume_exists_code ,peo.current_applicant_flag current_applicant_flag_code ,peo.current_employee_flag current_employee_flag_code ,peo.fast_path_employee fast_path_employee_code ,peo.on_military_service on_military_service_code ,peo.student_status student_status_code ,pptT.user_person_type person_type ,HRI_BPL_PERSON_TYPE.get_emp_user_person_type (trunc(sysdate) ,peo.person_id) employee_person_type ,HRI_BPL_PERSON_TYPE.get_apl_user_person_type (trunc(sysdate) ,peo.person_id) applicant_person_type ,HRI_BPL_PERSON_TYPE.get_concat_user_person_type (trunc(sysdate) ,peo.person_id) user_concat_person_type ,pp.phone_number work_telephone_number ,lang.nls_language correspondence_language ,ppt.system_person_type system_person_type_code ,'_DF:PER:PER_PEOPLE:peo' ,peo.business_group_id business_group_id ,peo.person_id person_id ,ppt.person_type_id person_type_id ,peo.last_update_date peo_last_update_date FROM fnd_languages lang ,hr_all_organization_units_tl bgrT ,per_person_types ppt ,per_person_types_tl pptT ,per_phones pp ,per_people_f peo ,per_periods_of_service pps ,FND_TERRITORIES_TL ftrT WHERE peo.person_type_id = ppt.person_type_id AND ppt.person_type_id = pptT.person_type_id AND pptT.language = userenv('LANG') AND pp.parent_table (+) = 'PER_ALL_PEOPLE_F' AND pp.phone_type (+) = 'W1' AND peo.person_id = pp.parent_id(+) AND TRUNC(sysdate) BETWEEN peo.effective_start_date AND peo.effective_end_date AND TRUNC(sysdate) BETWEEN pp.date_from (+) AND pp.date_to (+) AND peo.person_id = pps.person_id (+) AND TRUNC(sysdate) BETWEEN pps.date_start (+) AND NVL(pps.actual_termination_date (+), TRUNC(sysdate)) AND peo.correspondence_language = lang.language_code (+) AND peo.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND peo.country_of_birth = ftrT.TERRITORY_CODE(+) AND ftrT.language (+) = userenv('LANG') AND peo.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, peo.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.TITLE TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(DATE_OF_BIRTH
, NULL
, NULL
, FLOOR(MONTHS_BETWEEN( SYSDATE
, DATE_OF_BIRTH)/12)) AGE
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTRT.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_SEND_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_USER_PERSON_TYPE (TRUNC(SYSDATE)
, PEO.PERSON_ID) EMPLOYEE_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_APL_USER_PERSON_TYPE (TRUNC(SYSDATE)
, PEO.PERSON_ID) APPLICANT_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CONCAT_USER_PERSON_TYPE (TRUNC(SYSDATE)
, PEO.PERSON_ID) USER_CONCAT_PERSON_TYPE
, PP.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, LANG.NLS_LANGUAGE CORRESPONDENCE_LANGUAGE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
, PEO.LAST_UPDATE_DATE PEO_LAST_UPDATE_DATE
FROM FND_LANGUAGES LANG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, PER_PHONES PP
, PER_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, FND_TERRITORIES_TL FTRT
WHERE PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PP.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE (+) = 'W1'
AND PEO.PERSON_ID = PP.PARENT_ID(+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.DATE_FROM (+)
AND PP.DATE_TO (+)
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN PPS.DATE_START (+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE (+)
, TRUNC(SYSDATE))
AND PEO.CORRESPONDENCE_LANGUAGE = LANG.LANGUAGE_CODE (+)
AND PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.COUNTRY_OF_BIRTH = FTRT.TERRITORY_CODE(+)
AND FTRT.LANGUAGE (+) = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) WITH READ ONLY