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!