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

24 Responses to “Payroll Tables”

Jerry Reply December 3, 2020 at 12:06 pm

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.

LikeLike
Teri Greene Reply May 6, 2019 at 4:46 pm

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

LikeLike
- Victoria Yudin Reply May 7, 2019 at 3:27 pm
  
  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.
  
  -Victoria
  
  LikeLike
Brenda Carnes Reply May 25, 2016 at 3:13 pm

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

LikeLike
- Victoria Yudin Reply May 26, 2016 at 4:58 pm
  
  Hi Brenda,
  
  The employee names will be in the UPR00100 table.
  
  -Victoria
  
  LikeLike
Daniel A Reply October 9, 2015 at 12:20 pm

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.

LikeLike
- Victoria Yudin Reply October 9, 2015 at 1:16 pm
  
  Daniel,
  
  To my knowledge these are not stored in tables. You can sometimes find a function in the GP database that will have the values.
  
  -Victoria
  
  LikeLike
randy cayton Reply March 25, 2015 at 9:09 am

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

LikeLike
- Victoria Yudin Reply March 25, 2015 at 9:15 am
  
  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
  
  LikeLike
  - randy cayton Reply March 25, 2015 at 9:18 am
    
    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,
    
    LikeLike
    - Victoria Yudin Reply March 25, 2015 at 9:32 am
      
      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
      
      LikeLike
      - randy cayton Reply March 25, 2015 at 9:51 am
        
        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 .
        
        LikeLike
David Drlich Reply October 31, 2014 at 1:42 pm

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

LikeLike
- Victoria Yudin Reply November 3, 2014 at 10:32 am
  
  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
  
  LikeLike
Dan Saffo Reply July 28, 2014 at 9:17 pm

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

LikeLike
- Victoria Yudin Reply July 29, 2014 at 3:56 am
  
  Hi Dan,
  
  Thanks for catching my typo! 🙂
  
  -Victoria
  
  LikeLike
Randy Chapman Reply June 2, 2014 at 10:10 am

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

LikeLike
- Victoria Yudin Reply June 2, 2014 at 10:16 am
  
  Hi Randy,
  
  The only thing I have found on this is this KB article http://support.microsoft.com/kb/853103, but it sounds like you already saw that. You might have to check with Microsoft on this one, sorry.
  
  -Victoria
  
  LikeLike
  - Randy Chapman Reply June 2, 2014 at 10:41 am
    
    Thanks for the quick response 🙂 I know I ran into this about 5 years ago and I fixed it but I just can’t remember what I did. I will get with Microsoft.
    
    Randy
    
    LikeLike
Bill L. Reply March 19, 2014 at 2:47 pm

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

LikeLike
Bill L. Reply March 19, 2014 at 2:40 pm

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

LikeLike
- Victoria Yudin Reply March 19, 2014 at 2:44 pm
  
  Hi Bill,
  
  There sure is – check out this blog post. 🙂
  
  -Victoria
  
  LikeLike
- David Drlich Reply March 25, 2015 at 9:56 am
  
  Sometimes you have to be careful with that, Bill. GP is unfortunately not always consistent in its abbreviations, so that can sometimes make it tricky depending on what you’re looking for.
  
  LikeLike