Schema reference / Tables / view MDX source

service_request

Request for a health-related service or activity to be delivered by a clinician, organisation or agency.

Purpose. A generic framework for a request for a health-related service or activity to be delivered by a clinician, organisation or agency.

Source

Model	Leg	Role
`openEHR-EHR-INSTRUCTION.service_request.v1`	openEHR	defines this table

Columns

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`form_id`	`uuid` → `form`	no	The form this entry was recorded on.	—
`form_block_id`	`uuid`	yes	The block within the form, when the entry is bound to one.	—
`service_name` `service_name_code` `service_name_system` `service_name_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Service name	`/activities[at0001]/description[at0009]/items[at0121]`
`service_type` `service_type_code` `service_type_system` `service_type_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Service type	`/activities[at0001]/description[at0009]/items[at0148]`
`description`	`text`	yes	Description	`/activities[at0001]/description[at0009]/items[at0135]`
`clinical_indication` `clinical_indication_code` `clinical_indication_system` `clinical_indication_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Clinical indication	`/activities[at0001]/description[at0009]/items[at0152]`
`clinical_context`	`text`	yes	Clinical context	`/activities[at0001]/description[at0009]/items[at0154]`
`reason_for_request` `reason_for_request_code` `reason_for_request_system` `reason_for_request_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Reason for request	`/activities[at0001]/description[at0009]/items[at0062]`
`reason_description`	`text`	yes	Reason description	`/activities[at0001]/description[at0009]/items[at0064]`
`intent` `intent_code` `intent_system` `intent_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Intent	`/activities[at0001]/description[at0009]/items[at0065]`
`order_detail` `order_detail_code` `order_detail_system` `order_detail_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Order detail	`/activities[at0001]/description[at0009]/items[at0153]`
`urgency` `urgency_code` `urgency_system` `urgency_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Urgency	`/activities[at0001]/description[at0009]/items[at0068]`
`service_due`	`timestamptz`	yes	Service due	`/activities[at0001]/description[at0009]/items[at0040]`
`supplementary_information`	`boolean`	yes	Supplementary information	`/activities[at0001]/description[at0009]/items[at0076]`
`information_description`	`text`	yes	Information description	`/activities[at0001]/description[at0009]/items[at0078]`
`comment`	`text`	yes	Comment	`/activities[at0001]/description[at0009]/items[at0150]`
`requester_order_identifier_value` `requester_order_identifier_system`	text, text `text`, `text`	yes	Requester order identifier	`/protocol[at0008]/items[at0010]`
`requester`	`uuid`	yes	Requester	`/protocol[at0008]/items[at0141]`
`receiver_order_identifier_value` `receiver_order_identifier_system`	text, text `text`, `text`	yes	Receiver order identifier	`/protocol[at0008]/items[at0011]`
`receiver_organization`	`uuid`	yes	—	—
`receiver_person`	`uuid`	yes	—	—
`request_status` `request_status_code` `request_status_system` `request_status_valueset_id`	coded text `text`, `text`, `text`, `text`	yes	Request status	`/protocol[at0008]/items[at0127]`
`eligibility_guidance`	`text`	yes	Eligibility guidance	`/protocol[at0008]/items[at0158]`
`billing_guidance`	`text`	yes	Billing guidance	`/protocol[at0008]/items[at0156]`

Constraints

check ((service_name_code is null and service_name_system is null and service_name_valueset_id is null) or (service_name is not null and service_name_code is not null and service_name_system is not null and service_name_valueset_id is not null))
check ((service_type_code is null and service_type_system is null and service_type_valueset_id is null) or (service_type is not null and service_type_code is not null and service_type_system is not null and service_type_valueset_id is not null))
check ((clinical_indication_code is null and clinical_indication_system is null and clinical_indication_valueset_id is null) or (clinical_indication is not null and clinical_indication_code is not null and clinical_indication_system is not null and clinical_indication_valueset_id is not null))
check ((reason_for_request_code is null and reason_for_request_system is null and reason_for_request_valueset_id is null) or (reason_for_request is not null and reason_for_request_code is not null and reason_for_request_system is not null and reason_for_request_valueset_id is not null))
check ((intent_code is null and intent_system is null and intent_valueset_id is null) or (intent is not null and intent_code is not null and intent_system is not null and intent_valueset_id is not null))
check ((order_detail_code is null and order_detail_system is null and order_detail_valueset_id is null) or (order_detail is not null and order_detail_code is not null and order_detail_system is not null and order_detail_valueset_id is not null))
check ((urgency_code is null and urgency_system is null and urgency_valueset_id is null) or (urgency is not null and urgency_code is not null and urgency_system is not null and urgency_valueset_id is not null))
check (requester_order_identifier_system is null or requester_order_identifier_value is not null)
check (receiver_order_identifier_system is null or receiver_order_identifier_value is not null)
check (num_nonnulls(receiver_organization, receiver_person) <= 1)
check ((request_status_code is null and request_status_system is null and request_status_valueset_id is null) or (request_status is not null and request_status_code is not null and request_status_system is not null and request_status_valueset_id is not null))

Unique

form_block_id

References

Columns	Target	On delete
`form_id`	`form`	cascade
`form_id, form_block_id`	`form_block`	cascade
`service_name_valueset_id, service_name_code, service_name_system, service_name`	`valueset_code`	restrict
`service_type_valueset_id, service_type_code, service_type_system, service_type`	`valueset_code`	restrict
`clinical_indication_valueset_id, clinical_indication_code, clinical_indication_system, clinical_indication`	`valueset_code`	restrict
`reason_for_request_valueset_id, reason_for_request_code, reason_for_request_system, reason_for_request`	`valueset_code`	restrict
`intent_valueset_id, intent_code, intent_system, intent`	`valueset_code`	restrict
`order_detail_valueset_id, order_detail_code, order_detail_system, order_detail`	`valueset_code`	restrict
`urgency_valueset_id, urgency_code, urgency_system, urgency`	`valueset_code`	restrict
`request_status_valueset_id, request_status_code, request_status_system, request_status`	`valueset_code`	restrict
`requester`	`person`	no action
`receiver_organization`	`organization`	no action
`receiver_person`	`person`	no action

Referenced by

service_request_complex_timing via service_request_id
service_request_supporting_information via service_request_id
service_request_distribution_list via service_request_id
service_request_urgent_contact via service_request_id
service_request_responsible_payer via service_request_id

Indexes

service_request_form_id_idx on form_id
service_request_requester_idx on requester
service_request_receiver_organization_idx on receiver_organization
service_request_receiver_person_idx on receiver_person

Child tables

5 tables hang off service_request, one row per repetition.

`service_request_complex_timing`

Child table — one row per repetition, deleted with its parent.

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`service_request_id`	`uuid` → `service_request`	no	—	—
`position`	`integer`	no	Order of this row within its parent, from zero.	—
`service_direction_id`	`uuid`	yes	—	—
`timing_daily_id`	`uuid`	yes	—	—
`timing_nondaily_id`	`uuid`	yes	—	—

Constraints

check (num_nonnulls(service_direction_id, timing_daily_id, timing_nondaily_id) = 1)

Unique

service_request_id, position

References

Columns	Target	On delete
`service_request_id`	`service_request`	cascade
`service_direction_id`	`service_direction`	no action
`timing_daily_id`	`timing_daily`	no action
`timing_nondaily_id`	`timing_nondaily`	no action

Indexes

service_request_complex_timing_service_direction_id_idx on service_direction_id
service_request_complex_timing_timing_daily_id_idx on timing_daily_id
service_request_complex_timing_timing_nondaily_id_idx on timing_nondaily_id

`service_request_supporting_information`

Child table — one row per repetition, deleted with its parent.

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`service_request_id`	`uuid` → `service_request`	no	—	—
`position`	`integer`	no	Order of this row within its parent, from zero.	—
`attachment_id`	`uuid`	no	Supporting information	`/activities[at0001]/description[at0009]/items[at0149]`

Unique

service_request_id, position
service_request_id, attachment_id

References

Columns	Target	On delete
`service_request_id`	`service_request`	cascade
`attachment_id`	`attachment`	restrict

Indexes

service_request_supporting_information_attachment_id_idx on attachment_id

`service_request_distribution_list`

Child table — one row per repetition, deleted with its parent.

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`service_request_id`	`uuid` → `service_request`	no	—	—
`position`	`integer`	no	Order of this row within its parent, from zero.	—
`organization_id`	`uuid`	yes	—	—
`person_id`	`uuid`	yes	—	—

Constraints

check (num_nonnulls(organization_id, person_id) = 1)

Unique

service_request_id, position

References

Columns	Target	On delete
`service_request_id`	`service_request`	cascade
`organization_id`	`organization`	no action
`person_id`	`person`	no action

Indexes

service_request_distribution_list_organization_id_idx on organization_id
service_request_distribution_list_person_id_idx on person_id

`service_request_urgent_contact`

Child table — one row per repetition, deleted with its parent.

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`service_request_id`	`uuid` → `service_request`	no	—	—
`position`	`integer`	no	Order of this row within its parent, from zero.	—
`organization_id`	`uuid`	yes	—	—
`person_id`	`uuid`	yes	—	—

Constraints

check (num_nonnulls(organization_id, person_id) = 1)

Unique

service_request_id, position

References

Columns	Target	On delete
`service_request_id`	`service_request`	cascade
`organization_id`	`organization`	no action
`person_id`	`person`	no action

Indexes

service_request_urgent_contact_organization_id_idx on organization_id
service_request_urgent_contact_person_id_idx on person_id

`service_request_responsible_payer`

Child table — one row per repetition, deleted with its parent.

Column	Type	Null	Description	Source
`id`	`uuid primary key`	no	Primary key.	—
`service_request_id`	`uuid` → `service_request`	no	—	—
`position`	`integer`	no	Order of this row within its parent, from zero.	—
`organization_id`	`uuid`	yes	—	—
`person_id`	`uuid`	yes	—	—

Constraints

check (num_nonnulls(organization_id, person_id) = 1)

Unique

service_request_id, position

References

Columns	Target	On delete
`service_request_id`	`service_request`	cascade
`organization_id`	`organization`	no action
`person_id`	`person`	no action

Indexes

service_request_responsible_payer_organization_id_idx on organization_id
service_request_responsible_payer_person_id_idx on person_id

Value sets

Value set	Codes
`org.openehr::openEHR-EHR-INSTRUCTION.service_request.v1/urgency`	3