Benefit cost

General data related to salary, benefit, and cost, such as amounts and ranges.

The process uses these tables and stored procedures:

  • The staging table is DAT_BENEFITCOST_STAGE.
  • The stored procedure to validate staged data is depm_integration_wb_benefitcost_prepare_staging.
  • The stored procedure to process staged data is depm_integration_wb_benefitcost.
  • The data is imported to:
    • The DAT_BENEFITCOST_TRANSFORMED table.
    • The WBCCONFIG_BENEFITCOST cube.

The output tables are:

  • ATT_WBCCONFIG_BENEFITCOST contains the configuration cube data.
  • DIM_WBD_BENEFITCOST_ELEMENTS contains the elements for the WBD_BENEFITCOST dimension.
  • DIM_WBD_BENEFITCOST_PARENTS contains the parent/child relationships for the WBD_BENEFITCOST dimension.
  • DIM_WBDACTION_ELEMENTS contains the elements for the WBDACTION dimension.
  • DIM_WBDACTION_PARENTS contains the parent/child relationships for the WBDACTION dimension.
  • DIM_BPDDETAIL_ELEMENTS contains the elements for the BPDDETAIL dimension.
  • DIM_BPDDETAIL_PARENTS contains the parent/child relationships for the BPDDETAIL dimension.
  • The WBD_BENEFITCOST, WBDACTION and BPDDETAIL dimensions must be in sync.
  • The benefit cost elements are also replicated in the WBD_BASEDON_BENEFITCOST dimension.
    • DIM_WBD_BASEDON_BENEFITCOST_ELEMENTS contains the elements for the WBD_BASEDON_BENEFITCOST dimension.
    • DIM_WBD_BASEDON_BENEFITCOST_PARENTS contains the parent/child relationships for the WBD_BASEDON_BENEFITCOST dimension.

The entity ID for global benefits and account ID must exist prior to importing data.

The table describes DAT_BENEFITCOST_STAGE: The benefit cost elements are also replicated in the WBD_BASEDON_BENEFITCOST dimension.

Field Required Description
BENEFIT_COST_SALARY Y Type of salary, benefit or cost definition.

The valid values are SALARY, BENEFIT or COST.

For the BaseRateAmountChange and BaseRatePercentChange calculation types, the only allowed value is SALARY.

For all other calculation types, the valid values are BENEFIT and COST.

BenefitCostCode Y Unique ID of the salary/benefit/cost.
BenefitCostName Y Description of the salary/benefit/cost.
SUBPLAN_WFB_ACCOUNT Y A valid base account ID from BPDACCOUNT.

The related field is ID in the DIM_BPDACCOUNT_ELEMENTS table.
SUBPLAN_WFB_EFFECTIVEDATE Y/N Effective start date for the benefit/cost.

The format is YYYY/MM/DD.

Required for global benefits and costs that have date type set to calendar date.

Does not apply to any other benefit or cost.

SUBPLAN_WFB_ENDDATE N Effective end date for the benefit/cost.

The format is YYYY/MM/DD.

Only applies to global benefits and costs that have a calculation type of PercentOfPeriodic or YTDRanges.

SUBPLAN_WFB_YTDTYPE N Year to date calculation type.

The valid values are:

  • Fiscal
  • Calendar

It applies to these calculation types:

  • QuantityXRate
  • PercenotOfPeriodic
  • YTDRanges

The default is Fiscal.

SUBPLAN_WFB_CALCBYEMPLOYEE N Calculation by employee across positions.

The valid values are:

  • 1 to enable.
  • 0 to disable.

The CalcByEmployee property is used by the PercentOfPeriodic and YTDRanges calculation types for global benefits and costs. If empty, the default is disabled.

SUBPLAN_WFB_CALCTYPE Y Calculation type of salary, benefit or cost.

The valid values are:

  • BaseRateAmountChange
  • BaseRatePercentChange
  • FlatAmount
  • PercentOfAnnualized
  • PercentOfPeriodic
  • PremiumAmountBaseRate
  • PremiumAmountPremiumRate
  • PremiumPercentBaseRate
  • PremiumPercentPremiumRate
  • QuantityXRate
  • YTDRanges

These calculation type values are associated with the CALCULATION_TYPE_nnnn placeholder elements in the WBDCALCULATION_TYPE dimension.

SUBPLAN_WFB_GLOBALORDETAILED N

The valid values are:

  • Global
  • Detailed

Global must be one of these calculation types:

  • PercentOfPeriodic
  • PercentOfAnnualized
  • YTDRanges

The default is Detailed.

Value1 Y/N Value of the amount/percent/rate depending on the calculation type of the definition. Indicates whether the benefit or cost definition is global or detailed.
  • If the calculation type is YTDRanges, do not specify a value for Value1 as it does not apply to this type.
  • If non-global benefit or cost

Value1 is optional with no default.

Value1 is used to specify the default amount, percent, or rate which can be overridden by the user when creating an action.

If global benefit or cost, Value1 is required.

Used to specify the percent to use in the calculations.

Value2 N Value of QuantityXRate type definition to specify the default quantity. It can be overridden when creating an action.

Value2 should not be specified for other calculation type than QuantityXRate benefits and costs.

Value2 is optional with no default.

SUBPLAN_WFB_DATETYPE N Type of date that the rule is based on, must be an attribute of employee or position.

These benefits and costs are calculated by employee:

  • EmployeeEffectiveStart
  • EmployeeEffectiveEnd
  • HireDate
  • PromotionDate
  • LongevityDate
  • CalendarDate

These benefits and costs are not calculated by employee:

  • EmployeeEffectiveStart
  • EmployeeEffectiveEnd
  • AssignmentEffectiveStart
  • AssignmentEffectiveEnd
  • PositionEffectiveStart
  • PositionEffectiveEnd
  • HireDate
  • PromotionDate
  • FundingAvailable
  • LongevityDate
  • CalendarDate

This is optional. The default is CalendarDate.

CalculationMethod N One time or regularly paid benefit. Only applies to FlatAmount and QuantityXRate.

The valid values are:

  • 1 to apply across periods.
  • 0 to apply to a single period.

This is optional. The default is 1.

ShiftDifferential N Shift differential. Applies to non-global benefits and costs calculation type PremiumAmountBaseRate.

The valid values are:

  • 1 for active.
  • 0 for inactive.

This is optional. The default is 1.

AllowBudgeterToApply N Enable a user to apply the cost or benefit to positions or employees.

The valid values are:

  • 1 for active.
  • 0 for inactive.

Only applies to non-global benefits and costs.

This is optional. The default is 1.

AllowBudgeterToOverride N Allow budgeters to input different values.

The valid values are:

  • 1 for active.
  • 0 for inactive.

All calculation types for global benefits and costs and calculation type YTDRanges for benefits and costs must have a value 0. The default is 0.

All other calculation types for benefits and costs can be 0 or 1. The default is 1.

Scope Y/N Scope represents entity to which the pay or benefit definition applies.

The valid values are:

  • A single BPDENTITY.
  • A value that represents all entities. The valid values for all entities are ALLENTITIES.

Required for global benefits.

There is no default value.