drop table if exists [#_choTable#]; select cho.cho_id, cho.tastewiseId, cho.cho_type into [#_choTable#] from tbl_CHO_Operators cho with (nolock) where nullif( cho.tastewiseId, '' ) is not null or ( cho.cho_type in ( 'G', 'C' )) ; alter table [#_choTable#] add constraint [pk_#_choTable#] primary key ( cho_id ); declare @ownerId int = ; declare @ownerType varchar(3) = ; drop table if exists [#_affiliationsTable#]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, nullif( partnerAffiliateNbr, '' ) partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, cast( null as varchar(50) ) partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as ranking from rawAffiliations where orgId > 0 and partnerId > 0 and exists ( select * from [#_refreshTable#] r with (nolock) where r.operatorId = partnerId ) ) select * into [#_affiliationsTable#] from affiliations where ranking = 1; alter table [#_affiliationsTable#] add constraint [pk_#_affiliationsTable#] primary key ( ownerType, ownerId, partnerType, partnerId, orgType, orgId ); drop table if exists [#_memberGroupsTable#]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, opr.fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [#_memberGroupsTable#] from tbl_OPR_ClientOperators opr with (nolock) inner join [#_choTable#] cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [#_memberGroupsTable#] add constraint [pk_#_memberGroupsTable#] primary key ( fsltablecode, ownerId, operatorId ); drop table if exists [#_operatorsGPOsTable#]; declare @ownerId int = ; declare @ownerType varchar(3) = ; select opr.operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by opr.operatorId order by a.affiliationDate ) as ranking into [#_operatorsGPOsTable#] from [#_affiliationsTable#] a with (nolock ) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = a.partnerId and opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType and exists ( select * from [#_refreshTable#] r with (nolock) where r.operatorId = opr.operatorId ) inner join [#_memberGroupsTable#] org with (nolock) on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where a.ownerId = @ownerId and a.ownerType= @ownerType and a.partnerType = 'OPR' and a.affiliationStatus = 'A' and org.oprCompanyType = 'G'; alter table [#_operatorsGPOsTable#] alter column fsl_choId int not null; alter table [#_operatorsGPOsTable#] alter column ranking int not null; alter table [#_operatorsGPOsTable#] add constraint [pk_#_operatorsGPOsTable#] primary key ( operatorId, fsl_choId, ranking );

#encodeForHTML( _r.sql )#

drop table if exists [#_operatorsCMCsTable#]; declare @ownerId int = ; declare @ownerType varchar(3) = ; select opr.operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by opr.operatorId order by a.affiliationDate ) as ranking into [#_operatorsCMCsTable#] from [#_affiliationsTable#] a with (nolock) inner join tbl_OPR_ClientOperators opr (nolock) on opr.operatorId = a.partnerId and opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType and exists ( select * from [#_refreshTable#] r with (nolock) where r.operatorId = opr.operatorId ) inner join [#_memberGroupsTable#] org with (nolock) on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where a.ownerId = @ownerId and a.ownerType= @ownerType and a.partnerType = 'OPR' and a.affiliationStatus = 'A' and org.oprCompanyType = 'C'; alter table [#_operatorsCMCsTable#] alter column fsl_choId int not null; alter table [#_operatorsCMCsTable#] alter column ranking int not null; alter table [#_operatorsCMCsTable#] add constraint [pk_#_operatorsCMCsTable#] primary key ( operatorId, fsl_choId, ranking );

#encodeForHTML( _r.sql )#

drop table if exists #_operatorMembershipsTable#;

#_operatorMembershipsTable#

declare @ownerId int = ; declare @ownerType varchar(3) = ; drop table if exists #_operatorMembershipsTable#; with memberGroups as ( select * from [#_memberGroupsTable#] with (nolock) ) , affiliations as ( select * from [#_affiliationsTable#] with (nolock) ) , gpo as ( select * from [#_operatorsGPOsTable#] with (nolock) ) , allGPO as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo5OPR.operatorId as gpoId_5, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, gpo5OPR.companyName as gpoName_5 ,nbr1.partnerAffiliateNbr as gpoNbr_1, nbr2.partnerAffiliateNbr as gpoNbr_2, nbr3.partnerAffiliateNbr as gpoNbr_3, nbr4.partnerAffiliateNbr as gpoNbr_4, nbr5.partnerAffiliateNbr as gpoNbr_5 from ( select operatorId, fsl_choId, 'gpo' + cast( ranking as varchar) as gpoN from gpo where ranking <= 5 ) as gpoData pivot ( min( fsl_choId ) for gpoN in ( gpo1, gpo2, gpo3, gpo4, gpo5 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join gpo nbr1 with (nolock) on nbr1.fsl_choId = p.gpo1 and nbr1.ranking = 1 and nbr1.operatorId = p.operatorId left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join gpo nbr2 with (nolock) on nbr2.fsl_choId = p.gpo2 and nbr2.ranking = 2 and nbr2.operatorId = p.operatorId left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join gpo nbr3 with (nolock) on nbr3.fsl_choId = p.gpo3 and nbr3.ranking = 3 and nbr3.operatorId = p.operatorId left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join gpo nbr4 with (nolock) on nbr4.fsl_choId = p.gpo4 and nbr4.ranking = 4 and nbr4.operatorId = p.operatorId left outer join memberGroups gpo5OPR with (nolock) on gpo5OPR.fsl_choId = p.gpo5 and gpo5OPR.mappingPriority = 1 left outer join gpo nbr5 with (nolock) on nbr5.fsl_choId = p.gpo5 and nbr5.ranking = 5 and nbr5.operatorId = p.operatorId ), cmc as ( select * from [#_operatorsCMCsTable#] with (nolock) ), allCMC as ( select p.operatorId, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3 ,nbr1.partnerAffiliateNbr as cmcNbr_1, nbr2.partnerAffiliateNbr as cmcNbr_2, nbr3.partnerAffiliateNbr as cmcNbr_3 from ( select operatorId, fsl_choId, 'cmc' + cast( ranking as varchar) as cmcN from cmc where ranking <= 3 ) as cmcData pivot ( min( fsl_choId ) for cmcN in ( cmc1, cmc2, cmc3 ) ) as p left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join cmc nbr1 with (nolock) on nbr1.fsl_choId = p.cmc1 and nbr1.ranking = 1 and nbr1.operatorId = p.operatorId left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join cmc nbr2 with (nolock) on nbr1.fsl_choId = p.cmc2 and nbr1.ranking = 2 and nbr1.operatorId = p.operatorId left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join cmc nbr3 with (nolock) on nbr1.fsl_choId = p.cmc3 and nbr1.ranking = 3 and nbr1.operatorId = p.operatorId ) select coalesce( c.operatorId, g.operatorId ) operatorId, c.cmcId_1, c.cmcName_1, c.cmcId_2, c.cmcName_2, c.cmcId_3, c.cmcName_3, c.cmcNbr_1, c.cmcNbr_2, c.cmcNbr_3, g.gpoId_1, g.gpoName_1, g.gpoId_2, g.gpoName_2, g.gpoId_3, g.gpoName_3, g.gpoId_4, g.gpoName_4, g.gpoId_5, g.gpoName_5 , g.gpoNbr_1, g.gpoNbr_2, g.gpoNbr_3, g.gpoNbr_4, g.gpoNbr_5 into #_operatorMembershipsTable# from allCMC c full outer join allGPO g on g.operatorId = c.operatorId order by operatorId alter table #_operatorMembershipsTable# alter column operatorId int not null; alter table #_operatorMembershipsTable# add constraint pk_#_operatorMembershipsTable# primary key ( operatorId );

#encodeForHTML( _r.sql )#