1 / 25

PeopleSoft Financials Basic Query Training

PeopleSoft Financials Basic Query Training. Financial Information Systems and Reporting Finance Division /Controller’s Division. Agenda. What is a Query? PeopleSoft Financials Modules Query Viewer Query Manager Questions. What is a Query?. A query is a request for information from

glorias
Download Presentation

PeopleSoft Financials Basic Query Training

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related