Analyze existing data using Network reports

AD
DM

To review the existing data in your Network instance before you enable the multiple master feature, use the advanced ad hoc queries in Network reports (Reports > Ad Hoc Queries). These reports will identify the HCO/HCP types already being used in your Network instance for a specific country.

HCO/HCP types used for a specified country

To see what HCO/HCP types are currently used in your Network instance, use the following queries:

HCO query 

SELECT
        hco.hco_type__v,
		count(*) as count
    FROM
        hco
	WHERE hco.hco_status__v = 'A'
	      AND hco.primary_country__v = 'CN'
	GROUP BY hco.hco_type__v
	ORDER BY count DESC

Results - The query results for this example will display active records using HCO types in China.

HCP query 

SELECT
        hcp.hcp_type__v,
		count(*) as count
    FROM
        hcp
	WHERE hcp.hcp_status__v = 'A'
	      AND hcp.primary_country__v = 'CN'
	GROUP BY hcp.hcp_type__v
	ORDER BY count DESC

HCO/HCP types that are being used by master sources

To see which master sources are using HCO/HCP types, use the following queries that join the HCO/HCP tables with the custom key table:

HCP query 

SELECT
        hcp.hcp_type__v,
        customkey.custom_key_source_type__v,
		count(*) as count
    FROM
        hcp INNER JOIN customkey
		ON hcp.vid__v = customkey.custom_key_entity_id__v
	WHERE hcp.hcp_status__v = 'A'
	      AND hcp.primary_country__v = 'CN'
	      AND customkey.custom_key_status__v = 'A'
	      AND customkey.custom_key_source_type__v in ('MASTER__v','Third_party_master')
	GROUP BY hcp.hcp_type__v, customkey.custom_key_source_type__v
	ORDER BY hcp.hcp_type__v, customkey.custom_key_source_type__v

Note that in the query, MASTER__v is the Veeva OpenData custom key source. Replace Third_party_master with the custom key source that you defined for your third party master data.

Results - The query results for this example will display the HCP types used in China for the Veeva OpenData subscription and the specified third party master.

HCO query 

SELECT
        hco.hco_type__v,
        customkey.custom_key_source_type__v,
		count(*) as count
    FROM
        hco INNER JOIN customkey
		ON hco.vid__v = customkey.custom_key_entity_id__v
	WHERE hco.hco_status__v = 'A'
	      AND hco.primary_country__v = 'CN'
	      AND customkey.custom_key_status__v = 'A'	
	      AND customkey.custom_key_source_type__v in ('MASTER__v','Third_party_master')
	GROUP BY hco.hco_type__v, customkey.custom_key_source_type__v
	ORDER BY hco.hco_type__v, customkey.custom_key_source_type__v

Note that in the query, MASTER__v is the Veeva OpenData custom key source. Replace Third_party_master with the custom key source that you defined for your third party master data.