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 = --->

Timeframe: #lsdateformat( _startDay, "Mmmm YYYY" )# - #lsdateformat( _endDay, "Mmmm YYYY" )#

select syndicationSetId, syndicationSetName from tbl_PRD_SyndicationSets where ownerId = and ownertype = and syndicationSetName = '1FS Broker' order by systemSet select distinct brokerId from tbl_SMA_BrokerOfficeLink with (nolock) where mfr_id = with distributors as ( select distinct ter.ownerId, ter.fsl_tablecode, ter.internalSalesId, ter.name territoryName, cdr.cdr_recordId, cdr.cdr_dstCompanyType, case when coalesce(cdrBro.cdr_dstCode, '') = '' then cdr.cdr_dstCode else cdrBro.cdr_dstCode end as cdr_dstCode, cdr.cdr_dstName,cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, cdr.cdr_recordId tpf_cdr_recordId, bro.ERPClientCode 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' inner join tblBrokers bro with (nolock) on bro.broker_id = sma.brokerId left outer join tbl_CDR_Distributors cdrBro with (nolock) on cdrBro.ownerId = bro.broker_id and cdrBro.fsl_tableCode = 'BRO' and cdrBro.cdr_dstCompanyType = cdr.cdr_dstCompanyType and cdrBro.fsl_dstId = cdr.fsl_dstId and cdrBro.fsl_dstId > 0 and cdrBro.fsl_mapPriority = 1 where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCompanyType = 'B' and sma.brokerid = union select distinct ter.ownerId, ter.fsl_tablecode, ter.internalSalesId, ter.name territoryName, rda.cdr_recordId, cdr.cdr_dstCompanyType, case when coalesce(cdrBro.cdr_dstCode, '') = '' then cdr.cdr_dstCode else cdrBro.cdr_dstCode end as cdr_dstCode, cdr.cdr_dstName,cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, rda.cdr_recordId tpf_cdr_recordId, bro.ERPClientCode 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' inner join tblBrokers bro with (nolock) on bro.broker_id = sma.brokerId left outer join tbl_CDR_Distributors cdrBro with (nolock) on cdrBro.ownerId = bro.broker_id and cdrBro.fsl_tableCode = 'BRO' and cdrBro.cdr_dstName = cdr.cdr_dstName and cdrBro.fsl_dstId = cdr.fsl_dstId and cdrBro.fsl_dstId > 0 and cdrBro.fsl_mapPriority = 1 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, s.ownerId, s.fsl_tablecode, s.skuId, case when coalesce(broSku.sku, '') = '' then s.sku else broSku.sku end as broSku, case when coalesce(broSku.skuDesc, '') = '' then s.skuDesc else broSku.skuDesc end as broSkuDesc, case when coalesce(bsp.scc14, '') = '' then msp.scc14 else bsp.scc14 end as gtin from tbl_PRD_SKUS s with (nolock) 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 left outer join tbl_PRD_SKUS broSku with (nolock) on broSku.sku = s.sku and broSku.ownerId = and broSku.fsl_tableCode = 'BRO' left outer join tbl_PRD_Packaging msp with (nolock) on msp.ownerId = s.ownerId and msp.fsl_tableCode = s.fsl_tablecode and msp.linkID = s.skuId and msp.linkType = 'SKU' left outer join tbl_PRD_Packaging bsp with (nolock) on bsp.ownerId = broSku.ownerId and bsp.fsl_tableCode = broSku.fsl_tablecode and bsp.linkID = broSku.skuId and bsp.linkType = 'SKU' where s.ownerId = and s.fsl_tablecode = and s.skuId in () ) select ala.alias as mfr_code, mfr.mfr_name, distributors.cdr_dstCode as dist_code, distributors.cdr_dstName as dist_name, inv.purchaseOrderNumber as po_Number, inv.invoiceNumber as invoice_number, inv.invoiceDate as invoice_date, products.sku as mfr_sku, products.skuDesc as mfr_sku_description, products.gtin as gtin_upc, products.broSku as mfr_item_code, products.broSkuDesc as item_description, convert( int, inv.forecast_year ) forecast_year, convert( int, inv.forecast_month ) forecast_month, distributors.cdr_dstAddress1 as mfr_inv_dist_address, distributors.cdr_dstCity as mfr_inv_dist_city, distributors.cdr_dstState as mfr_inv_dist_state, distributors.cdr_dstZip as mfr_inv_dist_zip, distributors.territoryName, distributors.internalSalesId,products.skuId,distributors.ERPClientCode as broker_code, sum(inv.dollarAmt) as net_amt, sum( inv.weightAmt ) as NetCatchWt, 0 Quantity 0 NetCatchWt, sum( inv.caseAmt ) as Quantity from tbl_CDR_InvoiceHistory inv with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = inv.ownerId and inv.ownerType = inner join tbl_IMPORT_Aliases ala with (nolock) on ala.objectId = mfr.mfr_id and ala.objectType = 'MFRID' inner join products on products.skuId = inv.skuId and products.ownerId = inv.ownerId and products.fsl_tablecode = inv.ownerType inner join distributors on distributors.tpf_cdr_recordId = inv.cdr_recordId and distributors.ownerId = inv.ownerId and distributors.fsl_tablecode = inv.ownerType where mfr.mfr_id = and ala.ownerid = and ala.OwnerType = and convert ( int, inv.forecast_year ) = and convert ( int, inv.forecast_month ) = group by ala.alias, mfr.mfr_name,distributors.cdr_dstCode, distributors.cdr_dstName, inv.purchaseOrderNumber, inv.invoiceNumber, inv.invoiceDate, products.skuId, products.sku, products.skuDesc, products.broSku, products.broSkuDesc,products.gtin, inv.forecast_year, inv.forecast_month, distributors.ERPClientCode, distributors.cdr_dstAddress1, distributors.cdr_dstCity, distributors.cdr_dstState, distributors.cdr_dstZip, distributors.territoryName, distributors.internalSalesId select broker_code, mfr_code, mfr_name, dist_name, dist_code, mfr_inv_dist_address, mfr_inv_dist_city, mfr_inv_dist_state,mfr_inv_dist_zip, po_number,po_date, invoice_number, invoice_date, mfr_item_code, gtin_upc, item_description, quantity, net_amt , mfr_sku, mfr_sku_description from qmd_salesData order by mfr_name, dist_name, mfr_item_code update tbl_EAI_inboundBatches set fileName = where batchId = and ownerId = and ownerType =