Views [dbo].[vRevenueEntry]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created3:10:26 PM Tuesday, July 21, 2015
Last Modified2:30:42 AM Saturday, February 4, 2017
Columns
Name
OrderId
OrderNumber
OrderBranch
ShipperLastName
ShipperFirstName
RevenueClerk
SalesPerson
TypeOfMove
Commodity
AuthorityType
TariffRate
BilledDate
OriginCity
OriginState
OriginZip
DestinationCity
DestinationState
DestinationZip
CustomerNumber
PaymentType
PaymentMethod
PurchaseOrder
TotalEstimateAmount
TotalActualAmount
EstimatedDiscountedLinehaul
ActualDiscountedLinehaul
Weight
Miles
Discount
HaulingDocumentsReceivedDate
NationalAccount
RevenueGroup
RevenueGroupAmount
RevenueGroupInv
RevenueItem
RevenueItemServiceCode
RevenueItemAmount
RevenueItemPct
RevenueItemQty1
RevenueItemQty2
RevenueItemRate
RevenueItemAgent
RevenueItemDivision
RevenueItemInv
RevenueItemDocumentNumber
RevenueItemDocumentDate
RevenueItemJournalDate
RevenueItemPostedDate
RevenueItemPostedBy
CommissionItem
CommissionAmount
CommissionPct
CommissionDocumentNumber
CommissionDocumentDate
CommissionJournalDate
CommissionPostedDate
CommissionPostedBy
ThirdPartyItem
ThirPartyVendorNumber
ThirdPartyAmount
ThirdPartyDocumentNumber
ThirdPartyDocumentDate
ThirdPartyJournalDate
ThirdPartyPostedDate
ThirdPartyPostedBy
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].[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
Uses