PER Full Applicant Details

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Full Applicant Details
Application: Human Resources
Source: Full Applicant Details Report (XML)
Short Name: PERRPFP2_XML
DB package: PER_PERRPFP2_XMLP_PKG 
select  vac.name vacancy,
       rac.name recruitment_activity,
       asg.assignment_id assignment_number,
       org.name organization,
       jbt.name job,
       &c_app_ass_df_details conc_app_ass_df,
       &c_app_ass_df_label  conc_app_ass_label_df,
       gdt.name grade,
       astl.user_status status,
       ppg.group_name group_name,
       hr_general.decode_position_latest_name(asg.position_id) position,
       loc.location_code location,
       asg.change_reason reason,
       peo.full_name recruiter,
       peo2.full_name referred_by,
       asg.probation_period length,
       hrl4.meaning units,
       asg.date_probation_end end_date,
       asg.normal_hours working_hours,
       hrl3.meaning frequency,
       asg.time_normal_start start_time,
       asg.time_normal_finish end_time,
       peo3.full_name supervisor,
       hrl5.meaning manager,
       asg.effective_start_date effective_start_date,
     asg.effective_end_date effective_end_date,
     to_char(asg.effective_start_date,'YYYY/MM/DD HH24:MI:SS') ESD,
     to_char(asg.effective_end_date,'YYYY/MM/DD HH24:MI:SS') EED,
       asg.assignment_id assignment_id,
        asg.application_id application_id,
        asg.comment_id
from per_all_assignments_f  asg,
       per_assignment_status_types ast,
       per_assignment_status_types_tl astl,
       per_recruitment_activities rac,
       per_vacancies vac,
       per_all_organization_units org,
       per_jobs_tl jbt,
       per_grades_tl gdt,
       pay_people_groups ppg,
       hr_locations loc,
       per_all_people_f peo,
       per_all_people_f peo2,
       per_all_people_f peo3,
       hr_lookups hrl3,
       hr_lookups hrl4,
       hr_lookups hrl5
where asg.person_id = :p_person_id
  and  asg.business_group_id = :p_business_group_id
  and  asg.period_of_service_id is null
  and  asg.assignment_status_type_id = ast.assignment_status_type_id
  and  ast.assignment_status_type_id = astl.assignment_status_type_id
  and  astl.language = userenv('LANG')
  and  asg.vacancy_id = vac.vacancy_id (+)
  and  asg.recruitment_activity_id = rac.recruitment_activity_id (+)
  and  asg.organization_id = org.organization_id (+)
  and  asg.job_id = jbt.job_id (+)
  and  jbt.language(+) = userenv('LANG')
  and  asg.grade_id = gdt.grade_id (+)
  and  gdt.language(+) = userenv('LANG')
  and  asg.people_group_id = ppg.people_group_id (+)
  and  asg.location_id = loc.location_id (+)
  and  asg.recruiter_id = peo.person_id (+)
  and  asg.person_referred_by_id = peo2.person_id (+)
  and  asg.supervisor_id = peo3.person_id (+)
  and  hrl3.lookup_type (+) = 'FREQUENCY'
  and  hrl3.lookup_code (+) = asg.frequency
  and  hrl4.lookup_type (+) = 'QUALIFYING_UNITS'
  and  hrl4.lookup_code (+) = asg.probation_unit
  and  hrl5.lookup_type (+) = 'YES_NO'
  and  hrl5.lookup_code (+) = asg.manager_flag
    and :p_session_date  between peo.effective_start_date (+)
                                    and peo.effective_end_date (+)
     and :p_session_date between peo2.effective_start_date (+)
                                    and peo2.effective_end_date (+)
    and :p_session_date between peo3.effective_start_date (+)
                                     and peo3.effective_end_date (+)
 and asg.application_id=:application_id1
order by asg.effective_start_date
Parameter Name SQL text Validation
PER_DATES_STANDARD 
 
 Date
Applicant 
 
 LOV Oracle
Download
Related Blitz Reports
PER Requisition Summary PER Assignment Status PER Full Assignment Details PER Worker Summary PER Employee Assignments PER ADA (1) PER ADA PER New Hire State GL Account Analysis
Blitz Report™

Blitz Report™ provides multiple benefits: