select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$KELLANOVA_DIST_PURCHASING_DATA_EXPORT' and batchTime < and status = 'OK' order by batchTime desc delete from tbl_EXPORT_KELLANOVA_Dist_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_Dist_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; with distributors1 as ( SELECT cdr1.ownerId, cdr1.fsl_tableCode as ownerType, cdr1.cdr_dstName, cdr1.fsl_dstId, case when cdr1.FSL_mapPriority <= cdr2.FSL_mapPriority then COALESCE(NULLIF(cdr1.cdr_dstCode, ''), cdr2.cdr_dstCode) else COALESCE(NULLIF(cdr2.cdr_dstCode, ''), cdr1.cdr_dstCode) end as distCode, case when cdr1.FSL_mapPriority >= cdr2.FSL_mapPriority then cdr1.cdr_dstCode else cdr2.cdr_dstCode end as alt_distCode FROM tbl_CDR_Distributors cdr1 with (nolock) inner join tbl_CDR_Distributors cdr2 with (nolock) on cdr2.ownerId = cdr1.ownerId and cdr2.fsl_tableCode = cdr1.fsl_tableCode and cdr2.fsl_dstId = cdr1.fsl_dstId and cdr2.cdr_dstcompanytype <> cdr1.cdr_dstcompanytype and cdr2.cdr_dstcompanytype = 'S' where 1=1 and cdr1.ownerId = and cdr1.fsl_tableCode = and cdr1.fsl_dstId > 0 ), distributors as ( SELECT ownerId, ownerType, cdr_dstName, fsl_dstId, distCode , STRING_AGG(alt_distCode, ', ') AS alt_distCode FROM distributors1 GROUP BY ownerId, ownerType, cdr_dstName, fsl_dstId, distCode ) , salesRaw as ( select etl.skuDesc as sku_description, etl.cdr_dstCode as distributor_code, d.distCode, etl.cdr_dstName as distributor_name, etl.sku, etl.lastInvoiceDate as distributor_sku_last_invoice_date, etl.onefsId , year( etl.salesPeriod ) as sales_year, etl.cases, etl.lbs, etl.dollars from tbl_DW_DistributorSales_MFR207 etl with (nolock) inner join distributors d on (d.distCode = etl.cdr_dstCode or etl.cdr_dstCode in (d.alt_distCode ) ) where salesPeriodType = 'MONTH' and year( salesPeriod ) in ( @lastYear, @currYear ) ), salesSummaryRaw as ( select sku_description, distCode as distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sales_year, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars from salesRaw group by sku_description, distCode, distributor_name, sku, distributor_sku_last_invoice_date, sales_year ), salesData as ( select sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sum( case when sales_year = @currYear then cases else 0.0 end ) as this_year_volume_cases, sum( case when sales_year = @lastYear then cases else 0.0 end ) as last_year_volume_cases, sum( case when sales_year = @currYear then dollars else 0.0 end ) as this_year_volume_dollars,sum( case when sales_year = @lastYear then dollars else 0.0 end ) as last_year_volume_dollars from salesSummaryRaw group by sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date ), finalSalesData as ( select ss.*, m.novalytics_id from salesData ss inner join tbl_EXPORT_KELLANOVA_Dist_SKU_Map m with (nolock) on m.sku = ss.sku and m.distributor_code = ss.distributor_code and m.ownerId = and m. ownerType = ) insert into tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data( ownerId, ownerType, eai_batchId, eai_lineNumber, sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sales_year, this_year_volume_cases, last_year_volume_cases, this_year_volume_dollars, last_year_volume_dollars, eai_exportId ) select #qmd_batch.ownerId# as ownerId, '#qmd_batch.ownerType#' as ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by sku_description, distributor_code, distributor_name, sku ) as eai_lineNumber, sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, @currYear as sales_year, this_year_volume_cases,last_year_volume_cases, this_year_volume_dollars,last_year_volume_dollars, novalytics_id from finalSalesData SET IDENTITY_INSERT dbo.tbl_EXPORT_KELLANOVA_Dist_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; with distributors1 as ( SELECT cdr1.ownerId, cdr1.fsl_tableCode as ownerType, cdr1.cdr_dstName, cdr1.fsl_dstId, case when cdr1.FSL_mapPriority <= cdr2.FSL_mapPriority then COALESCE(NULLIF(cdr1.cdr_dstCode, ''), cdr2.cdr_dstCode) else COALESCE(NULLIF(cdr2.cdr_dstCode, ''), cdr1.cdr_dstCode) end as distCode, case when cdr1.FSL_mapPriority >= cdr2.FSL_mapPriority then cdr1.cdr_dstCode else cdr2.cdr_dstCode end as alt_distCode FROM tbl_CDR_Distributors cdr1 with (nolock) inner join tbl_CDR_Distributors cdr2 with (nolock) on cdr2.ownerId = cdr1.ownerId and cdr2.fsl_tableCode = cdr1.fsl_tableCode and cdr2.fsl_dstId = cdr1.fsl_dstId and cdr2.cdr_dstcompanytype <> cdr1.cdr_dstcompanytype and cdr2.cdr_dstcompanytype = 'S' where 1=1 and cdr1.ownerId = and cdr1.fsl_tableCode = and cdr1.fsl_dstId > 0 ), distributors as ( SELECT ownerId, ownerType, cdr_dstName, fsl_dstId, distCode , STRING_AGG(alt_distCode, ', ') AS alt_distCode FROM distributors1 GROUP BY ownerId, ownerType, cdr_dstName, fsl_dstId, distCode ) , salesRaw as ( select etl.skuDesc as sku_description, etl.cdr_dstCode as distributor_code, d.distCode, etl.cdr_dstName as distributor_name, etl.sku, etl.lastInvoiceDate as distributor_sku_last_invoice_date, etl.onefsId , year( etl.salesPeriod ) as sales_year, etl.cases, etl.lbs, etl.dollars from tbl_DW_DistributorSales_MFR207 etl with (nolock) inner join distributors d on (d.distCode = etl.cdr_dstCode or etl.cdr_dstCode in (d.alt_distCode ) ) where salesPeriodType = 'MONTH' and year( salesPeriod ) in ( @lastYear, @currYear ) ), salesSummaryRaw as ( select sku_description, distCode as distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sales_year, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars from salesRaw group by sku_description, distCode, distributor_name, sku, distributor_sku_last_invoice_date, sales_year ), salesData as ( select sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date,sales_year, sum( case when sales_year = @currYear then cases else 0.0 end ) as this_year_volume_cases, sum( case when sales_year = @lastYear then cases else 0.0 end ) as last_year_volume_cases, sum( case when sales_year = @currYear then dollars else 0.0 end ) as this_year_volume_dollars,sum( case when sales_year = @lastYear then dollars else 0.0 end ) as last_year_volume_dollars from salesSummaryRaw group by sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date,sales_year ), finalSalesData as ( select ss.*, m.novalytics_id from salesData ss left outer join tbl_EXPORT_KELLANOVA_Dist_SKU_Map m with (nolock) on m.sku = ss.sku and m.distributor_code = ss.distributor_code and m.ownerId = and m. ownerType = ) select #qmd_batch.ownerId# as ownerId, '#qmd_batch.ownerType#' as ownerType, #qmd_batch.batchId# as eai_batchId, row_number() over( order by sku_description, distributor_code, distributor_name, sku ) as eai_lineNumber, sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, @currYear as sales_year, this_year_volume_cases,last_year_volume_cases, this_year_volume_dollars,last_year_volume_dollars from finalSalesData where novalytics_id is null select novalytics_id from tbl_EXPORT_KELLANOVA_Dist_SKU_Map with (nolock) where ownerId = and ownerType = and sku = and distributor_code = insert into tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data( ownerId, ownerType, eai_batchId, eai_lineNumber, sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sales_year, this_year_volume_cases, last_year_volume_cases, this_year_volume_dollars, last_year_volume_dollars ) values ( , , , , , , , , , , , , , ) insert into tbl_EXPORT_KELLANOVA_Dist_SKU_Map( ownerId, ownerType, sku, distributor_code, novalytics_id ) values ( , , , , ) SET IDENTITY_INSERT dbo.tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data ON; insert into tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data( ownerId, ownerType, eai_batchId, eai_lineNumber, sku_description, distributor_code, distributor_name, sku, distributor_sku_last_invoice_date, sales_year, this_year_volume_cases, last_year_volume_cases, this_year_volume_dollars, last_year_volume_dollars, eai_exportId ) values ( , , , , , , , , , , , , , , ) SET IDENTITY_INSERT dbo.tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data OFF; ---> update tbl_EAI_InboundBatches set loadedColumns = 'sku_description,distributor_code,distributor_name,sku,distributor_sku_last_invoice_date,sales_year,this_year_volume_cases,last_year_volume_cases,this_year_volume_dollars,last_year_volume_dollars' where ownerId = and ownertype = and batchId = select * from tbl_EXPORT_KELLANOVA_Dist_Purchasing_Data with (nolock) where ownerId = and ownertype = and eai_batchId = order by eai_lineNumber
Exported #lsnumberformat( _exportCount )# Distributor Purchasing Data updated on or after #lsdateFormat( _cutoffDate, "MM/dd/yyyy" )#.