SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/MoversSuite/Common/SQL/Views/AgentPhoneNumbers.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the Agent Phone Number of the "Phone" and "Fax" type
*
*/

CREATE view [dbo].[AgentPhoneNumbers]
(
    AgentFID,
    PhoneCountryCodeStandardFID,
    PhoneAreaCode,
    PhoneLocalNumber,
    PhoneNumber,
    PhoneExtension,
    FaxCountryCodeStandardFID,
    FaxAreaCode,
    FaxLocalNumber,
    FaxNumber
)
as

select
    AgentFID = Agent.AgentPriKey,
    PhoneCountryCodeStandardFID = PhoneNumber.CountryCodeStandardFID,
    PhoneAreaCode = PhoneNumber.AreaCode,
    PhoneLocalNumber = PhoneNumber.LocalNumber,
    PhoneNumber = dbo.GetFormattedPhoneNumber( PhoneNumber.CountryCodeStandardFID, PhoneNumber.AreaCode, PhoneNumber.LocalNumber ),
    PoneExtension = PhoneNumber.Extension,
    FaxCountryCodeStandardFID = FaxNumber.CountryCodeStandardFID,
    FaxAreaCode = FaxNumber.AreaCode,
    FaxLocalNumber = FaxNumber.LocalNumber,
    FaxNumber = dbo.GetFormattedPhoneNumber( FaxNumber.CountryCodeStandardFID, FaxNumber.AreaCode, FaxNumber.LocalNumber )
from
    Agent
    left outer join AgentPhoneType        as PhoneType    on ( PhoneType.TypeName = 'Phone' )
    left outer join AgentPhoneNumber     as PhoneNumber    on ( PhoneNumber.AgentFID = Agent.AgentPriKey
                                                             and PhoneNumber.AgentPhoneTypeFID = PhoneType.AgentPhoneTypeID )
    left outer join AgentPhoneType        as FaxType        on ( FaxType.TypeName = 'Fax' )
    left outer join AgentPhoneNumber     as FaxNumber    on ( FaxNumber.AgentFID = Agent.AgentPriKey
                                                             and FaxNumber.AgentPhoneTypeFID = FaxType.AgentPhoneTypeID )

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