Questions? We have answers.

Data Checklist: SAP requirements for accounts receivable analysis

  • Updated

Summary

Learn which SAP fields MindBridge requires for the accounts receivable analysis.

This article should be read in conjunction with the Data Checklist: Accounts receivable.

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
KNA1 Customer Master (General Section)
KNC1 Customer Master (Transaction Figures)
T003T

Document Type Texts (may be used to determine Entry Type)

SKAT General Ledger Account Master Record
USR02

SAP Log in Data (may be used to determine Manual Entry)

 


AR detail (required)

The AR detail file contains all the activities in the accounts receivable 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

Customer name

Yes

KNA1.NAME Name of the customer If you have multiple customer 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

Customer ID

No

BSEG.KUNNR

BKPF.MANDT*

BKPF.BUKRS*

BKPF.GJAHR*

Account Number of Customer

*We strongly suggest to include Customer ID for SAP datasets. If no Customer ID is provided, MindBridge will use the Customer Name as the Customer ID. If both are provided, analysis will use Customer 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 Customer ID.

The Customer 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 data set 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 are no specific SAP field that corresponds to Entry Type, you can infer the Entry Type in the following ways: 

  1. 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
  2. Mapping from TCODE. 
  3. Mapping from Posting Key (BSCHL) from the BSEG table.Below is an example mapping:
    Posting Key (BSCHL)
    Entry Type
    01 (Invoice) Invoice
    02 (Reverse credit memo) Adjustment
    03 (Bank charges) Adjustment
    04 (Other receivables) Adjustment
    05 (Outgoing payment) Payment
    06 (Payment difference) Payment
    07 (Other clearing) Adjustment
    08 (Payment clearing) Adjustment
    09 (Special G/L debit) Adjustment
    11 (Credit memo) Credit Memo
    12 (Reverse invoice) Adjustment
    13 (Reverse charges) Adjustment
    14 (Other payables) Adjustment
    15 (Incoming payment) Payment
    16 (Payment difference) Payment
    17 (Other clearing) Adjustment
    18 (Payment clearing) Payment
    19 (Special G/L credit) Adjustment

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:

  1. User Name - Select User IDs may be related system and automated User IDs.  This would indicate that the entry is not a manual entry. 
  2. Document Type (BLART) - Select document types such as AA or SA are often considered manual entry.
  3. 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 receivables list (mandatory)

This file contains the line items covering all the receivables that remain outstanding at the end of the current reporting period. The data format follows that of the AR 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 receivables list by taking a subset of the AR 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 BSID / BSAD tables represent a current open / closed view, they may not necessarily coincide with the analysis period.

Customer list (optional)

This file provides names for each customer.

#

Field name

Required?

SAP field

SAP Description

Notes

1

Customer name

Yes KNA1.NAME Name If you have multiple customer names, you may need to concatenate all the fields.  

2

Customer ID

No

KNA1.LIFNR

BKPF.MANDT*

BKPF.BUKRS*

BKPF.GJAHR*

Account Number of Customer

*We strongly suggest to include Customer ID for SAP datasets. If no Customer ID is provided, MindBridge will use the Customer Name as the Customer ID. If both are provided, analysis will use Customer 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 Customer ID.

The Customer ID field needs to match across all files.

3

Customer address

No

KNA1.*

Various KNA1.* address fields  

4

Related

No     This data is typically not available in SAP systems. 

 


Customer opening balances (optional)

This file provides opening balances for each customer for the fiscal year. This file is optional and can be provided instead of the Start of Period Outstanding Receivables List.

Note: The following mapping is provided as a reference for the data structure. Due to the timing nature of the data in KNC1, 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 KNC1 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

Customer name

Yes KNC1.NAME Name If you have multiple customer names, you may need to concatenate all the fields.  

2

Balance

Yes KNC1.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

Customer ID

 No

KNC1.LIFNR

BKPF.MANDT*

BKPF.BUKRS*

BKPF.GJAHR*

Account Number of Customer

*We strongly suggest to include Customer ID for SAP datasets. If no Customer ID is provided, MindBridge will use the Customer name as the Customer ID. If both are provided, analysis will use Customer 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 Customer ID.

The Customer ID field needs to match across all files.

 


Start of period outstanding receivables list (optional)

This file contains the line items covering all the receivables from previous reporting period that were outstanding at the start of the current reporting period. The data format follows that of the AR 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 Customer Opening Balances.

To check for completeness within MindBridge, you can create the end of period outstanding receivables list by taking a subset of the previous period's AR 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. 

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

Was this article helpful?