drop table if exists [#_1fsOperatorsLoadTable#] select * into [#_1fsOperatorsLoadTable#] from [tbl_DW_1FSOperators#_tableSuffix#] with (nolock) where 1 = 2 declare @ownerId int = ; declare @ownerType varchar(3) = ; with choIds as ( select distinct fsl_choId as cho_id from tbl_OPR_ClientOperators with (nolock) where fsl_choId > 0 and ownerId = @ownerId and fsltablecode = @ownerType union select distinct sourceId as cho_id from tbl_TPM_ExternalClaimHistory with (nolock) where sourceType = 'CHO' and ownerId = @ownerId and ownerType = @ownerType ) insert into [#_1fsOperatorsLoadTable#] ( ownerType, ownerId, choId, operatorName, operatorAddress, operatorCity, operatorState, operatorZipCode, operatorSegment, operatorSegmentId, operatorCounty, lastActivityDate, operatorType, parentChoId, parentName, channelSegment, mainSegment, subSegment, primaryGPO, averageClientVolume ) select distinct @ownerType as ownerType, @ownerId as ownerId, cho.cho_Id choId, cho.cho_name operatorName, cho.cho_address operatorAddress, cho.cho_city operatorCity, cho.cho_state operatorState, cho.cho_zipcode operatorZipCode, seg.operatorSegment as operatorSegment, seg.segmentId operatorSegmentId, ( select top 1 usps.countyName from tbl_USPS_CityStateDetails usps with (nolock) where usps.zipCode = cho.cho_zipcode ) as operatorCounty, cho.freshnessDate as lastActivityDate, case when cho.cho_type = 'I' then 'INDEPENDENT' when cho.cho_type = 'U' then 'UNIT' when cho.cho_type = 'O' then 'PARENT' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'H' then 'HOLDING' else 'UNKNOWN ' + cho.cho_type end operatorType, pcho.cho_Id parentChoId, pcho.cho_name parentName, seg.channelSegment, seg.mainSegment, seg.subSegment, case when seg.segmentId = 54 then 'true' else 'false' end primaryGPO, 0 from tbl_CHO_Operators cho with (nolock) left outer join tbl_OPR_Segments seg with (nolock) on seg.segmentId = cho.cho_segmentId left join tbl_CHO_Operators pcho with (nolock) on pcho.cho_id = cho.cho_parentCo and pcho.cho_type = 'O' where cho.cho_id in ( select cho_id from choIds ) order by cho.cho_Id truncate table [#_broker1fsOperatorsTable#]; insert into [#_broker1fsOperatorsTable#] select * from [#_1fsOperatorsLoadTable#] with (nolock); drop table if exists [#_1fsOperatorsLoadTable#]; select top 1 format( dataDate, 'MM/dd/yyyy' ) dataDate from tbl_DW_1FSDistributors#_tableSuffix# with (nolock) where ownerType = and ownerId = select name from sys.tables where name = update d set d.operatorPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = d.ownerId and oa.ownerType = d.ownerType and oa.dstId = d.dstId ) then 'Y' else 'N' end from tbl_DW_1fsDistributors#_tableSuffix# d where d.ownerId = and d.ownerType = drop table if exists [#_1fsDistributorsLoadTable#]; select * into [#_1fsDistributorsLoadTable#] from [#_1fsDistributorsTable#] with (nolock) where 1 = 2 insert into [#_1fsDistributorsLoadTable#] ( ownerType, ownerId, cdr_recordId, dstId, dstName, parentDstId, parentDstName, dstType, dstAddr1, dstAddr2, dstCity, dstState, dstZip, dataDate ) select cdr.fsl_tablecode ownerType, cdr.ownerId, cdr.cdr_recordId, d.dstId, d.dstName, case when p.dstId is null then d.dstId else p.dstId end parentDstId, case when p.dstId is null then d.dstName else p.dstName end parentDstName, case when p.dstId is null then 'INDEPENDENT' else 'BRANCH' end as dstType, d.dstAddr1, d.dstAddr2, d.dstCity, d.dstState, d.dstZip, { fn now() } from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors d with (nolock) on d.dstId = cdr.fsl_dstId and d.dstCompanyType = 'B' left outer join tbl_DST_Distributors p with (nolock) on p.dstId = d.dstParentCo and p.dstCompanyType = 'P' where cdr.ownerId = and cdr.fsl_tablecode = update dw set dw.territoryId = ( select top 1 ter.territoryId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = cdr.cdr_territoryId and ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode where cdr.fsl_dstId = dstId and cdr.ownerId = dw.ownerId and cdr.fsl_tablecode = dw.ownerType and cdr.cdr_dstCompanyType = 'B' ) from [#_1fsDistributorsLoadTable#] dw update dw set dw.territoryName = ter.name, dw.territoryPath = ter.territoryPath from [#_1fsDistributorsLoadTable#] dw left outer join tbl_TER_Territories ter with (nolock) on ter.territoryId = dw.territoryId and ter.ownerId = dw.ownerId and ter.fsl_tablecode = dw.ownerType update d set d.operatorPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = d.ownerId and oa.ownerType = d.ownerType and oa.dstId = d.dstId ) then 'Y' else 'N' end from [#_1fsDistributorsLoadTable#] d where d.ownerId = and d.ownerType = truncate table [#_1fsDistributorsTable#]; insert into [#_1fsDistributorsTable#] select * from [#_1fsDistributorsLoadTable#] with (nolock); select c.name as columnName from sys.tables t inner join sys.columns c on c.object_id = t.object_id where t.name = 'tbl_DW_1fsUniverse_MFR0' and c.name not in ( ) order by c.column_id drop table if exists [#_segmentFactorsTable#]; select operatorSegment, count( distinct ownerId ) as clientCount, avg( forecast_qty / forecast_amt ) as factor, avg( forecast_qty ) quantity, avg( forecast_amt ) dollars into [#_segmentFactorsTable#] from tbl_DW_OperatorAnalyzerClientSummary with (nolock) where operatorSegment is not null and 1 = 2 group by operatorSegment; alter table [#_segmentFactorsTable#] alter column operatorSegment varchar(200) not null; alter table [#_segmentFactorsTable#] add constraint PK_#_segmentFactorsTable# primary key ( operatorSegment ); drop table if exists [#_1fsUniverseLoadTable#] select * into [#_1fsUniverseLoadTable#] from [#_broker1fsUniverseTable#] with (nolock) where 1 = 2 declare @brokerId int = #_brokerId#; with enabledMFRs as ( select distinct cfg.ownerId as mfr_Id from fsenablers..tbl_APP_Configurations cfg with (nolock) where cfg.applicationCode = '1FSBSR' and cfg.parameterName = 'apps.operatorSalesData.enabled' and cfg.parameterValue = 'true' and cfg.ownerType = 'MFR' and cfg.ownerId > 0 ) select distinct bpl.mfrId as mfr_id from tbl_BRO_Principals bpl with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.mfr_id = bpl.mfrId and sma.brokerid = bpl.ownerId inner join tbl_TER_Territories mt with (nolock) on mt.ownerId = sma.mfr_id and mt.fsl_tablecode = 'MFR' and mt.territoryId = sma.territoryId and nullif( mt.internalSalesId, '' ) = pt.mfrTerritoryNbr where bpl.ownerId = @brokerId and bpl.ownerType = 'BRO' and pt.hireStatus = 'A' and sma.mfr_id in ( select mfr_id from enabledMFRs ) select mfr_name from tblManufacturers with (nolock) where mfr_id = select distinct ter.internalSalesId, ter.name as territoryName from tbl_SMA_BrokerOfficeLink sma with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = sma.territoryId and ter.ownerId = sma.mfr_id and ter.fsl_tablecode = 'MFR' inner join tbl_BRO_Principals bpl with (nolock) on bpl.ownerId = sma.brokerId and bpl.ownerType = 'BRO' and bpl.mfrId = sma.mfr_Id inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId and pt.hireStatus = 'A' and pt.mfrTerritoryNbr = nullif( ter.internalSalesId, '' ) where sma.brokerid = and sma.mfr_id = order by internalSalesId declare @brokerId int = #_brokerId#; declare @mfrId int = #_mfrId#; with toAdd as ( select 'BRO' ownerType, @brokerId ownerId , mo.#_c# from #_mfr1FSUniverseTable# mo with (nolock) where operatorTerritoryId in ( select distinct ter.territoryId from tbl_SMA_BrokerOfficeLink sma with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = sma.territoryId and ter.ownerId = sma.mfr_id and ter.fsl_tablecode = 'MFR' inner join tbl_BRO_Principals bpl with (nolock) on bpl.ownerId = sma.brokerId and bpl.ownerType = 'BRO' and bpl.mfrId = sma.mfr_Id inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId and pt.hireStatus = 'A' and pt.mfrTerritoryNbr = nullif( ter.internalSalesId, '' ) where sma.brokerid = @brokerId and sma.mfr_id = @mfrId /* too loosey goosey select distinct territoryId from tbl_SMA_BrokerOfficeLink sma with (nolock) where sma.brokerid = @brokerId and sma.mfr_id = @mfrId */ ) and not exists ( select * from [#_1fsUniverseLoadTable#] bo with (nolock) where bo.operatorChoId = mo.operatorChoId ) and mo.operatorName not like '%blanket%' ) insert into [#_1fsUniverseLoadTable#] ( ownerType, ownerId , #_c# ) select * from toAdd declare @brokerId int = #_brokerId#; declare @mfrId int = #_mfrId#; with toAdd as ( select 'BRO' ownerType, @brokerId ownerId , mo.#_c# from #_mfr1FSUniverseTable# mo with (nolock) where operatorChoId in ( select distinct parentChoId from #_broker1fsUniverseTable# with (nolock) ) and not exists ( select * from [#_1fsUniverseLoadTable#] bo with (nolock) where bo.operatorChoId = mo.operatorChoId ) ) insert into [#_1fsUniverseLoadTable#] ( ownerType, ownerId , #_c# ) select * from toAdd

#htmleditformat( _mfrName )# - #lsnumberformat( _r.recordCount )# universe records

declare @brokerId int = #_brokerId#; with preSegments as ( select segment, charindex( '/', segment ) s1, charindex( '/', segment, charindex( '/', segment ) + 1 ) s2, segmentId from tbl_OPR_Segments with (nolock) ), segments as ( select substring( segment, 1, s1 - 1 ) channelSegment, subString( segment, s1 + 1, s2 - s1 - 1 ) mainSegment, substring( segment, s2 + 1, len( segment ) - s2 ) subSegment, substring( segment, s1 + 1, len( segment ) - s1 ) operatorSegment, segment as fullSegment, segmentId from preSegments ), missing1FSOperators as ( select distinct fsl_choId from tbl_OPR_ClientOperators opr with (nolock) where ownerId = @brokerId and fsltablecode = 'BRO' and fsl_choId > 0 and not exists ( select * from [#_1fsUniverseLoadTable#] u with (nolock) where u.operatorChoId = opr.fsl_choId ) ), operatorsToAdd as ( select 'BRO' as ownerType, @brokerId as ownerId, c.cho_id as operatorChoId, case when c.cho_type = 'I' then 'INDEPENDENT' when c.cho_type = 'U' then 'UNIT' else 'CHAINHQ' end as operatorType, c.cho_name as operatorName, c.cho_address as operatorAddress, c.cho_city as operatorCity, c.cho_state as operatorState, c.cho_zipcode as operatorZipCode, cp.cho_id as parentChoId, cp.cho_name as parentName, cp.cho_address as parentAddress, cp.cho_city as parentCity, cp.cho_state as parentState, cp.cho_zipcode as parentZipCode, s.segmentId, s.channelSegment, s.mainSegment, s.subSegment, s.operatorSegment /* operatorCounty, */ /* clientCount, averageClientVolume, rank1fs, totalClientVolume, firstMonth, lastMonth, */ /* primaryDstId, primaryDstName, primaryParentDstId, primaryParentDstName, secondaryDstId, secondaryDstName, secondaryParentDstId, secondaryParentDstName, */ from tbl_CHO_Operators c with (nolock) left outer join tbl_CHO_Operators cp with (nolock) on cp.cho_id = c.cho_parentCo left outer join segments s on s.segmentId = c.cho_segmentId where c.cho_id in ( select fsl_choId from missing1fsOperators ) and c.cho_type in ( 'I', 'O', 'U' ) and c.cho_name not like '%blanket%' ) insert into [#_1fsUniverseLoadTable#] ( #_insertColumns# ) select #_insertColumns# from operatorsToAdd update u set u.estMonthlyVolume = u.averageClientVolume * f.factor, u.estMonthlyVolumeStatus = 'e' from [#_1fsUniverseLoadTable#] u with (nolock) left outer join [#_segmentFactorsTable#] f on f.operatorSEgment = u.operatorSEgment ---> drop table if exists [#_segmentFactorsTable#]; declare @ownerId int = #_brokerId#; declare @ownerType varchar(3) = 'BRO'; with territories as ( select ter.ownerId, ter.fsl_tablecode, m.fspro_userId as primaryManager, m.ownerId primaryManagerOwnerId, m.fsl_tablecode primaryManagerOwnerType, ter.territoryId, ter.parentTerritoryId from tbl_TER_Territories ter with (nolock) left outer join tbl_fspro_members m with (nolock) on m.ownerId > 0 and m.fspro_userId = ter.primaryManager where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType ), territoryManagers as ( select ter.ownerId, ter.fsl_tablecode, ter.territoryId, ter.primaryManager, ter.primaryManagerOwnerId, ter.primaryManagerOwnerType, ter.parentTerritoryId ,1 as recurseLevel, case when nullif( ter.primaryManager, 0 ) is null then 'inherit' else 'explicit' end as origin from territorIes ter with (nolock) where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType and nullif( ter.parentTerritoryId, 0 ) is null union all select ter.ownerId, ter.fsl_tablecode, ter.territoryId, case when ter.primaryManager is not null then ter.primaryManager else cte.primaryManager end as primaryManager, case when ter.primaryManagerOwnerId is not null then ter.primaryManagerOwnerId else cte.primaryManagerOwnerId end as primaryManagerOwnerId, case when ter.primaryManagerOwnerType is not null then ter.primaryManagerOwnerType else cte.primaryManagerOwnerType end as primaryManagerOwnerType, ter.parentTerritoryId , cte.recurseLevel + 1 recurseLevel, case when nullif( ter.primaryManager, 0 ) is null then 'inherit' else 'explicit' end as origin from territories ter with (nolock) inner join territoryManagers cte on cte.territoryId = ter.parentTerritoryId and cte.fsl_tablecode = ter.fsl_tablecode and cte.ownerId = ter.ownerId ), primaryManagers as ( select ownerId, fsl_tablecode, territoryId, primaryManager, primaryManagerOwnerId, primaryManagerOwnerType, origin from territoryManagers with (nolock) where primaryManager > 0 ), mappedOperatorsRaw as ( select opr.fsl_choId,t.territoryId, t.name as territoryName, opr.operatorId, t.territoryPath, t.regionName, t.divisionName, pt.territoryId as parentTerritoryId, pt.name as parentTerritory, m.fspro_userId as accountOwnerUserId, m.ownerId as accountOwnerOwnerId, m.fsl_tablecode as accountOwnerOwnerType, case when m.fspro_userId is not null then 1 else case when pm.primaryManager is not null then case when pm.origin = 'explicit' then 2 else 3 end else 4 end end accountOwnerRank from tbl_OPR_ClientOperators opr with (nolock) left outer join tbl_fspro_members m with (nolock) on m.fspro_userId = opr.mfr_bsr_id and m.ownerId = opr.ownerId and m.fsl_tablecode = opr.fsltablecode inner join tbl_TER_Territories t with (nolock) on t.territoryId = opr.territoryId and t.ownerId = opr.ownerId and t.fsl_tablecode = opr.fsltablecode inner join tbl_TER_Territories pt with (nolock) on pt.territoryId = t.parentTerritoryId and pt.ownerId = t.ownerId and pt.fsl_tablecode = t.fsl_tablecode left outer join primaryManagers pm on pm.ownerId = t.ownerId and pm.fsl_tablecode = t.fsl_tablecode and pm.territoryId = t.territoryId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and fsl_choId > 0 ), mappedOperatorsRanked as ( select raw.*, row_number() over ( partition by fsl_choid order by raw.accountOwnerRank, raw.operatorId ) as seq from mappedOperatorsRaw raw ), mappedOperators as ( select mor.fsl_choId, mor.operatorId, mor.territoryId, mor.territoryName, mor.territoryPath, mor.regionName, mor.divisionName, mor.parentTerritoryId, mor.parentTerritory, mor.accountOwnerOwnerType, mor.accountOwnerOwnerId, mor.accountOwnerUserId, m.firstName + ' ' + m.lastName as accountOwnerFullName from mappedOperatorsRanked mor left outer join tbl_Fspro_members m with (nolock) on m.ownerId = mor.accountOwnerOwnerId and m.fsl_tablecode = mor.accountOwnerOwnerType and m.fspro_userId = mor.accountOwnerUserId where seq = 1 ) update u set u.operatorTerritoryPath = o.territoryPath, u.operatorTerritoryId = o.territoryId, u.operatorTerritory = o.territoryName, u.operatorRegion = o.regionName, u.operatorDivision = o.divisionName, u.parentTerritoryId = o.parentTerritoryId, u.parentTerritory = o.parentTerritory, u.inClientLibrary = 'Y', u.clientOperatorId = o.operatorId, u.accountOwnerUserId = o.accountOwnerUserId, u.accountOwnerOwnerId = o.accountOwnerOwnerId, u.accountOwnerOwnerType = o.accountOwnerOwnerType, u.accountOwnerFullName = o.accountOwnerFullName from [#_1fsUniverseLoadTable#] u inner join mappedOperators o on o.fsl_choId = u.operatorChoId declare @brokerId int = #_brokerId#; with mappedDistributors as ( select cdr.fsl_dstId, t.territoryId, t.name as territoryName, t.territoryPath, t.regionName, t.divisionName, pt.territoryId as parentTerritoryId, pt.name as parentTerritory from tbl_CDR_Distributors cdr with (nolock) 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_Territories pt with (nolock) on pt.territoryId = t.parentTerritoryId and pt.ownerId = t.ownerId and pt.fsl_tablecode = t.fsl_tablecode where cdr.ownerId = @brokerId and cdr.fsl_tablecode = 'BRO' and cdr.fsl_dstId > 0 and cdr.fsl_mapPriority = 1 and cdr.cdr_dstCompanyType = 'B' ) update u set u.operatorTerritoryPath = d.territoryPath, u.operatorTerritoryId = d.territoryId, u.operatorTerritory = d.territoryName, u.operatorRegion = d.regionName, u.operatorDivision = d.divisionName, u.parentTerritoryId = d.parentTerritoryId, u.parentTerritory = d.parentTerritory from [#_1fsUniverseLoadTable#] u inner join mappedDistributors d on d.fsl_dstId = nullif( u.primaryDstId, 0 ) where u.operatorTerritoryId is null and coalesce( u.inClientLibrary, 'N' ) = 'N' declare @brokerId int = #_brokerId#; with mappedDistributors as ( select cdr.fsl_dstId, t.territoryId, t.name as territoryName, t.territoryPath, t.regionName, t.divisionName, pt.territoryId as parentTerritoryId, pt.name as parentTerritory from tbl_CDR_Distributors cdr with (nolock) 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_Territories pt with (nolock) on pt.territoryId = t.parentTerritoryId and pt.ownerId = t.ownerId and pt.fsl_tablecode = t.fsl_tablecode where cdr.ownerId = @brokerId and cdr.fsl_tablecode = 'BRO' and cdr.fsl_dstId > 0 and cdr.fsl_mapPriority = 1 and cdr.cdr_dstCompanyType = 'B' ) update u set u.operatorTerritoryPath = d.territoryPath, u.operatorTerritoryId = d.territoryId, u.operatorTerritory = d.territoryName, u.operatorRegion = d.regionName, u.operatorDivision = d.divisionName, u.parentTerritoryId = d.parentTerritoryId, u.parentTerritory = d.parentTerritory from [#_1fsUniverseLoadTable#] u inner join mappedDistributors d on d.fsl_dstId = nullif( u.secondaryDstId, 0 ) where u.operatorTerritoryId is null and coalesce( u.inClientLibrary, 'N' ) = 'N' declare @brokerId int = #_brokerId#; with territories as ( select z3d.zipCode_3d, ter.name as territoryName, ter.regionName, ter.divisionName, ter.territoryId, ter.territoryPath, pter.name as parentTerritory, pter.territoryId as parentTerritoryId from tbl_UT_ZipCodes_3d z3d with (nolock) inner join tbl_TER_zipCodeLink zcl with (nolock) on zcl.zip3d_Id = z3d.zip3d_id inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = zcl.territoryId left outer join tbl_TER_Territories pter with (nolock) on pter.ownerId = ter.ownerId and pter.fsl_tablecode = ter.fsl_tablecode and pter.territoryId = ter.parentTerritoryId inner join tbl_TER_TerritoryLevels l with (nolock) on l.levelId = ter.levelId and l.zipCodeMapping = 'STANDARD' and l.brokerLevel = 1 where ter.ownerId = @brokerId and ter.fsl_tablecode = 'BRO' ) update u set u.operatorTerritoryPath = t.territoryPath, u.operatorTerritoryId = t.territoryId, u.operatorTerritory = t.territoryName, u.operatorDivision = t.divisionName, u.operatorRegion = t.regionName, u.parentTerritoryId = t.parentTerritoryId, u.parentTerritory = t.parentTerritory from [#_1fsUniverseLoadTable#] u inner join territories t on t.zipCode_3d = left( u.operatorZipCode, 3 ) where u.operatorTerritoryId is null and coalesce( u.inClientLibrary, 'N' ) = 'N' and nullif( u.operatorZipCode, '' ) is not null truncate table [#_broker1fsUniverseTable#]; insert into [#_broker1fsUniverseTable#] select * from [#_1fsUniverseLoadTable#] with (nolock); drop table if exists [#_1fsUniverseLoadTable#]; update opr set opr.estMonthlyVolumeFrom1FS = coalesce( ofs.estMonthlyVolume, 0.0 ), -- ( select ofs.estMonthlyVolume from tbl_DW_1fsUniverse#_tableSuffix# ofs with (nolock) where ofs.operatorChoId = opr.fsl_choId ) opr.percentScoreFrom1fs = coalesce( ofs.percentScore, 0 ) from tbl_OPR_CLientOperators opr inner join [#_broker1fsUniverseTable#] ofs with (nolock) on ofs.operatorChoId = opr.fsl_choId where opr.ownerId = and opr.fsltablecode ='BRO' truncate table tbl_DW_1fsSources_BRO#_brokerId# with toAdd as ( select 'BRO' ownerType, #_brokerId# ownerId, sourceType, sourceId, sourceName from tbl_DW_1fsSources_MFR#_mfrId# m with (nolock) where sourceType = 'CHO' and not exists ( select * from tbl_DW_1fsSources_BRO#_brokerId# b with (nolock) where b.sourceType = m.sourceType and b.sourceId = m.sourceId ) and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where ownerId = #_brokerId# and fsltablecode = 'BRO' and fsl_choId = m.sourceId ) ) insert into tbl_DW_1fsSources_BRO#_brokerId# ( ownerType, ownerId, sourceType, sourceId, sourceName ) select * from toAdd select count(*) as operatorCount from #_broker1fsUniverseTable# with (nolock)

#lsnumberformat( qmd_summary.operatorCount )# universe total records in #_broker1fsUniverseTable#

with missingTerritory as ( select left( operatorZipCode, 3 ) zip3d, primaryDstId, primaryDstName, secondaryDstId, secondaryDstName from #_broker1fsUniverseTable# with (nolock) where operatorTerritoryId is null ) select zip3d _3_Digit_ZipCode, primaryDstId primaryDst_1fsid, primaryDstName primaryDst_1fsName, secondaryDstId secondaryDst_1fsId, secondaryDstName secondaryDst_1fsName, count(*) as _1fsOperatorCount from missingTerritory group by zip3d, primaryDstName, secondaryDstName, primaryDstId, secondaryDstId order by _3_Digit_ZipCode, primaryDst_1fsName, secondaryDst_1fsName

Territory resolution is first done by primary distributor, then secondary distributor and then 3-digit zipcode. If there is a primary or secondary distributor listed below it is likely that the listed distributor is not properly setup in the distributor directory. If no distributor is listed, the 3-digit zipcode is not mapped to a territory. In all cases the 3-digit zipcodes below are not mapped to a territory.

#htmleditformat( _pingMessage )#