Views [dbo].[vLocalDispatchGrid]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:48 AM Monday, May 4, 2015
Last Modified7:41:53 PM Wednesday, February 28, 2024
Columns
Name
ServiceDate
ShipperName
OrderNumber
Service
Crew
Hours
JobStart
JobEnd
LeadCrewName
Equipment
Note
OriginAddress
OriginCity
OriginState
OriginZip
DestinationAddress
DestinationCity
DestinationState
DestinationZip
OrderWeight
TotalContainers
TotalCrates
OriginPhone
OriginWorkPhone
DestinationPhone
DestinationWorkPhone
DispatchStatus
LocServBranchID
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].[vLocalDispatchGrid]
as

select ServiceDate = convert(varchar(10), ls.ServiceDate, 101),
    ShipperName = rtrim(o.LastName) + isnull(', ' + rtrim(o.FirstName), ''),
    OrderNumber = o.OrderNo,
    Service = st.Service,
    Crew = ls.Crew,
    Hours = (ls.Crew * ls.HrsPerMan),
    JobStart = case
        when datepart(hh, ls.JobStart) > 12
        then convert(varchar(2), datepart(hh, ls.JobStart) - 12) + ':'
        else convert(varchar(2), datepart(hh, ls.JobStart)) + ':'
    end + case when datepart(mi, ls.JobStart) = 0 then '00' else convert(varchar(2), datepart(mi, ls.JobStart)) end +
    case when datepart(hh, ls.JobStart) > 12 then ' PM' else ' AM' end,
    JobEnd = case
        when datepart(hh, ls.JobEnd) > 12
        then convert(varchar(2), datepart(hh, ls.JobEnd) - 12) + ':'
        else convert(varchar(2), datepart(hh, ls.JobEnd)) + ':'
    end + case when datepart(mi, ls.JobEnd) = 0 then '00' else convert(varchar(2), datepart(mi, ls.JobEnd)) end +
    case when datepart(hh, ls.JobEnd) > 12 then ' PM' else ' AM' end,
    LeadCrewName = rtrim(crew.LASTNAME) + isnull(', ' + rtrim(crew.FIRSTNAME), ''),
    Equipment = (select    max(e.EqNo)
     from    Serveq se
     join    Equip e
        on    se.EqPriKey = e.PriKey
     where     ls.PriKey = se.LSPRIKEY),
    Note = (select    count(1)
     from    OrderNoteHeader onh
     join    NoteType nt
        on    onh.NoteTypeFID = nt.NoteTypeID
     where    o.PriKey = onh.OrderFID and
        nt.NoteType like '%local Disp%'),
    OriginAddress = oao.Address1,
    OriginCity = oao.City,
    OriginState = oao.State,
    OriginZip = oao.PostalCode,
    DestinationAddress = oad.Address1,
    DestinationCity = oad.City,
    DestinationState = oad.State,
    DestinationZip = oad.PostalCode,
    OrderWeight = o.Weight,
    TotalContainers = (select    sum(om.EstQuantity)
     from    OrdMaterial om
     join    MaterialItemCodeMap micm
        on    om.MaterialItemCodeMapFID = micm.MaterialItemCodeMapID and
            micm.MaterialCPUTypeFID = (select mct.MaterialCPUTypeID from MaterialCPUType mct where mct.Type = 'Container')
     where    o.PriKey = om.OrdPriKey),
    TotalCrates = (select sum(oc.Quantity)
     from    OrdCrates oc
     where    o.PriKey = oc.OrdPriKey),
    OriginPhone = '(' + opoh.AreaCode + ') ' + opoh.LocalNumber,
    OriginWorkPhone = '(' + opow.AreaCode + ') ' + opow.LocalNumber,
    DestinationPhone = '(' + opdh.AreaCode + ') ' + opdh.LocalNumber,
    DestinationWorkPhone = '(' + opdw.AreaCode + ') ' + opdw.LocalNumber,
    DispatchStatus = ds.Status,
    LocServBranchID = b.BranchID,
    OrderId = o.PriKey
from    Orders o
join    LocServ ls on o.PriKey = ls.OrdPriKey
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 OrderPhoneNumber opoh on o.PriKey = opoh.OrderFID and opoh.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Origin Home')
left join OrderPhoneNumber opow on o.PriKey = opow.OrderFID and opow.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Origin Work')
left join OrderPhoneNumber opdh on o.PriKey = opdh.OrderFID and opdh.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Destination Home')
left join OrderPhoneNumber opdw on o.PriKey = opdw.OrderFID and opdw.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Destination Work')
left join Branch b on b.BranchPriKey = ls.BranchPriKey
left join ServType st on st.ServiceID = ls.ServiceID
left join DispatchStatus ds on ls.DispatchStatusFID = ds.DispatchStatusID
left join LSCrew lsc on ls.PriKey = lsc.LSPriKey and lsc.LeadMan = 1
left join Sysuser crew on lsc.CrewID = crew.SysUserID
GO
GRANT SELECT ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT INSERT ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT DELETE ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT UPDATE ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GO
Uses