Change request query samples



All information about change requestsClosedA 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 entityClosedA 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 table describes the fields used for queries:

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.

DCR details

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

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:

DCR trends by week

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

Sample results:

DCR field details

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

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

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

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.

Sample results: