PeopleSoft Financials Basic Query Training

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

2. Agenda • What is a Query? • Query Viewer • Query Manager • Good Practices for Running Queries

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

4. Relationships Among PS Financials Modules Projects (grants) Requisition (ePro) ARB PeopleSoft HCM Purchase Order (Purchasing) Receipts (Purchasing) Accounts Payable Banner (Accounts Receivable) Asset Management Journal Lines / General Ledger / Commitment Control

5. PS Financials Key Records Document • Available on Augusta University PeopleSoft Website > PeopleSoft Documentation > Query Documentation > Key Records in PeopleSoft Financials 8.9 • 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 PO_HDR - (record) PO_LINE - (record) PO_LINE_SHIP - (record)

7. Query Viewer for Basic Query

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

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

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

11. Query Viewer – Schedule a Query • Schedule a Query • Search on a Query • Click on the Schedule hyperlink • Name the Run Control ID, and Add it • Write a description • Choose Run Date & Recurrence • Choose Type, Format and Distribution • Click OK

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

13. Query Manager for Basic Query Fields Add a Record Save as a Private Query Criteria Prompts

14. Query Manager • Allows a user to: • Run a Query to HTML or Excel (use hyperlinks) • Schedule a Query (use hyperlinks) • Copy a Private Query to Another User (use action box) • Edit a Query (use hyperlinks) • The navigation is: • Reporting Tools > Query > Query Manager

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

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

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

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

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

20. Button to Edit Criteria

21. Edit a Query - Prompts Tab • The Prompts Tab allows users to create prompts for users to type in run-time criteria. • Most prompts can be built on the criteria tab while building the criteria statement, EXCEPT for criteria statements using the condition of BETWEEN.

22. Button to Edit Prompt

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 • If your query runs and times-out, by exiting you from PS Financials, then your query could still be running on the database, and taking up most of the processor of the database server. • Contact PS_FINANCIALS@augusta.edu, so that we can have IT cancel your query that is still running.

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

26. QUESTIONS