Oracle Queries

Person Habits in Oracle HCM Cloud Benefits

-- Person Habits
SELECT ppln.full_name,
       ppln.display_name,
       ppl.person_number,
       pbh.effective_start_date                                         habit_start,
       pbh.effective_start_date                                         habit_end,
       hr_general.Decode_lookup(‘TOBACCO_USER’, pbh.tobacco_type_usage) tobacco_usage,
       pbh.tobacco_type_usage                                           tobacco_usage_cd
FROM   per_all_people_f ppl,
       per_person_names_f_v ppln,
       ben_per_le_habits_cov_f pbh
WHERE  1 = 1
       AND ppl.person_id = ppln.person_id(+)
       AND Trunc(SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
       AND Trunc(SYSDATE) BETWEEN ppln.effective_start_date AND ppln.effective_end_date
       AND ppl.person_id = pbh.person_id
       AND 1 = 1; 

Leave a Reply

Your email address will not be published. Required fields are marked *