USP_CURRENCYEXCHANGERATE_ADD

            CREATE procedure dbo.USP_CURRENCYEXCHANGERATE_ADD
            (
                @ID uniqueidentifier,
                @FROMCURRENCYID uniqueidentifier,
                @TOCURRENCYID uniqueidentifier,
                @RATE decimal(20,8) = 0,
                @ASOFDATE datetimeoffset,
                @TYPECODE tinyint = 0,
                @SOURCECODEID uniqueidentifier = null,
                @TIMEZONEENTRYID uniqueidentifier = null,
                @EXPIRATIONDATETIMEOFFSET datetimeoffset = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as
                set nocount on;

                declare @PREVIOUSRATEASOFDATE datetimeoffset(3);
                declare @NEXTRATEASOFDATE datetimeoffset(3);

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                if @EXPIRATIONDATETIMEOFFSET is null
                    set @EXPIRATIONDATETIMEOFFSET = dbo.UFN_CURRENCY_GETEXPIRATIONDATE(@TYPECODE,@ASOFDATE,@TIMEZONEENTRYID);


                --Get the next ASOFDATE after the ASOFDATE of the new record.

                if @TYPECODE <> 2
                    select top 1
                        @NEXTRATEASOFDATE = ASOFDATE
                    from 
                        dbo.CURRENCYEXCHANGERATE
                    where 
                        FROMCURRENCYID = @FROMCURRENCYID
                        and TOCURRENCYID = @TOCURRENCYID
                        and ASOFDATE > @ASOFDATE
                    order by
                        ASOFDATE; 

                insert into dbo.CURRENCYEXCHANGERATE(
                    ID, 
                    FROMCURRENCYID,
                    TOCURRENCYID,
                    RATE,
                    ASOFDATE,
                    TYPECODE,
                    SOURCECODEID,
                    TIMEZONEENTRYID,
                    EXPIRATIONDATE,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED,
                    NEXTRATEASOFDATE,
                    ISLATESTRATEFORASOFDATE
                )
                values(
                    @ID,
                    @FROMCURRENCYID,
                    @TOCURRENCYID,
                    @RATE,
                    @ASOFDATE,
                    @TYPECODE,
                    @SOURCECODEID,
                    @TIMEZONEENTRYID,
                    @EXPIRATIONDATETIMEOFFSET,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE,

                    --If there is a rate with an ASOFDATE after the new rate, set the NEXTRATEASOFDATE to

                    --the ASOFDATE of the next rate minus 3ms.

                    case when @NEXTRATEASOFDATE is null or @TYPECODE = 2
                        then null 
                        else DATEADD(ms, -1, @NEXTRATEASOFDATE) 
                    end,

                    --Since we define latest rate as order by ASOFDATE desc, DATEADDED desc, DATECHANGED desc, the new rate

                    --will always be the LATESTRATEFORASOFDATE

                    1
                );

                return 0;