select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_Id where t.name = 'tbl_DW_DistributorSales#_tableSuffix#' and c.is_identity = 0 order by c.column_id truncate table tbl_DW_DistributorSales#_tableSuffix# select coalesce( redistributorReversing, 'Y' ) redistributorReversing from tblManufacturers with (nolock) where mfr_id =

Redistributor Numbers will be adjusted by reversing the Indirect Numbers.

Redistributor Numbers will NOT be adjusted.

declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @endYear int; declare @startYear int; declare @fiscalEndMonth datetime; select @endYear = max( fiscalYear ), @fiscalEndMonth = max( fiscalEndMonth ) from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0; if @endYear is null begin select @endYear = fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y' and getDate() between startDate and endDate end if @endYear is null begin set @endYear = Year( getDate() ); set @fiscalEndMonth = DateFromParts( Year( getDate() ), Month( getDate() ), 1 ); end set @startYear = @endYear - 2; with productHierarchyL2 as ( select *, left( productHierarchyPath, len( productHierarchyPath ) - charindex( '/', reverse( productHierarchyPath ))) l2ProductHierarchyPath from tbl_DW_ProductHierarchy#_tableSuffix# with (nolock) ), productHierarchyL3 as ( select *, left( l2ProductHierarchyPath, len( l2ProductHierarchyPath ) - charindex( '/', reverse( l2ProductHierarchyPath ))) l3ProductHierarchyPath from productHierarchyL2 with (nolock) ), productHierarchy as ( select *, left( l3ProductHierarchyPath, len( l3ProductHierarchyPath ) - charindex( '/', reverse( l3ProductHierarchyPath ))) l4ProductHierarchyPath from productHierarchyL3 with (nolock) ), monthly as ( select cdr.ownerId, cdr.fsl_tablecode as ownerType, cdr.cdr_recordId, cdr.customerType, nullif( cdr.fsl_dstId, 0 ) oneFsId, cdr.cdr_dstCode, cdr.cdr_dstName, cdr.cdr_dstCompanyType, cdr.cdr_dstPath, cdr.classificationId, cdr.classification, cdr.accountOwner_fspro_userId, ao.firstName as accountOwner_firstName, ao.lastName as accountOwner_lastName, ao.email as accountOwner_email, ao.ownerId as accountOwner_ownerId, ao.fsl_tablecode as accountOwner_ownerType, cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, ter.territoryId, ter.name as territoryName, ter.territoryPath, ter.regionName, ter.divisionName, nullif( ter.internalSalesId, '' ) as territoryNbr, 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, 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.l2ProductHierarchyPath, ph.l3ProductHierarchyPath, ph.l4ProductHierarchyPath, ph.budgetCategoryId, ph.budgetCategory, ph.brand, ph.brandId, coalesce( ph.discontinued, 'N' ) discontinued, ph.productSetTags, fp.fiscalStartMonth as salesPeriod, fp.fiscalYear, fp.monthIndex fiscalMonth, -- dateFromParts( cast( forecast_year as int ), cast( forecast_month as int ), 1 ) as salesPeriod, 'MONTH' as salesPeriodType, tpf.forecast_qty * ph.qtyConversionToCases as cases, tpf.forecast_qty * ph.qtyConversionToLbs as lbs, tpf.forecast_amt as dollars, tpf.forecast_alt as alt, null as commission, tpf.active_pipeline_cases, tpf.active_pipeline_lbs, tpf.active_pipeline_dollars, tpf.sold_pipeline_cases, tpf.sold_pipeline_lbs, tpf.sold_pipeline_dollars, cdr.cdr_specialAcctType, rcdr.cdr_dstName redist_cdr_dstName, rcdr.cdr_recordId redist_cdr_recordId, rcdr.fsl_dstId redist_oneFsId, byg.buyingGroupId, byg.buyingGroup, byg.buyingGroupId2, byg.buyingGroup2, byg.buyingGroupId3, byg.buyingGroup3, bcm.budgetCategoryMgr_fspro_userId, bcm.budgetCategoryMgr_firstName, bcm.budgetCategoryMgr_lastName, bcm.budgetCategoryMgr_email, 'N' realloc, cast( null as int ) as realloc_cdr_recordId, ph.mfr_Id, ph.mfr_name, ph.MFRAbbrev_Name, cast( null as varchar(50) ) as customerSKU, cast( null as varchar(4)) as customerSKUType from #_tpfTableName# tpf with (nolock) inner join tbl_CRM_FiscalPeriods fp with (nolock) on fp.ownerId = tpf.mfr_id and fp.ownerType = 'MFR' and fp.periodType = 'M' and fp.fiscalStartMonth = dateFromParts( cast( tpf.forecast_year as int ), cast( tpf.forecast_month as int ), 1 ) and fp.fiscalEndMonth <= @fiscalEndMonth and fp.fiscalYear between @startYear and @endYear inner join #_distributorsTableName# cdr with (nolock) on cdr.sales_cdr_recordId = tpf.tpartnerId and 'CDR' = tpf.tpartnerType and cdr.ownerId = tpf.mfr_id and cdr.fsl_tablecode = 'MFR' left outer join tbl_fspro_members ao with (nolock) on ao.fspro_userId = cdr.accountOwner_fspro_userId and ao.ownerId > 0 left outer join #_buyingGroupsTableName# byg with (nolock) 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 inner join productHierarchy ph with (nolock) on ph.ownerId = tpf.mfr_id and ph.ownerType = 'MFR' and ph.skuId= tpf.skuId left outer join tbl_CDR_Distributors rcdr with (nolock) on rcdr.cdr_recordId = cdr.cdr_redistAcctFor and rcdr.ownerId = cdr.ownerId and rcdr.fsl_tablecode = cdr.fsl_tablecode and 'RDA' = cdr.cdr_specialAcctType and 'S' = cdr.cdr_dstCompanyType left outer join #_budgetCategoryManagersTableName# bcm with (nolock) on bcm.ownerId = ph.ownerId and bcm.ownerType = ph.ownerType and bcm.territoryId = ter.territoryId and bcm.budgetCategoryId = ph.budgetCategoryId where tpf.mfr_id = @ownerId and tpf.tpartnertype = 'CDR' and tpf.forecast_status = 'A' ), etl as( select m.ownerId, m.ownerType, m.cdr_recordId, m.oneFsId, m.cdr_dstCode, m.cdr_dstName, m.cdr_dstCompanyType, m.cdr_dstPath, m.accountOwner_fspro_userId, m.accountOwner_firstName, m.accountOwner_lastName, m.accountOwner_email, m.accountOwner_ownerId, m.accountOwner_ownerType, m.cdr_dstAddress1, m.cdr_dstCity, m.cdr_dstState, m.cdr_dstZip, m.classificationId, m.classification, m.territoryId, m.territoryName, m.territoryPath, m.regionName, m.divisionName, m.territoryNbr, m.parent_cdr_recordId, m.parent_oneFsId, m.parent_cdr_dstCode, coalesce( m.parent_cdr_dstName, 'Independent' ) parent_cdr_dstName, m.parent_cdr_dstCompanyType, m.skuId, m.sku, m.skuDesc, m.prodId, m.product, m.shortDesc, m.categoryId, m.catNum, m.category, m.prodline_id, m.productLineCode, m.productLine, m.focusProduct, m.privateLabelType, m.productHierarchyPath, m.l2ProductHierarchyPath, m.l3ProductHierarchyPath, m.l4ProductHierarchyPath, m.budgetCategoryId, m.budgetCategory, m.brand, m.brandId, m.discontinued, m.productSetTags, m.salesPeriod, m.fiscalYear, m.fiscalMonth, m.salesPeriodType, cases, lbs, dollars, alt, commission, active_pipeline_cases, active_pipeline_lbs, active_pipeline_dollars, sold_pipeline_cases, sold_pipeline_lbs, sold_pipeline_dollars, m.cdr_specialAcctType, m.redist_cdr_dstName, m.redist_cdr_recordId, m.redist_oneFsId, m.buyingGroupId, m.buyingGroup, m.buyingGroupId2, m.buyingGroup2, m.buyingGroupId3, m.buyingGroup3, case when m.cdr_specialAcctType = 'RDA' then 'INDIRECT' else 'DIRECT' end purchasingMethod, m.customerType, m.budgetCategoryMgr_fspro_userId, m.budgetCategoryMgr_firstName, m.budgetCategoryMgr_lastName, m.budgetCategoryMgr_email, m.realloc, m.realloc_cdr_recordId, m.mfr_id, m.mfr_name, m.MFRAbbrev_Name, 'MFR' as sourceOfData, li.lastInvoiceDate, li.lastInvoiceCases, li.lastInvoiceLbs, li.lastInvoiceDollars, m.customerSKU, m.customerSKUType from monthly m with (nolock) left outer join #_lastInvoiceTableName# li with (nolock) on li.fsl_dstId = m.oneFsId and li.skuId = m.skuId and li.ownerId = m.ownerId and li.ownerType = m.ownerType ) insert into tbl_DW_DistributorSales#_TableSuffix# (#_etlColumns#) select #_etlColumns# from etl; with redistributors as ( select distinct #_redistributorColumnList# from tbl_DW_DistributorSales#_tableSuffix# with (nolock) where customerType = 'REDISTRIBUTOR' ) , reverse as ( select #_reverseColumnList#, cases * -1.0 as cases, lbs * -1.0 as lbs, dollars * -1.0 as dollars, alt * -1.0 as alt, 'Y' as realloc, cdr_recordId as realloc_cdr_recordId, redist_cdr_recordId from tbl_DW_DistributorSales#_tableSuffix# where cdr_dstCompanyType = 'S' and cdr_specialAcctType = 'RDA' and purchasingMethod = 'INDIRECT' ) , realloc as ( select ,rdc.#_c# ,r.#_c# ,r.cases,r.lbs,r.dollars,r.alt,r.realloc,r.realloc_cdr_recordId from reverse r inner join redistributors rdc on rdc.cdr_recordId = r.redist_cdr_recordId ) insert into tbl_DW_DistributorSales#_tableSuffix# (#_etlColumns# ) select #_etlColumns# from realloc

#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 select distinct ownerId, ownerType from fsenablers..tbl_APP_Configurations with (nolock) where applicationCode = '1FSBSR' and parameterName = 'apps.distributorSalesData.enabled' and parameterValue = 'true' and ownerType = and ownerId = select distinct bpl.ownerId as broker_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 nullif( mt.internalSalesId, '' ) = pt.mfrTerritoryNbr where bpl.ownerId > 0 and bpl.ownerType = 'BRO' and pt.hireStatus = 'A' and sma.mfr_id = and 'MFR' = and exists ( select * from fsenablers..tbl_APP_Configurations cfg with (nolock) where cfg.applicationCode = 'MOBILE' and cfg.parameterName = 'apps.opr.mfrSalesDataSyndication.enabled' and cfg.parameterValue = 'true' and cfg.ownerType = 'BRO' and cfg.ownerId = bpl.ownerId ) select ownerId, ownerType, parameterName, parameterValue from fsenablers..tbl_APP_Configurations with (nolock) where applicationCode = 'MOBILE' and parameterName = 'apps.opr.mfrSalesDataSyndication.enabled' and parameterValue = 'true' and ownerType = 'BRO' and ownerId > 0 drop table if exists #arguments.tableName#; drop table if exists #_buyingGroupsTableName#; create table #_buyingGroupsTableName# ( cdr_recordId int not null, fsl_dstId int not null, buyingGroupId int not null, buyingGroup varchar(75) not null, buyingGroupId2 int, buyingGroup2 varchar(75), buyingGroupId3 int, buyingGroup3 varchar(75), buyingGroupId4 int, buyingGroup4 varchar(75) ) alter table #_buyingGroupsTableName# add constraint PK_#_buyingGroupsTableName# primary key ( cdr_recordId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with bygMembershipsD as ( select distinct afl.affiliationDate, byg.uniqueId as buyingGroupId, byg.name as buyingGroup, cdr.fsl_dstId 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 and cdr.fsl_dstId > 0 ) , bygMemberships as ( select affiliationDate, buyingGroupId, buyingGroup, fsl_dstId, 'BYG' + cast( row_number() over ( partition by fsl_dstId order by affiliationDate, buyingGroup ) as varchar ) as bygSeq from bygMembershipsD with (nolock) ) , bygMembershipsP as ( select fsl_dstId, byg1 as buyingGroupId, byg2 as buyingGroupId2, byg3 as buyingGroupId3, byg4 as buyingGroupId4 from ( select fsl_dstId, bygSeq, buyingGroupId from bygMemberships ) s pivot( max( buyingGroupId ) for bygSeq in ( BYG1, BYG2, BYG3, BYG4 ) ) as p ) , buyingGroups as ( select m.fsl_dstId, m.buyingGroupId, b1.name as buyingGroup, m.buyingGroupId2, b2.name as buyingGroup2, m.buyingGroupId3, b3.name as buyingGroup3, m.buyingGroupId4, b4.name as buyingGroup4 from bygMembershipsP m with (nolock) left outer join tbl_BYG_BuyingGroups b1 with (nolock) on b1.uniqueId = m.buyingGroupId left outer join tbl_BYG_BuyingGroups b2 with (nolock) on b2.uniqueId = m.buyingGroupId2 left outer join tbl_BYG_BuyingGroups b3 with (nolock) on b3.uniqueId = m.buyingGroupId3 left outer join tbl_BYG_BuyingGroups b4 with (nolock) on b4.uniqueId = m.buyingGroupId4 ) insert into #_buyingGroupsTableName# ( cdr_recordId, fsl_dstId, buyingGroupId, buyingGroup, buyingGroupId2, buyingGroup2, buyingGroupId3, buyingGroup3, buyingGroupId4, buyingGroup4) select cdr.cdr_recordId, byg.fsl_dstId, byg.buyingGroupId, byg.buyingGroup, byg.buyingGroupId2, byg.buyingGroup2, byg.buyingGroupId3, byg.buyingGroup3, byg.buyingGroupId4, byg.buyingGroup4 from buyingGroups byg with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.fsl_dstId = byg.fsl_dstId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType drop table if exists #_lastInvoiceTableName#; create table #_lastInvoiceTableName# ( ownerType varchar(3) not null, ownerId int not null, fsl_dstId int not null, skuId int not null, lastInvoiceDate datetime not null, lastInvoiceCases float, lastInvoiceLbs float, lastInvoiceDollars float ) alter table #_lastInvoiceTableName# add constraint PK_#_lastInvoiceTableName# primary key ( ownerType, ownerId, fsl_dstId, skuId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with invoiceHistory as ( select ih.ownerId, ih.ownerType, 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.fsl_dstId > 0 and cdr.cdr_recordId = ih.cdr_recordId where ih.ownerId = @ownerId and ih.ownerType = @ownerType and ( ih.caseAmt != 0 or ih.weightAmt != 0 or ih.dollarAmt != 0 ) ), lastInvoice as ( select ownerId, ownerType, fsl_dstId, skuId, invoiceDate as lastInvoiceDate, cases as lastInvoiceCases, lbs as lastInvoiceLbs, dollars as lastInvoiceDollars from invoiceHistory where historyRank = 1 ) insert into #_lastInvoiceTableName# ( ownerId, ownerType, fsl_dstId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars ) select ownerId, ownerType, fsl_dstId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars from lastInvoice drop table if exists #_budgetCategoryManagersTableName#; create table #_budgetCategoryManagersTableName# ( ownerId int not null, ownerType varchar(3) not null, territoryId int not null, budgetCategoryId int not null, budgetCategoryMgr_fspro_userId int, budgetCategoryMgr_firstName varchar(50) null, budgetCategoryMgr_lastName varchar(50) null, budgetCategoryMgr_email varchar(75) null ) alter table #_budgetCategoryManagersTableName# add constraint PK_#_budgetCategoryManagersTableName# primary key ( ownerType, ownerId, territoryId, budgetCategoryId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with budgetCategories as ( select distinct ownerId, ownerType, budgetCategoryId, budgetCategory from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where budgetCategoryId > 0 ), territoryBudgetCategoryManagers as ( select ter.territoryId, ter.name as territoryName, nullif( ter.parentTerritoryId, 0 ) parentTerritoryId, bc.ownerType, bc.ownerId, bc.budgetCategoryId, bc.budgetCategory, bcm.budgetCategoryManagerId, bcm.fspro_userId from budgetCategories bc inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = bc.ownerId and ter.fsl_tablecode = bc.ownerType left outer join tbl_TER_BudgetCategoryManagers bcm with (nolock) on bcm.ownerId = bc.ownerId and bcm.ownerType = bc.ownertype and bcm.budgetCategoryId = bc.budgetCategoryId and bcm.territoryId = ter.territoryId ), budgetCategoryManagersRaw as ( select ownerId, ownerType, territoryId, territoryName, budgetCategoryId, budgetCategory, fspro_userId from territoryBudgetCategoryManagers where parentTerritoryId is null union all select t.ownerId, t.ownerType, t.territoryId, t.territoryName, t.budgetCategoryId, t.budgetCategory, coalesce( t.fspro_userId, bcm.fspro_userId ) fspro_userId from budgetCategoryManagersRaw bcm inner join territoryBudgetCategoryManagers t on t.parentTerritoryId = bcm.territoryId and t.budgetCategoryId = bcm.budgetCategoryId ), budgetCategoryManagers as ( select bcm.ownerId, bcm.ownerType, bcm.territoryId, bcm.budgetCategoryId, bcm.fspro_userId as budgetCategoryMgr_fspro_userId, m.firstName as budgetCategoryMgr_firstName, m.lastName as budgetCategoryMgr_lastName, m.email as budgetCategoryMgr_email from budgetCategoryManagersRaw bcm left outer join tbl_FSPro_members m with (nolock) on m.fspro_userId = bcm.fspro_userId ) insert into #_budgetCategoryManagersTableName# ( ownerId, ownerType, territoryId, budgetCategoryId, budgetCategoryMgr_fspro_userId, budgetCategoryMgr_firstName, budgetCategoryMgr_lastName, budgetCategoryMgr_email ) select ownerId, ownerType, territoryId, budgetCategoryId, budgetCategoryMgr_fspro_userId, budgetCategoryMgr_firstName, budgetCategoryMgr_lastName, budgetCategoryMgr_email from budgetCategoryManagers drop table if exists [#_tpfTempTable#]; select ph.mfr_id, ph.forecast_status, ph.tPartnerType, ph.tPartnerId, ph.forecast_year, ph.forecast_month, ph.skuId, ph.forecast_qty, ph.forecast_amt, ph.forecast_alt into [#_tpfTempTable#] from tbl_MFR_TPF_Detail#_tableSuffix# ph with (nolock) where ph.forecast_status = 'A' and ph.tPartnerType = 'CDR'; alter table [#_tpfTempTable#] alter column skuId int not null; alter table [#_tpfTempTable#] add constraint [PK_#_tpfTempTable#] primary key ( mfr_id, forecast_status, tpartnerType, tPartnerId, forecast_year, forecast_month, skuId ); drop table if exists [#_tpfTableName#]; create table #_tpfTableName# ( mfr_id int not null, forecast_status char(1) not null, tPartnerType varchar(3) not null, tPartnerId int not null, forecast_year varchar(4) not null, forecast_month varchar(2) not null, skuId int not null, forecast_qty decimal( 18, 2) not null, forecast_amt decimal( 20, 2 ) not null, forecast_alt float null, active_pipeline_cases float null, active_pipeline_lbs float null, active_pipeline_dollars float null, sold_pipeline_cases float null, sold_pipeline_lbs float null, sold_pipeline_dollars float null, source varchar(3) null ); alter table #_tpfTableName# add constraint PK_#_tpfTableName# primary key ( mfr_id, forecast_status, tpartnerType, tPartnerId, forecast_year, forecast_month, skuId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with tpf as ( select ph.mfr_id, ph.forecast_status, ph.tPartnerType, ph.tPartnerId, ph.forecast_year, ph.forecast_month, ph.skuId, ph.forecast_qty, ph.forecast_amt, ph.forecast_alt, p.sold_pipeline_cases, p.sold_pipeline_lbs, p.sold_pipeline_dollars, p.active_pipeline_cases, p.active_pipeline_lbs, p.active_pipeline_dollars from [#_tpfTempTable#] ph with (nolock) left outer join [#_operatorOpportunityImpactTable#] p on p.tPartnerId = ph.tPartnerId and p.tPartnerType = ph.tPartnerType and p.forecast_month = ph.forecast_month and p.forecast_year = ph.forecast_year and p.skuId = ph.skuId and p.mfr_id = ph.mfr_id ) insert into #_tpfTableName# ( mfr_id, forecast_status, tPartnerType, tPartnerId, forecast_year, forecast_month, skuId, forecast_qty, forecast_amt, forecast_alt , sold_pipeline_cases, sold_pipeline_lbs, sold_pipeline_dollars , active_pipeline_cases, active_pipeline_lbs, active_pipeline_dollars , source ) select mfr_id, forecast_status, tPartnerType, tPartnerId, forecast_year, forecast_month, skuId, sum( forecast_qty ) forecast_qty, sum( forecast_amt ) forecast_amt, sum( forecast_alt ) forecast_alt, sum( sold_pipeline_cases ) sold_pipeline_cases, sum( sold_pipeline_lbs ) sold_pipeline_lbs, sum( sold_pipeline_dollars ) sold_pipeline_dollars, sum( active_pipeline_cases ) active_pipeline_cases, sum( active_pipeline_lbs ) active_pipeline_lbs, sum( active_pipeline_dollars ) active_pipeline_dollars, 'TPF' as source from tpf group by mfr_id, forecast_status, tPartnerType, tPartnerId, forecast_year, forecast_month, skuId; insert into [#_tpfTableName#] ( mfr_id, forecast_status, tPartnerType, tPartnerId, forecast_year, forecast_month, skuId, forecast_qty, forecast_amt, forecast_alt , sold_pipeline_cases, sold_pipeline_lbs, sold_pipeline_dollars , active_pipeline_cases, active_pipeline_lbs, active_pipeline_dollars , source ) select p.mfr_id, p.forecast_status, p.tPartnerType, p.tPartnerId, p.forecast_year, p.forecast_month, p.skuId, 0 forecast_qty, 0 forecast_amt, 0 forecast_alt, p.sold_pipeline_cases, p.sold_pipeline_lbs, p.sold_pipeline_dollars, p.active_pipeline_cases, p.active_pipeline_lbs, p.active_pipeline_dollars, 'OPP' as source from [#_operatorOpportunityImpactTable#] p where not exists ( select * from [#_tpfTableName#] tpf where tpf.mfr_id = p.mfr_id and tpf.skuId = p.skuId and tpf.tPartnerId = p.tPartnerId and tpf.tPartnerType = p.tPartnerType and tpf.forecast_year = p.forecast_year and tpf.forecast_month = p.forecast_month ) drop table if exists [#_tpfTempTable#]; drop table if exists [#_operatorOpportunityImpactTable#]; drop table if exists #_distributorsTableName#; create table #_distributorsTableName# ( fsl_tablecode varchar(3) not null, ownerId int not null, sales_cdr_recordId int not null, sales_cdr_dstName varchar(125), cdr_recordId int, cdr_dstName varchar(125), cdr_dstAddress1 varchar(75), cdr_dstCity varchar(50), cdr_dstState varchar(3), cdr_dstZip varchar(10), cdr_dstPath varchar(251), accountOwner_fspro_userId int, customerType varchar(25), fsl_dstId int, cdr_dstCompanyType char(1), cdr_redistAcctFor int, cdr_specialAcctType varchar(3), cdr_dstCode varchar(30), cdr_territoryId int, classificationId int, classification varchar(25) ) alter table #_distributorsTableName# add constraint PK_#_distributorsTableName# primary key ( fsl_tablecode, ownerId, sales_cdr_recordId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with mappedDistributors as ( select mcdr.cdr_recordId, mcdr.ownerId, mcdr.fsl_tablecode, mcdr.cdr_dstName, mcdr.cdr_dstCompanyType, mcdr.fsl_dstId, mcdr.cdr_dstAddress1, mcdr.cdr_dstCity, mcdr.cdr_dstState, mcdr.cdr_dstZip, mcdr.mfr_bsr_id, mcdr.cdr_dstPath, mcdr.cdr_territoryId, row_number() over( partition by mcdr.fsl_dstId order by mcdr.fsl_mapPriority ) as fsl_mapPriority, cls.classificationId, cls.name as classification from tbl_CDR_Distributors mcdr with (nolock) left outer join tbl_CDR_Classifications cls with (nolock) on cls.ownerId = mcdr.ownerId and cls.ownerType = mcdr.fsl_tablecode and cls.classificationId = mcdr.classificationId where mcdr.ownerId = @ownerId and mcdr.fsl_tablecode = @ownerType and mcdr.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_raw, 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, case when mcdr.cdr_recordId is not null then mcdr.classificationId else cls.classificationId end as classificationId, case when mcdr.cdr_recordId is not null then mcdr.classification else cls.name end as classification from tbl_CDR_Distributors cdr left outer join tbl_CDR_Classifications cls with (nolock) on cls.ownerId = cdr.ownerId and cls.ownerType = cdr.fsl_tablecode and cls.classificationId = cdr.classificationId left outer 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.fsl_dstId > 0 where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ) , branches as ( select d.cdr_recordId, d.fsl_dstId, d.cdr_dstCompanyType, d.accountOwner_fspro_userId_raw, row_number() over( partition by d.fsl_dstId order by d.cdr_recordId ) as branchSeq from distributorsRaw d with (nolock) where d.cdr_dstCompanyType = 'B' ) , distributors as ( select d.*, coalesce( nullif( rd.accountOwner_fspro_userId_raw, 0 ), d.accountOwner_fspro_userId_raw ) as accountOwner_fspro_userId from distributorsRaw d with (nolock) left outer join branches rd with (nolock) on rd.fsl_dstId = d.fsl_dstId and rd.cdr_dstCompanyType = 'B' and 'RDA' = d.cdr_specialAcctType and rd.branchSeq = 1 and rd.fsl_dstId > 0 ) insert into #_distributorsTableName# ( ownerId, fsl_tablecode, sales_cdr_recordId, sales_cdr_dstName, cdr_recordId, cdr_dstName, cdr_dstAddress1, cdr_dstCity, cdr_dstState, cdr_dstZip, cdr_dstPath, accountOwner_fspro_userId, customerType, fsl_dstId, cdr_dstCompanyType, cdr_redistAcctFor, cdr_specialAcctType, cdr_dstCode, cdr_territoryId, classificationId, classification ) select ownerId, fsl_tablecode, sales_cdr_recordId, sales_cdr_dstName, cdr_recordId, cdr_dstName, cdr_dstAddress1, cdr_dstCity, cdr_dstState, cdr_dstZip, cdr_dstPath, accountOwner_fspro_userId, customerType, fsl_dstId, cdr_dstCompanyType, cdr_redistAcctFor, cdr_specialAcctType, cdr_dstCode, cdr_territoryId, classificationId, classification from distributors