Segregation of Duties

Applies to: CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

This example shows how to identify violations of a segregation of duties policy with PQL.

Segregation of Duties (SoD) is a concept based on shared responsibilities: It ensures that certain activities are not executed by the same person or department. It applies the four-eyes principle and decreases the power of an individual person or department in order to prevent fraud and errors. Therefore, the concept is essential for effective risk management and internal controls. In procurement, unauthorized or unnecessary purchase orders or purchase orders for personal use may occur if duties are not separated properly. With this in mind, it is best practice in procurement to have different people, or departments, for purchase approvals and invoice payment approvals.

Challenge

Each purchase order (i.e. each case) is related to one purchase organization. For each purchase organization, the SoD violation ratio for the activities 'Request Approval' and 'Grant Approval' should be calculated.

As a second example, we show how to filter on purchase orders that contain a SoD violation.

Calculate SoD Violation Ratio

The following example shows a PQL query for calculating the ratio of purchase orders in which the SoD for the activities 'Request Approval' and 'Grant Approval' was violated because the same department executed both tasks. The ratio is calculated per purchase organization to discover the ones with the highest violation ratio.

[1]

The AVG operator in which the CASE WHEN statement is wrapped calculates the ratio of violations of the SoD. By specifying the purchasing organization ("PurchaseOrders"."PurchaseOrganization") as a dimension, the ratio of violations is calculated per purchase organization.

For the example data model, the query returns 0.5 for purchase organization PO-A (one SoD violation in the corresponding two pairs of 'Request Approval' and 'Grant Approval' activities) and 1.0 for PO-B (the corresponding pair of 'Request Approval' and 'Grant Approval' activities in purchase order '3' violates the SoD):

Query

Column1

"PurchaseOrders"."PurchaseOrganization"

Column2

AVG (
    CASE
        WHEN
            SOURCE (
                "Activities"."Department" ,
            REMAP_VALUES (
                "Activities"."Activity" ,
                [ 'Request Approval' , 'Request Approval' ] ,
                [ 'Grant Approval' , 'Grant Approval' ] ,
                NULL
            )
            )
            =
            TARGET ( "Activities"."Department" )
        THEN
            1
        ELSE
            0
    END
)

Input

Output

Activities

CaseID : INT	Activity : STRING	Timestamp : DATE	Department : STRING
1	'Request Approval'	Wed Jan 01 2020 01:00:00.000	'D-1'
1	'Do some work'	Wed Jan 01 2020 02:00:00.000	'D-1'
1	'Grant Approval'	Wed Jan 01 2020 03:00:00.000	'D-2'
2	'Request Approval'	Wed Jan 01 2020 01:00:00.000	'D-2'
2	'Do some work'	Wed Jan 01 2020 02:00:00.000	'D-1'
2	'Grant Approval'	Wed Jan 01 2020 03:00:00.000	'D-2'
3	'Request Approval'	Wed Jan 01 2020 01:00:00.000	'D-2'
3	'Grant Approval'	Wed Jan 01 2020 02:00:00.000	'D-2'
3	Wed Jan 01 2020 03:00:00.000	Wed Jan 01 2020 03:00:00.000	'D-1'

PurchaseOrders

CaseID : INT	PurchaseOrganization : STRING
1	'PO-A'
2	'PO-A'
3	'PO-B'

Foreign Keys

Activities.CaseID

PurchaseOrders.CaseID

Result

Column1 : STRING	Column2 : FLOAT
'PO-A'	0.5
'PO-B'	1.0

Filter on violating cases

The following example shows how to filter on purchase orders that contain at least one SoD violation for the activities 'Request Approval' and 'Grant Approval'.

[2] The SoD violations in this example are computed the same way as in the previous query, only that the condition which compares departments can now be found in a FILTER statement instead of a CASE WHEN. The condition filters out all purchase orders (cases) which don't contain any violations based on the SoD policy described above. Thus, all purchase orders containing at least one violation (purchase orders with CaseID '2' and '3' in the example data) are in the result: