CREATE PROCEDURE [dbo].[MssWebGetAccountProfileContactInfo]
    @accountProfileID                int,
    @accountProfileContactID        int
AS
BEGIN
    with PhoneType as(
    SELECT AccountProfileContactPhoneType.AccountProfileContactPhoneTypeID
     FROM AccountProfileContactPhoneType
     WHERE AccountProfileContactPhoneType.TypeName = 'Main'
),
MobileType as(
    SELECT AccountProfileContactPhoneType.AccountProfileContactPhoneTypeID
     FROM AccountProfileContactPhoneType
     WHERE AccountProfileContactPhoneType.TypeName = 'Mobile'
),
FaxType as(
    SELECT AccountProfileContactPhoneType.AccountProfileContactPhoneTypeID
     FROM AccountProfileContactPhoneType
     WHERE AccountProfileContactPhoneType.TypeName = 'Fax'
)
Select
    AccountProfileContactID,
    AccountProfile = AccountProfiles.Name,
    Name = Contact,
    Title,
    PhoneId = AccountProfileContactPhoneNumber.AccountProfileContactPhoneNumberID,
    PhoneCountryCode = ISNULL(CountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
    PhoneCountryCodeStandardId = ISNULL(AccountProfileContactPhoneNumber.CountryCodeStandardFID, DefaultCountry.CountryCodeStandardID),
    PhoneAreaCode = AccountProfileContactPhoneNumber.AreaCode,
    PhoneLocalNumber = AccountProfileContactPhoneNumber.LocalNumber,
    PhoneExtension = AccountProfileContactPhoneNumber.Extension,
    MobileId = MobilePhone.AccountProfileContactPhoneNumberID,
    MobileCountryCode = ISNULL(MobileCountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
    MobileCountryCodeStandardId = ISNULL(MobileCountryCodeStandard.CountryCodeStandardID, DefaultCountry.CountryCodeStandardID),
    MobileAreaCode = MobilePhone.AreaCode,
    MobileLocalNumber = MobilePhone.LocalNumber,
    MobileExtension = MobilePhone.Extension,
    FaxId = FaxPhone.AccountProfileContactPhoneNumberID,
    FaxCountryCode = ISNULL(FaxCountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
    FaxCountryCodeStandardId = ISNULL(FaxCountryCodeStandard.CountryCodeStandardID, DefaultCountry.CountryCodeStandardID),
    FaxAreaCode = FaxPhone.AreaCode,
    FaxLocalNumber = FaxPhone.LocalNumber,
    FaxExtension = FaxPhone.Extension,
    ContactAddressId = AccountProfileContactAddress.AccountProfileContactAddressID,
    ContactAddress1 = AccountProfileContactAddress.Address1,
    ContactAddress2 = AccountProfileContactAddress.Address2,
    ContactAddress3 = AccountProfileContactAddress.Address3,
    ContactCity = AccountProfileContactAddress.City,
    ContactState = AccountProfileContactAddress.State,
    ContactPostalCode = AccountProfileContactAddress.PostalCode,
    ContactCountryName = ContactAddressCountryCodeStandard.CountryName,
    County,
    Email,
    MonitorOrders    
FROM    AccountProfiles
    CROSS JOIN PhoneType
    CROSS JOIN MobileType
    CROSS JOIN FaxType
    INNER JOIN AccountProfileContacts ON AccountProfileContacts.AccountProfileFID = AccountProfiles.AccountProfileID AND AccountProfileContacts.AccountProfileFID = @accountProfileID
    LEFT OUTER JOIN AccountProfileContactPhoneNumber ON
        AccountProfileContactPhoneNumber.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND AccountProfileContactPhoneNumber.AccountProfileContactPhoneTypeFID = PhoneType.AccountProfileContactPhoneTypeID

    LEFT OUTER JOIN AccountProfileContactPhoneNumber  MobilePhone ON
        MobilePhone.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND MobilePhone.AccountProfileContactPhoneTypeFID = MobileType.AccountProfileContactPhoneTypeID

    LEFT OUTER JOIN AccountProfileContactPhoneNumber  FaxPhone ON
        FaxPhone.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND FaxPhone.AccountProfileContactPhoneTypeFID = FaxType.AccountProfileContactPhoneTypeID

    INNER JOIN AddressType ON AddressType.TypeName = 'Main'
    LEFT OUTER JOIN AccountProfileContactAddress ON
        AccountProfileContactAddress.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND AccountProfileContactAddress.AddressTypeFID = AddressType.AddressTypeID
    LEFT OUTER JOIN CountryCodeStandard ON AccountProfileContactPhoneNumber.CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
    LEFT OUTER JOIN CountryCodeStandard  MobileCountryCodeStandard ON MobilePhone.CountryCodeStandardFID = MobileCountryCodeStandard.CountryCodeStandardID
    LEFT OUTER JOIN CountryCodeStandard  FaxCountryCodeStandard ON FaxPhone.CountryCodeStandardFID = FaxCountryCodeStandard.CountryCodeStandardID
    LEFT OUTER JOIN CountryCodeStandard  ContactAddressCountryCodeStandard ON AccountProfileContactAddress.CountryCodeStandardFID = ContactAddressCountryCodeStandard.CountryCodeStandardID
    CROSS JOIN dbo.GetDefaultCountry() DefaultCountry
    WHERE AccountProfileContacts.AccountProfileFID = @accountProfileID AND AccountProfileContacts.AccountProfileContactID = @accountProfileContactID;
END;
GO
GRANT EXECUTE ON  [dbo].[MssWebGetAccountProfileContactInfo] TO [MssExec]
GO