select distinct ownerId as mfrId from fsenablers..tbl_APP_Configurations with (nolock) where applicationCode = '1FSBSR' and parameterName = 'apps.distributorSalesData.enabled' and parameterValue = 'true' and ownerType = 'MFR' and ownerId > 0 select distinct mfrId, principalId from tbl_IMPORT_FoodmarkOrderData WITH (NOLOCK) where mfrId not in () and ownerId = and ownerType = and eai_batchId = select distinct mfrId from tbl_BRO_Principals WITH (NOLOCK) where ownerId = and ownerType = and principalId = update tbl_IMPORT_FoodmarkOrderData set fseMfrId = where eai_batchId = and mfrId = and principalId = and ownerId = and ownerType = update tbl_IMPORT_FoodmarkOrderData set fseStatus = , fseMessage = where eai_batchId = and mfrId = and principalId = and ownerId = and ownerType = update tbl_IMPORT_FoodmarkOrderData set fseMfrId = , fseStatus = , fseMessage = where eai_batchId = and mfrId in () and ownerId = and ownerType = update tbl_IMPORT_FoodmarkOrderData set fseStatus = , fseMessage = where eai_batchId = and fseMfrId is null and fseStatus <> and ownerId = and ownerType = select distinct DstId, distributorCode from tbl_IMPORT_FoodmarkOrderData WITH (NOLOCK) where ownerId = and ownerType = and eai_batchId = select cdr.cdr_recordId FROM tbl_CDR_Distributors AS cdr WITH (NOLOCK) inner join tbl_CDR_DstCodes AS dstCode WITH (NOLOCK) on dstCode.cdr_recordId = cdr.cdr_recordId and dstCode.ownerId = cdr.ownerId and dstCode.ownerType = cdr.fsl_tableCode and cdr.fsl_dstId = and dstCode.cdr_dstCode = where cdr.ownerId = and cdr.fsl_tablecode = update tbl_IMPORT_FoodmarkOrderData set fseCdrRecordId = where eai_batchId = and dstId = and distributorCode = and ownerId = and ownerType = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @batchId int = ; with badSkus as ( select fseMfrId, SKU from tbl_IMPORT_FoodMarkOrderData AS fmd WITH (NOLOCK) where fmd.eai_batchId = @batchId and fmd.ownerType = @ownerType and fmd.ownerId = @ownerId group by fseMfrId, SKU having count( distinct foodmark_itemId ) > 1 ) update fmd set fmd.fseBadSKU = 'Y' from tbl_IMPORT_FoodMarkOrderData fmd inner join badSkus on badSkus.fseMFRId = fmd.fseMFRId and badSkus.sku = fmd.sku where fmd.eai_batchId = @batchId and fmd.ownerType = @ownerType and fmd.ownerId = @ownerId; update tbl_IMPORT_FoodMarkOrderData set fseBadSKU = 'N' where eai_batchId = @batchId and ownerType = @ownerType and ownerId = @ownerId and fseBadSKU is null; update fmd set fmd.fseSkuId = ( select ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fseMfrId and ph.skuId = fmd.skuId ) from tbl_IMPORT_FoodmarkOrderData fmd where fmd.ownerId = and fmd.ownerType = and fmd.eai_batchId = and fmd.skuId > 0 and fmd.fseMfrId > 0 and fmd.fseBadSKU = 'N' update fmd set fmd.fseSkuId = ( select top 1 ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fseMfrId and ph.sku = fmd.sku ) from tbl_IMPORT_FoodmarkOrderData fmd where fmd.ownerId = and fmd.ownerType = and fmd.eai_batchId = and fmd.skuId = 0 and fmd.fseMfrId > 0 and fmd.fseBadSKU = 'N' update fmd set fseStatus = 'Z', fseMessage = case when fseBadSKU = 'Y' then 'Duplicate from Foodmark' when skuId != 0 then 'Invalid SKUId' else 'Unknown SKU' end from tbl_IMPORT_FoodmarkOrderData fmd where fmd.ownerId = and fmd.ownerType = and fmd.eai_batchId = and fmd.fseMFRId > 0 and fmd.fseSkuId is null select distinct orderYear, orderMonth from tbl_IMPORT_FoodmarkOrderData with (nolock) where mfrId not in () and ownerId = and ownerType = and eai_batchId = update tbl_IMPORT_FoodmarkOrderData set fseForecastYear = , fseForecastMonth = where eai_batchId = and orderMonth = and orderYear = and ownerId = and ownerType = update fmd set fseStatus = 'U' from tbl_IMPORT_FoodmarkOrderData fmd with (nolock) where fmd.eai_batchId = and fmd.fseCdrRecordId > 0 and fmd.fseSkuId > 0 and fmd.fseMfrId > 0 and fmd.fseForecastYear >= and fmd.ownerId = and fmd.ownerType = and exists ( SELECT 1 FROM tbl_CDR_FoodmarkOrderData#_tableSuffix# f with (nolock) WHERE f.ownerId = fmd.ownerId and f.ownerType = fmd.ownerType and f.mfr_id = fmd.fseMfrId and f.skuId = fmd.fseSkuId and f.cdr_recordId = fmd.fseCdrRecordId and f.salesDate = DATEFROMPARTS ( fmd.orderYear, fmd.orderMonth, 1 ) and f.salesPeriodType = 'MONTH' and f.salesPeriod = DateFromParts( fmd.fseForecastYear, fmd.fseForecastMonth, 1 ) ) update fmd set fseStatus = 'A' from tbl_IMPORT_FoodmarkOrderData fmd with (nolock) where fmd.eai_batchId = and fmd.fseCdrRecordId > 0 and fmd.fseSkuId > 0 and fmd.fseMfrId > 0 and fmd.fseForecastYear >= and fmd.ownerId = and fmd.ownerType = and not exists ( SELECT 1 FROM tbl_CDR_FoodmarkOrderData#_tableSuffix# f with (nolock) WHERE f.ownerId = fmd.ownerId and f.ownerType = fmd.ownerType and f.mfr_id = fmd.fseMfrId and f.skuId = fmd.fseSkuId and f.cdr_recordId = fmd.fseCdrRecordId and f.salesDate = DATEFROMPARTS ( fmd.orderYear, fmd.orderMonth, 1 ) and f.salesPeriodType = 'MONTH' and f.salesPeriod = DateFromParts( fmd.fseForecastYear, fmd.fseForecastMonth, 1 ) ) select ownerId, ownerType, fmd.fseSkuId,fseMfrId, fmd.fseCdrRecordId, fmd.fseForecastYear, fmd.fseForecastMonth,fmd.orderYear, fmd.orderMonth, fmd.eai_batchId, --, bat.batchTime, fmd.skuLastInvoiceDate, fmd.skuLastInvoiceCases, fmd.SKULastInvoiceNetWeight skuLastInvoiceLbs, fmd.SKULastInvoiceTotalAmt skuLastInvoiceDollars, sum( fmd.sumCases ) as cases, sum( fmd.sumNetWeight ) as lbs ,sum( fmd.TotalAmt ) as dollars, sum( fmd.TotalCommission ) as commission from tbl_IMPORT_FoodmarkOrderData AS fmd WITH (NOLOCK) where fmd.eai_batchId = and fmd.fseCdrRecordId > 0 and fmd.fseSkuId > 0 and fmd.fseMfrId > 0 and fmd.fseForecastYear >= and fmd.ownerId = and fmd.ownerType = and fmd.fseStatus = 'U' group by ownerId, ownerType, fmd.fseSkuId,fseMfrId, fmd.fseCdrRecordId, fmd.fseForecastYear, fmd.fseForecastMonth,fmd.orderYear, fmd.orderMonth, fmd.eai_batchId,fmd.skuLastInvoiceDate, fmd.skuLastInvoiceCases, fmd.SKULastInvoiceNetWeight, fmd.SKULastInvoiceTotalAmt --, bat.batchTime update tbl_CDR_FoodmarkOrderData#_tableSuffix# set cases = , lbs = , dollars = , commission = , alt = , recordDate = , updateCount = coalesce( updateCount, 1 ) + 1, skulastInvoiceDate = , skulastInvoiceCases = , skulastInvoiceLbs = , skulastInvoiceDollars = where ownerId = and ownerType = and salesPeriodType = 'MONTH' and salesPeriod = and salesDate = and cdr_recordId = and mfr_id = and skuId =

insert into tbl_CDR_FoodmarkOrderData#_tableSuffix# ( ownerType, ownerId, salesPeriodType, salesPeriod, salesDate, cdr_recordId, mfr_id, skuId, cases, lbs, dollars, commission, alt , recordDate, updateCount, skuLastInvoiceDate, skuLastInvoiceCases, skuLastInvoiceLbs, skuLastInvoiceDollars ) select ownerType, ownerId, 'MONTH' salesPeriodType, DateFromParts( fseForecastYear, fseForecastMonth, 1 ) salesPeriod, DateFromParts( fseForecastYear, fseForecastMonth, 1 ) salesDate, fseCdrRecordId, fseMfrId, fseSkuId , sum( i.sumCases ) as cases, sum( i.sumNetWEight ) as lbs, sum( i.totalAmt ) as dollars, sum( i.totalCommission ) as commission , null as alt , as recordDate , 1 , max( skuLastInvoiceDate ) skuLastInvoiceDate , sum( skuLastInvoiceCases ) skuLastInvoiceCases, sum( skuLastInvoiceNetWeight ) skuLastInvoiceNetWeight, sum( skuLastInvoiceTotalAmt ) skuLastInvoiceTotalAmt from tbl_IMPORT_FoodmarkOrderData i WITH (NOLOCK) where fseSkuId > 0 and fseCdrRecordId > 0 and fseForecastYear > 0 and eai_batchId = and fseStatus = 'A' group by ownerType, ownerId, fseForecastYear, fseForecastMonth, fseCdrRecordId, fseSkuId, fseMfrId -- , skuLastInvoiceDate -- , skuLastInvoiceCases, skuLastInvoiceNetWeight, skuLastInvoiceTotalAmt

with lastInvoiceData as ( select cdr_recordId, skuId, skulastInvoiceDate, skulastInvoiceCases, skulastInvoiceLbs, skulastInvoiceDollars, row_number() over( partition by cdr_recordId, skuId order by skulastInvoiceDate desc ) as invoiceRank from tbl_CDR_FoodmarkOrderData#_tableSuffix# WITH (NOLOCK) where skulastInvoiceDate is not null ), lastInvoice as ( select cdr_recordId, skuId, skuLastInvoiceDate, skuLastInvoiceCases, skuLastInvoiceLbs, skuLastInvoiceDollars from lastInvoiceData where invoiceRank = 1 ) update fmd set fmd.skuLastInvoiceDate = li.skuLastInvoiceDate, fmd.skuLastInvoiceCases = li.skuLastInvoiceCases, fmd.skuLastInvoiceLbs = li.skuLastInvoiceLbs, fmd.skuLastInvoiceDollars = li.skuLastInvoiceDollars from tbl_CDR_FoodmarkOrderData#_tableSuffix# fmd inner join lastInvoice li with (nolock) on li.cdr_recordId = fmd.cdr_recordId and li.skuId = fmd.skuId where fmd.skuLastInvoiceDate is null or fmd.skuLastInvoiceDate != li.skuLastInvoiceDate select distinct mfrId,manufacturerCode, manufacturerName from tbl_IMPORT_FoodmarkOrderData with (nolock) where fseMfrId is null and fseStatus = 'Z' and ownerId = and ownerType = and eai_batchId = select distinct dstId, distributorCode, distributorName from tbl_IMPORT_FoodmarkOrderData WITH (NOLOCK) where coalesce(fseCdrRecordId,0) = 0 and ownerId = and ownerType = and eai_batchId = select distinct skuId, sku, itemDescription, foodmark_itemId, manufacturerName, fseMessage from tbl_IMPORT_FoodmarkOrderData with (nolock) where coalesce(fseSkuId,0) = 0 and ownerId = and ownerType = and eai_batchId = and fseMfrId > 0 and fseCdrRecordId > 0 order by manufacturerName, sku, itemDescription with summary as ( select manufacturerName, fseforecastYear,fseforecastMonth, sum( sumCases ) cases, sum( sumNetWeight ) lbs, sum( totalAmt ) dollars , sum( totalCommission ) commission, sum( case when fseForecastYear > 0 and fseCdrRecordId > 0 and fseSkuId > 0 then sumCases else 0 end ) loadedCases, sum( case when fseForecastYear > 0 and fseCdrRecordId > 0 and fseSkuId > 0 then sumNetWeight else 0 end ) loadedLbs, sum( case when fseForecastYear > 0 and fseCdrRecordId > 0 and fseSkuId > 0 then totalAmt else 0 end ) loadedDollars, sum( case when fseForecastYear > 0 and fseCdrRecordId > 0 and fseSkuId > 0 then totalCommission else 0 end ) loadedCommission from tbl_IMPORT_FoodmarkOrderData WITH (NOLOCK) where eai_batchId = and ownerId = and ownerType = and fseforecastYear = and fseForecastMonth = group by manufacturerName, fseforecastYear,fseforecastMonth ) select manufacturerName, cases, loadedCases, cases-loadedCases as CasesDiff, lbs, loadedLbs, lbs-loadedLbs as LbsDiff, dollars, loadedDollars, dollars - loadedDollars as DollarsDiff from summary order by manufacturerName

Data Summary for Manufacturers for the month of #monthAsString(orderMonth)# #orderYear# - #lsnumberformat( qmd_summaryByMfrMonthYear.recordCount )# Records

with distributorSummaryRaw as ( select OrderYear, OrderMonth, DistributorCode, DistributorName, sum( sumCases ) cases, round( sum( sumnetWeight ), 2) lbs, sum( totalAmt ) dollars, sum( totalCommission ) commission, sum( case when fseSKUId > 0 and fseForecastYear > 0 and fseCdrRecordId > 0 then sumCases else 0.0 end ) loadedCases, round( sum( case when fseSKUId > 0 and fseForecastYear > 0 and fseCdrRecordId > 0 then sumNetWeight else 0.0 end ), 2 ) loadedLbs, sum( case when fseSKUId > 0 and fseForecastYear > 0 and fseCdrRecordId > 0 then totalAmt else 0.0 end ) loadedDollars, sum( case when fseSKUId > 0 and fseForecastYear > 0 and fseCdrRecordId > 0 then totalCommission else 0.0 end ) loadedCommission from tbl_IMPORT_FoodmarkOrderData with (nolock) where eai_batchId = and ownerId = and ownerType = and fseforecastYear = and fseForecastMonth = group by OrderYear, OrderMonth, DistributorCode, DistributorName ) select OrderYear, OrderMonth, DistributorCode, DistributorName, cases, loadedCases, loadedCases - cases as diffCases, lbs, loadedLbs, round( loadedLbs - lbs, 2 ) as diffLbs, dollars, loadedDollars, round( loadedDollars - dollars, 2 ) as diffDollars from distributorSummaryRaw order by orderYear, orderMonth, DistributorCode

Data Summary for Distributors for the month of #monthAsString(orderMonth)# #orderYear# - #lsnumberformat( qmd_summaryByDistributorMonthYear.recordCount )# Records

Unresolved Manufacturers #lsnumberformat( qmd_unresolvedMfrs.recordCount )#

Unresolved Distributors #lsnumberformat( qmd_unresolvedDists.recordCount )#

Unresolved SKUs #lsnumberformat( qmd_unresolvedSkus.recordCount )#