UFN_DESIGNATION_RAISEDBYAPPEAL_INCURRENCY_INLINE

Creates a table of appeals, their descriptions, and the associated revenue information in a given currency.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
@CURRENCYCODE	tinyint	IN
@DECIMALDIGITS	tinyint	IN
@ROUNDINGTYPECODE	tinyint	IN

Definition

Copy



        CREATE function dbo.UFN_DESIGNATION_RAISEDBYAPPEAL_INCURRENCY_INLINE
            (
                @STARTDATE datetime, -- Expects EARLIESTTIME

                @ENDDATE datetime, -- Expects LATESTTIME

                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @CURRENCYCODE tinyint,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
            returns table
            as
            return
            (
                select 
                    BASEAPPEALREVENUESPLIT.APPEALID APPEALID,
                    BASEAPPEALREVENUESPLIT.DESIGNATIONID DESID,
                    coalesce(BASEAPPEALREVENUESPLIT.DESIGNATIONNAME, '(No designation)') DESNAME,
                    isnull(sum(BASEAPPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY),0) DESTOTALRECEIVED,
                    (
                        select
                            isnull(sum(APPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY),0)
                        from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUESPLIT
                        where (APPEALREVENUESPLIT.APPEALID = BASEAPPEALREVENUESPLIT.APPEALID) and
                            (APPEALREVENUESPLIT.DESIGNATIONID = BASEAPPEALREVENUESPLIT.DESIGNATIONID or (APPEALREVENUESPLIT.DESIGNATIONID is null and BASEAPPEALREVENUESPLIT.DESIGNATIONID is null)) and
                            (APPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                            (APPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null) and 
                                APPEALREVENUESPLIT.TRANSACTIONTYPECODE = 0 and
                                APPEALREVENUESPLIT.APPLICATIONCODE = 3
                    ) as DESTOTALRECEIVED_REGULAR,
                    isnull(sum(BASEAPPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY),0) + isnull(sum(BASEAPPEALREVENUESPLIT.TAXCLAIMAMOUNTINCURRENCY),0) DESGROSSAMOUNT
                from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) BASEAPPEALREVENUESPLIT
                where (BASEAPPEALREVENUESPLIT.APPEALID is not null) and 
                    (BASEAPPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                    (BASEAPPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null) and                          
                    (BASEAPPEALREVENUESPLIT.TRANSACTIONTYPECODE = 0 and BASEAPPEALREVENUESPLIT.APPLICATIONCODE not in (2,7))
                group by 
                    BASEAPPEALREVENUESPLIT.APPEALID, 
                    BASEAPPEALREVENUESPLIT.DESIGNATIONID, 
                    BASEAPPEALREVENUESPLIT.DESIGNATIONNAME
            )