PER Full Assignment Details

select asg.person_id,
        asg.soft_coding_keyflex_id,
       asg.assignment_number assignment_number,
       astl.user_status status,
       hrl4.meaning primary,
       orgtl.name organization,
       ppg.group_name group_name,
       jbt.name job,
       hr_general.decode_position_latest_name(asg.position_id) position,
       gdt.name grade,
       ppa.payroll_name payroll,
       loc.location_code location,
       peo.full_name supervisor,
       asg.change_reason reason,
       &c_ass_df_details con_ass_df,
       &c_ass_df_label   conc_ass_label_df,
       asg.probation_period length,
       hrl2.meaning units,
       asg.date_probation_end end_date,
       asg.normal_hours working_hours,
       hrl.meaning frequency,
       asg.time_normal_start start_time,
       asg.time_normal_finish end_time,
       hrl3.meaning manager,
       asg.internal_address_line internal_address,
       asg.effective_start_date effective_start_date,
       substr(asg.effective_end_date,1,11) effective_end_date,
       asg.assignment_id assignment_id,
        asg.period_of_service_id,
        asg.comment_id,
	PER_PERRPFP3_XMLP_PKG.c_scl_segsformula() C_scl_segs
from   per_all_assignments_f asg,
       per_assignment_status_types ast,
       per_assignment_status_types_tl astl,
       hr_all_organization_units org,
       hr_all_organization_units_tl orgtl,
       pay_all_payrolls_f ppa,
       per_jobs_tl jbt,
       per_grades_tl gdt,
       pay_people_groups ppg,
       hr_locations loc,
       per_all_people_f peo,
       hr_lookups hrl,
       hr_lookups hrl2,
       hr_lookups hrl3,
       hr_lookups hrl4
where  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.organization_id = org.organization_id
  and  org.organization_id = orgtl.organization_id
  and  orgtl.language = userenv('LANG')
  and  asg.payroll_id = ppa.payroll_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.supervisor_id = peo.person_id (+)
     and  :p_session_date between peo.effective_start_date (+)
                   and  peo.effective_end_date (+)
   and  :p_session_date between asg.effective_start_date
                 and asg.effective_end_date
   and  :p_session_date between ppa.effective_start_date (+)
                  and ppa.effective_end_date(+)
     and  hrl.lookup_type (+) = 'FREQUENCY'
  and  hrl.lookup_code (+) = asg.frequency
  and  hrl2.lookup_type (+) = 'QUALIFYING_UNITS'
  and  hrl2.lookup_code (+) = asg.probation_unit
  and  hrl3.lookup_type (+) = 'YES_NO'
  and  hrl3.lookup_code (+) = asg.manager_flag
  and  hrl4.lookup_type (+) = 'YES_NO'
  and  hrl4.lookup_code (+) = asg.primary_flag
    and  asg.business_group_id = :p_business_group_id
 and asg.period_of_service_id=:period_of_service_id1
order by hrl4.meaning desc, asg.assignment_id,asg.effective_start_date