GET_PLAN_BALANCE returns the plan balance as of the last accrual balance calculation date prior to the effective date.For example: For a calendar month plan,the last accrual balance calculated was 03/31/2018 and latest accrual balance was calculated as of 04/30/2018.When the function is called with context EFFECTIVE_DATE 04/15/2018,it will fetch the net accrual balance as of 03/31/2018.
Context Required:
The function requires below context parameters to be set.
• HR_ASSIGNMENT_ID
• PERSON_ID
• EFFECTIVE_DATE
• LEGISLATIVE_DATA_GROUP
Parameters:
It requires the plan name to be passed as a parameter.
Return Type: Number
Example: l_pl_balance_number = GET_PLAN_BALANCE(l_plan_name_char)
Technical insight:
GET_PLAN_BALANCE uses accrual details table ANC_PER_ACRL_ENTRY_DTLS which is a consolidated place holder for absence entries,periodic accruals,carry over and adjustments.
SELECT SUM(NVL(VALUE,0))
FROM ANC_PER_ACRL_ENTRY_DTLS
WHERE TYPE IN ('ACRL','FLDR','COVR','ADJOTH')
PROCD_DATE <= :P_LAST_ACRL_CALC_DT (last accrual calc dateprior to the effective date passed)
Below query gives the list of accrual calculation dates for each accrual period
SELECT ACCRUAL_PERIOD
FROM ANC_PER_ACCRUAL_ENTRIES
WHERE PLAN_ID=:P_PLAN_NAME
AND WORK_TERM_ASG_ID=:P_WORK_TERMS_ID
AND ACCRUAL_PERIOD <= :P_EFFECTIVE_DATE
