select ownerId, ownerType, ftpServer, ftpUserName, ftpPassword, ftpDirectory, connectionName from tbl_EAI_inboundFTPSources where ownerId = 709 and ownerType = 'MFR' and outbound = 'Y' and connectionName like 'MFR_SALES_DATA_EXPORT BROKER:%' declare @mfrId int = 709; declare @ownerId int = 709; declare @ownerType varchar(3) = 'MFR'; declare @endYear int; declare @startYear int; select @endYear = max( fiscalYear ) from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0; if @endYear is null begin select @endYear = fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y' and getDate() between startDate and endDate end if @endYear is null begin set @endYear = Year( getDate() ); end set @startYear = @endYear - 2; with distributors as ( select ter.ownerId, ter.fsl_tablecode, ter.internalSalesId, ter.name territoryName, cdr.cdr_recordId, cdr.cdr_dstCompanyType, cdr.cdr_dstCode, cdr.cdr_dstName, cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, cdr.cdr_recordId tpf_cdr_recordId, cdr.cdr_specialAcctType from tbl_CDR_Distributors cdr with (nolock) 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 inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.mfr_id = cdr.ownerId and sma.territoryId = ter.territoryId and sma.primaryOffice = 'Y' where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_dstCompanyType = 'B' and sma.brokerid = union select ter.ownerId, ter.fsl_tablecode, ter.internalSalesId, ter.name territoryName, rda.cdr_recordId, cdr.cdr_dstCompanyType, cdr.cdr_dstCode, cdr.cdr_dstName, cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, rda.cdr_recordId tpf_cdr_recordId, cdr.cdr_specialAcctType from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId inner join tbl_CDR_Distributors rda with (nolock) on rda.ownerId = cdr.ownerId and rda.fsl_tablecode = cdr.fsl_tablecode and rda.fsl_dstId = dst.dstId and rda.cdr_dstCompanyType = 'S' and rda.cdr_specialAcctType = 'RDA' 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 inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.mfr_id = cdr.ownerId and sma.territoryId = ter.territoryId and sma.primaryOffice = 'Y' where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_dstCompanyType = 'B' and sma.brokerid = ), products as ( select s.sku, s.skuDesc, p.product, p.shortDesc, b.brand, s.ownerId, s.fsl_tablecode, s.skuId from tbl_PRD_SKUS s with (nolock) inner join tbl_OFS_BrokerSkus bs with (nolock) on bs.ownerId = s.ownerId and bs.ownerType = s.fsl_tablecode and bs.skuId = s.skuId inner join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode inner join tbl_MFR_Brands b with (nolock) on b.brand_Id = s.brandId where s.ownerId = @ownerId and s.fsl_tablecode = @ownerType ) select mfr.mfr_name, convert( int, tpf.forecast_year ) forecast_year, convert( int, tpf.forecast_month ) forecast_month, products.sku, products.skuDesc, products.brand, products.product, products.shortDesc, distributors.cdr_dstCode, distributors.cdr_dstName, distributors.cdr_dstAddress1, distributors.cdr_dstCity, distributors.cdr_dstState, distributors.cdr_dstZip, distributors.territoryName, distributors.internalSalesId, products.skuId, fp.fiscalYear, fp.monthIndex fiscalMonth, case when distributors.cdr_specialAcctType = 'RDA' then 'INDIRECT' else 'DIRECT' end purchasingMethod, sum( tpf.forecast_qty ) as lbs, 0 cases 0 lbs, sum( tpf.forecast_qty ) as cases from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = tpf.mfr_Id inner join products on products.skuId = tpf.skuId and products.ownerId = tpf.mfr_id and products.fsl_tablecode = @ownerType inner join distributors on distributors.tpf_cdr_recordId = tpf.tPartnerId and distributors.ownerId = tpf.mfr_id and distributors.fsl_tablecode = @ownerType inner join tbl_CRM_FiscalPeriods fp with (nolock) on fp.ownerId = tpf.mfr_id and fp.ownerType = 'MFR' and fp.periodType = 'M' and fp.fiscalStartMonth = dateFromParts( cast( tpf.forecast_year as int ), cast( tpf.forecast_month as int ), 1 ) and fp.fiscalYear between @startYear and @endYear where tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'A' and tpf.productlevel = 'SKU' and tpf.mfr_id = @ownerId and convert ( int, tpf.forecast_year ) >= group by mfr.mfr_name, tpf.forecast_year, tpf.forecast_month, products.skuId, products.sku, products.skuDesc, products.product, products.shortDesc, products.brand, distributors.cdr_dstCode, distributors.cdr_dstName, distributors.cdr_dstAddress1, distributors.cdr_dstCity, distributors.cdr_dstState, distributors.cdr_dstZip, distributors.territoryName, distributors.internalSalesId, fp.fiscalYear, fp.monthIndex, distributors.cdr_specialAcctType select * from qmd_salesData where cdr_dstName = 'SYSCO - SYRACUSE - FZ' order by mfr_name, forecast_year, forecast_month, cdr_dstName, product, sku