create procedure [dbo].[MssWebGetOAndIOrderLocations]
    @inOrderId int,
    @inLocationId int =null
as
begin
    set nocount on;
    select
        Id = OrderLocations.OrderLocationID,
        Name = OrderLocations.Name,
        CrossStreet = OrderLocations.CrossStreet,
        ServiceEntrance = OrderLocations.ServiceEntrance,
        SiteNote = OrderLocations.SiteNote,
        ContactId = OrderContacts.OrderContactID,
        ContactName = OrderContacts.Contact,
        BuildingId = OrderLocations.BuildingFID,
        LocationTypeId = OrderLocationAddressType.OrderLocationAddressTypeID,
        LocationType = OrderLocationAddressType.TypeName,
        AddressTypeId = AddressLocationType.AddressLocationTypeID,
        AddressType = AddressLocationType.Description,
        AddressId = OrderLocationAddress.OrderLocationAddressID,
        Address1 = OrderLocationAddress.Address1,
        Address2 = OrderLocationAddress.Address2,
        Address3 = OrderLocationAddress.Address3,
        City = OrderLocationAddress.City,
        [State] = OrderLocationAddress.State,
        PostalCode = OrderLocationAddress.PostalCode,
        CountryName = isnull(AddressCountryCodeStandard.CountryName,DefaultCountry.CountryName),
        CountryCodeStandardId = isnull(AddressCountryCodeStandard.CountryCodeStandardID,DefaultCountry.CountryCodeStandardId),
        Imported = OrderLocations.ImportedFlag
    from OrderLocations
    cross join dbo.GetDefaultCountry() DefaultCountry
    left outer join OrderLocationAddress on OrderLocations.OrderLocationID = OrderLocationAddress.OrderLocationFID
    left outer join OrderContacts on OrderLocations.OrderContactFID = OrderContacts.OrderContactID
    left outer join AddressLocationType on OrderLocationAddress.AddressLocationTypeFID = AddressLocationType.AddressLocationTypeID
    left outer join OrderLocationAddressType on OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID
    left outer join CountryCodeStandard AddressCountryCodeStandard on OrderLocationAddress.CountryCodeStandardFID = AddressCountryCodeStandard.CountryCodeStandardID
    where OrderLocations.OrderFID = @inOrderId and (@inLocationId is null or  OrderLocations.OrderLocationId = @inLocationId)
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOAndIOrderLocations] TO [MssExec]
GO