Suspect match query samples

AD
DM
DS

Suspect match tasks details are available in the tables starting with suspect_match. You can query for key information, such as the incoming, match entity ID, and source. For information on which matching features created the suspect match tasks, you can query the suspect_match_feature table.

Suspect match tasks can have many potential matches. To see detailed information about all potential matches you can query the suspect_match_potential table and join to the related HCP/HCO.

The following table describes the fields used for queries:

Field

Label

Description

change_request_id

Change request ID

Link to the related change request if applicable.

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 suspect match task creator or a system-generated message.

match_entity_id

Match entity ID

ID of the matching HCP/HCO if a steward confirmed that this is a duplicate record.

match_entity_type

Match entity type

Entity type of the matching HCP/HCO if a steward confirmed that this is a duplicate record.

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.

primary_entity_id

Primary entity ID

Network entity ID of the HCP or HCO.

primary_entity_type

Primary entity type

Is the primary entity a HCP or a HCO?

proprietary

Is proprietary

Is the primary entity a proprietary record?

resolution

Resolution

Outcome of the change request.

source

Source

Name of the system that submitted the suspect match task. If the suspect match came from the Network user interface, this value will be 'Entity Profile Editor'.

state_key

State key

Current processing state of the suspect match task.

subject

Subject

Name of the related HCP/HCO or the related entity type if the suspect match task was created from an add request.

suspect_match_country

Country

Primary country of the suspect match task.

suspect_match_id

ID

Unique identifier for the suspect match task.

Suspect match details

This example provides details of suspect matches completed in the last 30 days.

SELECT
        suspect_match_id,
        primary_entity_type,
        primary_entity_id,
        match_entity_id,
        resolution,
        completed_by,
        completed_date
    FROM
        suspect_match
    WHERE
        state_key = 'PROCESSED'
        AND Datediff (
            'day',
            completed_date,
            CURRENT_DATE
        ) < 30
    ORDER BY
        completed_date DESC ;

Sample results:

Suspect Matches related to DCRs

This example analyzes the results of suspect matches that resulted from change requests.

SELECT
        suspect_match_id,
        c.change_request_id,
        primary_entity_id,
        primary_entity_type,
        match_entity_id,
        match_entity_type,
        s.created_date,
        s.completed_date,
        s.completed_by,
        s.resolution
    FROM
        suspect_match s JOIN change_request c
            ON c.change_request_id = s.change_request_id

Sample results:

Processed suspect matches by origin

This example generates a list of suspect matches that have been processed, ordered by origin (created_by). The origin can be Network (for master-initiated matches), DataFlow (initiated by a subscription), or a user ID (initiated by a data steward).

Dataflow matches also include an originating system. The description for dataflow-initiated matches includes the job number; other origins include (but are not limited to) the descriptions in the sample results.

SELECT
        suspect_match_id,
        created_date,
        created_by,
        description,
        originating_system,
        state_key,
        resolution,
        suspect_match_country,
        primary_entity_id,
        primary_entity_type,
        match_entity_id,
        match_entity_type,
        change_request_id,
        owner,
        completed_date,
        completed_by
    FROM
        suspect_match
	WHERE
		state_key = 'PROCESSED'
	ORDER BY
		created_by

Sample results: