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)

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

24 Responses to “Payroll Tables”

  1. Hi Victoria,
    Do you know what the names for the FLSASTATUS fields are in UPR40301. It shows 0-4. I need the names. They should be like Exempt, non-exempt … thanks for the help.


  2. I have a need to regenerate 3 years worth of payroll check registers. The GP report option allows you to print one audit trail code at a time – looking for something faster. Do you know, Victoria, if there is a SQL view out there that would help me with this? The standard register had a total pay, total deductions (including taxes) and net pay. I’m after that level of detail, but the history tables are more detailed than that, and federal tax data is in a separate table than the pay, deductions, state, local taxes, etc. Don’t want to reinvent the wheel, if I can help it!

    Thank you,
    Teri Greene


    • Hi Teri,

      Sorry, I don’t work with payroll enough to have an answer for this, but I am sure it’s possible in SQL. If you have not already, I would recommend posting this question on the GP User Forum, there are a lot of smart folks on there that might be able to help.



  3. 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,


  4. 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.


  5. 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.


    • 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.



      • 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,


        • 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.



          • 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 .


  6. 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!


    • 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.



  7. 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!


  8. 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?




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


  10. 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!




  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 […]


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: