Questions? We have answers.

Search

  1. MindBridge: English (US)
  2. Get started
  3. Import files
  4. ERP Guides
  5. PeopleSoft

PeopleSoft: Export the general ledger

  • Updated

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 Detailimage__87_.png
    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.image__88_.png

  4. Click the Refresh button to view all ChartFields.image__89_.png

  5. Select the ChartFields you wish to include in the report.chartfields.jpg

  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'

Source

What do you think of this guide? If you have any feedback, or you think you can help us improve it, then we would love to hear from you! Please contact us at support@mindbridge.ai. We are offering rewards for any information that leads to the improvement of a guide or the creation of a new one.

Anything else on your mind? Chat with us or submit a request for further assistance.

Was this article helpful?