Payroll data for queries

I am a new PeopleSoft user and am working with the delivered query tool.  Is there a document that details which fields/data are on what tables?  I'm looking to capture pay check detail by employee including:  earnings code, hours, rate, differential, overtime, wage, and associated job code. Thanks.

Nicole & Sue,

Thank you very much.  All of this information is terrific!

Hi - my approach has been sort of ad hoc as follows:

1. When on a page that has data you're interested in, for example paycheck earnings, click CTRL SHIFT J

2. Doing so should give you the page name

3. Look up the page name in Application Designer; read-only access should be sufficient for this exercise. If you don't have Application Designer read-only access, I'd ask for it. It comes in handy sometimes!

4. Right-click on the field you're interested in, and click View Definition. That will tell you the record/field name associated with that field on the page.

5. If you get subpages on the page and not individual field objects, then you will have to repeat steps 1-4 above using the subpage name in step 1.

Happy to show this at a future Payroll PUG roundtable for further clarification if anyone wants.

Thanks!

I put together a very basic document with navigations and the names of the major payroll records if that helps:

https://drive.google.com/file/d/1e0M97EF5iYsGCDWerU20THAslxzfA8Xf/view?usp=sharing

(This is from a while ago – I welcome any feedback!)

Hi Thomas.  Thank you so much for taking the time to provide such a detailed response.  This is a great start.

No there is no single source of detail on where you will find the data to create queries. I will answer what I think can get you started at the end, but I am going to explain how I have set up support for our functional staff to assist with your kinds of questions without having to wait on a technical resource all the time. What you can do is work with a technical resource to determine the hierarchy of tables in order to figure out how to get what you need. Most people learn where the data resides through use and having a technical resource assist by looking up the relationships through app designer. There are a lot of ways to tackle understanding your data sources, but I have found the best first resource is with the PeopleSoft Entity Relationship Diagrams using Document 1559851.1 located here:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1559851.1.

This is a project that loads a special navigation and ERDs, so you can follow the navigation and get the appropriate ERD mirroring the component you are on. It can be loaded into any system, like DMO, DEV, TST, etc, and that way functional users can examine them at any time. You would have to ask your technical support team to apply this project but once done it is insanely helpful. I have uploaded an example image here from our 9.0 environment. This shows the hierarchy of pay check related tables as such:

https://i.imgur.com/eZNP3yJ.jpg

1. PAY_CHECK

   1. PAY_DEDUCTION

   2. PAY_DISTRIBUTN

   3. PAY_GARNISH

   4. PAY_EARNINGS

      1. PAY_OTH_EARNS

   5. PAY_INS_EARNS

   6. PAY_SPCL_EARNS

   7. PAY_TAX

   8. PAY_TAX_1042

   9. PAY_TAX_CAN

This shows the hierarchy of Pay Line related tables as such:

1. PAY_CALENDAR

   1. PAY_PAGE

      1. PAY_LINE

         1. PAY_EARNINGS

            1. PAY_OTH_EARNS

            2. PAY_ONE_TIME

            3. PAY_CTX_OVRD

            4. PAY_TAX_OVRD

            5. PAY_GARN_OVRD

And by looking at the 2 relationship diagrams, we can see we can find related data by joining PAY_CHECK ande PAY_LINE where appropriate. On or more of these tables and joins may be required to find the information you need. I provide this as help in all areas not just in Payroll.

Now to your specific question, first be very careful with payroll tables in queries as providing public access with unrestricted controls can give people access to PII including address, social security number, etc.

1. Now the PS_PAY_CHECK has your company, paygroup, pay detail, employee id, name, and check totals, and is usually where payroll queries begin.

2. You can join that onto PS_PAY_LINE to get DEPTID and benefit program detail

3. You can join that onto PS_PAY_EARNINGS to get salary and hourly earnings details as well as details on department, jobcode, and position under which is was earned. This can include shift rates, hourly rates, flsa rates, etc.

4. You can join that onto PS_PAY_OTH_EARNS to get detail on multiple components of pay

I hope this will help you to get up to speed a bit faster.