USP_CURRENCYEXCHANGERATE_GETAVAILABLERATES

                CREATE procedure dbo.USP_CURRENCYEXCHANGERATE_GETAVAILABLERATES
                (
                    @FROMCURRENCYID uniqueidentifier,
                    @TOCURRENCYID uniqueidentifier,
                    @TYPECODE tinyint,
                    @ASOFDATE datetime,
                    @INCLUDESPOTRATE bit,
                    @CURRENTEXCHANGERATEID uniqueidentifier
                )    as
                    set nocount on;

                    declare @DATEWITHTIMEOFFSET datetimeoffset;

                    if @ASOFDATE is not null
                    begin
                        declare @DATE datetime;
                        set @DATE = @ASOFDATE;

                        --If the date does not have a time component, use the latest time 

                        --so that the latest exchange rate for this date will be used.

                        if @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE)
                            set @DATE = dbo.UFN_DATE_GETLATESTTIME(@DATE);

                        set @DATEWITHTIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@DATE, 0);
                    end

                    if @INCLUDESPOTRATE is null
                        set @INCLUDESPOTRATE = 0;

                    declare @SPOTRATEID uniqueidentifier;
                    set @SPOTRATEID = N'00000000-0000-0000-0000-000000000001';

                    declare @SPOTRATELABEL nvarchar(120);
                    set @SPOTRATELABEL = N'Spot rate';



                    select
                        CURRENCYEXCHANGERATERANKED.ID,
                        CURRENCYEXCHANGERATERANKED.SOURCECODEID,
                        convert(datetime,CURRENCYEXCHANGERATERANKED.ASOFDATE) as ASOFDATE,
                        CURRENCYEXCHANGERATERANKED.DATEADDED,
                        CURRENCYEXCHANGERATERANKED.SOURCECODEDESCRIPTION,
                        CURRENCYEXCHANGERATERANKED.ISSPOTRATE,
                        CURRENCYEXCHANGERATERANKED.RATERANK,
                        CURRENCYEXCHANGERATERANKED.TIMEZONEDISPLAYNAME
                    from
                    (
                        select
                            CURRENCYEXCHANGERATE.ID,
                            CURRENCYEXCHANGERATE.SOURCECODEID,
                            CURRENCYEXCHANGERATE.ASOFDATE,
                            CURRENCYEXCHANGERATE.DATEADDED,
                            CURRENCYEXCHANGERATESOURCECODE.[DESCRIPTION] SOURCECODEDESCRIPTION,
                            cast(0 as bit) ISSPOTRATE,
                            rank()
                                over 
                                (
                                    partition by
                                        CURRENCYEXCHANGERATE.SOURCECODEID
                                    order by
                                        CURRENCYEXCHANGERATE.ASOFDATE desc, CURRENCYEXCHANGERATE.DATEADDED desc, CURRENCYEXCHANGERATE.DATECHANGED desc, CURRENCYEXCHANGERATE.RATE
                                ) as RATERANK,
                            TIMEZONEENTRY.NAME TIMEZONEDISPLAYNAME
                        from
                            dbo.CURRENCYEXCHANGERATE
                            left join dbo.CURRENCYEXCHANGERATESOURCECODE on CURRENCYEXCHANGERATESOURCECODE.ID = CURRENCYEXCHANGERATE.SOURCECODEID
                            left join dbo.TIMEZONEENTRY on TIMEZONEENTRY.ID = CURRENCYEXCHANGERATE.TIMEZONEENTRYID
                        where
                            (@FROMCURRENCYID is null or @FROMCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID)
                            and (@TOCURRENCYID is null or @TOCURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID)
                            and ((CURRENCYEXCHANGERATE.TYPECODE = 0 and @TYPECODE = 0)
                            or (CURRENCYEXCHANGERATE.TYPECODE in (0,1) and @TYPECODE = 1))
                            and (CURRENCYEXCHANGERATE.ASOFDATE <= @DATEWITHTIMEOFFSET)
                            and (CURRENCYEXCHANGERATE.EXPIRATIONDATE is null or CURRENCYEXCHANGERATE.EXPIRATIONDATE >= @DATEWITHTIMEOFFSET)

                        union all

                        select
                            @SPOTRATEID,
                            null,
                            null,
                            null,
                            null,
                            cast(1 as bit),
                            1,
                            null
                        where
                            @INCLUDESPOTRATE = 1
                            and @FROMCURRENCYID <> @TOCURRENCYID

                        union

                        select
                            CURRENCYEXCHANGERATE.ID,
                            CURRENCYEXCHANGERATE.SOURCECODEID,
                            CURRENCYEXCHANGERATE.ASOFDATE,
                            CURRENCYEXCHANGERATE.DATEADDED,
                            CURRENCYEXCHANGERATESOURCECODE.[DESCRIPTION] SOURCECODEDESCRIPTION,
                            cast(0 as bit) ISSPOTRATE,
                            1 as RATERANK,
                            TIMEZONEENTRY.NAME TIMEZONEDISPLAYNAME
                        from
                            dbo.CURRENCYEXCHANGERATE
                            left join dbo.CURRENCYEXCHANGERATESOURCECODE on CURRENCYEXCHANGERATESOURCECODE.ID = CURRENCYEXCHANGERATE.SOURCECODEID
                            left join dbo.TIMEZONEENTRY on TIMEZONEENTRY.ID = CURRENCYEXCHANGERATE.TIMEZONEENTRYID
                        where
                            CURRENCYEXCHANGERATE.ID = @CURRENTEXCHANGERATEID
                            and 
                            (
                                CURRENCYEXCHANGERATE.TYPECODE = 2 
                                or
                                CURRENCYEXCHANGERATE.ASOFDATE <= @DATEWITHTIMEOFFSET
                            )
                    ) CURRENCYEXCHANGERATERANKED
                    where
                        CURRENCYEXCHANGERATERANKED.RATERANK = 1
                    order by
                        CURRENCYEXCHANGERATERANKED.ISSPOTRATE,
                        CURRENCYEXCHANGERATERANKED.ASOFDATE desc,
                        CURRENCYEXCHANGERATERANKED.SOURCECODEDESCRIPTION,
                        CURRENCYEXCHANGERATERANKED.DATEADDED desc;