declare @ownerId int = 103 ;
	declare @ownerType varchar(3) = 'BRO' ;
	declare @cdr_recordId int = 0 ;

	drop table if exists [tmp_buyingGroups_BRO103_1776448188406_04172026];

	select byg_raw.bgId, byg_raw.BgName, byg_raw.cdr_recordId, byg_raw.cdr_dstName
	into [tmp_buyingGroups_BRO103_1776448188406_04172026]
		from (
		select byg.uniqueId as bgId, byg.name as BgName, cdr.cdr_recordId, cdr.cdr_dstName, row_number() over ( partition by cdr_recordId order by cdr.cdr_recordId, afl.affiliationDate desc, byg.name ) as seq
		from tbl_BYG_BuyingGroups byg with (nolock)
		inner join tbl_CRM_Affiliations afl with (nolock)
		on afl.ownerId= 66
		and afl.ownerType = 'MSC'
		and afl.orgType = 'BYG'
		and afl.partnerType = 'DST'
		and afl.affiliationStatus = 'A'
		and afl.orgId = byg.uniqueId
		inner join tbl_CDR_Distributors cdr with (nolock)
			on cdr.fsl_dstId = afl.partnerId
		where cdr.ownerId = @ownerId
		and cdr.fsl_tablecode = @ownerType
		
			) as byg_raw 
		where seq = 1 ;

	alter table [tmp_buyingGroups_BRO103_1776448188406_04172026] add constraint [pk_tmp_buyingGroups_BRO103_1776448188406_04172026] primary key ( cdr_recordId );

declare @ownerId int = 103 ;
		declare @ownerType varchar(3) = 'BRO' ;
		declare @cdr_recordId int = 0 ;

	   declare @fiscalYear int = Year( getDate() );
	   declare @rolling12End datetime=  dateadd( month, -1, ( select max( salesPeriod ) from [tbl_DW_DistributorSales_BRO103] with (nolock) where salesPeriodType = 'MONTH'  ));
	   declare @rolling12Start datetime = dateadd( month, -11, @rolling12End );


		drop table if exists [tmp_distributorSales_BRO103_1776448188406_04172026];

		select ds.cdr_recordId, 
					

					sum( case when ds.fiscalYear = @fiscalYear then ds.cases else 0.0 end ) as cases, 
                	sum( case when ds.fiscalYear = @fiscalYear then ds.lbs else 0.0 end ) as lbs, 
                	sum( case when ds.fiscalYear = @fiscalYear then ds.dollars else 0.0 end ) as dollars,
                	sum( case when ds.salesPeriod between  @rolling12Start and @rolling12End then ds.cases else 0.0 end ) as rolling12cases, 
                	sum( case when ds.salesPeriod between  @rolling12Start and @rolling12End then ds.lbs else 0.0 end ) as rolling12lbs, 
                	sum( case when ds.salesPeriod between  @rolling12Start and @rolling12End then ds.dollars else 0.0 end ) as rolling12dollars

				into [tmp_distributorSales_BRO103_1776448188406_04172026]
              from tbl_DW_DistributorSales_BRO103 ds with (nolock)
 			where ds.salesPeriodType = 'MONTH'
			   and ( ds.fiscalYear = @fiscalYear or ds.salesPeriod between  @rolling12Start and @rolling12End )			  
               
            group by ds.cdr_recordId;

	  alter table [tmp_distributorSales_BRO103_1776448188406_04172026] alter column cdr_recordId int not null;

	  alter table [tmp_distributorSales_BRO103_1776448188406_04172026] add constraint [pk_tmp_distributorSales_BRO103_1776448188406_04172026] primary key ( cdr_recordId );

declare @ownerId int = 103 ;
  declare @ownerType varchar(3) = 'BRO' ;
  declare @cdr_recordId int = 0 ;
  declare @pri_lead int = 1681 ;


with	


	mappedDistributors as (


		-- this is here because there are some clients that have data anomalies related to the fsl_mapPriority and multiple mappings to the same 1fs id.

		select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId, cdr.cdr_dstName, cdr.fsl_dstId, row_number() over ( partition by cdr.fsl_dstId  order by cdr.fsl_mapPriority, cdr.cdr_recordId ) fsl_mapPriority
		from tbl_CDR_Distributors cdr with (nolock)
		where cdr.ownerId = @ownerId
		and cdr.fsl_tablecode = @ownerType
		and cdr.fsl_dstId > 0
		and cdr.fsl_mapPriority = 1

	),




  smaA as (

	select territoryId, brokerOfficeId, brokerId, primaryOffice, row_number() over( partition by territoryId order by brokerId, brokerOfficeId ) as ranking
	from tbl_SMA_BrokerOfficeLink sma with (nolock)
	where primaryOffice = 'Y'
	and mfr_id = @ownerId
	  and 'MFR' = @ownerType

	), 
	
	sma as (
		select * from smaA where ranking = 1
	),

  cdrRelatedTerritoriesRaw as (

		select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId, rt.territoryId as territoryId
		  from tbl_CDR_Distributors cdr with (nolock)
		  inner join tbl_TER_RelatedTerritories rt with(nolock)
		    on rt.relatedTerritoryId = cdr.cdr_territoryId
			and rt.ownerId = cdr.ownerId
			and rt.ownerType = cdr.fsl_tablecode
			and rt.relationshipCode = 'CDR'
	  
			where cdr.ownerId = @ownerId
			and cdr.fsl_tablecode = @ownerType

			
			   and cdr.crmactive = 'Y'
			   and cdr.cdr_dstcompanytype <> 'O' 
			   and cdr.cdr_dstcompanytype <> 'S' 			   
			

		union

		select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId, cdr.cdr_territoryId as territoryId
		  from tbl_CDR_Distributors cdr with (nolock)
			where cdr.ownerId = @ownerId
			and cdr.fsl_tablecode = @ownerType
			and cdr.cdr_territoryId > 0

			
			   and cdr.crmactive = 'Y'
			   and cdr.cdr_dstcompanytype <> 'O' 
			   and cdr.cdr_dstcompanytype <> 'S' 			   
			

  ),

  cdrRelatedTerritories as (

		select rt.ownerId, rt.fsl_tablecode, rt.cdr_recordId, string_agg( '#' + cast( rt.territoryId as varchar ) + ';', '' ) relatedTerritoryIds
		  from cdrRelatedTerritoriesRaw rt with (nolock)
		group by rt.ownerId, rt.fsl_tablecode, rt.cdr_recordId


  ),


  etl01 as (

  SELECT distinct cdr.cdr_recordid, 
                  cdr.fsl_tablecode ownerType, 
                  cdr.fsl_tablecode, 
                  cdr.ownerid, 
                  cdr.cdr_dstname, 
                  cdr.cdr_dstcode, 
                  cdr.cdr_dstaddress1,
                  cdr.cdr_dstaddress2,
                  cdr.cdr_dstcity, 
                  cdr.cdr_dststate,
                  cdr.cdr_dstzip, 
				  nullif( cdr.cdr_dstPhone, '' ) cdr_dstPhone,
                  cdr.cdr_dstcountryId,
                  cdr.cdr_territoryId,
                  cdr.focusAccount, 
                  cdr.cdr_priority, 
                  cdr.classificationId, 
                  cdr.mfr_bsr_id, 
				  cdr.fsl_dstId,
				  cdr.cdr_dstPath,
				  
				  cdr.recordSource,
				  /*
				  0  as cases,
                  0  as lbs,
                  0  as dollars,
                  */
                  '' as alt,
				  
				  
				  cast( floor( 10191817 * sqrt( cdr.cdr_recordid * log10( cdr.cdr_recordid ))) as bigint ) as cdr_RecordIdTk,
                  
				  /*
				  case 
				     when bsr.fspro_userid is not null then bsr.firstName
					 else case when rm.fspro_userid is not null then rm.firstName else pm.firstName end
				  end cmFirstName,
					 
				  case 
				      when bsr.fspro_userid is not null then bsr.lastName
					  else case when rm.fspro_userid is not null then rm.lastName else pm.lastName end
				  end cmLastName,
				  
				  case 
				      when bsr.fspro_userid is not null then bsr.firstname + ' ' + bsr.lastname 
					  else case when rm.fspro_userid is not null then rm.firstname + ' ' + rm.lastname else pm.firstname + ' ' + pm.lastname end
				  end cmFullName,

				  
				  case 
				    when bsr.fspro_userid is not null then bsr.email
					 else case when rm.fspro_userid is not null then rm.email else pm.email end
				   end cmEmail,
				  coalesce( bsr.fspro_userid, coalesce( rm.fspro_userid, pm.fspro_userid )) cmUserId,                    
				  
				  case 
				      when bsr.fspro_userid is not null then bsr.title
					  else case when rm.fspro_userid is not null then rm.title else pm.title end
				  end cmTitle,
				  */
				  
  				bsr.firstName as cmFirstName,
				bsr.lastName as cmLastName,
  			  	bsr.firstname + ' ' + bsr.lastname as cmFullName,
				bsr.email as cmEmail,
				bsr.fspro_userid as cmUserId,                    
				bsr.title as cmTitle,

				  cdr.primaryContactId, 
				  primaryContact.firstName as primaryContactFirstName,
				  primaryContact.lastName as primaryContactLastName,
				  
				  
                  t.territoryId,
				  rt.relatedTerritoryIds,
                  t.name as territoryName, 
				  pm.firstname as terManagerFirstName,
				  pm.lastname as terManagerLastName,
				  pm.email as terManagerEmail,
				  pm.firstname + ' ' + pm.lastname as terManagerFullName,                 
				  sm.firstname as terManagerFirstName2,
				  sm.lastname as terManagerLastName2,
				  sm.email as terManagerEmail2,
				  sm.firstname + ' ' + sm.lastname as terManagerFullName2,
                  
                  t.territoryPath, 
				  t.regionName,
				  t.divisionName,
                  
                  cls.name classificationName, 
                  
                  cdrp.cdr_recordid as parentCdrRecordId,
                  cdrp.cdr_dstname as parentCdrDstName,
                  cdr.cdr_dstcompanytype dstcompanytype,
                  
                 /* cdr.lastInteractionDate, */
				 ca_last.interactionDate as lastInteractionDate, ca_last.interactionPurposeName, ca_last.interactionRepFirstName, ca_last.interactionRepLastName,
			   ca_next.interactionDate as nextInteractionDate,
                  datediff(day, cdr.lastInteractionDate, getdate()) LastIntDays,

				  case when cdr.cdr_priority = '*' then 1
			 		   when cdr.cdr_priority = 'A' then 2
		               when cdr.cdr_priority = 'B' then 3
		               when cdr.cdr_priority = 'C' then 4
		               when cdr.cdr_priority = 'D' then 5
		               else 6
		          end sortOrder, 
				  
				  byg.bgId,
				  byg.bgName,
				  
				  sma.brokerId broker_Id,
				  sma.brokerOfficeId broker_Office_Id,
				  b.broker_name,
				  bo.broker_office_name,
				  
				  'case' as unitOfMeasure,
				  'cases' as unitOfMeasurePlural,
				  coalesce(dst.gs1_gln, dstpr.gs1_gln) gs1_gln,
				  cdr.fmid,
				  cdr.fmSyncTime,
				  cdr.fmDatabase,
				  cdr.createDate,
				  --cdr.comments
				  nullif(cdr.comments,'') comments,

				case when exists ( 
				
					select * from tbl_TPM_Requests tpmr with (nolock)
					inner join tbl_TPM_Contracts tpmc with (nolock)
						on tpmc.contractId = tpmr.contractId 
						and tpmc.ownerId = tpmr.ownerId
						and tpmc.ownerType = tpmr.ownerType
					where tpmr.ownerId = cdr.ownerId 
					and tpmr.ownerType = cdr.fsl_tablecode 
					and tpmc.partnerType = 'CDR' 
					and tpmc.partnerId = cdr.cdr_recordId 
					and tpmr.startDate <= cast( getDate() as date )
					and tpmr.endDate >= cast( getDate() as date )

				) then 1 else 0 end as hasActiveAgreements,

				case when exists ( 
				
					select * from tbl_TPM_Requests tpmr with (nolock)
					inner join tbl_TPM_Contracts tpmc with (nolock)
						on tpmc.contractId = tpmr.contractId 
						and tpmc.ownerId = tpmr.ownerId
						and tpmc.ownerType = tpmr.ownerType
					where tpmr.ownerId = cdr.ownerId 
					and tpmr.ownerType = cdr.fsl_tablecode 
					and tpmc.partnerType = 'CDR' 
					and tpmc.partnerId = cdr.cdr_recordId 
					and tpmr.endDate < cast( getDate() as date )
					
				) then 1 else 0 end as hasExpiredAgreements					  
				  
		        

			  FROM tbl_CDR_Distributors cdr with (nolock)
			  
			
			
			left outer join [tmp_buyingGroups_BRO103_1776448188406_04172026] byg on byg.cdr_recordId = cdr.cdr_recordID
			  
			inner join tbl_TER_Territories t with (nolock)
			  on t.territoryid = cdr.cdr_territoryid
			 and t.ownerid = cdr.ownerid
			 and t.fsl_tablecode = cdr.fsl_tablecode

			 
			 inner join tbl_TER_TerritoryLevels l with (nolock)
			   on l.ownerId = t.ownerId
			   and l.fsl_tablecode = t.fsl_tablecode
			   and l.levelId = t.levelId
			
			left outer join tbl_FSPro_Members pm with (nolock)
			  on pm.fspro_userId = t.primaryManager
			 and pm.ownerId = t.ownerId
			 and pm.fsl_tablecode = t.fsl_tablecode
			 
			 left outer join tbl_FSPro_Members sm with (nolock)
			  on sm.fspro_userId = t.secondaryManager
			 and sm.ownerId = t.ownerId
			 and sm.fsl_tablecode = t.fsl_tablecode

			 left outer join [tmp_callActivity_BRO103_1776448188406_04172026] ca_last with (nolock)
		     on ca_last.ownerId = cdr.ownerId
			 and ca_last.ownerType = cdr.fsl_tablecode
			 and ca_last.cdr_recordId = cdr.cdr_recordId
			 and ca_last.callSequence = 1

		  left outer join [tmp_callActivity_BRO103_1776448188406_04172026] ca_next with (nolock)
		     on ca_next.ownerId = cdr.ownerId
			 and ca_next.ownerType = cdr.fsl_tablecode
			 and ca_next.cdr_recordId = cdr.cdr_recordId
			 and ca_next.callSequence = -1
			
			left outer join tbl_FSpro_Members bsr with (nolock)
			  on bsr.fspro_userid = cdr.mfr_bsr_id
			
			
			left outer join sma with (nolock)
			  on sma.territoryId = t.territoryId
			  and sma.primaryOffice = 'Y'

	          
		    left outer join tblBrokers b with (nolock) on b.broker_id = sma.brokerid
			left outer join tblBrokerOffices bo with (nolock) on bo.broker_id = sma.brokerid and bo.broker_office_Id = sma.brokerOfficeId

			left outer join tbl_SEC_MemGrpLink rml with (nolock)
				on rml.partnerId = t.ownerId
			   and rml.partnerTyp = t.fsl_tablecode
			   and rml.brokerOfficeId = sma.brokerOfficeID
			   and rml.groupId = @pri_lead

			left outer join tbl_FSpro_Members rm with (nolock)
			   on rm.fspro_userid = rml.fspro_userId

			left outer join tbl_FSpro_Members primaryContact with (nolock)
			   on cdr.primaryContactId = primaryContact.fspro_userId

			left outer join tbl_CDR_Classifications cls with (nolock)
			   on cdr.classificationID = cls.classificationID
			   and cdr.ownerId = cls.ownerId
			   and cdr.fsl_tablecode = cls.ownerType

			left outer join tbl_DST_Distributors dst with (nolock)
				on dst.dstid = cdr.fsl_dstId

			left outer join tbl_DST_Distributors dstpr with (nolock)
				on dst.dstparentco = dstpr.dstid
				
			left outer join mappedDistributors cdrp  with (nolock) 
			    on cdrp.fsl_dstId = dstpr.dstid
			    and cdrp.fsl_tablecode = @ownerType
  		        and cdrp.ownerid = @ownerId
				and cdrp.fsl_mapPriority = 1

			left outer join cdrRelatedTerritories rt with (nolock)
			   on rt.ownerId = cdr.ownerId
			   and rt.fsl_tablecode = cdr.fsl_tablecode
			   and rt.cdr_recordId = cdr.cdr_recordId
			   
			   



		    where cdr.fsl_tablecode = @ownerType
  		      and cdr.ownerid = @ownerId
			   
			
			   and cdr.crmactive = 'Y'
			   and cdr.cdr_dstcompanytype <> 'O' 
			   and cdr.cdr_dstcompanytype <> 'S' 			   
			
  ),
  
  etl_raw as (

	select *, 
	
		case when hasActiveAgreements = 1 then 'Active'
			when hasExpiredAgreements = 1 then 'Expired'
			else 'None'
		end as agreementStatus

	from etl01 with (nolock)

  ),
  
	 
   etl as 
   ( select e.*, count(*) over( partition by territoryId ) as terDistribCount, 
   		
			salesTotals.cases, salesTotals.lbs, salesTotals.dollars,
			salesTotals.rolling12Cases, salesTotals.rolling12Lbs, salesTotals.rolling12Dollars
		
     from   etl_raw e with (nolock)

	  
      left outer join [tmp_distributorSales_BRO103_1776448188406_04172026] salesTotals with (nolock) on salesTotals.cdr_recordId = e.cdr_recordId
	  
      )
  
  

insert into [tmp_crmDistributorsLoad_BRO103_1776448188406_04172026] ( cdr_recordId,cdr_recordIdTk,ownerType,fsl_TableCode,ownerid,cdr_dstname,cdr_dstcode,cdr_dstcity,cdr_dststate,focusAccount,cdr_priority,classificationId,mfr_bsr_id,cdr_dstPath,sortOrder,cmFirstName,cmLastName,cmFullName,cmEmail,cmUserId,territoryid,territoryName,terManagerFirstName,terManagerLastName,terManagerFullName,terManagerEmail,terManagerFirstName2,terManagerLastName2,terManagerFullName2,terManagerEmail2,territoryPath,regionName,divisionName,terDistribCount,classificationname,dstcompanytype,lastInteractionDate,LastIntDays,bgId,bgName,broker_id,broker_office_id,broker_name,broker_office_name,unitOfMeasure,unitOfMeasurePlural,cdr_dstaddress1,cdr_dstaddress2,cdr_dstzip,fsl_dstId,recordSource,cmTitle,cases,lbs,dollars,alt,fmId,fmSyncTime,fmDatabase,cdr_dstcountryId,gs1_gln,primaryContactId,primaryContactFirstName,primaryContactLastName,parentCdrDstName,parentCdrRecordId,rolling12Cases,rolling12Lbs,rolling12Dollars,relatedTerritoryIds,createDate,comments,cdr_dstPhone,nextInteractionDate,interactionRepLastName,interactionRepFirstName,interactionPurposeName,hasActiveAgreements,hasExpiredAgreements,agreementStatus )
select cdr_recordId,cdr_recordIdTk,ownerType,fsl_TableCode,ownerid,cdr_dstname,cdr_dstcode,cdr_dstcity,cdr_dststate,focusAccount,cdr_priority,classificationId,mfr_bsr_id,cdr_dstPath,sortOrder,cmFirstName,cmLastName,cmFullName,cmEmail,cmUserId,territoryid,territoryName,terManagerFirstName,terManagerLastName,terManagerFullName,terManagerEmail,terManagerFirstName2,terManagerLastName2,terManagerFullName2,terManagerEmail2,territoryPath,regionName,divisionName,terDistribCount,classificationname,dstcompanytype,lastInteractionDate,LastIntDays,bgId,bgName,broker_id,broker_office_id,broker_name,broker_office_name,unitOfMeasure,unitOfMeasurePlural,cdr_dstaddress1,cdr_dstaddress2,cdr_dstzip,fsl_dstId,recordSource,cmTitle,cases,lbs,dollars,alt,fmId,fmSyncTime,fmDatabase,cdr_dstcountryId,gs1_gln,primaryContactId,primaryContactFirstName,primaryContactLastName,parentCdrDstName,parentCdrRecordId,rolling12Cases,rolling12Lbs,rolling12Dollars,relatedTerritoryIds,createDate,comments,cdr_dstPhone,nextInteractionDate,interactionRepLastName,interactionRepFirstName,interactionPurposeName,hasActiveAgreements,hasExpiredAgreements,agreementStatus
 from etl;

truncate table [tbl_DW_CRMDistributors_BRO103]

insert into [tbl_DW_CRMDistributors_BRO103] ( cdr_recordId,cdr_recordIdTk,ownerType,fsl_TableCode,ownerid,cdr_dstname,cdr_dstcode,cdr_dstcity,cdr_dststate,focusAccount,cdr_priority,classificationId,mfr_bsr_id,cdr_dstPath,sortOrder,cmFirstName,cmLastName,cmFullName,cmEmail,cmUserId,territoryid,territoryName,terManagerFirstName,terManagerLastName,terManagerFullName,terManagerEmail,terManagerFirstName2,terManagerLastName2,terManagerFullName2,terManagerEmail2,territoryPath,regionName,divisionName,terDistribCount,classificationname,dstcompanytype,lastInteractionDate,LastIntDays,bgId,bgName,broker_id,broker_office_id,broker_name,broker_office_name,unitOfMeasure,unitOfMeasurePlural,cdr_dstaddress1,cdr_dstaddress2,cdr_dstzip,fsl_dstId,recordSource,cmTitle,cases,lbs,dollars,alt,fmId,fmSyncTime,fmDatabase,cdr_dstcountryId,gs1_gln,primaryContactId,primaryContactFirstName,primaryContactLastName,parentCdrDstName,parentCdrRecordId,rolling12Cases,rolling12Lbs,rolling12Dollars,relatedTerritoryIds,createDate,comments,cdr_dstPhone,nextInteractionDate,interactionRepLastName,interactionRepFirstName,interactionPurposeName,hasActiveAgreements,hasExpiredAgreements,agreementStatus )
		select cdr_recordId,cdr_recordIdTk,ownerType,fsl_TableCode,ownerid,cdr_dstname,cdr_dstcode,cdr_dstcity,cdr_dststate,focusAccount,cdr_priority,classificationId,mfr_bsr_id,cdr_dstPath,sortOrder,cmFirstName,cmLastName,cmFullName,cmEmail,cmUserId,territoryid,territoryName,terManagerFirstName,terManagerLastName,terManagerFullName,terManagerEmail,terManagerFirstName2,terManagerLastName2,terManagerFullName2,terManagerEmail2,territoryPath,regionName,divisionName,terDistribCount,classificationname,dstcompanytype,lastInteractionDate,LastIntDays,bgId,bgName,broker_id,broker_office_id,broker_name,broker_office_name,unitOfMeasure,unitOfMeasurePlural,cdr_dstaddress1,cdr_dstaddress2,cdr_dstzip,fsl_dstId,recordSource,cmTitle,cases,lbs,dollars,alt,fmId,fmSyncTime,fmDatabase,cdr_dstcountryId,gs1_gln,primaryContactId,primaryContactFirstName,primaryContactLastName,parentCdrDstName,parentCdrRecordId,rolling12Cases,rolling12Lbs,rolling12Dollars,relatedTerritoryIds,createDate,comments,cdr_dstPhone,nextInteractionDate,interactionRepLastName,interactionRepFirstName,interactionPurposeName,hasActiveAgreements,hasExpiredAgreements,agreementStatus
		from [tmp_crmDistributorsLoad_BRO103_1776448188406_04172026] with (nolock);

drop table if exists [tmp_crmDistributorsLoad_BRO103_1776448188406_04172026];