Views [dbo].[vInternational]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:42 AM Monday, May 4, 2015
Last Modified8:55:41 AM Thursday, December 5, 2024
Columns
Name
OrderId
OrderNumber
ShipperLastName
ShipperFirstName
TransCoordinator
SalesPerson
LogisCoordinator
OriginCity
OriginState
OriginZip
DestinationCity
DestinationState
DestinationZip
Weight
Miles
TranportMode
Direction
OriginAgent
OriginLinehaulAgent
Carrier
Consolidator
CustomsAgent
DestinationLinehaulAgent
DestinationAgent
OriginPort
BookingNumber
SeawayBill
ConsignBOL
EstimatedPackDate
ActualPackDate
EstimatedLoadDate
ActualLoadDate
EstimatedReadyToGoDate
ActualReadyToGoDate
EstimatedOriginPortArrivalDate
ActualOriginPortArrivalDate
EstimatedOriginPortDepartureDate
ActualOriginPortDepartureDate
EstimatedDestinationPortArrivalDate
ActualDestinationPortArrivalDate
EstimatedClearedCustomsDate
ActualClearedCustomsDate
EstimatedPickupDestinationPortDate
ActualPickupDestinationPortDate
EstimatedArrivalAtDestinationDate
ActualArrivalAtDestinationDate
EstimatedDeliveryDate
ActualDeliveryDate
OAPapersReceivedDate
OAPapersSentDate
PreAlertSentDate
ShippingDetailSentDate
WaybillSentDate
DocumentsPrintedDate
DeliveryStatusSentDate
BrokerageCollectedDate
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].[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
Uses