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 = select distinct activity.customerNbr from tbl_IMPORT_DistributorSales activity where activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseDistributorId is null and nullif( activity.customerNbr, '' ) is not null select cdr.cdr_recordId from tbl_CDR_DstCodes dstCodes inner join tbl_CDR_Distributors cdr on cdr.ownerId = dstCodes.ownerId and cdr.fsl_tablecode = dstCodes.ownerType and cdr.cdr_recordId = dstCodes.cdr_recordId where dstCodes.ownerId = and dstCodes.ownerType = and dstCodes.cdr_dstCode = select cdr.cdr_recordId from tbl_CDR_Distributors cdr where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCode = update activity set activity.fseDistributorId = from tbl_IMPORT_DistributorSales activity where activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseDistributorId is null and activity.customerNbr = update activity set activity.fseDistributorId = cdr.cdr_recordId from tbl_IMPORT_DistributorSales activity inner join tbl_CDR_DstCodes dstCodes on dstCodes.ownerId = activity.ownerId and dstCodes.ownerType = activity.ownerType and dstCodes.cdr_dstcode = activity.customerNbr inner join tbl_CDR_Distributors cdr on cdr.ownerId = dstCodes.ownerId and cdr.fsl_tablecode = dstCodes.ownerType and cdr.cdr_recordId = dstCodes.cdr_recordId where activity.ownerId = and activity.ownerType = and activity.eai_batchId = and activity.fseDistributorId is null 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 = and activity.fseDistributorId is null ---> select sls.fseSkuId, sls.fseProdId, sls.ownerId, sls.salesYear, sls.salesMonth, sls.fseDistributorId, 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 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.fseSkuId, sls.fseProdId, sls.ownerId, sls.salesYear, sls.salesMonth, sls.fseDistributorId, sls.sku update tpf set tpf.forecast_qty = , tpf.forecast_amt = , tpf.lastUpdate = , tpf.sourceOfUpdate = 'EAI', tpf.prodId = from tbl_MFR_TPF_Detail#_tpfSuffix# tpf where tpf.tpartnerId = and tpf.tpartnerType = 'CDR' and tpf.skuId = and tpf.productLevel = 'SKU' and tpf.forecast_status = 'A' and tpf.mfr_id = and tpf.forecast_month = and tpf.forecast_year = update tpf set tpf.forecast_qty = , tpf.forecast_amt = , tpf.lastUpdate = , tpf.sourceOfUpdate = 'EAI', tpf.prodId = , tpf.skuId = from tbl_MFR_TPF_Detail#_tpfSuffix# tpf where tpf.tpartnerId = and tpf.tpartnerType = 'CDR' and tpf.productItemCode = and tpf.productLevel = 'SKU' and tpf.forecast_status = 'A' and tpf.mfr_id = and tpf.forecast_month = and tpf.forecast_year = insert into tbl_MFR_TPF_Detail#_tpfSuffix# ( mfr_id, forecast_year, forecast_month, tPartnertype, tpartnerId, productLevel, skuId, prodId, productItemCode, forecast_qty, forecast_amt, forecast_status, lastUpdate, sourceOfUpdate ) values ( , , , 'CDR',, 'SKU',, , , , , 'A', , 'EAI' ) 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 ---> select distinct fseDistributorId from tbl_IMPORT_DistributorSales where eai_batchId = and ownerId = and ownerType = and fseDistributorId is not null and fseSkuId is not null update tpf set tpf.prodId = s.prodId, tpf.skuId = s.skuId from tbl_MFR_TPF_Detail#_tpfSuffix# tpf left outer join tbl_PRD_Skus s on s.sku = tpf.productItemCode and s.ownerId = tpf.mfr_id and s.fsl_tablecode = 'MFR' and s.inactive = '0' and s.crmactive = 'Y' where tpf.productLevel = 'SKU' and tpf.mfr_id = and tpf.tpartnerType = 'CDR' and tpf.tpartnerId in ( ) and tpf.forecast_status = 'A' update tpf set tpf.bgtCategoryId = s.bgtCategoryId, tpf.mktCategoryId = s.mktCategoryId, tpf.mktSubCategoryId = s.mktSubCategoryId from tbl_MFR_TPF_Detail#_tpfSuffix# tpf inner join tbl_REF_Skus s on s.skuId = tpf.skuid and s.ownerId = tpf.mfr_id where tpf.mfr_id = and tpf.tpartnerType = 'CDR' and tpf.productLevel = 'SKU' and tpf.tpartnerId 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