PeopleSoft and The New Auditor

1
PeopleSoft and The New Auditor

• Session 428
• March 18, 2003 115 PM
• HEUG 2003 Conference - Dallas

2
Session Presenter

• Mark Carter
• Assistant Director, IT Audit
• Baylor University

3
Background

• Mark Carter is the Assistant Director of Internal
  Audit for Baylor University. Mark was hired to
  perform information systems audits. With over
  fifteen years experience auditing information
  systems, Mark never audited a PeopleSoft
  installation before coming to Baylor. Since May
  2002, Mark has found out what it is like to be a
  new auditor in a PeopleSoft environment.
• Mark is a Certified Information Systems Auditor
  and a Certified Internal Auditor. Mark has a MBA
  in Business Computer Information Systems and a
  BBA in Accounting
• Baylor University is running PeopleSoft
  Financials, Version 7.5

4
Synopsis of Presentation

• Synopsis of PresentationFind out what sort of
  learning process an auditor may go through after
  they are hired and given the assignment to audit
  in a PeopleSoft environment.

5
Agenda

• Plug In
• Pick the low hanging fruit
• Partner with the external auditor
• Peruse the data
• Plan for training
• Perform an audit
• Perfect the process

6
Plug In
What audits are currently being performed by your
Department that use PeopleSoft?

• Accounts Payable
• Duplicate Payments
• Use PeopleSoft Query to obtain data
• Use ACL to analyze data

7
Go/PeopleTools/Query
8
Criteria Scope
9
SQL Statements
10
Results Tab
11
Run to Excel Enter Dates
12
Resulting Excel Worksheet
13
ACL Input File Definition

• FILE_NAME 'AP Jun_02 Checks.fil'
• LASTVIEW "Default_View"
• SERVER 1 LOCAL
• RECORD_LENGTH 279 SKIP 0
• Bank ASCII 1 3
• Date DATE 4 10 PICTURE
  "YYYY-MM-DD" WIDTH 16
• Reference ASCII 17 9 WIDTH 12
• Name ASCII 26 50
• Amount NUMERIC 76 17 2
• Address_1 ASCII 93 37
• Address_2 ASCII 130 50
• Address_3 ASCII 180 36
• City ASCII 216 29
• St ASCII 245 2
• Postal ASCII 247 12
• Account ASCII 259 4
• Remit_Vndr ASCII 263 15
• Pay_Status ASCII 278 1

14
ACL Commands Executed

• _at_ STATISTICS ON Amount TO SCREEN NUMBER 5
• _at_ STRATIFY ON Amount INTERVALS 45 TO SCREEN
• _at_ DUPLICATES ON Reference ERRORLIMIT 10 TO SCREEN
  PRESORT
• _at_ GAPS ON Reference ERRORLIMIT 10 TO SCREEN
  PRESORT
• _at_ SAMPLE ON RECORD NUMBER 20 RECORD IF Remit_Vndr
  ltgt 'S' TO "AP Jun 02 Sample" OPEN

15
Duplicate Payment SQL

• SELECT A.BANK_CD, TO_CHAR(A.PYMNT_DT,'YYYY-MM-DD')
  , A.PYMNT_ID_REF, A.NAME1, A.PYMNT_AMT,
  A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.ADDRESS4,
  A.CITY, A.STATE, A.POSTAL, C.INVOICE_ID,
  TO_CHAR(C.INVOICE_DT,'YYYY-MM-DD'), C.VENDOR_ID,
  C.PAID_AMT, C.PYMNT_GROSS_AMT, C.DSCNT_PAY_AMT,
  C.PAID_AMT_GROSS, C.PAID_AMT_DSCNT,
  A.PYMNT_STATUS
• FROM PS_PAYMENT_TBL A, PS_PYMNT_XREF_VW C
• WHERE A.BANK_SETID 'BEARS'
• AND A.PYMNT_DT gt TO_DATE(1,'YYYY-MM-DD')
• AND A.PYMNT_DT lt TO_DATE(2,'YYYY-MM-DD')
• AND A.BANK_SETID C.BANK_SETID
• AND A.BANK_CD C.BANK_CD
• AND A.BANK_ACCT_KEY C.BANK_ACCT_KEY
• AND A.PYMNT_ID C.PYMNT_ID
• AND A.BANK_CD 'CNB'
• AND A.PYMNT_STATUS 'P'
• ORDER BY 1, 3

16
Advanced ACL Functions

• EXCLUDE( ) returns a variable length string,
  excluding characters that you specify from the
  result.
• Function Format
• EXCLUDE(C1,C2)
• EXCLUDE( ) returns a string containing the
  characters in C1, excluding any characters that
  appear in C2, in the same order that they appear
  in C1.
• SOUNDEX( ) returns a four-character value for a
  specified string which you can compare with the
  value of another string to see if both strings
  are phonetically similar.
• This function is useful if you want to find items
  with duplicate entries or items where spelling is
  inconsistent.
• The first character in the value represents the
  first letter in the string. The next three
  characters in the value represent phonetically
  similar consonant groups in the string.
  SOUNDEX( ) ignores both capitalization and vowels
  in the string and only uses the first three
  consonant groups in long arguments.

17
Actual ACL Commands

• Clean_Invoice EXCLUDE( Invoice,
• " '0/-,_at_._!\?gtlt()")
• Name_Soundex SOUNDEX( Name)
• DUPLICATES ON Name_Soundex Clean_Invoice Amount1
  OTHER Date Reference Name Amount Invoice Date1
  Vendor

18
Results Testing for Duplicates

• As advertised, the PeopleSoft Accounts Payable
  application prevents redundant data entry of
  vendor number, invoice number and invoice amount.
  However, the vendor number and invoice number
  must match. As a result of our review, we found
  the following
• Invoice Number not exact matches we found
  invoice numbers with extra or leading zeros, and
  containing special characters.
• Vendor Number not matching we found payments to
  the same vendor name but different vendor number
  and address.
• Vendor Number and Vendor Name not matching we
  found payments made to a company under a
  different vendor number and a phonetically
  similar (but not exact) vendor name.

19
The Rest of the Story

• Two audit reports issued identifying causes
• Nearly 14,000 overpayments found
• One single overpayment exceeding 10,000
• Controllers Office now taking steps to identify
  duplicate payments before we do

20
Pick Low Hanging Fruit

• User Accounts and Terminated Employees
• PeopleSoft Query
• HR Query
• Use ACL to analyze

21
SQL for PeopleSoft Operator IDs

• SELECT A.OPRID, A.VERSION, A.OPRTYPE,
  A.OPRDEFNDESC, A.EMPLID, A.OPRCLASS,
  A.CLASSCOUNT, A.ROWSECCLASS, A.TIMEOUTMINUTES,
  A.AUTHSIGNONCOUNT, A.AUTHITEMCOUNT, A.OPERPSWD,
  A.ENCRYPTED, A.ACCESSID, A.ACCESSPSWD,
  A.LANGUAGE_CD, A.AUTHPRCSCOUNT, A.PRCSPROFILE,
  A.BACKGRNDDISCINT, A.DEFAULTBPM,
  A.STARTAPPSERVER, A.MULTILANG
• FROM PSOPRDEFN A
• WHERE A.OPRTYPE 0

22
Find Terminated Employees
23
Other Fruit?
24
Partner with External Auditor

• Requisitions Over 25,000
• Three Queries
• Analyze with ACL

25
SQL Statements Identifying Budget Approvers

• SELECT A.ROLEUSER, A.ROLENAME, A.DESCR, A.OPRID,
  A.EMAILID, A.FORMID, A.WORKLIST_USER_SW,
  A.EMAIL_USER_SW, A.FORMS_USER_SW, A.EMPLID
• FROM PS_ROLEUSER_VW A
• WHERE A.ROLENAME 'BUDGET APPROVER'

26
Users With Budget Approver Role
27
SQL - Business Rule Tied to the Role of Budget
Approver

• SELECT A.BUSPROCNAME, A.APPR_RULE_SET,
  TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.APPR_STEP,
  A.APPR_PATH, A.APPR_RULE_SEQ, A.FIELD_PNLFIELD,
  A.MAX_DEBIT_AMT, A.MAX_CREDIT_AMT, A.RECNAME,
  A.CURRENCY_CD, B.ROLENAME, B.APPR_PATH,
  B.APPR_STEP, TO_CHAR(B.EFFDT,'YYYY-MM-DD'),
  B.APPR_RULE_SET, B.BUSPROCNAME
• FROM PS_APPR_RULE_AMT A, PS_APPR_RULE_ROLE B
• WHERE A.EFFDT
• (SELECT MAX(A_ED.EFFDT) FROM
  PS_APPR_RULE_AMT A_ED
• WHERE A.BUSPROCNAME A_ED.BUSPROCNAME
• AND A.APPR_RULE_SET
  A_ED.APPR_RULE_SET
• AND A_ED.EFFDT lt SYSDATE)
• AND A.BUSPROCNAME B.BUSPROCNAME
• AND A.APPR_RULE_SET B.APPR_RULE_SET
• AND A.APPR_STEP B.APPR_STEP
• AND A.APPR_PATH B.APPR_PATH
• AND B.EFFDT
• (SELECT MAX(B_ED.EFFDT) FROM
  PS_APPR_RULE_ROLE B_ED
• WHERE B.BUSPROCNAME B_ED.BUSPROCNAME
• AND B.APPR_RULE_SET
  B_ED.APPR_RULE_SET
• AND B_ED.EFFDT lt A.EFFDT)

28
Results of Business Rule Query
29
SQL for Requisition Information from June 1, 2002
through August 31, 2002

• SELECT TO_CHAR(A.ACCOUNTING_DT,'YYYY-MM-DD'),
  A.REQ_ID, A.REQ_STATUS, A.POST_DOC,
  A.BCM_HDR_STATUS, B.BCM_LINE_STATUS,
  B.PO_POST_STATUS, B.DISTRIB_LN_STATUS,
  B.MERCHANDISE_AMT, B.LINE_NBR, B.DISTRIB_LINE_NUM,
  B.ACCOUNT, B.DEPTID, B.BCM_POST_AMOUNT,
  B.PO_POST_AMT, B.SCHED_NBR, B.CLOSE_AMOUNT,
  B.AMT_OPEN, B.BCM_POST_TOTAL, B.BUDGET_PERIOD,
  B.QTY_REQ, B.QTY_OPEN, B.GFPF_INPROCESS_FLG,
  B.GFPF_FULL_LIQ_FLG, A.HOLD_STATUS,
  TO_CHAR(A.REQ_DT,'YYYY-MM-DD'), A.ORIGIN,
  A.REQUESTOR_ID, A.OPRID_ENTERED_BY,
  A.OPRID_MODIFIED_BY, A.OPRID_APPROVED_BY,
  TO_CHAR(A.ENTERED_DT,'YYYY-MM-DD'),
  TO_CHAR(A.APPROVAL_DT,'YYYY-MM-DD')
• FROM PS_REQ_HDR A, PS_REQ_LN_DISTRIB B
• WHERE A.BUSINESS_UNIT B.BUSINESS_UNIT
• AND A.REQ_ID B.REQ_ID
• AND A.ACCOUNTING_DT BETWEEN
  TO_DATE('2002-06-01','YYYY-MM-DD') AND
  TO_DATE('2002-08-31','YYYY-MM-DD')

30
ACL Commands

• _at_ SUMMARIZE ON Req_ID ACCUMULATE Amount OTHER
  Approved_By TO "Summarize on Req ID.FIL" OPEN
  PRESORT
• _at_ EXTRACT FIELDS Req_ID Amount COUNT Approved_By
  IF Amount gt 25000 TO "Requistions Over 25K.fil"
  OPEN
• _at_ CLASSIFY ON Approved_By TO SCREEN

31
What Are Your Business Rules?
32
Peruse the Data
Query the tables
33
Operator Definition Tables
34
Plan for Training

• MIS Training Institute
• ISACA
• External Auditors
• PSHEUG

35
Perform an Audit
Purchasing Accounts Payable
General Controls
Application Controls
36
Initial Passwords

• During implementation, a common initial password
  was set for users
• Encrypted password stored in PSOPRDEFN
• Changed my password to password
• Determined that 60 users still had this password

37
Import Manager

• Mechanism to upload data from existing files to
  PeopleSoft application data tables
• Typically, used during conversion from old to new
  systems
• However, Import Manager can upload data from
  other sources like Excel

38
Application Parameters

• OPR_DEF_TBL_AP
• INVOICE_AMT_MAX
• CHK_INV_AMT_FLG
• OPR_DEF_TBL_PM
• ALL_REQUESTORS_FLG
• REQ_CLOSE_AUTH
• REQ_APPROVAL_AUTH
• PO_APPROVAL_AUTH

39
Vendor Master Files

• Vendor Name Matches
• Soundex Match
• Vendor Address Matches
• Vendor / Employee Address Matches
• Will be reviewed after year end

40
Perfect the Process

• Use ACLs ODBC compliant interface to directly
  access the Oracle Tables
• Controllers Office now considering purchase of
  ACL to look for duplicate payments prior to our
  reviews. Internal Audit has reduced its audit
  time.

41
ODBC Connection to Tables
42
ODBC Connection to Tables
43
Conclusion of Presentation
Today, we discussed the learning process an
auditor may go through to develop the skill set
necessary to audit in the PeopleSoft environment.
Training in auditing PeopleSoft is an
imperative. I am particularly appreciative of
the support I received from Internal Audit
management at Baylor University. Also, I could
not have performed an audit in the PeopleSoft
environment without the assistance of our
Financial Systems team in our Information
Technology Services Department. In
conclusion Sic Em
44
Contact Information

• Mark CarterAssistant Director IT Audit
• Baylor UniversityMark_Carter_at_baylor.edu
• 254-710-3867HTTP//HEUG.ORG (attendees may
  download HEUG2003 presentations from the archives
  at this location)