drop table if exists [#_operatorAnalyzerLoadTable#]; select * into [#_operatorAnalyzerLoadTable#] from [#_brokerOperatorAnalyzerTable#] with (nolock) where 1 = 2 drop table if exists [#_clientOperatorSegmentsTable#]; select opr.fsltablecode, opr.ownerId, opr.operatorId, cseg.clientSegId, cseg.parentSegmentName, cseg.segmentName, cseg.segmentPath into [#_clientOperatorSegmentsTable#] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_OPR_CLientSegments cseg with (nolock) on cseg.ownerId = opr.ownerId and cseg.fsl_tablecode = opr.fsltablecode and cseg.clientSEgId = opr.oprSegment where opr.ownerId = and opr.fsltablecode = 'BRO'; alter table [#_clientOperatorSegmentsTable#] add constraint [pk_#_clientOperatorSegmentsTable#] primary key ( fsltablecode, ownerId, operatorId ); #_clientOperatorSegmentsTable# select distinct mfrId from tbl_TPM_ExternalClaimHistory with (nolock) where ownerId = and ownerType = 'BRO' and coalesce( excluded, 'N' ) = 'N' and mfrId is not null and mfrId not in ( ) select distinct mfrId from tbl_TPM_ExternalClaimHistory with (nolock) where ownerId = and ownerType = 'BRO' and coalesce( excluded, 'N' ) = 'N' and mfrId is not null select mfr_name from tblManufacturers with (nolock) where mfr_id = select distinct ter.territoryId from tbl_BRO_Principals p with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = p.ownerId and pt.ownertype = p.ownerType and pt.principalId = p.principalId and pt.hireStatus = 'A' inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = p.mfrId and ter.fsl_tablecode = 'MFR' and nullif( ter.internalSalesId, '' ) = pt.mfrTerritoryNbr inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.brokerId = p.ownerId and sma.mfr_id = ter.ownerId and sma.territoryId = ter.territoryId where p.mfrId = and p.ownerId = and p.ownertype = 'BRO' select distinct ter.internalSalesId, ter.name as territoryName from tbl_TER_Territories ter with (nolock) where ter.fsl_tablecode = 'MFR' and ter.ownerId = and ter.territoryId in ( ) order by internalSalesId drop table if exists [#_mfr1fsDataTable#]; select coalesce( u.operatorChoId, o.choId ) cho_id, o.locationLevelPurchasing, o.beneficiary, u.operatorPurchasing into [#_mfr1fsDataTable#] from [#_mfrUniverseTable#] u with (nolock) full outer join [#_mfr1fsOperatorsTable#] o with (nolock) on o.choId = u.operatorChoId where ( o.locationLevelPurchasing = 'N' and o.beneficiary = 'Y' ) or u.operatorPurchasing = 'Y'; alter table [#_mfr1fsDataTable#] alter column cho_id int not null; alter table [#_mfr1fsDataTable#] add constraint [pk_#_mfr1fsDataTable#] primary key ( cho_id ); with etl as ( select dw.operatorName, dw.cho_id, dw.cho_type, dw.segmentId, dw.operatorSegment, dw.purchasingMonth, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catnum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, o.operatorTerritoryId territoryId, o.operatorTerritory territoryName, o.operatorRegion as regionName, o.operatorDivision as divisionName, dw.rollup_cho_id, dw.rollup_operatorName, dw.operatorCity, dw.operatorAddress, dw.operatorZipCode, dw.operatorState, dw.rollup_operatorCity, dw.rollup_operatorState, dw.rollup_operatorZipCode, dw.rollup_operatorAddress, dw.rollup_cho_type, dw.dstId, dw.dstName, dw.parentDstId, dw.parentDstName, dw.channelSegment, dw.mainSegment, dw.subSegment, dw.operatorCounty, dw.rollup_operatorCounty, o.operatorTerritoryPath territoryPath, ph.brand, ph.brandId, ph.brandOwnerType, ph.brandOwnerId, -- averageClientVolumne ph.ofsCategoryId, ph.ofsCategory, ph.productHierarchyPath, coalesce( sum( dw.forecast_amt ), 0.0) as forecast_amt, coalesce( sum( dw.forecast_qty ), 0.0 ) as forecast_qty, o.accountOwnerOwnerId, o.accountOwnerOwnerType, o.accountOwnerUserId, o.accountOwnerFullName, o.parentChoId as parent_cho_id, o.parentName as parent_operatorName, dw.agreementOwnerChoId, dw.agreementOwnerName, cseg.clientSegId, cseg.parentSegmentName as parentSegment, cseg.segmentName, cseg.segmentPath from #_mfrOperatorAnalyzerTable# dw with (nolock) inner join #_brokerProductHierarchyTable# ph with (nolock) on ph.mfr_id = #_mfrId# and ph.mfrSkuId= dw.skuId left outer join #_broker1fsUniverseTable# o with (nolock) on o.operatorChoId = dw.cho_Id and o.ownerId = #_brokerId# and o.ownerType = 'BRO' left outer join [#_clientOperatorSegmentsTable#] cseg with (nolock) on cseg.operatorId = o.clientOperatorId and cseg.ownerId = #_brokerId# and cseg.fsltablecode = 'BRO' where dw.territoryId in ( ) and ( ( dw.cho_type in ( 'I', 'U' ) ) or ( dw.cho_type = 'O' and exists ( select * from [#_mfr1fsDataTable#] m1dt with (nolock) where m1dt.cho_id = dw.cho_id and m1dt.locationLevelPurchasing = 'N' and m1dt.beneficiary = 'Y' ) ) ) and exists( select * from [#_mfr1fsDataTable#] m1dt with (nolock) where m1dt.cho_id = dw.cho_id and m1dt.operatorPurchasing = 'Y' ) and dw.operatorName not like '%blanket%' group by dw.operatorName, dw.cho_id, dw.cho_type, dw.segmentId, dw.operatorSegment, dw.purchasingMonth, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catnum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, o.operatorTerritoryId, o.operatorTerritory, o.operatorRegion, o.operatorDivision, dw.rollup_cho_id, dw.rollup_operatorName, dw.operatorCity, dw.operatorAddress, dw.operatorZipCode, dw.operatorState, dw.rollup_operatorCity, dw.rollup_operatorState, dw.rollup_operatorZipCode, dw.rollup_operatorAddress, dw.rollup_cho_type, dw.dstId, dw.dstName, dw.parentDstId, dw.parentDstName, dw.channelSegment, dw.mainSegment, dw.subSegment, dw.operatorCounty, dw.rollup_operatorCounty, o.operatorTerritoryPath, ph.brand, ph.brandId, ph.brandOwnerType, ph.brandOwnerId, ph.ofsCategoryId, ph.ofsCategory, ph.productHierarchyPath, o.accountOwnerOwnerId, o.accountOwnerOwnerType, o.accountOwnerUserId, o.accountOwnerFullName, o.parentChoId, o.parentName, dw.agreementOwnerChoId, dw.agreementOwnerName, cseg.clientSegId, cseg.parentSegmentName, cseg.segmentName, cseg.segmentPath ) insert into [#_operatorAnalyzerLoadTable#] ( ownerId, ownerType, #_etlColumns# ) select #_brokerId#, 'BRO', #_etlColumns# from etl drop table if exists [#_mfr1fsDataTable#];

#encodeforHTML( _mfr_name )#

#encodeforHTML( _r.sql )#

with rollupOperatorIds as ( select distinct rollup_cho_id from [#_operatorAnalyzerLoadTable#] a with (nolock) where not exists ( select * from [#_operatorAnalyzerLoadTable#] b with (nolock) where b.cho_id = a.rollup_cho_id and b.mfr_id = #_mfrId# ) and a.rollup_cho_id > 0 ) select rollup_cho_id into #_rollupOperatorIdsTable# from rollupOperatorIds; with rollupOperators as ( select u.operatorChoId as cho_id, case when u.operatorType = 'CHAINHQ' then 'O' when u.operatorType = 'INDEPENDENT' then 'I' when u.operatorType = 'GPO' then 'G' when u.operatorType = 'CMC' then 'C' when u.operatorType = 'UNIT' then 'U' else '?' end as cho_type, u.operatorName, u.operatorAddress, u.operatorCity, u.operatorState, u.operatorZipCode, u.operatorCounty, u.segmentId, u.operatorSegment, u.channelSegment, u.mainSegment, u.subSegment, cseg.clientSegId, cseg.parentSegmentName as parentSegment, cseg.segmentName, cseg.segmentPath, u.operatorTerritoryId, u.operatorTerritory, u.operatorTerritoryPath, u.operatorRegion, u.operatorDivision, u.primaryParentDstId as parentDstId, u.primaryParentDstName parentDstName, u.primaryDstName as dstName, u.primaryDstId dstId, u.accountOwnerOwnerId, u.accountOwnerOwnerType, u.accountOwnerUserId, u.accountOwnerFullName, u.parentChoId as parent_cho_id, u.parentName as parent_operatorName from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) left outer join [#_clientOperatorSegmentsTable#] cseg with (nolock) on cseg.operatorId = u.clientOperatorId and cseg.ownerId = #_brokerId# and cseg.fsltablecode = 'BRO' where operatorChoId in ( select rollup_cho_id from #_rollupOperatorIdsTable# ) ), rollupETL as ( select u.operatorName, u.cho_id, u.cho_type, u.segmentId, u.operatorSegment, dw.purchasingMonth, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catnum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, u.operatorTerritoryId territoryId, u.operatorTerritory territoryName, u.operatorDivision as divisionName, u.operatorRegion as regionName, u.cho_id rollup_cho_id, u.operatorName rollup_operatorName, u.operatorCity, u.operatorAddress, u.operatorZipCode, u.operatorState, u.operatorCity rollup_operatorCity, u.operatorState rollup_operatorState, u.operatorZipCode rollup_operatorZipCode, u.operatorAddress rollup_operatorAddress, u.cho_type rollup_cho_type, u.dstId, u.dstName, u.parentDstId, u.parentDstName, u.channelSegment, u.mainSegment, u.subSegment, u.clientSegId, u.parentSegment, u.segmentName, u.segmentPath, u.operatorCounty, u.operatorCounty rollup_operatorCounty, u.operatorTerritoryPath territoryPath, u.accountOwnerOwnerId, u.accountOwnerOwnerType, u.accountOwnerUserId, u.accountOwnerFullName, ph.brand, ph.brandId, ph.brandOwnerType, ph.brandOwnerId, -- averageClientVolumne ph.ofsCategoryId, ph.ofsCategory, ph.productHierarchyPath, u.parent_cho_id, u.parent_operatorName, dw.agreementOwnerChoId, dw.agreementOwnerName, coalesce( sum( dw.forecast_amt ), 0.0) as forecast_amt, coalesce( sum( dw.forecast_qty ), 0.0 ) as forecast_qty from rollupOperators u with (nolock) inner join [#_operatorAnalyzerLoadTable#] dw with (nolock) on dw.rollup_cho_id = u.cho_id and dw.cho_id != dw.rollup_cho_id inner join #_brokerProductHierarchyTable# ph with (nolock) on ph.mfr_id = #_mfrId# and ph.skuId = dw.skuId where dw.cho_type in ( 'U' ) group by u.operatorName, u.cho_id, u.cho_type, u.segmentId, u.operatorSegment, dw.purchasingMonth, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catnum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, u.operatorTerritoryId, u.operatorTerritory, u.operatorRegion, u.operatorDivision, u.operatorCity, u.operatorAddress, u.operatorZipCode, u.operatorState, u.dstId, u.dstName, u.parentDstId, u.parentDstName, u.channelSegment, u.mainSegment, u.subSegment, u.clientSegId, u.parentSegment, u.segmentName, u.segmentPath, u.operatorCounty, u.operatorTerritoryPath, ph.brand, ph.brandId, ph.brandOwnerType, ph.brandOwnerId, ph.ofsCategoryId, ph.ofsCategory, ph.productHierarchyPath, u.accountOwnerOwnerId, u.accountOwnerOwnerType, u.accountOwnerUserId, u.accountOwnerFullName, u.parent_cho_id, u.parent_operatorName, dw.agreementOwnerChoId, dw.agreementOwnerName ) insert into [#_operatorAnalyzerLoadTable#] ( ownerId, ownerType, #_etlColumns# ) select #_brokerId#, 'BRO', #_etlColumns# from rollupETL; drop table #_rollupOperatorIdsTable#; with unitDistributors as ( select oa.cho_id, oa.purchasingMonth, oa.skuId, oa.parentDstId, count( distinct oau.dstId ) dstCount, min( oau.dstId ) newDstId from [#_operatorAnalyzerLoadTable#] oa with (nolock) inner join [#_operatorAnalyzerLoadTable#] oau (nolock) on oau.rollup_cho_Id = oa.rollup_cho_Id and oau.rollup_cho_id != oau.cho_id and oau.purchasingMonth = oa.purchasingMonth and oau.skuId = oa.skuId and oau.parentDstId = oa.parentDstId where oa.dstId is null and oa.parentDstId is not null and oa.cho_id = oa.rollup_cho_id and oa.mfr_id = #_mfrId# group by oa.cho_id, oa.purchasingMonth, oa.skuId, oa.parentDstId ), resolvedDistributors as ( select ud.*, d.dstName as newDstName from unitDistributors ud with (nolock) inner join tbl_DST_Distributors d with (nolock) on d.dstId = ud.newDstId where dstCount = 1 ) update oa set oa.dstId = rd.newDstId, oa.dstName = rd.newDstName from [#_operatorAnalyzerLoadTable#] oa inner join resolvedDistributors rd with (nolock) on rd.cho_id = oa.cho_Id and rd.purchasingMonth = oa.purchasingMonth and rd.skuId = oa.skuId and rd.parentDstId = oa.parentDstId where oa.dstId is null and oa.parentDstId is not null and oa.cho_id = oa.rollup_cho_id and oa.mfr_id = #_mfrId# with toAdd as ( select 'BRO' ownerType, #_brokerId# ownerId, memberGroupChoId, memberGroupName, memberChoId, memberName, partnerAffiliateNbr, affiliationDate, deaffiliationDate, clientAffiliationDate, clientDeAffiliationDate from tbl_DW_1fsOperatorMemberships_MFR#_mfrId# mm with (nolock) where exists ( select * from [#_operatorAnalyzerLoadTable#] b with (nolock) where b.cho_id = mm.memberChoId ) and not exists ( select * from tbl_DW_1fsOperatorMemberships_BRO#_brokerId# bm with (nolock) where bm.memberGroupChoId = mm.memberGroupChoId and bm.memberChoId = mm.memberChoId ) ) insert into tbl_DW_1fsOperatorMemberships_BRO#_brokerId# ( ownerType, ownerId, memberGroupChoId, memberGroupName, memberChoId, memberName, partnerAffiliateNbr, affiliationDate, deaffiliationDate, clientAffiliationDate, clientDeAffiliationDate ) select ownerType, ownerId, memberGroupChoId, memberGroupName, memberChoId, memberName, partnerAffiliateNbr, affiliationDate, deaffiliationDate, clientAffiliationDate, clientDeAffiliationDate from toAdd update b set b.deaffiliationDate = m.deaffiliationDate, b.clientDeaffiliationDate = m.clientDeaffiliationDate from tbl_DW_1fsOperatorMemberships_BRO#_brokerId# b inner join tbl_DW_1fsOperatorMemberships_MFR#_mfrId# m with (nolock) on m.memberGroupChoId = b.memberGroupChoId and m.memberChoId = b.memberChoId and m.ownerId = #_mfrId# and m.ownerType ='MFR' where b.ownerId = #_brokerId# and b.ownerType = 'BRO' and ( coalesce( b.deaffiliationDate, cast( '06/01/1966' as datetime )) != m.deaffiliationDate ) select name from sys.columns where object_id = ( select object_id from sys.tables where name = ) and is_identity = 0 truncate table [#_brokerOperatorAnalyzerTable#]; insert into [#_brokerOperatorAnalyzerTable#] ( #_columnsToInsert #) select #_columnsToInsert# from [#_operatorAnalyzerLoadTable#] with (nolock); drop table if exists [#_operatorAnalyzerLoadTable#]; drop table if exists [#_clientOperatorSegmentsTable#]; truncate table tbl_DW_PurchasingMonths#_tableSuffix# insert into tbl_DW_PurchasingMonths#_tableSuffix# ( ownerType, ownerId, purchasingMonth, analyzerRecords ) select ownerType, ownerId, purchasingMonth, count(*) analyzerRecords from [#_brokerOperatorAnalyzerTable#] with (nolock) where ownerId = and ownerType = group by ownerType, ownerId, purchasingMonth order by ownertype, ownerId, purchasingMonth update o set o.purchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = o.ownerId and oa.ownerType = o.ownerType and oa.cho_id = o.choId ) then 'Y' else 'N' end from tbl_DW_1fsOperators#_tableSuffix# o where o.ownerId = and o.ownerType = ---> update o set o.locationLevelPurchasing = 'Y', o.locationLevelPurchasingType = '4' from tbl_DW_1fsOperators#_tableSuffix# o where o.operatorType = 'PARENT' and coalesce( o.locationLevelPurchasing, 'N' ) = 'N' and o.purchasing = 'Y' and o.ownerId = and o.ownertype = and exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# u with (nolock) where u.ownerId = o.ownerId and u.ownerType = o.ownerType and u.rollup_cho_id = o.choId and u.cho_Id <> u.rollup_cho_id ) update h set h.operatorPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = h.ownerId and oa.ownerType = h.ownerType and oa.skuId = h.skuId ) then 'Y' else 'N' end from tbl_DW_ProductHierarchy#_tableSuffix# h where h.ownerId = and h.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 tbl_DW_1fsDistributors#_tableSuffix# d where d.ownerId = and d.ownerType =

#htmleditformat( _pingMessage )#