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

CREATE view [dbo].[vOrderInformation]
as

select
    OrderId = o.PriKey,
    OrderNumber = o.Orderno,
    OrderBranch = b.BranchID,
    ShipperLastName = o.LastName,
    ShipperFirstName = o.FirstName,
    OrderStatus = o.OrderStatus,
    EstimateNumber = o.EstimateNo,
    TransCoordinator = rtrim(coor.FirstName) + ' ' + rtrim(coor.LastName),
    SalesPerson = rtrim(sls.FirstName) + ' ' + rtrim(sls.LastName),
    LeadSource = lt.LeadType,
    LogisCoordinator = rtrim(lcoor.FirstName) + ' ' + rtrim(lcoor.LastName),
    OriginCity = oao.City,
    OriginState = oao.State,
    OriginZip = oao.PostalCode,
    OriginCountry = oaoc.VanlineCountryCode,
    DestinationCity = oad.City,
    DestinationState = oad.State,
    DestinationZip = oad.PostalCode,
    DestinationCountry = oadc.VanlineCountryCode,
    EarlyPackDate = o.StartPack,
    LatePackDate = o.EndPack,
    EarlyLoadDate = o.StartLoad,
    LateLoadDate = o.EndLoad,
    ActualLoadDate = o.ActLoadDate,
    EarlyDeliveryDate = o.StartDeliv,
    LateDeliveryDate = o.EndDeliv,
    ActualDelieveryDate = o.ActDelDate,
    BookDate = o.BookDate,
    CreatedDate = o.CreatedOn,
    Weight = o.Weight,
    Miles = o.Miles,
    TypeOfMove = mt.MoveName,
    Commodity = ct.Commodity,
    AuthorityType = at.Description,
    BookingAgent = ba.AgentID,
    OriginAgent = oa.AgentID,
    HaulingAgent = ha.AgentID,
    DestinationAgent = da.AgentID,
    OACoordinator = rtrim(oacoor.FirstName) + ' ' + rtrim(oacoor.LastName),
    OASurveyor = rtrim(oasrv.FirstName) + ' ' + rtrim(oasrv.LastName),
    SurveyDate = o.EstDate,
    NationalAccount = a.AcctName,
    PurchaseOrder = o.PurchaseOrderNo,
    EstimateAmount = o.EstAmt,
    ActualAmount = o.ActualCost,
    RevenueClerk = rtrim(rev.FirstName) + ' ' + rtrim(rev.LastName),
    BilledDate = o.ReleaseDate,
    AccountProfile = ap.Name,
    CompanyRevenue = vr.CompanyRevenue,
    TotalCommissionPaid = vr.TotalCommissionPaid,
    ThirdPartyExpenses = vr.ThirdPartyExpenses,
    RetainedRevenue = vr.CompanyRevenue - vr.TotalCommissionPaid - vr.ThirdPartyExpenses,
    CustomerNumber = o.CustomerNumber,
    CustomerName = vci.CustomerName
from Orders o
left join Branch b on o.BranchPriKey = b.BranchPriKey
left join AccountProfiles ap on o.AccountProfileFID = ap.AccountProfileID
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 Sysuser oacoor on o.OACoordinator = oacoor.SysuserID
left join Sysuser oasrv on o.OASurveyor = oasrv.SysuserID
left join Sysuser rev on o.RevenueClerk = rev.SysuserID
left join LeadType lt on o.LeadType = lt.PriKey
left join OrderAddress oao on o.PriKey = oao.OrderFID and oao.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Origin')
left join CountryCodeStandard oaoc on oaoc.CountryCodeStandardID = oao.CountryCodeStandardFID
left join OrderAddress oad on o.PriKey = oad.OrderFID and oad.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Destination')
left join CountryCodeStandard oadc on oadc.CountryCodeStandardID = oad.CountryCodeStandardFID
left join MoveType mt on o.MoveType = mt.PriKey
left join CommType ct on o.Commodity = ct.PriKey
left join AuthorityTypes at on o.AuthPriKey = at.AuthPriKey
left join Agent ba on o.BookAgent = ba.AgentPriKey
left join Agent oa on o.OrgAgent = oa.AgentPriKey
left join Agent ha on o.HaulAgent = ha.AgentPriKey
left join Agent da on o.DestAgent = da.AgentPriKey
left join Accounts a on o.AcctPriKey = a.AccountPriKey
left join vRetainedRevenue vr on o.PriKey = vr.OrderID
left join vCustomerInformation vci on vci.CustomerNumber = o.CustomerNumber
where o.OrderStatus not in ('Cancelled', 'Will Advise', 'Voided')
GO
GRANT SELECT ON  [dbo].[vOrderInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[vOrderInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[vOrderInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[vOrderInformation] TO [MssExec]
GO