Query to get employee Special Information

select papf.employee_number,

  papf.full_name,

  ppt.user_person_type employee_type,

  -- version 2.09 changes --

  to_char(ppa.date_from,'dd-mon-yyyy') start_date,

  to_char(ppa.date_to,'dd-mon-yyyy')end_date,

  pac.segment1 "plan name",

  pac.segment2 "member id",

  pac.segment3 "plan id",

  pac.segment4 "exclusion code"

from per_all_people_f papf,

 per_all_assignments_f paaf,

  hr_soft_coding_keyflex hsck,

 per_person_analyses ppa,

  fnd_id_flex_structures fifs,

  per_special_info_types psit,

  per_analysis_criteria pac,

  per_person_types_tl ppt

where paaf.person_id            = papf.person_id

and ppt.person_type_id          = papf.person_type_id

and pac.id_flex_num             = fifs.id_flex_num

--and fifs.id_flex_structure_code = 'pera'

and ppt.person_type_id(+)       = papf.person_type_id

and psit.id_flex_num            = pac.id_flex_num

and ppa.person_id(+)            = papf.person_id

and pac.analysis_criteria_id(+) = ppa.analysis_criteria_id

and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)

and paaf.assignment_type        = 'e'

and ppt.user_person_type       <> 'ex-employee'

  --     and papf.business_group_id = :p_business_group_id

and sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between paaf.effective_start_date and paaf.effective_end_date

--and papf.employee_number = '30987'

group by papf.full_name,

  papf.employee_number,

  ppt.user_person_type,

  hsck.concatenated_segments,

  pac.segment2,

  pac.segment3,

  pac.segment4,

  pac.segment1,

  ppa.date_from,

  ppa.date_to

order by ppa.date_from desc;