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?

#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:

#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

3. These queries need to be run on all of the extracts which are imported into the database.

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.

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

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:

• Multichannel Cycle Plans Overview

Align Help Documentation:

• Targeting Overview

3rd Party Tool:

• DB Browser for SQLite

Knowledge Base:

• What is MCCP Mismatching in Veeva CRM and in Veeva Align?
• How to Find Duplicate Field Values in a Veeva Align Dataset Containing More Records than Microsoft Excel is Capable of Opening?