Skip to main content

Automerge

Description

Automerge creates the table _CEL_MERGED_ACTIVITIES by merging all available and 'mergeable' event logs into the default event log.

Automerge uses MERGE_EVENTLOG functionality to merge all mergeable tables in a tree-structured manner.

The default eventlog is the target eventlog. That is enhanced by the activities of all connected eventlogs. This means that cases that exist in the case table of the target eventlog will get more activities from other eventlogs that have a join relationship to this case. If a case does not exist in the case table of the target eventlog, it will not appear in _CEL_MERGED_ACTIVITIES. Even if an activity has a join partner in another activity table but those have no connection to the default activity table, these activities will not show in the table _CEL_MERGED_ACTIVITIES.

If automerge is enabled (or disabled), a full data load is required for the change to take effect.

If the case table of an activity table is connected to the rest of the data model (where the default activity table is configured) via the activity table, we ignore the activity table.

Result Columns

The automerge result table contains the three default event attribute columns which always have the names CASE_ID, ACTIVITY and TIMESTAMP. These contain the merged content of the default columns from the activity tables and replace the original column names. For example, if there is the activity table "O2C_ACTIVITIES" with the activity column "ACTIVITY_EN", the merged result would be in "_CEL_MERGED_ACTIVITIES"."ACTIVITY". All other columns of the data model activity tables are also merged and added to the result table under their original name.

If activity tables have the same column name but different types, separate columns are added to the table _CEL_MERGED_ACTIVITIES. We assign them a name that is the concatenation of the column name and the column type.

Examples

[1]

All columns from the three activity tables Table1_Activity, Table2_Activity, and Table3_Activity are available in the _CEL_MERGED_ACTIVITIES table. If a column is not present in another activity table, the respective rows are filled with NULL values.

Query

Column1 

         "_CEL_MERGED_ACTIVITIES"."CASE_ID"

Column2 

         "_CEL_MERGED_ACTIVITIES"."ACTIVITY"

Column3 

         "_CEL_MERGED_ACTIVITIES"."TIMESTAMP"

Column4 

         "_CEL_MERGED_ACTIVITIES"."ADDITION1"

Column5 

         "_CEL_MERGED_ACTIVITIES"."ADDITION2"

Column6 

         "_CEL_MERGED_ACTIVITIES"."ADDITION3"

Column7 

         "_CEL_MERGED_ACTIVITIES"."ADDITION4"

Input

Output

Table1_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION3 : string

ADDITION5 : string

'100'

'Vendor creates Invoice'

Wed Jan 01 2020 01:00:00.000

'B'

'X'

'R'

'100'

'Enter in SAP'

Wed Jan 01 2020 02:00:00.000

'B'

'X'

'R'

Table1_Case

CASE_ID : string

NETWR : float

'100'

5.0

Table2_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION2 : string

ADDITION5 : int

'2001'

'Delete Purchase Order Item'

Wed Jan 01 2020 03:00:00.000

'A'

'X'

10

'2002'

'Record Goods Receipt'

Wed Jan 01 2020 04:00:00.000

'A'

'X'

10

Table2_Case

CASE_ID : string

SUPER_CASE_ID : string

'2001'

'100'

'2002'

'100'

Table3_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION4 : string

ADDITION5 : string

'3001'

'Send Purchase Order'

Wed Jan 01 2020 05:00:00.000

'C'

'X'

'S'

'3002'

'Clear Invoice 1'

Wed Jan 01 2020 06:00:00.000

'C'

'X'

'S'

Table3_Case

CASE_ID : string

SUPER_CASE_ID : string

'3001'

'100'

'3002'

'100'

Foreign Keys

Table1_Case.CASE_ID

Table1_Activity.CASE_ID

Table2_Case.CASE_ID

Table2_Activity.CASE_ID

Table3_Case.CASE_ID

Table3_Activity.CASE_ID

Table1_Case.CASE_ID

Table2_Case.SUPER_CASE_ID

Table1_Case.CASE_ID

Table3_Case.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

Column3 : date

Column4 : string

Column5 : string

Column6 : string

Column7 : string

'2001'

'Vendor creates Invoice'

Wed Jan 01 2020 01:00:00.000

'B'

null

'X'

null

'2001'

'Enter in SAP'

Wed Jan 01 2020 02:00:00.000

'B'

null

'X'

null

'2001'

'Delete Purchase Order Item'

Wed Jan 01 2020 03:00:00.000

'A'

'X'

null

null

'2001'

'Send Purchase Order'

Wed Jan 01 2020 05:00:00.000

'C'

null

null

'X'

'2001'

'Clear Invoice 1'

Wed Jan 01 2020 06:00:00.000

'C'

null

null

'X'

'2002'

'Vendor creates Invoice'

Wed Jan 01 2020 01:00:00.000

'B'

null

'X'

null

'2002'

'Enter in SAP'

Wed Jan 01 2020 02:00:00.000

'B'

null

'X'

null

'2002'

'Record Goods Receipt'

Wed Jan 01 2020 04:00:00.000

'A'

'X'

null

null

'2002'

'Send Purchase Order'

Wed Jan 01 2020 05:00:00.000

'C'

null

null

'X'

'2002'

'Clear Invoice 1'

Wed Jan 01 2020 06:00:00.000

'C'

null

null

'X'

[2]

The column ADDITION5 appears in table Table1_Activity and Table3_Activity with type STRING and with type INT in table Table2_Activity. Therefore, two columns are created: ADDITION5_STRING and ADDITION5_INT.

Query

Column1 

         "_CEL_MERGED_ACTIVITIES"."CASE_ID"

Column2 

         "_CEL_MERGED_ACTIVITIES"."ACTIVITY"

Column3 

         "_CEL_MERGED_ACTIVITIES"."ADDITION5_STRING"

Column4 

         "_CEL_MERGED_ACTIVITIES"."ADDITION5_INT"

Input

Output

Table1_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION3 : string

ADDITION5 : string

'100'

'Vendor creates Invoice'

Wed Jan 01 2020 01:00:00.000

'B'

'X'

'R'

'100'

'Enter in SAP'

Wed Jan 01 2020 02:00:00.000

'B'

'X'

'R'

Table1_Case

CASE_ID : string

NETWR : float

'100'

5.0

Table2_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION2 : string

ADDITION5 : int

'2001'

'Delete Purchase Order Item'

Wed Jan 01 2020 03:00:00.000

'A'

'X'

10

'2002'

'Record Goods Receipt'

Wed Jan 01 2020 04:00:00.000

'A'

'X'

10

Table2_Case

CASE_ID : string

SUPER_CASE_ID : string

'2001'

'100'

'2002'

'100'

Table3_Activity

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

ADDITION1 : string

ADDITION4 : string

ADDITION5 : string

'3001'

'Send Purchase Order'

Wed Jan 01 2020 05:00:00.000

'C'

'X'

'S'

'3002'

'Clear Invoice 1'

Wed Jan 01 2020 06:00:00.000

'C'

'X'

'S'

Table3_Case

CASE_ID : string

SUPER_CASE_ID : string

'3001'

'100'

'3002'

'100'

Foreign Keys

Table1_Case.CASE_ID

Table1_Activity.CASE_ID

Table2_Case.CASE_ID

Table2_Activity.CASE_ID

Table3_Case.CASE_ID

Table3_Activity.CASE_ID

Table1_Case.CASE_ID

Table2_Case.SUPER_CASE_ID

Table1_Case.CASE_ID

Table3_Case.SUPER_CASE_ID

Result

Column1 : string

Column2 : string

Column3 : string

Column4 : int

'2001'

'Vendor creates Invoice'

'R'

null

'2001'

'Enter in SAP'

'R'

null

'2001'

'Delete Purchase Order Item'

null

10

'2001'

'Send Purchase Order'

'S'

null

'2001'

'Clear Invoice 1'

'S'

null

'2002'

'Vendor creates Invoice'

'R'

null

'2002'

'Enter in SAP'

'R'

null

'2002'

'Record Goods Receipt'

null

10

'2002'

'Send Purchase Order'

'S'

null

'2002'

'Clear Invoice 1'

'S'

null