USP_CURRENCYEXCHANGERATELOOKUP_DATALIST

                CREATE procedure dbo.USP_CURRENCYEXCHANGERATELOOKUP_DATALIST
                (
                    @FROMCURRENCYID uniqueidentifier,
                    @TOCURRENCYID uniqueidentifier,
                    @TYPECODE tinyint = 1, -- 0 = latest corporate rate, 1 = latest daily/corporate rate

                    @ENDDATE datetime = null,
                    @MAXROWS int = null
                )
                as
                set nocount on;

                declare @ENDDATEWITHTIMEOFFSET datetimeoffset;

                if @ENDDATE is not null
                begin
                    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
                    set @ENDDATEWITHTIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE,0);
                end

                if @MAXROWS is not null
                  set rowcount @MAXROWS;

                select
                    CURRENCYEXCHANGERATE.ID,
                    CURRENCYEXCHANGERATE.RATE,
                    FROMCURRENCY.DECIMALDIGITS FROMCURRENCYDECIMALDIGITS,
                    FROMCURRENCY.ROUNDINGTYPECODE FROMCURRENCYROUNDINGTYPECODE,
                    TOCURRENCY.DECIMALDIGITS TOCURRENCYDECIMALDIGITS,
                    TOCURRENCY.ROUNDINGTYPECODE TOCURRENCYROUNDINGTYPECODE,
                    case
                        when CURRENCYEXCHANGERATE.SOURCECODEID is null
                            then convert(nvarchar,CURRENCYEXCHANGERATE.ASOFDATE)
                        else 
                            CURRENCYEXCHANGERATESOURCECODE.DESCRIPTION + ' (' + convert(nvarchar,CURRENCYEXCHANGERATE.ASOFDATE) + ')'
                    end LABEL
                from
                    dbo.CURRENCYEXCHANGERATE
                    inner join dbo.CURRENCY as FROMCURRENCY on CURRENCYEXCHANGERATE.FROMCURRENCYID = FROMCURRENCY.ID
                    inner join dbo.CURRENCY as TOCURRENCY on CURRENCYEXCHANGERATE.TOCURRENCYID = TOCURRENCY.ID
                    left join dbo.CURRENCYEXCHANGERATESOURCECODE on CURRENCYEXCHANGERATESOURCECODE.ID = CURRENCYEXCHANGERATE.SOURCECODEID
                where
                    @FROMCURRENCYID = FROMCURRENCY.ID
                    and @TOCURRENCYID = TOCURRENCY.ID
                    and ((CURRENCYEXCHANGERATE.TYPECODE = 0 and @TYPECODE = 0)
                    or (CURRENCYEXCHANGERATE.TYPECODE in (0,1) and @TYPECODE = 1))
                    and (@ENDDATEWITHTIMEOFFSET is null 
                         or ((CURRENCYEXCHANGERATE.ASOFDATE <= @ENDDATEWITHTIMEOFFSET)
                             and (CURRENCYEXCHANGERATE.EXPIRATIONDATE is null or CURRENCYEXCHANGERATE.EXPIRATIONDATE >= @ENDDATEWITHTIMEOFFSET)))
                order by 
                    CURRENCYEXCHANGERATE.ASOFDATE desc,
                    CURRENCYEXCHANGERATE.DATEADDED desc, 
                    CURRENCYEXCHANGERATE.DATECHANGED desc, 
                    CURRENCYEXCHANGERATE.RATE;

                set rowcount 0;