select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$KELLANOVA_OPR_PURCHASING_DATA_EXPORT' and batchTime < and status = 'OK' order by batchTime desc delete from tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data where ownerId = and ownertype = and eai_batchId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @currYear int; declare @lastYear int; SET IDENTITY_INSERT dbo.tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data ON; select @currYear = max( fiscalYear ) from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0; if @currYear is null begin select @currYear = fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y' and getDate() between startDate and endDate end if @currYear is null begin set @currYear = Year( getDate() ); end set @lastYear = @currYear - 1; insert into tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data( ownerId, ownerType, eai_batchId, eai_lineNumber, sku, sku_description, sales_year, total_year_cases_by_sku, last_year_cases_by_sku, total_year_dollars_by_sku, last_year_dollars_by_sku, operator_name, novalytics_id, eai_exportId ) select #qmd_batch.ownerId# as ownerId, '#qmd_batch.ownerType#' as ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by op.sku,op.skuDesc, op.operatorName, op.operatorId ) as eai_lineNumber, op.sku, op.skuDesc as sku_description, year( DATEADD(month, -1, op.latestPurchaseMonth)) as sales_year, op.confirmed_ytd_cases as total_year_cases_by_sku, op.confirmed_lytd_cases as last_year_cases_by_sku, op.confirmed_ytd_dollars as total_year_dollars_by_sku, op.confirmed_lytd_dollars as last_year_dollars_by_sku, op.operatorName as operator_name, op.operatorId as novalytics_id, m.novalytics_history_id from tbl_DW_OperatorPurchasing_MFR207 op with (nolock) inner join tbl_EXPORT_KELLANOVA_Opr_SKU_Map m with (nolock) on m.sku = op.sku and m.operatorId = op.operatorId and m.ownerId = and m. ownerType = order by skuDesc, operatorName SET IDENTITY_INSERT dbo.tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data OFF; declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @currYear int; declare @lastYear int; select @currYear = max( fiscalYear ) from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0; if @currYear is null begin select @currYear = fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y' and getDate() between startDate and endDate end if @currYear is null begin set @currYear = Year( getDate() ); end set @lastYear = @currYear - 1; insert into tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data( ownerId, ownerType, eai_batchId, eai_lineNumber, sku, sku_description, sales_year, total_year_cases_by_sku, last_year_cases_by_sku, total_year_dollars_by_sku, last_year_dollars_by_sku, operator_name, novalytics_id ) select #qmd_batch.ownerId# as ownerId, '#qmd_batch.ownerType#' as ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by sku,skuDesc, operatorName, operatorId ) as eai_lineNumber, sku, skuDesc as sku_description, year( DATEADD(month, -1, latestPurchaseMonth)) as sales_year, confirmed_ytd_cases as total_year_cases_by_sku, confirmed_lytd_cases as last_year_cases_by_sku, confirmed_ytd_dollars as total_year_dollars_by_sku, confirmed_lytd_dollars as last_year_dollars_by_sku, operatorName as operator_name, operatorId as novalytics_id from tbl_DW_OperatorPurchasing_MFR207 op with (nolock) where not exists (select m.operatorId, m.sku from tbl_EXPORT_KELLANOVA_Opr_SKU_Map m with (nolock) where m.sku = op.sku and m.operatorId = op.operatorId and m.ownerId = and m.ownerType = ) order by skuDesc, operatorName update tbl_EAI_InboundBatches set loadedColumns = 'sku,sku_description,sales_year,total_year_cases_by_sku,last_year_cases_by_sku,total_year_dollars_by_sku,last_year_dollars_by_sku,operator_name,novalytics_id' where ownerId = and ownertype = and batchId =

Exported #lsnumberformat( _exportCount )# Operator Purchasing Data updated on or after #lsdateFormat( _cutoffDate, "MM/dd/yyyy" )#.

select * from tbl_EXPORT_KELLANOVA_Opr_Purchasing_Data with (nolock) where ownerId = and ownertype = and eai_batchId = order by eai_lineNumber select novalytics_history_id from tbl_EXPORT_KELLANOVA_Opr_SKU_Map with (nolock) where ownerId = and ownerType = and sku = and operatorId = and novalytics_history_id = insert into tbl_EXPORT_KELLANOVA_Opr_SKU_Map( ownerId, ownerType, sku, operatorId, novalytics_history_id ) values ( , , , , )