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

create view [dbo].[vPRMilitaryScores]
as

select
    OrderId = Orders.PriKey,
    OrderNumber = Orders.OrderNo,
    FirstName = Orders.FirstName,
    LastName = Orders.LastName,
    SurveyCompleted = MilitaryOrder.SurveyCompleted,
    TSPScore = MilitaryOrder.TspScore,
    PackQuality = MilitaryOrder.PackQuality,
    PackAgent = PackAgent.AgentID,
    LoadCourtesy = MilitaryOrder.LoadCourtesy,
    PickUpTimeliness = MilitaryOrder.PickupTimeliness,
    PickUpAgent = PickupAgent.AgentID,
    CourtesyOfDestination = MilitaryOrder.DestCourtesy,
    DeliveryTimeliness = MilitaryOrder.DelvryTimeliness,
    DeliveryAgent = DeliveryAgent.AgentID,
    OverallSatisfaction = MilitaryOrder.OverallStatisfaction,
    FollowUpSurvey = MilitaryOrder.FollowUpSurvey
from Orders
join MilitaryOrder on Orders.PriKey = MilitaryOrder.OrdersFID
left join Agent PackAgent on MilitaryOrder.PackAgentFID = PackAgent.AgentPriKey
left join Agent PickupAgent on MilitaryOrder.PickupAgentFID = PickupAgent.AgentPriKey
left join Agent DeliveryAgent on MilitaryOrder.DelvryAgentFID = DeliveryAgent.AgentPriKey
where MilitaryOrder.SurveyCompleted is not null
GO
GRANT SELECT ON  [dbo].[vPRMilitaryScores] TO [MssExec]
GRANT INSERT ON  [dbo].[vPRMilitaryScores] TO [MssExec]
GRANT DELETE ON  [dbo].[vPRMilitaryScores] TO [MssExec]
GRANT UPDATE ON  [dbo].[vPRMilitaryScores] TO [MssExec]
GO