Summary

Learn about the process of exporting a general ledger file from PeopleSoft.

Extract data

Via PeopleSoft Interphase

1. From PeopleSoft Finance, navigate to the Journal Entry Detail report via:
   General Ledger > General Reports > Journal Entry Detail
   Note: Be sure to have the Journal ID available to use the report most efficiently.
   The General Ledger Run Control ID will display on your screen.
   
   
2. Select an existing Run Control ID, or create a new one as appropriate. The Trial Balance search parameters will display on your screen.
3. Enter the Journal ID to search in the Journal ID field.
   
   
4. Click the Refresh button to view all ChartFields.
   
   
5. Select the ChartFields you wish to include in the report.
   
   
6. Click the Save button and Run the Report.

Via Script 

1. Provide the script text file *** to your client – see below.
2. Tell your client to give this script to their Database Administrator (or some other in-house technical resource) and have them run the script.
3. You can ingest the resulting files directly into MindBridge.

Note: the below query is a starting point that may need to be adjusted, depending on the client’s PeopleSoft implementation.

Sample GL Export Script

-- GL Data

SELECT JL.ACCOUNT, ACCT.DESCR, JH.POSTED_DATE, JH.BUSINESS_UNIT AS TransactionId1, JH.JOURNAL_ID AS TransactionId2, TO_CHAR(JH.JOURNAL_DATE, 'YYYY-MM-DD') AS TransactionId3, JH.UNPOST_SEQ AS TransactionId4

, CASE WHEN JL.MONETARY_AMOUNT > 0 THEN JL.MONETARY_AMOUNT ELSE 0 END AS Debit

, CASE WHEN JL.MONETARY_AMOUNT < 0 THEN JL.MONETARY_AMOUNT ELSE 0 END AS Credit

, JL.LINE_DESCR AS memo, JH."SOURCE" AS TransactionType

, JH.OPRID AS UserId

FROM PS_JRNL_LN JL

JOIN PS_JRNL_HEADER JH ON JH.BUSINESS_UNIT = JL.BUSINESS_UNIT AND JH.JOURNAL_ID = JL.JOURNAL_ID AND JH.JOURNAL_DATE = JL.JOURNAL_DATE AND JH.UNPOST_SEQ = JL.UNPOST_SEQ

JOIN PS_GL_ACCOUNT_TBL ACCT ON ACCT.SETID = 'SHARE' AND ACCT.ACCOUNT = JL.ACCOUNT

WHERE

ACCT.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL ACCT2 WHERE ACCT2.SETID = ACCT.SETID AND ACCT2.ACCOUNT = ACCT.ACCOUNT AND EFFDT <=sysdate)

AND JL.LEDGER = 'ACTUALS'

AND JH.JOURNAL_DATE BETWEEN DATE '2018-01-01' AND DATE '2018-09-30'

• Instruction: https://wiki.umbc.edu/pages/viewpage.action?pageId=19103983