Views [dbo].[vSpecialServices]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:49 AM Monday, May 4, 2015
Last Modified9:14:51 AM Thursday, May 23, 2024
Columns
Name
OrderId
OrderNumber
ShipperLastName
ShipperFirstName
TransCoordinator
SalesPerson
LogisCoordinator
OriginCity
OriginState
OriginZip
DestinationCity
DestinationState
DestinationZip
Weight
Miles
JobNo
StoreNo
StoreName
StoreContactName
StoreCity
StoreState
StoreZip
AllActionsComplete
LabelsPrinted
ConfirmationContact
TransportationFee
TruckNumber
PONumber
LogisticsOrderNo
DropSequence
InvoiceDate
InstallDate
PODDate
CompanyRevenue
TotalCommissionPaid
ThirdPartyExpenses
RetainedRevenue
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].[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
Uses