/**
* This view is for ProServ, used in custom reports. Don't rename the view without confirmation from ProServ.
*/

create view [dbo].[vLongDistanceDispatchGrid]
as

select    DriverLastName = drv.LASTNAME,
    DriverFirstName = drv.FIRSTNAME,
    DriverNumber = drv.DRVNO,
    OrderNumber = o.OrderNo,
    FromCity = oao.City,
    FromST = oao.State,
    ToCity = oad.City,
    ToST = oad.State,
    StartLoad = convert(varchar(10), o.StartLoad, 101),
    StartDelivery = convert(varchar(10), o.StartDeliv, 101),
    EndDelivery = convert(varchar(10), o.EndDeliv, 101),
    Linehaul = o.LineHaul,
    Status = o.OrderStatus,
    TripNo = t.TripNo,
    Weight = o.Weight,
    EndLoad = convert(varchar(10), o.EndLoad, 101),
    ActualDeliveryDate = convert(varchar(10), o.ActDelDate, 101),
    FromPostalCode = oao.PostalCode,
    ToPostalCode = oad.PostalCode,
    FromArea = fsa.Area,
    ToArea = tsa.Area,
    FromLocationType = alto.Description,
    ToLocationType = altd.Description,
    Commodity = ct.Commodity,
    MoveType = mt.MoveName,
    ServiceType = st.ServiceTypeDescription,
    ShipmentType = ship.ShipmentTypeDescription,
    Miles = o.Miles,
    CubicFeet = o.CubicFeet,
    LinearFeet = o.LinearFeet,
    SelfHaul = case when o.SelfHaul <> 'F' then 'Yes' else 'No' end,
    PreferredPack = convert(varchar(10), o.PreferredPack, 101),
    PreferredLoad = convert(varchar(10), o.PreferredLoad, 101),
    PreferredDelivery = convert(varchar(10), o.PreferredDeliv, 101),
    StartPack = convert(varchar(10), o.StartPack, 101),
    EndPack = convert(varchar(10), o.EndPack, 101),
    PackStartTime = case when year(o.PackTime) < 1960 then null else right('0' + ltrim(right(convert(varchar, o.PackTime, 100), 8)),7) end,
    PackEndTime = case when year(o.PackEndTime) < 1960 then null else right('0'+ltrim(right(convert(varchar, o.PackEndTime ,100),8)),7) end,
    LoadStartTime = case when year(o.LoadTime) < 1960 then null else right('0'+ltrim(right(convert(varchar, o.LoadTime ,100),8)),7) end,
    LoadEndTime = case when year(o.LoadEndTime) < 1960 then null else right('0'+ltrim(right(convert(varchar, o.LoadEndTime ,100),8)),7) end,
    DeliveryStartTime = case when year(o.DeliveryTime) < 1960 then null else right('0'+ltrim(right(convert(varchar, o.DeliveryTime ,100),8)),7) end,
    DeliveryEndTime = case when year(o.DeliveryEndTime) < 1960 then null else right('0'+ltrim(right(convert(varchar, o.DeliveryEndTime ,100),8)),7) end,
    Auto = case when o.Automobile <> 'F' then 'Yes' else 'No' end,
    HaulAgentID = ha.AgentID,
    HaulAgentName = ha.Name,
    NationalAccount = a.AcctName,
    Discount = o.Discount,
    OrderId = o.PriKey
from    Orders o
join    TripOrd tor on o.PriKey = tor.OrdPriKey
join    Trips t on tor.TripPriKey = t.PriKey
join    Sysuser drv on t.DriverEmpNo = drv.SysUserID
left join OrderAddress oao on o.PriKey = oao.OrderFID and oao.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Origin')
left join OrderAddress oad on o.PriKey = oad.OrderFID and oad.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Destination')
left join AddressLocationType alto on oao.AddressLocationTypeFID = alto.AddressLocationTypeID
left join AddressLocationType altd on oad.AddressLocationTypeFID = altd.AddressLocationTypeID
left join StateArea fsa on oao.State = fsa.State
left join StateArea tsa on oad.State = tsa.State
left join CommType ct on o.Commodity = ct.PriKey
left join MoveType mt on o.MoveType = mt.PriKey
left join ServiceType st on o.ServiceTypeFID = st.ServiceTypeID
left join ShipmentType ship on o.ShipmentTypeFID = ship.ShipmentTypeID
left join Agent ha on o.HaulAgent = ha.AgentPriKey
left join Accounts a on o.AcctPriKey = a.AccountPriKey
GO
GRANT SELECT ON  [dbo].[vLongDistanceDispatchGrid] TO [MssExec]
GRANT INSERT ON  [dbo].[vLongDistanceDispatchGrid] TO [MssExec]
GRANT DELETE ON  [dbo].[vLongDistanceDispatchGrid] TO [MssExec]
GRANT UPDATE ON  [dbo].[vLongDistanceDispatchGrid] TO [MssExec]
GO