Beginning PRF for Payroll Data

1
Beginning PRFfor Payroll Data

• Session 1

2
Objectives

• Learn how to access PRF
• How to navigate and list queries and forms
• How to add a query of our own

3
Accessing PRF in CICS

• On the Nevada Screen enter A for User
  Applications
• Enter your user id and password to log onto CICS
• Enter PRF (Platinum Report Facility) at the CICS
  Primary Option menu and press Enter
• ----------------------- CICS/MVS PRIMARY OPTION
  MENU -------------------------
• OPTION gt prf
  
• 
  USERID - PJO
• 1 STUDENT APPLICATIONS
  DATE - 10/09/02
• 2 PERSONNEL APPLICATIONS
  TERMID - 0070
• 3 FINANCIAL APPLICATIONS
  APPLID - DBDCCICS
• 4 PURCHASING/INVENTORY APPLICATIONS
  
• 5 COOPERATIVE OCCUPATIONAL EDUCATION (COE)
  
• 6 MANAGEMENT INFORMATION SERVICES
  APPLICATIONS
• 7 FACILITIES APPLICATIONS
  
• 8 CCSD ELECTRONIC MAIL SYSTEM (SYSM)
  
• 9 SCHOOL/FACILITY PROFILE APPLICATIONS
• At the next screen enter 1 for PRF and press
  Enter

4
Three PRF Object Types

• Query a statement sent to DB2 asking it to
  retrieve data. You define the data you need on
  the PRF Build Query screen, and it is translated
  into an SQL (Structured Query Language) SELECT
  statement.
• Form a format for how you want the results of
  your query presented to you. You define the form
  on the Build screen for the type of form you
  want, such as Column form, Letter, Mailing
  Labels, or Extract File. Every form must have an
  underlying query.
• Procedure a series of commands to run queries
  and/or forms, combined to complete an entire task.

5
Listing Queries, Forms, and Procedures

• Enter L under OPT on the appropriate line.
• If you leave a blank under NAME, PRF will show a
  list of the queries, forms or procedures
  associated with your user ID on the Library
  Screen. If you dont have any, you can enter the
  user ID of a colleague who uses PRF, followed by
  ., or you can enter an asterisk and press enter
  to see all the PRFs that are designated as
  Shared. Press Enter.
• PRFM.01 R04.01 -------- PLATINUM REPORT
  FACILITY -------- 09/30 1353
• COMMANDgt
  
• 
  
• 
  
• OPT NAME
  
• l QUERY ...................... pjo.
  
• FORM .......................
  
• PROCEDURE ..................
  
• 
  
• New object name for C/N options...
  
• 
  
• 
  DB2 subsystem DSN
• --------------------------------------------------
  ----------------------------
• Enter one of the following options
  
• A - Add B - Batch C -
  Copy D - Delete
• E - Edit F - Freeform L -
  Library N - reName

6
Library Screen

• QYLB.02 R04.01 --------------- QUERY LIBRARY
  --------------- 11/12 1135
• COMMANDgt
  USER ID SYSAD2
• PRF0003I Query saved
  
• NAMEgt PAY CREATORgt SYSAD2
  TYPEgt SELgt N
• 
  
• Options-gt S -Select R -Run D -Delete U
  -Undelete B -Batch I -Information
• QUERY NAME SHR AREA
  DESCR
• SYSAD2.PAYDEMO_GROUP Y
  NUMBER OF EMPLOYEES NEEDING X-R
• SYSAD2.PAYDEMO_JOBS Y
  ...............................
• SYSAD2.PAYDEMO_JOBS2 Y
  ...............................
• SYSAD2.PAYDEMO_JOIN Y DEMO
  FOR USE OF JOIN IN PAYROLL
• SYSAD2.PAYDEMO1 Y HIRE
  DATE AND XRAY DUE DATE FOR
• (END OF LIBRARY)
• Creator is the user ID of the person who owns the
  PRF
• Name can be used to select a particular query or
  queries (such as PAY, above. Note the asterisk
  by itself is a wild card, but with part of the
  name, you need to use the SQL wild cards, _ or
  .)
• For Forms, Type selects a particular type, such
  as Labels.
• SHR designates an object as available for others
  to use. When SHR is U others can update the
  object too. When N, only the creator can run or
  update
• To see more information, on the Library Screen,
  press F10. You will see the dates created and
  modified and by whom, and whether parms are
  required at run time.

7
Using Commands on the Library Screen

• SHR designates an object as available for others
  to use. When SHR is U others can run and update
  the object, when Y, others can run but not
  update. When N, only the creator can run or
  update.
• Description is optional, but recommended
• To see more information, on the Library Screen,
  press F10. You will see the dates created and
  modified and by whom, and whether parameters are
  required at run time.
• To Select an object for edit, enter S.
• To Run, enter R to run interactively, or B to set
  up a batch job.
• To Delete or Undelete, enter the appropriate
  letter under OPT next to the desired object.
  (Warning about Undelete it only works while you
  are still on the Library Screen and have just
  Deleted the object. Once you leave the screen,
  the object is gone)
• To view Information about the object, enter I.

8
Getting Help in PRF

• Press F1 for help anywhere in PRF. Place the
  cursor on the entry field for which you need
  help.
• Follow directions on help screens to drill into
  specific topics or areas of the screen.
• Press F1 twice for menu of help topics.
• Press F12 for list of function keys active on the
  current screen.

9
Viewing Comments for an Object

• To see or enter comments for an object, enter I
  (Info) next to the desired object on the library
  screen. A numbered list of comment lines
  appears, with the instructions to enter EDIT on
  the command line to make changes. Comments can
  be entered here and saved with your PRF object.
• INFO 03 R04.01 ------------ PRF Object
  Information ------------ 11/06 1347
• COMMANDgt
  
• PRF1155I Use the EDIT command for updates.
  
• Object SYSAD2.PAYDEMO1 Type
  QERY Last Update 2002-11-06
• 1. List of employees for a particular work
  location, their
• 2. original hire dates, and date their X-rays
  are due.
• 3.
  
• 4.

10
Running a Query or Form

• To run an object, enter R next to the desired
  object on the library screen. The Run Query or
  Run Report screen is displayed.
• Set row limit if you want just a sample of the
  report
• Change replaceable parameters if necessary
• If a query has replaceable parameters, a question
  appears asking if you want to change them. The
  query below doesnt have replaceable parameters.
• Press Enter to run the report
• RUNQ.03 R04.01 ------------------ RUN QUERY
  ----------------- 11/06 1348
• COMMANDgt
  SYSAD2.PAYDEMO1
• 
  
• 
  
  
  
• ROW LIMIT NONE
  
• 
  
• USING FORM ..... DEFAULT FORM
  
• 
  
• Forms that can be used by this Query NO FORMS
  BASED ON THIS QUERY

11
Lab Navigating in PRF

• Sign onto CICS and initiate PRF
• List your queries or those of someone who has
  some, if you do not. (Try user PJO or SYSAD2)
• Copy query SYSAD2.PAYDEMO2 to yourID.somename
• Add a comment in the Object Information screen so
  you remember where you got it
• Look at the help screen for the PRF Query Library
  screen and the menu of help topics
• Run the new query you just created by copying
  PAYDEMO2. Use a row limit of 20.

12
Adding a New Query

• To create a new query, enter A(dd) on the
  Platinum Report Facility screen under OPT next to
  QUERY
• Enter a name for the query under NAME
• Pressing enter brings you to the Select Tables
  screen
• PRFM.01 R04.01 -------- PLATINUM REPORT
  FACILITY -------- 09/30 1617
• COMMANDgt
  
• 
  
• 
  
• OPT NAME
  
• a QUERY ......................
  paydemo1
• FORM .......................
  
• PROCEDURE ..................
  
• 
  
• New object name for C/N options...
  

13
Selecting a Table

• SLTB.03 R04.01 ---------------- SELECT TABLES
  --------------- 11/06 1226
• COMMANDgt
  SYSAD2.PAYDEMO1
• PRF1082I Press ltEntergt to initiate the table
  search.
• NAMEgt IDgt MSPAYDB1
  LOCgt LOCAL SELgt N
• TYPEgt
  
• - 1 (V) MSPAYDB1.FEDPGM_ID LABEL
  VIEW OF MSPAY_ID FOR FED PRGS
• 2 (V) MSPAYDB1.FEDPGM_JOB LABEL
  VIEW OF MSPAY_JOB FOR FED PRGS
• 3 (T) MSPAYDB1.MSPAY_ACCUMULATOR LABEL
  WAGE STATs (CURR,MTD,QTR,YTD )
• 4 (T) MSPAYDB1.MSPAY_ATTACHMENTS LABEL
  IRS OR COURT ORDER attachments
• 5 (T) MSPAYDB1.MSPAY_DEDUCTIONS LABEL
  EMPLOYEE DEDUCTION DATA
• Enter the database name for the tables you need
  next to ID. For example MSPAYDB1 for the Payroll
  tables. Press enter and you will see a list of
  tables in that database.
• The NAME field may be used to refine your search
  through a long list of tables. It defaults to ,
  for all the tables. You may enter part of a
  table name and the _ or sign, as used in SQL.
• The FIND command may be used on the command line
  to further refine your search, or enter Y in the
  SEL field to do a more advanced search.
• When you have found the table you need, enter S
  and press enter to select it. The BUILD QUERY
  screen will appear.

14
Select Columns for the Query

• BLDQ.03 R04.01 ---------------- BUILD QUERY
  ----------------- 11/14 1555
• COMMANDgt
  SYSAD2.PAYDEMO1
• PRF0238W Use extended "WHERE" screens for long
  text strings
• Distinct N Shrink N Location LOCAL
  
• SEL MSPAYDB1.MSPAY_ID ORD GRP FUNC
  WHERE ..... using AND
• 25. XRAY_CODE ............ C
  
• 2 26. XRAY_DUE_MM .......... C 1
  
• 3 27. XRAY_DUE_CCYY ........ C
  '2003'
• 28. MEDX_CODE ............ C
  
• 29. MEDX_DUE_MM .......... C
  
• 30. MEDX_DUE_CCYY ........ C
  
• 4 31. ORIG_HIRE_DATE ....... D
  
• 32. ADJ_HIRE_CCYYMMDD .... C
  
• 33. TLOA_CODE ............ C
  ' ' OR TLOA_DATE gt CURREN
• 34. TLOA_DATE ............ D
  
• 35. LOA_RET_CODE ......... C
  
• 36. LOA_RET_DATE ......... D
  
• --------------------------------------------------
  ----------------------------
• SELECT FNAME, XRAY_DUE_MM, XRAY_DUE_CCYY,
  ORIG_HIRE_DATE,

15
Define a WHERE Clause

• BLDQ.03 R04.01 ---------------- BUILD QUERY
  ----------------- 11/06 1410
• COMMANDgt
  SYSAD2.PAYDEMO1
• 
  
• Distinct N Shrink N Location LOCAL
  
• SEL MSPAYDB1.MSPAY_ID ORD GRP FUNC
  WHERE ..... using AND
• 37. PRI_JOB_NUM .......... C
  
• 5 38. PRI_WORK_LOC ......... C
  041'
• 39. PRI_VAR_LOC .......... C
  
• 40. PRI_EMP_GROUP ........ C
  
• 41. PRI_EMP_CATEGORY ..... C
  
• 42. PRI_MONTHS_WORK ...... N
  
• 43. PRI_JOB_POS_CODE ..... C
  
• 44. SENIORITY_CCYYMMDD.... C
  
• 45. NUM_OF_JOBS .......... N
  
• 46. COHORT_CODE .......... C
  
• 47. DUAL_EMP_IND ......... C
  
• 48. IRS_RESTRICTED_IND.... C
  
• --------------------------------------------------
  ----------------------------
• SELECT FNAME, XRAY_DUE_MM, XRAY_DUE_CCYY,
  ORIG_HIRE_DATE,

16
Some Query Hints

• Change using AND to using OR as needed
• Use the ORD column to indicate sort order
• Enter WHERE on the command line for more room in
  the WHERE area. If there is still not enough
  room, place the cursor on the WHERE clause you
  need to enter and press F1. The Where Condition
  screen is displayed.
• To remove a table from your query, enter DETACH
  on the command line, followed by the letter
  designation of the table you want to remove. To
  add a different one, use ATTACH to get back to
  the table selection screen.
• If you have a very complicated query or
  something PRF cant handle, such as a UNION,
  enter EDIT QUERY on the command line for a
  freeform area to type your query manually.

17
Test the Query

• Enter RUN on the command line
• The RUN QUERY screen appears, offering a choice
  of ROW LIMIT and FORM. Since we havent created
  a form for our query yet, our only choice is the
  DEFAULT FORM, which displays the data just as it
  is returned from DB2.
• Use ROW LIMIT as a means of testing the query on
  just a few rows, without getting back all the
  results.
• Press Enter to run the query.
• RUNQ.03 R04.01 ------------------ RUN QUERY
  ----------------- 11/06 1135
• COMMANDgt
  SYSAD2.PAYDEMO1
• 
  
• 
  
  
  
• ROW LIMIT 30
  
• 
  
• USING FORM ..... DEFAULT FORM
  
• 
  
• Forms that can be used by this Query NO FORMS
  BASED ON THIS QUERY

18
Add a Description

• Enter PROFILE on the command line to access the
  Query Profile screen
• Description is very handy once you accumulate
  several queries and forms
• OPRO.04 R04.01 -------------- QUERY PROFILE
  -------------- 11/06 1424
• COMMANDgt
  SYSAD2.PAYDEMO1
• 
  
• 
  
• DESCRgt HIRE DATE AND XRAY DUE DATE FOR
  WORK LOCATION
• 
  
• SHAREgt Y
  
• "Y" - others can use - but not update - your
  QUERY
• "N" - your QUERY cannot be referenced or
  copied by others
• "U" - others can use - and update - your
  QUERY

19
Using Groups and Functions

• Use the GROUP BY clause in SQL to select summary
  data instead of a row for every detail line
• For example what if we want to find the number
  of employees who need x-rays each month? Instead
  of printing a line for every employee, we print
  one line per month showing the total number of
  employees in that group.
• Use Functions to perform operations on grouped
  data
• For example we will use the count function.
  Examples of other functions are max, min, avg,
  and sum.

20
Using Groups and Functions

• BLDQ.02 R04.01 ---------------- BUILD QUERY
  ----------------- 11/12 1119
• COMMANDgt
  SYSAD2.PAYDEMO_GROUP
• 
  
• Distinct N Shrink N Location LOCAL
  
• SEL MSPAYDB1.MSPAY_ID ORD GRP FUNC
  WHERE ..... using AND
• 3 1. SOC_SEC_NUM .......... C COUNT
  
• 2. EMPLOYEE_NAME ........ C
  
• 3. FNAME ................ C
  
• 4. LNAME ................ C
• ---------
• ---------
• 25. XRAY_CODE ............ C
  
• 1 26. XRAY_DUE_MM .......... C 2
  
• 2 27. XRAY_DUE_CCYY ........ C 1
  '2003'
• 28. MEDX_CODE ............ C
  
• 29. MEDX_DUE_MM .......... C
• ---------
• ---------

21
Lab Building a Query

• Build a query called SENIOR_EMPLOYEES to list
  active employees in job location 091 whose
  hiredate is earlier than 01/01/1982.
• List each employees first name and hiredate
• Order by hiredate
• Place the cursor in an entry field under the ORD
  column and press F1 for help to sort the
  hiredates in descending order instead of
  ascending.
• Optional Build a query to select from a single
  payroll table information you might use in your
  daily tasks.

22
Summary

• So far, we have learned to navigate in PRF and
  create a simple query.
• Context-sensitive help is available by pressing
  F1 with the cursor on the entry field of
  interest.
• Be aware of the amounts of data in your tables
  and be sure you have defined a WHERE clause to
  limit the data you will select.
• In the next sessions we will learn about the
  Payroll data tables and create a form to enhance
  the appearance of our results.