Follow us: Connect on YouTube 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 :

2 comments:

  1. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete
  2. When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your. small-business

    ReplyDelete