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 = and sls.ownerId = and sls.eai_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 = 'A' and tpf.mfr_id = and ( OR ( tpf.forecast_year = and tpf.forecast_month = ) ) 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, 'A' 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 = 'A' where sls.eai_batchId = and sls.ownerType = and sls.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 = and tpf.forecast_status = 'A' 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, 'A' 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 = 'A' and tpf.mfr_id = sls.ownerId where sls.eai_batchId = and sls.ownerType = and sls.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, 'A' 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 = and tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'A') 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 = where tpf.forecast_month is null and skus.forecast_year in ( ) 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 OR ( tpf.forecast_month = and tpf.forecast_year = ) ) 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 = and tpf.forecast_status = 'A' and ( OR ( tpf.forecast_month = and tpf.forecast_year = ) ) select fpd.ownerId, fpd.ownerType, fpd.mktCategoryId, fpd.mktSubCategoryId, fpd.cdr_recordId, cls.classificationId, cls.secondaryThresholdQty, cls.secondaryThresholdAmt, cls.primaryThresholdQty, cls.primaryThresholdAmt, round( sum ( tpf.forecast_qty ) / count( distinct (tpf.forecast_year + tpf.forecast_month )), 0 ) avgQty, round( sum ( tpf.forecast_amt ) / count( distinct (tpf.forecast_year + tpf.forecast_month )), 0 ) avgSls from ( select fp.ownerId, fp.ownerType, fp.mktCategoryId, fp.mktSubCategoryId, cdr.cdr_recordId, cdr.classificationId from tbl_MFR_FocusProducts fp inner join tbl_CDR_Distributors cdr on cdr.ownerId = fp.ownerId and cdr.fsl_tablecode = fp.ownerType ) fpd left outer join tbl_CDR_Classifications cls on cls.ownerId = fpd.ownerId and cls.ownerType = fpd.ownerType and cls.classificationId = fpd.classificationId left outer join tbl_MFR_TPF_Detail tpf on tpf.mktCategoryId = fpd.mktCategoryId and tpf.mktSubCategoryId = fpd.mktSubCategoryId and tpf.mfr_id = fpd.ownerId and tpf.tpartnerId = fpd.cdr_recordId and tpf.tpartnerType = 'CDR' and tpf.productLevel = 'SKU' and tpf.forecast_status = 'A' and ( 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 ) ) delete tpf from tbl_MFR_TPF_Detail tpf inner join tbl_IMPORT_DistributorSales sls on sls.fseDistributorId = tpf.tpartnerId and sls.salesYear = convert( int, tpf.forecast_year ) and sls.salesMonth = convert( int, tpf.forecast_month ) and sls.ownerId = tpf.mfr_id where tpf.productLevel = 'STP' and tpf.tpartnerType = 'CDR' and sls.eai_batchId = and sls.ownerId = and sls.ownerType = insert into tbl_MFR_TPF_Detail ( mfr_id, forecast_year, forecast_month, tpartnerId, tpartnerType, forecast_status, productLevel, productItemCode, forecast_amt, forecast_qty ) select tpf.mfr_id, tpf.forecast_year, tpf.forecast_month, tpf.tpartnerId, tpf.tpartnerType, tpf.forecast_status, 'STP' productLevel, convert( varchar, tpf.tpartnerId ) productItemCode, sum( coalesce( tpf.forecast_amt, 0 )) forecast_amt, sum( coalesce( tpf.forecast_qty, 0 )) forecast_qty from tbl_MFR_TPF_Detail tpf inner join ( select distinct ownerid, ownerType, fseDistributorId, salesYear, salesMonth from tbl_IMPORT_DistributorSales where eai_batchId = and fseDistributorId is not null and fseSkuId is not null) sls on sls.ownerId = tpf.mfr_id and sls.ownerType = and sls.ownerId = and sls.salesMonth = convert( int, tpf.forecast_month ) and sls.salesYear = convert( int, tpf.forecast_year ) and sls.fseDistributorId = tpf.tpartnerId where tpf.forecast_status = 'A' and tpf.productLevel = 'SKU' and tpf.tpartnertype = 'CDR' and tpf.mfr_id = group by tpf.mfr_id, tpf.forecast_year, tpf.forecast_month, tpf.tpartnerId, tpf.tpartnerType, tpf.forecast_status --->