Data Checklist: SAP requirements for accounts payable analysis – MindBridge: English (US)
Questions? We have answers.
Summary
Learn what SAP fields that MindBridge requires for the accounts payable analysis.
This article should be read with Data checklist: Accounts payable.
Note: SAP fields mentioned in this document are specified by {SAP Table Name}.{SAP Field Name}. Concatenation of any of the fields mentioned below can be done during the import of the file within MindBridge.
General notes
The various SAP tables used are as follows:
| Table | Description |
|---|---|
| BKPF | Accounting Document Header Table |
| BSEG | Accounting Document Segment Cluster Table |
| SKAT | General Ledger Account Master Record |
| LFA1 | Vendor Master (General Section) |
| LFC1 | Vendor Master (Transaction Figures) |
| T003T | Document Type Texts (may be used to determine Entry Type) |
| USR02 | SAP Log in Data (may be used to determine Manual Entry) |
AP detail (required)
The AP detail file contains all the activities in the accounts payable accounts.
| # | Field name | Required? | SAP field | SAP Description | Notes |
|---|---|---|---|---|---|
| 1 | Entry ID | Yes | BKPF.BELNR BKPF.MANDT* BKPF.BUKRS* BKPF.GJAHR* |
Accounting Document Number | You may also need to include a combination of the other primary keys (such as MANDT, BUKRS, and GJAHR) in order to create a unique Entry ID. The Entry ID field needs to match across all files. |
| 2 | Vendor name | Yes | LFA1.NAME | Name of the payee | If you have multiple vendor names, you may need to concatenate all the fields together. |
| 3 | Amount | No | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency |
|
| 4 | Debit | Yes | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency |
If BSEG.SHKZG is "S" (ie: Debit), use amount in BSEG.DMBTR, otherwise leave amount as 0 or blank. |
| 5 | Credit | Yes | BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency |
If BSEG.SHKZG is "H" (ie: Credit), use amount in BSEG.DMBTR, otherwise leave amount as 0 or blank. |
| 6 | Effective date | Yes | BKPF.BUDAT | Posting Date in the Document | |
| 7 | Entry type | Yes | BKPF.BLART* BKPF.TCODE* BSEG.BSCHL* |
Document Type Transaction Code Posting Key |
*There is no specific SAP field that corresponds to Entry Type. See the Entry Type section below for more details. |
| 8 | Vendor ID | No | BSEG.LIFNR BKPF.MANDT* BKPF.BUKRS* BKPF.GJAHR* |
Account Number of Vendor or Creditor | *We strongly suggest to include Vendor ID for SAP datasets. If no vendor ID is provided, MindBridge will use the Vendor Name as the Vendor ID. If both are provided, analysis will use Vendor ID, but both are displayed. You may also need to include a combination of the other primary keys (such as MANDT, BUKRS, and GJAHR) in order to create a unique Vendor ID. The Vendor ID field needs to match across all files. |
| 9 | GL date | No | BKPF.CPUDT | Day On Which Accounting Document Was Entered | |
| 10 | Account ID | No | BSEG.HKONT | G/L Account Number | |
| 11 | Invoice Ref | No | BSEG.REBZG* BSEG.AUGBL* |
Number of the Invoice the Transaction Belongs to | *These values need to link back to the Entry ID of the related invoice entry. This may either be stored in REBZG of the payment document or, in reverse, stored in AUGBL of the invoice document. Invoice and payments may have a many to many relationship in SAP. This is currently not supported in MindBridge. |
| 12 | Memo | No | BKPF.BKTXT and / or BSEG.SGTXT |
Document Header Text Item Text |
Use either or a concatenation of both. |
| 13 | Due Date | No | FAEDT BSEG.ZFBDT* BSEG.ZTERM* |
Net Due Date Baseline date for due date calculation Terms of Payment Key |
*Net Due Date may not be available, in which case the Due Date will require calculation using BSEG.ZFBDT, BSEG.ZTERM and other fields. |
| 14 | Invoice date | No | BKPF.BLDAT | Document Date in Document | |
| 15 | Transaction type | No | BKPF.BLART | Document Type | |
| 16 | User ID | No | BKPF.USNAM | User name |
You may also have additional columns that would be useful to include when evaluating your MindBridge results. Please include them in the dataset as needed.
Some examples include: AWTYP, BSTAT, BUZEI, CPUTM, GLVOR, KTOKK, MONAT, RLDNR, STBLG, STGRD, UMSKZ, and XREVERSAL.
Entry type
Entry type is used to determine whether the nature of the entry. The following are accepted entry types:
- Credit memo
- Debit memo
- Adjustment
- Invoice
- Payment
While there is no specific SAP field that corresponds to Entry Type, you can infer the Entry Type in the following ways:
- This is the preferred way to get the entry type field. If it's not available, please try options 2 or 3. Mapping from Posting Key (BSCHL) from the BSEG table. Below is an example mapping:
| Posting Key (BSCHL) | Entry Type |
|---|---|
| 21 (Credit memo) | Debit Memo |
| 22 (Reverse invoice) | Adjustment |
| 24 (Other receivables) | Adjustment |
| 25 (Outgoing payment) | Payment |
| 26 (Payment difference) | Payment |
| 27 (Clearing) | Adjustment |
| 28 (Payment clearing) | Payment |
| 29 (Special General Ledger debit) | Adjustment |
| 31 (Invoice) | Invoice |
| 32 (Reverse credit memo) | Adjustment |
| 34 (Other payables) | Adjustment |
| 35 (Incoming payment) | Payment |
| 36 (Payment difference) | Payment |
| 37 (Other clearing) | Adjustment |
| 38 (Payment clearing) | Payment |
| 39 (Special General Ledger credit) | Adjustment |
- Mapping from Document Type (BLART) from the BKPF table. Below is an example mapping:
| Document Type (BLART) | Entry Type |
|---|---|
| RE | Invoice |
| KZ | Payment |
| KG | Credit Memo |
- Mapping from TCODE.
Manual Entry
There is a Manual Entry control point that is triggered by entries containing a keyword in a column of the data set. There are a variety of ways that can be used to determine whether an entry is a manual entry depending on your SAP implementation and purpose.
Here are some examples of how you can determine manual entry:
- User Name - Select User IDs may be related system and automated User IDs. This would indicate that the entry is not a manual entry.
- Document Type (BLART) - Select document types such as AA or SA are often considered manual entry.
- A formula involving multiple columns, such as TCODE = F* AND KOART = S.
You can select all the keywords that indicate a manual entry within MindBridge or you can adjust your extraction scripts to output either a True/False or 1/0 to indicate a manual entry.
End of period outstanding payables list (mandatory)
This file contains the line items covering all the payables that remain outstanding at the end of the current reporting period. The data format follows that of the AP Detail table described above, except the Entry Type field should be excluded as each entry will automatically be set to "Outstanding".
To check for completeness within MindBridge, you can create the end of period outstanding payables list by taking a subset of the AP Detail where: (Clearing Date > date at End of Period) or (Clearing Date is null). Below are SAP fields that can be used as the clearing date in the formula above
- BSEG.AUGDT = Clearing Date
- BSEG.AUGBL = Document Number of the Clearing Document SAP field
Note: Although the BSIK / BSAK tables represent a current open / closed view, they may not necessarily coincide with the analysis period.
Vendor list (optional)
This file provides names for each vendor.
| # | Field name | Required? | SAP field | SAP Description | Notes |
|---|---|---|---|---|---|
| 1 | Vendor name | Yes | LFA1.NAME | Name | If you have multiple vendor names, you may need to concatenate all the fields together. |
| 2 | Vendor ID | No | LFA1.LIFNR BKPF.MANDT* BKPF.BUKRS* BKPF.GJAHR* |
Account Number of Vendor or Creditor | *We strongly suggest to include Vendor ID for SAP datasets. If no vendor ID is provided, MindBridge will use the Vendor Name as the Vendor ID. If both are provided, analysis will use Vendor ID, but both are displayed. You may also need to include a combination of the other primary keys (such as MANDT, BUKRS, and GJAHR) in order to create a unique Vendor ID. The Vendor ID field needs to match across all files. |
| 3 | Vendor address | No | LFA1.* | Various LFA1.* address fields | |
| 4 | Related | No | This data is typically not available in SAP systems. |
Vendor opening balances (optional)
This file provides opening balances for each vendor for the fiscal year. This file is optional and can be provided instead of the Start of Period Outstanding Payables List.
Note: The following mapping is provided as a reference for the data structure. Due to the timing nature of the data in LFC1, it may not work for many SAP implementations. In these cases, the data will need to be obtained from a different source.
Note: The primary key for LFC1 includes MANDT, BUKRS, GJAHR. You may need to include these so that each row is well defined.
| # | Field name | Required? | SAP field | SAP Description | Notes |
|---|---|---|---|---|---|
| 1 | Vendor name | Yes | LFC1.NAME | Name | If you have multiple vendor names, you may need to concatenate all the fields together. |
| 2 | Balance | Yes | LFC1.UMSAV | Balance Carried Forward in Local Currency | Opening balance in local currency. Negative for credit, positive for debit. |
| 3 | Debit | No | |||
| 4 | Credit | No | |||
| 5 | Vendor ID | No | LFC1.LIFNR BKPF.MANDT* BKPF.BUKRS* BKPF.GJAHR* |
Account Number of Vendor or Creditor | *We strongly suggest to include vendor ID for SAP datasets. If no vendor ID is provided, MindBridge will use the vendor name as the vendor ID. If both are provided, analysis will use vendor ID, but both are displayed. You may also need to include a combination of the other primary keys (such as MANDT, BUKRS, and GJAHR) in order to create a unique vendor ID. The vendor ID field needs to match across all files. |
Start of period outstanding payables list (optional)
This file contains the line items covering all the payables from previous reporting period that were outstanding at the start of the current reporting period. The data format follows that of the AP Detail table described above, except the Entry Type field should be excluded as each entry will automatically be set to "Opening".
This file is optional and can be provided instead of the Vendor Opening Balances.
To check for completeness within MindBridge, you can create the end of period outstanding payables list by taking a subset of the previous period's AP Detail where: (Clearing Date > date at End of Previous Period) or (Clearing Date is null). Below are SAP fields that can be used as the clearing date in the formula above.
- BSEG.AUGDT = Clearing Date
- BSEG.AUGBL = Document Number of the Clearing Document SAP field
Note: Although the BSIK / BSAK tables represent a current open / closed view, they may not necessarily coincide with the analysis period.