Table of Contents

Competency

Table

Database Table Entity On Key 5 Conversions On Key 5 Tables Other Entities stored in same Table
Transaction res.competency Competency ConvertCompetency
 stfCompetencies

Columns

Property Db Table Db Column Db DataType Nullable Primary Key Joins
Code res.competency code character varying(50) False
CreatedByUserId res.competency created_by_user_id bigint False
CreatedByUserCode uam.user code character varying(50) False res.competency.created_by_user_id = uam.user.user_id
CreatedByUserFullName uam.user full_name character varying(200) False res.competency.created_by_user_id = uam.user.user_id
CreatedOn res.competency created_on timestamp without time zone False
IsActive res.competency is_active boolean False
ModifiedByUserId res.competency modified_by_user_id bigint False
ModifiedByUserCode uam.user code character varying(50) False res.competency.modified_by_user_id = uam.user.user_id
ModifiedByUserFullName uam.user full_name character varying(200) False res.competency.modified_by_user_id = uam.user.user_id
ModifiedOn res.competency modified_on timestamp without time zone False
PermissionTreeId res.competency permission_tree_id bigint False
PermissionTreeCode uam.permission_tree code text False res.competency.permission_tree_id = uam.permission_tree.permission_tree_id
PermissionTreeDescription uam.permission_tree description text True res.competency.permission_tree_id = uam.permission_tree.permission_tree_id
Version res.competency version integer False
Id res.competency competency_id bigint False True
Description res.competency description character varying(100) False
IsStrict res.competency is_strict boolean False
IsTradeSpecific res.competency is_trade_specific boolean False
Notes res.competency notes text True
SequenceNumber res.competency sequence_number integer True
ValidityType res.competency validity_type res.competency_validity_type False

Enumerations

Mapping 1

Property Property Type Db Column Db Column Type Db Column Values
ValidityType CompetencyValidityType validity_type res.competency_validity_type Permanent
Transient

Queries

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

Legend Description
Primary Table Alias The alias for the res.competency 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
_c False False 
SELECT
    _c.code AS "Code",
    _c.created_by_user_id AS "CreatedByUserId",
    _c.created_on AS "CreatedOn",
    _c.is_active AS "IsActive",
    _c.modified_by_user_id AS "ModifiedByUserId",
    _c.modified_on AS "ModifiedOn",
    _c.permission_tree_id AS "PermissionTreeId",
    _c.version AS "Version",
    _c.competency_id AS "Id",
    _c.description AS "Description",
    _c.is_strict AS "IsStrict",
    _c.is_trade_specific AS "IsTradeSpecific",
    _c.notes AS "Notes",
    _c.sequence_number AS "SequenceNumber",
    _c.validity_type AS "ValidityType"
FROM
    res.competency _c

Query 2

Primary Table Alias Include References Include Permissions
_c True False 
SELECT
    _c.code AS "Code",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _c.created_on AS "CreatedOn",
    _c.is_active AS "IsActive",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _c.modified_on AS "ModifiedOn",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _c.version AS "Version",
    _c.competency_id AS "Id",
    _c.description AS "Description",
    _c.is_strict AS "IsStrict",
    _c.is_trade_specific AS "IsTradeSpecific",
    _c.notes AS "Notes",
    _c.sequence_number AS "SequenceNumber",
    _c.validity_type AS "ValidityType"
FROM
    res.competency _c
    JOIN uam.user _u ON _c.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _c.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _c.permission_tree_id = _pt.permission_tree_id

Query 3

Primary Table Alias Include References Include Permissions
_c False True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 70200 
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
    _c.code AS "Code",
    _c.created_by_user_id AS "CreatedByUserId",
    _c.created_on AS "CreatedOn",
    _c.is_active AS "IsActive",
    _c.modified_by_user_id AS "ModifiedByUserId",
    _c.modified_on AS "ModifiedOn",
    _c.permission_tree_id AS "PermissionTreeId",
    _c.version AS "Version",
    _c.competency_id AS "Id",
    _c.description AS "Description",
    _c.is_strict AS "IsStrict",
    _c.is_trade_specific AS "IsTradeSpecific",
    _c.notes AS "Notes",
    _c.sequence_number AS "SequenceNumber",
    _c.validity_type AS "ValidityType",
    _c.permission_tree_id AS "_c_permission_tree_id"
FROM
    res.competency _c
    JOIN permission_tree_ids permission_join ON _c.permission_tree_id = permission_join.permission_tree_id

Query 4

Primary Table Alias Include References Include Permissions
_c True True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 70200 
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
    _c.code AS "Code",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _c.created_on AS "CreatedOn",
    _c.is_active AS "IsActive",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _c.modified_on AS "ModifiedOn",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _c.version AS "Version",
    _c.competency_id AS "Id",
    _c.description AS "Description",
    _c.is_strict AS "IsStrict",
    _c.is_trade_specific AS "IsTradeSpecific",
    _c.notes AS "Notes",
    _c.sequence_number AS "SequenceNumber",
    _c.validity_type AS "ValidityType",
    _c.permission_tree_id AS "_c_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id"
FROM
    res.competency _c
    JOIN permission_tree_ids permission_join ON _c.permission_tree_id = permission_join.permission_tree_id
    JOIN uam.user _u ON _c.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _c.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _c.permission_tree_id = _pt.permission_tree_id