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
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM