GET_PLAN_BALANCE formula function in Oracle HCM Cloud

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

HCM Extract Import Errors

Here are some common possible errors while importing the extracts from one environment to another environment.

Error:You must enter a value for Rule.

Your error log may show something like this.

JBO-27023: Failed to validate all rows in a transaction.JBO-${adfBundle['oracle.apps.fnd.applcore.messages.FndMessagesResourceBundle']['PER:::PER_EXTS_CMN_REQ_ATTR']}:
<MESSAGE>
<NUMBER>PER-1531176</NUMBER>
<TEXT>You must enter a value for Rule.</TEXT>
<CAUSE/>
<ACTION/>
<DETAILS>Value is required for this attribute.</DETAILS>
<INCIDENT/>
</MESSAGE>JBO-PER:::PER_EXTS_DELMT_RULE_FRML: <MESSAGE>
<NUMBER>PER-1530694</NUMBER>
<TEXT>The formula ID provided for the rule is invalid.</TEXT>
<CAUSE/>
<ACTION/>
<DETAILS/>
<INCIDENT/>
</MESSAGE>

Reason:
One of the record attributes is defined as RULE but the import is unable to find the fast formula.

Solution:
- Check if you have created the fast formula in your target environment
- If the fast formula is already created check if the effective start date of fast formula is on or before the extract definition/attribute effective date
Example: When you are creating an attribute the you might have given the effective date as 01/01/1950 but you created the fast formula as of 01/01/1951
-Check if you have created any legislative data group (LDG) specific fast formula.The LDG of extract and fast formula should match.

You may want to remove/edit the attribute with formulas in XML,export the extract first and then debug the issue.