Table: dbo.Client_Payroll_Item
Columns
NameTypeConstraintsDescription
client_payroll_item_KEYint (4)NOT NULLKey for the record
descriptionnvarchar (60)NOT NULLItem description, which must be unique within the scope of the items for the client
alternate_descriptionnvarchar (60)NOT NULLThis allows a user to optionally display a different description on checks and reports.
client_KEYint (4)NOT NULLClient the item applies to.
payroll_item_type_KEYint (4)NOT NULLKey of item type.
payroll_item_calculation_type_KEYint (4)NOT NULLKey of calculation type.
amountdecimal (9, 2)NOT NULLDefault amount copied to employee payroll item when the system adds the item to an employee.
percentagedecimal (5, 4)NOT NULLDefault percentage copied to employee payroll item when the system adds the item to an employee.
ratedecimal (9, 4)NOT NULLDefault rate copied to employee payroll item when the system adds the item to an employee.
is_excluded_from_gross_pay_amountbit (1)NOT NULLIndicates if item amount is excluded from gross pay
is_excluded_from_net_paybit (1)NOT NULLis_excluded_net_pay
wage_type_KEYnvarchar (30)Key of wage type.
ot_dt_amounts_are_supplemental_wagesbit (1)NOT NULLare_ot_dt_amts_supplemental_wages
vendor_KEYint (4)Vendor to which payables are generated for this item.
special__deduction_type_KEYint (4)Holds benefit type for this deduction item
related_payroll_item__client_payroll_item_KEYint (4)Reference of a deduction item to a employer contribution payroll item
limit_calculation_type_KEYnvarchar (30)Used for limit setup. Holds limit calculation type
calculation_limitdecimal (9, 2)NOT NULLDefines an optional calculation limit for the payroll item.
calculation_limit_related_item__client_payroll_item_KEYint (4)Key of a deduction item which a user may optionally use to specify a calculation limit for a payroll item.
federal_unemployment_tax_exemption_type_KEYint (4)Optional federal unemployment tax exemption.
inactivebit (1)NOT NULLFlags the payroll item as inactive, which means the user does not want to use it but does not want to delete it either.
is_other_wagesbit (1)NOT NULLFlag indicating whether or not this payroll item is not regular wages, but rather other wages.
client_payroll_item_guiduniqueidentifier (16)NOT NULLA unique GUID key for this row
worker_compensation_class_code_detail_KEYint (4)worker compensation class code this deduction is for
selection_scope_KEYint (4)NOT NULLworker compensation exemption type key
liability__gl_account_KEYint (4)Key to the gl_account table for liability account.
expense__gl_account_KEYint (4)Key to the gl_account table for expense account.
is_auto_paybit (1)NOT NULLFlag when true specifies that the system automatically give this employee hours or pay when creating the check.
garnishment_type_KEYint (4)If this payroll item is a garnishment, this says what kind. If not a garnishment this field should be null and not used.
is_w2_dependent_care_benefitbit (1)NOT NULLFlag pay and deduction item amount for display in W-2 Dependent Care Benefits box.
w2_compensation_code_type_KEYint (4)W-2 compensation code, optional for all payroll item types. When used on a payroll item, the code and associated description appear on the employee W-2.
is_w2_retirement_planbit (1)NOT NULLW-2 processing uses this flag to know when to mark the W-2 retirement plan check box. Applicable to deduction and employer contribution items.
w2_other_descriptionnvarchar (30)NOT NULLOptional description (determined by the user) for use in the W-2 other description box. Applicable to all payroll item types.
sequence_numberint (4)NOT NULLThis user defined sequence number which defines the order which the payroll item should be presented.
special_type_KEYint (4)NOT NULLThe payroll item special type if needed.
accruable_benefits__selection_scope_KEYint (4)NOT NULLShows which type of accruable benefits to exclude from pay check calculations
payroll_item_special_timing_KEYint (4)NOT NULLForeign key into the Payroll Item Special timing table.
limit_period_type_KEYint (4)Used for limit setup. Holds the selected limit triggert
is_excluded_from_garnishment_disposable_incomebit (1)NOT NULLIndicates if we should exclude this payroll item from garnishment disposal income.
pay_items_exclude_all_regularbit (1)NOT NULLIndicates that the client payroll item should exclude regular wages from all pay items for this client.
pay_items_exclude_all_OTbit (1)NOT NULLIndicates that the client payroll item should exclude overtime wages from all pay items for this client.
pay_items_exclude_all_DTbit (1)NOT NULLIndicates that the client payroll item should exclude doubletime wages from all pay items for this client.
pay_items_exclude_all_accruable_benefitsbit (1)NOT NULLIndicates that the client payroll item should exclude accruable benefit wages from all pay items for this client.
is_exclude_premium_wagesbit (1)NOT NULLIndicates that the client payroll item should calculate assuming 1.0 as both the OT and DT multipliers when excluding wages.
w2_nonqualified_deferred_compensation_type_KEYint (4)Optional type of nonqualified deferred compensation. Used for W2 processing.
health_plan_type_KEYint (4)NOT NULLThe selection for health plan type such as individual or family.
is_subject_to_health_savings_account_limitbit (1)NOT NULLUsed only for employer contribution payroll item types to indicate if the item is subject to the health savings account limit.
is_excluded_from_gross_pay_hoursbit (1)NOT NULLIndicates if item hours are excluded from gross pay
is_weighted_allocationbit (1)NOT NULLFlag that indicates the payroll item is using a weighted allocation that will be based on gross pay per location/department distribution.
maximum_percentage_of_disposable_incomedecimal (5, 2)NOT NULLPercentage field that holds the maximum percentage of disposable income that can be garnished for pay items of garnishment type.
use_lesser_max_percent_or_amount_exceeds_min_wagebit (1)NOT NULLUse the lesser of maximun percent of disposable income or amount by which disposable income exceeds minimum wage. Only used for Other garnishment type.
use_federal_min_wage_that_exceeds_disposal_incomebit (1)NOT NULLUse federal minimum wage when determining the amount that exceeds disposable income. Only valid for Other garnishment type. Only used for Other garnishment type.
apply_catch_up_limitbit (1)The apply_catch_up_limit is only used for Retirement plans. True indicates that catch-up contributions should be made to the Retirement plan if the employee’s age meets the catch-up criteria. False means that catch-up contributions should not be made, even if the employee’s age qualifies. This column is set to null for all items that are not Retirement plans.
client_payroll_item_versiontimestamp (8)NOT NULLThe version of this row. It's an aid in versioning the row.
client_payroll_item_force_new_version_togglebit (1)NOT NULLA bit to toggle that causes the client_payroll_item_version to update. Flip this bit when a subordinate tables are changed.
alternate_minimum_wagedecimal (5, 2)The alternate minimum wage is optionally used as an default minimum wage on special tipped wages types
subtract_cafeteria_125_deduction_before_calculatingbit (1)NOT NULLThis flag determines whether or not to subtract the Section 125 Cafeteria deduction before calculating retirement plans.
require_direct_deposit_allocationbit (1)NOT NULLIf (true), direct deposit allocations are required.
is_deferred_fica_ss_repaymentbit (1)NOT NULLFlag that will indicate if this client payroll item is a special type of deduction item.
Primary key
NameColumnsDescription
PK_Client_Payroll_Itemclient_payroll_item_KEY
Unique constraints
NameColumnsDescription
UK_Client_Payroll_Item$client_KEY$payroll_item_type_KEY$descriptionclient_KEY, payroll_item_type_KEY, description
UK_Client_Payroll_Item$client_payroll_item_guidclient_payroll_item_guid
UK_Client_Payroll_Item$payroll_item_special_timing_KEYpayroll_item_special_timing_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Client_Payroll_Item__Client__client_KEYclient_KEYClient (client_KEY)
FK_Client_Payroll_Item__Client_Payroll_Item__calculation_limit_related_item__client_payroll_item_KEYcalculation_limit_related_item__client_payroll_item_KEYClient_Payroll_Item (client_payroll_item_KEY)
FK_Client_Payroll_Item__Client_Payroll_Item__related_payroll_item__client_payroll_item_KEYrelated_payroll_item__client_payroll_item_KEYClient_Payroll_Item (client_payroll_item_KEY)
FK_Client_Payroll_Item__Deduction_Type__special__deduction_type_KEYspecial__deduction_type_KEYDeduction_Type (deduction_type_KEY)
FK_Client_Payroll_Item__Federal_Unemployment_Tax_Exemption_Type__federal_unemployment_tax_exemption_type_KEYfederal_unemployment_tax_exemption_type_KEYFederal_Unemployment_Tax_Exemption_Type (federal_unemployment_tax_exemption_type_KEY)
FK_Client_Payroll_Item__Garnishment_Type__garnishment_type_KEYgarnishment_type_KEYGarnishment_Type (garnishment_type_KEY)
FK_Client_Payroll_Item__GL_Account__expense__gl_account_KEYexpense__gl_account_KEYGL_Account (gl_account_KEY)
FK_Client_Payroll_Item__GL_Account__liability__gl_account_KEYliability__gl_account_KEYGL_Account (gl_account_KEY)
FK_Client_Payroll_Item__Health_Plan_Type__health_plan_type_KEYhealth_plan_type_KEYHealth_Plan_Type (health_plan_type_KEY)
FK_Client_Payroll_Item__Limit_Calculation_Type__limit_calculation_type_KEYlimit_calculation_type_KEYLimit_Calculation_Type (limit_calculation_type_KEY)
FK_Client_Payroll_Item__Limit_Period_Type__limit_period_type_KEYlimit_period_type_KEYLimit_Period_Type (limit_period_type_KEY)
FK_Client_Payroll_Item__Payroll_Item_Calculation_Type__payroll_item_calculation_type_KEYpayroll_item_calculation_type_KEYPayroll_Item_Calculation_Type (payroll_item_calculation_type_KEY)
FK_Client_Payroll_Item__Payroll_Item_Special_Timing__payroll_item_special_timing_KEYpayroll_item_special_timing_KEYPayroll_Item_Special_Timing (payroll_item_special_timing_KEY)
FK_Client_Payroll_Item__Payroll_Item_Special_Type__special_type_KEYspecial_type_KEYPayroll_Item_Special_Type (payroll_item_special_type_KEY)
FK_Client_Payroll_Item__Payroll_Item_Type__payroll_item_type_KEYpayroll_item_type_KEYPayroll_Item_Type (payroll_item_type_KEY)
FK_Client_Payroll_Item__Selection_Scope__accruable_benefits__selection_scope_KEYaccruable_benefits__selection_scope_KEYSelection_Scope (selection_scope_KEY)
FK_Client_Payroll_Item__Selection_Scope__selection_scope_KEYselection_scope_KEYSelection_Scope (selection_scope_KEY)
FK_Client_Payroll_Item__Vendor__vendor_KEYvendor_KEYVendor (vendor_KEY)
FK_Client_Payroll_Item__W2_Compensation_Code_Type__w2_compensation_code_type_KEYw2_compensation_code_type_KEYW2_Compensation_Code_Type (w2_compensation_code_type_KEY)
FK_Client_Payroll_Item__W2_Nonqualified_Deferred_Compensation_Type__w2_nonqualified_deferred_compensation_type_KEYw2_nonqualified_deferred_compensation_type_KEYW2_Nonqualified_Deferred_Compensation_Type (w2_nonqualified_deferred_compensation_type_KEY)
FK_Client_Payroll_Item__Wage_Type__wage_type_KEYwage_type_KEYWage_Type (wage_type_KEY)
FK_Client_Payroll_Item__Worker_Compensation_Class_Code_Detail__worker_compensation_class_code_detail_KEYworker_compensation_class_code_detail_KEYWorker_Compensation_Class_Code_Detail (worker_compensation_class_code_detail_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Client_Payroll_Item__Client_Payroll_Item__calculation_limit_related_item__client_payroll_item_KEYcalculation_limit_related_item__client_payroll_item_KEYClient_Payroll_Item
FK_Client_Payroll_Item__Client_Payroll_Item__related_payroll_item__client_payroll_item_KEYrelated_payroll_item__client_payroll_item_KEYClient_Payroll_Item
FK_Shortfall_Deduction_Treatment_Selection__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYShortfall_Deduction_Treatment_Selection
FK_Graduated_Payroll_Item_Tier__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYGraduated_Payroll_Item_Tier
FK_Deduction_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYDeduction_Item
FK_Import_Template_Column_Payroll_Item_Data__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYImport_Template_Column_Payroll_Item_Data
FK_Federal_Tax_Exemption__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYFederal_Tax_Exemption
FK_State_Tax_Exemption__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYState_Tax_Exemption
FK_Import_Template_Column_Employee_Data__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYImport_Template_Column_Employee_Data
FK_Pay_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPay_Item
FK_Local_Tax_Exemption__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYLocal_Tax_Exemption
FK_Payroll_Check_Entry_Option_Payroll_Item_Exclusion__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPayroll_Check_Entry_Option_Payroll_Item_Exclusion
FK_Payroll_Item_Worker_Compensation_Exemption__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPayroll_Item_Worker_Compensation_Exemption
FK_ATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYATF_Payroll_Check_Entry_Option_Pay_And_Tax_Item_Definition
FK_Ascii_Time_Clock_Client_Payroll_Item_Id_Map__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYAscii_Time_Clock_Client_Payroll_Item_Id_Map
FK_Quick_Books_Payroll_Item_N_Client_Payroll_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYQuick_Books_Payroll_Item_N_Client_Payroll_Item
FK_Payroll_Check_Entry_Option_Payroll_Item_Definition__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPayroll_Check_Entry_Option_Payroll_Item_Definition
FK_Payroll_Check_Entry_Option_Shortfall_Deduction_Treatment__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPayroll_Check_Entry_Option_Shortfall_Deduction_Treatment
FK_Employer_Contribution_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYEmployer_Contribution_Item
FK_Employee_Payroll_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYEmployee_Payroll_Item
FK_Insurepay_Client_Class_Code_N_Client_Payroll_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYInsurepay_Client_Class_Code_N_Client_Payroll_Item
FK_Benefit_Accrual_Exclusion_Detail__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYBenefit_Accrual_Exclusion_Detail
FK_Pay_Item_Exclusion_Detail__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPay_Item_Exclusion_Detail
FK_Pay_Item_Exclusion_Detail__Client_Payroll_Item__excluded__client_payroll_item_KEYexcluded__client_payroll_item_KEYPay_Item_Exclusion_Detail
FK_Payroll_Check_Payroll_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYPayroll_Check_Payroll_Item
FK_Shortfall_Transaction_N_Client_Payroll_Item__Client_Payroll_Item__client_payroll_item_KEYclient_payroll_item_KEYShortfall_Transaction_N_Client_Payroll_Item

Schema diagram