Requisition Status Document

Table

Database	Table	Entity	On Key 5 Conversions	On Key 5 Tables	Other Entities stored in same Table
`Transaction`	`mm.requisition_status_document`	Requisition Status Document	ConvertRequisitionStatusDocument	`stdDocumentLinks`

Columns

Property	Db Table	Db Column	Db DataType	Nullable	Primary Key	Joins
`RequisitionStatusId`	`mm.requisition_status_document`	`requisition_status_id`	bigint	False
`RequisitionStatusCode`	`mm.requisition_status`	`code`	character varying(50)	False		`mm.requisition_status_document.requisition_status_id = mm.requisition_status.requisition_status_id`
`RequisitionStatusDescription`	`mm.requisition_status`	`description`	character varying(100)	False		`mm.requisition_status_document.requisition_status_id = mm.requisition_status.requisition_status_id`
`IsActive`	`mm.requisition_status_document`	`is_active`	boolean	False
`CreatedByUserId`	`mm.requisition_status_document`	`created_by_user_id`	bigint	False
`CreatedByUserCode`	`uam.user`	`code`	character varying(50)	False		`mm.requisition_status_document.created_by_user_id = uam.user.user_id`
`CreatedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`mm.requisition_status_document.created_by_user_id = uam.user.user_id`
`CreatedOn`	`mm.requisition_status_document`	`created_on`	timestamp without time zone	False
`ModifiedByUserId`	`mm.requisition_status_document`	`modified_by_user_id`	bigint	False
`ModifiedByUserCode`	`uam.user`	`code`	character varying(50)	False		`mm.requisition_status_document.modified_by_user_id = uam.user.user_id`
`ModifiedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`mm.requisition_status_document.modified_by_user_id = uam.user.user_id`
`ModifiedOn`	`mm.requisition_status_document`	`modified_on`	timestamp without time zone	False
`Version`	`mm.requisition_status_document`	`version`	integer	False
`PermissionTreeId`	`mm.requisition_status_document`	`permission_tree_id`	bigint	False
`PermissionTreeCode`	`uam.permission_tree`	`code`	text	False		`mm.requisition_status_document.permission_tree_id = uam.permission_tree.permission_tree_id`
`PermissionTreeDescription`	`uam.permission_tree`	`description`	text	True		`mm.requisition_status_document.permission_tree_id = uam.permission_tree.permission_tree_id`
`Id`	`mm.requisition_status_document`	`requisition_status_document_id`	bigint	False	True
`DocumentRecordId`	`mm.requisition_status_document`	`document_record_id`	bigint	False
`DocumentRecordCode`	`dm.document_record`	`code`	character varying(50)	False		`mm.requisition_status_document.document_record_id = dm.document_record.document_record_id`
`DocumentRecordDescription`	`dm.document_record`	`description`	character varying(100)	False		`mm.requisition_status_document.document_record_id = dm.document_record.document_record_id`
`DocumentRecordFileLocation`	`dm.document_record`	`file_location`	text	True		`mm.requisition_status_document.document_record_id = dm.document_record.document_record_id`
`DocumentRecordFileInfoId`	`dm.document_record_file_store`	`document_record_file_store_id`	bigint	False	True
`DocumentRecordFileFullName`	`dm.document_record_file_store`	`file_full_name`	text	False
`DocumentRecordContentId`	`system.file_content_store_identity`	`file_content_store_identity_id`	bigint	False	True
`DocumentRecordContentSizeInMb`	`system.file_content_store_identity`	`size_in_mb`	numeric(19,4)	False
`DocumentRecordFileTypeId`	`dm.file_attachment_file_type`	`file_attachment_file_type_id`	bigint	False	True
`DocumentRecordFileTypeExtension`	`dm.file_attachment_file_type`	`extension`	text	False
`Notes`	`mm.requisition_status_document`	`notes`	text	True
`SequenceNumber`	`mm.requisition_status_document`	`sequence_number`	integer	True

Queries

The list of example Requisition Status Document queries can be interpreted using the following legend:

Legend	Description
Primary Table Alias	The alias for the `mm.requisition_status_document` 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
`_rsd`	False	False

SELECT
    _rsd.requisition_status_id AS "RequisitionStatusId",
    _rsd.is_active AS "IsActive",
    _rsd.created_by_user_id AS "CreatedByUserId",
    _rsd.created_on AS "CreatedOn",
    _rsd.modified_by_user_id AS "ModifiedByUserId",
    _rsd.modified_on AS "ModifiedOn",
    _rsd.version AS "Version",
    _rsd.permission_tree_id AS "PermissionTreeId",
    _rsd.requisition_status_document_id AS "Id",
    _rsd.document_record_id AS "DocumentRecordId",
    _rsd.notes AS "Notes",
    _rsd.sequence_number AS "SequenceNumber"
FROM
    mm.requisition_status_document _rsd

Query 2

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

SELECT
    _rs.requisition_status_id AS "RequisitionStatusId",
    _rs.code AS "RequisitionStatusCode",
    _rs.description AS "RequisitionStatusDescription",
    _rsd.is_active AS "IsActive",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rsd.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rsd.modified_on AS "ModifiedOn",
    _rsd.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _rsd.requisition_status_document_id AS "Id",
    _dr.document_record_id AS "DocumentRecordId",
    _dr.code AS "DocumentRecordCode",
    _dr.description AS "DocumentRecordDescription",
    _dr.file_location AS "DocumentRecordFileLocation",
    _drfs.document_record_file_store_id AS "DocumentRecordFileInfoId",
    _drfs.file_full_name AS "DocumentRecordFileFullName",
    _drfs.file_content_store_identity_id AS "DocumentRecordContent",
    _fcsi.file_content_store_identity_id AS "DocumentRecordContentId",
    _fcsi.size_in_mb AS "DocumentRecordContentSizeInMb",
    _drfs.type_id AS "DocumentRecordFileType",
    _faft.file_attachment_file_type_id AS "DocumentRecordFileTypeId",
    _faft.extension AS "DocumentRecordFileTypeExtension",
    _rsd.notes AS "Notes",
    _rsd.sequence_number AS "SequenceNumber"
FROM
    mm.requisition_status_document _rsd
    JOIN mm.requisition_status _rs ON _rsd.requisition_status_id = _rs.requisition_status_id
    JOIN uam.user _u ON _rsd.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rsd.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _rsd.permission_tree_id = _pt.permission_tree_id
    JOIN dm.document_record _dr ON _rsd.document_record_id = _dr.document_record_id
    LEFT JOIN dm.document_record_file_store _drfs ON _dr.document_record_id = _drfs.document_record_file_store_id
    LEFT JOIN system.file_content_store_identity _fcsi ON _drfs.file_content_store_identity_id = _fcsi.file_content_store_identity_id
    LEFT JOIN dm.file_attachment_file_type _faft ON _drfs.type_id = _faft.file_attachment_file_type_id

Query 3

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

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

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
    _rsd.requisition_status_id AS "RequisitionStatusId",
    _rsd.is_active AS "IsActive",
    _rsd.created_by_user_id AS "CreatedByUserId",
    _rsd.created_on AS "CreatedOn",
    _rsd.modified_by_user_id AS "ModifiedByUserId",
    _rsd.modified_on AS "ModifiedOn",
    _rsd.version AS "Version",
    _rsd.permission_tree_id AS "PermissionTreeId",
    _rsd.requisition_status_document_id AS "Id",
    _rsd.document_record_id AS "DocumentRecordId",
    _rsd.notes AS "Notes",
    _rsd.sequence_number AS "SequenceNumber",
    _rsd.permission_tree_id AS "_rsd_permission_tree_id"
FROM
    mm.requisition_status_document _rsd
    JOIN permission_tree_ids permission_join ON _rsd.permission_tree_id = permission_join.permission_tree_id

Query 4

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

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

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
    _rs.requisition_status_id AS "RequisitionStatusId",
    _rs.code AS "RequisitionStatusCode",
    _rs.description AS "RequisitionStatusDescription",
    _rsd.is_active AS "IsActive",
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _rsd.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _rsd.modified_on AS "ModifiedOn",
    _rsd.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _rsd.requisition_status_document_id AS "Id",
    _dr.document_record_id AS "DocumentRecordId",
    _dr.code AS "DocumentRecordCode",
    _dr.description AS "DocumentRecordDescription",
    _dr.file_location AS "DocumentRecordFileLocation",
    _drfs.document_record_file_store_id AS "DocumentRecordFileInfoId",
    _drfs.file_full_name AS "DocumentRecordFileFullName",
    _drfs.file_content_store_identity_id AS "DocumentRecordContent",
    _fcsi.file_content_store_identity_id AS "DocumentRecordContentId",
    _fcsi.size_in_mb AS "DocumentRecordContentSizeInMb",
    _drfs.type_id AS "DocumentRecordFileType",
    _faft.file_attachment_file_type_id AS "DocumentRecordFileTypeId",
    _faft.extension AS "DocumentRecordFileTypeExtension",
    _rsd.notes AS "Notes",
    _rsd.sequence_number AS "SequenceNumber",
    _rsd.permission_tree_id AS "_rsd_permission_tree_id",
    _rs.permission_tree_id AS "_rs_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id",
    _dr.permission_tree_id AS "_dr_permission_tree_id"
FROM
    mm.requisition_status_document _rsd
    JOIN permission_tree_ids permission_join ON _rsd.permission_tree_id = permission_join.permission_tree_id
    JOIN mm.requisition_status _rs ON _rsd.requisition_status_id = _rs.requisition_status_id
    JOIN uam.user _u ON _rsd.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _rsd.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _rsd.permission_tree_id = _pt.permission_tree_id
    JOIN dm.document_record _dr ON _rsd.document_record_id = _dr.document_record_id
    LEFT JOIN dm.document_record_file_store _drfs ON _dr.document_record_id = _drfs.document_record_file_store_id
    LEFT JOIN system.file_content_store_identity _fcsi ON _drfs.file_content_store_identity_id = _fcsi.file_content_store_identity_id
    LEFT JOIN dm.file_attachment_file_type _faft ON _drfs.type_id = _faft.file_attachment_file_type_id

Table of Contents

Requisition Status Document

Table

Columns

Queries

Query 1

Query 2

Query 3

Query 4