UFN_APPEAL_GETBENEFITDETAILS_ALL_2

Returns the benefit details associated with an appeal, considering currencies.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@APPEALID	uniqueidentifier	IN
@GIFTAMOUNT	money	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASECURRENCYID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_APPEAL_GETBENEFITDETAILS_ALL_2
            (
                @APPEALID uniqueidentifier,
                @GIFTAMOUNT money,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASECURRENCYID uniqueidentifier = null
            )
            returns @RESULT table
            (
                ID uniqueidentifier, 
                BENEFITID uniqueidentifier,
                VALUE money,
                QUANTITY int,
                SEQUENCE int,
                VALUEPERCENT int,
                USEPERCENT bit,
                BASECURRENCYID uniqueidentifier
            )
            as
            begin

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @TRANSACTIONCURRENCYID is null
                    set @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID;

                if @BASECURRENCYID is null
                    set @BASECURRENCYID = @ORGANIZATIONCURRENCYID;

                declare @APPEALBENEFITID uniqueidentifier; 
                set @APPEALBENEFITID = null;

                select top 1
                    @APPEALBENEFITID = ID 
                from dbo.APPEALBENEFIT 
                where
                    APPEALID = @APPEALID and
                    dbo.UFN_APPEALBENEFIT_GETAMOUNTINCURRENCY(ID, @TRANSACTIONCURRENCYID) <= @GIFTAMOUNT
                order by AMOUNT desc;

                if not @APPEALBENEFITID is null
                    insert into @RESULT
                        select
                            APPEALBENEFITDETAIL.ID,
                            APPEALBENEFITDETAIL.BENEFITID,
                            APPEALBENEFITDETAIL.VALUE,
                            APPEALBENEFITDETAIL.QUANTITY,
                            APPEALBENEFITDETAIL.SEQUENCE,
                            APPEALBENEFITDETAIL.VALUEPERCENT,
                            BENEFIT.USEPERCENT,
                            APPEALBENEFITDETAIL.BASECURRENCYID
                        from dbo.APPEALBENEFITDETAIL
                            inner join dbo.BENEFIT on APPEALBENEFITDETAIL.BENEFITID = BENEFIT.ID
                        where APPEALBENEFITID = @APPEALBENEFITID and
                            APPEALBENEFITDETAIL.BASECURRENCYID = @BASECURRENCYID;

                return;
            end