Here's a possible re-write of the first temp table INSERT:

insert into #temp_violation

select

tv.OrderID

,tv.StartDate

,tv.EndDate

,ca1.HourID

from EMSTBLSN.dbo.TimeViolation tv

cross apply (

select distinct fh.HourID

from G4SFEP1.dbo.Service fs

join G4SFEP1.dbo.Hours fhon fh.ServiceID= fs.ServiceID

where

fs.LocationID = tv.SiteID and

exists(

select 1

from test.dbo.FilteredOrders fo

where fo.OrderId = fs.OrderID

)

) as ca1

where

tv.ExcludeFromATV= 0