This is only for brokers update tbl_EAI_inboundBatches set message = 'Preparing Foodmark Data', statusTime = getDate() where ownerId = and ownerType = and batchId = update tbl_EAI_inboundBatches set message = 'Processing...', statusTime = getDate() where ownerId = and ownerType = and batchId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @batchTime datetime = ; declare @lastRun datetime = ( select max( batchTime ) from tbl_EAI_InboundBatches where ownerId = @ownerId and ownerType = @ownerType and status = 'OK' and fileFormat = '$FoodmarkDistributorSalesETL' and batchTime < @batchTime ); declare @lastRunUTC datetime = ( select @lastRun at time zone 'Eastern Standard Time' at time zone 'UTC' as lastRunUTC ); with manufacturers as ( select fse_mfr_id, max( record_date ) lastUpdatedUTC from [tbl_FM_orderSummary_#qmd_batch.ownerType##qmd_batch.ownerId#] with (nolock) where record_status = 'X' group by fse_mfr_id ) select * from manufacturers where case when @lastRunUTC < lastUpdatedUTC then 1 else 0 end = 1 order by fse_mfr_id select mfr_id, mfr_name from tblManufacturers mfr with (nolock) where mfr_id not in ( 6868, 6500 ) and mfr_id not in ( 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 ) and mfr_id in ( select distinct bpl.mfrId from tbl_BRO_Principals bpl with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId where bpl.ownerId = and bpl.ownerType = and pt.hireStatus = 'A' and bpl.mfrId in ( ) and 1 = 2 ) and mfr_id = order by mfr_name select name from sys.columns where object_id = (select object_id from sys.tables where name = 'tbl_DW_DistributorSales_MFR0') and name in ( ) order by column_id select top 1 cdr_recordId from tbl_CDR_FoodmarkOrderData#_tableSuffix# with (nolock) where mfr_id = declare @startMonth date = ; declare @endMonth date = ; delete from tbl_DW_DistributorSales#_tableSuffix# where mfr_id = and ( salesPeriodType = 'MONTH' and salesPeriod >= @startMonth ) and ( salesPeriodType = 'MONTH' and salesPeriod <= @endMonth )

#htmleditformat( _r.sql )#

update tbl_EAI_inboundBatches set message = , statusTime = getDate() where ownerId = and ownerType = and batchId = drop table if exists [#_mfrSalesTable#]; declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @mfrId int = ; declare @startMonth date = ; declare @endMonth date = ; with mfrSales as ( select fd.cdr_recordId, fd.skuId, cs.customerSKU, cs.customerType as customerSKUType, fd.salesPeriod, fd.salesPeriodType, fd.skuLastInvoiceDate lastInvoiceDate, fd.skuLastInvoiceCases lastInvoiceCases, fd.skuLastInvoiceLbs lastInvoiceLbs, fd.skuLastInvoiceDollars lastInvoiceDollars, sum( fd.cases ) cases, sum( fd.lbs ) lbs, sum( fd.dollars ) dollars, sum( fd.commission ) commission, sum( fd.alt ) alt from tbl_CDR_FoodmarkOrderData#_tableSuffix# fd with (nolock) left outer join tbl_DW_CustomerSKUs#_tableSuffix# cs with (nolock) on cs.ownerId = fd.ownerId and cs.ownerType = fd.ownertype and cs.skuId = fd.skuId and cs.cdr_recordId = fd.cdr_recordId where fd.salesPeriodType = 'MONTH' and ( salesPeriod >= @startMonth ) and ( salesPeriod <= @endMonth ) and fd.mfr_id = @mfrId group by fd.cdr_recordId, fd.skuId, fd.salesPeriod, fd.salesPeriodType, fd.skuLastInvoiceDate, fd.skuLastInvoiceCases, fd.skuLastInvoiceLbs, fd.skuLastInvoiceDollars, cs.customerSKU, cs.customerType ) select * into [#_mfrSalesTable#] from mfrSales

#htmleditformat( _r.sql )#

declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @mfrId int = ; declare @startMonth date = ; declare @endMonth date = ; with buyingGroupsRaw as ( select afl.affiliationDate, byg.uniqueId as buyingGroupId, byg.name as buyingGroup, cdr.cdr_recordId, cdr.cdr_dstName, row_number() over ( partition by cdr_recordId order by cdr.cdr_recordId, afl.affiliationDate desc, byg.name ) as seq from tbl_BYG_BuyingGroups byg with (nolock) inner join tbl_CRM_Affiliations afl with (nolock) on afl.ownerId= 66 and afl.ownerType = 'MSC' and afl.orgType = 'BYG' and afl.partnerType = 'DST' and afl.affiliationStatus = 'A' and afl.orgId = byg.uniqueId inner join tbl_CDR_Distributors cdr with (nolock) on cdr.fsl_dstId = afl.partnerId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ) , buyingGroups as ( select buyingGroupId, buyingGroup, cdr_recordId from buyingGroupsRaw where seq = 1 ) , mappedDistributors as ( select cdr_recordId, ownerId, fsl_tablecode, cdr_dstName, cdr_dstCompanyType, fsl_dstId, cdr_dstAddress1, cdr_dstCity, cdr_dstState, cdr_dstZip, mfr_bsr_id, cdr_dstPath, cdr_territoryId, row_number() over( partition by cdr_recordId order by fsl_mapPriority, cdr_recordId ) as fsl_mapPriority from tbl_CDR_Distributors mcdr with (nolock) where ownerId = @ownerId and fsl_tablecode = @ownerType ), distributorsRaw as ( select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId sales_cdr_recordId, cdr.cdr_dstName sales_cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_recordId else cdr.cdr_recordId end as cdr_recordId, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstName else cdr.cdr_dstName end as cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstAddress1 else cdr.cdr_dstAddress1 end as cdr_dstAddress1, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstCity else cdr.cdr_dstCity end as cdr_dstCity, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstState else cdr.cdr_dstState end as cdr_dstState, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstZip else cdr.cdr_dstZip end as cdr_dstZip, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstPath else cdr.cdr_dstPath end as cdr_dstPath, case when mcdr.cdr_recordId is not null then mcdr.mfr_bsr_id else cdr.mfr_bsr_id end as accountOwner_fspro_userId, case when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'B' then 'DISTRIBUTOR' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'R' then 'REDISTRIBUTOR' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'S' then 'SPECIAL' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'P' then 'PARENT' when coalesce( mcdr.cdr_dstCompanyType, cdr.cdr_dstCompanyType ) = 'O' then 'OPERATOR' else 'UNKNOWN' end as customerType, cdr.fsl_dstId, cdr.cdr_dstCompanyType, cdr.cdr_redistAcctFor, cdr.cdr_specialAcctType, cdr.cdr_dstCode, cdr.cdr_territoryId from tbl_CDR_Distributors cdr with (nolock) left outer join mappedDistributors mcdr on mcdr.ownerId = cdr.ownerId and mcdr.fsl_tablecode = cdr.fsl_tablecode and mcdr.fsl_mapPriority >= 1 and mcdr.cdr_recordId = cdr.cdr_recordId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ), distributors as ( select cdr.*, ter.territoryId, ter.name as territoryName, ter.territoryPath, ter.regionName, ter.divisionName, pcdr.cdr_recordId as parent_cdr_recordId, nullif( pcdr.fsl_dstId, 0 ) parent_oneFsId, pcdr.cdr_dstCode as parent_cdr_dstCode, pcdr.cdr_dstName parent_cdr_dstName, pcdr.cdr_dstCompanyType parent_cdr_dstCompanyType, byg.buyingGroupId, byg.buyingGroup from distributorsRaw cdr inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = cdr.ownerId and ter.fsl_tablecode= cdr.fsl_tablecode and ter.territoryId= cdr.cdr_territoryId left outer join buyingGroups byg on byg.cdr_recordId = cdr.cdr_recordId left join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_DST_Distributors pdst with (nolock) on pdst.dstId = dst.dstParentCo left outer join tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstId = pdst.dstId and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode= cdr.fsl_tablecode ), products as ( select ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catNum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, ph.focusProduct, ph.privateLabelType, ph.productHierarchyPath, ph.budgetCategoryId, ph.budgetCategory, ph.brand, ph.brandId, coalesce( ph.discontinued, 'N' ) discontinued, ph.mfrSkuId, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name from tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) where ph.mfr_id = @mfrId ), etl as ( select #qmd_batch.ownerId# ownerId, '#qmd_batch.ownerType#' ownerType, sales.salesPeriod, sales.salesPeriodType, sales.cases, sales.lbs, sales.dollars, sales.commission, sales.alt, ph.skuId, ph.sku, ph.skuDesc, ph.prodId, ph.product, ph.shortDesc, ph.categoryId, ph.catNum, ph.category, ph.prodline_id, ph.productLineCode, ph.productLine, ph.focusProduct, ph.privateLabelType, ph.productHierarchyPath, ph.budgetCategoryId, ph.budgetCategory, ph.brand, ph.brandId, ph.discontinued, ph.mfrSkuId, ph.mfr_id, ph.mfr_name, ph.MFRAbbrev_Name, cdr.sales_cdr_recordId,cdr.sales_cdr_dstName,cdr.cdr_recordId,cdr.cdr_dstName,cdr.cdr_dstAddress1,cdr.cdr_dstCity,cdr.cdr_dstState,cdr.cdr_dstZip,cdr.cdr_dstPath,cdr.accountOwner_fspro_userId,cdr.customerType,cdr.fsl_dstId oneFsId,cdr.cdr_dstCompanyType,cdr.cdr_redistAcctFor,cdr.cdr_specialAcctType,cdr.cdr_dstCode,cdr.cdr_territoryId as territoryId,cdr.territoryName,cdr.territoryPath,cdr.regionName,cdr.divisionName,cdr.parent_cdr_recordId,cdr.parent_oneFsId,cdr.parent_cdr_dstCode,cdr.parent_cdr_dstName,cdr.parent_cdr_dstCompanyType,cdr.buyingGroupId,cdr.buyingGroup, m.firstName as accountOwner_firstName, m.lastName as accountOwner_lastName, m.email accountOwner_email, 'DIRECT' purchasingMethod, fp.fiscalYear, fp.periodIndex as fiscalMonth, 'BRO' sourceOfData, lastInvoiceDate, lastInvoiceCases, lastInvoiceLbs, lastInvoiceDollars, sales.customerSKU, sales.customerSKUType from [#_mfrSalesTable#] sales with (nolock) inner join products ph on ph.skuId = sales.skuId inner join distributors cdr on cdr.sales_cdr_recordId = sales.cdr_recordId inner join tbl_CRM_FiscalPeriods fp with (nolock) on fp.ownerId = @ownerId and fp.ownerType = @ownerType and fp.periodType = 'M' and fp.fiscalStartMonth = sales.salesPeriod left outer join tbl_FSPRO_Members m with (nolock) on m.fspro_userId = cdr.accountOwner_fspro_userId and m.ownerId = @ownerId and m.fsl_tablecode = @ownerType ) insert into tbl_DW_DistributorSales#_tableSuffix# ( #_columnList# ) select #_columnList# from etl drop table if exists [#_mfrSalesTable#];

#htmleditformat( _r.sql )#

#htmleditformat( mfr_name )# - #lsnumberformat( _r.recordCount )# records - #lsnumberformat( _endTicks - _startTicks )#ms.

update ds set ds.customerSKU = cs.customerSKU, ds.customerSkuType = cs.customerType from tbl_DW_DistributorSales#_tableSuffix# ds inner join tbl_DW_CustomerSKUs#_tableSuffix# cs on cs.skuId = ds.skuId and cs.cdr_recordId = ds.cdr_recordId where (( ds.customerSKU != nullif( cs.customerSKU, '' )) or ( ds.customerSKU is null and nullif( cs.customerSKU, '' ) is not null )) and ds.mfr_id = #_mfrId#

#htmleditformat( _r.sql )#

#htmleditformat( mfr_name )# - Customer SKU Changes - #lsnumberformat( _r.recordCount )# records. #lsnumberformat( _endTicks - _startTicks )#ms.

update ds set ds.customerSKU = null, ds.customerSkuType = null from tbl_DW_DistributorSales#_tableSuffix# ds where nullif( ds.customerSKU, '' ) is not null and ds.mfr_id = #_mfrId# and not exists ( select * from tbl_DW_CustomerSKUs#_tableSuffix# cs where cs.skuId = ds.skuId and cs.cdr_recordId = ds.cdr_recordId )

#htmleditformat( _r.sql )#

#htmleditformat( mfr_name )# - Customer SKU Removals - #lsnumberformat( _r.recordCount )# records. #lsnumberformat( _endTicks - _startTicks )#ms.

#htmleditformat( mfr_name )# - Update Last Invoice. #lsnumberformat( _endTicks - _startTicks )#ms.

#htmleditformat( mfr_name )# - Update Stocking. #lsnumberformat( _endTicks - _startTicks )#ms.