CREATE PROCEDURE [dbo].[MssWebGetVesselVoyagePortsLookups]
    @inInternationalTransportationModeID int,
    @inOrderID int,
    @inVesselVoyageId int = null
as
begin
    set nocount on

    select
        [Key] = InternationalPort.InternationalPortID,
        [Value] = InternationalPort.PortCode + ' - ' + InternationalPort.Name + isnull( ' (' + CountryCodeStandard.VanlineCountryCode + ')', '' )
    from InternationalPort
        inner join InternationalTransportationType on InternationalTransportationType.InternationalTransportationTypeID = InternationalPort.InternationalTransportationTypeFID
        inner join InternationalTransportationMode on InternationalTransportationMode.InternationalTransportationTypeFID = InternationalTransportationType.InternationalTransportationTypeID
        left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardID = InternationalPort.CountryCodeStandardFID
        left outer join InternationalVoyage on
            @inVesselVoyageId is not null
            and InternationalVoyage.OrdersFID = @inOrderID
            and InternationalVoyage.InternationalVoyageID = @inVesselVoyageId
            and (
                InternationalVoyage.InternationalPortFID = InternationalPort.InternationalPortID
                or InternationalVoyage.InternationalOriginPortFID = InternationalPort.InternationalPortID
            )
    where
        InternationalTransportationMode.InternationalTransportationModeID = @inInternationalTransportationModeID
        and (
            InternationalPort.Inactive = 0
            or InternationalVoyage.InternationalVoyageID is not null  -- Include ports that are already set on the voyage, even if inactive
        )
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetVesselVoyagePortsLookups] TO [MssExec]
GO