Standard Task Resource

Table

Database	Table	Entity	On Key 5 Conversions	On Key 5 Tables	Other Entities stored in same Table
`Transaction`	`ampc.asset_type_task_resource`	Standard Task Resource			Regular Asset Type Task Resource Auxiliary Asset Type Task Resource

Columns

Property	Db Table	Db Column	Db DataType	Nullable	Primary Key	Joins
`CreatedByUserId`	`ampc.asset_type_task_resource`	`created_by_user_id`	bigint	False
`CreatedByUserCode`	`uam.user`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.created_by_user_id = uam.user.user_id`
`CreatedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`ampc.asset_type_task_resource.created_by_user_id = uam.user.user_id`
`CreatedOn`	`ampc.asset_type_task_resource`	`created_on`	timestamp without time zone	False
`ModifiedByUserId`	`ampc.asset_type_task_resource`	`modified_by_user_id`	bigint	False
`ModifiedByUserCode`	`uam.user`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.modified_by_user_id = uam.user.user_id`
`ModifiedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`ampc.asset_type_task_resource.modified_by_user_id = uam.user.user_id`
`ModifiedOn`	`ampc.asset_type_task_resource`	`modified_on`	timestamp without time zone	False
`Version`	`ampc.asset_type_task_resource`	`version`	integer	False
`PermissionTreeId`	`ampc.asset_type_task_resource`	`permission_tree_id`	bigint	False
`PermissionTreeCode`	`uam.permission_tree`	`code`	text	False		`ampc.asset_type_task_resource.permission_tree_id = uam.permission_tree.permission_tree_id`
`PermissionTreeDescription`	`uam.permission_tree`	`description`	text	True		`ampc.asset_type_task_resource.permission_tree_id = uam.permission_tree.permission_tree_id`
`DevelopmentStatus`	`ampc.asset_type_task_resource`	`development_status`	ampc.asset_type_development_status	False
`Id`	`ampc.asset_type_task_resource`	`asset_type_task_resource_id`	bigint	False	True
`IsActive`	`ampc.asset_type_task_resource`	`is_active`	boolean	False
`IsBackup`	`ampc.asset_type_task_resource`	`is_backup`	boolean	False
`IsInherited`	`ampc.asset_type_task_resource`	`is_inherited`	boolean	True
`LastConfirmedOn`	`ampc.asset_type_task_resource`	`last_confirmed_on`	timestamp without time zone	True
`PreviousDevelopmentStatus`	`ampc.asset_type_task_resource`	`previous_development_status`	ampc.asset_type_development_status	True
`SequenceNumber`	`ampc.asset_type_task_resource`	`sequence_number`	integer	True
`CompetencyId`	`ampc.asset_type_task_resource`	`competency_id`	bigint	True
`CompetencyCode`	`res.competency`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.competency_id = res.competency.competency_id`
`CompetencyDescription`	`res.competency`	`description`	character varying(100)	False		`ampc.asset_type_task_resource.competency_id = res.competency.competency_id`
`CompetencyValidityType`	`res.competency`	`validity_type`	res.competency_validity_type	False		`ampc.asset_type_task_resource.competency_id = res.competency.competency_id`
`InheritedFromId`	`ampc.asset_type_task_resource`	`inherited_from_id`	bigint	True
`Notes`	`ampc.asset_type_task_resource`	`notes`	text	True
`Proficiency`	`ampc.asset_type_task_resource`	`proficiency`	integer	True
`Quantity`	`ampc.asset_type_task_resource`	`quantity`	integer	False
`RecordType`	`ampc.asset_type_task_resource`	`record_type`	ampc.asset_type_task_record_type	False
`ResourceId`	`ampc.asset_type_task_resource`	`resource_id`	bigint	True
`ResourceCode`	`res.resource`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.resource_id = res.resource.resource_id`
`ResourceResourceType`	`res.resource`	`resource_type`	res.resource_type	False		`ampc.asset_type_task_resource.resource_id = res.resource.resource_id`
`ResourceContactDetailId`	`res.resource_contact_detail`	`resource_contact_detail_id`	bigint	False	True
`ResourceContactDetailFirstName`	`res.resource_contact_detail`	`first_name`	character varying(100)	True
`ResourceContactDetailLastName`	`res.resource_contact_detail`	`last_name`	character varying(100)	True
`ResourceType`	`ampc.asset_type_task_resource`	`resource_type`	res.resource_type	False
`SectionId`	`ampc.asset_type_task_resource`	`section_id`	bigint	True
`SectionCode`	`res.section`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.section_id = res.section.section_id`
`SectionDescription`	`res.section`	`description`	character varying(100)	False		`ampc.asset_type_task_resource.section_id = res.section.section_id`
`StandardTaskId`	`ampc.asset_type_task_resource`	`asset_type_task_id`	bigint	False
`StandardTaskCode`	`ampc.asset_type_task`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.asset_type_task_id = ampc.asset_type_task.asset_type_task_id`
`StandardTaskDescription`	`ampc.asset_type_task`	`description`	character varying(255)	False		`ampc.asset_type_task_resource.asset_type_task_id = ampc.asset_type_task.asset_type_task_id`
`StandardTaskLastConfirmedOn`	`ampc.asset_type_task`	`last_confirmed_on`	timestamp without time zone	True		`ampc.asset_type_task_resource.asset_type_task_id = ampc.asset_type_task.asset_type_task_id`
`TradeId`	`ampc.asset_type_task_resource`	`trade_id`	bigint	True
`TradeCode`	`res.trade`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.trade_id = res.trade.trade_id`
`TradeDescription`	`res.trade`	`description`	character varying(100)	False		`ampc.asset_type_task_resource.trade_id = res.trade.trade_id`
`UnitOfMeasurementId`	`ampc.asset_type_task_resource`	`unit_of_measurement_id`	bigint	False
`UnitOfMeasurementCode`	`gen.unit_of_measurement`	`code`	character varying(50)	False		`ampc.asset_type_task_resource.unit_of_measurement_id = gen.unit_of_measurement.unit_of_measurement_id`
`UnitOfMeasurementDescription`	`gen.unit_of_measurement`	`description`	character varying(100)	False		`ampc.asset_type_task_resource.unit_of_measurement_id = gen.unit_of_measurement.unit_of_measurement_id`
`UnitOfMeasurementMeasurementType`	`gen.unit_of_measurement`	`measurement_type`	gen.measurement_type	False		`ampc.asset_type_task_resource.unit_of_measurement_id = gen.unit_of_measurement.unit_of_measurement_id`
`Usage`	`ampc.asset_type_task_resource`	`usage`	system.dynamic_value	True

Enumerations

Mapping 1

Property	Property Type	Db Column	Db Column Type	Db Column Values
DevelopmentStatus	`AssetTypeDevelopmentStatus`	`development_status`	`ampc.asset_type_development_status`	`NewUnderDevelopment` `NewAwaitingConfirmation` `ModifiedUnderDevelopment` `ModifiedAwaitingConfirmation` `DeletedAwaitingConfirmation` `DeletedConfirmed` `Confirmed`

Mapping 2

Property	Property Type	Db Column	Db Column Type	Db Column Values
PreviousDevelopmentStatus	`AssetTypeDevelopmentStatus`	`previous_development_status`	`ampc.asset_type_development_status`	`NewUnderDevelopment` `NewAwaitingConfirmation` `ModifiedUnderDevelopment` `ModifiedAwaitingConfirmation` `DeletedAwaitingConfirmation` `DeletedConfirmed` `Confirmed`

Mapping 3

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

Mapping 4

Property	Property Type	Db Column	Db Column Type	Db Column Values
RecordType	`AssetTypeTaskRecordType`	`record_type`	`ampc.asset_type_task_record_type`	`Regular` `StandardTask` `Auxiliary`

Mapping 5

Property	Property Type	Db Column	Db Column Type	Db Column Values
ResourceResourceType	`ResourceType`	`resource_type`	`res.resource_type`	`StaffMember` `SpecialResource` `Contact`

Mapping 6

Property	Property Type	Db Column	Db Column Type	Db Column Values
ResourceType	`ResourceType`	`resource_type`	`res.resource_type`	`StaffMember` `SpecialResource` `Contact`

Mapping 7

Property	Property Type	Db Column	Db Column Type	Db Column Values
UnitOfMeasurementMeasurementType	`MeasurementType`	`measurement_type`	`gen.measurement_type`	`None` `Duration`

Composites

Mapping 1

Property	Property Type	Db Column	Db Column Type
Usage	`DbDynamicValue`	usage	`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 Standard Task Resource queries can be interpreted using the following legend:

Legend	Description
Primary Table Alias	The alias for the `ampc.asset_type_task_resource` 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
`_attr`	False	False

Parameter Name	Data Type	Value
`@asset_type_task_resource_id`	Bigint	`0`

SELECT
    _attr.created_by_user_id AS "CreatedByUserId",
    _attr.created_on AS "CreatedOn",
    _attr.modified_by_user_id AS "ModifiedByUserId",
    _attr.modified_on AS "ModifiedOn",
    _attr.version AS "Version",
    _attr.permission_tree_id AS "PermissionTreeId",
    _attr.development_status AS "DevelopmentStatus",
    _attr.asset_type_task_resource_id AS "Id",
    _attr.is_active AS "IsActive",
    _attr.is_backup AS "IsBackup",
    _attr.is_inherited AS "IsInherited",
    _attr.last_confirmed_on AS "LastConfirmedOn",
    _attr.previous_development_status AS "PreviousDevelopmentStatus",
    _attr.sequence_number AS "SequenceNumber",
    _attr.competency_id AS "CompetencyId",
    _attr.inherited_from_id AS "InheritedFromId",
    _attr.notes AS "Notes",
    _attr.proficiency AS "Proficiency",
    _attr.quantity AS "Quantity",
    _attr.record_type AS "RecordType",
    _attr.resource_id AS "ResourceId",
    _attr.resource_type AS "ResourceType",
    _attr.section_id AS "SectionId",
    _attr.asset_type_task_id AS "StandardTaskId",
    _attr.trade_id AS "TradeId",
    _attr.unit_of_measurement_id AS "UnitOfMeasurementId",
    _attr.usage AS "Usage"
FROM
    ampc.asset_type_task_resource _attr
WHERE
    (_attr.asset_type_task_resource_id > @asset_type_task_resource_id)

Query 2

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

Parameter Name	Data Type	Value
`@asset_type_task_resource_id`	Bigint	`0`

SELECT
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _attr.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _attr.modified_on AS "ModifiedOn",
    _attr.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _attr.development_status AS "DevelopmentStatus",
    _attr.asset_type_task_resource_id AS "Id",
    _attr.is_active AS "IsActive",
    _attr.is_backup AS "IsBackup",
    _attr.is_inherited AS "IsInherited",
    _attr.last_confirmed_on AS "LastConfirmedOn",
    _attr.previous_development_status AS "PreviousDevelopmentStatus",
    _attr.sequence_number AS "SequenceNumber",
    _c.competency_id AS "CompetencyId",
    _c.code AS "CompetencyCode",
    _c.description AS "CompetencyDescription",
    _c.validity_type AS "CompetencyValidityType",
    _attr1.asset_type_task_resource_id AS "InheritedFromId",
    _attr.notes AS "Notes",
    _attr.proficiency AS "Proficiency",
    _attr.quantity AS "Quantity",
    _attr.record_type AS "RecordType",
    _r.resource_id AS "ResourceId",
    _r.code AS "ResourceCode",
    _r.resource_type AS "ResourceResourceType",
    _rcd.resource_contact_detail_id AS "ResourceContactDetailId",
    _rcd.first_name AS "ResourceContactDetailFirstName",
    _rcd.last_name AS "ResourceContactDetailLastName",
    _attr.resource_type AS "ResourceType",
    _s.section_id AS "SectionId",
    _s.code AS "SectionCode",
    _s.description AS "SectionDescription",
    _att.asset_type_task_id AS "StandardTaskId",
    _att.code AS "StandardTaskCode",
    _att.description AS "StandardTaskDescription",
    _att.last_confirmed_on AS "StandardTaskLastConfirmedOn",
    _t.trade_id AS "TradeId",
    _t.code AS "TradeCode",
    _t.description AS "TradeDescription",
    _uom.unit_of_measurement_id AS "UnitOfMeasurementId",
    _uom.code AS "UnitOfMeasurementCode",
    _uom.description AS "UnitOfMeasurementDescription",
    _uom.measurement_type AS "UnitOfMeasurementMeasurementType",
    _attr.usage AS "Usage"
FROM
    ampc.asset_type_task_resource _attr
    JOIN uam.user _u ON _attr.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _attr.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _attr.permission_tree_id = _pt.permission_tree_id
    LEFT JOIN res.competency _c ON _attr.competency_id = _c.competency_id
    LEFT JOIN ampc.asset_type_task_resource _attr1 ON _attr.inherited_from_id = _attr1.asset_type_task_resource_id
    LEFT JOIN res.resource _r ON _attr.resource_id = _r.resource_id
    LEFT JOIN res.resource_contact_detail _rcd ON _r.resource_id = _rcd.resource_contact_detail_id
    LEFT JOIN res.section _s ON _attr.section_id = _s.section_id
    JOIN ampc.asset_type_task _att ON _attr.asset_type_task_id = _att.asset_type_task_id
    LEFT JOIN res.trade _t ON _attr.trade_id = _t.trade_id
    LEFT JOIN gen.unit_of_measurement _uom ON _attr.unit_of_measurement_id = _uom.unit_of_measurement_id
WHERE
    (_attr.asset_type_task_resource_id > @asset_type_task_resource_id)

Query 3

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

Parameter Name	Data Type	Value
`@asset_type_task_resource_id`	Bigint	`0`
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`157000`

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
    _attr.created_by_user_id AS "CreatedByUserId",
    _attr.created_on AS "CreatedOn",
    _attr.modified_by_user_id AS "ModifiedByUserId",
    _attr.modified_on AS "ModifiedOn",
    _attr.version AS "Version",
    _attr.permission_tree_id AS "PermissionTreeId",
    _attr.development_status AS "DevelopmentStatus",
    _attr.asset_type_task_resource_id AS "Id",
    _attr.is_active AS "IsActive",
    _attr.is_backup AS "IsBackup",
    _attr.is_inherited AS "IsInherited",
    _attr.last_confirmed_on AS "LastConfirmedOn",
    _attr.previous_development_status AS "PreviousDevelopmentStatus",
    _attr.sequence_number AS "SequenceNumber",
    _attr.competency_id AS "CompetencyId",
    _attr.inherited_from_id AS "InheritedFromId",
    _attr.notes AS "Notes",
    _attr.proficiency AS "Proficiency",
    _attr.quantity AS "Quantity",
    _attr.record_type AS "RecordType",
    _attr.resource_id AS "ResourceId",
    _attr.resource_type AS "ResourceType",
    _attr.section_id AS "SectionId",
    _attr.asset_type_task_id AS "StandardTaskId",
    _attr.trade_id AS "TradeId",
    _attr.unit_of_measurement_id AS "UnitOfMeasurementId",
    _attr.usage AS "Usage",
    _attr.permission_tree_id AS "_attr_permission_tree_id"
FROM
    ampc.asset_type_task_resource _attr
    JOIN permission_tree_ids permission_join ON _attr.permission_tree_id = permission_join.permission_tree_id
WHERE
    (_attr.asset_type_task_resource_id > @asset_type_task_resource_id)

Query 4

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

Parameter Name	Data Type	Value
`@asset_type_task_resource_id`	Bigint	`0`
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`157000`

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
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _attr.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _attr.modified_on AS "ModifiedOn",
    _attr.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _attr.development_status AS "DevelopmentStatus",
    _attr.asset_type_task_resource_id AS "Id",
    _attr.is_active AS "IsActive",
    _attr.is_backup AS "IsBackup",
    _attr.is_inherited AS "IsInherited",
    _attr.last_confirmed_on AS "LastConfirmedOn",
    _attr.previous_development_status AS "PreviousDevelopmentStatus",
    _attr.sequence_number AS "SequenceNumber",
    _c.competency_id AS "CompetencyId",
    _c.code AS "CompetencyCode",
    _c.description AS "CompetencyDescription",
    _c.validity_type AS "CompetencyValidityType",
    _attr1.asset_type_task_resource_id AS "InheritedFromId",
    _attr.notes AS "Notes",
    _attr.proficiency AS "Proficiency",
    _attr.quantity AS "Quantity",
    _attr.record_type AS "RecordType",
    _r.resource_id AS "ResourceId",
    _r.code AS "ResourceCode",
    _r.resource_type AS "ResourceResourceType",
    _rcd.resource_contact_detail_id AS "ResourceContactDetailId",
    _rcd.first_name AS "ResourceContactDetailFirstName",
    _rcd.last_name AS "ResourceContactDetailLastName",
    _attr.resource_type AS "ResourceType",
    _s.section_id AS "SectionId",
    _s.code AS "SectionCode",
    _s.description AS "SectionDescription",
    _att.asset_type_task_id AS "StandardTaskId",
    _att.code AS "StandardTaskCode",
    _att.description AS "StandardTaskDescription",
    _att.last_confirmed_on AS "StandardTaskLastConfirmedOn",
    _t.trade_id AS "TradeId",
    _t.code AS "TradeCode",
    _t.description AS "TradeDescription",
    _uom.unit_of_measurement_id AS "UnitOfMeasurementId",
    _uom.code AS "UnitOfMeasurementCode",
    _uom.description AS "UnitOfMeasurementDescription",
    _uom.measurement_type AS "UnitOfMeasurementMeasurementType",
    _attr.usage AS "Usage",
    _attr.permission_tree_id AS "_attr_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id",
    _c.permission_tree_id AS "_c_permission_tree_id",
    _attr1.permission_tree_id AS "_attr1_permission_tree_id",
    _r.permission_tree_id AS "_r_permission_tree_id",
    _s.permission_tree_id AS "_s_permission_tree_id",
    _att.permission_tree_id AS "_att_permission_tree_id",
    _t.permission_tree_id AS "_t_permission_tree_id",
    _uom.permission_tree_id AS "_uom_permission_tree_id"
FROM
    ampc.asset_type_task_resource _attr
    JOIN permission_tree_ids permission_join ON _attr.permission_tree_id = permission_join.permission_tree_id
    JOIN uam.user _u ON _attr.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _attr.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _attr.permission_tree_id = _pt.permission_tree_id
    LEFT JOIN res.competency _c ON _attr.competency_id = _c.competency_id
    LEFT JOIN ampc.asset_type_task_resource _attr1 ON _attr.inherited_from_id = _attr1.asset_type_task_resource_id
    LEFT JOIN res.resource _r ON _attr.resource_id = _r.resource_id
    LEFT JOIN res.resource_contact_detail _rcd ON _r.resource_id = _rcd.resource_contact_detail_id
    LEFT JOIN res.section _s ON _attr.section_id = _s.section_id
    JOIN ampc.asset_type_task _att ON _attr.asset_type_task_id = _att.asset_type_task_id
    LEFT JOIN res.trade _t ON _attr.trade_id = _t.trade_id
    LEFT JOIN gen.unit_of_measurement _uom ON _attr.unit_of_measurement_id = _uom.unit_of_measurement_id
WHERE
    (_attr.asset_type_task_resource_id > @asset_type_task_resource_id)

Table of Contents

Standard Task Resource

Table

Columns

Enumerations

Mapping 1

Mapping 2

Mapping 3

Mapping 4

Mapping 5

Mapping 6

Mapping 7

Composites

Mapping 1

Queries

Query 1

Query 2

Query 3

Query 4