UFN_DESIGNATIONLEVELGOAL_GETGOALINCURRENCY

Returns the goal of the given designation level goal in the given currency.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode	Description
@DESIGNATIONLEVELGOALID	uniqueidentifier	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


        CREATE function dbo.UFN_DESIGNATIONLEVELGOAL_GETGOALINCURRENCY
        (
            @DESIGNATIONLEVELGOALID uniqueidentifier,
            @CURRENCYID uniqueidentifier
        )
        returns money
        as
        begin
        declare @GOAL money = 0;

        if (@CURRENCYID is null) or (@CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
        begin
            select
                @GOAL = DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL
            from
                dbo.DESIGNATIONLEVELGOAL
            where
                DESIGNATIONLEVELGOAL.ID = @DESIGNATIONLEVELGOALID;
        end
        else
        begin
            declare @RECORDBASECURRENCYID uniqueidentifier;
            declare @RECORDDATE datetime;

            select
                @RECORDBASECURRENCYID = DESIGNATIONLEVELGOAL.BASECURRENCYID,
                @RECORDDATE = DESIGNATIONLEVELGOAL.DATEADDED
            from
                dbo.DESIGNATIONLEVELGOAL
            where
                DESIGNATIONLEVELGOAL.ID = @DESIGNATIONLEVELGOALID;

            if @CURRENCYID = @RECORDBASECURRENCYID
            begin
                select
                    @GOAL = DESIGNATIONLEVELGOAL.GOAL
                from
                    dbo.DESIGNATIONLEVELGOAL
                where
                    DESIGNATIONLEVELGOAL.ID = @DESIGNATIONLEVELGOALID;
            end
            else
            begin
                declare @CURRENCYEXCHANGERATEID uniqueidentifier;

                set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENCYID, @RECORDDATE, 1, null);
                if (@CURRENCYEXCHANGERATEID is not null)
                begin
                    select
                        @GOAL = dbo.UFN_CURRENCY_CONVERT(DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL, @CURRENCYEXCHANGERATEID)
                    from
                        dbo.DESIGNATIONLEVELGOAL
                    where
                        DESIGNATIONLEVELGOAL.ID = @DESIGNATIONLEVELGOALID;
                end
                else
                begin
                    set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @RECORDDATE, 1, null);

                    select
                        @GOAL = dbo.UFN_CURRENCY_CONVERTINVERSE(DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL, @CURRENCYEXCHANGERATEID)
                    from
                        dbo.DESIGNATIONLEVELGOAL
                    where
                        DESIGNATIONLEVELGOAL.ID = @DESIGNATIONLEVELGOALID;
                end
            end
        end    

        return @GOAL;
    end