How to get net entitlement of leaves of an employee on a specific date in oracle hrms??
Please refer to the function code below.
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;
/
No comments:
Post a Comment