select formatId from tbl_EAI_inboundFileFormats with (nolock) where ownerId = and ownerType = and formatName = select formatId from tbl_EAI_inboundFileFormats with (nolock) where ownerId = 0 and ownerType = and formatName = select ownerId, ownerType, ftpServer, ftpUserName, ftpPassword, ftpDirectory, connectionName from tbl_EAI_inboundFTPSources where ownerId = and ownerType = and outbound = 'Y' and connectionName like 'MFR_SALES_DATA_EXPORT BROKER:%'

Timeframe: #lsdateformat( _timeframeStart, "Mmmm YYYY" )# - #lsdateformat( _timeframeEnd, "Mmmm YYYY" )#

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 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 = and cdr.fsl_tablecode = 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 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 = and cdr.fsl_tablecode = 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 = and s.fsl_tablecode = ) 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, 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 = inner join distributors on distributors.tpf_cdr_recordId = tpf.tPartnerId and distributors.ownerId = tpf.mfr_id and distributors.fsl_tablecode = where tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'A' and tpf.productlevel = 'SKU' and tpf.mfr_id = 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 select * from qmd_salesData where ( ( forecast_year = and forecast_month >= ) or ( forecast_year = and forecast_month <= ) ) order by mfr_name, forecast_year, forecast_month, cdr_dstName, product, sku update tbl_EAI_inboundBatches set fileName = where batchId = and ownerId = and ownerType =

#htmleditformat( _remoteFile )# sent to via ftp to #htmleditformat( ftpUserName )#@#htmleditformat( ftpServer )#

#htmleditformat( listLast( _batchFilePath, "\" ) )# NOT sent to via ftp to #htmleditformat( ftpUserName )#@#htmleditformat( ftpServer )# - development run