select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$NXT_MANUFACTURER_EXPORT_CSV' and batchTime < and status = 'OK' order by batchTime desc if object_id('[#_tempTable#]', 'U') is not null drop table [#_tempTable#]; with export as ( SELECT m.mfr_id, m.mfr_name, pc.alias as mfr_code, t.name as territory_name, t.territoryid as territory_id, case when p.priority = '*' then 'A+' else p.priority end as priority, cl.name as classification, pt.hireStatus as territory_status, case when isDate(pt.hireDate) = 1 then cast(pt.hireDate as date) else null end as territory_hire_date, erm.fspro_userId as relationship_manager_id, erm.lastname as relationship_manager_lastname, erm.firstname as relationship_manager_firstname, erm.email as relationship_manager_email, rclientMgr.fspro_userId as region_relmanager_id, rclientMgr.lastname as region_relmanager_lastname, rclientMgr.firstname as region_relmanager_firstname, rclientMgr.email as region_manager_email, terMgr.fspro_userId as ter_relmanager_id, terMgr.lastname as ter_relmanager_lastname, terMgr.firstname as ter_relmanager_firstname, terMgr.email as ter_relmanager_email FROM tbl_BRO_Principals p with (nolock) INNER JOIN tblManufacturers m with (nolock) on p.mfrid = m.mfr_id INNER JOIN tbl_BRO_PrincipalTerritories pT with (nolock) on p.principalId = pT.principalId INNER JOIN tbl_BRO_PrincipalTerritoryResources tR with (nolock) on pT.principalTerritoryId = tR.principalTerritoryId AND tR.resourceType = 'TER' AND tR.ownerType = 'BRO' INNER JOIN tbl_TER_Territories t with (nolock) on tR.resourceId = t.TerritoryID INNER JOIN tbl_TER_TerritoryLevels tL with (nolock) on t.LevelID = tL.LevelID INNER JOIN tblBrokers b with (nolock) on t.OwnerID = b.BROKER_ID LEFT OUTER JOIN tbl_IMPORT_ALiases pc with (nolock) on p.mfrid = pc.objectId and p.ownerId = pc.ownerid and p.ownerType = pc.ownerType and pc.objectType = 'MFRID' LEFT OUTER JOIN tbl_BPL_Classifications cl with (nolock) on p.classificationId = cl.classificationId and p.ownerId = cl.ownerid and p.ownerType = cl.ownerType LEFT OUTER JOIN tbl_FSPro_Members terMgr ON terMgr.fspro_Userid = t.primaryManager AND terMgr.ownerId = t.ownerId AND terMgr.fsl_tablecode = t.FSL_tableCode AND terMgr.staffMember = 1 /* LEFT JOIN tbl_TER_Territories AS rgn WITH (nolock) ON t.FSL_TableCode = rgn.FSL_TableCode AND t.ownerId = rgn.ownerId AND t.parentTerritoryId = rgn.territoryId LEFT JOIN tbl_FSPro_Members AS rgnMgr WITH (nolock) ON rgnMgr.FSPro_UserId IN (rgn.PrimaryManager) */ LEFT JOIN tbl_BRO_PrincipalRegionalClientManagers AS crMGR WITH (nolock) ON p.ownerType = crMGR.ownerType AND p.ownerId = crMGR.ownerId AND p.principalId = crMGR.principalId AND t.parentTerritoryId = crMGR.brokerRegionId LEFT JOIN tbl_FSPro_Members AS rclientMgr WITH (nolock) ON rclientMgr.FSPro_UserId IN (crMGR.brokerRegionalClientManagerId) LEFT OUTER JOIN tbl_FSPro_Members erm ON erm.fspro_Userid = p.customerManagerUserId AND erm.ownerId = p.ownerId AND erm.fsl_tablecode = p.ownerType AND erm.staffMember = 1 WHERE p.ownerType = AND p.ownerId = AND tL.BrokerLevel = 1 ) select * into [#_tempTable#] from export

Exported #lsnumberformat( _exportCount )# Manufacturers

update tbl_EAI_inboundBatches set fileName = , ttlDays = 365 where batchId = and ownerId = and ownerType =