PeopleSoft Financials Basic Query Training

1. PeopleSoft FinancialsBasic Query Training Financial Information Systems and Reporting Finance Division /Controller’s Division

2. Agenda • What is a Query? • PeopleSoft Financials Modules • Query Viewer • Query Manager • Questions

3. What is a Query? A query is a request for information from a database. A database is a structured collection of records or data that is stored in a computer system. Two types of “Queries” -Public (everyone) -Private (only you)

4. Relationships Among PS Financials Modules Projects (grants) - SPA Requisition (ePro) - EP ARB OneUSG Connect HCM Purchase Order – PO (Purchasing) Accounts Payable – AP Receipts – PO (Purchasing) Banner - BNR (Accounts Receivable) Asset Management – AM Journal Lines - GL / Commitment Control - KK

5. PS Financials Key Records Document • Available on Augusta University PeopleSoft Website > PeopleSoft Documentation > Query Documentation > Key Records in PeopleSoft Financials • https://www.augusta.edu/finance/peoplesoft/documents/key_records_in_psfin.doc • Lists key records for each module • Lists key (indexed) fields for each record • Lists exceptions and rules when using the record to make a query

6. Example of Record / Field Structure REQ_HDR - (record) REQ_LINE - (record) REQ_LN_DISTRIB- (record)

7. Query Viewer for Running Queries Query Manager for Running and Editing

8. Practice/Training Access https://core.aufplay.gafirst.usg.edu Log into Training PS Financials Environment User ID: TRAINxx (01 to 25) Password: WELC0ME1% 0 is a zero

9. Query Viewer • Allows a user to: • Run a Query to HTML or Excel • Schedule a Query • Add it to Query Favorites • The navigation is: Reporting Tools > Query > Query Viewer

10. Query Viewer – Search for a Query • Search on a Query by: • Query Name • Uses Record Name • Uses Field Name • Public Query Naming Standards • 120_AP – Accounts Payable Queries • 120_AM – Asset Management Queries • 120_GL – General Ledger/Journal Queries • 120_PO – Purchasing PO Queries • 120_REQ – Purchasing Requisition Queries • 120_BPA – Budget, Planning and Analysis Queries • 120_SPA – Sponsored Accounting Queries

11. Query Viewer – Add to Favorites • To add a query as a Favorite • Search on a Query • Click on the Favorite Hyperlink to the right of the Query name • Note: Once a query is a favorite, then it will come up when you open Query Viewer or Query Manager in a My Favorite Queries box

12. Query Viewer – To Run a Query • To Run a Query • Search on a Query • Click either the HTML or Excel hyperlink beside the query that you want to run. • If there are prompts, you will be prompted to enter your criteria before the query runs.

13. Recommended Public Queries to Run • 120_GL_JOURNAL_LIKE_CFC • This will list all expenses that have hit your CFC • 120_GL_JOURNAL_LIKE_CFC_VW • This will list all expenses that have hit your CFC with older history tables available. • 120_GL_JRNL_LIKE_CFC_REV_ONLY • This lists your revenue that was posted to your CFC, if it generates revenue. • 120_GL_BNR_JGEN_DEP_DETAIL • This lists your detailed lumped deposits from Banner.

14. Recommended Public Queries to Run – Cont’d • 120_SPA_ALL_YRS_ACCTDESC • This lists all activity for your sponsored Project ID. • 120_MCG0005_OPEN_ENC • This run a query that used to be the Open Encumbrance Report. It shows all open encumbrances for your Department ID. • 120_MCG0001_PG_STATUS_LIFE • This runs a query that used to be the Project Grant Status Life Basis report.

15. Query Viewer – Schedule a Query Schedule a Query: 1. Click on the Schedule hyperlink 2. Name the Run Control ID, and Add it 3. Write a description 4. Choose Run Date & Recurrence 5. Choose Type, Format, and Distribution 6. Click “OK”

16. Query Manager for Basic Query Fields Save as a Private Query Criteria Prompts

17. Query Manager – Edit a Query • Edit a Query • Search on a Query • Click the Edit hyperlink beside the query that you want to modify and save as private.

18. Edit a Query - Fields Tab • The Fields tab allows users to see the fields that will be output when the query is run. • At this tab, you can: • Change the order of the field output • Change the output column header • Show the XLAT translate values • Change to sort order

19. Button Reorder Fields Button to Change Sort Order Button to Edit Fields & Heading Text

20. Reorder / Sort This column is to setup the sort order This column is used to reorder the output columns To make the sort descending order

21. Edit a Query - Criteria Tab • The Criteria Tab allows users to: • Create Criteria statements using mathematical and logical conditions • Users can group criteria and reorder it • You must have criteria to pull your data; otherwise, you will be trying to run a query on the entire institution’s data

22. Button to Edit Criteria

23. Edit a Query - Save As A Private Query • To save a Query as a Private Query • Click on the Save As hyperlink at the bottom of the query tool • Rename the query to change the name slightly (can add your initials to beginning or end) • At the Owner drop-down box, select Private – this should be defaulted for most users

24. Good Business Practices for Running Queries – Cont’d • Start with a public query, and modify it to make a private query. Do not start from scratch. • DO NOT run query for an entire year or more of data on the entire institution’s data. • Use department specific criteria • Test running on one month’s worth of data, before many months.

25. QUESTIONS