In this article, we will look into a way to find out employees/plans for whom the accrual process is not run.
As part of the absence management, the accrual process should be scheduled to run every day for all the plans. If for some reason the process doesn’t run for some plans, the employees enrolled in those absence plans will not get the leaves accrued which will result in employees not being able to avail of the leave which in turn leads to employee dissatisfaction.
We can use the below query to identify such employees and then resolve the issue by running the process for them.
SQL Query to pull Employees enrolled in Absence Plans for which the accruals have not been run:
SELECT DISTINCT PS.PERSON_NUMBER
,ASGN.ASSIGNMENT_NUMBER
,name.full_name
,B.NAME Absence_Plan
,ast.user_status Assignmt_Status
,E.STATUS Enroll_Status
,substr(E.ENRT_ST_DT, 1, 10) enrt_start_date
,substr(E.LAST_ACCRUAL_RUN, 1, 10) LAST_ACCRUAL_RUN
,substr(E.LAST_UPDATE_DATE, 1, 10) LAST_Update_Date_RUN
FROM anc_per_plan_enrollment E
,PER_ALL_PEOPLE_F PS
,ANC_ABSENCE_PLANS_F_TL B
,PER_ALL_ASSIGNMENTS_M ASGN
,PER_PERSON_NAMES_F name
,ANC_ABSENCE_PLANS_VL P
,per_assignment_status_types_vl ast
WHERE b.LANGUAGE = 'US'
AND ASGN.WORK_TERMS_ASSIGNMENT_ID = E.WORK_TERM_ASG_ID
AND E.plan_id = B.ABSENCE_PLAN_ID
AND E.plan_id = P.ABSENCE_PLAN_ID
AND PS.PERSON_ID = E.PERSON_ID
AND ASGN.PERSON_ID = E.PERSON_ID
AND asgn.assignment_type = 'E'
AND ast.assignment_status_type_id(+) = asgn.assignment_status_type_id
AND asgn.effective_latest_change = 'Y'
AND name.person_id = ps.person_id
AND name.name_type = 'GLOBAL'
AND E.LAST_ACCRUAL_RUN < :param_effdt
AND ast.user_status IN ('Active - no payroll','Active - Payroll Eligible')
AND nvl(:param_effdt, sysdate) BETWEEN name.effective_start_date
AND name.Effective_end_date
AND nvl(:param_effdt, sysdate) BETWEEN E.ENRT_ST_DT
AND E.ENRT_END_DT
AND nvl(:param_effdt, sysdate) BETWEEN ASGN.EFFECTIVE_START_DATE
AND ASGN.EFFECTIVE_END_DATE
AND nvl(:param_effdt, sysdate) BETWEEN ps.effective_start_date
AND ps.Effective_end_date
ORDER BY B.NAME
,LAST_Update_Date_RUN
