declare @ownerId int = 2074 ;
declare @ownerType varchar(3) = 'MFR' ;

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_MFR2074 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 tmp_distributorSalesETL_tpf_12102746 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 tmp_distributorSalesETL_distributors_12102746 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 tmp_distributorSalesETL_buyingGroups_12102746 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 tmp_distributorSalesETL_budgetCategoryManagers_12102746 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 tmp_distributorSalesETL_lastInvoice_12102746 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_MFR2074 (ownerId,ownerType,cdr_recordId,oneFsId,cdr_dstCode,cdr_dstName,cdr_dstCompanyType,territoryId,territoryName,territoryPath,parent_cdr_recordId,parent_oneFsId,parent_cdr_dstCode,parent_cdr_dstName,parent_cdr_dstCompanyType,skuId,sku,skuDesc,prodId,product,shortDesc,categoryId,catNum,category,prodline_id,productLineCode,productLine,budgetCategoryId,budgetCategory,brand,brandId,salesPeriod,salesPeriodType,cases,lbs,dollars,alt,cdr_specialAcctType,redist_cdr_dstName,redist_cdr_recordId,redist_oneFsId,buyingGroupId,buyingGroup,purchasingMethod,customerType,discontinued,fiscalYear,fiscalMonth,cdr_dstPath,regionName,divisionName,accountOwner_email,accountOwner_fspro_userId,accountOwner_firstName,accountOwner_lastName,cdr_dstAddress1,cdr_dstCity,cdr_dstState,cdr_dstZip,privateLabelType,focusProduct,productHierarchyPath,budgetCategoryMgr_fspro_userId,budgetCategoryMgr_email,budgetCategoryMgr_firstName,budgetCategoryMgr_lastName,realloc,realloc_cdr_recordId,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,classificationId,classification,commission,buyingGroupId2,buyingGroup2,buyingGroupId3,buyingGroup3,accountOwner_ownerId,accountOwner_ownerType,territoryNbr,productSetTags,active_pipeline_cases,active_pipeline_lbs,active_pipeline_dollars,sold_pipeline_cases,sold_pipeline_lbs,sold_pipeline_dollars,l2ProductHierarchyPath,l3ProductHierarchyPath,l4ProductHierarchyPath,customerSKU,customerSKUType)
select ownerId,ownerType,cdr_recordId,oneFsId,cdr_dstCode,cdr_dstName,cdr_dstCompanyType,territoryId,territoryName,territoryPath,parent_cdr_recordId,parent_oneFsId,parent_cdr_dstCode,parent_cdr_dstName,parent_cdr_dstCompanyType,skuId,sku,skuDesc,prodId,product,shortDesc,categoryId,catNum,category,prodline_id,productLineCode,productLine,budgetCategoryId,budgetCategory,brand,brandId,salesPeriod,salesPeriodType,cases,lbs,dollars,alt,cdr_specialAcctType,redist_cdr_dstName,redist_cdr_recordId,redist_oneFsId,buyingGroupId,buyingGroup,purchasingMethod,customerType,discontinued,fiscalYear,fiscalMonth,cdr_dstPath,regionName,divisionName,accountOwner_email,accountOwner_fspro_userId,accountOwner_firstName,accountOwner_lastName,cdr_dstAddress1,cdr_dstCity,cdr_dstState,cdr_dstZip,privateLabelType,focusProduct,productHierarchyPath,budgetCategoryMgr_fspro_userId,budgetCategoryMgr_email,budgetCategoryMgr_firstName,budgetCategoryMgr_lastName,realloc,realloc_cdr_recordId,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,classificationId,classification,commission,buyingGroupId2,buyingGroup2,buyingGroupId3,buyingGroup3,accountOwner_ownerId,accountOwner_ownerType,territoryNbr,productSetTags,active_pipeline_cases,active_pipeline_lbs,active_pipeline_dollars,sold_pipeline_cases,sold_pipeline_lbs,sold_pipeline_dollars,l2ProductHierarchyPath,l3ProductHierarchyPath,l4ProductHierarchyPath,customerSKU,customerSKUType
 from etl;



	with redistributors as (

	select distinct ownerId,ownerType,cdr_recordId,cdr_dstName,oneFsId,cdr_dstCode,cdr_dstCompanyType,territoryId,territoryName,territoryPath,parent_cdr_recordId,parent_oneFsId,parent_cdr_dstCode,parent_cdr_dstName,parent_cdr_dstCompanyType,cdr_specialAcctType,redist_cdr_dstName,redist_cdr_recordId,redist_oneFsId,buyingGroupId,buyingGroup,buyingGroupId2,buyingGroup2,buyingGroupId3,buyingGroup3,customerType,cdr_dstPath,regionName,divisionName,accountOwner_email,accountOwner_fspro_userId,accountOwner_firstName,accountOwner_lastName,cdr_dstAddress1,cdr_dstCity,cdr_dstState,cdr_dstZip,customerSKU,customerSKUType 
		from tbl_DW_DistributorSales_MFR2074  with (nolock)
		where customerType = 'REDISTRIBUTOR'
	)

	

	, reverse as (

		select skuId,sku,skuDesc,prodId,product,shortDesc,categoryId,catNum,category,prodline_id,productLineCode,productLine,budgetCategoryId,budgetCategory,brand,brandId,salesPeriod,salesPeriodType,purchasingMethod,discontinued,fiscalYear,fiscalMonth,privateLabelType,focusProduct,productHierarchyPath,budgetCategoryMgr_fspro_userId,budgetCategoryMgr_email,budgetCategoryMgr_firstName,budgetCategoryMgr_lastName,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,classificationId,classification,commission,accountOwner_ownerId,accountOwner_ownerType,territoryNbr,productSetTags,active_pipeline_cases,active_pipeline_lbs,active_pipeline_dollars,sold_pipeline_cases,sold_pipeline_lbs,sold_pipeline_dollars,l2ProductHierarchyPath,l3ProductHierarchyPath,l4ProductHierarchyPath, 
			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_MFR2074 
		where cdr_dstCompanyType = 'S' 
			and cdr_specialAcctType = 'RDA'
			and purchasingMethod = 'INDIRECT'


	)
	, realloc as (

		select 
			rdc.ownerId,rdc.ownerType,rdc.cdr_recordId,rdc.cdr_dstName,rdc.oneFsId,rdc.cdr_dstCode,rdc.cdr_dstCompanyType,rdc.territoryId,rdc.territoryName,rdc.territoryPath,rdc.parent_cdr_recordId,rdc.parent_oneFsId,rdc.parent_cdr_dstCode,rdc.parent_cdr_dstName,rdc.parent_cdr_dstCompanyType,rdc.cdr_specialAcctType,rdc.redist_cdr_dstName,rdc.redist_cdr_recordId,rdc.redist_oneFsId,rdc.buyingGroupId,rdc.buyingGroup,rdc.buyingGroupId2,rdc.buyingGroup2,rdc.buyingGroupId3,rdc.buyingGroup3,rdc.customerType,rdc.cdr_dstPath,rdc.regionName,rdc.divisionName,rdc.accountOwner_email,rdc.accountOwner_fspro_userId,rdc.accountOwner_firstName,rdc.accountOwner_lastName,rdc.cdr_dstAddress1,rdc.cdr_dstCity,rdc.cdr_dstState,rdc.cdr_dstZip,rdc.customerSKU,rdc.customerSKUType ,r.skuId,r.sku,r.skuDesc,r.prodId,r.product,r.shortDesc,r.categoryId,r.catNum,r.category,r.prodline_id,r.productLineCode,r.productLine,r.budgetCategoryId,r.budgetCategory,r.brand,r.brandId,r.salesPeriod,r.salesPeriodType,r.purchasingMethod,r.discontinued,r.fiscalYear,r.fiscalMonth,r.privateLabelType,r.focusProduct,r.productHierarchyPath,r.budgetCategoryMgr_fspro_userId,r.budgetCategoryMgr_email,r.budgetCategoryMgr_firstName,r.budgetCategoryMgr_lastName,r.mfr_id,r.mfr_name,r.MFRAbbrev_Name,r.sourceOfData,r.lastInvoiceDate,r.lastInvoiceCases,r.lastInvoiceLbs,r.lastInvoiceDollars,r.classificationId,r.classification,r.commission,r.accountOwner_ownerId,r.accountOwner_ownerType,r.territoryNbr,r.productSetTags,r.active_pipeline_cases,r.active_pipeline_lbs,r.active_pipeline_dollars,r.sold_pipeline_cases,r.sold_pipeline_lbs,r.sold_pipeline_dollars,r.l2ProductHierarchyPath,r.l3ProductHierarchyPath,r.l4ProductHierarchyPath
			,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_MFR2074 (ownerId,ownerType,cdr_recordId,oneFsId,cdr_dstCode,cdr_dstName,cdr_dstCompanyType,territoryId,territoryName,territoryPath,parent_cdr_recordId,parent_oneFsId,parent_cdr_dstCode,parent_cdr_dstName,parent_cdr_dstCompanyType,skuId,sku,skuDesc,prodId,product,shortDesc,categoryId,catNum,category,prodline_id,productLineCode,productLine,budgetCategoryId,budgetCategory,brand,brandId,salesPeriod,salesPeriodType,cases,lbs,dollars,alt,cdr_specialAcctType,redist_cdr_dstName,redist_cdr_recordId,redist_oneFsId,buyingGroupId,buyingGroup,purchasingMethod,customerType,discontinued,fiscalYear,fiscalMonth,cdr_dstPath,regionName,divisionName,accountOwner_email,accountOwner_fspro_userId,accountOwner_firstName,accountOwner_lastName,cdr_dstAddress1,cdr_dstCity,cdr_dstState,cdr_dstZip,privateLabelType,focusProduct,productHierarchyPath,budgetCategoryMgr_fspro_userId,budgetCategoryMgr_email,budgetCategoryMgr_firstName,budgetCategoryMgr_lastName,realloc,realloc_cdr_recordId,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,classificationId,classification,commission,buyingGroupId2,buyingGroup2,buyingGroupId3,buyingGroup3,accountOwner_ownerId,accountOwner_ownerType,territoryNbr,productSetTags,active_pipeline_cases,active_pipeline_lbs,active_pipeline_dollars,sold_pipeline_cases,sold_pipeline_lbs,sold_pipeline_dollars,l2ProductHierarchyPath,l3ProductHierarchyPath,l4ProductHierarchyPath,customerSKU,customerSKUType )
	select ownerId,ownerType,cdr_recordId,oneFsId,cdr_dstCode,cdr_dstName,cdr_dstCompanyType,territoryId,territoryName,territoryPath,parent_cdr_recordId,parent_oneFsId,parent_cdr_dstCode,parent_cdr_dstName,parent_cdr_dstCompanyType,skuId,sku,skuDesc,prodId,product,shortDesc,categoryId,catNum,category,prodline_id,productLineCode,productLine,budgetCategoryId,budgetCategory,brand,brandId,salesPeriod,salesPeriodType,cases,lbs,dollars,alt,cdr_specialAcctType,redist_cdr_dstName,redist_cdr_recordId,redist_oneFsId,buyingGroupId,buyingGroup,purchasingMethod,customerType,discontinued,fiscalYear,fiscalMonth,cdr_dstPath,regionName,divisionName,accountOwner_email,accountOwner_fspro_userId,accountOwner_firstName,accountOwner_lastName,cdr_dstAddress1,cdr_dstCity,cdr_dstState,cdr_dstZip,privateLabelType,focusProduct,productHierarchyPath,budgetCategoryMgr_fspro_userId,budgetCategoryMgr_email,budgetCategoryMgr_firstName,budgetCategoryMgr_lastName,realloc,realloc_cdr_recordId,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,classificationId,classification,commission,buyingGroupId2,buyingGroup2,buyingGroupId3,buyingGroup3,accountOwner_ownerId,accountOwner_ownerType,territoryNbr,productSetTags,active_pipeline_cases,active_pipeline_lbs,active_pipeline_dollars,sold_pipeline_cases,sold_pipeline_lbs,sold_pipeline_dollars,l2ProductHierarchyPath,l3ProductHierarchyPath,l4ProductHierarchyPath,customerSKU,customerSKUType
		from realloc