Order-to-Cash input fields

Introduction

This section contains an overview of the fields for each of the input tables of Order-to-Cash. For each entity the fields are listed. For each field, the name and a data type are displayed. Apart from that, it is indicated whether the field is mandatory if the entity is included in the Order-to-Cash process.

Note:

When a table or field is not mandatory, it should be part of the input. Optional tables may contain no records and optional fields may contain empty values.

When a field is mandatory, it should not contain NULL values.

Entity	Tables	Mandatory Y/N
Sales order	Sales_orders_base Sales_order_items_base	Y
Deliveries	Deliveries_base Delivery_items_base	N
Invoice	Invoices_base Invoice_items_base Invoide_cancellations_base	N
Accounting	Accounting_documents_base	N
Payment	Payments_base	N

The illustration below displays the relations between entities of Order-to-Cash.

Important: Table names and field names are case-sensitive. Always make sure that the field names (column headers) in your dataset match the field names in the table below and that the file names match the table names.

Important: Make sure to add the suffix _raw to the table names, when Loading data using CData Sync.

Field types

Below is an overview of the different field types and their default format settings.

Field type	Format
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

Sales order

Sales_orders_base table

Below is an overview of the fields of the Sales_orders_base table.

Name	Type	Mandatory Y/N	Description
sales_order_id	text	Y	The unique identifier of the sales order.
company	text	N	The company for which the sales order is created.
customer	text	N	The customer for whom the sales order is created.
customer_country	text	N	The country associated to the customer.
customer_region	text	N	The region associated to the customer.
distribution_channel	text	N	The distribution channel associated to the sales order.
division	text	N	The division associated to the sales order.
requested_delivery_date	date	N	The customer’s requested delivery date for the sales order.
sales_group	text	N	The sales group associated to the sales order.
sales_office	text	N	The sales office associated to the sales order.
sales_order_type	text	N	The sales order type of the sales order.
sales_organization	text	N	The sales organization of the sales order.

Sales_order_items_base table

Below is an overview of the fields of the Sales_order_items_base table.

Name	Type	Mandatory Y/N	Description
sales_order_item_id	text	Y	The unique identifier of the sales order item.
sales_order_id	text	Y	The unique identifier of the sales order.
creation_date	date	Y	The date on which the sales order item is created.
category	text	N	The category of sales order item.
incoterms	text	N	The incoterms associated to the sales order item.
latest_expected_delivery_date	date	N	The latest expected delivery date of (parts of) the sales order item.
material	text	N	The material sold.
material_group	text	N	The categorization of the material sold.
payment_terms	text	N	The terms of payment associated to the sales order item.
plant	text	N	The plant associated to the sales order item.
profit_center	text	N	The profit center associated to the sales order item.
quantity	text	N	The quantity and unit of measurement sold.
sales_order_item	text	N	A user-friendly name to identify the sales order item
shipping_point_country	text	N	The country where the goods are delivered associated to the sales order item.
storage_location	text	N	Storage location associated to the sales order item.
value	double	N	A monetary value related to the sales order item.

Delivery

Deliveries_base table

Below is an overview of the fields of the Deliveries_base table.

Name	Type	Mandatory Y/N	Description
delivery_id	text	Y	The unique identifier of the delivery.

Delivery_items_base table

Below is an overview of the fields of the Delivery_items_base table.

Name	Type	Mandatory Y/N	Description
delivery_item_id	text	Y	The unique identifier of the delivery item.
delivery_id	text	N*	The unique identifier of the delivery.
sales_order_item_id	text	N*	The unique identifier of the sales order item.
delivery_item_is_invoiced	boolean	N	Indicates if the sales order item is released to invoicing.

* If you want to include events related to other entities than the sales order in the process graphs, the delivery_id and sales_order_item_id fields are mandatory.

Invoice

Invoices_base table

Below is an overview of the fields of the Invoices_base table.

Name	Type	Mandatory Y/N	Description
invoice_id	text	Y	The unique identifier of the invoice.

Invoice_items_base table

Below is an overview of the fields of the Invoice_items_base table.

Name	Type	Mandatory Y/N	Description
invoice_item_id	text	Y	The unique identifier of the invoice item.
invoice_id	text	N*	The unique identifier of the invoice.
delivery_item_id	text	N*	The unique identifier of the delivery item.
invoice_item_is_cancelled	boolean	N	Indicates if the invoice item is cancelled.
invoice_item_is_released_into_accounting	boolean	N	Indicates if the invoice item is billable.

* If you want to include events related to other entities than the sales order in the process graphs, the invoice_id and delivery_item_id fields are mandatory.

Invoice_cancellations_base table

Below is an overview of the fields of the Invoice_cancellations_base table.

Name	Type	Mandatory Y/N	Description
invoice_cancellation_id	text	Y	The unique identifier of the invoice cancellation.
invoice_id	text	N*	The unique identifier of the invoice that is cancelled.

* If you want to include events related to other entities than the sales order in the process graphs, the invoice_id field is mandatory.

Accounting

Accounting_documents_base table

Below is an overview of the fields of the Accounting_documents_base table.

Name	Type	Mandatory Y/N	Description
accounting_document_id	text	Y	The unique identifier of the accounting document.
invoice_id	text	N*	The unique identifier of the invoice.
accounting_document_is _cancelled	boolean	N	Indicates if the accounting document is cancelled.
accounting_document_is _cleared	boolean	N	Indicates if the accounting document has been handled and is closed.

* If you want to include events related to other entities than the sales order in the process graphs, the invoice_id field is mandatory.

Payment

Payments_base table

Below is an overview of the fields of the Payments_base table.

Name	Type	Mandatory Y/N	Description
payment_id	text	Y	The unique identifier of the payment.
accounting_document_id	text	N*	The unique identifier of the accounting document.

* If you want to include events related to other entities than the sales order in the process graphs, the accounting_document_id field is mandatory.

Events

Events_base table

The Events_base table contains all sub-event logs for all entities. Below is an overview of the fields of the Events_base table.

Name	Type	Mandatory Y/N	Description
activity	text	Y	The name of the event. This describes the step in the process.
event_end	datetime	Y	The timestamp associated with the end of executing the event
accounting_document_id	text	Y	The unique identifier of the accounting document.
delivery_id	text	Y	The unique identifier of the delivery.
delivery_item_id	text	Y	The unique identifier of the delivery item.
invoice_id	text	Y	The unique identifier of the invoice.
invoice_item_id	text	Y	The unique identifier of the invoice item.
invoice_cancellation_id	text	Y	The unique identifier of the invoice cancellation.
payment_id	text	Y	The unique identifier of the payment.
sales_order_id	text	Y	The unique identifier of the sales order.
sales_order_item_id	text	Y	The unique identifier of the sales order item.
activity_category	text	N	The type of activity that takes place (e.g. change, set/remove block)
activity_order	integer	N	The number that defines in which order activities are executed in case they have the same Event end.
activity_trigger	text	N	Field describing the reason for the executed activity
automated	boolean	N	Indicator whether the event is manually executed or automated.
department	text	N	The department that executed the event
event_cost	double	N	The costs of executing the event.
event_detail	text	N	Information related to the event.
event_start *	datetime	N
user	text	N	The user who executed the event.
user_function	text	N	The function associated with the user.
user_type	Text	N	The categorization of the user.

* If both event_start and event_end are defined in your dataset, the duration of the event is known. This will be used to calculate the throughput time of events. If only event_end is defined, the throughput time will be calculated as the difference between previous event_end and the event_end.

event_start is only taken into account if every record in the dataset contains a not-null value.

Important:

If you want to use event_cost and/or event_start in Order-to-Cash, you must add these fields to your .mvp connector and use DataBridgeAgent to load the data in your Purchase-to-Pay process app.

See Loading data using DataBridgeAgent.

Make sure you add the connector to DataBridgeAgent. See Adding a custom connector to DataBridgeAgent.

Note:

It is expected that for each record exactly one of the id fields is filled in, which is the one of the entity on which the event takes place. The other id fields may be empty.

Next to the standard mandatory fields (activity and event_end), one entity id is mandatory. This field depends on the entity the event is part of.

Custom fields

Additionally, custom case and event fields for both case and event properties are available for Order-to-Cash.

• 10 fields of type text (5 for events);
• 10 fields of type double (5 for events);
• 5 fields of type datetime; (These must be of the format yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional.)

• 10 fields of type boolean (5 for events)

• 10 fields of type duration (5 for events).

Important:

If you want to use custom fields in Order-to-Cash, you must add these fields to your .mvp connector and use DataBridgeAgent to load the data in your Order-to-Cash process app.

See Loading data using DataBridgeAgent.

Make sure you add the connector to DataBridgeAgent. See Adding a custom connector to DataBridgeAgent.

Custom fields have generic names. Below is an overview of the custom fields that can be used in a Order-to-Cash process app.

Name	Data type	Mandatory Y/N
custom_sales_order_item_text_{1...10}	text	N
custom_sales_order_item_number_{1...10}	double	N
custom_sales_order_item_datetime_{1...5}	datetime	N
custom_sales_order_item_boolean_{1...10}	boolean	N
custom_sales_order_item_duration_{1...10	integer	N
custom_event_text_{1...5}	text	N
custom_event_number_{1...5}	double	N
custom_event_datetime_{1...5}	datetime	N
custom_event_boolean_{1...5}	boolean	N
custom_event_duration_{1...5}	integer	N

When you use a custom field, you should rename the field in using Data manager to a name that matches the value. See Fields.

Custom fields that are empty in your dataset, are not displayed in Process Mining.