select fmOrderSummaryRunning from tblBrokers with (nolock) where broker_id =

Skipping import of FoodMark data because FoodMark OrderSummary Processing Running Since: #qmd_processingCheck.fmOrderSummaryRunning#.

update tblBrokers set seOrderSummaryRunning = getDate() where broker_id = 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 truncate table [tbl_CDR_FoodmarkOrderData#_tableSuffix#] with li as ( select *, row_number() over ( partition by fse_mfr_id, fse_cdr_id, fse_sku_id order by record_date ) as x from [#_lastInvoiceTable#] li with (nolock) where record_status = 'X' ) 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 '#attributes.ownerType#' ownerType, #attributes.ownerId# ownerId, 'MONTH' salesPeriodType, fmd.sales_month salesPeriod, fmd.sales_month salesDate, fmd.fse_cdr_id as cdr_recordId, fmd.fse_mfr_id as mfr_id, fmd.fse_sku_id as sku_id, sum( fmd.total_cases ) as cases, sum( fmd.total_lbs ) as lbs, sum( fmd.total_dollars ) as dollars, sum( fmd.total_commission ) as commission, null as alt, max( fmd.record_date ) as recordDate, count(*) as updateCount, li.last_invoice_date as skuLastInvoiceDate, li.last_invoice_cases as skuLastInvoiceCases, li.last_invoice_lbs as skuLastInvoiceNetWeight, li.last_invoice_dollars as skuLastInvoiceTotalAmt from [#_orderSummaryTable2#] AS fmd WITH (NOLOCK) left outer join li with (nolock) on li.fse_cdr_id = fmd.fse_cdr_id and li.fse_sku_id = fmd.fse_sku_id and li.fse_mfr_id = fmd.fse_mfr_id and li.x = 1 where fmd.record_status = 'X' and fmd.sales_month <= datefromParts( year( getDate() ), month( getDate() ), 1 ) group by fmd.sales_month, fmd.fse_cdr_id, fmd.fse_mfr_id, fmd.fse_sku_id, li.last_invoice_date, li.last_invoice_cases, li.last_invoice_lbs, li.last_invoice_dollars update tblBrokers set seOrderSummaryRunning = null, seOrderSummaryLastRunMs = where broker_id = update [#_orderSummaryTable#] set fse_mfr_id = null, fse_cdr_id = null, fse_sku_id = null, record_status = '?', record_error = null select distinct mfr_id, principal_id from [#_orderSummaryTable#] WITH (NOLOCK) where mfr_id not in () select distinct mfrId from tbl_BRO_Principals WITH (NOLOCK) where ownerId = and ownerType = and principalId = update [#_orderSummaryTable#] set fse_mfr_id = where mfr_Id = and principal_Id = update [#_orderSummaryTable#] set record_status = , record_error = where mfr_Id = and principal_Id = update [#_orderSummaryTable#] set fse_mfr_id = , record_status = , record_error = where mfr_Id in () update [#_orderSummaryTable#] set record_status = , record_error = where fse_mfr_id is null select distinct dst_id, distributor_code from [#_orderSummaryTable#] WITH (NOLOCK) 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 [#_orderSummaryTable#] set fse_cdr_id = , record_status = 'Z', record_error = nullif( case when record_error is not null then ',' + else end, '' ) where dst_Id = and distributor_Code = with badSkus as ( select fse_mfr_id, SKU from [#_orderSummaryTable#] AS fmd WITH (NOLOCK) group by fse_mfr_id, SKU having count( distinct fmItemId ) > 1 ) update fmd set fmd.fse_sku_id = -1 from [#_orderSummaryTable#] fmd inner join badSkus on badSkus.fse_mfr_id = fmd.fse_mfr_id and badSkus.sku = fmd.sku update fmd set fmd.fse_sku_id = ( select ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fse_mfr_id and ph.skuId = fmd.sku_id ) from [#_orderSummaryTable#] fmd where fmd.sku_id > 0 and fmd.fse_mfr_id > 0 and coalesce( fmd.fse_sku_id, 0 ) = 0 update fmd set fmd.fse_sku_id = ( select top 1 ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fse_mfr_id and ph.sku = fmd.sku ) from [#_orderSummaryTable#] fmd where fmd.sku_id = 0 and fmd.fse_mfr_id > 0 and coalesce( fmd.fse_sku_id, 0 ) = 0 update fmd set record_status = 'Z', record_error = case when record_error is not null then coalesce( record_error, '' ) + ',' + case when fse_sku_id = -1 then 'Duplicate from Foodmark' when sku_id != 0 then 'Invalid sku_id' else 'Unknown SKU' end else case when fse_sku_id = -1 then 'Duplicate from Foodmark' when sku_id != 0 then 'Invalid sku_id' else 'Unknown SKU' end end from [#_orderSummaryTable#] fmd where fmd.fse_mfr_id > 0 and coalesce( fmd.fse_sku_id, 0 ) <= 0 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 = ---> truncate table [tbl_CDR_FoodmarkOrderData#_tableSuffix#] 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 '#attributes.ownerType#' ownerType, #attributes.ownerId# ownerId, 'MONTH' salesPeriodType, fmd.sales_month salesPeriod, fmd.sales_month salesDate, fmd.fse_cdr_id as cdr_recordId, fmd.fse_mfr_id as mfr_id, fmd.fse_sku_id as sku_id, fmd.total_cases as cases, fmd.total_lbs as lbs, fmd.total_dollars as dollars, fmd.total_commission as commission, null as alt, getDate() as recordDate, 1 as updateCount, null as skuLastInvoiceDate, null as skuLastInvoiceCases, null as skuLastInvoiceNetWeight, null as skuLastInvoiceTotalAmt from [#_orderSummaryTable#] AS fmd WITH (NOLOCK) where fmd.fse_cdr_id > 0 and fmd.fse_sku_id > 0 and fmd.fse_mfr_id > 0 and fmd.sales_month <= datefromParts( year( getDate() ), month( getDate() ), 1 ) update [#_orderSummaryTable#] set record_status = 'X' where record_status = '?' 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 )#

update [#_tableName#] set record_error = null, fse_mfr_id = null, fse_cdr_id = null, fse_sku_id = null, record_status = '?' where record_status in ( '?', 'Z' ) update [#_tableName#] set fse_mfr_id = 0, record_status = 'Z', record_error = 'Syndicated MFR' where mfr_Id in () select distinct mfr_id, principal_id from [#_tableName#] WITH (NOLOCK) where record_status = '?' select distinct mfrId from tbl_BRO_Principals WITH (NOLOCK) where ownerId = and ownerType = and principalId = update [#_tableName#] set fse_mfr_id = where mfr_Id = and principal_Id = and record_status = '?' update [#_tableName#] set record_error = where mfr_Id = and principal_Id = and record_status = '?' update [#_tableName#] set record_error = where fse_mfr_id is null select distinct dst_id, distributor_code from [#_tableName#] WITH (NOLOCK) where record_status = '?' 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 [#_tableName#] set fse_cdr_id = , record_error = case when record_error is not null then record_error + ',' + else end where dst_Id = and distributor_Code = and record_status = '?' with badSkus as ( select fse_mfr_id, SKU from [#_tableName#] AS fmd WITH (NOLOCK) where record_status = '?' group by fse_mfr_id, SKU having count( distinct fmItemId ) > 1 ) update fmd set fmd.fse_sku_id = -1, fmd.record_status = '?', fmd.record_error = case when record_error is not null then ', Multiple fmItemId of ' + cast( fmd.fmItemId as varchar ) else 'Multiple fmItemId of ' + cast( fmd.fmItemId as varchar ) end from [#_tableName#] fmd inner join badSkus on badSkus.fse_mfr_id = fmd.fse_mfr_id and badSkus.sku = fmd.sku update fmd set fmd.fse_sku_id = ( select ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fse_mfr_id and ph.skuId = fmd.sku_id ) from [#_tableName#] fmd where fmd.sku_id > 0 and fmd.fse_mfr_id > 0 and fmd.record_status = '?' ---> update fmd set fmd.fse_sku_id = ( select top 1 ph.skuId from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = fmd.fse_mfr_id and ph.sku = fmd.sku ) from [#_tableName#] fmd where fmd.sku_id = 0 and fmd.fse_mfr_id > 0 and fmd.record_status = '?' ---> update fmd set record_error = case when record_error is not null then coalesce( record_error, '' ) + ',' + case when fse_sku_id = -1 then 'Duplicate from Foodmark' when sku_id != 0 then 'Invalid sku_id' else 'Unknown SKU' end else case when fse_sku_id = -1 then 'Duplicate from Foodmark' when sku_id != 0 then 'Invalid sku_id' else 'Unknown SKU' end end from [#_tableName#] fmd where fmd.fse_mfr_id > 0 and coalesce( fmd.fse_sku_id, 0 ) <= 0 and record_status = '?' update [#_tableName#] set record_status = case when record_error is not null then 'Z' when fse_sku_id > 0 and fse_mfr_id > 0 and fse_cdr_id > 0 then 'X' else 'Z' end where record_status != 'X' select fmDistributorId, distributor_code, distributor_name, dst_id, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%unknown distributor%' group by fmDistributorId, distributor_code, distributor_name, dst_id

select fmDistributorId, distributor_code, distributor_name, dst_id, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%multiple distributor%' group by fmDistributorId, distributor_code, distributor_name, dst_id

select mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%multiple fmItemId%' group by mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId

select mfr_id, manufacturer_code, manufacturer_name, fmManufacturerId, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%unknown mFR%' group by mfr_id, manufacturer_code, fmManufacturerId, manufacturer_name

select mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%invalid sku_id%' group by mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId

select mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId, count(*) error_lines from tbl_FM_OrderSummary#_tableSuffix# with (nolock) where record_status != 'X' and record_error like '%unknown sku%' group by mfr_id, manufacturer_code, sku_id, sku, item_description, fmItemId