Employee Tobacco Usage (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This Employee Tobacco Usage query displays tobacco usage data for active employees as of today, helping users to monitor benefits eligibility and support compliance tracking.

#sqlquery

SELECT
  --SQL4FUSION (AN ORBIT ANALYTICS PROJECT)
  --This query retrieves current tobacco usage details for active employees.
  PAPF.PERSON_NUMBER,
  PPNFV.FULL_NAME,
  PPNFV.DISPLAY_NAME,
  BPLHCF.EFFECTIVE_START_DATE HABIT_START,
  BPLHCF.EFFECTIVE_END_DATE HABIT_END,
  HR_GENERAL.DECODE_LOOKUP('TOBACCO_USER', BPLHCF.TOBACCO_TYPE_USAGE) TOBACCO_USAGE,
  BPLHCF.TOBACCO_TYPE_USAGE USAGE_CODE,
  BPLHCF.CVRD_IN_ANTHR_PL COVERED_IN_ANOTHER_PLAN_FLAG 
FROM
  PER_ALL_PEOPLE_F PAPF,
  PER_PERSON_NAMES_F_V PPNFV,
  BEN_PER_LE_HABITS_COV_F BPLHCF
WHERE
  PAPF.PERSON_ID = PPNFV.PERSON_ID
  AND PAPF.PERSON_ID = BPLHCF.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE