This is only for brokers select mfr_id, mfr_name from tblManufacturers mfr with (nolock) where exists ( select * from sys.tables where name = 'tbl_DW_DistributorSales_MFR' + cast( mfr_id as varchar )) and exists ( select * from tbl_SMA_BrokerOfficeLink sma where sma.mfr_id = mfr.mfr_id and sma.brokerid = ) and mfr_id = and mfr_id not in ( 6868, 6500 ) and mfr_id in ( select distinct bpl.mfrId 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 nullif( mt.internalSalesId, '' ) = pt.mfrTerritoryNbr where bpl.ownerId = and bpl.ownerType = and pt.hireStatus = 'A' ) order by mfr_name select name from sys.columns where object_id = (select object_id from sys.tables where name = 'tbl_DW_DistributorSales_MFR0') and name in ( ) order by column_id select top 1 cdr_recordId from tbl_DW_DistributorSales#_mfrSuffix# where year( salesPeriod ) > delete from tbl_DW_DistributorSales#_tableSuffix# where mfr_id = update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @mfrId int = ; with buyingGroupsRaw as ( select afl.affiliationDate, byg.uniqueId as buyingGroupId, byg.name as buyingGroup, 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 ) , buyingGroups as ( select buyingGroupId, buyingGroup, cdr_recordId from buyingGroupsRaw where seq = 1 ) , mappedDistributors as ( select cdr_recordId, ownerId, fsl_tablecode, cdr_dstName, cdr_dstCompanyType, fsl_dstId, cdr_dstAddress1, cdr_dstCity, cdr_dstState, cdr_dstZip, mfr_bsr_id, cdr_dstPath, cdr_territoryId, row_number() over( partition by fsl_dstId order by fsl_mapPriority, cdr_recordId ) as fsl_mapPriority from tbl_CDR_Distributors mcdr with (nolock) where ownerId = @ownerId and fsl_tablecode = @ownerType and fsl_dstId > 0 ), distributorsRaw as ( select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId sales_cdr_recordId, cdr.cdr_dstName sales_cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_recordId else cdr.cdr_recordId end as cdr_recordId, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstName else cdr.cdr_dstName end as cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstAddress1 else cdr.cdr_dstAddress1 end as cdr_dstAddress1, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstCity else cdr.cdr_dstCity end as cdr_dstCity, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstState else cdr.cdr_dstState end as cdr_dstState, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstZip else cdr.cdr_dstZip end as cdr_dstZip, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstPath else cdr.cdr_dstPath end as cdr_dstPath, case when mcdr.cdr_recordId is not null then mcdr.mfr_bsr_id else cdr.mfr_bsr_id end as accountOwner_fspro_userId, case when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'B' then 'DISTRIBUTOR' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'R' then 'REDISTRIBUTOR' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'S' then 'SPECIAL' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'P' then 'PARENT' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'O' then 'OPERATOR' else 'UNKNOWN' end as customerType, cdr.fsl_dstId, cdr.cdr_dstCompanyType, cdr.cdr_redistAcctFor, cdr.cdr_specialAcctType, cdr.cdr_dstCode, cdr.cdr_territoryId from tbl_CDR_Distributors cdr inner join mappedDistributors mcdr on mcdr.ownerId = cdr.ownerId and mcdr.fsl_tablecode = cdr.fsl_tablecode and mcdr.fsl_dstId = cdr.fsl_dstId and mcdr.fsl_mapPriority = 1 and mcdr.cdr_recordId = cdr.cdr_recordId and mcdr.fsl_dstId > 0 where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ), distributors as ( select cdr.*, ter.territoryId, ter.name as territoryName, ter.territoryPath, ter.regionName, ter.divisionName, pcdr.cdr_recordId as parent_cdr_recordId, nullif( pcdr.fsl_dstId, 0 ) parent_oneFsId, pcdr.cdr_dstCode as parent_cdr_dstCode, pcdr.cdr_dstName parent_cdr_dstName, pcdr.cdr_dstCompanyType parent_cdr_dstCompanyType, byg.buyingGroupId, byg.buyingGroup from distributorsRaw cdr left outer join buyingGroups byg on byg.cdr_recordId = cdr.cdr_recordId inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = cdr.ownerId and ter.fsl_tablecode= cdr.fsl_tablecode and ter.territoryId= cdr.cdr_territoryId left join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_DST_Distributors pdst with (nolock) on pdst.dstId = dst.dstParentCo left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = pdst.dstId and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode= cdr.fsl_tablecode ), products as ( select 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, ph.focusProduct, ph.privateLabelType, ph.productHierarchyPath, ph.budgetCategoryId, ph.budgetCategory, ph.brand, ph.brandId, coalesce( ph.discontinued, 'N' ) discontinued, ph.mfrSkuId, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = @mfrId ), hiredTerritories as ( select distinct mt.territoryId /* pt.mfrTerritoryName, mt.territoryId, mt.name, bpl.mfrId */ 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 nullif( mt.internalSalesId, '' ) = pt.mfrTerritoryNbr where bpl.ownerId = @ownerId and bpl.ownerType = @ownerType and pt.hireStatus = 'A' and bpl.mfrId = @mfrId ), mfrInvoiceHistory as ( select ih.ownerId mfrId, cdr.fsl_dstId, ih.skuId, ih.invoiceDate, ih.caseAmt cases, ih.weightAmt lbs, ih.dollarAmt dollars, row_number() over( partition by cdr.fsl_dstId, ih.skuId order by ih.invoiceDate desc ) historyRank from tbl_CDR_InvoiceHistory ih with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = ih.ownerId and cdr.fsl_tablecode = ih.ownertype and cdr.cdr_recordId = ih.cdr_recordId and cdr.fsl_dstId > 0 where ih.ownerId = @mfrId and ih.ownerType ='MFR' and ( ih.caseAmt != 0 or ih.weightAmt != 0 or ih.dollarAmt != 0 ) ), mfrLastInvoice as ( select mfrId, fsl_dstId, skuId, invoiceDate as lastInvoiceDate, cases as lastInvoiceCases, lbs as lastInvoiceLbs, dollars as lastInvoiceDollars from mfrInvoiceHistory where historyRank = 1 ), mfrSales as ( select mds.oneFsId, mds.skuId, mds.salesPeriod, mds.salesPeriodType, li.lastInvoiceDate, li.lastInvoiceCases, li.lastInvoiceLbs, li.lastInvoiceDollars, sum( mds.cases ) cases, sum( mds.lbs ) lbs, sum( mds.dollars ) dollars, sum( mds.alt ) alt from tbl_DW_DistributorSales#_mfrSuffix# mds with (nolock) left outer join mfrLastInvoice li on li.mfrId = @mfrId and li.fsl_dstId = mds.oneFsId and li.skuId = mds.skuId where mds.oneFsId > 0 and mds.territoryId in ( select territoryId from hiredTerritories ) and mds.salesPeriodType = 'MONTH' group by mds.oneFsId, mds.skuId, mds.salesPeriod, mds.salesPeriodType, li.lastInvoiceDate, li.lastInvoiceCases, li.lastInvoiceLbs, li.lastInvoiceDollars ), etl as ( select #qmd_batch.ownerId# ownerId, '#qmd_batch.ownerType#' ownerType, sales.salesPeriod, sales.salesPeriodType, sales.cases, sales.lbs, sales.dollars, sales.alt, 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, ph.focusProduct, ph.privateLabelType, ph.productHierarchyPath, ph.budgetCategoryId, ph.budgetCategory, ph.brand, ph.brandId, ph.discontinued, ph.mfrSkuId, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, cdr.sales_cdr_recordId,cdr.sales_cdr_dstName,cdr.cdr_recordId,cdr.cdr_dstName,cdr.cdr_dstAddress1,cdr.cdr_dstCity,cdr.cdr_dstState,cdr.cdr_dstZip,cdr.cdr_dstPath,cdr.accountOwner_fspro_userId,cdr.customerType,cdr.fsl_dstId oneFsId,cdr.cdr_dstCompanyType,cdr.cdr_redistAcctFor,cdr.cdr_specialAcctType,cdr.cdr_dstCode,cdr.cdr_territoryId as territoryId,cdr.territoryName,cdr.territoryPath,cdr.regionName,cdr.divisionName,cdr.parent_cdr_recordId,cdr.parent_oneFsId,cdr.parent_cdr_dstCode,cdr.parent_cdr_dstName,cdr.parent_cdr_dstCompanyType,cdr.buyingGroupId,cdr.buyingGroup, m.firstName as accountOwner_firstName, m.lastName as accountOwner_lastName, m.email accountOwner_email, 'DIRECT' purchasingMethod, fp.fiscalYear, fp.periodIndex as fiscalMonth, 'MFR' sourceOfData, sales.lastInvoiceDate, sales.lastInvoiceCases, sales.lastInvoiceLbs, sales.lastInvoiceDollars from mfrSales sales inner join products ph on ph.mfrSkuId = sales.skuId inner join distributors cdr on cdr.fsl_dstId = sales.oneFsId inner join tbl_CRM_FiscalPeriods fp with (nolock) on fp.ownerId = @ownerId and fp.ownerType = @ownerType and fp.periodType = 'M' and fp.fiscalStartMonth = sales.salesPeriod left outer join tbl_FSPRO_Members m on m.fspro_userId = cdr.accountOwner_fspro_userId and m.ownerId = @ownerId and m.fsl_tablecode = @ownerType ) insert into tbl_DW_DistributorSales#_tableSuffix# ( #_columnList# ) select #_columnList# from etl

#htmleditformat( mfr_name )# - #lsnumberformat( _r.recordCount )# records - #lsnumberformat( _endTicks - _startTicks )#ms.

#htmleditformat( _r.sql )#

update dw set dw.customerSKU = cs.customerSKU, dw.customerSKUType = cs.customerType from tbl_DW_DistributorSales#_tableSuffix# dw inner join tbl_DW_CustomerSKUs#_tableSuffix# cs on cs.ownerId = dw.ownerId and cs.ownerType = dw.ownertype and cs.skuId = dw.skuId and cs.cdr_recordId = dw.cdr_recordId where dw.mfr_id =