Views [dbo].[XtraStopInformation]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created6:18:17 PM Tuesday, February 27, 2007
Last Modified2:29:39 AM Saturday, February 4, 2017
Columns
Name
ExtraStopID
OrderFID
OriginDestination
Contact
AgentFID
StopNumber
Company
Address1
Address2
Address3
City
County
State
CountryCodeStandardFID
PostalCode
ContactPhone
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses