Summary
Learn how to map SAP accounts to MindBridge in accounts receivable analyses.
General notes
The various files (i.e.; tables) used are as follows:
|
File
|
Description
|
|---|---|
| BKPF | Accounting Document Header Table |
| BSEG | Accounting Document Segment Cluster Table |
| SKAT | G/L Account Master Record |
| LFA1 | Vendor Master (General Section) |
| LFC1 | Vendor Master (Transaction Figures) |
| T003T |
Document Type Texts (used only if needed to aid in determining Entry Type) |
| USR02 |
SAP Logon Data (only if needed for Manual Entry) |
File 1
AP detail (mandatory)
The AP detail file contains all the activities in the accounts payable accounts.
|
MindBridge column name
|
SAP field
|
SAP description
|
Required
|
Used in analytics
|
Notes
|
|---|---|---|---|---|---|
| Entry ID |
BKPF.BELNR may also need BKPF.MANDT BKPF.BUKRS BKPF.GJAHR |
Accounting Document Number Client Company Code Fiscal Year |
Yes* | Yes |
Values need to uniquely define transactions. Depending on your SAP configuration, you may also need to include some combination of the other primary keys, MANDT, BUKRS, and GJAHR in order to preserve uniqueness. The Entry ID field needs to match across all files. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Vendor Name | LFA1.NAME1 | Name of the payee | Yes | Yes |
May also need to concatenate NAME2, NAME3, and NAME4, depending on the SAP implementation. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Amount |
BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency |
Yes | Yes |
if BSEG.SHKZG is "S (ie: Debit)", use BSEG.DMBTR if BSEG.SHKZG is "H (ie: Credit)", use 0 - BSEG.DMBTR If supplying line entry amounts via the Amount column, leave Debit / Credit columns blank |
| Debit |
BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency
|
Yes | Yes |
if BSEG.SHKZG is "S (ie: Debit)", use BSEG.DMBTR, otherwise leave as 0 or blank If supplying line entry amounts via the Debit / Credit columns, leave the Amount column blank |
| Credit |
BSEG.SHKZG BSEG.DMBTR |
Debit/Credit Indicator Amount in Local Currency |
Yes | Yes |
if BSEG.SHKZG is "H (ie: Credit)", use BSEG.DMBTR, otherwise leave as 0 or blank If supplying line entry amounts via the Debit / Credit columns, leave the Amount column blank |
| Effective Date | BKPF.BUDAT | Posting Date in the Document | Yes | Yes | In the format YYYY-MM-DD |
| Entry Type |
BKPF.BLART* BKPF.TCODE* BSEG.BSCHL* |
Document Type Transaction Code Posting Key |
Yes | Yes |
*There is no specific SAP field that corresponds to Entry Type. The allowable values are:
See below for detailed information on Entry Type. Note: This column is not mapped in the Open Payables Lists. |
| Vendor ID |
BSEG.LIFNR |
Account Number of Vendor or Creditor | No* | Yes |
*For SAP data sets, Vendor ID is strongly suggested. The Vendor ID field needs to match across all files. Depending on your SAP configuration, you may also need to include some combination of the other primary keys, MANDT, BUKRS, and GJAHR in order to preserve uniqueness. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| GL Date | BKPF.CPUDT | Day On Which Accounting Document Was Entered | No | No | |
| Account ID | BSEG.HKONT | G/L Account Number | No | No | BSEG.SAKNR may also be available, but is not always filled in |
| Invoice Ref |
BSEG.REBZG* BSEG.AUGBL* |
Number of the Invoice the Transaction Belongs to | No | Yes |
*These values need to link back to the Entry ID of the related invoice entry. Depending on your SAP implementation, this may be stored in REBZG of the payment document or, in reverse, stored in AUGBL of the invoice document. Depending on the SAP implementation, there may be yet other ways of doing this. Note that invoice and payments may have a many to many relationship in SAP. This is currently not supported in MindBridge. |
| Memo |
BKPF.BKTXT and / or BSEG.SGTXT |
Document Header Text Item Text |
No | Yes |
Depending on the data, can use either or a concatenation of both. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Due Date |
FAEDT BSEG.ZFBDT* BSEG.ZTERM* |
Net Due Date Baseline date for due date calculation Terms of Payment Key |
No | No |
*Depending on the SAP implementation, Net Due Date may not be available in which case further business logic may be necessary to arrive at Due Date from BSEG.ZFBDT, BSEG.ZTERM and other fields. If this is the case, you can adjust your extraction scripts to do that calculation. Alternatively, you can provide a formula along with the appropriate raw columns and mapping table for MindBridge Data Services to preprocess your data. |
| Invoice date | BKPF.BLDAT | Document Date in Document | No | Yes | The date as shown on the vendor's paper. |
| Transaction Type | BKPF.BLART |
Document Type |
No | No |
Transaction type identifier. |
| User ID |
BKPF.USNAM |
User name |
No | No |
While not used in analytics, if supplied, User ID is a sortable / filterable column in our reports |
| Manual Entry |
BKPF.USNAM BKPF.BLART BKPF.TCODE BKPF.AWTYP BKPF.GLVOR USR02.USTYP BSEG.KOART |
User name Document Type Transaction Code Reference Transaction Business Transaction User Type Account Type |
No | Yes |
*SAP does not provide a field that explicitly defines a manual entry. This field indicates whether each entry is to be considered as manual. See notes on Manual Entry below Note that this column does not show up in column mapping. Instead, you need to configure the Manual Entry codes in the Settings. |
| BSEG.AUGDT | Clearing Date | Yes* | Yes |
*This field is not needed for AP Detail, rather it can be used to determine whether this entry should be in the Open Payables Lists. However, for completeness checking purposes, the Open Payables Lists are often obtained through a separate system. See notes below on File 2 and File 5. |
|
|
BSEG.AUGBL BSEG.AUGGJ |
Document Number of the Clearing Document Fiscal Year of Clearing Document |
Yes* | Yes |
*This field is not needed for AP Detail, rather it can be used to determine whether this entry should be in the Open Payables Lists. However, for completeness checking purposes, the Open Payables Lists are often obtained through a separate system. May also need AUGGJ for uniqueness. See notes below on File 2 and File 5. |
You may also have additional columns to the ones mentioned above that would be useful to your auditors when reviewing MindBridge analysis results. Please include them in the data set as needed. Some examples are:
AWTYP, BSTAT, BUZEI, CPUTM, GLVOR, KTOKK, MONAT, RLDNR, STBLG, STGRD, UMSKZ, XREVERSAL
Entry type
Entry type is used to determine whether the entry is an invoice, payment, credit memo, debit memo, or adjustment. There can be a variety of methods to determine the entry type depending on your SAP implementation and your audit focus. You can adjust your extraction scripts to output one of the allowable values (Invoice, Payment, Credit Memo, Debit Memo, Adjustment). Alternatively, you can provide a formula or mapping table along with the appropriate raw columns for MindBridge Data Services to preprocess your data.
Here are some examples:
- Mapping from Document Type (BLART). For example RE=invoice, KZ=payment, KG=credit memo, etc
- Mapping from TCODE.
- Mapping from Posting Key (BSCHL). Here is an example mapping from Posting Key to Entry Type.
|
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 G/L 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 G/L credit) | Adjustment |
Manual entry
There can be a variety of methods to determine manual entry depending on your SAP implementation and your audit focus. You can adjust your extraction scripts to output either a True/False or 1/0 to indicate a manual entry. Alternatively, you can provide a formula or mapping table along with the appropriate raw columns for MindBridge Data Services to preprocess your data.
Here are some examples
- A mapping from User Name. In this case user IDs connected to "real people" are manual entry, but system and automated user IDs are not manual entry
- A mapping from Document Type (BLART). Some document types such as AA, SA are considered manual entry
- A formula involving multiple columns such as TCODE=F* AND KOART=S
- Some other formula
File 2
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 as described above except for the Entry Type field which should be excluded as each entry will automatically be set to "outstanding"
For completeness checking purposes, this file typically comes from another source, however if obtaining from SAP, one way would be as follows:
- Take the subset of AP Detail where:
- (Clearing Date > date at End of Period) or (Clearing Date is null)
- or (Document Number of the Clearing Document is null)
- Note that this may be redundant as a null Clearing Date should cover this situation
See also BSEG.AUGDT / BSEG.AUGBL mentioned in the AP Detail file
File 3
Vendor list (optional)
This file provides names for each vendor.
|
MindBridge column name
|
SAP field
|
SAP description
|
Required
|
Used in analytics
|
Notes
|
|---|---|---|---|---|---|
| Vendor Name | LFA1.NAME1 | Name 1 | Yes | Yes |
May also need to concatenate NAME2, NAME3, and NAME4, depending on the SAP implementation. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Vendor ID | LFA1.LIFNR | Account Number of Vendor or Creditor | No* | Yes |
*For SAP data sets, Vendor ID is strongly suggested. The Vendor ID field needs to match across all files. Depending on your SAP configuration, you may also need to include some combination of the other primary keys, MANDT, BUKRS, and GJAHR in order to preserve uniqueness. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Vendor Address |
LFA1.* |
Various LFA1.* address fields | No | No |
Concatenate the various address fields from the LFA1 table as needed. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Related | No | Yes | Indicates whether this vendor is a related party. This data is typically not available in SAP systems. |
File 4
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.
The primary key for LFC1 includes MANDT, BUKRS, GJAHR. You may need to include these so that each row is well defined.
|
MindBridge column name
|
SAP field
|
SAP description
|
Required
|
Used in analytics
|
Notes
|
|---|---|---|---|---|---|
| Vendor Name | LFC1.NAME1 | Name 1 | Yes | Yes |
May also need to concatenate NAME2, NAME3, and NAME4, depending on the SAP implementation. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
| Balance | LFC1.UMSAV | Balance Carried Forward in Local Currency | Yes | Yes | Opening balance in local currency. Negative for credit, positive for debit. |
| Vendor ID |
LFC1.LIFNR may also need BKPF.MANDT BKPF.BUKRS BKPF.GJAHR |
Account Number of Vendor or Creditor | No* | Yes |
*For SAP data sets, Vendor ID is strongly suggested. The Vendor ID field needs to match across all files. Depending on your SAP configuration, you may also need to include some combination of the other primary keys, MANDT, BUKRS, and GJAHR in order to preserve uniqueness. If needed, concatenation of the component raw SAP columns can be done via the MindBridge column mapping UI. |
File 5
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 as described above except for the Entry Type field which 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.
For completeness checking purposes, this file typically comes from another source, however if obtaining from SAP, one way would be as follows:
- Take the subset of the previous period's AP Detail where:
- (Clearing Date > date at End of Previous Period) or (Clearing Date is null)
- or (Document Number of the Clearing Document is null) ← Note that this may be redundant as a null Clearing Date should cover this situation
See also BSEG.AUGDT / BSEG.AUGBL mentioned in the AP Detail file
TCODEs
Depending on your implementation of SAP, the TCODEs FBL1N, FBL3N and FAGLL03 may be a good starting point.
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.