Summary
Learn about the process of exporting a general ledger file from PeopleSoft.
Extract data
Via PeopleSoft Interphase
- 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.
- Select an existing Run Control ID, or create a new one as appropriate. The Trial Balance search parameters will display on your screen.
- Enter the Journal ID to search in the Journal ID field.
- Click the Refresh button to view all ChartFields.
- Select the ChartFields you wish to include in the report.
- Click the Save button and Run the Report.
Via Script
- Provide the script text file *** to your client – see below.
- Tell your client to give this script to their Database Administrator (or some other in-house technical resource) and have them run the script.
- 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'
Source
- Supporting document to verify which report was needed: https://docs.oracle.com/cd/E21910_01/psft/acrobat/fscm91fglr-r0311.pdf - page 6
Anything else on your mind?
Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.