Views [dbo].[vLongDistanceDispatchGrid]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:48 AM Monday, May 4, 2015
Last Modified7:33:10 PM Tuesday, June 11, 2024
Columns
Name
DriverLastName
DriverFirstName
DriverNumber
OrderNumber
FromCity
FromST
ToCity
ToST
StartLoad
StartDelivery
EndDelivery
Linehaul
Status
TripNo
Weight
EndLoad
ActualDeliveryDate
FromPostalCode
ToPostalCode
FromArea
ToArea
FromLocationType
ToLocationType
Commodity
MoveType
ServiceType
ShipmentType
Miles
CubicFeet
LinearFeet
SelfHaul
PreferredPack
PreferredLoad
PreferredDelivery
StartPack
EndPack
PackStartTime
PackEndTime
LoadStartTime
LoadEndTime
DeliveryStartTime
DeliveryEndTime
Auto
HaulAgentID
HaulAgentName
NationalAccount
Discount
OrderId
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
/**
* 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
Uses