with gpoSources as ( select distinct cho.cho_id, cho.cho_name from tbl_TPM_Claims c inner join tbl_OPR_CLientOperators opr on opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.operatorId = c.partnerId inner join tbl_CHO_Operators cho on cho.cho_id = opr.fsl_choId and cho.cho_type in ( 'C', 'G' ) where c.ownerId > 0 and c.ownerType ='MFR' and c.partnerType = 'OPR' ), gpoOperators as ( select gpo.fsl_choId, gpo.ownerId, gpo.fsltablecode from tbl_OPR_CLientOperators gpo inner join gpoSources s on s.cho_id = gpo.ownerId where gpo.fsltablecode = 'CHO' and gpo.crmActive = 'Y' -- and gpo.crmActiveDate is not null and nullif( gpo.mfrCustNum, '' ) is not null and gpo.fsl_choId > 0 and gpo.oprCompanyType = '0' ) select top #_repeatCount# opr.ownerId, opr.fsltablecode, opr.operatorId, opr.fsl_choId, count( distinct gpo.ownerId ) gpoCount from gpoOperators gpo with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId > 0 and ( ( opr.fsltablecode = 'MFR' and opr.ownerId in ( select mfr_id from tblManufacturers with (nolock) where coalesce( oneFsEnabled, 'N' ) = 'Y' )) or ( opr.fsltablecode = 'BRO' and opr.ownerId in ( select broker_id from tblBrokers with (nolock) where coalesce( oneFsEnabled, 'N' ) = 'Y' )) ) and opr.fsl_choId = gpo.fsl_choId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_Id = opr.fsl_choId where not exists ( select * from tbl_CRM_Affiliations aa with (nolock) inner join tbl_OPR_CLientOperators org with (nolock) on org.ownerId = aa.ownerId and org.fsltablecode = aa.ownertype and org.operatorId = aa.orgId and org.fsl_choId = gpo.ownerId where aa.orgType = 'OPR' and aa.ownerId = opr.ownerId and aa.ownerType = opr.fsltablecode and aa.partnerId = opr.operatorId and aa.partnerType = 'OPR' ) and opr.ownerId = and opr.fsltablecode = 'MFR' group by opr.ownerId, opr.fsltablecode, opr.operatorId, opr.fsl_choId order by ownerId, fsltablecode, operatorId select count(*) newRecords from tbl_CRM_Affiliations with (nolock) where recordDate >= select count(*) newRecords from tbl_CRM_Affiliations with (nolock) where recordDate >=
Loop #lsnumberFormat( url.repeats + 1 )# Added #lsnumberformat( _added )# Affiliations
 with gpoSources as ( select distinct cho.cho_id, cho.cho_name from tbl_TPM_Claims c with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and opr.operatorId = c.partnerId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId and cho.cho_type in ( 'C', 'G' ) where c.ownerId > 0 and c.ownerType ='MFR' and c.partnerType = 'OPR' ) select * from gpoSources where cho_id = order by cho_id with gpoOperators as ( select gpo.fsl_choId, gpo.ownerId, gpo.fsltablecode, gpo.mfrCustNum, gpo.operatorId from tbl_OPR_CLientOperators gpo where gpo.fsltablecode = 'CHO' and gpo.ownerId = and gpo.crmActive = 'Y' and nullif( gpo.mfrCustNum, '' ) is not null and gpo.fsl_choId > 0 and gpo.oprCompanyType = '0' ), toPropagate as ( select distinct gpo.fsl_choId, gpo.ownerId, gpo.fsltablecode, gpo.operatorId from gpoOperators gpo with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId > 0 and ( ( opr.fsltablecode = 'MFR' and opr.ownerId in ( select mfr_id from tblManufacturers with (nolock) where coalesce( oneFsEnabled, 'N' ) = 'Y' )) or ( opr.fsltablecode = 'BRO' and opr.ownerId in ( select broker_id from tblBrokers with (nolock) where coalesce( oneFsEnabled, 'N' ) = 'Y' )) ) and nullif( opr.fsl_choId, 0 ) is null where exists ( select * from tbl_CRM_Affiliations aa with (nolock) inner join tbl_OPR_CLientOperators org with (nolock) on org.ownerId = aa.ownerId and org.fsltablecode = aa.ownertype and org.operatorId = aa.orgId and org.fsl_choId = gpo.ownerId where aa.orgType = 'OPR' and aa.ownerId = opr.ownerId and aa.ownerType = opr.fsltablecode and aa.partnerId = opr.operatorId and aa.partnerType = 'OPR' and aa.partnerAffiliateNbr = gpo.mfrCustNum) ) select * from toPropagate order by operatorId
#htmleditformat( cho_name )#(#cho_id#): Propagating #lsnumberformat( qmd_operatorsToPropagate.recordCount )#