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

create view [dbo].[vSpecialServices]
as

select
    OrderId = o.PriKey,
    OrderNumber = o.Orderno,
    ShipperLastName = o.LastName,
    ShipperFirstName = o.FirstName,
    TransCoordinator = rtrim(coor.FirstName) + ' ' + rtrim(coor.LastName),
    SalesPerson = rtrim(sls.FirstName) + ' ' + rtrim(sls.LastName),
    LogisCoordinator = rtrim(lcoor.FirstName) + ' ' + rtrim(lcoor.LastName),
    OriginCity = oao.City,
    OriginState = oao.State,
    OriginZip = oao.PostalCode,
    DestinationCity = oad.City,
    DestinationState = oad.State,
    DestinationZip = oad.PostalCode,
    Weight = o.Weight,
    Miles = o.Miles,
    JobNo = j.ClientJobNo,
    StoreNo = s.StoreNo,
    StoreName = s.StoreName,
    StoreContactName = s.StoreContact,
    StoreCity = sa.City,
    StoreState = sa.State,
    StoreZip = sa.PostalCode,
    AllActionsComplete = j.AllActionsComplete,
    LabelsPrinted = j.LabelPrinted,
    ConfirmationContact = j.Comments,
    TransportationFee = j.TransFee,
    TruckNumber = j.TruckNo,
    PONumber = j.PONo,
    LogisticsOrderNo = j.LogisticsOrderNo,
    DropSequence = j.DropSequence,
    InvoiceDate = j.InvoiceDate,
    InstallDate = j.InstallDateTime,
    PODDate = j.PODReturnDate,
    CompanyRevenue = vr.CompanyRevenue,
    TotalCommissionPaid = vr.TotalCommissionPaid,
    ThirdPartyExpenses = vr.ThirdPartyExpenses,
    RetainedRevenue = vr.CompanyRevenue - vr.TotalCommissionPaid - vr.ThirdPartyExpenses
from    Orders o
join    Jobs j on o.PriKey = j.OrdersPriKey
left join Stores s on j.StoresID = s.StoreID
left join StoreAddress sa on s.StoreID = sa.StoreFID and sa.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Main')
left join Sysuser sls on o.SalesPerson = sls.SysuserID
left join Sysuser coor on o.Coordinator = coor.SysuserID
left join Sysuser lcoor on o.LogisticCoordinator = lcoor.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 vRetainedRevenue vr on o.PriKey = vr.OrderID
where    o.OrderStatus not in ('Cancelled', 'Will Advise', 'Voided')
GO
GRANT SELECT ON  [dbo].[vSpecialServices] TO [MssExec]
GRANT INSERT ON  [dbo].[vSpecialServices] TO [MssExec]
GRANT DELETE ON  [dbo].[vSpecialServices] TO [MssExec]
GRANT UPDATE ON  [dbo].[vSpecialServices] TO [MssExec]
GO