declare @startMonth date = '2024-10-01' ;
        
        declare @endMonth date = '2024-10-01' ;
        

        delete from tbl_DW_DistributorSales_BRO387
            where mfr_id = 96 
                and ( salesPeriodType = 'MONTH' and salesPeriod >= @startMonth )
            
                and ( salesPeriodType = 'MONTH' and salesPeriod <= @endMonth )

declare @ownerId int = 387 ;
            declare @ownerType varchar(3) = 'BRO' ;
            declare @mfrId int = 96 ;

            
            declare @startMonth date = '2024-10-01' ;
            
            declare @endMonth date = '2024-10-01' ;
            

           

            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_BRO387 ph with (nolock)
                 where ph.mfr_id = @mfrId

            ),

			
            mfrSales as (

                select cdr_recordId, skuId, salesPeriod, salesPeriodType, skuLastInvoiceDate lastInvoiceDate, skuLastInvoiceCases lastInvoiceCases, skuLastInvoiceLbs lastInvoiceLbs, skuLastInvoiceDollars lastInvoiceDollars, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars, sum( commission ) commission, sum( alt ) alt 
                from tbl_CDR_FoodmarkOrderData_BRO387 fd with (nolock)
                where fd.salesPeriodType = 'MONTH'
                
                    and ( salesPeriod >= @startMonth )
                
                    and ( salesPeriod <= @endMonth )
                

                group by cdr_recordId, skuId, salesPeriod, salesPeriodType, skuLastInvoiceDate, skuLastInvoiceCases, skuLastInvoiceLbs, skuLastInvoiceDollars

            ),

            etl as (

                select 387 ownerId, 'BRO' ownerType, sales.salesPeriod, sales.salesPeriodType, sales.cases, sales.lbs, sales.dollars, sales.commission, 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,
                       'BRO' sourceOfData, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars
            from mfrSales sales
            inner join products ph
                on ph.skuId = sales.skuId
            inner join distributors cdr
               on cdr.sales_cdr_recordId = sales.cdr_recordId
            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_BRO387 ( 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,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,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,commission )
            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,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,mfr_id,mfr_name,MFRAbbrev_Name,sourceOfData,lastInvoiceDate,lastInvoiceCases,lastInvoiceLbs,lastInvoiceDollars,commission
              from etl

declare @mfrId int = ?;

    with lastInvoiceRanked as (
    select oneFsId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars, row_number() over( partition by oneFsId, skuId order by lastInvoiceDate desc ) lastInvoiceRank
    from tbl_DW_DistributorSales_BRO387 with (nolock)
    where mfr_id = @mfrId
    and lastInvoiceDate is not null
    ),

    lastInvoice as (
    select oneFsId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars
    from lastInvoiceRanked
    where lastInvoiceRank = 1
    )

    update dw
        set dw.lastInvoiceDate = li.lastInvoiceDate,
            dw.lastInvoiceCases = li.lastInvoiceCases,
            dw.lastInvoiceLbs = li.lastInvoiceLbs,
            dw.lastInvoiceDollars = li.lastInvoiceDollars
        from tbl_DW_DistributorSales_BRO387 as dw
        inner join lastInvoice li
        on li.oneFsId = dw.oneFsId
        and li.skuId = dw.skuId
        where dw.lastInvoiceDate is null
        or dw.lastInvoiceDate != li.lastInvoiceDate

declare @mfrId int = ?;

        with lastInvoice as (
        select distinct skuId, oneFsId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars
        from tbl_DW_DistributorSales_BRO387 with (nolock)
        where lastInvoiceDate is not null
            and mfr_id = @mfrId
        )
        
        update o
            set o.distributorLastInvoiceDate = li.lastInvoiceDate,
                o.distributorLastInvoiceCases = li.lastInvoiceCases,
                o.distributorLastInvoiceLbs = li.lastInvoiceLbs,
                o.distributorLastInvoiceDollars = li.lastInvoiceDollars
        from tbl_DW_Opportunities_BRO387 o
        inner join lastInvoice li
            on li.skuId = o.skuId
            and li.oneFsId = o.distributorOneFSId
        where o.distributorLastInvoiceDate is null
        or o.distributorLastInvoiceDate != li.lastInvoiceDate

declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;
    declare @mfrId int = 96 ;
    declare @volumeThreshold int = 1 ;
    declare @windowMonths int = 6 ;
    declare @lastSalesPeriod datetime = ( select max( salesPeriod ) from tbl_DW_DistributorSales_BRO387 where salesPeriodType = 'MONTH' and mfr_id = @mfrId );

    with stockingWindowVolume as (
        select s.oneFsId, s.skuId, min( s.salesPeriod ) firstSalesPeriod, max( s.salesPeriod ) lastSalesPeriod, sum( case when datediff( month, s.salesPeriod, @lastSalesPeriod ) between 0 and ( @windowMonths - 1) then s.cases else 0 end ) cases 
          from tbl_DW_DistributorSales_BRO387 s
         where s.salesPeriodType = 'MONTH'
           and s.oneFsId > 0
        
           and s.mfr_id = @mfrId
        
         group by s.oneFsId, s.skuId
    )

    , newStockingStatus as ( 
        select cdr.cdr_recordId, swv.skuId, swv.lastSalesPeriod lastPurchaseMonth, case when swv.cases >= @volumeThreshold then 'Y' else 'N' end stocking
        from stockingWindowVolume swv
        inner join tbl_CDR_Distributors cdr with (nolock)
         on cdr.fsl_dstId = swv.oneFSId
         and cdr.ownerId = @ownerId
         and cdr.fsl_tablecode = @ownerType
    )

    , currentStockingStatus as (

        select distinct ss.cdr_recordId, ss.skuId, ss.stockingStatus
         from tbl_CDR_Stocking ss with (nolock)
         
        inner join tbl_DW_ProductHierarchy_BRO387 ph with (nolock)
            on ph.ownerId = ss.ownerId
            and ph.ownerType =ss.ownerType
            and ph.skuId = ss.skuId
            and ph.mfr_id = @mfrId
        
        where ss.ownerId = @ownerId
            and ss.ownerType= @ownerType
        
    )


    , stockingCompare as (
    select coalesce( css.cdr_recordId, nss.cdr_recordId ) as cdr_recordId, coalesce(css.skuId, nss.skuId ) skuId, coalesce( css.stockingStatus, 'n' ) currentStockingStatus, coalesce( nss.stocking, 'n' ) as newStockingStatus
    from currentStockingStatus css with (nolock) 
    full outer join newStockingStatus nss
        on nss.cdr_recordId= css.cdr_recordId
        and nss.skuId = css.skuId
    )


    select cdr.cdr_recordId, cdr.cdr_dstName, s.skuId, s.sku, s.skuDesc, currentStockingStatus, newStockingStatus 
    from stockingCompare sc 
    inner join tbl_CDR_Distributors cdr with(nolock) 
    on cdr.cdr_recordId = sc.cdr_recordId
    and cdr.cdr_dstCompanyType = 'B'
    and cdr.ownerId = 387 
    and cdr.fsl_tablecode = 'BRO' 
    inner join tbl_PRD_SKus s
        on s.skuId= sc.skuId
        and s.ownerId = 387 
        and s.fsl_tablecode = 'BRO' 
    where newStockingStatus != currentStockingStatus

declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;

    with lastInvoice as (
        select oneFsId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars, row_number() over( partition by oneFsId, skuId order by lastInvoiceDate desc ) as lastInvoiceRank
        from tbl_DW_DistributorSales_BRO387 with (nolock)
        where lastInvoiceDate is not null
    )

    update s
    set s.lastInvoiceDate = l.lastInvoiceDate,
        s.lastInvoiceCases = l.lastInvoiceCases,
        s.lastInvoiceDollars = l.lastInvoiceDollars,
        s.lastInvoiceLbs = l.lastInvoiceLbs
    from tbl_CDR_Stocking s
    inner join tbl_CDR_Distributors cdr with (nolock)
        on cdr.ownerId = s.ownerId
        and cdr.fsl_tablecode = s.ownerType
        and cdr.cdr_recordId = s.cdr_recordId
        and cdr.fsl_dstId > 0
    inner join lastInvoice l with (nolock)
        on l.skuId = s.skuId
        and l.oneFsId = cdr.fsl_dstId
        and l.lastInvoiceRank = 1
    where s.ownerId = @ownerId
    and s.ownerType = @ownerType
    and coalesce( s.lastInvoiceDate, dateFromParts( 1966, 1, 1 )) != l.lastInvoiceDate

declare @ownerId int = 387 ;
    declare @ownerType varchar(3) = 'BRO' ;

    with lastInvoice as (
        select cdr_recordId, skuId, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars, row_number() over( partition by cdr_recordId, skuId order by lastInvoiceDate desc ) as lastInvoiceRank
        from tbl_DW_DistributorSales_BRO387 with (nolock)
        where lastInvoiceDate is not null
    )

    update s
    set s.lastInvoiceDate = l.lastInvoiceDate,
        s.lastInvoiceCases = l.lastInvoiceCases,
        s.lastInvoiceDollars = l.lastInvoiceDollars,
        s.lastInvoiceLbs = l.lastInvoiceLbs
    from tbl_CDR_Stocking s
    inner join tbl_CDR_Distributors cdr with (nolock)
        on cdr.ownerId = s.ownerId
        and cdr.fsl_tablecode = s.ownerType
        and cdr.cdr_recordId = s.cdr_recordId
        and cdr.fsl_dstId = 0
    inner join lastInvoice l with (nolock)
        on l.skuId = s.skuId
        and l.cdr_recordId = cdr.cdr_recordId
        and l.lastInvoiceRank = 1
    where s.ownerId = @ownerId
    and s.ownerType = @ownerType
    and coalesce( s.lastInvoiceDate, dateFromParts( 1966, 1, 1 )) != l.lastInvoiceDate

INITIALIZE_IMPORT_TABLE

The following SKUs were skipped as duplicates exist: