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

CREATE view [dbo].[vInternational]
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,
    TranportMode = itm.Description,
    Direction = id.Direction,
    OriginAgent = oa.Name,
    OriginLinehaulAgent = ola.Name,
    Carrier = ic.Name,
    Consolidator = ico.Name,
    CustomsAgent = ica.Name,
    DestinationLinehaulAgent = dla.Name,
    DestinationAgent = da.Name,
    OriginPort = ip.Name,
    BookingNumber = ibn.BookingNumber,
    SeawayBill = isi.BillNumber,
    ConsignBOL = ibc.Contact,
    EstimatedPackDate = o.StartPack,
    ActualPackDate = o.ActPackDate,
    EstimatedLoadDate = o.StartLoad,
    ActualLoadDate = o.ActLoadDate,
    EstimatedReadyToGoDate = isd.ReadyToGoEstimatedDate,
    ActualReadyToGoDate = isd.ReadyToGoActualDate,
    EstimatedOriginPortArrivalDate = isd.ArrivalOriginPortEstimatedDate,
    ActualOriginPortArrivalDate = isd.ArrivalOriginPortActualDate,
    EstimatedOriginPortDepartureDate = isd.DepartureOriginPortEstimatedDate,
    ActualOriginPortDepartureDate = isd.DepartureOriginPortActualDate,
    EstimatedDestinationPortArrivalDate = isd.ArrivalDestinationPortEstimatedDate,
    ActualDestinationPortArrivalDate = isd.ArrivalDestinationPortActualDate,
    EstimatedClearedCustomsDate = isd.ClearedCustomsEstimatedDate,
    ActualClearedCustomsDate = isd.ClearedCustomsActualDate,
    EstimatedPickupDestinationPortDate = isd.PickupDestinationPortEstimatedDate,
    ActualPickupDestinationPortDate = isd.PickupDestinationPortActualDate,
    EstimatedArrivalAtDestinationDate = isd.ArrivalAtDestinationEstimatedDate,
    ActualArrivalAtDestinationDate = isd.ArrivalAtDestinationActualDate,
    EstimatedDeliveryDate = o.StartDeliv,
    ActualDeliveryDate = o.ActDelDate,
    OAPapersReceivedDate = isd.OAPapersReceivedDate,
    OAPapersSentDate = isd.OAPapersSentDate,
    PreAlertSentDate = isd.PreAlertSentDate,
    ShippingDetailSentDate = isd.ShippingDetailSentDate,
    WaybillSentDate = isd.WaybillSentDate,
    DocumentsPrintedDate = isd.DocumentsPrintedDate,
    DeliveryStatusSentDate = isd.DeliveryStatusSentDate,
    BrokerageCollectedDate = isd.BrokerageCollectedDate,
    CompanyRevenue = vr.CompanyRevenue,
    TotalCommissionPaid = vr.TotalCommissionPaid,
    ThirdPartyExpenses = vr.ThirdPartyExpenses,
    RetainedRevenue = vr.CompanyRevenue - vr.TotalCommissionPaid - vr.ThirdPartyExpenses
from    Orders o
join InternationalShippingInformation isi on o.PriKey = isi.OrdersFID
left join InternationalTransportationMode itm on isi.InternationalTransportationModeFID = itm.InternationalTransportationModeID
left join InternationalDirection id on isi.InternationalDirectionFID = id.InternationalDirectionID
left join InternationalCarrier ic on isi.InternationalCarrierFID = ic.InternationalCarrierID and ic.InternationalTransportationTypeFID = itm.InternationalTransportationTypeFID
left join InternationalConsolidator ico on isi.InternationalConsolidatorFID = ico.InternationalConsolidatorID and ico.InternationalTransportationTypeFID = itm.InternationalTransportationTypeFID
left join InternationalCustomsAgent ica on isi.InternationalCustomsAgentFID = ica.InternationalCustomsAgentID
left join InternationalPort ip on isi.InternationalPortFID = ip.InternationalPortID and ip.InternationalTransportationTypeFID = itm.InternationalTransportationTypeFID
left join InternationalBookingNumber ibn on o.PriKey = ibn.OrdersFID
left join InternationalBOLConsignee ibc on o.PriKey = ibc.OrdersFID
left join InternationalShippingDate isd on o.PriKey = isd.OrdersFID
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 Agent oa on o.OrgAgent = oa.AgentPriKey
left join InternationalLinehaulCarrier ola on isi.OriginLinehaulCarrierFID = ola.InternationalLinehaulCarrierID
left join Agent da on o.DestAgent = da.AgentPriKey
left join InternationalLinehaulCarrier dla on isi.DestinationLinehaulCarrierFID = dla.InternationalLinehaulCarrierID
left join vRetainedRevenue vr on o.PriKey = vr.OrderID
where    o.OrderStatus not in ('Cancelled', 'Will Advise', 'Voided')
GO
GRANT SELECT ON  [dbo].[vInternational] TO [MssExec]
GRANT INSERT ON  [dbo].[vInternational] TO [MssExec]
GRANT DELETE ON  [dbo].[vInternational] TO [MssExec]
GRANT UPDATE ON  [dbo].[vInternational] TO [MssExec]
GO