SAP ECC (R/3): Export the general ledger – MindBridge: English (US)
Questions? We have answers.
Summary
Learn about the methods and requirements for exporting data from SAP systems.
Notes
There are 2 methods for exporting data from SAP systems.
- Use the SE16N (General Table Display) transaction code to view and filter the SAP tables. You can then export the results to a spreadsheet format (.xlsx), or,
- Run queries against the database to get the required values.
- Excel files limit rows to a maximum of 1048576. If the export is larger than that, use filters to split the data into smaller files.
- If using a chart of accounts file to load account descriptions and/or MAC code mapping, be sure that the values in Account ID uniquely match those in the general ledger file. Depending on how the SAP system is configured, you may need to qualify this with a combination of other data fields such as MANDT, BUKRS, SPRAS, KTOPL.
Extract data
Chart of accounts
| MindBridge column | SAP field | SAP description | Required | Notes |
|---|---|---|---|---|
| Account | SKAT.SAKNR | G/L Account Number | Y | – |
| Account Description | SKAT.TXT50 | G/L Account Long Text | Y | Can use SKAT.TXT20 instead |
| Account Tags | – | – | N | This is not an SAP data element. |
| MAC code | – | – | N | This is not an SAP data element. It can be used to map to a MAC code from within the file. |
General ledger
For general ledger data, you will need access to the following tables:
- BKPF – Accounting Document Header
- BSEG – Accounting Document Segment
It is mandatory to include the following SAP fields in both tables in order to perform an effective merge:
- MANDT
- BUKRS
- BELNR
- GJAHR
Below is a table that links the MindBridge columns with their corresponding SAP fields:
| MindBridge column | SAP field | SAP description | Required | Used in analytics | Notes |
|---|---|---|---|---|---|
| Account ID | BSEG.SAKNR | G/L account number | Y | Y | - |
| Account Description | SKAT.TXT50 | G/L Account Long Text | N | N | Can be left out and picked up from the chart of accounts (above). Can use SKAT.TXT20 instead. |
| Amount | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit indicator Amount in local currency |
Y | Y | If BSEG.SHKZG is "S (i.e.; Debit)" use BSEG.DMBTR. If BSEG.SHKZG is "H (i.e.; Credit)" use 0 - BSEG.DMBTR. If supplying line entry amounts via the Amount column, leave the Debit and Credit columns blank. |
| Debit | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit indicator Amount in local currency |
Y | Y | If BSEG.SHKZG is "S (i.e.; Debit)" use BSEG.DMBTR, otherwise leave as 0 or blank. If supplying line entry amounts via the Debit and Credit columns, leave the Amount column blank. |
| Credit | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit indicator Amount in local currency |
Y | Y | If BSEG.SHKZG is "H (i.e.; Credit)" use BSEG.DMBTR, otherwise leave as 0 or blank. If supplying line entry amounts via the Debit and Credit columns, leave the Amount column blank. |
| Entered Date | BKPF.CPUDAT | Day on which accounting document was entered | Y | Y | Appears in format YYYY-MM-DD. |
| Effective Date | BKPF.BUDAT | Posting date in the document | Y | Y | Appears in format YYYY-MM-DD. |
| MAC code | – | – | N | Y | This is not an SAP data element. It can be used to map to a MAC code from within the file. |
| Manual Entry | – | – | N | Y | SAP does not provide a field for manual entries. Configure the manual entry codes in the settings, then indicate whether each entry was entered manually or automatically (by batch). This may involve a combination of USNAM, BLART, and TCODE. |
| Memo | BKPF.BKTXT and/or BSEG.SGTXT |
Document header text Item text |
N | Y | Depending on the data, you can use one or the other, or a concatenation of both. |
| Source | – | Indicates the entry's source ledger or sub-ledger. | N | Y | Depending on SAP configuration, this data field may not be available. |
| Type | BKPF/BLART | Document type | N | Y | – |
| Transaction ID | BKPF.BELNR | Accounting document number | Y | Y | – |
| User ID | BKPF/USNAM | User name | N | N | While not used in analytics, you can use this to sort and filter results for reports. |