PeopleSoft Financials 8.9 Basic Query Training

1. PeopleSoft Financials 8.9Basic Query Training Janice Chancey Financial Information Systems and Reporting Controller’s Division Annex I – B221 706-721-3288

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 HRMS Purchase Order (ePro) Receiver (ePro) Accounts Payable AR Banner Asset Management General Ledger / Commitment Control

5. PS Financials Key Records Document • Available on GRU 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 • MCG_AP – Accounts Payable Queries • MCG_AM – Asset Management Queries • MCG_GL – General Ledger/Journal Queries • MCG_PO – Purchasing PO Queries • MCG_REQ – Purchasing Requisition Queries • MCG_BPA – Budget, Planning and Analysis Queries • MCG_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 PSUNX server • 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) • Schedule a Query (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. Button Reorder Fields Button to Change Sort Order Button to Edit Fields & Heading Text

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

19. Button to Edit Criteria

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

21. Button to Edit Prompt

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

23. 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@gru.edu, so that we can have IT cancel your query that is still running.

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

26. More Advanced Edit Query – How to Add a Record • To add another record to a query, you need to Click on the Records Tab: • Search for the Record to Use • Click on Join Record • Click on the Record to Join the Record to • Accept the Auto-join criteria • Then the fields can be added to the query from the new record.

27. More Advanced Edit Query - Joining Records • SQL JOINs are used to query data from two or more records, based on a relationship between certain columns in these records. • Use a JOIN with joining most records for your queries. Accept auto-join criteria in most instances. • JRNL_HEADER.JOURNAL_ID = JRNL_LN.JOURNAL_ID.