Questions? We have answers.

SAP mapping for accounts receivable analyses

Article author
Wing-Leung Chan
  • Updated

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

Document Type Texts

(used only if needed to aid in determining Entry Type)

SKAT G/L Account Master Record
USR02

SAP Login Data

(only if needed for Manual Entry)

 


File 1

AR detail (mandatory)

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

Customer Name KNA1.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 screen.

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:

  • Invoice
  • Payment
  • Credit Memo
  • Debit Memo
  • Adjustment

See below for detailed information on Entry Type.

Note: This column is not mapped in the Open Receivables Lists.

Customer ID

BSEG.KUNNR

Customer Number No Yes

*For SAP data sets, Customer ID is strongly suggested. The Customer 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 No The date as shown on the invoice.
Transaction Type BKPF.BLART

Document Type

Yes Yes

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*

User name

Document Type

Transaction Code

No Yes

*SAP does not provide a field that explicitly defines a manual entry. This field indicates whether each entry was manually or automatically (batch) entered.

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 AR Detail, rather it can be used to determine whether this entry should be in the Open Receivables Lists. However, for completeness checking purposes, the Open Receivables 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 AR Detail, rather it can be used to determine whether this entry should be in the Open Receivables Lists. However, for completeness checking purposes, the Open Receivables 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:

  1. Mapping from Document Type (BLART). For example RE=invoice, KZ=payment, KG=credit memo, etc
  2. Mapping from TCODE. 
  3. Mapping from Posting Key (BSCHL). Here is an example mapping from Posting Key to Entry Type.
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 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:

  1. 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
  2. A mapping from Document Type (BLART). Some document types such as AA, SA are considered manual entry
  3. A formula involving multiple columns such as TCODE=F* AND KOART=S
  4. Some other formula


File 2

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 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 AR 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 AR Detail file

Note: Although the BSID / BSAD tables represent a current open / closed view, they may not necessarily coincide with the analysis period. 

File 3

Customer list (optional)

This file provides names for each customer.

MindBridge column name
SAP field
SAP description
Required
Used in analytics
Notes
Customer Name KNA1.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.

Customer ID KNA1.KUNNR Customer Number No Yes

*For SAP data sets, Customer ID is strongly suggested. The Customer 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.

Customer Address

KNA1.*

Various KNA1.* address fields No No

Concatenate the various address fields from the KNA1 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 customer is a related party. This data is typically not available in SAP systems.

 


File 4

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.

The primary key for KNC1 includes MANDT, KUNNR, BUKRS, GJAHR. You may need to include these so that each row is well defined.

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.
MindBridge column name
SAP field
SAP description
Required
Used in analytics
Notes
Customer Name KNC1.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 KNC1.UMSAV Balance Carried Forward in Local Currency Yes Yes Opening balance in local currency. Negative for credit, positive for debit.
Customer ID KNC1.KUNNR Customer Number No Yes

*For SAP data sets, Customer ID is strongly suggested. The Customer 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 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 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 Customer 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 AR 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 AR Detail file

TCODEs

Depending on your implementation of SAP, the TCODEs FBL3N, FBL5N, 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.

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.