SAP Order-to-Cash


Process description

Order-to-Cash concerns the set of business processes from receiving and processing sales orders for goods and services to payment. The Order-to-Cash process starts from the Sales order and completes at Payment received from the customer. The most important artifacts of the Order-to-Cash process are sales orders, goods delivery, billing, and payment.

More information about what is available in the resulting app, can be found here.

Extraction

System configuration

The data extraction is developed for SAP and is using Theobald Xtract Universal. Follow the general steps as described in Loading data using Theobald Xtract Universal (Automation Cloud) or Loading data using Theobald Xtract Universal (SQL Server) to set up data loading using Theobald Xtract Universal.

Importing the template extractions

  1. Download the XU_template_extractions.zip file with template extractions for O2C for Automation Cloud or the XU_template_extractions_AS.zip file with template extractions for O2C 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 in the config.json file.

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 HH24MISS
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
BSAD ADRP
BSE_CLR KNA1
BSID MAKT
CDHDR T001
CDPOS T001L
LIKP T001W
LIPS T005T
VBAK T005U
VBAP T006A
VBEP T008T
VBRK T023T
VBRP T041CT
TCURF
TCURR
TCURX
TSPAT
TVAGT
TVAKT
TVAPT
TVFST
TVGRT
TVKBT
TVKOT
TVLST
TVST
TVTWT
USR02
USR21
VBKD

All Master Data tables are optional for the SAP Order-to-Cash app template. If these tables are not available, the app template will still work, but some of the data, like names and descriptions for certain codes, will not be available.

ADCP

Contains address master data.

Field Type Description
ADDRNUMBER text Address ID
CLIENT text Client number
DATE_FROM date Start date of period this record belongs to
DATE_TO date End date of period this record belongs to
DEPARTMENT text Department
FUNCTION text Function
NATION text ID for international addresses, referring to different character sets
PERSNUMBER text User ID

Filtering: table is filtered on records where NATION = NULL or I.

ADRP

Contains personal master data.

Field Type Description
CLIENT text Client number
DATE_FROM date Start date of period this record belongs to
DATE_TO date End date of period this record belongs to
NAME_FIRST text First name
NAME_LAST text Last name
NATION text ID for international addresses, referring to different character sets
PERSNUMBER text User ID

Filtering: table is filtered on records where NATION = NULL or I.

BKPF

Contains accounting document header transactional data.

Field Type Description
AWKEY text Object key
BELNR text Accounting document number
BLART text Document type
BUKRS text Company code
CPUDT date Day of entry
CPUTM time Time of entry
GJAHR text Fiscal year
MANDT text Client number
USNAM text User ID

BSAD

Contains open invoice transactional data.

Field Type Description
BELNR text Accounting document number
BUKRS text Company code
BUZEI text Number of line item within accounting document
GJAHR text Fiscal year
MANDT text Client number

Filtering: Table is filtered on records where BUZEI = 001

BSE_CLR

Contains payment transactional data.

Field Type Description
BELNR text Accounting document number (of payment item)
BELNR_CLR text Accounting document number
BUKRS text Company code (of payment item)
BUKRS_CLR text Company code
BUZEI text Number of line item within accounting document
CLRIN text Type of clearing
GJAHR text Fiscal year (of payment item)
GJAHR_CLR text Fiscal year
INDEX_CLR text Sequential Number for Clearing Information
MANDT text Client number
SHKZG text Debit/credit indicator
WAERS text Document currency key
WRBTR double Amount in document currency

Filtering: Table is filtered on records where BUZEI = 001 and CLRIN = null or 2

BSID

Contains cleared invoice transactional data.

Field Type Description
BELNR text Accounting document number
BUKRS text Company code
BUZEI text Number of line item within accounting document
GJAHR text Fiscal year
MANDT text Client number

Filtering: Table is filtered on records where BUZEI = 001

CDHDR

Contains header data about document changes, like the user who made the change. Each record in this table can have multiple records in the CDPOS table.

Field Type Description
CHANGENR text Document ID of the change
MANDANT text Client number
OBJECTCLAS text Object class
OBJECTID text Object ID
UDATE date Date on which the change happened
USERNAME text User name ID
UTIME time Time on which the change happened

CDPOS

Contains the actual changes on documents, storing both old and new values of a specific field within a document. Multiple records can belong to the same CDHDR record.

Field Type Description
CHANGENR text Document ID of the change
CHNGIND text Type of the change, which can be U, I, S or D
FNAME text Name of the field that was changed
MANDANT text Client number
OBJECTCLAS text Object class
OBJECTID text Object ID
TABKEY text Document ID of the record that changed
TABNAME text Table name of the record that changed
VALUE_NEW text New contents
VALUE_OLD text Old contents

KNA1

Contains general customer master data.

Field Type Description
KUNNR text Customer number
LAND1 text Country key
NAME1 text Name 1
MANDT text Client number
NAME2 text Name 2
REGIO text Region

MAKT

Contains material description master data.

Field Type Description
MANDT text Client number
MATNR text Material number
MAKTX text Material description
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

LIKP

Contains delivery document header transactional data.

Field Type Description
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
MANDT text Client number
VBELN text Delivery document header ID
VBTYP text Delivery document category

Filtering: the table is filtered on records where VBTYP = J.

LIPS

Contains delivery item transactional data.

Field Type Description
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
MANDT text Client number
POSNR text Delivery item ID
VBELN text Delivery document header ID
VGBEL text Sales order header ID
VGPOS text Sales order item ID

T001

Contains company code master data.

Field Type Description
MANDT text Client number
BUKRS text Company code
BUTXT text Description of company code
WAERS text Currency key

T001L

Contains storage location master data.

Field Type Description
MANDT text Client number
WERKS text Plant
LGOBE text Description of storage location
LGORT text Storage location

T001W

Contains plants master data.

Field Type Description
MANDT text Client number
WERKS text Plant
NAME1 text Name of the plant

T005T

Contains company code master data.

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

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

T005U

Contains customer master data.

Field Type Description
MANDT text Client number
BEZEI text Country name
BLAND text Country ISO code
LAND1 text Country key
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

T006A

Contains unit of measurement master data.

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

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

T008T

Contains payment block reason master data.

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

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

T023T

Contains material group master data.

Field Type Description
MANDT text Client number
MATKL text Material group
WGBEZ text Description of the material group
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

T041CT

Contains billing cancellation reason master data.

Field Type Description
MANDT text Client number
STGRD text Cancellation reason
TXT40 text Description of cancellation reason
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TCURF

Contains exchange rate master data, used to convert amounts from one currency to another.

Field Type Description
FCURR text From currency
FFACT double From factor
GDATU date Validity date
MANDT text Client number
TCURR text To currency
TFACT double To factor
KURST text Exchange rate type

Filtering: table is filtered on KURST, based on the exchange_rate_type dbt variable defined.

TCURR

Contains currency exchange rate master data, used to convert currency amounts to a single currency.

Field Type Description
FCURR text Currency key
GDATU date Exchange rate date
MANDT text Client number
TCURR text Currency in which the amount is expressed
KURST text Exchange rate type
UKURS double Exchange rate

Filtering: table is filtered on KURST, based on the exchange_rate_type dbt variable defined.

TCURX

Contains exchange rate master data for currencies, used to convert currency values between different currencies.

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

TSPAT

Contains division master data.

Field Type Description
MANDT text Client number
SPART text Division
VTEXT text Description of division
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVAGT

Contains sales document rejection reason master data.

Field Type Description
MANDT text Client number
ABGRU text Reason for rejecting the sales document
BEZEI text Description of rejection reason
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVAKT

Contains sales document type master data.

Field Type Description
MANDT text Client number
AUART text Sales document type
BEZEI text Description of sales document type
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVAPT

Contains sales document item category master data.

Field Type Description
MANDT text Client number
PSTYV text Sales document item category
VTEXT text Description of sales document item category
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVFST

Contains billing blocking reason master data.

Field Type Description
MANDT text Client number
FAKSP text Reason for blocking the billing document
VTEXT text Description of billing block reason
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVGRT

Contains sales group master data.

Field Type Description
MANDT text Client number
VKGRP text Sales group
BEZEI text Description of sales group
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVKBT

Contains sales office master data.

Field Type Description
MANDT text Client number
VKBUR text Sales office
BEZEI text Description of sales office
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVKOT

Contains sales organization master data.

Field Type Description
MANDT text Client number
VKORG text Sales organization
VTEXT text Description of sales organization
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVLST

Contains delivery block reason master data.

Field Type Description
MANDT text Client number
LIFSP text Reason for blocking the delivery
VTEXT text Description of delivery block reason
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

TVST

Contains shipping point master data.

Field Type Description
MANDT text Client number
VSTEL text Shipping point
ALAND text Departure country

TVTWT

Contains distribution channel master data.

Field Type Description
MANDT text Client number
VTWEG text Distribution channel
VTEXT text Description of distribution channel
SPRAS text Language key

Filtering: table is filtered on SPRAS, based on the language dbt variable defined.

USR02

Contains user login master data.

Field Type Description
BNAME text User name ID
MANDT text Client number
USTYP text User type

USR21

Contains user master data.

Field Type Description
ADDRNUMBER text Address ID
BNAME text User name ID
MANDT text Client number
PERSNUMBER text User ID

VBAK

Contains sales order transactional data.

Field Type Description
AUART text Sles order type
BUKRS_VF text Company code to be billed
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
KUNNR text Customer
MANDT text Client number
SPART text Division
VBELN text Sales order document header ID
VBTYP text Sales document category
VDATU date Requested delivery date
VKBUR text Sales office
VKGRP text Sales group
VKORG text Sales organization
VTWEG text Distribution channel

Filtering: the table is filtered on records where VBTYP = C.

VBAP

Contains sales order item transactional data.

Field Type Description
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
KWMENG double Quantity
LGORT text Storage location
MANDT text Client number
MATKL text Material group
MATNR text Material
NETWR double Value
POSNR text Sales order item ID
PRCTR text Profit center
PSTYV text Category of the order
VBELN text Sales order document header ID
WAERK text Document currency
WERKS text Plant

VBEP

Contains sales order schedule line transactional data.

Field Type Description
EDATU date Requested delivery date
ETENR text Sales order item schedule line ID
MANDT text Client number
POSNR text Sales order item ID
VBELN text Sales order document header ID

VBKD

Contains sales document business data transactional data.

Field Type Description
MANDT text Client number
VBELN text Sales document number
POSNR text Item number of the sales document
PRSDT date Date on which the item was entered
ZTERM text Payment terms
INCO1 text Incoterms (part 1)

VBRK

Contains invoice transactional data.

Field Type Description
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
FKTYP text Invoice type
MANDT text Client number
SFAKN text Invoice cancellation header ID
STGRD text Cancellation reason
VBELN text Invoice document header ID
VBTYP text Sales document type

Filtering: the table is filtered on records where VBTYP = M or N and FKTYP != P.

VBRP

Contains invoice item transactional data.

Field Type Description
ERDAT date Creation date of the document
ERNAM text User that created the document
ERZET time Creation time of the document
MANDT text Client number
POSNR text Invoice item ID
VBELN text Invoice document header ID
VGBEL text Delivery document header ID
VGPOS text Delivery item ID

Configuring transformations

Seed files

Activity_configuration

This seed file is used to define some properties for an activity. In the app template all activities are referenced using an Activity_code. The seed file gives an opportunity to easily configure the name, the order and the category for each activity.

Field Type Description
Activity_code Text Identifier for an activity
Activity Text Display name for the activity
Activity_order Integer Order for the activity (in case multiple activities occur on the same time)
Activity_category Text Category for the activity

Dbt variables

The following variables can be configured in the dbt_project.yml file.

Variable Type Description
accounting_document_type Text Set of accounting document type codes used for incoming payments. Example: ('DZ')
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

Design specifications

Entities

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

Entity Input Data Filtered records
Accounting documents BKPF, BSAD, BSID
Deliveries LIKP
Delivery items LIPS
Invoice cancellations VBRK VBTYP = N
Invoice items VBRP
Invoices VBRK VBTYP = M
Payments BKPF, BSE_CLR
Sales order items VBAP
Sales order item schedule line VBEP
Sales orders VBAK

Activities

This is an overview of all default activities.

Activity Entity Description
Change delivery billing date Delivery Billing date has changed, based on the change log where FNAME = FKDAT
Change delivery shipping date Delivery Shipping date has changed, based on the change log where FNAME = LFDAT
Set delivery billing block Delivery Billing block of the delivery is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSK
Remove delivery billing block Delivery Billing block of the delivery is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSK
Set delivery credit block Delivery Credit block of the delivery is set, based on VALUE_NEW from the change log where FNAME = CMGST
Remove delivery credit block Delivery Credit block of the delivery is removed, based on VALUE_NEW from the change log where FNAME = CMGST
Set delivery shipping block Delivery Shipping block of the delivery is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSK
Remove delivery shipping block Delivery Shipping block of the delivery is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSK
Post goods issue Delivery Goods issue was posted, based on VALUE_NEW from the change log where FNAME = WBSTK
Cancel goods issue Delivery Goods issue was cancelled, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = WBSTK
Receive proof of delivery Delivery Proof of delivery was received, based on the change log where FNAME = PODAT
Create delivery item Delivery item The delivery item is created, based on the ERDAT and ERZET fields of the entity
Change delivery item quantity Delivery item Quantity has changed, based on the changelog where FNAME = LFIMG
Set delivery item billing block Delivery item Billing block of the delivery item is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSP
Remove delivery item billing block Delivery item Billing block of the delivery item is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSP
Create sales order Sales order The sales order is created, based on the ERDAT and ERZET fields of the entity
Change sales order document currency Sales order Document currency has changed, based on the change log where FNAME = WAERK
Change sales order exchange rate Sales order Exchange rate has changed, based on the change log where FNAME = KURSK
Change sales order payment terms Sales order Payment terms have changed, based on the change log where FNAME = ZTERM
Change sales order price date Sales order Price date has changed, based on the change log where FNAME = PRSDT
Change sales order requested delivery date Sales order Requested delivery date has changed, based on the change log where FNAME = VDATU
Set sales order billing block Sales order Billing block of the sales order is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSK
Remove sales order billing block Sales order Billing block of the sales order is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSK
Set sales order credit block Sales order Credit block of the sales order is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = CMGST
Remove sales order credit block Sales order Credit block of the sales order is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = CMGST
Set sales order delivery block Sales order Delivery block of the sales order is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSK
Remove sales order delivery block Sales order Delivery block of the sales order is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSK
Create sales order item Sales order item The sales order item is created, based on the ERDAT and ERZET fields of the entity
Change sales order item payment terms Sales order item Payment terms have changed, based on the change log where FNAME = ZTERM
Change sales order item price Sales order item Price has changed, based on the change log where FNAME = NETPR
Change sales order item price date Sales order item Price date has changed, based on the change log where FNAME = PRSDT
Change sales order item exchange rate Sales order item Exchange rate has changed, based on the change log where FNAME = KURSK
Reject sales order item Sales order item The sales order item is rejected, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ABGRU
Restore sales order item Sales order item The sales order item is restored, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = ABGRU
Set sales order item billing block Sales order item Billing block of the sales order item is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSP
Remove sales order item billing block Sales order item Billing block of the sales order item is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = FAKSP.
Set sales order item delivery block Sales order item Delivery block of the sales order item is set, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSP
Remove sales order item delivery block Sales order item Delivery block of the sales order item is removed, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = LIFSP
Change sales order item ordered quantity Sales order item Quantity has changed, based on the change log where FNAME = WMENG
Change sales order item confirmed quantity Sales order item Confirmed quantity has changed, based on the change log where FNAME = BMENG
Change sales order item expected delivery date Sales order item Expected delivery date has changed, based on the change log where FNAME = EDATU
Cancel invoice Invoice cancellation Invoice has been cancelled, based on the VBRK records where VBTYP = N
Change invoice date Invoice Invoice date has changed, based on the change log where FNAME = FKDAT
Create invoice item Invoice item The invoice item is created, based on the ERDAT and ERZET fields of the entity
Change invoice item cash discount Invoice item Cash discount has changed, based on the change log where FNAME = SKFBP
Create incoming payment Payment Incoming payment is created, based on the CPUDT and CPUTM fields of the entity
Create outgoing payment Payment Outgoing payment is created, based on the CPUDT and CPUTM fields of the entity
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 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 = BVTYP or HBKID
Cancel payment Accounting document Payment has been cancelled, based on VALUE_OLD and VALUE_NEW from the change log where FNAME = AUGBL

Design details

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: