Segregation of Duties
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] Comparing whether the activities 'Request Approval' and 'Grant Approval' were executed by the same department is done within the CASE WHEN statement. The statement contrasts the source event department to the target event department by using the SOURCE and TARGET operators. The REMAP_VALUES function passed as a parameter to the SOURCE operator, allows to extract the activities 'Request Approval' and 'Grant Approval' by mapping them to the same name while mapping all the other activities to 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): | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
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: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|