BROKERID [empty string] BUDGETCAT [empty string] CALENDARTYPE d CHART bgt DATASOURCE fslibrary_rpc DATEOP TY,Fiscal ENDDATE [empty string] EXCLUDEOPR YES INCLUDEALLPRODUCTS YES INCLUDEDIRECTSHIPOPERATORS NO INCLUDEFPONLY NO INCLUDEMARKETVOIDS YES INCLUDESIBLINGVOIDS YES INCLUDETRADE YES OWNERID 1382 OWNERTYPE MFR SORTBY vol STARTDATE [empty string] TERRITORYID [empty string] TOPGAIN 5 TRADECLAIMSSORTBY segment VARIABLE _pdfResult includeDistributors true includeOperatorPrivateLabel false includeRedistribution true ---> select distinct dst.dstId, dst.dstCompanyType from tbl_CDR_DIstributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = select distinct fsl_dstId from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and ( cdr.cdr_dstCompanyType = 'B' or ( cdr.cdr_dstCompanyType = 'S' and cdr.cdr_specialAcctType = 'RDA' )) and cdr.cdr_territoryId in ( ) and fsl_dstId > 0 with distributors as ( select distinct dstId from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and #_col# in ( ) ), operatorPurchases as ( select 1 allocationRank, '#attributes.allocationType#' as allocationType, coalesce( oa.#_allocationIdColumn#, 0 ) as allocationId, coalesce( oa.#_allocationColumn#, 'Unknown' ) allocationName, oa.purchasingMonth, sum( oa.forecast_qty ) forecast_qty, case when oa.purchasingMonth >= then 'P1' else 'P0' end as periodCode ,oa.sku, oa.skuDesc, oa.product, oa.shortDesc, oa.catnum, oa.category, oa.productLineCode, oa.productLine, oa.budgetCategory, oa.territoryName, oa.dstName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join distributors d on d.dstId = oa.dstId where oa.ownerId = and oa.ownerType = and oa.skuId in ( ) and oa.prodId in ( ) and oa.categoryId in ( ) and oa.prodLine_id in ( ) and oa.budgetCategoryId in ( ) and exists ( select * from tbl_DW_DistributorAnalyzer#_tableSuffix# da with (nolock) where da.skuId = oa.skuId and da.purchasingMonth = oa.purchasingMonth and da.dstId = oa.dstId ) and (( oa.locationLevelAgreementOwner = 1 and oa.cho_id != oa.agreementOwnerChoId ) or oa.locationLevelAgreementOwner = 0 ) and oa.requestId is not null and ( oa.purchasingMonth between and or oa.purchasingMonth between and ) group by oa.#_allocationIdColumn#, oa.#_allocationColumn#, oa.purchasingMonth ,oa.sku, oa.skuDesc, oa.product, oa.shortDesc, oa.catnum, oa.category, oa.productLineCode, oa.productLine, oa.budgetCategory, oa.territoryName, oa.dstName ), distributorSales as ( select 0 allocationRank, 'streetBusiness' as allocationType, 0 as allocationId, 'STREET BUSINESS' as allocationName, da.purchasingMonth, sum( da.forecast_qty ) forecast_qty, case when da.purchasingMonth >= then 'P1' else 'P0' end as periodCode ,da.sku, da.skuDesc, da.product, da.shortDesc, da.catnum, da.category, da.productLineCode, da.productLine, da.budgetCategory, da.territoryName, da.dstName from tbl_DW_DistributorAnalyzer#_tableSuffix# da with (nolock) inner join distributors as d on d.dstId = da.dstId where da.ownerId = and da.ownerType = and da.skuId in ( ) and da.prodId in ( ) and da.categoryId in ( ) and da.prodLine_id in ( ) and da.budgetCategoryId in ( ) and ( da.purchasingMonth between and or da.purchasingMonth between and ) group by da.purchasingMonth ,da.sku, da.skuDesc, da.product, da.shortDesc, da.catnum, da.category, da.productLineCode, da.productLine, da.budgetCategory, da.territoryName, da.dstName ), adjDistributorSales as ( select ds.allocationRank, ds.allocationType, ds.allocationId, ds.allocationName, ds.purchasingMonth, ds.periodCode, -- ds.forecast_qty2, ds.forecast_qty - coalesce( ( select sum( op.forecast_qty ) from operatorPurchases op where op.purchasingMonth = ds.purchasingMonth and coalesce( op.sku, '' ) = coalesce( ds.sku, '' ) -- and coalesce( op.skuDesc, '' ) = coalesce( ds.skuDesc, '' ) and coalesce( op.dstName, '' ) = coalesce( ds.dstName, '' ) ) , 0.0 ) as forecast_qty ,ds.sku, ds.skuDesc, ds.product, ds.shortDesc, ds.catnum, ds.category, ds.productLineCode, ds.productLine, ds.budgetCategory, ds.territoryName, ds.dstName from distributorSales ds ), allocationSummary as ( select allocationRank, allocationType, allocationId, allocationName, periodCode, sum( forecast_qty ) forecast_qty ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from operatorPurchases group by allocationRank, allocationType, allocationId, allocationName, periodCode ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName union all select allocationRank, allocationType, allocationId, allocationName, periodCode, sum( forecast_qty ) forecast_qty ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from adjDistributorSales group by allocationRank, allocationType, allocationId, allocationName, periodCode ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName ) select ROW_NUMBER() OVER(ORDER BY allocationRank, P1 desc) AS rowNumber, '#_dataType#' as dataType, '#_dataId#' as dataId, allocationType, allocationId, allocationName, coalesce( P1, 0.0 ) as P1, coalesce( P0, 0.0 ) as P0, coalesce( P1, 0.0 ) - coalesce( P0, 0.0 ) change, case when coalesce( P0, 0.0 ) != 0 then ( coalesce( P1, 0.0 ) - coalesce( P0, 0.0 ) ) / coalesce( P0, 0.0 ) * 100.0 else null end as changePct, case when coalesce( p1, 0.0 ) != 0 then round( coalesce( p1, 0.0 ) / sum( coalesce( p1, 0.0 )) over() * 100.0, 1) else 0.00 end as pctOfTotal ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from ( select * from allocationSummary ) as ps pivot ( sum( forecast_qty ) for periodCode in ( P1, P0 ) ) as pvt order by allocationRank, allocationName, territoryName, dstName, sku order by allocationRank, P1 desc