PER Worker Summary

select asg.person_id person_id6,
       asg.assignment_number assignment_number,
       ast.user_status status,
       hrl4.meaning primary2,
       org.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,
       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,
       hrl3.meaning manager,
       asg.internal_address_line internal_address,
       asg.effective_start_date effective_start_date2,
       asg.effective_end_date effective_end_date2,
       asg.assignment_id assignment_id,
       asg.vendor_id vendor_id,
       asg.vendor_employee_number vendor_employee_number,
       asg.vendor_assignment_number vendor_assignment_number,
--	PER_PERRPPSM_XMLP_PKG.c_pay_meth2formula(:ASSIGNMENT_ID) C_PAY_METH2
	PER_PERRPPSM_XMLP_PKG.c_pay_meth2formula(asg.assignment_id) C_PAY_METH2
from   per_all_assignments_f asg,
       per_assignment_status_types ast,
       hr_all_organization_units org,
       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  asg.organization_id = org.organization_id
  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  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.effective_start_date between ppa.effective_start_date (+)
                                    and ppa.effective_end_date (+)
  and  :p_session_date between asg.effective_start_date
			   and asg.effective_end_date
  and  asg.business_group_id = :p_business_group_id
 and asg.person_id=:person_id5
order by hrl4.meaning desc, asg.assignment_id