Desktop Auditing In PeopleSoft

1
Desktop Auditing In PeopleSoft

• Session 443
• Monday 115 pm 215 pm
• HEUG 2003 Conference - Dallas

2
W. Brad Hamilton, CPA Senior Manager City of
Tallahassee
3
W. Brad Hamilton is a Senior Manager with
responsibilities over PeopleSoft Reporting and
Security Administration. The City is live with
PeopleSoft HRMS 8.3, PeopleSoft Education
Government Financials 7.51 and PeopleSoft
Customer Information System 7.51.
4
Agenda

• What is SQL ?
• PS Query
• Securing The PS Query Tool
• Queries - Compensating Control
• Queries Auditing Security
• Top Five Tips To A Good Design

5
Who you are?

• Security Administrators

• Internal/External Auditors

• Other PeopleSoft Team Members

• Who cares
• I just want to see Dallas!!!

6
How many using the PS Query Tool?
Who you are?
7
Definitions

• PeopleSoft
  Typical DB

Record
Table
Field
Field/Column
Row
Record
8
What is SQL?
9
What Is SQL ?

• Definition
• Structured Query Language Is the standard
  database language for communicating with
  relational databases. Each DB platform does have
  its isms in SQL.
• Oracle
• Microsoft SQL Server
• DB2
• Informix
• Sybase
• PeopleSoft Applications Is an extensive SQL
  generator

10
What Is SQL ?

• Five main parts in a PS Query SQL statement
• Select - fields
• From - tables
• Where filtering criteria
• Group By sum, cnt, avg, max, min
• Order By sort order

11
What Is SQL ?

• Examples
• EMPLOYEES Table Current Employees
• HEALTH_BENEFIT Table EE Health Benefit Election
  
• RTRMNT_PLAN Table Retirement Benefit Enrollment
  Information

12
PS Query
13
PS Query

• Defined
• PS Query is a graphical user interface for
  querying a database. This tool allows you to
  create simple and complex queries.
• PS Query has read only privileges
• Results
• Viewed in a grid control, Excel, PS/nVision or
  Crystal Reports

14
PS Query

• Types of SQL Statements
• Simple Query
• Inner Join
• Subquery
• Union
• Outer Join

15
Inner Join (3,000 rows)

• EMPLOYEES HEALTH_BENEFIT_med
  only
• 5,000
  Active Rows not EEs
• 3,000
  Active Rows - EEs
• 12,000
  Inactive Rows
• 10,000 Total 20,000 Total

16
Subquery - does not exist (7,000 rows)

• EMPLOYEES HEALTH_BENEFIT_med
  only
• 5,000
  Active Rows not EEs
• 3,000
  Active Rows - EEs
• 12,000
  Inactive Rows
• 10,000 Total 20,000 Total

17
Union ( 3,000 7,000 Rows )

• 3,000 - Inner Join (SQL Select 1)
• 7,000 - Does Not Exist SubQuery (SQL Select 2)
• __________________________
• Union Results Set 10,000 rows

18
Outer Join ( 3,000 7,000 Rows )

• One select statement will return 10,000 rows.

19
Cartesian Join (200,000,000 rows)

• EMPLOYEES HEALTH_BENEFIT med only
• 10,000 20,000
• 10,000 x 20,000 200,000,000

20
PS Query

• An extremely powerful tool that enables and
  empowers users to do their job more efficiently
  and effectively.

21
PS Query

• Users include
• Functional Leads
• Technical Leads
• Project Mangers
• System Administrators
• Security Administrators
• Auditors

22
Securing PS Query
23
Securing The PS Query Tool

• Six areas to consider
• Menu PeopleTools Query
• Query Profile
• Access Groups
• Query Security Record (Row level)
• SQL Views (Columns)
• Query Access List Cache - Process

24
Compensating Control Queries
25
Compensating Controls

• Definition
• A compensating control query is a query that
  is used to ensure the integrity of transactions
  in a PeopleSoft Application.

26
When do you run a compensating control
query?

• Input
• Processing
• Output

27
Payroll Compensating Queries

• PAYROLL_CC_40HR_WRONG_FLEXBUCKS Flexbucks s/b
  92 or 93 (no f)
• PAYROLL_CC_53HR_WRONG_FLEXBUCKS - Flexbucks s/b
  92 or 93
• PAYROLL_CC_ADDLPAY_CHECK_BOXES 2ND AND 3RD
  boxes not checked
• PAYROLL_CC_ADDL_WOUT_REG_OR - verify EE-
  w/AddlPay no RegLve
• PAYROLL_CC_ADDTL_PAY_WRONG - Code
  table driven Blank amt.
• PAYROLL_CC_CHECK_DATE_WRONG - check
  date on payline is wrong
• PAYROLL_CC_DD_NETPAY_EXCESSBOX check excess
  pay for NETPAY
• PAYROLL_CC_ DD_NETPAY_PRIORITY4 Set
  Priority to 4 for NETPAY
• PAYROLL_CC_GENL_DED_WRONG - EE Ded
  Calc routine wrong.
• PAYROLL_CC_MISSING_FLEXBUCKS - needs
  enrolling in Flexbucks
• PAYROLL_CC_MISSING_SRMGMT_BENE - needs Senior
  Management benefit
• PAYROLL_CC_MORE_THAN_40REG_HOURS gt40 reg hrs.
  s/b moved to 67
• PAYROLL_CC_OFFCYC_WRONG_SUBS. subset wrong
  for offcycle chek

28
Payroll Compensating Queries

• PAYROLL_CC_PAYCHANGE_FOR_WEEK Make change
  week 1 hrly rate
• PAYROLL_CC_PEN_MAP_NOT_TAKEN - Check to see
  if s/b comiung out
• PAYROLL_CC_PERCENT_TAX_WRONG - Payout or
  Penref tax wrong
• PAYROLL_CC_REG_NOT80_WCD67 - WEEK2
  Reg Hrslt8067 excl TA CC
• PAYROLL_CC_ REG_NOT80_WCD67_1 - WEEK2 Reg
  Hrslt8067
• PAYROLL_CC_REG_TEMP_FLAG_WRONG - Get HR or Rtmt
  to fix JOB data
• PAYROLL_CC_STATE_TAX_CORRECTIN - State tax needs
  to fix not in Fl
• PAYROLL_CC_TEMPS_NOT_IN_TEMP - contact HR
  to fix JOB data
• PAYROLL_CC_VERIFY_CODE_67 - Code
  67 is lt 39 hours
• PAYROLL_CC_WRONG_SUBSET_FOR_COMPANY - Subset is
  wrong for company
• PAYROLL_CC_ZERO_NET_PAY -
  Payroll 0 net pay

29
HR Compensating Queries

• Query Name Description
• BEN_CC__BENEFIT_PRGM_CHGS
  Prompts for "Eff Date After
• BEN_CC__CLIFE_WITHOUT_EMP_LIFE
• BEN_CC__CLIFE_WITHOUT_EMP_LIFE 2-pty ltgt1
  dependnt
• BEN_CC__DEPENDENT_PROBLEMS2 Enroll --
  3-pty lt 2 dependents
• BEN_CC__DEPENDENT_PROBLEMS3 2-Party or
  Fam w 0 dependents
• BEN_CC__DEPENDENT_PROBLEMS4
  Disenroll--Ind w dependents
• BEN_CC__EE_CASH_ACCUM_WO_EUL Has 341 Gen
  Ded w/o EmpUnivLif
• BEN_CC__ELECT_TERM_SAME_DAY Cvg Elect
  Term With Same Day
• BEN_CC__EMP_UNIV_W_CLIFE_PLAN Should be
  CLIF-U instead
• BEN_CC__LIFE_MISSING_SPOUSE
  SpouseLife w No Spouse Info
• BEN_CC__OPTOUT_FB_IN_ERROR
• BEN_CC__SP_CASH_ACCUM_WO_SUL Has 342 Gen
  Ded w/o SpoUnivLif
• BEN_CC__TERM_EES_W_FSAS
  Must terminate coverage
• BEN_CC__TERM_EES_W_HEALTHPLANS terminate
  enrollments?
• BEN_CC__TERM_EES_W_HEALTH_INS Must
  terminate coverage
• BEN_CC__TERM_EES_W_LIFE
  Must Terminate enrollments?
• HR_CC__CHECK_ORIG_HIRE_DATE
  OriginalHire Dt after Hire Dt\
• HR_CC__CK_HIRE_ACTIONS
  Ck Hires entered in date range
  

30
HR Compensating Queries

• Query Name Description
• HR_CC__CK_PROMOTION_DEM_PAY Jan - Check
  Promotion,Dem,Pay
• HR_CC__FIRE40_WRONG_PAYGROUP
  Fire40hrs-paygroupltgtU40 or N40
• HR_CC__FIRE53_WRONG_PAYGROUP
  Fire53hrs-paygroupltgtU53 or N53
• HR_CC__FIR_HOURLY_RATE
  Hourly Rt correct for StdHrs?
• HR_CC__JOB_SALGRADE_ERROR Job Sal
  GradeltgtJobCode SalGrad
• HR_CC__JOB_SALGRADE_ERRORS JOB
  SalGradeltgtJOBCODE SalGrade
• HR_CC__LEAVE_ENROLLMENT_ERROR Cvg Elect "E"
  with no Ben Plan
• HR_CC__LEAVE_ENROLLMENT_ERROR1 PartTime Emp w FT
  Leave
• HR_CC__LEAVE_ENROLLMENT_ERROR2 FNU-WrongLeave
  Based on StdHrs
• HR_CC__LEAVE_ENROLLMENT_ERROR3 Full-Time Emp w
  PT Leave
• HR_CC__MISSING_BAD_COUNTY County
  missing/misspelled?
• HR_CC__MISSING_CATASTROP_LEAVE rev12042000
• HR_CC__MISSING_PERSONAL_LEAVE rev12042000
• HR_CC__MISSING_SICK_LEAVE
  rev12042000
• HR_CC__REG_EMP_IN_TMP_PAYGROU
• HR_CC__RETIREE_BEN_PROG_ERROR Ret EEs not in
  RET Ben Prog
• HR_CC__TEMP_NOT_IN_TMP_PAYGROP
• HR_CC__UNMTCH_BENPROG_PAYGRP_1 BenProg ltgt
  Paygroup Group 1

31
Queries Auditing Security
32
Auditing and Security Queries

• Queries that are used to ensure that
  operators/users have the appropriate access in
  the PeopleSoft Application

33
How often do you run these queries ?

• Depends on your organization
• Number of Users
• Turnover
• Remember several PSTools tables are not effective
  dated no history
•  

34

How do you track of your Users?
35
(No Transcript)
36
(No Transcript)
37
(No Transcript)
38
How do you track your Roles ?
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
How do you track your Permission List?
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
How do you track your Users and assignment of
Roles?
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
How do you track your Roles and assignment of
Permission List?
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
How do you track your Users and their Roles
along with Permission Lists ?
55
(No Transcript)
56
(No Transcript)
57
(No Transcript)
58
How do you know when a User has terminated?
59
(No Transcript)
60
(No Transcript)
61
(No Transcript)
62
(No Transcript)
63
(No Transcript)
64
Are you using Field or Record Level Auditing?
65
(No Transcript)
66
(No Transcript)
67
(No Transcript)
68
What reports and processes have Users run?
69
(No Transcript)
70
(No Transcript)
71
(No Transcript)
72
How do you track those users that have
correction? (Authorized actions in PSAUTHITEM
Table)
(Refer to handout)
73
(No Transcript)
74
(No Transcript)
75
(No Transcript)
76
PS Query is an extremely valuable tool for
Auditors!!!
77
Here are five tips to good query design.
78
Top 5 Tips to Good Design

• Know your Data

• Start Small

• Build in Stages

• Good Joins (no cartesian joins)

• Verify, verify, verify!!!

79
Summary

• What is SQL ?
• PS Query
• Securing The PS Query Tool
• Queries - Compensating Control
• Queries Auditing and Security
• Top Five Tips To A Good Design

80

• Questions and Answers

81
W. Brad Hamilton, CPA [phone] Hamilton_at_talg
ov.com HTTP//HEUG.ORG (attendees may download
HEUG2003 presentations from the archives at this
location)