SAP Production-Planning

Process description
- Available tags & due dates
Extraction
- System configuration
- Extraction tool configuration
Input data
- Input types
- Input tables & fields
Configuring transformations
- Seed files
- Dbt variables
Design specifications

Process description

This app template was built for the SAP ECC Production Planning process. This is an end-to-end process that usually starts with the creation of a production order. Then, the production order is released and its operations are executed. After that, the production confirmation is created and the material movements (Post goods issue for material consumption and Post goods receipt of the finished product) are done. Finally, the production order is moved to 'Technically completed' status and the process ends.

This process includes the following types of Production orders:

Standard Production Orders
Process Orders
Re-work Orders

The graphic below briefly shows a standard SAP ECC Production Planning process:

Available tags & due dates

The following tags are available by default.

Tag	Description
Long-lasting open order	Production order items that are actively in 'Released' status for more than the regular time that it should take for that material and plant to process it. That regular time is calculated based on the sum of average time and standard deviation of the release-to-finish throughput time for each combination of material and plant. Orders that are still opened could keep the hold on the raw materials needed for them, besides having other unwanted effects on the overall process. Therefore, it's a good idea to understand why they're still opened and suggest their closure if possible.
Multiple Release statuses	Having the 'Release' status repeated multiple times means that there was some sort of reversal of the following statuses and the order went back to 'Released'. This is clearly a rework done to the order and it should be avoided.
Multiple Technically completed statuses	Having the 'Technically completed' status repeated multiple times means that there was some sort of reversal of the following statuses and the order went back to 'Technically completed'. This is clearly a rework done to the order and it should be avoided.
Order with change activities	Production order items that have their attributes changed. Changes to orders are generally frown upon and they are a good indicator of inefficiencies or activities that do not add any value.
Order with rework required	Production order items where production rework is required. This tag applies only to the original orders that were reworked (i.e. Re-work orders are filtered out). All rework activities show inefficiencies in the production process.
Production quantity deficit	Production order items where the production order generated less quantity than expected which can be understood as a lack of efficacy. It is worth the effort to understand why this is happening in those cases.
Scrap portion exceeded	Production order items which exceeded the portion of scrap estimated for that order. Creating more scrap than estimated means that the consumption of materials during the production process is greater than the standard. It's important to understand their root causes so they can be avoided.
Total order quantity changed after release	All changes in quantities done after the release of an order would render all calculations done by the system (e.g. raw materials availability) incorrect. Therefore, these changes could potentially impact the production process as they are bypassing the validations done by SAP during the release of the order.
Zero-quantity confirmations	Confirmations done with zero quantity entered do not usually make sense. Therefore, it requires attention to validate that these activities were correctly done or they are actually showing inefficiencies in the process.
Multiple users for same activity	A production order item has the same activity executed multiple times by different users.

The following due dates are available by default.

Due date	Description
Basic finish date	Compares the date the user defines as the finished date for the production order (basic finish date) with the date it actually finished.
Basic start date	Compares the date the user defines as the start date for the production order (basic start date) with the date it actually started.
Scheduled finished date	This due date compares the date when the system estimates the production order should finish (scheduled finish date, based on predefined production parameters) with the date it actually ended.
Scheduled release date	This due date compares the date when the system estimates the production order should be released (scheduled release date, based on predefined production parameters) with the date it actually got released.
Scheduled start date	This due date compares the date when the system estimates the production order should start (scheduled start date, based on predefined production parameters) with the date it actually started.

Extraction

System configuration

The SAP ECC Production Planning (PP) module is mandatory.

This app template contains models based on historical information. This information is contained in the following tables:

JCDS for status changes of both, orders and operations
CDHDR and CDPOS for change logs

The tracking of these changes in SAP ECC PP must be configured. Detailed instructions can be found in the section Instructions for enabling History Tracking in SAP ECC Production Planning.

For this app template, the specific fields which need to be enabled in SAP are the following:

CDPOS tracking

Table	Fields
AUFK	KTEXT
AFKO	GAMNG, TERKZ, GLTRP, GSTRP, GLUZP, GSUZP, GASMG, APRIO
AFPO	PSMNG, PSAMG, LGORT, DNREL, INSMK
AFVC	LTXA1

JCDS tracking of status

Select all desired types of Production orders.

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

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.
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.
Open Xtract Universal Designer and click on the refresh button to load all extractions.

Configuring the extraction script

Download the extract_theobald.zip file to load data for Automation Cloud or extract_theobald_as.zip to load data for Automation Suite.
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.
Configure the variables for the script.

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	`yyyy-mm-dd`
datetime	`yyyy-mm-dd hh:mm:ss[.ms]`, where `[.ms]` is optional
double	Decimal separator: `.` (dot); thousand separator: none
integer	Thousand separator: none
text	N/A

Input tables & fields

Transactional Data	Master Data
AFKO	ADCP
AFPO	ADRP
AFRU	MARC
AFVC	MARM
AUFK	MBEW
CDHDR	T001L
CDPOS	T001W
JCDS	T001
JEST	T003P
MSEG	T006A
	T156T
	T156
	TCA02
	TCURF
	TCURR
	TCURX
	TGSBT
	TJ02T
	TKA01
	TTZCU
	TTZDF
	TTZDV
	TTZD
	TTZR
	TTZZ
	USR02
	USR21

Transactional tables

AFKO

Header input table for Order data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`AUFNR`	text	Production order number (Primary Key).
`GASMG`	double	Expected scrap quantity
`GETRI`	date	Indicates the actual finish date for the order.
`GEUZI`	time	Indicates the actual finish time for the order.
`FTRMI`	date	Indicates the actual release date for the order.
`GSTRP`	date	Indicates the basic start date for the order.
`GSUZP`	time	Indicates the basic start time for the order.
`GLTRP`	date	Indicates the basic finish date for the order.
`GLUZP`	time	Indicates the basic finish time for the order.
`AUFPL`	text	Routing number of operations in the order.
`GSTRS`	date	Indicates the scheduled start date for the order.
`GSUZS`	time	Indicates the scheduled start time for the order.
`GLTRS`	date	Indicates the scheduled finish date for the order.
`GLUZS`	time	Indicates the scheduled finish time for the order.
`FTRMS`	date	Indicates the scheduled release date for the order.
`IASMG`	double	Confirmed scrap quantity
`RMNGA`	integer	Indicates the number of confirmed rework quantity in the order.

Filtering: This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with AUFK table to bring the filter by date when loaded from source.

AFPO

Item input table for Order data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`POSNR`	text	Production order item number (Primary Key).
`AUFNR`	text	Production order number (Primary Key).
`AMEIN`	text	Foreign key identifying the base unit of measure in-house.
`GSBER`	text	Foreign key identifying the business area.
`UMREN`	double	Denominator to convert into base unit of measure.
`WEMNG`	double	Indicates the goods received quantity.
`MATNR`	text	Foreign key identifying the material number.
`MEINS`	text	Foreign key identifying the base unit of measure.
`UMREZ`	double	Numerator to convert into base unit of measure.
`OBJNP`	text	Object number for the order item.
`DAUTY`	text	Foreign key identifying the order category.
`PSMNG`	double	Indicates the production order item quantity.
`DAUAT`	text	Foreign key identifying the order type.
`PLNUM`	text	Planned production order number.
`DWERK`	text	Foreign key identifying the order item's plant.
`PSAMG`	double	Indicates the production order item scrap quantity.
`LGORT`	text	Foreign key identifying the storage location.

Filtering: This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with AUFK table to bring the filter by date when loaded from source.

AFRU

Input table for Order confirmations.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`RUECK`	text	Completion confirmation number for the operation (Primary Key).
`RMZHL`	text	Confirmation counter for the operation (Primary Key).
`GMEIN`	text	Foreign key identifying the base unit of measure.
`STZHL`	integer	Cancelled confirmation indicator.
`LTXA1`	text	Description for the confirmation.
`IEDD`	date	Confirmed date for the operation's execution finish.
`ISDD`	date	Confirmed date for the operation's execution start.
`ERSDA`	date	Confirmation entry date.
`ERZET`	time	Confirmation entry time.
`MEINH`	text	Foreign key identifying the unit of measure for the confirmation.
`IEDZ`	time	Confirmed time for the operation's execution finish.
`ISDZ`	time	Confirmed time for the operation's execution start.
`APLZL`	text	General counter for order.
`AUFNR`	text	Foreign key identifying the production order number.
`AUERU`	text	Partial-final confirmation indicator.
`GMNGA`	double	Indicates the quantity confirmed.
`STOKZ`	text	Reverse indicator.
`AUFPL`	text	Routing number of operations in the order.
`XMNGA`	double	Indicates the scrap confirmed.
`ERNAM`	text	Name of the person who created the record.

Filtering: This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with AUFK table to bring the filter by date when loaded from source.

AFVC

Input table for Operations within an order.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`APLZL`	text	General counter for order (Primary Key).
`AUFPL`	text	Routing number of operations in the order (Primary Key).
`OBJNR`	text	Object number for the operation.
`LTXA1`	text	Indicates the operation name.
`PLNTY`	text	Foreign key identifying the operation type.

Filtering: This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with AFKO table to bring the filter by date when loaded from source.

AUFK

Input table for Order data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`AUFNR`	text	Production order number (Primary Key).
`BUKRS`	text	Foreign key identifying the company code.
`KOKRS`	text	Foreign key identifying the controlling area.
`ERDAT`	date	Date on which the record was created.
`ERFZEIT`	time	Time on which the record was created.
`OBJNR`	text	Object number for the order.
`AUTYP`	text	Indicates the order category.
`AUART`	text	Indicates the order type.
`ERNAM`	text	Name of the person who created the record.

Filtering: Date: ERDAT and AUTYP in ('10', '40').

CDHDR

Header input table for object changes.

Field	Type	Description
`MANDANT`	text	Client Identifier (Primary Key).
`CHANGENR`	text	Change record identifier (Primary Key).
`OBJECTCLAS`	text	Object class category (Primary Key).
`OBJECTID`	text	Object value (Primary Key).
`UDATE`	date	Date the change was executed.
`UTIME`	time	Time the change was executed.
`USERNAME`	text	User who executed the change.

Filtering: Date: UDATE.

CDPOS

Log table for field change events for Work Orders, Operations and Notifications.

Field	Type	Description
`TABKEY`	text	Change table record identifier (Primary Key).
`CHNGIND`	text	Identifies the type of change made (insertion, update) (Primary Key).
`MANDANT`	text	Client Identifier (Primary Key).
`CHANGENR`	text	Change record identifier (Primary Key).
`FNAME`	text	Field which was updated (Primary Key).
`OBJECTCLAS`	text	Object class category (Primary Key).
`OBJECTID`	text	Object value (Primary Key).
`TABNAME`	text	Table name where the change was executed (Primary Key).
`VALUE_NEW`	text	Value which was updated into the table.
`VALUE_OLD`	text	Value which was updated out of the table.

Filtering:

This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with CDHDR table to bring the filter by date when loaded from source.
FNAME != 'KEY', TABNAME in ('AUFK', 'AFKO', 'AFPO', 'AFVC', 'AFRU').

JCDS

Input table containing all status changes for Production orders.

Field	Type	Description
`CHGNR`	text	Change record identifier (Primary Key).
`MANDT`	text	Client Identifier (Primary Key).
`OBJNR`	text	Object number (Primary Key).
`STAT`	text	Status to which the object was updated (Primary Key).
`UDATE`	date	Date when the change was executed.
`UTIME`	time	Time when the change was executed.
`USNAM`	text	Name of the person who executed the change.
`INACT`	text	Indicates if the status is active.

Filtering: Date: UDATE and INACT = '' or is null.

JEST

Table that keeps track of all current statuses for an order.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`OBJNR`	text	Object number (Primary Key).
`STAT`	text	Active status of the object (Primary Key).
`INACT`	text	Indicates if the status is active.

Filtering:

This table doesn't have any date field that can be used for filtering purposes. Therefore, it was joined with AUFK table to bring the filter by date when loaded from source.
INACT = '' or is null.

MSEG

Input table for Material Document Items.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`ZEILE`	text	Material document item number (Primary Key).
`MBLNR`	text	Material document item (Primary Key).
`MJAHR`	text	Material Document Year Identifier (Primary Key).
`CPUDT_MKPF`	date	Date the accounting document was entered.
`CPUTM_MKPF`	time	Time the accounting document was entered.
`KZVBR`	text	Indicates the consumption posting.
`MATNR`	text	Foreign key identifying the material number.
`KZBEW`	text	Movement indicator.
`BWART`	text	Foreign key identifying the movement type.
`AUFPS`	text	Indicates the order item number.
`AUFNR`	text	Indicates the order number.
`KZZUG`	text	Receipt indicator.
`SOBKZ`	text	Special stock indicator.
`USNAM_MKPF`	text	Indicates the user name.

Filtering: Date: CPUDT_MKPF and AUFNR != ''.

Master data tables

ADCP

Input table for Person/Address Assignment (Business Address Services) data.

Field	Type	Description
`ADDRNUMBER`	text	Address Number of the Business (Primary Key).
`CLIENT`	text	Client Identifier (Primary Key).
`DATE_FROM`	date	Date from which record applies (Primary Key).
`NATION`	text	Identifier for international addresses.
`PERSNUMBER`	text	Identifier of the person/user (Primary Key).
`DATE_TO`	date	Date to which record applies.
`DEPARTMENT`	text	Department the user is assigned to.
`FUNCTION`	text	Function or role of the user.

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

ADRP

Input table for Persons (Business Address Services) data.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`DATE_FROM`	date	Date from which record applies (Primary Key).
`NATION`	text	Identifier for International Addresses (Primary Key).
`PERSNUMBER`	text	Identifier of the person/user (Primary Key).
`DATE_TO`	date	Date to which record applies.
`NAME_FIRST`	text	Name of the User.
`NAME_LAST`	text	Last name of the User.

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

MARC

Input table for Plant data for Material data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`MATNR`	text	Material Number Identifier (Primary Key).
`WERKS`	text	Plant (Primary Key).
`KZKUP`	text	Indicator: Material can be co-product.

MARM

Input table for Units of Measure for Material data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`MATNR`	text	Material Number Identifier (Primary Key).
`MEINH`	text	Alternative Unit of Measure for Stock keeping Unit (Primary Key).
`UMREN`	double	Denominator for conversion to base units of measure.
`UMREZ`	double	Numerator for Conversion to Base Units of Measure.

MBEW

Input table for Material Valuation data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`MATNR`	text	Material Number Identifier (Primary Key).
`BWKEY`	text	Valuation Area (Primary Key).
`BWTAR`	text	Valuation Type (Primary Key).
`VERPR`	double	Moving average price/Periodic unit price.
`PEINH`	double	Unit price.
`STPRS`	double	Standard price.

T001

Input table for Company Codes data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`BUKRS`	text	Company Code (Primary Key).
`BUTXT`	text	Identifies the company name.
`WAERS`	text	Foreign key identifying the currency key.

T001L

Input table for Storage Locations data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`WERKS`	text	Plant (Primary Key).
`LGORT`	text	Storage Location (Primary Key).
`LGOBE`	text	Description of storage location.

T001W

Input table for Plants/Branches data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`WERKS`	text	Plant Code identifier (Primary Key).
`NAME1`	text	Plant name.
`BWKEY`	text	Valuation Area.

T003P

Input table for Order Type Descriptions data.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`SPRAS`	text	Language Key (Primary Key).
`AUART`	text	Order Type (Primary Key).
`TXT`	text	Order Type description.

Filtering: SPRAS = var("language").

T006A

Input table for Measurement unit data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`SPRAS`	text	Language key identifier (Primary Key).
`MSEHI`	text	Measurement unit (Primary Key).
`MSEHT`	text	Measurement unit description.

Filtering: SPRAS = var("language").

T156

Input table for Movement Type data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`BWART`	text	Movement Type Identifier (Primary Key).
`SHKZG`	text	Debit/Credit indicator.
`XSTBW`	text	Reversal movement type.

T156T

Input table for Movement Type descriptions data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`KZVBR`	text	Consumption Posting (Primary Key).
`SPRAS`	text	Language key identifier (Primary Key).
`KZBEW`	text	Movement indicator (Primary Key).
`BWART`	text	Movement Type Identifier (Primary Key).
`KZZUG`	text	Receipt indicator (Primary Key).
`SOBKZ`	text	Special stock indicator (Primary Key).
`BTEXT`	text	Movement Type description.

Filtering: SPRAS = var("language").

TCA02

Input table for Language-dependent texts for TCA01: Task list types data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`SPRAS`	text	Language key identifier (Primary Key).
`PLNTY`	text	Task List Type (Primary Key).
`TXT`	text	Description of the task list type.

Filtering: SPRAS = var("language").

TCURF

Input table for Conversion Factors data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`KURST`	text	Exchange rate type used for the conversion (Primary Key).
`FCURR`	text	Original currency (Primary Key).
`GDATU`	date	Date in which the record was created (Primary Key).
`TCURR`	text	Destination currency of the exchange rate (Primary Key).
`FFACT`	double	Ratio for the "From" Currency Units.
`TFACT`	double	Ratio for the "To" Currency Units.

Filtering: KURST = var("exchange_rate_type").

TCURR

Input table for Exchange Rates data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`KURST`	text	Exchange rate type used for the conversion (Primary Key).
`FCURR`	text	Original currency (Primary Key).
`TCURR`	text	Destination currency of the exchange rate (Primary Key).
`GDATU`	date	Date on which the exchange rate is valid (Primary Key).
`UKURS`	text	The absolute exchange rate on the specific date.

Filtering: KURST = var("exchange_rate_type").

TCURX

Input table for Decimal Places in Currencies.

Field	Type	Description
`CURRKEY`	text	Currency identifier (Primary Key).
`CURRDEC`	integer	Number of decimal places.

TGSBT

Input table for Business Area Names data.

Field	Type	Description
`GSBER`	text	Business Area (Primary Key).
`MANDT`	text	Client Identifier (Primary Key).
`SPRAS`	text	Language Key (Primary Key).
`GTEXT`	text	Business Area description.

Filtering: SPRAS = var("language").

TJ02T

Input table for System status descriptions data.

Field	Type	Description
`SPRAS`	text	Language Key (Primary Key).
`ISTAT`	text	System status (Primary Key).
`TXT04`	text	Individual status of an object (short form).
`TXT30`	text	Object status.

Filtering: SPRAS = var("language").

TKA01

Input table for Controlling area descriptions data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`KOKRS`	text	Controlling Area (Primary Key).
`BEZEI`	text	Name of the controlling area.

TTZCU

Input table for Customizing time zones.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`FLAGACTIVE`	text	Indicates active records.
`TZONESYS`	text	Foreign key identifying the system time zone.
`TZONEDEF`	text	Foreign key identifying the default time zone for user.

Filtering: FLAGACTIVE = 'X'.

TTZD

Input table for Summer time rules.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`DSTRULE`	text	Daylight saving time rule (Primary Key).
`FLAGACTIVE`	text	Indicates active records.
`DSTDIFF`	text	Indicates the difference between daylight saving time and winter time.

Filtering: FLAGACTIVE = 'X'.

TTZDF

Input table for Summer time rules (fixed annual dates).

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`DSTRULE`	text	Daylight saving time rule (Primary Key).
`YEARACT`	integer	Valid for year (Primary Key).
`DATEFROM`	date	Starting date for the daylight saving time.
`TIMEFROM`	time	Starting time for the daylight saving time.
`DATETO`	date	Ending date for the daylight saving time.
`TIMETO`	time	Ending time for the daylight saving time.

TTZDV

Input table for Summer time rules (variable dates).

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`DSTRULE`	text	Daylight saving time rule (Primary Key).
`YEARFROM`	integer	Valid from year (Primary Key).
`MONTHFROM`	text	Starting month for the daylight saving time.
`WEEKDFROM`	text	Starting day of the week for the daylight saving time.
`WEEKDCFROM`	text	Starting weekday in month (occurrence number) for the daylight saving time.
`TIMEFROM`	time	Starting time for the daylight saving time.
`MONTHTO`	text	Ending month for the daylight saving time.
`WEEKDTO`	text	Ending day of the week for the daylight saving time.
`WEEKDCTO`	text	Ending weekday in month (occurrence number) for the daylight saving time.
`TIMETO`	time	Ending time for the daylight saving time.

TTZR

Input table for Time zone rules.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`ZONERULE`	text	Time zone rule (Primary Key).
`FLAGACTIVE`	text	Indicates active records.
`UTCDIFF`	text	Indicates the difference of time zone from UTC.
`UTCSIGN`	text	Indicates the sign of the difference from UTC.

Filtering: FLAGACTIVE = 'X'.

TTZZ

Input table for Time zones.

Field	Type	Description
`CLIENT`	text	Client Identifier (Primary Key).
`TZONE`	text	Time zone (Primary Key).
`FLAGACTIVE`	text	Indicates active records.
`ZONERULE`	text	Indicates the time zone rule.
`DSTRULE`	text	Indicates the daylight saving time rule.

Filtering: FLAGACTIVE = 'X'.

USR02

Input table for User type data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`BNAME`	text	User key identifier (Primary Key).
`TZONE`	text	Time Zone.
`USTYP`	text	Classification of the user.

USR21

Input table for User Name/Address Key Assignment data.

Field	Type	Description
`MANDT`	text	Client Identifier (Primary Key).
`BNAME`	text	User key identifier (Primary Key).
`ADDRNUMBER`	text	Address Number of the Business.
`KOSTL`	text	User's cost center.
`PERSNUMBER`	text	Identifier of the person/user.

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Field	Type	Description
Activity	Text	Display name for the activity
Event_cost	Double	Cost associated with the activity
Event_processing_time	Integer	Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Field	Type	Description
Due_date	Text	The name of the due date
Due_date_type	Text	The Due date type
Fixed_costs	Boolean	An indication whether costs are fixed or time based
Cost	Double	Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time	Integer	A number indicating the amount of time in case of time-based costs
Time_type	Text	Type of time period for cost calculations. This can be any of the following values: `day`, `hour`, `minute`, `second` or `millisecond`

Activity_configuration_raw

This seed file is used to set properties of activities.

Field	Type	Description
Activity	Text	Name of the activity
Activity_order	Integer	How the activities are ordered
Activity_type	Text	Type of the activity
Automated	Boolean	Whether the activity is automated or not

User_type_raw

This seed file is used to set properties for domain fixed values of User_type codes.

Field	Type	Description
User_type	Text	User type code
User_type_description	Text	Description of the user type
Automated	Boolean	Whether the user type is automated or not

Order_status_configuration_raw

This seed file is used to set the relevant statuses for production orders.

Field	Type	Description
Order_status_code	Text	The code of the order status
Order_status_description	Text	Description of the order status
Status_order	Integer	Defines hierarchy of most relevant statuses

Production Orders can go through multiple statuses at the same time, and some of them may not be useful for a clear analysis of the process. The seed file Order status configuration is used to filter order statuses used in the transformations, break ties when more than one status is active (based on the standard process flow) and add the descriptions of the statuses. Specific logics where created to select only one current status (the 'main' one) for each Production order, using the order predefined. Also, to select the listed statuses in the change-status models. These logics are further explained in the Design Details section. For your reference, all the Order_status_codes and their descriptions to populate this seed file can be found in the TJ02T (System status texts) SAP ECC table.

Note: Please consider not adding status code I0001 as it refers to the creation of either orders or operations. This activities are already being generated based on the transactional information, thus, adding this status will just duplicate them.

Production_order_category_raw

This seed file is used to set descriptions for production order categories.

Field	Type	Description
Order_category	Text	The order category
Category_description	Text	Description of the order category

In SAP, the order category codes and their descriptions are customizable. Production order category is used to set the order categories desired to be present in the transformations. For your reference, all the Order_category_codes and their descriptions to populate this seed file can be found in the DD07T (DD: Texts for Domain Fixed Values (Language-Dependent)) table. This is a big table so it is recommended to filter the table by 'Domain' = AUFTYP' and the Language value desired (e.g. 'E' for english) to bring only the Production order category values.

Dbt variables

Variable	Type	Description
date_format	string	Specifies the format variable for converting to date depending on the destination.
language	string	This variable specifies the system language, set accordingly to the default SAP system language. Default value is `E` (English).
exchange_rate_type	string	Defines the exchange rate to be applied on client currency in order to convert it into a single currency. Default value is `M`.
display_currency	string	This variable defines the currency to be displayed. Default value is `USD`.
use_operation_events	boolean	Determines if the Operation events are to be displayed or not. Default value is 'true'.
jcds_tracking_activated	boolean	When JCDS table tracking is activated and the variable is enabled, specific models for status-change activities will be created. Default value is 'true'.
production_deficit_percentage	integer	This variable is needed for setting up the 'Quantity deficit' tag. This should be populated as a percentage that represents the maximum admissible value of the ratio `Goods_received_quantity` / `Order_item_quantity`. (e.g. 5 would mean 5%). If there is no threshold for deficit, then set variable to zero (0).

Design specifications

Objects

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

Process objects

Object	Input Data
Material Document Items	MSEG
Operations	AFVC
Production order Items	AFPO, AUFK
Production orders	AFPO, AUFK

Activities

Activity	Object	Description
Post goods issue	Material document items	Movement of consumption of raw material needed for the Production order.
Post goods receipt	Material document items	Movement of reception of finished product derived from the Production order.
Reverse goods receipt	Material document items	Reception reversal of finished products coming from the Production order.
Reverse goods issue	Material document items	Consumption reversal of raw material needed for the Production order.
Change operation description	Operations	A change in the description of an operation within the Production order. This activity will only be displayed if the variable 'use_operation_events' is set as 'true'.
Cancel operation confirmation	Operations	Cancel an operation previously confirmed. This activity will only be displayed if the variable 'use_operation_events' is set as 'true'.
Create partial operation confirmation	Operations	Partial confirmation done at operation level.
Create final operation confirmation	Operations	Final confirmation done at operation level. This activity will only be displayed if the variable 'use_operation_events' is set as 'true'.
Execute operation (Partial/Final)	Operations	This activity represents the time frame an operation took place. It contains Event_start and Event_end attributes. Basically, these fields are taken from the Confirmations (AFRU) table, where the actual execution times should be entered. This activity will be repeated as many times as confirmations are available in AFRU table. The name of the activity depends on the Final_indicator (AFRU."AUERU"). If the Final_indicator field classifies the confirmation record as a final confirmation, then the activity ni the model will be 'Execute operation (Final)', otherwise it will be named as 'Execute operation (Partial)'. This activity will only be displayed if the variable 'use_operation_events' is set as 'true'.
Execute all operations	Operations	This activity represents the time frame where all operations within a Production order took place. Basically, it looks (in AFRU table) for the first actual start execution date-time, which will be the Event_start, and the last actual finish execution date-time, which will be the Event_end, in all the operations of the same Production order. This activity will only be displayed if the variable 'use_operation_events' is set as 'false'. This activity summarizes all operations executions when the analyst does not want to have it broken down into each operation.
Change operation status to `value`	Operations	In the PP process, an operation can move across many statuses. Usually these statuses are related to the Production order status. This activity will only be displayed if the variable 'jcds_tracking_activated' is set as 'true' and the 'use_operation_events' is set as 'true'.
Change order description	Production orders	Changing the entered description of a production order.
Change total order quantity	Production orders	Change in the production order total target quantity.
Change order scheduling type	Production orders	Change in the type of scheduling set in the production order.
Change order basic finish date	Production orders	Change in the date the user estimates a production order will finish.
Change order basic start date	Production orders	Change in the date the user estimates a production order will start.
Change order basic start time	Production orders	Change in the time the user estimates a production order will start.
Change order basic finish time	Production orders	Change in the time the user estimates a production order will finish.
Change order total scrap quantity	Production orders	The scrap quantity entered in the Production order has increased/decreased.
Change order priority	Production orders	The priority entered in the Production order has increased/decreased.
Cancel production order confirmation	Production orders	Cancel a production order, that was previously confirmed.
Create partial production order confirmation	Production orders	Partial confirmation done at Production order level.
Create final production order confirmation	Production orders	Final confirmation done at Production order level.
Create production order	Production orders	Creation of a Production order at header level.
Execute production order (Partial/Final)	Production orders	This activity represents the time frame in which a Production order took place. It contains Event_start and Event_end. Basically, these fields are taken from the Confirmation (AFRU) table, where the actual execution times should be entered. If many confirmations exist, then many activities of execution will be generated, one for each confirmation record. The name of the activity depends on the Final_indicator (AFRU."AUERU"). If the Final_indicator field classifies the confirmation record as a final confirmation, then the activity ni the model will be 'Execute production order (Final)', otherwise it will be named as 'Execute production order (Partial)'.
Change production order status to Released	Production orders	The Release of a Production order. The release is a main step of the SAP ECC PP process. When the variable 'jcds_tracking_activated' is set to 'false', this activity is generated with a specific logic. Otherwise, when the variable is set to 'true' this activity will still be generated by JDCS related models.
Change production order status to `value`	Production orders	In the PP process, a Production order move across many statuses. This activity will track all the statuses for which the Production order went through. It will only be displayed if the variable 'jcds_tracking_activated' is set as 'true'.
Change storage location	Production order items	Change in the production order item total storage location.
Change indicator: order item not relevant for MRP	Production order items	MRP in SAP ECC PP means 'Material requirements planning'. When this field is flagged, it means the item is not relevant for planning requirements anymore. A new change in this field may affect the planned material requirements for the Production order.
Change stock type	Production order items	Change in what type of stock the production order item is.
Create production order item	Production order items	Creation of a Production order at item level.

Design details

Activity design

The app template follows 2 approaches for naming activities:

Activities that can be named based on transactional tables
Activities that can be named based on historical tables Each approach follows a different logic in the transformations.

Approach 1: Activities that can be named based on transactional tables.

These activities have specific models where they were created and the name of the model derives from the activity name.

Example: Production_order_creation_events

Approach 2: Activities that can be named based on historical tables.

These activities have specific models and they are based on SAP ECC PP historical tables:

Approach 2.1: JCDS for status change events

All the activity names in the status change event models were created in the following way:
```
  'Change' + [Object_name] + ' status to ' + [Status_description]
  Example: Change production order status to Released
```
In these models, the Order_status_configuration seed file is used to filter only the statuses desired to be tracked and their descriptions.
Approach 2.2: CDHDR and CDPOS for change log events

All the activity names in the change log event models were created in the following way:
```
  'Change' + [Field_name]
  Example: Change total order quantity
```
In these models, the Field_name comes from the selection of fields enabled to be tracked in CDHDR and CDPOS table.

Relevant considerations for both approaches:

To enable the creation of the models based on JCDS table, the variable 'jcds_tracking_activated' needs to be set to 'true' in advance.
If the variable is set as 'true', JCDS table needs to be enabled in SAP. Otherwise all models related to JCDS will produce no records. Also, for tracking change log events, CDHDR and CDPOS need to be properly set. Detailed instructions can be found in the section Instructions for enabling History Tracking in SAP ECC Production Planning.
Ìf neither of the aforementioned tables are activated in the Client, the transformations will still work properly.

Instructions for enabling History Tracking in SAP ECC Production Planning

These instructions are intended to help analyst to enable the SAP ECC PP historical tracking of:

Status changes of both, orders and operations based on JCDS table
Change logs based on CDHDR and CDPOS tables

Instructions:

Run spro t-code and click on 'SAP Reference IMG' option.

Follow the path shown below: 'Plant maintenance and Customer Service'> 'Maintenance and Service Processing'> 'Functions and Settings for Order types'

Scroll down and click on the clock icon of the option 'Define Change Docs., Collective Purc. Req. Indicator, Operation No. Interval'

Search the Plant for which the changes need to be enabled and select the following items for the required order types (Production Orders, Process Orders and Rework Orders):

OrdStChngDc
Change document
OpstatChangeDoc

These checks will enable JCDS (historical table of statuses) and CDPOS tables.

Save the changes and exit the transaction.
Go to the transaction aut01 and select the table needed to be tracked (e.g. AFKO table, GAMNG field) and check the 'Logging' option.

Enable also the 'Logging' of the Data Element needed for that field.

Repeat the previous two steps for all the specific fields needed.

For this app template, the specific fields which need to be enabled in SAP are the following:

Table	Fields
AUFK	KTEXT
AFKO	GAMNG, TERKZ, GLTRP, GSTRP, GLUZP, GSUZP, GASMG, APRIO
AFPO	PSMNG, PSAMG, LGORT, DNREL, INSMK
AFVC	LTXA1

Save the changes and exit the transaction.

Note regarding time zone conversions

In SAP ECC, there are specific date/time fields that are recorded in user’s time zone instead of server’s. These fields are being used for all activities related to execution events:

Execute production order (Partial/Final)
Execute operation (Partial/Final)
Execute all operations

Specific logics in supporting models were created to convert these timestamps into server’s time zone to avoid discrepancies between timestamps of the activities (activities happening in an order that is impossible).

These logics adjust the following:

Time zone difference (in minutes) between each user and the SAP server
Summertime rule applied to the user (as this will affect the time zone difference calculated before)
Summertime rule applied to the server (as this will also affect the time zone difference calculated before)

1. Time zone difference (in minutes) between each user and the SAP server

This logic works by calculating the UTC time for both, the user and server. After that, it brings the difference between both in minutes. Finally, this difference is applied to the corresponding SAP fields that are stored in the user’s timezone.

Example 1:

Consider the following scenario.
    1. User1 is in Chile (GMT-4) and creates the order at 8:30:00 AM CH
    2. The server is in Brazil (GMT-3), so the creation time is recorded as 9:30:00 AM BR
    3. User1 enters the start_time for the execution of an order at 09:00:00 AM CH
    4. The record is created without any changes as it is stored in user’s time zone (09:00:00 AM CH)

If the logic is not applied, then the activities would be order as follows:
    1. Execute production order (9:00 AM CH)
    2. Create production order (9:30 AM BR)
This cannot ever happen in a system as it is impossible to execute an order that was not created yet. 

The supporting model will adjust the records to the server’s timezone, adding or subtracting the corresponding difference (in this case, it is needed to add 1 hour to the Execute event):
    1. Create production order (9:30 AM BR)
    2. Execute production order (10:00 AM BR)

2 & 3. Summertime rules applied to the user and to the server

These two summertime rule logics are also applied to consider those cases where the original time zone was modified in summertime.

They work as follows:

First, convert the record in user’s time to server’s (see bullet 1). Then, adjust the record with the user summertime rule, if applicable. Finally, adjust the record with the server summertime rule, if applicable.

Example 2:

Consider the following scenario.
    1. User1 is in Chile (GMT-4) and summertime rule does not apply (then, still in GMT-4)
    2. The server is in Brazil (GMT-3) and summertime rule applies (then, GMT-2)
    3. User1 executes an operation at 8:30:00 AM chilean local time
    
The correct value for the execution time should be 10:30:00 AM, brazilian local time.

The conversion process will apply all 3 logics as follows:
    1. For time zone conversion: the logic adds 60’ (GMT-4 vs GMT-3)
    2. For summertime rule applied to the user, the logic subtracts 0’ (summertime rule is not active in Chile)
    3. For summertime rule applied to the server, the logic adds 60’ (GMT-3 vs GMT-2)

As a result, the total addition is 120’.

Known limitations

Release timestamp: when the JCDS table is not being used, the activity for the release gets populated based on a date only (as there is no time stored in the system). The logics where created to reconstruct the timestamp but it won't be as precise as when using the JCDS table.
Execution time frames: Usually most of the manufacturing activities may not be logged automatically in the system, and their execution time is always set manually. These times are used to create all "Execution" activities. This means that if the user that is uploading this info makes a mistake, that mistake will be transferred to the model. If this is the case, we recommend suggesting this as an insight for improving the process.
User timezone changed multiple times: There are specific date/time fields that are recorded in the user timezone. Specific logic was created to convert these fields into the server timezone, to avoid discrepancies between timestamps of the activities. However, if the user has changed multiple times their timezone, as there is no Master Data historical information, this logic will only take the current record for that specific user. This may still cause some discrepancies in timestamps for specific activities performed. This should be very rare as users involved in production activities scarcely move to different timezones.

Troubleshooting

Change status and Change log records have to be configured: In the SAP ECC PP environment, in order to be tracked, they need to be enabled. These configurations are not retroactive to previously registered records. To enable the tracking of Order status changes and change logs of specific fields please follow the Instructions for enabling History Tracking in SAP ECC Production Planning.
Data not recorded in CDHDR/CDPOS objects: Change events have to be configured in the environment in order to be tracked; these configurations are not retroactive to previously registered records.