Veeva OpenData hierarchy data

OpenData HCO records (US only) include a Hierarchy section that contains the Veeva IDs of specific related HCOs.

The Hierarchy section contains the following fields:

  • Immediate Parent (immediate_parent__v)

    Veeva ID (VID) of the HCO parent with the 'Ownership' relationship type.

  • Hospital Parent (hospital_parent__v)

    Veeva ID of the hospital that the HCO rolls up to.

  • Regional Health System (regional_health_system__v)

    Veeva ID that the HCO rolls up to. This HCO is the child of a Health System and its key_hco_network__v field value = 'Y'

  • Top Parent (top_parent__v)

    Veeva ID of the top parent for this entity.

These fields are managed by Veeva OpenData and are read only; they cannot be changed by submitting data change requests.

Enable the fields

The fields were added in Network version 23R1.0. They are enabled by default in all new instances. Administrators and Data Managers can enable the fields in existing instances.

Note: The Hierarchy section does not display on US HCO record profiles until one of these fields is enabled.

Viewing HCO details for hierarchy fields

To learn more about the HCO in each hierarchy field, use Network search or use data components.

Search

Copy the VID into the Network search bar to open that HCO record.

Data components

Administrators can create a data component so users can quickly access additional information about the related hierarchy Veeva IDs directly from the HCO record.

Example

Users can click the Hierarchy Details tab (data component) to immediately access details about the related VIDs from the Profile page.

Create a data component

To give users immediate access to details about the hierarchy VIDs, add a data component to the Profile page. The data component will query the Network reporting tables to provide these additional details.

To add the data component:

  1. In the Admin console, click Widgets & Portal > Data Components.

  2. Click Add Data Component.

  3. In the New Data Component pop-up, choose Network Reporting and click Add Data Component.

  4. Type a meaningful Name and Description.

  5. Choose a System.

  6. In the Permissions section, define the following settings:

    • Display In - Choose Network Profile and DCR Pages.

    • Country of the record - Choose Selected Countries and add United States.

    • Entities - Select HCO.

    • User Groups - Choose All Users Except Integration Users or provide access to specific user groups.

  7. In the Component Builder section, define the following:

    • Section Name - Type a name for this component.

    • View Type - Choose Table View.

    • Query - Paste the appropriate query for your US OpenData subscription type into the box.

      Query - Enterprise Level Agreement (ELA) subscriptions

      If you subscribe to all US OpenData records, use this query. It includes access to the OpenData Reporting tables to view not-yet-downloaded OpenData records.

      SELECT
        Hierarchy,
        vid,
        od_hco.corporate_name__v,
        od_hco.hco_type__v,
        od_address.formatted_address__v,
        CASE
            WHEN hco.vid__v IS NOT NULL
            THEN 'LOCAL' -- "LOCAL" indicates that the record exists in this instance
 WHEN od_hco.vid__v IS NOT NULL
            THEN 'OPENDATA' -- "OPENDATA" indicates the record has not yet been downloaded to this instance
            ELSE 'NOT FOUND' -- "NOT FOUND" will be shown if the vid cannot be found in either the local instance or in the associated OpenData instance

        END
            AS "Record Location"
    FROM
        (
            -- The section below retrieves information about the vid that exists in any of the four hierarchy fields. The order of these four fields is fixed. A field will not display if it is empty.
 SELECT
                    'Top Parent' AS Hierarchy,
                    top_parent__v AS vid,
                    4 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Regional Health System' AS Hierarchy,
                    regional_health_system__v AS vid,
                    3 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Hospital Parent' AS Hierarchy,
                    hospital_parent__v AS vid,
                    2 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Immediate Parent' AS Hierarchy,
                    immediate_parent__v AS vid,
                    1 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
        ) parent -- The query only returns information for valid HCO records.
 LEFT JOIN hco
            ON parent.vid = hco.vid__v
        AND hco.record_state__v = 'VALID' LEFT JOIN opendata_na.hco od_hco
            ON parent.vid = od_hco.vid__v
        AND od_hco.record_State__v = 'VALID' -- The address information shown is for an active address with an ordinal value of 1.
 LEFT JOIN opendata_na.address od_address
            ON parent.vid = od_address.entity_vid__v
        AND od_address.address_ordinal__v = 1
        AND od_address.address_status__v = 'A'
    WHERE
        vid IS NOT NULL
    ORDER BY
        sequence ;

      Query - Pay Per Record (PPR) subscriptions

      Use this query if you buy OpenData per record for your US subscription. It does not include the OpenData reporting tables. 

       SELECT
        Hierarchy,
        vid,
        corporate_name__v,
        hco_type__v,
        address.formatted_address__v,
        CASE
            WHEN hco.vid__v IS NOT NULL
            THEN 'LOCAL' -- "LOCAL" indicates that the record exists in this instance
            ELSE 'NOT FOUND' -- "NOT FOUND" will be shown if the vid cannot be found in the local instance. It could reside in the OpenData instance or not exist.

        END
            AS "Record Location"
    FROM
        (
            SELECT
                    'Top Parent' AS Hierarchy,
                    top_parent__v AS vid,
                    4 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Regional Health System' AS Hierarchy,
                    regional_health_system__v AS vid,
                    3 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Hospital Parent' AS Hierarchy,
                    hospital_parent__v AS vid,
                    2 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
            UNION
            SELECT
                    'Immediate Parent' AS Hierarchy,
                    immediate_parent__v,
                    1 AS sequence
                FROM
                    hco
                WHERE
                    vid__v = :vid__v
        ) parent LEFT JOIN hco
            ON parent.vid = hco.vid__v
        AND hco.record_state__v = 'VALID' LEFT JOIN address
            ON address.entity_vid__v = parent.vid
        AND address_ordinal__v = 1
        AND address_status__v = 'A'
    WHERE
        vid IS NOT NULL
    ORDER BY
        sequence ;
    • Select Include only VALID and UNDER_REVIEW records in results.
    • Beside the Show Reference Value Fields As option, choose Labels.

  8. (Optional) Click Test Query to check the output.

    • In the pop-up, add an HCO Veeva ID.

    • Click Continue to see the output.

  9. Save the data component.

The data component will display on US OpenData HCO records.