create procedure [dbo].[MssWebGetOrderContact]
    @inOrderId int,
    @inContactId int
as
begin
    set nocount on;

     with MainAddressType as(
        select AddressTypeID from dbo.AddressType where TypeName = 'Main'
    ), MainOrderContactPhoneType as (
        select OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Main'
    ), FaxOrderContactPhoneType as (
        select OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Fax'
    ), MobileOrderContactPhoneType as (
        select OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Mobile'
    )
    -- Get the Order Contact
    select
        Id = OrderContacts.OrderContactID,
        Contact = OrderContacts.Contact,
        Title = OrderContacts.Title,
        Company = OrderContacts.Name,
        AddessId = OrderContactAddress.OrderContactAddressID,
        Address1 = OrderContactAddress.Address1,
        Address2 = OrderContactAddress.Address2,
        Address3 = OrderContactAddress.Address3,
        City = OrderContactAddress.City,
        State = OrderContactAddress.State,
        PostalCode= OrderContactAddress.PostalCode,
        CountryName = isnull(AddressCountryCodeStandard.CountryName,DefaultCountry.CountryName),
        CountryCodeStandardId = isnull(AddressCountryCodeStandard.CountryCodeStandardID,DefaultCountry.CountryCodeStandardId),
        Email = OrderContacts.Email,
        PhoneId = PhoneOrderContactPhoneNumber.OrderContactPhoneTypeFID,
        PhoneCountryCodeStandardId = PhoneOrderContactPhoneNumber.CountryCodeStandardFID,
        PhoneCountryCode = isnull(PhoneCountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
        PhoneAreaCode = PhoneOrderContactPhoneNumber.AreaCode,
        PhoneLocalNumber = PhoneOrderContactPhoneNumber.LocalNumber,
        PhoneExtension = PhoneOrderContactPhoneNumber.Extension,
        MobileId = MobileOrderContactPhoneNumber.OrderContactPhoneTypeFID,
        MobileCountryCodeStandardId = MobileOrderContactPhoneNumber.CountryCodeStandardFID,
        MobileCountryCode = isnull(MobileCountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
        MobileAreaCode = MobileOrderContactPhoneNumber.AreaCode,
        MobileLocalNumber = MobileOrderContactPhoneNumber.LocalNumber,
        FaxId = FaxOrderContactPhoneNumber.OrderContactPhoneTypeFID,
        FaxCountryCodeStandardId = FaxOrderContactPhoneNumber.CountryCodeStandardFID,
        FaxCountryCode = isnull(FaxCountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
        FaxAreaCode = FaxOrderContactPhoneNumber.AreaCode,
        FaxLocalNumber = FaxOrderContactPhoneNumber.LocalNumber,
        Imported = OrderContacts.ImportedFlag
    from OrderContacts
    cross join MainAddressType
    cross join MainOrderContactPhoneType
    cross join FaxOrderContactPhoneType
    cross join MobileOrderContactPhoneType
    cross join dbo.GetDefaultCountry() DefaultCountry
    inner join Orders on OrderContacts.OrderFID = Orders.PriKey
    left outer join OrderContactAddress on
    (
        OrderContacts.OrderContactID = OrderContactAddress.OrderContactFID and
        OrderContactAddress.AddressTypeFID = MainAddressType.AddressTypeID
    )
    left outer join OrderContactPhoneNumber PhoneOrderContactPhoneNumber on
    (
        OrderContacts.OrderContactID = PhoneOrderContactPhoneNumber.OrderContactFID and
        PhoneOrderContactPhoneNumber.OrderContactPhoneTypeFID = MainOrderContactPhoneType.OrderContactPhoneTypeID
    )
    left outer join OrderContactPhoneNumber FaxOrderContactPhoneNumber on
    (
        OrderContacts.OrderContactID = FaxOrderContactPhoneNumber.OrderContactFID and
        FaxOrderContactPhoneNumber.OrderContactPhoneTypeFID = FaxOrderContactPhoneType.OrderContactPhoneTypeID
    )
    left outer join OrderContactPhoneNumber MobileOrderContactPhoneNumber on
    (
        OrderContacts.OrderContactID = MobileOrderContactPhoneNumber.OrderContactFID and
        MobileOrderContactPhoneNumber.OrderContactPhoneTypeFID = MobileOrderContactPhoneType.OrderContactPhoneTypeID
    )
    left outer join CountryCodeStandard AddressCountryCodeStandard on OrderContactAddress.CountryCodeStandardFID = AddressCountryCodeStandard.CountryCodeStandardID
    left outer join CountryCodeStandard PhoneCountryCodeStandard on PhoneOrderContactPhoneNumber.CountryCodeStandardFID = PhoneCountryCodeStandard.CountryCodeStandardID
    left outer join CountryCodeStandard MobileCountryCodeStandard on MobileOrderContactPhoneNumber.CountryCodeStandardFID = MobileCountryCodeStandard.CountryCodeStandardID
    left outer join CountryCodeStandard FaxCountryCodeStandard on FaxOrderContactPhoneNumber.CountryCodeStandardFID = MobileCountryCodeStandard.CountryCodeStandardID
    where OrderContacts.OrderFID = @inOrderId and OrderContacts.OrderContactID = @inContactId
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOrderContact] TO [MssExec]
GO