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 :

11 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
  3. Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts. Employee Attendance

    ReplyDelete
  4. That appears to be without doubt terrific. A good number of teeny facts are meant experiencing great deal of background abilities. I will be interested the thing a large amount. health news

    ReplyDelete
  5. One critical way in which facts era is affecting work is via reducing the importance of distance. in many industries, the geographic distribution of labor is changing drastically.TutuApp web

    ReplyDelete
  6. Thank you for your submit. I will definitely https://tipandroid.com/ comeback.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Today, it's basic for two-year-olds to utilize iPads, rudimentary schoolers snared to video games, and we as a whole endure (or live with) the test of prying your center schooler away from the PC sufficiently long to eat a good supper.Access Control System

    ReplyDelete
  9. You actually make it look so easy with your performance but I find this matter to be actually something which I think I would never comprehend. It seems too complicated and extremely broad for me. I'm looking forward for your next post, I’ll try to get the hang of it! vape attic olathe

    ReplyDelete
  10. Cool stuff you have got and you keep update all of us. Direct Response Copywriter

    ReplyDelete