SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/Common/SQL/Views/XtraStopInformation.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the address information for all extra stops
*
*/

CREATE view [dbo].[XtraStopInformation]
(
    ExtraStopID,
    OrderFID,
    OriginDestination,
    Contact,
    AgentFID,
    StopNumber,
    Company,
    Address1,
    Address2,
    Address3,
    City,
    County,    
    State,
    CountryCodeStandardFID,
    PostalCode,
    ContactPhone
)
as

select
    ExtraStopID = XtraStop.PriKey,
    OrderFID = XtraStop.OrdPriKey,
    OriginDestination = XtraStop.OriginDest,
    Contact = XtraStop.Contact,
    AgentFID = XtraStop.AgentPriKey,
    StopNumber = XtraStop.StopNumber,
    Company = XtraStop.Company,
    Address1 = XtraStopAddress.Address1,
    Address2 = XtraStopAddress.Address2,
    Address3 = XtraStopAddress.Address3,
    City = XtraStopAddress.City,
    County = XtraStop.County,
    State = XtraStopAddress.State,
    CountryCodeStandardFID = XtraStopAddress.CountryCodeStandardFID,
    PostalCode = XtraStopAddress.PostalCode,
    ContactPhone = dbo.GetFormattedPhoneNumber
    (
        XtraStopPhoneNumber.CountryCodeStandardFID,
        XtraStopPhoneNumber.AreaCode,
        XtraStopPhoneNumber.LocalNumber
    )
from XtraStop
left outer join AddressType on AddressType.TypeName = 'Main'    
left outer join XtraStopAddress on
(
    XtraStopAddress.XtraStopFID = XtraStop.PriKey and
    XtraStopAddress.AddressTypeFID = AddressType.AddressTypeID
)
left outer join XtraStopPhoneType on XtraStopPhoneType.TypeName = 'Contact'
left outer join XtraStopPhoneNumber on
(
    XtraStopPhoneNumber.XtraStopFID = XtraStop.PriKey and
    XtraStopPhoneNumber.XtraStopPhoneTypeFID = XtraStopPhoneType.XtraStopPhoneTypeID
)

GO
GRANT SELECT ON  [dbo].[XtraStopInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[XtraStopInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[XtraStopInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[XtraStopInformation] TO [MssExec]
GO