Views [dbo].[vMilitaryGovernment]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created3:10:18 PM Tuesday, July 21, 2015
Last Modified2:30:42 AM Saturday, February 4, 2017
Columns
Name
OrderId
OrderNumber
ShipperLastName
ShipperFirstName
TransCoordinator
SalesPerson
LogisCoordinator
OriginCity
OriginState
OriginZip
DestinationCity
DestinationState
DestinationZip
Weight
Miles
GBLNumber
SSN
RankGrade
BranchOfService
CodeOfService
ContractLotNumber
ServiceOrderNumber
BasesCarrier
BasesOrdering
BasesOrderingState
BasesOrigin
BasesOriginState
BasesDestination
BasesDestinationState
DateSubmitted1840
DateScored1840
Score1840
OriginalWeightsDate
OriginalWeightsTruckNumber
OriginalWeightsGross
OriginalWeightsTare
OriginalWeightsNet
OriginalWeightsProGear
ReWeighWeightsDate
ReWeighWeightsNumber
ReWeighWeightsGross
ReWeighWeightsTare
ReWeighWeightsNet
ReWeighWeightsProGear
DPMContractNumber
DPMDateShipmentReceived
DPMDateBaseNotified
DPMDateShipmentReleased
DPMDatePaperworkSubmittedToBase
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].[vMilitaryGovernment]
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,
    GBLNumber = mo.GBLNumber,
    SSN = mo.SSNNumber,
    RankGrade = mo.RankGrade,
    BranchOfService = mbs.BranchOfService,
    CodeOfService = mcs.CodeOfService,
    ContractLotNumber = mo.ContractLotNumber,
    ServiceOrderNumber = mo.ServiceOrderNumber,
    BasesCarrier = mc.SCACNumber,
    BasesOrdering = mbod.BaseCode,
    BasesOrderingState = sord.Code,
    BasesOrigin = mbor.BaseCode,
    BasesOriginState = sorg.Code,
    BasesDestination = mbde.BaseCode,
    BasesDestinationState = sde.Code,
    DateSubmitted1840 = mo.[1840Submitted],
    DateScored1840 = mo.[1840Scored],
    Score1840 = mo.[1840Score],
    OriginalWeightsDate = mw.Date,
    OriginalWeightsTruckNumber = mw.TruckNumber,
    OriginalWeightsGross = mw.OriginalGross,
    OriginalWeightsTare = mw.OriginalTare,
    OriginalWeightsNet = mw.OriginalNet,
    OriginalWeightsProGear = mw.OriginalProGear,
    ReWeighWeightsDate = mw.ReweighDate,
    ReWeighWeightsNumber = mw.ReweighNumber,
    ReWeighWeightsGross = mw.ReweighGross,
    ReWeighWeightsTare = mw.ReweighTare,
    ReWeighWeightsNet = mw.ReweighNet,
    ReWeighWeightsProGear = mw.ReweighProGear,
    DPMContractNumber = mdpmc.ContractNumber,
    DPMDateShipmentReceived = mdpm.DateShipmentReceived,
    DPMDateBaseNotified = mdpm.DateBaseNotified,
    DPMDateShipmentReleased = mdpm.DateShipmentReleased,
    DPMDatePaperworkSubmittedToBase = mdpm.DatePaperworkSubmitted,
    CompanyRevenue = vr.CompanyRevenue,
    TotalCommissionPaid = vr.TotalCommissionPaid,
    ThirdPartyExpenses = vr.ThirdPartyExpenses,
    RetainedRevenue = vr.CompanyRevenue - vr.TotalCommissionPaid - vr.ThirdPartyExpenses
from    Orders o
join    MilitaryOrder mo on o.PriKey = mo.OrdersFID
left join MilitaryBranchOfService mbs on mo.MilitaryBranchOfServiceFID = mbs.MilitaryBranchOfServiceID
left join MilitaryCodeOfService mcs on mo.MilitaryCodeOfServiceFID = mcs.MilitaryCodeOfServiceID
left join MilitaryCarrier mc on mo.MilitaryCarrierFID = mc.MilitaryCarrierID
left join MilitaryBase mbod on mo.OrderingBaseFID = mbod.MilitaryBaseID
left join MilitaryBaseState mbsod on mo.OrderingStateFID = mbsod.MilitaryBaseStateID
left join State sord on mbsod.StateFID = sord.StateID
left join MilitaryBase mbor on mo.OriginBaseFID = mbor.MilitaryBaseID
left join MilitaryBaseState mbsor on mo.OriginStateFID = mbsor.MilitaryBaseStateID
left join State sorg on mbsor.StateFID = sorg.StateID
left join MilitaryBase mbde on mo.DestinationBaseFID = mbde.MilitaryBaseID
left join MilitaryBaseState mbsde on mo.DestinationStateFID = mbsde.MilitaryBaseStateID
left join State sde on mbsde.StateFID = sde.StateID
left join MilitaryWeight mw on o.PriKey = mw.OrdersFID
left join MilitaryDPM mdpm on o.PriKey = mdpm.OrdersFID
left join    MilitaryDPMContract mdpmc on mdpm.MilitaryDPMContractFID = mdpmc.MilitaryDPMContractID
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].[vMilitaryGovernment] TO [MssExec]
GRANT INSERT ON  [dbo].[vMilitaryGovernment] TO [MssExec]
GRANT DELETE ON  [dbo].[vMilitaryGovernment] TO [MssExec]
GRANT UPDATE ON  [dbo].[vMilitaryGovernment] TO [MssExec]
GO
Uses