Change request query samples

AD
DM
DS

All information about change requestsClosed A request to change data from Veeva CRM, the Network API, or from within the Network user interface. Change requests are processed by either customer or Veeva data stewards, depending on ownership of the data being changed. is contained in tables with the prefix change_request. Core information about change requests, such as the resolution date and user, are contained in the change_request table.

For details about specific fields in a change request, you should join tables for the appropriate entityClosed A high-level record attribute; in general, an HCO or HCP. type. For example, to find out how many address change requests are approved, you would join change_request_address.

For each field in your Network data model, there are four corresponding fields in the change_request tables:

  • field_name_old: the previous value of the field, where applicable
  • field_name_req: the requested value of the field by the user
  • field_name_fin: the final value of the field
  • field_name_res: the result for the particular field (for example, change_rejected)

For change requests on Veeva OpenData records, you can query for status information about the related master task in the change_request_master table. 

The following tables describe the fields used for queries:

  • change_request
  • change_request_master

Field

Label

Description

change_request_country

Country

Primary country of the change request.

change_request_id

Change request ID

Unique identifier for the change request.

change_request_type

Type

Was the DCR originally created as an add request or a change request?

completed_by

Completed by

Data steward that resolved the change request.

completed_date

Completed date

Date and time the change request was reviewed and resolved by a data steward or the Network application.

created_by

Created by

Network user that created the change request.

created_date

Created date

Date and time the change request was created.

description

Description

Detailed notes provided by the DCR submitter.

entity_type

Entity type

Is the related entity a HCP or a HCO?

linked_suspect_match_id

Related suspect match

Link to the related suspect match task if applicable.

originating_system

Originating system

Applicable to Veeva OpenData only. Name of the customer instance that submitted the DCR.

owner

Owner

Data steward that is current assigned to this task.

resolution

Resolution

Outcome of the change request.

resolution_notes

Resolution notes

Detailed notes provided by the data steward that resolved the change request.

source

Source

Name of the system that submitted the DCR. If the DCR came from the Network user interface then this value will be 'Entity Profile Editor'.

state_key

State key

Current processing state of the change request.

subject

Subject

Name of the related HCP/HCO, or simply 'HCP/HCO' if the DCR was created as an add request.

vid__v

Network entity ID

Network entity ID of the related HCP or HCO.

vid_key

Key

Custom key that was submitted with the DCR. For DCRs originating from Veeva CRM this value is a Salesforce ID.

Field

Label

Description

change_request_id

Change request ID

Unique identifier for the change request.

completed_date

Completed date

Date and time the change request was reviewed and resolved by a data steward.

created_date

Created date

Date and time the change request was created.

jobs

Jobs

Jobs relating to the task.

master_task_id

Master instance change request ID

Unique identifier for the Veeva OpenData task.

master_id

Master instance ID

The instance identifier for the VeevaOpenData instance.

master_key

Master instance key

Unique key for the Veeva OpenData instance.

result

Result

The result of the task.

updated_date

Updated date

Date and time the change request was updated.

DCR details

This example provides details on change requests submitted in the last five days.

SELECT
        change_request_id,
        vid_key,
        resolution,
        resolution_notes,
        completed_by,
        completed_date
    FROM
        change_request
    WHERE
        Datediff (
            'day',
            completed_date,
            CURRENT_DATE
        ) < 5
    ORDER BY
        completed_date DESC ;

Sample results:

DCR details with master status

This example returns details of the DCRs created in the last five days along with the status of the related master DCR:

SELECT
        c.change_request_id,
        c.vid__v,
        c.resolution,
        c.resolution_notes,
        c.completed_by,
        c.completed_date,
        m.result,
        m.completed_date
    FROM
        change_request c LEFT OUTER JOIN change_request_master m
            ON c.change_request_id = m.change_request_id
    WHERE
        Datediff (
            'day',
            c.completed_date,
            CURRENT_DATE
        ) < 5
    ORDER BY
        c.completed_date DESC ;

DCR trends by week

This example provides a breakdown, by week, of all change requests approved by a data steward in the past year.

SELECT
        Date_trunc (
            'week',
            completed_date
        ),
        COUNT (*)
    FROM
        change_request
    WHERE
        completed_by = 'master.steward@veevanetwork.com'
        AND Datediff (
            'year',
            completed_date,
            CURRENT_DATE
        ) < 1
    GROUP BY
        Date_trunc (
            'week',
            completed_date
        )
    ORDER BY
        Date_trunc (
            'week',
            completed_date
        ) DESC ;

Sample results:

DCR field details

This example provides details of all the change requests for specialty 1.

SELECT
        c.change_request_id,
        h.vid__v,
        specialty_1__v_req,
        specialty_1__v_res,
        completed_by,
        completed_date
    FROM
        change_request c JOIN change_request_hcp h
            ON c.change_request_id = h.change_request_id
    WHERE
        specialty_1__v_req IS NOT NULL
    ORDER BY
        completed_date DESC ;

Sample results:

Multiple people submitting DCRs against the same record

This example finds records where multiple people submitted address DCRs within the same time period. This is a complex query, constructed in multiple stages:

  1. Filter the DCRs by:
    • Address Line 1 requested change is not null (DCR creator requested a change to line 1)
    • Network Entity is not null (not a rejected Add Request)
    • Created after Jan 1, 2015
  2. Summarize all DCRs grouping by:
    • Network Entity ID (same HCP/HCO)
    • Quarter (same time period)
    • Created By
  3. Find HCPs/HCOs that had more than one user create a DCR in the same time period
  4. Join to change_request and change_request_address to include relevant information for each change request
SELECT
        d.vid__v,
        d.quarter,
        c.change_request_id,
        change_request_original_type,
        subject,
        description,
        entity_type,
        resolution,
        c.source,
        created_date,
        created_by,
        completed_date,
        completed_by,
        address_line_1__v_req,
        address_line_1__v_res,
        locality__v_req,
        locality__v_res,
        administrative_area__v_req,
        ca.administrative_area__v_res
    FROM
        (
            SELECT
                    vid__v,
                    quarter,
                    COUNT (1) AS num_creators
                FROM
                    (
                        SELECT
                                vid__v,
                                Date_trunc (
                                    'quarter',
                                    created_date
                                ) AS quarter,
                                created_by,
                                COUNT (vid__v) AS num_dcrs
                            FROM
                                change_request c
                            WHERE
                                vid__v IS NOT NULL
                                AND created_date > '2015-01-01'
                                AND EXISTS (
                                    SELECT
                                            change_request_id
                                        FROM
                                            change_request_address ca
                                        WHERE
                                            ca.change_request_id = c.change_request_id
                                            AND address_line_1__v_req IS NOT NULL
                                )
                            GROUP BY
                                vid__v,
                                quarter,
                                created_by
                    ) d
                GROUP BY
                    vid__v,
                    quarter
                HAVING
                    num_creators > 1
        ) d JOIN change_request c
            ON c.vid__v = d.vid__v
        AND d.quarter = Date_trunc (
            'quarter',
            c.created_date
        ) JOIN change_request_address ca
            ON c.change_request_id = ca.change_request_id
    ORDER BY
        d.vid__v DESC,
        c.created_date DESC ;

Sample results:

Users re-submitting the same DCR

This example identifies the same individual repeatedly submitting DCRs with similar content. To simplify the problem we make a couple of assumptions:

  1. We’re only interested in ‘duplicate’ DCRs within a given time window. In this example we chose quarter, but month or week would be equally viable.
  2. DCRs are considered similar if the user requested the exact same change for ‘key’ fields. In this case the ‘key’ fields are specialty_1__v and address_line_1__v.

You can see the completed query below, but the logic can be summarized as:

  1. Filter the DCRs by:
  1. Address Line 1 OR Specialty 1 requested change is not null (DCR creator requested a change to one of the ‘key’ fields)
  2. Network Entity is not null (not a rejected Add Request)
  3. Created after Jan 1, 2015
  1. There can be multiple address changes in a single DCR, so summarize the address DCR changes grouping by:
  1. Network Entity ID (same HCP/HCO)
  2. Change Request ID (Same DCR)
  3. Quarter (same time period)
  4. Created By
  1. Summarize all DCR changes grouping by:
  1. Network Entity ID (same HCP/HCO)
  2. Change Request ID (Same DCR)
  3. Quarter (same time period)
  4. Created By
  1. Find the records and the related user where the user submitted the ‘same’ DCR more than once in the same time period
  2. Join to change_request and change_request_address to include relevant information for each change request
SELECT
        d.vid__v,
        d.quarter,
        c.created_by,
        c.change_request_id,
        change_request_original_type,
        subject,
        description,
        entity_type,
        resolution,
        created_date,
        completed_date,
        completed_by,
        specialty_1__v_req,
        address_line_1__v_req
    FROM
        (
            SELECT
                    d.vid__v,
                    d.quarter,
                    d.created_by,
                    d.specialty_1__v_req,
                    d.address_line_1__v_req,
                    COUNT (d.vid__v) AS num_dcrs
                FROM
                    (
                        SELECT
                                c.vid__v,
                                c.change_request_id,
                                Date_trunc (
                                    'quarter',
                                    created_date
                                ) AS quarter,
                                created_by,
                                specialty_1__v_req,
                                address_line_1__v_req,
                                COUNT (c.vid__v) AS num_address_dcrs
                            FROM
                                change_request c JOIN change_request_hcp h
                                    ON h.change_request_id = c.change_request_id LEFT OUTER JOIN change_request_address a
                                    ON a.change_request_id = c.change_request_id
                            WHERE
                                vid__v IS NOT NULL
                                AND created_date > '2015-01-01'
                                AND (
                                    specialty_1__v_req IS NOT NULL
                                    OR address_line_1__v_req IS NOT NULL
                                )
                            GROUP BY
                                c.vid__v,
                                c.change_request_id,
                                quarter,
                                created_by,
                                address_line_1__v_req,
                                specialty_1__v_req
                    ) d
                GROUP BY
                    d.vid__v,
                    d.quarter,
                    d.created_by,
                    d.address_line_1__v_req,
                    d.specialty_1__v_req
                HAVING
                    num_dcrs > 1
        ) d JOIN change_request c
            ON c.vid__v = d.vid__v
        AND d.quarter = Date_trunc (
            'quarter',
            c.created_date
        )
        AND d.created_by = c.created_by
    ORDER BY
        d.vid__v DESC,
        d.quarter,
        c.created_by,
        c.created_date DESC ;

Sample results:

DCRs sent to Veeva OpenData

This example retrieves change requests that have been sent to Veeva OpenData using the Send to OpenData feature. 

SELECT
        *
    FROM
        change_request
    WHERE
        description like 'OpenData Takeover Request%'

Sample results: