SAP Accounts Payable

Process description

The Accounts Payable process is a process which revolves around receiving invoices and paying vendors and is part of the purchase-to-pay process.

The end-to-end process typically starts with a purchase request and ends with the payment of the invoice, covering the following processes in the procurement lifecycle:

The Accounts Payable app template focusses mostly on the Payment process.

Buying

The process starts with a buyer from the company who wants to buy some goods. The buyer requests the purchase, using a purchase requisition. Procurement is involved to get the best deal. This could mean negotiation for a new contract or adapting to an already available contract. Procurement converts the requisition to a purchase order and makes sure the missing information is filled where needed. Procurement approves the purchase order (PO), which is then sent to the vendor.

Delivery

The warehouse department (if available) waits for the goods to be received. When goods arrive, the inventory is checked and updated. The goods are received by the initial buyer, who accepts the goods.

Payment

The finance department awaits the invoice to be received. When the invoice arrives, it is administrated. The invoice is paid by accounts payable. Accounting administrates the payment.

Available tags & due dates

The following tags are available by default.

Tag Description
Invoice received before goods received The invoice was received before the goods were received.

The following due dates are available by default.

Due date Description
Late payment The invoice was paid too late, after the net payment period expired.

Extraction

System configuration

This app template uses the Material Management (MM) and Financial Accounting (FI) modules of SAP.

Extraction tool configuration

The data extraction is developed for SAP and is using Theobald Xtract Universal. In general, you should follow the steps as described in Loading Data Using Theobald Xtract Universal (Automation Cloud) to set up data loading using Theobald Xtract Universal. Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option on the documentation page. In addition, select the Automation Suite version you are using to open the appropriate Loading Data Using Theobald Xtract Universal page.

Importing the template extractions

  1. Download the XU_template_extractions.zip file with template extractions for Automation Cloud or the XU_template_extractions_as.zip file with template extractions for Automation Suite.
  2. This file contains the folders destinations, extractions, and sources. Copy the folders from the downloaded zip file to the config folder of Xtract Universal. The location of this folder is [XU_installation_path]/config.
  3. Open Xtract Universal Designer and click on the refresh button to load all extractions.

Configuring the extraction script

  1. Download the extract_theobald.zip to load data for Automation Cloud or extract_theobald_as.zip to load data for Automation Suite.
  2. This file contains the extract_theobald.ps1 script and the config.json file. Unzip the content on the server where Theobald Xtract Universal is installed.
  3. Configure the variables for the script.

Running the extraction script

The extraction script can be executed by calling the Powershell script extract_theobald.ps1. In case you are not authorized to run scripts on your system, you can temporarily bypass the check by calling the following command before executing the script: Set-ExecutionPolicy Bypass -Scope Process


Input data

Input types

The following table provides an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
date YYYYMMDD
time hhmmss
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Input tables & fields

The following tables are extracted from the source system:

Transactional Data Master Data
BKPF ADCP
BSAK ADRP
BSE_CLR CSKT
BSIK KNA1
CDHDR LFA1
CDPOS MAKT
EBAN T001
EKBE T001L
EKET T001W
EKKN T003T
EKKO T005T
EKPO T005U
RBKP T006A
RSEG T008T
T023T
T024
T024E
T052U
T161T
TCURF
TCURR
TCURX
TGSBT
USR02
USR21

Purchase requisition tables

EBAN

Stores Purchase Requisition Data.

Field Type Description
AFNAM text Requisitioner name
BADAT date Requisition date
BANFN text Purchase requisition item number
BANPR text Requisition processing date
BNFPO text Cross-System Purchase requisition item
BSART text Purchase requisition document type
BSTYP text Purchasing document category
ERNAM text Name of person who created the object
MANDT text Client
MATKL text Material group
MATNR text Material number
MEINS text Purchase requisition unit of measure
MENGE double Purchase requisition quantity
PREIS double Price in purchase requisition
WAERS text Currency key

Filtering: BSTYP = 'B', Date: BADAT

Purchasing document tables

EKKO

Stores Purchasing Document Header Data.

Field Type Description
AEDAT date Record creation data
BSART text Purchasing document type
BSTYP text Purchasing document category
BUKRS text Company code
EBELN text Purchasing document number
EKGRP text Purchasing group
EKORG text Purchasing organization
ERNAM text Name of person who created the object
LIFNR text Vendor account number
MANDT text Client
PROCSTAT text Purchasing document processing state
WAERS text Currency key
WKURS currency Exchange rate

Filtering: BSTYP = 'F', Date: AEDAT

EKPO

Stores Purchasing Document Item Data.

Field Type Description
BANFN text Purchase requisition item number
BNFPO text Cross-System Purchase requisition item
EBELN text Purchasing document number
EBELP text Purchasing document item number
KUNNR text Customer
LGORT text Storage location
MANDT text Client
MATKL text Material group
MATNR text Material number
MEINS text Purchase Order unit of measure
MENGE double Purchase order quantity
NETWR double Net order value in Purchase order currency
PRDAT date Date of price determination
WERKS text Plant
BSTYP text Purchasing document category

Filtering: BSTYP = 'F'

EKBE

Stores History per Purchasing Document Data.

Field Type Description
BELNR text Material document number
BUZEI text Material document item
CPUDT date Accounting document creation date
CPUTM time Time of entry
EBELN text Purchasing document number
EBELP text Purchasing document item number
ELIKZ text "Delivery Completed" indicator
ERNAM text Name of person who created the object
MANDT text Client
BEWTP text Purchase Order history category
SHKZG text Debit/Credit Indicator

Filtering: BEWTP = 'E' AND SHKZG = 'S', Date: CPUDT

EKET

Stores Scheduling Agreement Schedule Lines Data.

Field Type Description
EBELN text Purchasing document number
EBELP text Purchasing document item number
EINDT date Item delivery date
MANDT text Client
SLFDT date Statistics-Relevant delivery date
EKKN

Stores Account Assignment in Purchasing Document Data.

Field Type Description
EBELN text Purchasing document number
EBELP text Purchasing document item number
GSBER text Business area
KOKRS text Controlling area
KOSTL text Cost centre
MANDT text Client

Invoice tables

RBKP

Stores Document Header Invoice Receipt Data.

Field Type Description
BELNR text Invoice document number
BUKRS text Company code
CPUDT date Accounting document creation date
CPUTM time Time of entry
GJAHR text Fiscal year
MANDT text Client
USNAM text User name

Filtering: Date: CPUDT

RSEG

Stores Document Item Incoming Invoice Data.

Field Type Description
BELNR text Invoice document number
BUZEI text Invoice document item
EBELN text Purchasing document number
EBELP text Purchasing document item number
GJAHR text Fiscal year
MANDT text Client

Accounting tables

BKPF

Stores Accounting Document Header Data.

Field Type Description
AWKEY text Reference key
BELNR text Accounting document number
BLART text Document type
BLDAT date Document date
BUDAT date Document posting date
BUKRS text Company code
CPUDT date Accounting document creation date
CPUTM time Time of entry
GJAHR text Fiscal year
KURSF text Exchange rate
MANDT text Client
USNAM text User name
WAERS text Currency key

Filtering: Date: CPUDT

BSIK

Stores Accounting Secondary Index for Vendors Data.

Field Type Description
BELNR text Accounting document number
BUKRS text Company code
BUZEI text Accounting document item
BVTYP text Partner bank type
EBELN text Purchasing document number
EBELP text Purchasing document item number
GJAHR text Fiscal year
HBKID text Short key for a house bank
MANDT text Client
WRBTR double Amount in document currency
ZBD1T integer Discount period 1
ZBD2T integer Discount period 2
ZBD3T integer Net payment period
ZBD1P double Discount percentage 1
ZBD2P double Discount percentage 2
ZFBDT date Baseline date for due date calculation
ZTERM text Terms of payment key
ZUONR text Assignment number

Filtering: BUZEI = 001, Date: CPUDT

BSAK

Stores Accounting Secondary Index for Vendors (Cleared Items) Data.

Field Type Description
BELNR text Accounting document number
BUKRS text Company code
BUZEI text Accounting document item
BVTYP text Partner bank type
EBELN text Purchasing document number
EBELP text Purchasing document item number
GJAHR text Fiscal year
HBKID text Short key for a house bank
MANDT text Client
WRBTR double Amount in document currency
ZBD1T integer Discount period 1
ZBD2T integer Discount period 2
ZBD3T integer Net payment period
ZBD1P double Discount percentage 1
ZBD2P double Discount percentage 2
ZFBDT date Baseline date for due date calculation
ZTERM text Terms of payment key
ZUONR text Assignment number

Filtering: BUZEI = 001, Date: CPUDT

BSE_CLR

Stores Additional Data for Document Segment Clearing Information Data.

Field Type Description
BELNR text Accounting document number
BELNR_CLR text Accounting document number
BUKRS text Company code
BUKRS_CLR text Company code
BUZEI text Accounting document line item number
CLRIN text Type of clearing
GJAHR text Fiscal year
GJAHR_CLR text Fiscal year
INDEX_CLR text Sequential number for clearing information
MANDT text Client
SHKZG text Debit/Credit Indicator
WAERS text Currency key
WRBTR double Amount in document currency

Filtering: BUZEI = 001 AND (CLRIN = '' or CLRIN = 2)

Change log tables

CDHDR

Stores Change Document Header Data.

Field Type Description
CHANGENR text Document change number
MANDANT text Client
OBJECTCLAS text Object class
OBJECTID text Object value
UDATE date Change document creation dated
USERNAME text User name responsible in change document
UTIME time Time changed

Filtering: OBJECTCLAS in ["BANF", "EINKBELEG", "INCOMINGINVOICE", "BELEG"], Date: UDATE

CDPOS

Stores Change Document Item Cluster Data.

Field Type Description
CHANGNR text Document change number
CHNGIND text Change Type (U, I, S, D)
FNAME text Field name
MANDANT text Client
OBJECTCLAS text Object class
OBJECTID text Object value
TABKEY text Changed table record key
TABNAME text Table name
VALUE_NEW text New contents of changed field
VALUE_OLD text Old contents of changed field

Filtering: FNAME in ["KEY", "BANPR", 'BLDAT', 'BUDAT', 'MENGE', "PROCSTAT", 'RMWWR', "FRGZU", "LOEKZ", "ABSKZ", "NETPR", 'WRBTR', "ZLSPR", "ZTERM", "ZFBDT", "ZUONR", "BVTYP", "HBKID", "AUGBL"] AND TABNAME in ["EBAN", "EKKO", "EKPO", "RBKP", "RSEG", "BSEG", "BSEGR"] AND CHANGENR >= '<value>' AND CHANGENR <= '<value>' (For example, AND CHANGENR >= '0001299707' AND CHANGENR <= '0001507899')

User master data tables

USR21

Stores User Name/Address Key Assignment Data

Field Type Description
ADDRNUMBER text Address number number
BNAME text User name in user master record
MANDT text Client
PERSNUMBER text Person number
ADRP

Stores Persons (Business Address Services) Data

Field Type Description
CLIENT text Client
DATE_FROM date Valid-from date
DATE_TO date Valid-to date
NAME_FIRST text First name
NAME_LAST text Last name
PERSNUMBER text Person number
NATION text Version ID for international addresses

Filtering: NATION = '' or NATION = 'I'

ADCP

Stores Person/Address Assignment (Business Address Services) Data

Field Type Description
ADDRNUMBER text Address number
CLIENT text Client
DATE_FROM date Valid-from date
DATE_TO date Valid-to date
DEPARTMENT text Department
FUNCTION text Function
PERSNUMBER text Person number
NATION text Version ID for international addresses

Filtering: NATION = '' or NATION = 'I'

USR02

Stores Logon Data

Field Type Description
BNAME text User name in user master record
MANDT text Client
USTYP text User type

Supplier master data tables

LFA1

Stores General Vendor Master Data

Field Type Description
LAND1 text Country key
LIFNR text Vendor account number
MANDT text Client
NAME1 text Name 1
NAME2 text Name 2
REGIO text Region (State, Province, County)
XCPDK text Indicator: one-time account?
T003T

Stores Document Type Texts

Field Type Description
BLART text Document type
LTEXT text Country name
MANDT text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

T005T

Stores Country Names Data

Field Type Description
LAND1 text Country key
LANDX text Country name
MANDT text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

T005U

Stores Taxes Region Key Texts Data

Field Type Description
BEZEI text Description
BLAND text Region (State, Province, County)
LAND1 text Country key
MANDT text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

T052U

Stores Own Explanations for Terms of Payment

Field Type Description
MANDT text Client
TEXT1 text Payment terms name
ZTAGG text Day limit of payment terms
ZTERM text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

Customer master data tables

KNA1

Stores General Vendor Master Data

Field Type Description
KUNNR text Customer number
LAND1 text Country key
MANDT text Client
NAME1 text Name 1
NAME2 text Name 2
REGIO text Region (State, Province, County)

Material master data tables

MAKT

Stores Material Descriptions Data

Field Type Description
MAKTX text Material Description (Short Text)
MANDT text Client
MATNR text Material number
SPRAS text Language key

Filtering: SPRAS = 'Language'

T023T

Stores Material Group Descriptions Data

Field Type Description
MANDT text Client
MATKL text Material group
WGBEZ text Material group description
SPRAS text Language key

Filtering: SPRAS = 'Language'

T001L

Stores Storage Locations Data

Field Type Description
LGOBE text Storage location description
LGORT text Storage location
MANDT text Client
WERKS text Plant
T006A

Stores Assign Internal to Language-Dependent Unit Table and Data

Field Type Description
MANDT text Client
MSEHI text Unit of measurement
MSEHT text Unit of measurement text
SPRAS text Language key

Filtering: SPRAS = 'Language'

Organizational structure master data tables

CSKT

Stores Cost Center Texts Data

Field Type Description
KOKRS text Controlling area
KOSTL text Cost center
KTEXT text General name
MANDT text Client
SPRAS text Language key
DATBI text Valid to date

Filtering: SPRAS = 'Language' AND DATBI = "99991231"

T001

Stores Company Codes Data

Field Type Description
BUKRS text Company code
BUTXT text Company (code) name
MANDT text Client
WAERS text Currency key
T001W

Stores Plants/Branches Data

Field Type Description
MANDT text Client
NAME1 text Name
WERKS text Plant
T024

Stores Purchasing Groups Data

Field Type Description
EKGRP text Purchasing group
EKNAM text Purchasing group description
MANDT text Client
T024E

Stores Purchasing Organizations Data

Field Type Description
EKORG text Purchasing organization
EKOTX text Purchasing organization description
MANDT text Client
TGSBT

Stores Business Area Names Data

Field Type Description
GSBER text Business area
GTEXT text Business area description
MANDT text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

Purchasing document master data tables

T161T

Stores Texts for Purchasing Document Types Data

Field Type Description
BATXT text Purchasing document type short description
BSART text Purchasing document type
BSTYP text Purchasing document category
MANDT text Client
SPRAS text Language key

Filtering: SPRAS = 'Language'

Block & Cancellation reasons master data tables

T008T

Stores Blocking Reason Names in Automatic Payment Transactional Data

Field Type Description
MANDT text Client
TEXTL text Explanation of the reason for payment block
ZAHLS text Block key for payment
SPRAS text Language key

Filtering: SPRAS = 'Language'

Currency master data tables

TCURR

Stores Exchange Rates Data

Field Type Description
FCURR text From currency
GDATU date Date as of which exchange rate is effective
MANDT text Client
TCURR text To currency
KURST text Exchange rate type
UKURS text Exchange rate

Filtering: KURST = 'Exchange rate type'

TCURF

Stores Conversion Factors Data

Field Type Description
FCURR text From currency
FFACT integer Ratio for the "from" currency units
GDATU date Block key for payment
MANDT text Client
TCURR text To currency
TFACT integer Ratio for the "to" currency units
KURST text Exchange rate type

Filtering: KURST = 'Exchange rate type'

TCURX

Stores Decimal Places in Currencies data

Field Type Description
CURRDEC integer Number of decimal places
CURRKEY text Currency key

Configuring transformations

Seed files

Currently no seed files are used for this app template.

Dbt variables

Variable Type Description
accounting_document_type Text Set of accounting document type codes used for incoming payments. Example: ('KZ')
date-format Text Date format used. The date format is dependent on the database used
exchange_rate_type Text Currency exchange rate type. Example: 'M'
language Text Language code from the SAP system
reporting_currency Text Currency which is used for reporting

Note that while there is a date format that can be configured in the dbt variables, there is no time format defined. The time format is implemented via a custom macro. This is done because SQL server does not support converting the 6 digits time format to time data type.


Design specifications

Entities

The following diagram shows all entities that are used for the process.

Process entities

Entity Input Data
Purchase requisition EBAN
Purchase order EKKO
Purchase order item EKPO
Goods receipt EKBE
Invoice RBKP
Invoice item RSEG
Accounting document BKPF, BSAK, BSIK
Payment BKPF, BSE_CLR

Master data entities

Entity Input Data
Business area TGSBT
Company T001
Cost center CSKT
Currency conversion factor TCURF
Currency decimal places TCURX
Currency exchange rate TCURR
Customer KNA1, T005T, T005U
Invoice document type T003T
Material MAKT
Material group T023T
Payment block reason T008T
Payment terms T052U
Plant T001W
Purchasing document type T161T
Purchasing group T024
Purchasing organization T024E
Storage location T001L
Supplier LFA1, T005T, T005U
Unit of measurement T006A
User address USR21, ADCP
User name USR21, ADRP
User type USR02

Activities

Activity Entity Description
Create purchase requisition Purchase requisition The purchase requisition is created, based on the creation date or time of insertion in the changelog.
Approve purchase requisition level Purchase requisition Purchase requistion is approved, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FRGZU
Revoke approved purchase requisition level Purchase requisition Purchase requistion approval is revoked, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FRGZU
Delete purchase requisition Purchase requisition Purchase requistion is deleted, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Restore purchase requisition Purchase requisition Purchase requistion is restored, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Reject purchase requisition Purchase requisition Purchase requistion is rejected, based on VALUE_NEW from the change log where FNAME = BANPR
Create purchase order Purchase order The purchase order is created, based on the creation date or time of insertion in the changelog.
Set purchase order block Purchase order Purchase order block is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Remove purchase order block Purchase order Purchase order block is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Delete purchase order Purchase order Purchase order is deleted, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Restore purchase order Purchase order Purchase order is restored, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Reject purchase order Purchase order Purchase order is rejected, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = PROCSTAT
Approve purchase order level Purchase order Purchase order is approved, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FRGZU
Revoke approved purchase order level Purchase order Purchase order appproval is revoked, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FRGZU
Create purchase order item Purchase order The purchase order item is created, based on the creation date of the purchase order or time of insertion in the changelog.
Set purchase order item block Purchase order Purchase order item block is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Remove purchase order item block Purchase order Purchase order item block is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Delete purchase order item Purchase order Purchase order item is deleted, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Restore purchase order item Purchase order Purchase order item is restored, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LOEKZ
Change purchase order item value Purchase order Purchase order item value is changed, based on the change log where FNAME = NETPR
Reject purchase order item by supplier Purchase order Purchase order item is rejected by the supplier, based on VALUE_NEW from the change log where FNAME = ABSKZ
Post goods receipt Goods receipt Goods receipt was posted, based on the CPUDT and CPUTM fields of the entity.
Set invoice item payment block Invoice Invoice payment block is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ZLSPR
Remove invoice item payment block Invoice Invoice payment block is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ZLSPR
Create invoice item Invoice The invoice item is created, based on the CPUDT and CPUTM fields of the entity.
Change invoice document date Invoice Invoice document date is changed, based on the change log where FNAME = BLDAT
Change invoice posting date Invoice Invoice posting date is changed, based on the change log where FNAME = BUDAT
Change invoice item price Invoice Invoice item price is changed, based on the change log where FNAME = RMWWR
Change invoice item quantity Invoice Invoice item quantity is changed, based on the change log where FNAME = MENGE
Create accounting document Accounting document The accounting document is created, based on the CPUDT and CPUTM fields of the entity.
Set payment block Accounting document Payment block is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ZLSPR
Remove payment block Accounting document Payment block is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ZLSPR
Change document date Accounting document Document date is changed, based on the change log where FNAME = BLDAT
Change posting date Accounting document Posting date is changed, based on the change log where FNAME = BUDAT
Change invoice value Accounting document Invoice value is changed, based on the change log where FNAME = WRBTR
Change payment terms Accounting document Payment terms have changed, based on the change log where FNAME = ZTERM
Change payment due date Accounting document Payment due date has changed, based on the change log where FNAME = ZFBDT
Change payment assignment Accounting document Payment assignment has changed, based on the change log where FNAME = ZUONR
Change payment bank data Accounting document Payment bank data has changed, based on the change log where FNAME is BVTYP or HBKID
Cancel payment Accounting document Payment is caneclled, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = AUGBL
Create incoming payment Accounting document An incoming payment created, based on the CPUDT and CPUTM fields of the entity
Create outgoing payment Accounting document An outgoing payment is created, based on the CPUDT and CPUTM fields of the entity

Design details

Accounting documents without invoice

The SAP system allows accounting documents in the BKPF table to be created in multiple ways. The default way is when an invoice (in the RBKP table) is created and released into accounting. However, it is also possible to create an accounting document without an invoice. In this case the accounting document is linked to purchase order items directly, using the EBELN and EBELP fields available on the BSIK and BSAK tables. This sub-process is used to gather additional events for the event log, which is done in the Entity_relations.sql table.

Change log

The Change log, created by joining the CDHDR and CDPOS tables, is not an entity on its own, but is used for all entities to generate the corresponding events. By default the Change log contains only changes for the default activities, gained by filtering on the FNAME field from the CDPOS table during data extraction.

For each entity its 'change events' are created as follows:

Automated events

An event can be labeled as automated. This is dependent on the user_type associated with the event. The user_type stems from the USR02 table. If the value of the field USTYP is B or C, meaning System or Communications Data, respectively, the event is labeled as 'Automated'.

Currency conversion

Price-related information is given in a certain currency. These prices and corresponding currency are stored in the document tables. These currencies vary between items. To be able to analyze all of them in one app template, all prices are converted from their document currency to a single currency, referred to as the reporting currency. This conversion is done based on the exchange rate information stored in the TCURR, TCURF, and TCURX tables. In case the exchange rates listed are an indirect rate, they contain a - to indicate this.

For both accounting documents and purchase order items the currency conversion takes place in two steps:

The local currency is the currency of the company. This local currency can be found in the company table T001. On the document table there is an exchange rate available to convert the document currency to the local currency. This exchange rate can be the same as the exchange rate in the currency exchange rate table TCURR or manually adjusted to any other rate. The conversion to the reporting currency is again done based on the exchange rate information in the currency information tables as mentioned above.

Troubleshooting