USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS

Handles inserting a spot rate if an existing rate hasn't been selected.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASECURRENCYID	uniqueidentifier	IN
@EXCHANGERATE	decimal(20, 8)	IN
@DATE	datetime	IN
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS(
    @ID uniqueidentifier output,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null,
    @EXCHANGERATE decimal(20,8) = null,
    @DATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
begin
    if @ID = '00000000-0000-0000-0000-000000000001'
    begin
        set @ID = newid()

        /*
        TODO: Check system privilege when PBI 102747 is addressed.
        if (not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
            and (not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1)
        begin
            raiserror('ERR_ADDSPOTRATE_NORIGHTS : You do not have rights to add a new spot rate.', 13, 1);
            return 1;
        end
        */

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

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

        declare @TIMEZONEENTRYID uniqueidentifier;
        declare @ASOFDATEWITHOFFSET datetimeoffset;

        set @TIMEZONEENTRYID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();
        set @ASOFDATEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@DATE, 0);        

        insert into dbo.CURRENCYEXCHANGERATE(
            ID, 
            FROMCURRENCYID,
            TOCURRENCYID,
            RATE,
            ASOFDATE,
            TYPECODE,
            SOURCECODEID,
            TIMEZONEENTRYID,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        values(
            @ID,
            @TRANSACTIONCURRENCYID,
            @BASECURRENCYID,
            @EXCHANGERATE,
            @ASOFDATEWITHOFFSET,
            2,
            null,
            @TIMEZONEENTRYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    end
end