/**
* 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