Table of Contents

Risk Model

Table

Database Table Entity On Key 5 Conversions On Key 5 Tables Other Entities stored in same Table
Transaction risk.risk_model Risk Model ConvertRiskModel
 astCriticalityModels

Columns

Property Db Table Db Column Db DataType Nullable Primary Key Joins
Code risk.risk_model code character varying(50) False
IsActive risk.risk_model is_active boolean False
Version risk.risk_model version integer False
PermissionTreeId risk.risk_model permission_tree_id bigint False
PermissionTreeCode uam.permission_tree code text False risk.risk_model.permission_tree_id = uam.permission_tree.permission_tree_id
PermissionTreeDescription uam.permission_tree description text True risk.risk_model.permission_tree_id = uam.permission_tree.permission_tree_id
CreatedByUserId risk.risk_model created_by_user_id bigint False
CreatedByUserCode uam.user code character varying(50) False risk.risk_model.created_by_user_id = uam.user.user_id
CreatedByUserFullName uam.user full_name character varying(200) False risk.risk_model.created_by_user_id = uam.user.user_id
CreatedOn risk.risk_model created_on timestamp without time zone False
ModifiedByUserId risk.risk_model modified_by_user_id bigint False
ModifiedByUserCode uam.user code character varying(50) False risk.risk_model.modified_by_user_id = uam.user.user_id
ModifiedByUserFullName uam.user full_name character varying(200) False risk.risk_model.modified_by_user_id = uam.user.user_id
ModifiedOn risk.risk_model modified_on timestamp without time zone False
Id risk.risk_model risk_model_id bigint False True
RiskCalculationType risk.risk_model risk_calculation_type risk.risk_calculation_type False
Description risk.risk_model description character varying(100) False
ImportancesConfigured risk.risk_model importances_configured boolean False
ImportancesRangesVerified risk.risk_model importances_ranges_verified boolean False
Notes risk.risk_model notes text True
Reason risk.risk_model reason text True
SequenceNumber risk.risk_model sequence_number integer True

Enumerations

Mapping 1

Property Property Type Db Column Db Column Type Db Column Values
RiskCalculationType RiskCalculationType risk_calculation_type risk.risk_calculation_type NormalisedWeightedSum
WeightedSum
WeightedProduct

Queries

The list of example Risk Model queries can be interpreted using the following legend:

Legend Description
Primary Table Alias The alias for the risk.risk_model table in the query
Include References Include (true) or exclude (false) all lookup table joins in the query
Include Permissions Include (true) or exclude (false) the permission table join in the query to enforce or skip row-level security based on the permissions for the user

Query 1

Primary Table Alias Include References Include Permissions
_rm False False 
SELECT
    _rm.code AS "Code",
    _rm.is_active AS "IsActive",
    _rm.version AS "Version",
    _rm.permission_tree_id AS "PermissionTreeId",
    _rm.created_by_user_id AS "CreatedByUserId",
    _rm.created_on AS "CreatedOn",
    _rm.modified_by_user_id AS "ModifiedByUserId",
    _rm.modified_on AS "ModifiedOn",
    _rm.risk_model_id AS "Id",
    _rm.risk_calculation_type AS "RiskCalculationType",
    _rm.description AS "Description",
    _rm.importances_configured AS "ImportancesConfigured",
    _rm.importances_ranges_verified AS "ImportancesRangesVerified",
    _rm.notes AS "Notes",
    _rm.reason AS "Reason",
    _rm.sequence_number AS "SequenceNumber"
FROM
    risk.risk_model _rm

Query 2

Primary Table Alias Include References Include Permissions
_rm True False 
SELECT
    _rm.code AS "Code",
    _rm.is_active AS "IsActive",
    _rm.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rm.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rm.modified_on AS "ModifiedOn",
    _rm.risk_model_id AS "Id",
    _rm.risk_calculation_type AS "RiskCalculationType",
    _rm.description AS "Description",
    _rm.importances_configured AS "ImportancesConfigured",
    _rm.importances_ranges_verified AS "ImportancesRangesVerified",
    _rm.notes AS "Notes",
    _rm.reason AS "Reason",
    _rm.sequence_number AS "SequenceNumber"
FROM
    risk.risk_model _rm
    JOIN uam.permission_tree _pt ON _rm.permission_tree_id = _pt.permission_tree_id
    JOIN uam.user _u ON _rm.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rm.modified_by_user_id = _u1.user_id

Query 3

Primary Table Alias Include References Include Permissions
_rm False True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 119800 
WITH permission_tree_ids AS
(
    SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
    _rm.code AS "Code",
    _rm.is_active AS "IsActive",
    _rm.version AS "Version",
    _rm.permission_tree_id AS "PermissionTreeId",
    _rm.created_by_user_id AS "CreatedByUserId",
    _rm.created_on AS "CreatedOn",
    _rm.modified_by_user_id AS "ModifiedByUserId",
    _rm.modified_on AS "ModifiedOn",
    _rm.risk_model_id AS "Id",
    _rm.risk_calculation_type AS "RiskCalculationType",
    _rm.description AS "Description",
    _rm.importances_configured AS "ImportancesConfigured",
    _rm.importances_ranges_verified AS "ImportancesRangesVerified",
    _rm.notes AS "Notes",
    _rm.reason AS "Reason",
    _rm.sequence_number AS "SequenceNumber",
    _rm.permission_tree_id AS "_rm_permission_tree_id"
FROM
    risk.risk_model _rm
    JOIN permission_tree_ids permission_join ON _rm.permission_tree_id = permission_join.permission_tree_id

Query 4

Primary Table Alias Include References Include Permissions
_rm True True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 119800 
WITH permission_tree_ids AS
(
    SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
    _rm.code AS "Code",
    _rm.is_active AS "IsActive",
    _rm.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rm.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rm.modified_on AS "ModifiedOn",
    _rm.risk_model_id AS "Id",
    _rm.risk_calculation_type AS "RiskCalculationType",
    _rm.description AS "Description",
    _rm.importances_configured AS "ImportancesConfigured",
    _rm.importances_ranges_verified AS "ImportancesRangesVerified",
    _rm.notes AS "Notes",
    _rm.reason AS "Reason",
    _rm.sequence_number AS "SequenceNumber",
    _rm.permission_tree_id AS "_rm_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id"
FROM
    risk.risk_model _rm
    JOIN permission_tree_ids permission_join ON _rm.permission_tree_id = permission_join.permission_tree_id
    JOIN uam.permission_tree _pt ON _rm.permission_tree_id = _pt.permission_tree_id
    JOIN uam.user _u ON _rm.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rm.modified_by_user_id = _u1.user_id