Monitoring Point Attribute

Table

Database	Table	Entity	On Key 5 Conversions	On Key 5 Tables	Other Entities stored in same Table
`Transaction`	`ahm.monitoring_point_attribute`	Monitoring Point Attribute	ConvertMonitoringPointAttribute	`stdAttributeValues`

Columns

Property	Db Table	Db Column	Db DataType	Nullable	Primary Key	Joins
`MonitoringPointId`	`ahm.monitoring_point_attribute`	`monitoring_point_id`	bigint	False
`MonitoringPointCode`	`ahm.monitoring_point`	`code`	character varying(50)	False		`ahm.monitoring_point_attribute.monitoring_point_id = ahm.monitoring_point.monitoring_point_id`
`MonitoringPointDescription`	`ahm.monitoring_point`	`description`	character varying(100)	False		`ahm.monitoring_point_attribute.monitoring_point_id = ahm.monitoring_point.monitoring_point_id`
`CreatedByUserId`	`ahm.monitoring_point_attribute`	`created_by_user_id`	bigint	False
`CreatedByUserCode`	`uam.user`	`code`	character varying(50)	False		`ahm.monitoring_point_attribute.created_by_user_id = uam.user.user_id`
`CreatedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`ahm.monitoring_point_attribute.created_by_user_id = uam.user.user_id`
`CreatedOn`	`ahm.monitoring_point_attribute`	`created_on`	timestamp without time zone	False
`IsActive`	`ahm.monitoring_point_attribute`	`is_active`	boolean	False
`ModifiedByUserId`	`ahm.monitoring_point_attribute`	`modified_by_user_id`	bigint	False
`ModifiedByUserCode`	`uam.user`	`code`	character varying(50)	False		`ahm.monitoring_point_attribute.modified_by_user_id = uam.user.user_id`
`ModifiedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`ahm.monitoring_point_attribute.modified_by_user_id = uam.user.user_id`
`ModifiedOn`	`ahm.monitoring_point_attribute`	`modified_on`	timestamp without time zone	False
`PermissionTreeId`	`ahm.monitoring_point_attribute`	`permission_tree_id`	bigint	False
`PermissionTreeCode`	`uam.permission_tree`	`code`	text	False		`ahm.monitoring_point_attribute.permission_tree_id = uam.permission_tree.permission_tree_id`
`PermissionTreeDescription`	`uam.permission_tree`	`description`	text	True		`ahm.monitoring_point_attribute.permission_tree_id = uam.permission_tree.permission_tree_id`
`Version`	`ahm.monitoring_point_attribute`	`version`	integer	False
`Id`	`ahm.monitoring_point_attribute`	`monitoring_point_attribute_id`	bigint	False	True
`AttributeId`	`ahm.monitoring_point_attribute`	`attribute_id`	bigint	False
`AttributeCode`	`gen.attribute`	`code`	character varying(50)	False		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id`
`AttributeDescription`	`gen.attribute`	`description`	character varying(100)	False		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id`
`AttributeAllowAnyValue`	`gen.attribute`	`allow_any_value`	boolean	False		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id`
`AttributeDataType`	`gen.attribute`	`data_type`	system.dynamic_value_data_type	False		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id`
`AttributeDefaultValue`	`gen.attribute`	`default_value`	system.dynamic_value	True		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id`
`AttributeDefaultAllowedValueId`	`gen.attribute_allowed_value`	`attribute_allowed_value_id`	bigint	False	True	`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id` Then `gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id`
`AttributeDefaultAllowedValueDescription`	`gen.attribute_allowed_value`	`description`	text	True		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id` Then `gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id`
`AttributeDefaultAllowedValueValue`	`gen.attribute_allowed_value`	`value`	system.dynamic_value	False		`ahm.monitoring_point_attribute.attribute_id = gen.attribute.attribute_id` Then `gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id`
`Notes`	`ahm.monitoring_point_attribute`	`notes`	text	True
`PredefinedValueId`	`ahm.monitoring_point_attribute`	`attribute_value_id`	bigint	True
`PredefinedValueDescription`	`gen.attribute_allowed_value`	`description`	text	True		`ahm.monitoring_point_attribute.attribute_value_id = gen.attribute_allowed_value.attribute_allowed_value_id`
`PredefinedValueValue`	`gen.attribute_allowed_value`	`value`	system.dynamic_value	False		`ahm.monitoring_point_attribute.attribute_value_id = gen.attribute_allowed_value.attribute_allowed_value_id`
`SequenceNumber`	`ahm.monitoring_point_attribute`	`sequence_number`	integer	True
`Value`	`ahm.monitoring_point_attribute`	`attribute_value`	system.dynamic_value	True

Enumerations

Mapping 1

Property	Property Type	Db Column	Db Column Type	Db Column Values
AttributeDataType	`DynamicValueType`	`data_type`	`system.dynamic_value_data_type`	`String` `Email` `Integer` `Long` `Float` `Currency` `DateTime` `Date` `Time` `Duration` `Boolean`

Composites

Mapping 1

Property	Property Type	Db Column	Db Column Type
AttributeDefaultValue	`DbDynamicValue`	default_value	`system.dynamic_value`

Composite Property	Db Composite Column	Property Type
`Type`	`type`	`String` `Email` `Integer` `Long` `Float` `Currency` `DateTime` `Date` `Time` `Duration` `Boolean`
`Value`	`value`	String

Mapping 2

Property	Property Type	Db Column	Db Column Type
AttributeDefaultAllowedValueValue	`DbDynamicValue`	value	`system.dynamic_value`

Composite Property	Db Composite Column	Property Type
`Type`	`type`	`String` `Email` `Integer` `Long` `Float` `Currency` `DateTime` `Date` `Time` `Duration` `Boolean`
`Value`	`value`	String

Mapping 3

Property	Property Type	Db Column	Db Column Type
PredefinedValueValue	`DbDynamicValue`	value	`system.dynamic_value`

Composite Property	Db Composite Column	Property Type
`Type`	`type`	`String` `Email` `Integer` `Long` `Float` `Currency` `DateTime` `Date` `Time` `Duration` `Boolean`
`Value`	`value`	String

Mapping 4

Property	Property Type	Db Column	Db Column Type
Value	`DbDynamicValue`	attribute_value	`system.dynamic_value`

Composite Property	Db Composite Column	Property Type
`Type`	`type`	`String` `Email` `Integer` `Long` `Float` `Currency` `DateTime` `Date` `Time` `Duration` `Boolean`
`Value`	`value`	String

Queries

The list of example Monitoring Point Attribute queries can be interpreted using the following legend:

Legend	Description
Primary Table Alias	The alias for the `ahm.monitoring_point_attribute` 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
`_mpa`	False	False

SELECT
    _mpa.monitoring_point_id AS "MonitoringPointId",
    _mpa.created_by_user_id AS "CreatedByUserId",
    _mpa.created_on AS "CreatedOn",
    _mpa.is_active AS "IsActive",
    _mpa.modified_by_user_id AS "ModifiedByUserId",
    _mpa.modified_on AS "ModifiedOn",
    _mpa.permission_tree_id AS "PermissionTreeId",
    _mpa.version AS "Version",
    _mpa.monitoring_point_attribute_id AS "Id",
    _mpa.attribute_id AS "AttributeId",
    _mpa.notes AS "Notes",
    _mpa.attribute_value_id AS "PredefinedValueId",
    _mpa.sequence_number AS "SequenceNumber",
    _mpa.attribute_value AS "Value"
FROM
    ahm.monitoring_point_attribute _mpa

Query 2

Primary Table Alias	Include References	Include Permissions
`_mpa`	True	False

SELECT
    _mp.monitoring_point_id AS "MonitoringPointId",
    _mp.code AS "MonitoringPointCode",
    _mp.description AS "MonitoringPointDescription",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _mpa.created_on AS "CreatedOn",
    _mpa.is_active AS "IsActive",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _mpa.modified_on AS "ModifiedOn",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _mpa.version AS "Version",
    _mpa.monitoring_point_attribute_id AS "Id",
    _a.attribute_id AS "AttributeId",
    _a.code AS "AttributeCode",
    _a.description AS "AttributeDescription",
    _a.allow_any_value AS "AttributeAllowAnyValue",
    _a.data_type AS "AttributeDataType",
    _a.default_value AS "AttributeDefaultValue",
    _a.default_allowed_value_id AS "AttributeDefaultAllowedValue",
    _aav.attribute_allowed_value_id AS "AttributeDefaultAllowedValueId",
    _aav.description AS "AttributeDefaultAllowedValueDescription",
    _aav.value AS "AttributeDefaultAllowedValueValue",
    _mpa.notes AS "Notes",
    _aav1.attribute_allowed_value_id AS "PredefinedValueId",
    _aav1.description AS "PredefinedValueDescription",
    _aav1.value AS "PredefinedValueValue",
    _mpa.sequence_number AS "SequenceNumber",
    _mpa.attribute_value AS "Value"
FROM
    ahm.monitoring_point_attribute _mpa
    JOIN ahm.monitoring_point _mp ON _mpa.monitoring_point_id = _mp.monitoring_point_id
    JOIN uam.user _u ON _mpa.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _mpa.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _mpa.permission_tree_id = _pt.permission_tree_id
    JOIN gen.attribute _a ON _mpa.attribute_id = _a.attribute_id
    LEFT JOIN gen.attribute_allowed_value _aav ON _a.default_allowed_value_id = _aav.attribute_allowed_value_id
    LEFT JOIN gen.attribute_allowed_value _aav1 ON _mpa.attribute_value_id = _aav1.attribute_allowed_value_id

Query 3

Primary Table Alias	Include References	Include Permissions
`_mpa`	False	True

Parameter Name	Data Type	Value
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`31600`

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
    _mpa.monitoring_point_id AS "MonitoringPointId",
    _mpa.created_by_user_id AS "CreatedByUserId",
    _mpa.created_on AS "CreatedOn",
    _mpa.is_active AS "IsActive",
    _mpa.modified_by_user_id AS "ModifiedByUserId",
    _mpa.modified_on AS "ModifiedOn",
    _mpa.permission_tree_id AS "PermissionTreeId",
    _mpa.version AS "Version",
    _mpa.monitoring_point_attribute_id AS "Id",
    _mpa.attribute_id AS "AttributeId",
    _mpa.notes AS "Notes",
    _mpa.attribute_value_id AS "PredefinedValueId",
    _mpa.sequence_number AS "SequenceNumber",
    _mpa.attribute_value AS "Value",
    _mpa.permission_tree_id AS "_mpa_permission_tree_id"
FROM
    ahm.monitoring_point_attribute _mpa
    JOIN permission_tree_ids permission_join ON _mpa.permission_tree_id = permission_join.permission_tree_id

Query 4

Primary Table Alias	Include References	Include Permissions
`_mpa`	True	True

Parameter Name	Data Type	Value
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`31600`

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
    _mp.monitoring_point_id AS "MonitoringPointId",
    _mp.code AS "MonitoringPointCode",
    _mp.description AS "MonitoringPointDescription",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _mpa.created_on AS "CreatedOn",
    _mpa.is_active AS "IsActive",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _mpa.modified_on AS "ModifiedOn",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _mpa.version AS "Version",
    _mpa.monitoring_point_attribute_id AS "Id",
    _a.attribute_id AS "AttributeId",
    _a.code AS "AttributeCode",
    _a.description AS "AttributeDescription",
    _a.allow_any_value AS "AttributeAllowAnyValue",
    _a.data_type AS "AttributeDataType",
    _a.default_value AS "AttributeDefaultValue",
    _a.default_allowed_value_id AS "AttributeDefaultAllowedValue",
    _aav.attribute_allowed_value_id AS "AttributeDefaultAllowedValueId",
    _aav.description AS "AttributeDefaultAllowedValueDescription",
    _aav.value AS "AttributeDefaultAllowedValueValue",
    _mpa.notes AS "Notes",
    _aav1.attribute_allowed_value_id AS "PredefinedValueId",
    _aav1.description AS "PredefinedValueDescription",
    _aav1.value AS "PredefinedValueValue",
    _mpa.sequence_number AS "SequenceNumber",
    _mpa.attribute_value AS "Value",
    _mpa.permission_tree_id AS "_mpa_permission_tree_id",
    _mp.permission_tree_id AS "_mp_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id",
    _a.permission_tree_id AS "_a_permission_tree_id",
    _aav.permission_tree_id AS "_aav_permission_tree_id",
    _aav1.permission_tree_id AS "_aav1_permission_tree_id"
FROM
    ahm.monitoring_point_attribute _mpa
    JOIN permission_tree_ids permission_join ON _mpa.permission_tree_id = permission_join.permission_tree_id
    JOIN ahm.monitoring_point _mp ON _mpa.monitoring_point_id = _mp.monitoring_point_id
    JOIN uam.user _u ON _mpa.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _mpa.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _mpa.permission_tree_id = _pt.permission_tree_id
    JOIN gen.attribute _a ON _mpa.attribute_id = _a.attribute_id
    LEFT JOIN gen.attribute_allowed_value _aav ON _a.default_allowed_value_id = _aav.attribute_allowed_value_id
    LEFT JOIN gen.attribute_allowed_value _aav1 ON _mpa.attribute_value_id = _aav1.attribute_allowed_value_id

Table of Contents

Monitoring Point Attribute

Table

Columns

Enumerations

Mapping 1

Composites

Mapping 1

Mapping 2

Mapping 3

Mapping 4

Queries

Query 1

Query 2

Query 3

Query 4