USP_DATAFORMTEMPLATE_EDIT_CURRENCYEXCHANGERATE

The save procedure used by the edit dataform template "Currency Exchange Rate Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@FROMCURRENCYID	uniqueidentifier	IN	From currency
@TOCURRENCYID	uniqueidentifier	IN	To currency
@RATE	decimal(20, 8)	IN	Rate
@ASOFDATETIME	datetime	IN	As of date/time
@TYPECODE	tinyint	IN	Type
@SOURCECODEID	uniqueidentifier	IN	Source
@TIMEZONEENTRYID	uniqueidentifier	IN	Time zone
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CURRENCYEXCHANGERATE (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @FROMCURRENCYID uniqueidentifier,
                        @TOCURRENCYID uniqueidentifier,
                        @RATE decimal(20,8),
                        @ASOFDATETIME datetime,
                        @TYPECODE tinyint,
                        @SOURCECODEID uniqueidentifier,
                        @TIMEZONEENTRYID uniqueidentifier
                    ) as
                        set nocount on;

                        declare @CURRENTDATE datetime = getdate();

                        if @ID is null
                            set @ID = newid();

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

                        if @TIMEZONEENTRYID is null
                            set @TIMEZONEENTRYID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();

                        begin try

                            --Bug 103436 - AdamBu - Only date/time and source can be edited on existing rates.  As such,

                            --    error if the call is attempting to change anything else.

                            if exists(
                                select 1
                                from dbo.CURRENCYEXCHANGERATE
                                where ID = @ID
                                    and(
                                        (@FROMCURRENCYID is not null and FROMCURRENCYID <> @FROMCURRENCYID)
                                        or (@TOCURRENCYID is not null and TOCURRENCYID <> @TOCURRENCYID)
                                        or (@RATE <> 0 and RATE <> @RATE)
                                        or (@TYPECODE is not null and TYPECODE <> @TYPECODE)
                                    )
                            )
                            begin
                                raiserror('ERR_CANNOTEDITFIELDS - Only date, time, and source can be edited on existing exchange rates.', 13, 1);
                            end

                            declare @UTCOFFSET integer = dbo.UFN_TIMEZONEENTRY_GETTIMEZONEOFFSETFROMUTC(@TIMEZONEENTRYID, @ASOFDATETIME, 0);
                            declare @ASOFDATETIMEOFFSET datetimeoffset = TODATETIMEOFFSET(@ASOFDATETIME, @UTCOFFSET);

                            declare @NEXTRATEASOFDATE datetimeoffset;
                            declare @OLDASOFDATE datetimeoffset;
                            declare @OLDISLATESTRATEFORASOFDATE bit = 0;
                            declare @OLDTYPECODE tinyint;

                            select 
                                @OLDASOFDATE = ASOFDATE,
                                @OLDISLATESTRATEFORASOFDATE = ISLATESTRATEFORASOFDATE,
                                @OLDTYPECODE = TYPECODE
                            from
                                dbo.CURRENCYEXCHANGERATE
                            where
                                ID = @ID;

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

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

                            update dbo.CURRENCYEXCHANGERATE
                            set
                                ASOFDATE = @ASOFDATETIMEOFFSET,
                                SOURCECODEID = @SOURCECODEID,
                                TIMEZONEENTRYID = @TIMEZONEENTRYID,
                                NEXTRATEASOFDATE = 
                                    case when @NEXTRATEASOFDATE is null or @OLDTYPECODE = 2
                                        then null 
                                        else DATEADD(ms, -1, @NEXTRATEASOFDATE) 
                                    end,

                                -- If we changed the ASOFDATE and there are multiple rates having the same ASOFDATE, 

                                -- USP_CURRENCYEXCHANGERATE_HANDLEEXCHANGERATEINSERT will handle setting ISLATESTRATEFORASOFDATE correctly.

                                ISLATESTRATEFORASOFDATE = 
                                    case when @OLDASOFDATE <> @ASOFDATETIMEOFFSET
                                        then 1 
                                        else @OLDISLATESTRATEFORASOFDATE
                                    end,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where 
                                ID = @ID;


                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                    return 0;