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

create view [dbo].[vRevenueEntry]
as

select
    OrderId = o.PriKey,
    OrderNumber = o.Orderno,
    OrderBranch = b.BranchID,
    ShipperLastName = o.LastName,
    ShipperFirstName = o.FirstName,
    RevenueClerk = rtrim(rev.FirstName) + ' ' + rtrim(rev.LastName),
    SalesPerson = rtrim(sls.FirstName) + ' ' + rtrim(sls.LastName),
    TypeOfMove = mt.MoveName,
    Commodity = ct.Commodity,
    AuthorityType = at.Description,
    TariffRate = o.TariffContract,
    BilledDate = o.ReleaseDate,
    OriginCity = oao.City,
    OriginState = oao.State,
    OriginZip = oao.PostalCode,
    DestinationCity = oad.City,
    DestinationState = oad.State,
    DestinationZip = oad.PostalCode,
    CustomerNumber = o.CustomerNumber,
    PaymentType = pt.PayName,
    PaymentMethod = pm.Method,
    PurchaseOrder = o.PurchaseOrderNo,
    TotalEstimateAmount = o.EstAmt,
    TotalActualAmount = o.ActualCost,
    EstimatedDiscountedLinehaul = o.LineHaul,
    ActualDiscountedLinehaul = o.ActLineHaul,
    Weight = o.Weight,
    Miles = o.Miles,
    Discount = o.Discount,
    HaulingDocumentsReceivedDate = o.PaperworkRecd,
    NationalAccount = a.AcctName,
    RevenueGroup = bmj.Description,
    RevenueGroupAmount = bmj.Amount,
    RevenueGroupInv = bmj.InvoiceFlag,
    RevenueItem = bmn.Description,
    RevenueItemServiceCode = ic.ItemCode,
    RevenueItemAmount = bmn.Amount,
    RevenueItemPct = bmn.Percentage,
    RevenueItemQty1 = bmn.Quantity,
    RevenueItemQty2 = bmn.Quantity2,
    RevenueItemRate = bmn.Rate,
    RevenueItemAgent = revagt.AgentID,
    RevenueItemDivision = revdiv.Number,
    RevenueItemInv = bmn.InvoiceFlag,
    RevenueItemDocumentNumber = bmn.DocumentNumber,
    RevenueItemDocumentDate = bmn.DocDate,
    RevenueItemJournalDate = bmn.JournalDate,
    RevenueItemPostedDate = bmn.PostedDate,
    RevenueItemPostedBy = pb.FIRSTNAME + ' ' + pb.LASTNAME,
    CommissionItem = cd.Description,
    CommissionAmount = cd.Amount,
    CommissionPct = cd.CommissionPerc,
    CommissionDocumentNumber = cd.DocumentNumber,
    CommissionDocumentDate = cd.DocDate,
    CommissionJournalDate = cd.JournalDate,
    CommissionPostedDate = cd.PostedDate,
    CommissionPostedBy = cpb.FIRSTNAME + ' ' + cpb.LASTNAME,
    ThirdPartyItem = tp.Description,
    ThirPartyVendorNumber = tp.VendorID,
    ThirdPartyAmount = tp.PayableAmount,
    ThirdPartyDocumentNumber = tp.DocumentNumber,
    ThirdPartyDocumentDate = tp.DocDate,
    ThirdPartyJournalDate = tp.JournalDate,
    ThirdPartyPostedDate = tp.PostedDate,
    ThirdPartyPostedBy = tpb.FIRSTNAME + ' ' + tpb.LASTNAME
from    Orders o
join BillingMajorItem bmj on o.PriKey = bmj.OrdPriKey
left join Branch b on o.BranchPriKey = b.BranchPriKey
left join Sysuser rev on o.RevenueClerk = rev.SysuserID
left join Sysuser sls on o.SalesPerson = sls.SysuserID
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 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 PayType pt on o.PayTypeFID = pt.PayTypeID
left join PayMethod pm on o.PayMethodFID = pm.PayMethodID
left join Accounts a on o.AcctPriKey = a.AccountPriKey
left join BillingMinorItem bmn on bmj.BMajPriKey = bmn.BMajPriKey
left join ItemCode ic on bmn.ICPriKey = ic.ICPriKey
left join Agent revagt on b.AgentPriKey = revagt.AgentPriKey
left join Division revdiv on bmn.DivisionFID = revdiv.DivisionID
left join Sysuser pb on bmn.PostedBy = pb.SysUserID
left join CommissionedDetail cd on bmn.BMinPriKey = cd.BMinPriKey
left join Sysuser cpb on cd.PostedBy = cpb.SysUserID
left join ThirdParty tp on bmn.BMinPriKey = tp.BMinPriKey
left join Sysuser tpb on tp.PostedBy = tpb.SysUserID
GO
GRANT SELECT ON  [dbo].[vRevenueEntry] TO [MssExec]
GRANT INSERT ON  [dbo].[vRevenueEntry] TO [MssExec]
GRANT DELETE ON  [dbo].[vRevenueEntry] TO [MssExec]
GRANT UPDATE ON  [dbo].[vRevenueEntry] TO [MssExec]
GO