1. Implementing Benefits
  2. Sample Cost-Distribution Formula

Sample Cost-Distribution Formula

You can use a fast formula to distribute costs under different assignments. The costing distribution will be done based on the formula return values.

Here’s a sample fast formula for your reference.

Formula Details

Formula Type Formula Contexts

Return Variables
Benefits Rate Distribution
  • HR_RELATIONSHIP_ID
  • HR_TERM_ID
  • ENTERPRISE_ID
  • ELEMENT_TYPE_ID
  • PAYROLL_RELATIONSHIP_ID
  • PAYROLL_TERM_ID
  • LEGAL_EMPLOYER_ID
  • DATE_EARNED
  • HR_ASSIGNMENT_ID
  • BUSINESS_GROUP_ID
  • PERSON_ID
  • JOB_ID
  • EFFECTIVE_DATE
  • PAYROLL_ASSIGNMENT_ID
  • PAYROLL_ID
  • LEGISLATIVE_DATA_GROUP_ID
  • LER_ID
  • OPT_ID
  • ORGANIZATION_ID
  • ELIG_PER_ELCTBL_CHC_ID
  • ACTY_BASE_RT_ID
  • BNFTS_BAL_ID
  • PGM_ID
  • PL_ID
  • PL_TYP_ID
  • BENEFIT_RELATION_ID
  • PER_IN_LER_ID

 Here are some points to consider:

  • The return variable names must follow this format:

    l_asg1, l_val1.......l_asg10, l_val10

    Example: l_asg1,l_val1,l_asg2,l_val2,l_asg3,l_val3

  • As in the sample formula, the return variables need to follow this convention:

    return l_asg1,l_val1,l_asg2,l_val2,l_asg3,l_val3

  • The maximum number of assignments for allocation is 10. So, there can be up to 10 pairs. The above example has 3 pairs.
  • The return variable l_valx indicates the distribution percentage of the costs across assignments. The percentage value must be between 0 and 100 and the sum of the values must be 100. Also, the value can’t have more than 2 decimal places.
  • The assignment_ids should belong to the person being processed and also to the same payroll relationship.
  • If the value for the assignment_id is 0, the formula ignors the assignment_id and value set.
  • A missing set is not allowed. For example, l_asg1,l_val1,l_asg3,l_val3. Here, the set l_asg2,l_val2 is missing.
Here’s a sample distribution formula to store percentage values for each person assignment in a user-defined table, and to use it for costing distribution.

User-Defined Table

Assignment PERSON_ID VALUE(Percentage)
Assignment_1 Person_id_1 50
Assignment_2 Person_id_1 30
Assignment_3 Person_id_1 20
Assignment_4 Person_id_2 50
Assignment_5 Person_id_2 50
Sample Formula:
DEFAULT_DATA_VALUE for PER_HIST_ASG_ASSIGNMENT_ID is 0
l_asg1=0
l_val1=0
l_asg2=0
l_val2=0
l_asg3=0
l_val3=0
i=1
j=1
l_effective_date = GET_CONTEXT(EFFECTIVE_DATE,to_date('1951/01/01 00:00:00'))
CHANGE_CONTEXTS(LEGISLATIVE_DATA_GROUP_ID = 202)

WHILE(PER_HIST_ASG_ASSIGNMENT_ID.EXISTS(i)) LOOP(
    if ((PER_HIST_ASG_ASSIGNMENT_TYPE[i]='E' or PER_HIST_ASG_ASSIGNMENT_TYPE[i]='N') and ((l_effective_date > PER_HIST_ASG_EFFECTIVE_START_DATE[i]) and (l_effective_date <PER_HIST_ASG_EFFECTIVE_END_DATE[i]))) then (
        l_person_id = to_number(GET_TABLE_VALUE ('DHQA_ASG_COSTING_DISTRIBUTION','PERSON_ID',to_char(PER_HIST_ASG_ASSIGNMENT_ID[i]),'-999'))
    if (l_person_id = GET_CONTEXT(PERSON_ID,-9999)) then (
        if(j=1) then(
            l_asg1= PER_HIST_ASG_ASSIGNMENT_ID[i]
            l_val1 = to_number(GET_TABLE_VALUE ('DHQA_ASG_COSTING_DISTRIBUTION','VALUE',to_char(PER_HIST_ASG_ASSIGNMENT_ID[i]))))
        if (j=2) then (
            l_asg2 = PER_HIST_ASG_ASSIGNMENT_ID[i]l_val2 = to_number(GET_TABLE_VALUE ('DHQA_ASG_COSTING_DISTRIBUTION','VALUE',to_char(PER_HIST_ASG_ASSIGNMENT_ID[i]))))
        if (j=3) then (
            l_asg3 =PER_HIST_ASG_ASSIGNMENT_ID[i]l_val3 = to_number(GET_TABLE_VALUE ('DHQA_ASG_COSTING_DISTRIBUTION','VALUE',to_char(PER_HIST_ASG_ASSIGNMENT_ID[i]))))
             j=j+1
        )
    )
i=i+1
)

return l_asg1,l_val1,l_asg2,l_val2,l_asg3,l_val3

Related Topics