Definition

The Organisation model is the core reference dataset for healthcare organisations across the data platform. It provides organisation details, including name, ODS code, organisation type, address, and open/closed status.

All data models provides reference to organisation and can be linked to this model for further information.

Information

The model contains every organisation referenced anywhere in the data set you have access to. That includes the organisations covered by your data sharing agreement, organisations that have interacted with those organisations’ patients (for example a referral target or an out-of-hours provider), and organisations referenced only by a user record.

This model contains the following key information for organisations:

• Record identifiers
• Organisation details
• Organisation lifecycle and status
• Linking fields to location and address details

Grain and Scope

Each record is uniquely identified by either combining organisation_idand organisation or the organisation_uuid column.

This model has one row per organisation, per EMIS Web instance. organisation identifies the EMIS Web instance that the data came from, in the form CDB-50002. It appears on every model in iPCV. organisation_id is only unique within an instance, so the same numeric ID can refer to different organisations in different instances.

Always include organisation in the join predicate. Joining on organisation_id alone will silently match unrelated organisations across instances.

This model includes the following key identifiers:

• organisation_id: The unique internal identifier for the organisation record within an organisation in an EMIS Web instance.
• organisation_guid: The GUID for the organisation within an organisation in an EMIS Web instance.
• organisation_uuid: The UUID derived from organisation_id and organisation, providing a stable unique identifier.
• ods_code: The NHS unique identifier for the organisation matching to NHS reference data and other national data sets.
• customer_database_id: The EMIS customer database (CDB) number. This is the numeric part of the organisation value.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether organisation record has been deleted at source.
• is_open: Indicates whether the organisation is open and can change between runs. Since is_open is recalculated every run rather than taken as supplied, an organisation’s status reflects the state at the time the model last ran.
• open_datetime: The timestamp indicating when the organisation was opened.
• close_datetime: The timestamp indicating when the organisation was closed.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Identifying your own organisations

is_this_organisation is TRUE for the organisation that owns the EMIS Web instance identified by the organisation column — that is, the practice or provider whose data you have been granted access to. All other rows for that instance represent organisations referenced within that practice’s data.

Use this flag when you want to scope results to the practices covered by your data sharing agreement, rather than every organisation mentioned across the dataset.

Lifecycle

When a customer organisation closes, every Organisation row belonging to that EMIS Web instance is marked is_deleted = TRUE, not just the row for the practice itself. The rows are retained so you keep an auditable record, but the same organisations are removed entirely from Location, Organisation Location and User In Role.

Filtering guidance

Most Organisation queries want one or more of the following in the WHERE clause:

Purpose	Filter
Exclude deleted and closed customer organisations	is_deleted = FALSE
Restrict to the practices in your sharing agreement	is_this_organisation = TRUE
Restrict to organisations still trading	is_open = TRUE
Restrict to a single EMIS Web instance	organisation = 'CDB-50002'
Restrict to a type of provider	organisation_type_description = 'General Practice'

Things to be aware of

• Row counts grow over time. The model includes any organisation referenced anywhere in the data set, so new organisations appear as new referrals, registrations and users are recorded. Do not treat the row count as a measure of practice coverage — use is_this_organisation = TRUE for that.

• is_open can change between runs, because it is recalculated from close_datetime each time the model runs.

• name is not a reliable key. Names can change and are not unique. Join on organisation_id + organisation, or on organisation_uuid.

• Not every organisation has an ODS code. commissioner is derived from the ODS code, so it is NULL wherever the ODS code is missing or unmatched.

• Deleted rows return NULL for most attributes.

Overview

flowchart TD
    n7["Organisation 123"] --> n10["Patient A"]
    n5["Organisation 98"] --> n10
    n6["Organisation 123"] --> n11["Patient B"]
    n4["Organisation 20"] --> n11
    n8["Organisation 47"] --> n11
    n9["Organisation 123"] --> n12["Patient C"]
    n10 --> n13["Gather Used Organisations"]
    n11 --> n13
    n12 --> n13
    n13 --> n16["Unique Organization IDs: 123, 98, 20, 47"]
    n16 --> n14["Organisation Model"]

    classDef nodeStyle stroke:#9961a4;
    class n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16 nodeStyle;

    linkStyle default stroke:#117abf,fill:none;

Examples

Get main organisation

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.organisation_v2
WHERE
  is_this_organisation = TRUE;

Find closed organisations

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.organisation_v2
WHERE
  is_open = FALSE;

Get organisation and location

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.organisation_v2 org
  JOIN hive.explorer_ipcv_vanilla.location_v2 loc ON org.emis_main_location_guid = loc.emis_location_guid
  AND org.organisation = loc.organisation;