How to get Net Entitlement of Leaves in Oracle HRMS Payroll

How to get net entitlement of leaves of an employee on a specific date in oracle hrms?

Please refer to the function code below.

CREATE OR REPLACE FUNCTION CUST_GET_NET_ENTITLEMENT (

   P_ASSG_ID            IN   NUMBER,

   P_PAYROLL_ID         IN   NUMBER,

   P_CALCULATION_DATE   IN   DATE

)

   RETURN NUMBER

IS

   L_START_DATE         DATE;

   L_END_DATE           DATE;

   L_ACC_END_DATE       DATE;

   L_VALUE              NUMBER         := 0;

   L_NET_VALUE          NUMBER (15, 2);

   P_PLAN_ID            NUMBER;

--   L_CALCULATION_DATE   DATE;

BEGIN

   SELECT PAP.ACCRUAL_PLAN_ID

     INTO P_PLAN_ID

     FROM PAY_ACCRUAL_PLANS PAP

    WHERE UPPER (PAP.ACCRUAL_PLAN_NAME) LIKE

             (SELECT UPPER (PETF.ELEMENT_NAME)

                FROM PAY_ELEMENT_ENTRIES_F PEEF, PAY_ELEMENT_TYPES_F PETF

               WHERE PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID

                 AND UPPER (PETF.ELEMENT_NAME) LIKE '%PLAN%'

                 AND PETF.PROCESSING_TYPE = 'R'

                 AND PEEF.ASSIGNMENT_ID = P_ASSG_ID

                 AND P_CALCULATION_DATE BETWEEN PEEF.EFFECTIVE_START_DATE

                                            AND PEEF.EFFECTIVE_END_DATE);

/* QUERY ABOVE AUTOMATICALLY GETS THE PLAN ID OF THE ACCRUAL PLAN ID ATTACHED AS ON CALCULATION DATE*/

   PER_ACCRUAL_CALC_FUNCTIONS.GET_NET_ACCRUAL

                           (P_ASSIGNMENT_ID               => P_ASSG_ID,

                            P_PLAN_ID                     => P_PLAN_ID,

                            P_PAYROLL_ID                  => P_PAYROLL_ID,

                            P_BUSINESS_GROUP_ID           => 81,   -- Kindly change your business group id accordingly

                            P_ASSIGNMENT_ACTION_ID        => -1,

                            P_CALCULATION_DATE            => TO_DATE

                                                               (P_CALCULATION_DATE

                                                                )

-- DATE YOU WANT TO CHECK THE NET ENTITLEMENT EX. TO_DATE('01-MAR-2009', 'DD-MON-YYYY')

   ,

                            P_ACCRUAL_START_DATE          => NULL,

                            P_ACCRUAL_LATEST_BALANCE      => NULL,

                            P_CALLING_POINT               => 'FRM',

                            P_START_DATE                  => L_START_DATE,

                            P_END_DATE                    => L_END_DATE,

                            P_ACCRUAL_END_DATE            => L_ACC_END_DATE,

                            P_ACCRUAL                     => L_VALUE,

                            P_NET_ENTITLEMENT             => L_NET_VALUE

                           );

   RETURN NVL (L_NET_VALUE, 0);

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN 0;

END;

/