CREATE PROCEDURE [dbo].[MssWebGetAccountProfileContactsForOrder]
    @orderId 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'
    )        
    SELECT
            AccountProfileContactId=AccountProfileContacts.AccountProfileContactId,
            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,
            IsPrimaryContact = convert(bit,
                CASE
                    WHEN AccountProfileContacts.AccountProfileContactID = AccountProfileOrderPrimaryContact.AccountProfileContactFID THEN 1
                    ELSE 0
                END
            )
    from Orders
    inner join AccountProfileContacts on Orders.AccountProfileFID = AccountProfileContacts.AccountProfileFID
    cross join PhoneType
    cross join MobileType
    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
    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
    left outer join AccountProfileOrderPrimaryContact ON
        AccountProfileOrderPrimaryContact.OrderFID = Orders.PriKey
    where Orders.PriKey = @orderId
END;
GO
GRANT EXECUTE ON  [dbo].[MssWebGetAccountProfileContactsForOrder] TO [MssExec]
GO