VIEW: APPS.HRIFV_US_PERDTL

Source

View Text - Preformatted

SELECT bgr.name business_group_name ,peo.full_name person_name ,peo.effective_start_date effective_start_date ,peo.effective_end_date effective_end_date ,peo.employee_number employee_number ,peo.national_identifier social_security_number ,DECODE(peo.per_information1, null, null, hr_general.decode_lookup('US_ETHNIC_GROUP',peo.per_information1)) ethnic_origin ,DECODE(peo.per_information2, null, null, hr_general.decode_lookup('PER_US_I9_STATE',peo.per_information2)) i9 ,fnd_date.canonical_to_date(peo.per_information3) i9_expiry_date ,DECODE(peo.per_information4, null, null, hr_general.decode_lookup('US_VISA_TYPE',peo.per_information4)) visa_type ,DECODE(peo.per_information5, null, null, hr_general.decode_lookup('US_VETERAN_STATUS',peo.per_information5)) vietnam_vets_status ,DECODE(peo.per_information6, null, null, hr_general.decode_lookup('YES_NO',peo.per_information6)) student ,DECODE(peo.per_information7, null, null, hr_general.decode_lookup('US_NEW_HIRE_STATUS', peo.per_information7)) new_hire_status ,peo.per_information8 exclusion_reason ,DECODE(peo.per_information9, null, null, hr_general.decode_lookup('YES_NO',peo.per_information9)) child_support_obligation ,DECODE(peo.registered_disabled_flag, null, null, hr_general.decode_lookup('YES_NO',peo.registered_disabled_flag )) registered_disabled ,'_DF:PER:PER_PEOPLE:peo' ,peo.business_group_id business_group_id ,peo.person_id person_id FROM per_business_groups bgr ,per_people_f peo WHERE peo.business_group_id = bgr.organization_id AND bgr.legislation_code = 'US' 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 BGR.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PEO.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, DECODE(PEO.PER_INFORMATION1
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('US_ETHNIC_GROUP'
, PEO.PER_INFORMATION1)) ETHNIC_ORIGIN
, DECODE(PEO.PER_INFORMATION2
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('PER_US_I9_STATE'
, PEO.PER_INFORMATION2)) I9
, FND_DATE.CANONICAL_TO_DATE(PEO.PER_INFORMATION3) I9_EXPIRY_DATE
, DECODE(PEO.PER_INFORMATION4
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('US_VISA_TYPE'
, PEO.PER_INFORMATION4)) VISA_TYPE
, DECODE(PEO.PER_INFORMATION5
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('US_VETERAN_STATUS'
, PEO.PER_INFORMATION5)) VIETNAM_VETS_STATUS
, DECODE(PEO.PER_INFORMATION6
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION6)) STUDENT
, DECODE(PEO.PER_INFORMATION7
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('US_NEW_HIRE_STATUS'
, PEO.PER_INFORMATION7)) NEW_HIRE_STATUS
, PEO.PER_INFORMATION8 EXCLUSION_REASON
, DECODE(PEO.PER_INFORMATION9
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION9)) CHILD_SUPPORT_OBLIGATION
, DECODE(PEO.REGISTERED_DISABLED_FLAG
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG )) REGISTERED_DISABLED
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
FROM PER_BUSINESS_GROUPS BGR
, PER_PEOPLE_F PEO
WHERE PEO.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND BGR.LEGISLATION_CODE = 'US'
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) WITH READ ONLY