Changes in iPCV V2
1. Introducing Sharing Agreement
Section titled “1. Introducing Sharing Agreement”Sharing Agreement is a new model that replaces Sharing Organisation and adds new functionality. The changes between V1 and V2 for the 2 models are highlighted below.
a. Sharing Agreement carries both sides of the agreement
Section titled “a. Sharing Agreement carries both sides of the agreement”What changed: Sharing Organisation in v1 described only the sharing
organisation. V2 describes the requester organisation, the sharer organisation
and the agreement itself, using requester_*, sharer_* and agreement_*
prefixes, and adds activity metrics for the sharer organisation.
Effect on your data: The generic name, ods_code, organisation_cdb and
emis_organisation_guid columns no longer exist. Their nearest equivalents are
sharer_name, sharer_ods_code, sharer_cdb and sharer_organisation_guid.
Customer Action: Repoint your queries at the new model. Validate downstream systems or reports to ensure they can utilise the new columns effectively.
b. Sharing Agreement status is now descriptive rather than boolean
Section titled “b. Sharing Agreement status is now descriptive rather than boolean”What changed: The is_activated_flag and disabled booleans have been
replaced by requester_status, sharer_status and agreement_status, which
returns text representation. sharer_status also takes the organisation’s close
date into account, so a closed practice is reported as 05. Closed Site rather
than simply “not activated”.
Effect on your data: Organisations with a close date report a different
status to the equivalent v1 flag. The underlying booleans are still available as
requester_is_activated, requester_was_activated, sharer_is_activated and
sharer_was_activated if you need them.
Customer Action: Replace is_activated_flag = TRUE with
sharer_status = '02. Activated', and replace disabled logic with
agreement_status. The full value lists are on the Sharing Agreement definition
page.
c. Sharing Agreement records are only attributed to ‘EMIS’ when deleted
Section titled “c. Sharing Agreement records are only attributed to ‘EMIS’ when deleted”What changed: V1 set the organisation column to EMIS whenever an
agreement was not activated. V2 sets it to EMIS only when the agreement record
is deleted; every other row is attributed to the sharer organisation.
Effect on your data: Rows that are not activated and not deleted are now
attributed to the real organisation (sharer). As a knock-on effect,
last_modified_date values also differ for those rows, because they are now
taken from the sharer organisation record.
Customer Action: Remove any logic that inferred “not activated” from
organisation = 'EMIS', and use sharer_status instead. Validate downstream
systems or reports to ensure they can utilise the new columns effectively.
d. Sharing Agreement joins on agreement GUID rather than name
Section titled “d. Sharing Agreement joins on agreement GUID rather than name”What changed: V1 linked organisation-level and user-level agreement data on
the agreement name. V2 links on agreement_guid.
Effect on your data: Agreement names are not always uniquely populated, so in V2 we have updated the logic to join on agreement_guid to ensure there’s no conflicts with agreement names.
Customer Action: Use agreement_guid as your agreement key, not
agreement_name.
2. Merging Practitioner into User In Role
Section titled “2. Merging Practitioner into User In Role”In V2, User in Role model has absorbed information which was previously under the Practitioner model, to eliminate duplication of data across the two models.
a. Practitioner data has moved into User In Role
Section titled “a. Practitioner data has moved into User In Role”What changed: The practitioner model has been removed. Practitioner
attributes are merged into columns on user_in_role_v2.
Effect on your data: You no longer need to join or reconcile two views, and
every user in role carries its practitioner attributes directly. Practitioner
columns are NULL for users who do not hold the relevant professional
registration.
Customer Action: Repoint practitioner queries at user_in_role_v2 and drop
the join to practitioner.
b. The combined professional identifier has been split
Section titled “b. The combined professional identifier has been split”What changed: V1 held a single professional_identifier column that could
contain a GMC, GDC or NMC number. V2 exposes each in its own column, alongside
identifier_issuing_body recording which body issued it.
Effect on your data: You no longer need to infer the identifier type from its format.
Customer Action: Replace professional_identifier with the relevant one of
general_medical_council_number, general_dental_council_number or
nursing_and_midwifery_council_number, or use COALESCE(...) across all three
together with identifier_issuing_body.
3. Removal of columns
Section titled “3. Removal of columns”In iPCV v1, several attributes of other entities were directly included in the organisation and user related models. In iPCV v2, the below fields have been removed from the data models, and relevant IDs are provided to retrieve this information from dedicated extended data models.
| Model | Decommissioned Column | Reason / Extended Data Model to be Referred |
|---|---|---|
| Organisation | ccg_emis_organisation_id | No longer available in v2 |
| Organisation | ccg_ods_code | No longer available in v2 |
| Organisation | parent_type | No longer available in v2 |
| Organisation | releaseversion | No longer available in v2 |
| Organisation | role | No longer available in v2 |
| Organisation location | emis_main_location_guid | Available in the model itself as location_guid. This model now includes all locations and not just main ones |
| Organisation location | processing_id | No longer available in v2 |
| Sharing Agreement | _ingest_time | No longer available in v2. |
| Sharing Agreement | name | No longer available in v2. |
| Sharing Agreement | emis_organisation_guid | No longer available in v2. |
| Sharing Agreement | ods_code | No longer available in v2. |
| Sharing Agreement | organisation_cdb | No longer available in v2. |
| Sharing Agreement | ods_code | No longer available in v2. |
| Sharing Agreement | disabled | Available in the model itself |
| Sharing Agreement | is_activated_flag | Available in the model itself |
| User In Role | employer_id | Available as organisation_id / organisation_uuid in the Organisation model |
| User In Role | employer_ods_code | Available as ods_code in the Organisation model |
| User In Role | employer_cdb | Available as customer_database_id in the Organisation model |
| User In Role | sds_role_profile_id | Almost never populated and provided no analytical value |
Why?
- Simplifies the organisation and users models, making it lighter and easier to manage.
- By moving specific attributes to their own models, data redundancy is reduced, and maintainability is improved.
Customer benefit
- Improved Performance: Leaner models can lead to faster query performance.
- Enhanced Scalability: Decoupled models are easier to maintain and extend independently.
- Data Consistency: Centralising specific attributes in their own models ensures a single source of truth.
Customer action
- Review all reports and queries that rely on the decommissioned fields listed above.
- Update them to join with the appropriate extended data models using the new IDs provided in the table.
- Test thoroughly to ensure data accuracy and query performance with the new model structure.
4. NULL value columns
Section titled “4. NULL value columns”Several columns in the organisation and user related models now consistently return NULL values and have been retained for backward compatibility.
| Model | Decommissioned Column | Reason / Extended Data Model to be Referred |
|---|---|---|
| Organisation | emis_ccg_organisation_id | No longer available in v2 |
| Organisation | parent_emis_organisation_id | No longer available in v2 |
| Organisation | full_address | No longer available in v2 |
| Location | emis_parent_location_guid | Available in the location model itself |
| All models | processing_id | Retained for backward compatibility and populated as NULL in v2 |
Why? These columns are retained to maintain backward compatibility while returning NULL values to ensure consistent behavior in downstream processes.
Customer benefit
- Reduced risk of unexpected behavior in downstream processes.
- Clear distinction between deprecated columns and new standardized relationships.
Customer action
- Identify any queries or reports that use the deprecated columns and replace/remove them.
- Update processes to use
parent_location_idjoining withlocation_v2onlocation_idto retrieve parent location details.
5. Renamed columns
Section titled “5. Renamed columns”Several columns in the organisation and user related models have been renamed due to merging of models or to provide consistent column names across models.
| Model | Decommissioned Column | Relevant ID/ Column | Reason / Extended Data Model to be Referred |
|---|---|---|---|
| Organisation | deleted | is_deleted | Available in the model itself |
| Location | _base_ingest_time | _ingest_time | Consistent column naming |
| Sharing Agreement | deleted | is_deleted | Available in the model itself |
| Sharing Agreement | emis_organisation_guid | sharer_organisation_guid | Available in the model itself |
| Sharing Agreement | name | sharer_name | Available in the model itself |
| Sharing Agreement | ods_code | sharer_ods_code | Available in the model itself |
| Sharing Agreement | organisation_cdb | sharer_organisation_cdb | Available in the model itself |
| Sharing Agreement | is_activated_flag | sharer_is_activated | Available in the model itself |
| Sharing Agreement | last_modified_date | last_modified_datetime | Available in the model itself |
| User In Role | forename | given_name | Merging of Practitioner and User In Role model |
| User In Role | hcp_type | healthcare_practitioner_type | Merging of Practitioner and User In Role model |
| User In Role | gmp_number | general_medical_practitioner_number | Merging of Practitioner and User In Role model |
| User In Role | local_id | user_id | Merging of Practitioner and User In Role model |
| User In Role | emis_user_id | user_id/user_in_role_id | Merging of Practitioner and User In Role model. Some flavours have the user_in_role_id set as emis_user_id. In v2, there are separate columns for user_id and user_in_role_id |
| User In Role | emis_user_id | user_id | Merging of Practitioner and User In Role model |
| User In Role | local_job_role | job_category_name | Merging of Practitioner and User In Role model |
| User In Role | sds_job_role_code | job_category_code | Merging of Practitioner and User In Role model |
| User In Role | specialty | organisation_specialities | Merging of Practitioner and User In Role model |
| User In Role | professional_identifier | general_medical_council_number, general_dental_council_number, nursing_and_midwifery_council_number | Merging of Practitioner and User In Role model |
| User In Role | emis_job_categoy_name | emis_job_categoy_name | Merging of Practitioner and User In Role model |
Why
- Several organisation and user-related columns were renamed to align naming across merged models and remove duplicated or conflicting semantics.
- Standardised names make the data model more consistent, especially where the same concept appears in multiple tables.
Customer Benefit
- Easier querying and onboarding; the same concept now uses the same column name across models.
Customer Action
- Review downstream data for references to old column names.
- Update field mappings, semantic layers, and data dictionaries to the new standard names.
- Run regression checks on key reports to confirm outputs.
6. Additions in V2
Section titled “6. Additions in V2”New columns have been added to enhance auditability, relationships and more information. The models now include the below columns:
Relationship Columns:
| Column Name | Organisation | Location | Organisation Location | User In Role |
|---|---|---|---|---|
organisation_id | ✓ | ✗ | ✓ | ✗ |
organisation_uuid | ✓ | ✗ | ✓ | ✓ |
organisation_type_id | ✓ | ✗ | ✗ | ✗ |
address_id | ✓ | ✓ | ✓ | ✗ |
address_uuid | ✓ | ✓ | ✓ | ✗ |
main_location_id | ✓ | ✗ | ✗ | ✗ |
main_location_uuid | ✓ | ✗ | ✗ | ✗ |
region_id | ✓ | ✗ | ✗ | ✗ |
location_uuid | ✗ | ✓ | ✓ | ✗ |
location_type_id | ✗ | ✓ | ✗ | ✗ |
parent_location_id | ✗ | ✓ | ✗ | ✗ |
user_in_role_uuid | ✗ | ✗ | ✗ | ✓ |
user_id | ✗ | ✗ | ✗ | ✓ |
user_uuid | ✗ | ✗ | ✗ | ✓ |
sds_user_id | ✗ | ✗ | ✗ | ✓ |
sds_user_uuid | ✗ | ✗ | ✗ | ✓ |
Organisation Columns:
| Column Name | Description |
|---|---|
is_this_organisation | Identifies the customer organisation that owns the EMIS Web instance |
speciality_codes | National speciality codes recorded against the organisation |
commissioner | Commissioning body, derived from the organisation’s ODS code |
User In Role
Section titled “User In Role”| Column | Description |
|---|---|
general_medical_council_number, general_dental_council_number, nursing_and_midwifery_council_number | Split out of the v1 professional_identifier |
identifier_issuing_body | Which professional body issued the identifier |
Why? These columns improve the model by making relationships explicit, standardising cross-model keys and exposing data that was previously embedded or duplicated elsewhere.
Customer benefit
- Easier joins across organisations, locations and user records
- More complete organisation and user context in downstream reporting
- Clearer record lineage and easier migration from v1
Customer action
- Include the relevant relationship keys in joins and analysis workflows
- Replace any old embedded identifier logic with the new split identifier columns
- Use the organisation and user relationship fields where you need explicit model links
7. Enhancements / Changes to Existing Columns
Section titled “7. Enhancements / Changes to Existing Columns”a. Organisation - name now holds the EMIS Web display name
Section titled “a. Organisation - name now holds the EMIS Web display name”What changed: The organisation name is now sourced from the organisation’s display name in EMIS Web, rather than the name held internally in the source database.
Effect on your data: The name column is unchanged in name and type, but
some values differ. Names now match what a user sees in the EMIS Web interface.
Customer Action: Review any logic that matches organisations on name.
b. Organisation - is_open is calculated during transformation
Section titled “b. Organisation - is_open is calculated during transformation”What changed: In v1 the open/closed status was taken as supplied at ingestion. In v2 it is recalculated from the organisation’s close date each time the model runs, using the close date recorded in EMIS Index in preference to the one held in EMIS Web.
Effect on your data: Status is more current, and a small number of organisations have a different status to v1.
Customer Action: No action required, but be aware that is_open can change
between runs for the same organisation.
c. Location/Organisation Location/User In Role - Closed customer organisations are handled explicitly
Section titled “c. Location/Organisation Location/User In Role - Closed customer organisations are handled explicitly”What changed: When a customer organisation closes, v2 marks every
Organisation row for that organisation as is_deleted = TRUE, and excludes that
organisation entirely from Location, Organisation Location and User In Role.
Effect on your data: Location and User In Role rows disappear for closed organisations rather than lingering. Organisation rows remain, but flagged as deleted, so you retain an auditable record.
Customer Action: Include is_deleted = FALSE in your Organisation queries,
and expect Location and User In Role row counts to drop when an organisation
closes.
d. Location - postcodes are standardised
Section titled “d. Location - postcodes are standardised”What changed: v1 used a calculated source field that removed spaces but was truncated to seven characters and did not handle dashes or mixed case. v2 derives the Location postcode from the full source postcode and normalises it by removing all non-alphanumeric characters and converting to upper case.
Effect on your data: Location postcodes are consistently formatted and are
no longer truncated, for example LS997BZ. Note that the postcode on the
Organisation model is a separate field and retains its normal spacing, for
example LS99 7BZ.
Customer Action: If you match or join on Location postcode, apply the same normalisation to your comparison values, and do not assume Location and Organisation postcodes are formatted identically.
e. Organisation Location - All locations are now available, not just the main one
Section titled “e. Organisation Location - All locations are now available, not just the main one”What changed: v1 exposed only an organisation’s main location, via
main_location_guid on the Organisation model. v2 adds the Organisation
Location bridge model, which lists every location an organisation operates from
and flags the main one.
Effect on your data. Organisations that operate across multiple sites now
have multiple rows in Organisation Location. main_location_id /
main_location_guid remain on the Organisation model, so existing main-location
logic still works.
Customer Action: Use Organisation Location when you need all sites; keep using the main location columns on Organisation when you only need the primary site. Where the model is available in your flavour, joining Organisation to Organisation Location without filtering on the main-location flag will multiply your rows.