PeopleSoft Financials 8.9 Basic Query Training

1
PeopleSoft Financials 8.9Basic Query Training

• Janice Chancey
• Financial Information Systems and Reporting
• Controllers Division
• HS-B217
• 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)
AR Banner
Receiver (ePro)
Accounts Payable
Asset Management
General Ledger / Commitment Control
5
Typical Record Structure for PS Financials

• REQ and PO Records
• Header
• Line
• Ship / Schedule
• Distribution
• Voucher Record
• Header
• Line
• Distribution

• Journal Record
• Header
• Line

6
Example of Record / Field Structure
PO_HDR - (record)
PO_ID
PO_DT
PO_Status
Other demographic data..
I091234
04/01/2009
D
..
PO_LINE - (record)
PO_ID
Line_Nbr
Descr
Qty, Amount, other line info
I091234
1
Item1
..
I091234
2
Item2
..
PO_LINE_DISTRIB - (record)
PO_ID
Line_Nbr
Fund_Code
Other CFC values, etc
I091234
1
10000
..
I091234
2
10000
..
7
PS Financials Key Records Document

• Available on MCG PeopleSoft Website at
  http//www.mcg.edu/peoplesoft/documents/key_record
  s_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

8
Query Viewer for Basic Query
9
Query Viewer

• Allows a user to Run a Query to HTML or Excel,
  Schedule a Query and/or Add it to Query Favorites
• The navigation is
• Reporting Tools gt Query gt Query Viewer

10
Query Viewer

• Search on a Query by
• Query Name
• Uses Record Name
• Uses Field Name
• Public Query Naming Standards
• MCG_AP Accounts Payable Queries
• MCG_GL General Ledger/Journal Queries
• MCG_PO Purchasing Queries
• MCG_BPA Budget, Planning and Analysis Queries
• MCG_SPA Sponsored Accounting Queries

11
Query Viewer

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

12
Query Manager for Basic Query
13
Query Manager

• Allows a user to Edit a Query, Run a Query to
  HTML or Excel, and/or Schedule a Query
• The navigation is
• Reporting Tools gt Query gt Query Manager

14
Query Viewer

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

15
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 column head
• Show the XLAT translate values
• Change the Heading Text

16
Criteria Tab

• The Criteria Tab allows users to
• Create Criteria statements using mathematical and
  logical conditions
• Users can group criteria and reorder it

17
Prompts Tab

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

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

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

20
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

21
Good Business Practices for Running Queries

• Only join three or less records together before
  testing your query to run. If you need more
  records joined, only add one at a time and test
  running per each additional record.
• DO NOT run query for an entire year or more of
  data on the entire institutions data.
• Use department specific criteria
• Test running on one months worth of data, before
  many months.
• If you have questions, please seek help.
• Janice Chancey x-3288
• ITSERVICE x-4000

22
Good Business Practices for Running Queries
Contd

• If your query runs and times-out, by exiting you
  from PSFin 8.9, then your query could still be
  running on the database, and taking up most of
  the processor of the database server.
• If you get timed-out, check PeopleTools gt Process
  Scheduler gtProcess Monitor gt Server List tab
• If PSUNX server CPU () is over 85, then email
  ITSERVICE (with a copy to Janice). ITSS can
  cancel your query that is still running. Then I
  can help you troubleshoot the problem with your
  query.

23
QUESTIONS