update tbl_IMPORT_DistributorSales set customerNbr = right( '0000000000' + customerNbr, 10 ) where eai_batchId = and ownerId = 1644 and ownerType = 'MFR' update tbl_IMPORT_DistributorSales set customerNbr = right( '00000000' + customerNbr, 8 ) where eai_batchId = and ownerId = 1984 and ownerType = 'MFR' update i set fseInclude = 0, eai_batchId = eai_batchId * -1 from tbl_IMPORT_DistributorSales i inner join tbl_IMPORT_Exclusions x on x.ownerId = i.ownerId and x.ownerType = i.ownerType and x.exclusionType = 'CDR' and x.exclusionId = i.customerNbr where i.ownerId = and i.ownerType = and i.eai_batchId = set nocount on update activity set activity.fseDistributorId = cdr.cdr_recordId from tbl_IMPORT_DistributorSales activity inner join tbl_CDR_Distributors cdr on cdr.ownerId = activity.ownerId and cdr.fsl_tablecode = activity.ownerType and cdr.cdr_dstcode = activity.customerNbr where activity.ownerId = and activity.ownerType = and activity.eai_batchId = select rowsAffected = @@rowcount set nocount off select distinct salesMonth, salesYear from tbl_IMPORT_DistributorSales sls where sls.eai_batchId = and sls.ownerId = and sls.ownerType = order by salesYear desc, salesMonth desc select distinct salesYear from tbl_IMPORT_DistributorSales sls where sls.eai_batchId = and sls.ownerId = and sls.ownerType = update tpf set tpf.forecast_qty = 0, tpf.forecast_amt = 0 from tbl_MFR_TPF_Detail tpf left outer join tbl_IMPORT_DistributorSales sls on sls.ownerId = tpf.mfr_id and sls.ownerType = '#qmd_batch.ownerType#' and sls.ownerId = #qmd_batch.ownerId# and sls.eai_batchId = #qmd_batch.batchId# and sls.fseDistributorId = tpf.tpartnerId and right( '0' + convert( varchar, sls.salesMonth ), 2 ) = tpf.forecast_month and sls.salesYear = tpf.forecast_year and sls.sku = tpf.productItemCode where tpf.tpartnerType = 'CDR' and tpf.productLevel = 'SKU' and tpf.forecast_status = 'Q' and tpf.mfr_id = #qmd_batch.ownerId# and ( OR ( tpf.forecast_year = '#salesYear#' and tpf.forecast_month = '#_salesMonth#' ) ) and sls.fseSkuId is null update tpf set tpf.forecast_qty = sls.forecast_qty, tpf.forecast_amt = sls.forecast_amt from tbl_MFR_TPF_Detail tpf inner join ( select sls.ownerId mfr_id, sls.salesYear forecast_year, right( '0' + convert( varchar, sls.salesMonth ), 2 ) forecast_month, sls.fseDistributorId tpartnerId, sls.sku productItemCode, sum( sls.qty ) forecast_qty, sum( sls.salesAmt ) forecast_amt, { fn now() } lastupdate, 'Q' forecast_status, 'EAI' sourceOfUpdate, 'CDR' tpartnerType, 'SKU' productLevel from tbl_IMPORT_DistributorSales sls inner join tbl_MFR_TPF_Detail tpf on tpf.tpartnerId = sls.fseDistributorId and tpf.tpartnerType = 'CDR' and tpf.productItemCode = sls.sku and tpf.productLevel = 'SKU' and tpf.forecast_month = right( '0' + convert( varchar, sls.salesMonth ) , 2 ) and tpf.forecast_year = sls.salesYear and tpf.mfr_id = sls.ownerId and tpf.forecast_status = 'Q' where sls.eai_batchId = #qmd_batch.batchId# and sls.ownerType = '#qmd_batch.ownerType#' and sls.ownerId = #qmd_batch.ownerId# and sls.fseDistributorId is not null and sls.fseSkuId is not null group by sls.ownerId, sls.salesYear, sls.salesMonth, sls.fseDistributorId, sls.sku ) sls on sls.mfr_id = tpf.mfr_id and sls.tpartnerId = tpf.tpartnerId and sls.tpartnerType = tpf.tpartnerType and sls.forecast_year = tpf.forecast_year and sls.forecast_month = tpf.forecast_month and sls.productLevel = tpf.productLevel and sls.productItemCode = tpf.productItemCode where tpf.mfr_id = #qmd_batch.ownerId# and tpf.forecast_status = 'Q' insert into tbl_MFR_TPF_Detail ( mfr_id, forecast_year, forecast_month, tpartnerId, productItemCode, forecast_qty, forecast_amt, lastUpdate, forecast_status, sourceOfUpdate, tpartnerType, productLevel ) select sls.ownerId mfr_id, sls.salesYear forecast_year, right( '0' + convert( varchar, sls.salesMonth ), 2 ) forecast_month, sls.fseDistributorId tpartnerId, sls.sku, sum( sls.qty ) forecast_qty, sum( sls.salesAmt ) forecast_amt, { fn now() } lastupdate, 'Q' forecast_status, 'EAI' sourceOfUpdate, 'CDR' tpartnerType, 'SKU' productLevel from tbl_IMPORT_DistributorSales sls left outer join tbl_MFR_TPF_Detail tpf on tpf.tpartnerId = sls.fseDistributorId and tpf.tpartnerType = 'CDR' and tpf.productItemCode = sls.sku and tpf.productLevel = 'SKU' and tpf.forecast_month = right( '0' + convert( varchar, sls.salesMonth ) , 2 ) and tpf.forecast_year = sls.salesYear and tpf.forecast_status = 'Q' and tpf.mfr_id = sls.ownerId where sls.eai_batchId = #qmd_batch.batchId# and sls.ownerType = '#qmd_batch.ownerType#' and sls.ownerId = #qmd_batch.ownerId# and tpf.mfr_id is null and sls.fseDistributorId is not null and sls.fseSkuId is not null group by sls.ownerId, sls.salesYear, sls.salesMonth, sls.fseDistributorId, sls.sku insert into tbl_MFR_TPF_Detail( mfr_id, tpartnerId, tpartnerType, forecast_year, forecast_month, productLevel, productItemCode, forecast_qty, lastUpdate, forecast_status, sourceOfUpdate ) select skus.mfr_id, skus.tpartnerId, skus.tpartnerType, skus.forecast_year, right( '0' + convert( varchar, cal.fiscalMonth ), 2 ) forecast_month, skus.productLevel, skus.productItemCode, 0 forecast_qty, { fn now() } lastUpdate, 'Q' forecast_status, 'EAI' sourceOfUpdate from tbl_SYS_Calendar cal inner join ( select distinct mfr_id, productItemCode, productLevel, tpartnerId, tpartnerType, forecast_year from tbl_MFR_TPF_Detail tpf where tpf.mfr_id = #qmd_batch.ownerId# and tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'Q') skus on skus.forecast_year = cal.fiscalYear left outer join tbl_MFR_TPF_Detail tpf on convert( int, tpf.forecast_month ) = cal.fiscalMonth and convert( int, tpf.forecast_year ) = skus.forecast_year and tpf.tpartnerType = skus.tpartnerType and tpf.tpartnerId = skus.tpartnerId and tpf.productLevel = skus.productLevel and tpf.productItemCode = skus.productItemCode and tpf.mfr_id = #qmd_batch.ownerId# and tpf.forecast_status = 'Q' where tpf.forecast_month is null and skus.forecast_year in ( ) OR ( tpf.forecast_month = '#_monthParam#' and tpf.forecast_year = '#_yearParam#') ) update tpf set tpf.mktCategoryId = mktCat.filterId, tpf.mktSubCategoryId = mktSubCat.attrId from tbl_MFR_TPF_Detail tpf inner join tbl_ORG_AttributeLinks scl on scl.child_attrId = tpf.prodId and scl.child_attrTypeId = 59 and scl.mstr_attrTypeId = 29 inner join tbl_ORG_Attributes mktSubCat on mktSubCat.attrId = scl.mstr_attrId and mktSubCat.ownerId = tpf.mfr_id and mktSubCat.fsl_tablecode = 'MFR' inner join tbl_ORG_AttributeLinks cl on cl.child_attrId = mktSubCat.attrId and cl.child_attrTypeId = 29 and cl.mstr_attrTypeId = 28 inner join tbl_CMM_Filters mktCat on mktCat.filterId = cl.mstr_attrId and mktCat.ownerId = mktSubCat.ownerId and mktCat.fsl_tablecode = mktSubCat.fsl_tablecode inner join tbl_CMM_FilterTypes ft on ft.filterTypeId = mktCat.filterTypeId and ft.filterTypeName = 'ProductHierarchy' where tpf.tpartnerType = 'CDR' and tpf.mfr_id = #qmd_batch.ownerId# and tpf.forecast_status = 'Q' and ( OR ( tpf.forecast_month = '#_monthParam#' and tpf.forecast_year = '#_yearParam#') ) ---> OR ( tpf.forecast_month = and tpf.forecast_year = ) ) where fpd.ownerId = and fpd.ownerType = group by fpd.ownerId, fpd.ownerType, fpd.mktCategoryId, fpd.mktSubCategoryId, fpd.cdr_recordId, cls.classificationId, cls.secondaryThresholdQty, cls.secondaryThresholdAmt, cls.primaryThresholdQty, cls.primaryThresholdAmt ---> ---> select bp.ownerId, bp.ownerType, bp.territoryId, bp.businessPlanId from tbl_TER_BusinessPlans bp inner join tbl_TER_BusinessPlanGoals bpg on bpg.businessPlanId = bp.businessPlanId and bpg.goalType = 'G' and bpg.ownerId = bp.ownerId and bpg.ownerType = bp.ownerType where bp.ownerId = and bp.ownerType = and ( or ( >= bp.startDate and <= bp.endDate ) ) ---> select endDate, datediff( month, startDate, endDate ) + 1 months from ( select max( convert( datetime, convert( varchar, salesYear ) + '-' + convert( varchar, salesMonth ) + '-1' ) ) endDate, min( convert( datetime, convert( varchar, salesYear ) + '-' + convert( varchar, salesMonth ) + '-1' ) ) startDate from tbl_IMPORT_DistributorSales where eai_batchId = ) x select activity.sku, sum( qty ) qtyTotal, sum( salesAmt ) salesTotal from tbl_IMPORT_DistributorSales activity where activity.eai_batchId = and activity.ownerId = and activity.ownerType = and fseSkuId is null and sku <> '' group by activity.sku having sum( qty ) <> 0 order by abs( sum( salesAmt )) desc, abs( sum( qty )) desc

Unmapped SKUs

SKUQuantityDollars
#sku##lsnumberFormat( qtyTotal)##lscurrencyformat( salesTotal)#
--->