Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

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;
Share this article :

0 comments:

Post a Comment