CREATE PROCEDURE [dbo].[MssWebGetAccountProfileContacts]
      @accountProfileID        int
AS
BEGIN        
    SELECT
            AccountProfileContactId=AccountProfileContacts.AccountProfileContactID,
            AccountProfileFid=AccountProfileContacts.AccountProfileFID,   
            AccountProfileContacts.Contact,
            AccountProfileContacts.Title,
            AccountProfileContactAddress.City,
            AccountProfileContactAddress.State,
            AccountProfileContacts.Email,
            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
    FROM AccountProfileContacts    
    INNER JOIN AccountProfileContactPhoneType AccountProfileContactPhoneType ON AccountProfileContactPhoneType.TypeName = 'Main'
    LEFT OUTER JOIN AccountProfileContactPhoneNumber ON
        AccountProfileContactPhoneNumber.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND AccountProfileContactPhoneNumber.AccountProfileContactPhoneTypeFID = AccountProfileContactPhoneType.AccountProfileContactPhoneTypeID
    INNER JOIN AccountProfileContactPhoneType  MobilePhoneType ON MobilePhoneType.TypeName = 'Mobile'
    LEFT OUTER JOIN AccountProfileContactPhoneNumber  MobilePhone ON
        MobilePhone.AccountProfileContactFID = AccountProfileContacts.AccountProfileContactID
        AND MobilePhone.AccountProfileContactPhoneTypeFID = MobilePhoneType.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
    CROSS JOIN dbo.GetDefaultCountry() DefaultCountry
    WHERE AccountProfileContacts.AccountProfileFID = @accountProfileID;
    
END;
GO
GRANT EXECUTE ON  [dbo].[MssWebGetAccountProfileContacts] TO [MssExec]
GO