PER Full Applicant Details

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