Payroll Tables


Commonly Used Tables:
UPR00100 – Employee Master
UPR00102 – Address Master
UPR00300 – Tax Information Master
UPR00400 – Pay Code Master
UPR00500 – Deduction Master
UPR00600 – Benefit Master
UPR00700 – State Tax Master
UPR00800 – Local Tax Master
UPR00900 – Employee Summary
UPR10100 – Year End Header
UPR10101 – Year End Wage
UPR10200 – Work Master
UPR10201 – Work Master Detail
UPR10202 – Work Header
UPR10300 – Payroll Activity
UPR10301 – Payroll Batches
UPR10302 – Transactions
UPR10304 – Transaction Batch Activity
UPR10307 – Manual Check
UPR10308 – Manual Check Detail
UPR10309 – Keys Master
UPR30100 – Check History
UPR30300 – Transaction History
UPR30301 – Transaction History Header
UPR30400 – Distribution History Header
UPR30401 – Distribution History Detail
UPR40300 – Departments
UPR40301 – Positions
UPR40500 – Payroll Posting Accounts
UPR40900 – Deduction Setup
UPR41600 – Employee Count (in system database)

INACTIVE:
0 – No
1 – Yes

DEDNFREQ (Deduction Frequency):
1 – Weekly
2 – Biweekly
3 – Semimonthly
4 – Monthly
5 – Quarterly
6 – Semiannually
7 – Annually
8 – Daily/Miscellaneous

VARDEDTN (Transaction Required):
0 – No
1 – Yes

UPRACTYP (Payroll Account Type):
1 – Gross Pay (DR)
2 – Federal Tax Withholding (CR)
3 – State Tax Withholding (CR)
4 – Local Tax Withholding (CR)
5 – Deduction Withholding (CR)
6 – Employer’s Tax Expense (DR)
7 – Benefits Expense (DR)
8 – Benefits Payable (CR)
9 – Taxable Benefits Expense (DR)
10 – Taxable Benefits Payable (CR)
11 – SUTA Payable (CR)
12 – FUTA Payable (CR)
13 – W/Comp Tax Expense (DR)
14 – W/Comp Tax Payable (CR)

PYRLRTYP (Payroll Record Type):
1 – Pay Codes
2 – Deductions
3 – Benefits
4 – State Taxes
5 – Local Taxes

PAYTYPE (Pay Type):
1 – Hourly
2 – Salary
3 – Piecework
4 – Commission
5 – Business Expense
6 – Overtime
7 – Double Time
8 – Vacation
9 – Sick
10 – Holiday
11 – Pension
12 – Other
13 – Earned Income Credit
14 – Charged Tips
15 – Reported Tips
16 – Minimum Wage Balance


Last Updated August 4, 2014

21 Responses to “Payroll Tables”

  1. Hi Victoria,
    I generate an overtime report for my employer each pay period from the payroll history reports. This report only has the employee ID numbers. I would like for the employee names to print out in stead or in addition to. I think I need to modify this report by creating a calculated field with the correct table number for names or create a smartlist report for it. Either way I need to know which table has the employee names. Can you advise me? Thank you,
    Brenda

    Like

  2. Victoria, do you know if there’s a table where I can get the meaning of the values on the different code types, some sort of like a string map? E.g. PYRLRTYP 1 means Gross Pay.

    Like

  3. Hello Victoria ,
    I am trying to write to table UPR10302 and I am getting duplicate key error on COMPTRNM. Is there a way to increment and stay consistent with DEX_ROW_ID field. Thanks for all of your knowledge.
    -Randy

    Like

    • Hi Randy,

      All the GP tables are set up so that you do not have to insert the DEX_ROW_ID…leave it out of your insert and SQL will do automatically fill it in for you.

      -Victoria

      Like

      • thanks for the reply , but my error is on UPR10302.COMPTRNM I was wondering if it can be automatically filled in similar to DEX_ROW_ID,

        Like

        • Randy,

          Sorry I misunderstood your question. COMPTRNM is the next payroll transaction number. The next number GP wants to use is stored in the UPR40200 table in field NXCTRNUM. So you should use what’s in there and increment it once used.

          However, this brings up a possibly bigger question – why are you trying to manually insert data into a transaction table? That’s usually not advised and will cause all sorts of trouble if you’re not doing proper data validation and not updating all necessary related tables at the same time.

          -Victoria

          Like

          • thank you very much for the info.
            To you bigger question I am writing an interface from iSeries/as400 into GP. I know I am doing this totally backwards but I created payroll records in GP search for the tables that was updated i then sent a similar record from iSeries into GP and corrected errors and included new tables along they way until i get it sync with the GP record. The tables i am currently using are UPR10301 & UPR10302 I am new to GP and your blogs has help me tremendously .

            Like

  4. Victoria:
    I have a scenario where there can be a check for an employee that is obligated to make payments to a court or government body. In some instances, these amounts should completely consume the check amount. However if there are multiple obligations, the rounding errors sometimes leave a check for a few pennies. We don’t want to issues these checks due to the small amount and due to the fact that their obligation should amount to the whole check amount. Do you have a suggestion on the best way to handle this scenario?
    Thank you in advance for your answer, and for all you do for the GP community. I know that when I see your name on a comment, I always have confidence that the answer is spot-on!
    Thanks
    Dave

    Like

    • Hi Dave,

      Thanks for your kind words! Unfortunately, I don’t work with the payroll module much and have not run into this before, so I am not sure what the best way to handle it would be. If you have not already, I would recommend posting your question on the Dynamics GP Community Forum, there are a number of folks who frequently answer questions there that know Payroll very well and may be able to help you better.

      -Victoria

      Like

  5. Hi Victoria.

    Thank you so much for the work you put into your blog. I just wanted to mention that the “positions” table should be UPR40301, not UPR40300.

    Thanks again!
    Dan

    Like

  6. Hi Victoria,

    I am working with a client who has multiple test companies set up in GP 2010 and they are now ready to go live. I tried to change the name of one of the companies to remove the test name so the message went away and it tells me that I cannot because I have too many employees. They have never set up payroll at all. I deleted a table UPR41600 which I read should have re-set the employee count but it did not fix it. Would you have any suggestions?

    Thanks!

    Randy

    Like

  7. Thanks a bunch!!! That’ll help me out LOTS!!

    Like

  8. Victoria, Is there a script I could use in SQL Studio, where I could identify all the tables that have a check number field (CHECKNMBR)?? I’m sure it would be handy for other searches, too. Thanks for your time!

    Bill

    Like

Trackbacks/Pingbacks

  1. Determining the Payroll Posting Account for GP Part II: Uses of the Function - Sockeye Consulting - February 1, 2017

    […] out Victoria Yudin’s Blog HERE to get a list of the other UPRACTYP […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: