In every GL implementation I’ve been involved with, auditors or managers always ask for the ability to audit who posted their own journal entries. Unfortunately PeopleSoft doesn’t provide the means to do this very easily, so I’ve written a couple of queries that you can use to build a report or a view that satisfies this requirement.
The first query is only valid if you’ve disallowed users from On-Demand Posting journals (the “Journal Post” option in the drop-down box on the JE Lines screen). If you disable this process, users have to utilize the batch journal post process. Here is the SQL:
SELECT A.OPRID, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.DESCR FROM PS_JRNL_POST_REQ A, PS_JRNL_HEADER B WHERE A.OPRID = B.OPRID AND B.BUSINESS_UNIT = A.BUSINESS_UNIT;
The second query is valid if you haven’t restricted On-Demand Posting of journal entries. It’s a bit tricky, so stay with me:
SELECT A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE, A.DESCR, A.OPRID,
A.POSTED_DATE, A.PROCESS_INSTANCE, B.JOBID, B.PROGRAM_NAME,
C.MESSAGE_PARM
FROM PS_JRNL_HEADER A, PS_MESSAGE_LOG B, PS_MESSAGE_LOGPARM C
WHERE B.PROCESS_INSTANCE = A.PROCESS_INSTANCE
AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE
AND B.MESSAGE_SEQ = C.MESSAGE_SEQ
AND C.MESSAGE_SEQ = 2
AND C.PARM_SEQ = 2
AND A.OPRID = C.MESSAGE_PARM
AND B.PROGRAM_NAME LIKE 'GLPP%'
ORDER BY A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE;
I’ve heard that PeopleSoft 9.1 provides this ability as delivered, but haven’t seen it yet. I’ll update this post once I verify the feature is available. Happy querying!
