USP_DATALIST_TRIBUTEREVENUE

Returns a list of revenue records that have been applied to the given tribute.
Parameters

Parameter	Parameter Type	Mode	Description
@TRIBUTEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_TRIBUTEREVENUE
                (
                    @TRIBUTEID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                    declare @APPUSER_IN_NONRACROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                    declare @APPUSER_IN_NOSECGROUPROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                    declare @APPUSER_IN_NONSITEROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                    declare @APPUSER_IN_NOSITEROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                    select 
                        REVENUE.ID,
                        NF.NAME,
                        REVENUE.DATE,
                        RT.AMOUNT,
                        REVENUE.TRANSACTIONTYPE,
                        coalesce(RT.AMOUNT + RTTCA.TAXCLAIMAMOUNT, RT.AMOUNT) as GROSSAMOUNT,
                        RT.BASECURRENCYID
                    from
                        dbo.REVENUETRIBUTE RT
                    inner join
                        dbo.REVENUE on RT.REVENUEID = REVENUE.ID
                    inner join
                        dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                    left join
                        dbo.REVENUETRIBUTETAXCLAIMAMOUNT RTTCA on RT.ID = RTTCA.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                    where RT.TRIBUTEID = @TRIBUTEID and
                        (
                        --Bug 130859 - AdamBu - Added security checks on revenue donors and short circuit

                        --    designation site check if user is admin/in all sites role.

                            @ISADMIN = 1 or
                            (
                                (
                                    @APPUSER_IN_NONRACROLE = 1 or 
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, '8452a585-7d36-4974-b363-c798de136e21', CONSTITUENT.ID) = 1
                                ) and
                                (
                                    @APPUSER_IN_NONSITEROLE = 1 or
                                    (
                                        exists 
                                        (
                                            select 1
                                            from dbo.UFN_CONSTITUENT_GETSITES(CONSTITUENT.ID) SITE
                                            where dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORSITE(@CURRENTAPPUSERID, '8452a585-7d36-4974-b363-c798de136e21', SITE.SITEID) = 1
                                        ) and 
                                        exists
                                        (
                                            select 1
                                            from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                                        )
                                    )
                                )
                            )
                        )
                    order by
                        REVENUE.DATE, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, RT.AMOUNT;