Table: dbo.Client_Benefit
Columns
NameTypeConstraintsDescription
client_benefit_KEYint (4)NOT NULLPrimary key of table.
client_KEYint (4)NOT NULLKey of associated client.
descriptionnvarchar (100)NOT NULLDescription of Client_Benefit record.
graduated_allowance_calculation__length_of_service_selection_KEYint (4)The unit of time by which the system measures employee service when determining an accruable benefit allowance using a graduated table. Null when the benefit is not configured to use a graduated table.
accrual_method_KEYnvarchar (40)NOT NULLBenefit accrual method
benefit_accrual_balance_reset_type_KEYnvarchar (26)NOT NULLSpecifies when the balance resets
basis__compare_to_date_type_KEYnvarchar (34)NOT NULLThe kind of date we use to know when the system should accrue a benefit or reset benefit balance.
include_overtime_hoursbit (1)NOT NULLIndicates whether overtime hours are considered when calculating the benefit
include_doubletime_hoursbit (1)NOT NULLIndicates whether doubletime hours are considered when calculating the benefit
allowance_amountdecimal (5, 4)NOT NULLallowance_amount
available_accrual_limitdecimal (5, 4)NOT NULLMaximum benefit hours available to use in the current benefit period.
per_month_accrual_limitdecimal (5, 4)NOT NULLMaximum benefit hours that may accrue in one calendar month.
annual_accrual_limitdecimal (5, 4)NOT NULLMaximum number of benefit hours that may accrue in one calendar year.
per_check_accrual_limitdecimal (5, 4)NOT NULLMaximum benefit hours that may accrue on one payroll check.
reset_balances_to_zerobit (1)NOT NULLIndicates if balances are to be reset
carryover_maximumdecimal (5, 4)NOT NULLMaximum amount that can be carried over from one accrual period to the next.
balance_reset_datesmalldatetime (4)When balance reset type is per year, this is the calendar date on which the system resets the balance. Otherwise, it is null.
report_descriptionnvarchar (100)NOT NULLOptional description for use on reports, in place of["BR"] benefit description.
department_specificbit (1)NOT NULLOptionally allow the benefit to accrue via time worked["BR"] in a single department or in selected departments.
selection_scope_KEYint (4)NOT NULLworker compensation exemption type key
client_benefit_guiduniqueidentifier (16)NOT NULLA unique GUID key for this row
sequence_numberint (4)NOT NULLThis user defined sequence number which defines the order which the benefit item should be presented.
benefit_period_usage_type_KEYint (4)NOT NULL
record_status_KEYint (4)NOT NULLThe status of the accruable benefit record.
insurepay_accruable_benefit_type_KEYint (4)A Foreign key for this table. The associated Insurepay_Accruable_Benefit_Type primary key
xactpay_accruable_benefit_type_KEYint (4)A Foreign key for this table.The associated XactPay_Accruable_Benefit_Type primary key.
accrue_benefits_on_benefit_hours_usedbit (1)NOT NULLFlag which denotes if benefit hours are accrued on benefit hours used. Only applicable to Per Hours Worked type benefits.
work_hours_per_benefit_allowancedecimal (5, 4)NOT NULLThe number of hours an employee must work to accrue the benefit allowance. Only applicable for "Per hourly increment" type accruals.
accrual_delay_valuesmallint (2)NOT NULLThe number of duration units after the employee hire date the employee begins accruing benefit hours.
accrual_delay__benefit_eligibility_duration_type_KEYtinyint (1)NOT NULLAccrual delay duration unit; FK to the Benefit_Eligibility_Duration_Type.
availability_delay_valuesmallint (2)NOT NULLThe number of duration units after the employee hire date that the employee can use accrued benefit hours.
availability_delay__benefit_eligibility_duration_type_KEYtinyint (1)NOT NULLAvailability delay duration unit; FK to the Benefit_Eligibility_Duration_Type.
annual_usage_limitdecimal (5, 4)NOT NULLMaximum number of benefit hours that may used in one benefit year. Could be employee career, calendar year, or anniversary based on the benefit reset method. Not to be confused with the limit on hours that can accrue in a year.
per_month_usage_limitdecimal (5, 4)NOT NULLMaximum number of benefit hours that may used in the current calendar month. If the check is in a particular month the limit will just be on hours in that month. Not to be confused with the limit on hours that can accrue in a month.
per_check_usage_limitdecimal (5, 4)NOT NULLMaximum number of benefit hours that may used on one payroll check. Not to be confused with the limit on hours that can accrue on a payroll check.
Primary key
NameColumnsDescription
PK_Client_Benefitclient_benefit_KEY
Unique constraints
NameColumnsDescription
UK_Client_Benefit$client_benefit_guidclient_benefit_guid
UK_Client_Benefit$client_KEY$descriptionclient_KEY, description
Foreign keys
NameColumnsReferenced tableDescription
FK_Client_Benefit__Benefit_Accrual_Balance_Reset_Type__benefit_accrual_balance_reset_type_KEYbenefit_accrual_balance_reset_type_KEYBenefit_Accrual_Balance_Reset_Type (benefit_accrual_balance_reset_type_KEY)
FK_Client_Benefit__Benefit_Period_Usage_Type__benefit_period_usage_type_KEYbenefit_period_usage_type_KEYBenefit_Period_Usage_Type (benefit_period_usage_type_KEY)
FK_Client_Benefit__Client__client_KEYclient_KEYClient (client_KEY)
FK_Client_Benefit__Compare_Date_Type__basis__compare_to_date_type_KEYbasis__compare_to_date_type_KEYCompare_Date_Type (compare_date_type_KEY)
FK_Client_Benefit__Insurepay_Accruable_Benefit_Type__insurepay_accruable_benefit_type_KEYinsurepay_accruable_benefit_type_KEYInsurepay_Accruable_Benefit_Type (insurepay_accruable_benefit_type_KEY)
FK_Client_Benefit__Length_Of_Service_Selection__graduated_allowance_calculation__length_of_service_selection_KEYgraduated_allowance_calculation__length_of_service_selection_KEYLength_Of_Service_Selection (length_of_service_selection_KEY)
FK_Client_Benefit__Record_Status__record_status_KEYrecord_status_KEYRecord_Status (record_status_KEY)
FK_Client_Benefit__Selection_Scope__selection_scope_KEYselection_scope_KEYSelection_Scope (selection_scope_KEY)
FK_Client_Benefit__XactPay_Accruable_Benefit_Type__xactpay_accruable_benefit_type_KEYxactpay_accruable_benefit_type_KEYXactPay_Accruable_Benefit_Type (xactpay_accruable_benefit_type_KEY)
FK_Client_Benefit__Accrual_Method__accrual_method_KEYaccrual_method_KEYAccrual_Method (accrual_method_KEY)
FK_Client_Benefit$accrual_delay__benefit_eligibility_duration_type_KEYaccrual_delay__benefit_eligibility_duration_type_KEYBenefit_Eligibility_Duration_Type (benefit_eligibility_duration_type_KEY)
FK_Client_Benefit$availability_delay__benefit_eligibility_duration_type_KEYavailability_delay__benefit_eligibility_duration_type_KEYBenefit_Eligibility_Duration_Type (benefit_eligibility_duration_type_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Ascii_Time_Clock_Client_Benefit_Id_Map$client_benefit_KEYclient_benefit_KEYAscii_Time_Clock_Client_Benefit_Id_Map
FK_Benefit_Item_Worker_Compensation_Exemption__Client_Benefit__client_benefit_KEYclient_benefit_KEYBenefit_Item_Worker_Compensation_Exemption
FK_Accrual_Date__Client_Benefit__client_benefit_KEYclient_benefit_KEYAccrual_Date
FK_Benefit_Allowance__Client_Benefit__client_benefit_KEYclient_benefit_KEYBenefit_Allowance
FK_Insurepay_Client_Class_Code_N_Client_Benefit__Client_Benefit__client_benefit_KEYclient_benefit_KEYInsurepay_Client_Class_Code_N_Client_Benefit
FK_Employee_Benefit__Client_Benefit__client_benefit_KEYclient_benefit_KEYEmployee_Benefit
FK_Import_Template_Column_Payroll_Item_Data__Client_Benefit__client_benefit_KEYclient_benefit_KEYImport_Template_Column_Payroll_Item_Data
FK_Import_Template_Column_Employee_Data__Client_Benefit__client_benefit_KEYclient_benefit_KEYImport_Template_Column_Employee_Data
FK_ATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition__Client_Benefit__client_benefit_KEYclient_benefit_KEYATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition
FK_Payroll_Check_Entry_Option_Payroll_Item_Definition__Client_Benefit__client_benefit_KEYclient_benefit_KEYPayroll_Check_Entry_Option_Payroll_Item_Definition
FK_Benefit_Accrual_Exclusion_Detail__Client_Benefit__client_benefit_KEYclient_benefit_KEYBenefit_Accrual_Exclusion_Detail

Schema diagram