How to Locate Mismatching MCCP Records in Veeva Align and in Veeva CRM?

Question:

How to locate mismatching Multichannel Cycle Plan (MCCP) records in Veeva Align and in Veeva CRM?

Answer:

MCCP mismatching is explained in the following article, see What is MCCP Mismatching in Veeva CRM and in Veeva Align?

Follow these steps to locate the mismatching records in Veeva Align:

1. Using VQL (Vault Loader), extract the records having the following fields of the MCCP-related objects and rename the CSV files according to the value provided after the hashtags.

#MCC_Channel.csv
select id,external_id__aln,name__v,mc_cycle__aln FROM mc_cycle_channel__aln

#MCC_Prod.csv
select id,external_id__aln,name__v,mc_cycle_channel__aln FROM mc_cycle_product__aln

#MCCP.csv
select id,external_id__aln,name__v,mc_cycle__aln FROM mc_cycle_plan__aln

#MCCP_Target.csv
select id,external_id__aln,name__v,mc_cycle_plan__aln FROM mc_cycle_plan_target__aln

#MCCP_Channel.csv
select id,external_id__aln,name__v,mc_cycle_channel__aln,mc_cycle_plan_target__aln FROM mc_cycle_plan_channel__aln

#MCCP_Prod.csv
select id,external_id__aln,name__v,mc_cycle_product__aln,mc_cycle_plan_channel__aln FROM mc_cycle_plan_product__aln

The Loader UI provides a user-friendly graphical interface to select these fields of specific objects.

2. It is recommended to use DB Browser for SQLite in order to import and perform specific SQL queries to locate these mismatching records at both the MC Cycle Plan Channel and MC Cycle Plan Product levels.

The steps as to how to import and manage data in DB Browser for SQLlite are explained in this article, see: How to Find Duplicate Field Values in a Veeva Align Dataset Containing More Records than Microsoft Excel is Capable of Opening?

Multiple CSVs can be imported at the same time into a database. Be sure to check Column Names in First Line, and uncheck Trim fields. Also, be sure to check Separate Tables and make sure that the encoding is UTF-8 and that the field separator and quote characters are correct. (Comma and double quotes respectively.)

The SQL queries are as follows:

For Align:

#Verify Channels
select D.mc_cycle__aln as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",F.ID as "MCC Channel ID",
F.mc_cycle__aln as "MC Cycle ID (MCC Channel)"
from MCCP_Channel B
left join MCCP_Target C on b.mc_cycle_plan_target__aln=C.ID
left join MCCP D on C.mc_cycle_plan__aln=D.ID
left join MCC_Channel F on F.ID=B.mc_cycle_channel__aln
where F.mc_cycle__aln!=D.mc_cycle__aln

#Verify Products
select D.mc_cycle__aln as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",A.ID as "MCCP Product ID",
A.mc_cycle_product__aln as "MCC Product ID",F.ID as "MCC Channel ID",F.mc_cycle__aln as "MC Cycle ID (MCC Product)"
from MCCP_Prod A
left join MCCP_Channel B on A.mc_cycle_plan_channel__aln=B.ID
left join MCCP_Target C on b.mc_cycle_plan_target__aln=C.ID
left join MCCP D on C.mc_cycle_plan__aln=D.ID
left join MCC_Prod E on A.mc_cycle_product__aln=E.ID
left join MCC_Channel F on F.ID=E.mc_cycle_channel__aln
where F.mc_cycle__aln!=D.mc_cycle__aln

For Vault CRM Align:

#Verify Channels
select D.cycle__v as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",F.ID as "MCC Channel ID",
F.cycle__v as "MC Cycle ID (MCC Channel)"
from MCCP_Channel B
left join MCCP_Target C on b.cycle_plan_target__v=C.ID
left join MCCP D on C.cycle_plan__v=D.ID
left join MCC_Channel F on F.ID=B.cycle_channel__v
where F.cycle__v!=D.cycle__v

#Verify Products
select D.cycle__v as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",A.ID as "MCCP Product ID",
A.cycle_product__v as "MCC Product ID",F.ID as "MCC Channel ID",F.cycle__v as "MC Cycle ID (MCC Product)"
from MCCP_Prod A
left join MCCP_Channel B on A.cycle_plan_channel__v=B.ID
left join MCCP_Target C on b.cycle_plan_target__v=C.ID
left join MCCP D on C.cycle_plan__v=D.ID
left join MCC_Prod E on A.cycle_product__v=E.ID
left join MCC_Channel F on F.ID=E.cycle_channel__v
where F.cycle__v!=D.cycle__v

The results contain the MC Cycle Plan Channel and MC Cycle Plan Product records which have mismatching MC Cycles and the suggested MC Cycle corrections. The first column shows the current MC Cycle, while the last column shows the suggestion. The customer is required to analyze the data and perform the necessary data changes.

Follow these steps to locate the mismatching records in Veeva CRM:

1. The Veeva CRM-related SOQL queries are as follows to extract the data:

#MCC_Channel.csv
select id,external_id_vod__c,name,cycle_vod__c FROM mc_cycle_channel_vod__c

#MCC_Prod.csv
select id,external_id_vod__c,name,cycle_channel_vod__c FROM mc_cycle_product_vod__c

#MCCP.csv
select id,external_id_vod__c,name,cycle_vod__c FROM mc_cycle_plan_vod__c

#MCCP_Target.csv
select id,external_id_vod__c,name,cycle_plan_vod__c from mc_cycle_plan_target_vod__c

#MCCP_Channel.csv
select id,external_id_vod__c,name,cycle_channel_vod__c,cycle_plan_target_vod__c FROM mc_cycle_plan_channel_vod__c

#MCCP_Prod.csv
select id,external_id_vod__c,name,cycle_product_vod__c,cycle_plan_channel_vod__c FROM mc_cycle_plan_product_vod__c

2. The SOQL equivalents are as follows:

#Verify Channels
select D.cycle_vod__c as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",F.ID as "MCC Channel ID",
F.cycle_vod__c as "MC Cycle ID (MCC Channel)"
from MCCP_Channel B
left join MCCP_Target C on b.cycle_plan_target_vod__c=C.ID
left join MCCP D on C.cycle_plan_vod__c=D.ID
left join MCC_Channel F on F.ID=B.cycle_channel_vod__c
where F.cycle_vod__c!=D.cycle_vod__c

#Verify Products
select D.cycle_vod__c as "MC Cycle ID",D.ID as "MCCP ID",C.ID as "MCCP Target ID",B.ID as "MCCP Channel ID",A.ID as "MCCP Product ID",
A.cycle_product_vod__c as "MCC Product ID",F.ID as "MCC Channel ID",F.cycle_vod__c as "MC Cycle ID (MCC Product)"
from MCCP_Prod A
left join MCCP_Channel B on A.cycle_plan_channel_vod__c=B.ID
left join MCCP_Target C on b.cycle_plan_target_vod__c=C.ID
left join MCCP D on C.cycle_plan_vod__c=D.ID
left join MCC_Prod E on A.cycle_product_vod__c=E.ID
left join MCC_Channel F on F.ID=E.cycle_channel_vod__c
where F.cycle_vod__c!=D.cycle_vod__c

Related Documentation:

CRM Help Documentation: