Custom Crystal Report showing pre-tax and post-tax deductions

SOLVED

I'm trying to create a custom crystal report that shows Employee Number, Full Name, Gross wages, Pre-Tax Deductions, Post-Tax Deductions, Gross wages (excluding Pre-Tax Deductions).

I believe I need to use UPEMPL, UPCHKH, and UPCHKD to get the Employee Number, Full Name, and Gross Wages. I can sum the check detail on Category=4 (Deductions) to get all deductions but how do I determine if it is a pre-tax or post-tax deduction?

Any help would be appreciated.

EDIT:  We are using Sage HRMS v10.7045 / Sage 300. We will be applying the quarterly update in the next day or two.

  • 0
    SUGGESTED

    You may need to add one more table.  UPDTLM includes a W2 definition that might be helpful.:

    EW2BOX  Integer  Employee W-2 Box  List: 13 entries
    1 = Not Applicable
    2 = Other Information Box
    3 = Dependent Care
    4 = Employee 401(k)
    5 = Employee 403(b)
    6 = Employee 408(k)(6)
    7 = Employee 457
    8 = Employee 501(c)(18)(d)
    9 = SIMPLE Retirement Acct
    10 = Deferrals under section 409A
    11 = Roth contributions to Employee 401(k)
    12 = Roth contributions to Employee 403(b)
    13 = Health Savings Account
  • 0 in reply to Arlie C. Skory

    When I look at the EW2BOX, there is also a number 14 listed which appears to be our Health Insurance Plan. Does that seem right? Also there is a 0 listed in the EW2BOX for some earning codes?

  • +1 in reply to JStrother
    verified answer

    UPDTLM.EW2BOX is used for Aatrix integration. Unless you guys set it up correctly, it probably is not. I don't think this can be used to determine what deductions are pre tax and which are not. Querying deductions that are pre-tax is not that simple because a deduction can be pre tax for one or many different taxes. I recommend looking in the table UPINCL where CATEGORY=4 and OWNERTYPE=2 . This will query deductions/INCLUDEID's with individual records for each tax/OWNERID that is deducted after the deduction. If a deduction does not appear on this query that means it is after tax. Alternatively, you can also add an optional field at the earnings/deduction code level in order to flag it as pretax or after tax and then write your report around that.

  • 0 in reply to angel g

    Using the table UPINCL is what I was looking for. The EW2BOX in UPDTLM might work for some but did not achieve the effect I was seeking.

    Thank you both for your help!