Table of Contents

Resource Trade

Table

Database Table Entity On Key 5 Conversions On Key 5 Tables Other Entities stored in same Table
Transaction res.resource_trade Resource Trade ConvertStaffMemberTradeRates
ConvertTrade
 stfStaffMembers
stfTrades

Columns

Property Db Table Db Column Db DataType Nullable Primary Key Joins
IsActive res.resource_trade is_active boolean False
CreatedByUserId res.resource_trade created_by_user_id bigint False
CreatedByUserCode uam.user code character varying(50) False res.resource_trade.created_by_user_id = uam.user.user_id
CreatedByUserFullName uam.user full_name character varying(200) False res.resource_trade.created_by_user_id = uam.user.user_id
CreatedOn res.resource_trade created_on timestamp without time zone False
ModifiedByUserId res.resource_trade modified_by_user_id bigint False
ModifiedByUserCode uam.user code character varying(50) False res.resource_trade.modified_by_user_id = uam.user.user_id
ModifiedByUserFullName uam.user full_name character varying(200) False res.resource_trade.modified_by_user_id = uam.user.user_id
ModifiedOn res.resource_trade modified_on timestamp without time zone False
Version res.resource_trade version integer False
PermissionTreeId res.resource_trade permission_tree_id bigint False
PermissionTreeCode uam.permission_tree code text False res.resource_trade.permission_tree_id = uam.permission_tree.permission_tree_id
PermissionTreeDescription uam.permission_tree description text True res.resource_trade.permission_tree_id = uam.permission_tree.permission_tree_id
Id res.resource_trade resource_trade_id bigint False True
DefaultResourceTradeFinancialRateId res.resource_trade default_resource_trade_financial_rate_id bigint True
DefaultResourceTradeFinancialRateFinancialRateId res.financial_rate financial_rate_id bigint False True res.resource_trade.default_resource_trade_financial_rate_id = res.resource_trade_financial_rate.resource_trade_financial_rate_id Then
res.resource_trade_financial_rate.financial_rate_id = res.financial_rate.financial_rate_id
DefaultResourceTradeFinancialRateFinancialRateDescription res.financial_rate description character varying(100) False res.resource_trade.default_resource_trade_financial_rate_id = res.resource_trade_financial_rate.resource_trade_financial_rate_id Then
res.resource_trade_financial_rate.financial_rate_id = res.financial_rate.financial_rate_id
IsCustomised res.resource_trade is_customised boolean True
Notes res.resource_trade notes text True
ResourceId res.resource_trade resource_id bigint False
ResourceCode res.resource code character varying(50) False res.resource_trade.resource_id = res.resource.resource_id
SequenceNumber res.resource_trade sequence_number integer True
TradeId res.resource_trade trade_id bigint False
TradeCode res.trade code character varying(50) False res.resource_trade.trade_id = res.trade.trade_id
TradeDescription res.trade description character varying(100) False res.resource_trade.trade_id = res.trade.trade_id

Queries

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

Legend Description
Primary Table Alias The alias for the res.resource_trade 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
_rt False False 
SELECT
    _rt.is_active AS "IsActive",
    _rt.created_by_user_id AS "CreatedByUserId",
    _rt.created_on AS "CreatedOn",
    _rt.modified_by_user_id AS "ModifiedByUserId",
    _rt.modified_on AS "ModifiedOn",
    _rt.version AS "Version",
    _rt.permission_tree_id AS "PermissionTreeId",
    _rt.resource_trade_id AS "Id",
    _rt.default_resource_trade_financial_rate_id AS "DefaultResourceTradeFinancialRateId",
    _rt.is_customised AS "IsCustomised",
    _rt.notes AS "Notes",
    _rt.resource_id AS "ResourceId",
    _rt.sequence_number AS "SequenceNumber",
    _rt.trade_id AS "TradeId"
FROM
    res.resource_trade _rt

Query 2

Primary Table Alias Include References Include Permissions
_rt True False 
SELECT
    _rt.is_active AS "IsActive",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rt.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rt.modified_on AS "ModifiedOn",
    _rt.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _rt.resource_trade_id AS "Id",
    _rtfr.resource_trade_financial_rate_id AS "DefaultResourceTradeFinancialRateId",
    _rtfr.financial_rate_id AS "DefaultResourceTradeFinancialRateFinancialRate",
    _fr.financial_rate_id AS "DefaultResourceTradeFinancialRateFinancialRateId",
    _fr.description AS "DefaultResourceTradeFinancialRateFinancialRateDescription",
    _rt.is_customised AS "IsCustomised",
    _rt.notes AS "Notes",
    _r.resource_id AS "ResourceId",
    _r.code AS "ResourceCode",
    _rt.sequence_number AS "SequenceNumber",
    _t.trade_id AS "TradeId",
    _t.code AS "TradeCode",
    _t.description AS "TradeDescription"
FROM
    res.resource_trade _rt
    JOIN uam.user _u ON _rt.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rt.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _rt.permission_tree_id = _pt.permission_tree_id
    LEFT JOIN res.resource_trade_financial_rate _rtfr ON _rt.default_resource_trade_financial_rate_id = _rtfr.resource_trade_financial_rate_id
    LEFT JOIN res.financial_rate _fr ON _rtfr.financial_rate_id = _fr.financial_rate_id
    JOIN res.resource _r ON _rt.resource_id = _r.resource_id
    JOIN res.trade _t ON _rt.trade_id = _t.trade_id

Query 3

Primary Table Alias Include References Include Permissions
_rt False True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 70800 
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
    _rt.is_active AS "IsActive",
    _rt.created_by_user_id AS "CreatedByUserId",
    _rt.created_on AS "CreatedOn",
    _rt.modified_by_user_id AS "ModifiedByUserId",
    _rt.modified_on AS "ModifiedOn",
    _rt.version AS "Version",
    _rt.permission_tree_id AS "PermissionTreeId",
    _rt.resource_trade_id AS "Id",
    _rt.default_resource_trade_financial_rate_id AS "DefaultResourceTradeFinancialRateId",
    _rt.is_customised AS "IsCustomised",
    _rt.notes AS "Notes",
    _rt.resource_id AS "ResourceId",
    _rt.sequence_number AS "SequenceNumber",
    _rt.trade_id AS "TradeId",
    _rt.permission_tree_id AS "_rt_permission_tree_id"
FROM
    res.resource_trade _rt
    JOIN permission_tree_ids permission_join ON _rt.permission_tree_id = permission_join.permission_tree_id

Query 4

Primary Table Alias Include References Include Permissions
_rt True True
Parameter Name Data Type Value
@permission_user_id Bigint 500000000000
@permission_id Bigint 70800 
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
    _rt.is_active AS "IsActive",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rt.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rt.modified_on AS "ModifiedOn",
    _rt.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _rt.resource_trade_id AS "Id",
    _rtfr.resource_trade_financial_rate_id AS "DefaultResourceTradeFinancialRateId",
    _rtfr.financial_rate_id AS "DefaultResourceTradeFinancialRateFinancialRate",
    _fr.financial_rate_id AS "DefaultResourceTradeFinancialRateFinancialRateId",
    _fr.description AS "DefaultResourceTradeFinancialRateFinancialRateDescription",
    _rt.is_customised AS "IsCustomised",
    _rt.notes AS "Notes",
    _r.resource_id AS "ResourceId",
    _r.code AS "ResourceCode",
    _rt.sequence_number AS "SequenceNumber",
    _t.trade_id AS "TradeId",
    _t.code AS "TradeCode",
    _t.description AS "TradeDescription",
    _rt.permission_tree_id AS "_rt_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id",
    _rtfr.permission_tree_id AS "_rtfr_permission_tree_id",
    _r.permission_tree_id AS "_r_permission_tree_id",
    _t.permission_tree_id AS "_t_permission_tree_id"
FROM
    res.resource_trade _rt
    JOIN permission_tree_ids permission_join ON _rt.permission_tree_id = permission_join.permission_tree_id
    JOIN uam.user _u ON _rt.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rt.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _rt.permission_tree_id = _pt.permission_tree_id
    LEFT JOIN res.resource_trade_financial_rate _rtfr ON _rt.default_resource_trade_financial_rate_id = _rtfr.resource_trade_financial_rate_id
    LEFT JOIN res.financial_rate _fr ON _rtfr.financial_rate_id = _fr.financial_rate_id
    JOIN res.resource _r ON _rt.resource_id = _r.resource_id
    JOIN res.trade _t ON _rt.trade_id = _t.trade_id