PAYROLL - Data Model or Descriptive Table Names

[mfstutz]

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

 

[mfstutz]

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

 

[mfstutz]

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

 

[triad66]

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.

 

[mfstutz]

Sweet! Thank you!

 

[winthropdc]

Have a look at this post

http://blogs.msdn.com/developingfor...eld-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

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