-- Oracle HCM Cloud Queries
-- 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;