VIEW: APPS.HRFV_APPLICANT_DETAILS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name applicant_name ,hr_bis.bis_decode_lookup( 'IRC_INT_EXT', decode(ppt.system_person_type, 'EMP_APL', 'I', 'E') ) applicant_type ,peo.applicant_number applicant_number ,NVL(astaT.user_status,astT.user_status) application_status ,vac.name vacancy_name ,app.date_received application_start_date ,app.date_end application_end_date ,GREATEST(peo.effective_start_date, hir.effective_start_date) record_start_date ,LEAST(peo.effective_end_date, hir.effective_end_date) record_end_date ,peo.effective_start_date person_start_date ,peo.effective_end_date person_end_date ,hir.effective_start_date assignment_start_date ,hir.effective_end_date assignment_end_date ,hr_bis.bis_decode_lookup('YES_NO', peo.current_employee_flag) current_employee_flag ,hr_bis.bis_decode_lookup('YES_NO', app.successful_flag) successful_applicant_flag ,app.comments application_comments ,ppt.user_person_type system_person_type ,HRI_BPL_PERSON_TYPE.get_emp_user_person_type (GREATEST(peo.effective_start_date,hir.effective_start_date) ,peo.person_id) employee_person_type ,HRI_BPL_PERSON_TYPE.get_apl_user_person_type (GREATEST(peo.effective_start_date,hir.effective_start_date) ,peo.person_id) applicant_person_type ,HRI_BPL_PERSON_TYPE.get_concat_user_person_type (GREATEST(peo.effective_start_date,hir.effective_start_date) ,peo.person_id) user_concat_person_type ,app.current_employer current_employer ,app.projected_hire_date projected_hire_date ,decode(peo.current_employee_flag,'Y',pps.date_start,null) hire_date ,peo.original_date_of_hire original_date_of_hire ,hir.assignment_number assignment_number ,hr_bis.bis_decode_lookup('EMP_APL',hir.assignment_type) assignment_type ,hir.assignment_type assignment_type_code ,peo.employee_number employee_number ,rac.name recruitment_activity_name ,HR_BIS.BIS_DECODE_LOOKUP('YES_NO', DECODE(hir.person_referred_by_id, NULL,'N', 'Y')) employee_refferal ,ref.full_name referring_employee_name ,ref.employee_number referring_employee_number ,DECODE(hir.source_type, NULL, NULL, hr_bis.bis_decode_lookup('REC_TYPE',hir.source_type)) source_type ,hir.job_post_source_name job_post_source_name ,NVL(astaT.user_status,astT.user_status) user_assignment_status ,hr_bis.bis_decode_lookup('PER_ASS_SYS_STATUS',ast.per_system_status) per_system_assignment_status ,hr_bis.bis_decode_lookup('PAY_ASS_SYS_STATUS', NVL(asta.pay_system_status,ast.pay_system_status)) pay_system_assignment_status ,souT.name source_organization_name ,sup.full_name supervisor_name ,sup.employee_number supervisor_number ,rec.full_name recruiter_name ,orgT.name organization_name ,pft.name position_name ,jbt.name job_name ,locT.location_code location_name ,gdt.name grade_name ,pgr.group_name people_group ,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 , peo.current_employee_flag current_employee_flag_code , app.successful_flag successful_applicant_flag_code , hir.source_type source_type_code , ppt.system_person_type system_person_type_code ,ast.per_system_status per_system_ass_status_code ,NVL(asta.pay_system_status,ast.pay_system_status) pay_system_ass_status_code , decode(ppt.system_person_type, 'EMP_APL', 'I', 'E') applicant_type_code ,'_DF:PER:PER_ASSIGNMENTS:hir' ,'_DF:PER:PER_APPLICATIONS:app' ,'_DF:PER:PER_PEOPLE:peo' ,'_DF:PER:Person Developer DF:peo' ,hir.application_id application_id ,hir.person_id person_id ,hir.assignment_id assignment_id ,hir.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,locT.location_id location_id ,hir.organization_id organization_id ,pgr.people_group_id people_group_id ,pft.position_id position_id ,rec.person_id recruiter_id ,rac.recruitment_activity_id recruitment_activity_id ,ref.person_id referring_employee_id ,sup.person_id supervisor_id ,vac.vacancy_id vacancy_id ,peo.party_id party_id FROM per_recruitment_activities rac ,per_periods_of_service pps ,per_person_types ppt ,hr_all_organization_units_tl souT ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,hr_all_positions_f_tl pft ,per_jobs_tl jbt ,hr_locations_all_tl locT ,per_grades_tl gdt ,per_all_vacancies vac ,pay_people_groups pgr ,per_applications app ,per_people_x rec ,per_people_x sup ,per_people_x ref ,per_all_people_f peo ,per_assignments_f hir ,per_assignment_status_types ast ,per_assignment_status_types_tl astT ,per_ass_status_type_amends asta ,per_ass_status_type_amends_tl astaT WHERE hir.organization_id = orgT.organization_id AND orgT.language (+) = userenv('LANG') AND hir.position_id = pft.position_id(+) AND pft.language(+) = userenv('LANG') AND hir.job_id = jbt.job_id(+) AND jbt.language(+) = userenv('LANG') AND hir.location_id = locT.location_id(+) AND locT.language (+) = userenv('LANG') AND hir.grade_id = gdt.grade_id(+) AND gdt.language(+) = userenv('LANG') AND hir.people_group_id = pgr.people_group_id(+) AND hir.vacancy_id = vac.vacancy_id (+) AND hir.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND hir.application_id = app.application_id (+) AND hir.person_id = peo.person_id AND peo.applicant_number IS NOT NULL AND hir.source_organization_id = souT.organization_id (+) AND souT.language (+) = userenv('LANG') AND hir.period_of_service_id = pps.period_of_service_id(+) AND hir.person_referred_by_id = ref.person_id (+) AND hir.supervisor_id = sup.person_id (+) AND hir.recruiter_id = rec.person_id (+) AND hir.recruitment_activity_id = rac.recruitment_activity_id(+) AND (hir.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date OR peo.effective_start_date BETWEEN hir.effective_start_date AND hir.effective_end_date ) AND peo.person_type_id = ppt.person_type_id AND ppt.system_person_type in ('EMP_APL','APL') AND ast.assignment_status_type_id = hir.assignment_status_type_id AND ast.assignment_status_type_id = astT.assignment_status_type_id AND astT.language = userenv('LANG') AND hir.assignment_status_type_id = asta.assignment_status_type_id (+) AND hir.business_group_id = asta.business_group_id (+) AND asta.ass_status_type_amend_id = astaT.ass_status_type_amend_id (+) AND NVL(astaT.language,userenv('LANG')) = userenv('LANG') AND hir.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, hir.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME APPLICANT_NAME
, HR_BIS.BIS_DECODE_LOOKUP( 'IRC_INT_EXT'
, DECODE(PPT.SYSTEM_PERSON_TYPE
, 'EMP_APL'
, 'I'
, 'E') ) APPLICANT_TYPE
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, NVL(ASTAT.USER_STATUS
, ASTT.USER_STATUS) APPLICATION_STATUS
, VAC.NAME VACANCY_NAME
, APP.DATE_RECEIVED APPLICATION_START_DATE
, APP.DATE_END APPLICATION_END_DATE
, GREATEST(PEO.EFFECTIVE_START_DATE
, HIR.EFFECTIVE_START_DATE) RECORD_START_DATE
, LEAST(PEO.EFFECTIVE_END_DATE
, HIR.EFFECTIVE_END_DATE) RECORD_END_DATE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, HIR.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, HIR.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_EMPLOYEE_FLAG) CURRENT_EMPLOYEE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, APP.SUCCESSFUL_FLAG) SUCCESSFUL_APPLICANT_FLAG
, APP.COMMENTS APPLICATION_COMMENTS
, PPT.USER_PERSON_TYPE SYSTEM_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, HIR.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) EMPLOYEE_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_APL_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, HIR.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) APPLICANT_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CONCAT_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, HIR.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) USER_CONCAT_PERSON_TYPE
, APP.CURRENT_EMPLOYER CURRENT_EMPLOYER
, APP.PROJECTED_HIRE_DATE PROJECTED_HIRE_DATE
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, HIR.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, HR_BIS.BIS_DECODE_LOOKUP('EMP_APL'
, HIR.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, HIR.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, RAC.NAME RECRUITMENT_ACTIVITY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(HIR.PERSON_REFERRED_BY_ID
, NULL
, 'N'
, 'Y')) EMPLOYEE_REFFERAL
, REF.FULL_NAME REFERRING_EMPLOYEE_NAME
, REF.EMPLOYEE_NUMBER REFERRING_EMPLOYEE_NUMBER
, DECODE(HIR.SOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('REC_TYPE'
, HIR.SOURCE_TYPE)) SOURCE_TYPE
, HIR.JOB_POST_SOURCE_NAME JOB_POST_SOURCE_NAME
, NVL(ASTAT.USER_STATUS
, ASTT.USER_STATUS) USER_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PER_ASS_SYS_STATUS'
, AST.PER_SYSTEM_STATUS) PER_SYSTEM_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PAY_ASS_SYS_STATUS'
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)) PAY_SYSTEM_ASSIGNMENT_STATUS
, SOUT.NAME SOURCE_ORGANIZATION_NAME
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, REC.FULL_NAME RECRUITER_NAME
, ORGT.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GDT.NAME GRADE_NAME
, PGR.GROUP_NAME PEOPLE_GROUP
, 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
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, APP.SUCCESSFUL_FLAG SUCCESSFUL_APPLICANT_FLAG_CODE
, HIR.SOURCE_TYPE SOURCE_TYPE_CODE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_ASS_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_ASS_STATUS_CODE
, DECODE(PPT.SYSTEM_PERSON_TYPE
, 'EMP_APL'
, 'I'
, 'E') APPLICANT_TYPE_CODE
, '_DF:PER:PER_ASSIGNMENTS:HIR'
, '_DF:PER:PER_APPLICATIONS:APP'
, '_DF:PER:PER_PEOPLE:PEO'
, '_DF:PER:PERSON DEVELOPER DF:PEO'
, HIR.APPLICATION_ID APPLICATION_ID
, HIR.PERSON_ID PERSON_ID
, HIR.ASSIGNMENT_ID ASSIGNMENT_ID
, HIR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, HIR.ORGANIZATION_ID ORGANIZATION_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, PFT.POSITION_ID POSITION_ID
, REC.PERSON_ID RECRUITER_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, REF.PERSON_ID REFERRING_EMPLOYEE_ID
, SUP.PERSON_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
, PEO.PARTY_ID PARTY_ID
FROM PER_RECRUITMENT_ACTIVITIES RAC
, PER_PERIODS_OF_SERVICE PPS
, PER_PERSON_TYPES PPT
, HR_ALL_ORGANIZATION_UNITS_TL SOUT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_POSITIONS_F_TL PFT
, PER_JOBS_TL JBT
, HR_LOCATIONS_ALL_TL LOCT
, PER_GRADES_TL GDT
, PER_ALL_VACANCIES VAC
, PAY_PEOPLE_GROUPS PGR
, PER_APPLICATIONS APP
, PER_PEOPLE_X REC
, PER_PEOPLE_X SUP
, PER_PEOPLE_X REF
, PER_ALL_PEOPLE_F PEO
, PER_ASSIGNMENTS_F HIR
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTT
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTAT
WHERE HIR.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND HIR.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND HIR.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND HIR.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND HIR.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND HIR.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND HIR.VACANCY_ID = VAC.VACANCY_ID (+)
AND HIR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND HIR.APPLICATION_ID = APP.APPLICATION_ID (+)
AND HIR.PERSON_ID = PEO.PERSON_ID
AND PEO.APPLICANT_NUMBER IS NOT NULL
AND HIR.SOURCE_ORGANIZATION_ID = SOUT.ORGANIZATION_ID (+)
AND SOUT.LANGUAGE (+) = USERENV('LANG')
AND HIR.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID(+)
AND HIR.PERSON_REFERRED_BY_ID = REF.PERSON_ID (+)
AND HIR.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND HIR.RECRUITER_ID = REC.PERSON_ID (+)
AND HIR.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID(+)
AND (HIR.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE OR PEO.EFFECTIVE_START_DATE BETWEEN HIR.EFFECTIVE_START_DATE
AND HIR.EFFECTIVE_END_DATE )
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP_APL'
, 'APL')
AND AST.ASSIGNMENT_STATUS_TYPE_ID = HIR.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTT.ASSIGNMENT_STATUS_TYPE_ID
AND ASTT.LANGUAGE = USERENV('LANG')
AND HIR.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND HIR.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID (+)
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTAT.ASS_STATUS_TYPE_AMEND_ID (+)
AND NVL(ASTAT.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND HIR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, HIR.BUSINESS_GROUP_ID) WITH READ ONLY