I'm a PeopleSoft programmer who's been asked to work on some Great Plains reports . . . and I need some help.  Please help, as once I have the information I'll repost the results back into an FAQ for others.

All of the table names are jibberish.  Sure, you've got the first three letters of a table tied to the module . . . but how do I know what tables to join for my report?  For instance, which table contains the detailed data for a paycheck?  How is a cancelled check defined?  How do I determine if a deduction is taxable or non-taxable at the table level (I assume there's a field on some deduction detail record).

If someone could point me to a resource, or maybe take the time to put a brief description next to each table . . . then I've at least got a fighting chance of making an effective set of report.  What would be best is if I could find a datamodel that shows the relationships between the tables along with a "usable" table name.

Thanks . . . and I promise to give more back to this forum than I take . . . so load me up with good information!!!

I told you I'd give more back than I got . . . but I didn't expect NO responses.  

IT WOULD BE REALLY NICE IF SOMEONE WHO KNOWS THESE TABLES COULD FILL IN THE BLANKS, ADD MORE RELEVANT INFORMATION, AND/OR CONFIRM MY WORK.

Anyway . . . this is what I've found so far . . . I did some informational SQL against a live database and the following tables had the following counts and rows per EE.  My Guesses on table "functional" names and comments on the data are included . . .

UPR00112     3 rows mult/EE (1-2  rows/EE) not all EEs
-- GUESS: EE Education Information

UPR10104    35 rows mult/EE (1-2  rows/EE) not all EEs
-- GUESS: Special Earnings Table (Yearly Totals)
-- Yearly / EE only one row per EE per Year
-- Multiple columns have "SPCL" in name: assume = "special"

UPR00113   205 rows mult/EE (1-4  rows/EE) not all EEs
-- Appears to be Emergency Contact Table

UPR00800   247 rows mult/EE (1-5  rows/EE)
-- GUESS: Local Tax Table (EE Level Setup)             
-- Only one row per EE per LOCALTAX
-- Presume LOCALTAX = a Local Tax Location Code

UPR10106   396 rows mult/EE (4-6  rows/EE)
-- GUESS: Local Tax Table (Yearly Totals)
-- Only one row per EE per LOCLCODE per Reporting Year
-- Presume LOCALTAX = a Local Tax Location Code
             
UPR00501   586 rows    1/EE not all EEs                
-- GUESS:  no guess (EE Level Setup)
-- Employee level setup table
-- All but two rows have DEDUCTON = 'L&I'
-- Two rows have DEDUCTON = 'GARN'
-- BSDONCDE column has this distribution:
--   1 = ALLW
-- 146 = HRLY
-- 146 = OT
-- 147 = SALARY
-- 146 = SALHRY
-- 146 EEs in table
-- Each employee each has 4 rows except one has 6

UPR00300  1258 rows    1/EE                            
-- GUESS: W2 Elections (EE Level Setup)
-- Includes Marital Status, Exemptions
-- 1 Record per employee
-- NOT by year.
-- Has a key of "NOTEINDX"

UPR00100  1258 rows    1/EE                            
-- GUESS: Employee Job Information Table (EE Level Setup)
-- Includes SSN, DOB, EEO codes, Job Title, etc.

UPR00700  1290 rows    1/EE                            
-- GUESS: Employee State Tax Elections (EE Level Setup)
-- Also has an attached "NOTEINDX"

UPR00102  1293 rows mult/EE (1-2  rows/EE)             
-- GUESS: Employee Contact Info (EE Level Setup)
-- Address and Phone Number
-- Multiple Address Types (Home, Mail, etc.)

UPR00111  1391 rows mult/EE (1-7  rows/EE)             
-- GUESS: Depemdent Beneficiary Table
-- Encoded Relationship (1, 2, 7, etc.)
-- Some strangly large Sequence Number is used (ex:16388)

UPR00600  1464 rows mult/EE (1-8  rows/EE)             
-- GUESS: Benefits Election Table
-- Includes Start/End dates
-- Includes Tax Deductibility
-- Includes a LOAD of amount fields

UPR10105  1512 rows mult/EE (1-6  rows/EE)
-- GUESS: State Tax Withholding Table (Yearly Totals)
-- Only 1 row per EE per Year per STATECD
-- Appears to hold yearly State Tax, State Wages
             
UPR00900  2310 rows mult/EE (1-6  rows/EE)             
-- GUESS: Check Detail Table
-- Includes EE ID, Check#, Date, & a lot of amounts

UPR00500  2836 rows mult/EE (1-12 rows/EE)             
-- GUESS: Deduction Table (EE Level Setup
-- Has Deduction Begin & End dates
-- Includes Garnishments, Dental, Other Insurances
-- Includes Tax Deductability Information
-- Includes a lot of different amounts
-- Has "Active/Inactive" flag (INACTIVE)

UPR00901  3224 rows mult/EE (1-6  rows/EE)
-- GUESS: Tips Table (Employee Level)
-- Completely empty (all 0 values)
-- Only DEX_ROW_ID makes rows unique for EE with mult row
             
UPR10101  3772 rows mult/EE (1-6  rows/EE)
-- GUESS: W2 Reported Table
-- Yearly/EE with a few EE that have up to 3 rows/year
-- EEs with mult/yr have col W2BFSTTL = <blank>, A, B, etc.
-- appears to be Yearly Pay & Deduction Totals
-- There are a couple rows for particular years with 'ÿÿÿÿÿÿÿ' in most fields and some very big numbers . . . probably some totals or something

UPR00400  8134 rows mult/EE (1-17 rows/EE)             
-- GUESS: Compensation Table (EE Level Setup)
-- Includes Hourly/Salary/Holiday/Etc. types of pay
-- Includes Pay Rate Amounts & associated unit
-- Includes Taxable Statuses

UPR00402     0 rows                                    
UPR10107     0 rows                                    
UPR10202     0 rows                                    
UPR10204     0 rows                                    
UPR10203     0 rows                                    
UPR00601     0 rows                                    
UPR10103     0 rows                                    
UPR10100     4 rows NON EE (Employer Table?)                            
UPR10200     0 rows NON EE                             
UPR10201     0 rows NON EE                             
              

UPDATE ON MY PREVIOUS GUESSES . . .

UPR00900  2310 rows mult/EE (1-6  rows/EE)             
-- GUESS: Last Pay Check Detail Table (YTD & MTD Check Stub Info)
-- Includes EE ID, Check#, Date, & a lot of amounts
-- Inculdes Month-to-Date totals for each month
-- One Record Per Year Per Employee
-- Has some extra "blank" records dated 1900
 

Here's a couple of things to know about PR tables which may help you:

When a pay run is posted the PR history files are updated. The history files are stored in the "UPR3______' range. The checks are stored in PR check history (UPR30100) and PR transaction history (UPR30300).

Most but not all of the details you need are in those two tables if you want to create PR transaction related reports. Link by employee ID.

Sweet!  Thank you!

Have a look at this post

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/05/finding-table-and-field-information-in-microsoft-dynamics-gp.aspx

You can also use the Support Debugging Tool's Resource Information and Resource Explorer windows:

http://blogs.msdn.com/developingfordynamicsgp/pages/support-debugging-tool.aspx

Also Victoria Yudin has lots of table info... just not payroll yet.

http://victoriayudin.com/gp-tables/

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David dot Musgrave at microsoft dot com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.