VIEW: APPS.HRFV_TRANSFERS_IN

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,ppf.full_name person_name ,hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE',hrl.lookup_code) budget_measurement_type ,hrl.lookup_code budget_measurement_code ,paf.effective_start_date transfer_date ,paf.assignment_number assignment_number ,ppf.employee_number employee_number ,pptT.user_person_type person_type ,HRI_BPL_PERSON_TYPE.get_emp_user_person_type (paf.effective_start_date ,ppf.person_id) employee_person_type ,HRI_BPL_PERSON_TYPE.get_concat_user_person_type (paf.effective_start_date ,ppf.person_id) user_concat_person_type ,hr_bis.bis_decode_lookup('EMP_CAT',paf.employment_category) employment_category ,org2T.name old_organization_name ,houT.name organization_name ,gdt.name grade_name ,jbt.name job_name ,pft.name position_name ,hlT.location_code location_name ,ppt.system_person_type system_person_type_code ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,paf.organization_id organization_id ,paf.person_id person_id ,pft.position_id position_id ,ppt.person_type_id person_type_id FROM hr_all_organization_units_tl houT ,hr_all_organization_units_tl bgrT ,per_grades_tl gdt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,per_assignment_status_types past ,hr_locations_all_tl hlT ,per_person_types ppt ,per_person_types_tl pptT ,per_people_f ppf ,hr_all_organization_units_tl org2T ,per_all_assignments_f paf2 ,per_all_assignments_f paf ,hr_lookups hrl WHERE paf.person_id = ppf.person_id AND paf.effective_start_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND ppf.person_type_id = ppt.person_type_id AND ppt.system_person_type in ('EMP','EMP_APL') AND ppt.person_type_id = pptT.person_type_id AND pptT.language = userenv('LANG') AND paf.assignment_status_type_id = past.assignment_status_type_id AND past.per_system_status <> 'TERM_ASSIGN' AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND paf.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND paf.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND paf.location_id = hlT.location_id(+) AND hlT.language (+) = userenv('LANG') AND paf.assignment_type = 'E' AND paf.primary_flag = 'Y' AND paf.assignment_id = paf2.assignment_id AND paf.effective_start_date-1 = paf2.effective_end_date AND paf.organization_id <> paf2.organization_id AND paf2.organization_id = org2T.organization_id AND org2T.language = userenv('LANG') AND paf2.assignment_type = 'E' AND paf2.primary_flag = 'Y' AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hrl.lookup_type = 'BUDGET_MEASUREMENT_TYPE' AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, paf.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PPF.FULL_NAME PERSON_NAME
, HR_BIS.BIS_DECODE_LOOKUP('BUDGET_MEASUREMENT_TYPE'
, HRL.LOOKUP_CODE) BUDGET_MEASUREMENT_TYPE
, HRL.LOOKUP_CODE BUDGET_MEASUREMENT_CODE
, PAF.EFFECTIVE_START_DATE TRANSFER_DATE
, PAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_USER_PERSON_TYPE (PAF.EFFECTIVE_START_DATE
, PPF.PERSON_ID) EMPLOYEE_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CONCAT_USER_PERSON_TYPE (PAF.EFFECTIVE_START_DATE
, PPF.PERSON_ID) USER_CONCAT_PERSON_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, PAF.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, ORG2T.NAME OLD_ORGANIZATION_NAME
, HOUT.NAME ORGANIZATION_NAME
, GDT.NAME GRADE_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, HLT.LOCATION_CODE LOCATION_NAME
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
, PAF.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
, PPT.PERSON_TYPE_ID PERSON_TYPE_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, PER_ASSIGNMENT_STATUS_TYPES PAST
, HR_LOCATIONS_ALL_TL HLT
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, PER_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL ORG2T
, PER_ALL_ASSIGNMENTS_F PAF2
, PER_ALL_ASSIGNMENTS_F PAF
, HR_LOOKUPS HRL
WHERE PAF.PERSON_ID = PPF.PERSON_ID
AND PAF.EFFECTIVE_START_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL')
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = USERENV('LANG')
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND PAF.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND PAF.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND PAF.LOCATION_ID = HLT.LOCATION_ID(+)
AND HLT.LANGUAGE (+) = USERENV('LANG')
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_ID = PAF2.ASSIGNMENT_ID
AND PAF.EFFECTIVE_START_DATE-1 = PAF2.EFFECTIVE_END_DATE
AND PAF.ORGANIZATION_ID <> PAF2.ORGANIZATION_ID
AND PAF2.ORGANIZATION_ID = ORG2T.ORGANIZATION_ID
AND ORG2T.LANGUAGE = USERENV('LANG')
AND PAF2.ASSIGNMENT_TYPE = 'E'
AND PAF2.PRIMARY_FLAG = 'Y'
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HRL.LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE'
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) WITH READ ONLY