Reports
Sample queries
The following queries have been added as Sample Queries in the SQL Query Editor. These queries were previously available as Saved Reports but they have been discontinued. Use these sample queries to report on merged HCOs or HCPs.
These queries are available by default if Reporting is enabled in your Network instance.
Merged HCO Report
Entity: HCO
Country: All
Description: Report of HCOs merged starting from a given date.
Query
SELECT
hco_surviving.vid__v AS "Veeva ID (Surviving)",
hco_non_surviving.vid__v AS "Veeva ID (Non-Surviving)",
hco_revision.created_at AS "Timestamp"
FROM
hco_revision
INNER JOIN hco AS hco_non_surviving ON hco_revision.vid__v = hco_non_surviving.vid__v
INNER JOIN hco AS hco_surviving ON hco_non_surviving.record_merged_vid__v = hco_surviving.vid__v
WHERE
hco_revision.record_state__v = 'MERGED_INTO'
and hco_revision.created_at >= 'INSERT_DATE_HERE' -- i.e. '2022-01-01'
Merged HCP Report
Entity: HCP
Country: All
Description: Report of HCPs merged starting from a given date.
Query
SELECT
hcp_surviving.vid__v AS "Veeva ID (Surviving)",
hcp_non_surviving.vid__v AS "Veeva ID (Non-Surviving)",
hcp_revision.created_at AS "Timestamp"
FROM
hcp_revision
INNER JOIN hcp AS hcp_non_surviving ON hcp_revision.vid__v = hcp_non_surviving.vid__v
INNER JOIN hcp AS hcp_surviving ON hcp_non_surviving.record_merged_vid__v = hcp_surviving.vid__v
WHERE
hcp_revision.record_state__v = 'MERGED_INTO'
and hcp_revision.created_at >= 'INSERT_DATE_HERE' -- i.e. '2022-01-01'
Use a sample query
To report on merged HCOs and HCPs:
-
Select Reports > SQL Query Editor and click Sample Queries.
-
In the Sample Queries dialog, use the search bar to find the query or find the query in the HCO or HCP entity section.
-
Select the query and click Insert Selected Query.
-
In the query editor box, replace the INSERT_DATE_HERE placeholder with a date. The report will display all of the merges of that object from the date that you specify.
-
Click Run Query to view the results.