USP_DATALIST_CONSTITUENTTRIBUTE

Returns a list of tributes where the current constituent is the tributee or the acknowledgee.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CONSTITUENTTRIBUTE
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null
                )
                as
                    set nocount on;

                    select distinct
                        TRIBUTE.ID,
                        TRIBUTE.TRIBUTETEXT,
                        TYPE.DESCRIPTION as TRIBUTETYPE,
                        TRIBUTE.ISACTIVE,
                        TRIBUTE.DATEADDED as DATECREATED,
                        cast(1 as bit) as ISTRIBUTEE,
                        case 
                            when exists 
                                (select T.TRIBUTEEID from dbo.TRIBUTE T inner join dbo.TRIBUTEACKNOWLEDGEE A on T.ID = A.TRIBUTEID where T.ID = TRIBUTE.ID and A.CONSTITUENTID = CONSTITUENT.ID)
                            then cast(1 as bit) else cast(0 as bit) end as ISACKNOWLEDGEE,
                        (
                            select dbo.UDA_BUILDLIST(SITE.NAME)
                            from dbo.SITE
                                inner join dbo.TRIBUTESITE on TRIBUTESITE.SITEID = SITE.ID
                            where TRIBUTESITE.TRIBUTEID = TRIBUTE.ID
                        ) as SITES
                    from
                        dbo.TRIBUTE
                    inner join
                        dbo.TRIBUTETYPECODE TYPE on TRIBUTE.TRIBUTETYPECODEID = TYPE.ID
                    inner join
                        dbo.CONSTITUENT on TRIBUTE.TRIBUTEEID = CONSTITUENT.ID
                    left join
                        dbo.TRIBUTESITE on TRIBUTE.ID = TRIBUTESITE.TRIBUTEID
                    where 
                        CONSTITUENT.ID = @CONSTITUENTID and
                        ((dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, TRIBUTESITE.SITEID) = 1) and
                            (@SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where TRIBUTESITE.SITEID = SITEFILTER.SITEID)
                            )
                        )

                    union all

                    select distinct
                        TRIBUTE.ID,
                        TRIBUTE.TRIBUTETEXT,
                        TYPE.DESCRIPTION as TRIBUTETYPE,
                        TRIBUTE.ISACTIVE,
                        TRIBUTE.DATEADDED as DATECREATED,
                        cast(0 as bit) as ISTRIBUTEE,
                        cast(1 as bit) as ISACKNOWLEDGEE,
                        (
                            select dbo.UDA_BUILDLIST(SITE.NAME)
                            from dbo.SITE
                                inner join dbo.TRIBUTESITE on TRIBUTESITE.SITEID = SITE.ID
                            where TRIBUTESITE.TRIBUTEID = TRIBUTE.ID
                        ) as SITES
                    from
                        dbo.TRIBUTE
                    inner join
                        dbo.TRIBUTETYPECODE TYPE on TRIBUTE.TRIBUTETYPECODEID = TYPE.ID
                    inner join
                        dbo.TRIBUTEACKNOWLEDGEE ACK on TRIBUTE.ID = ACK.TRIBUTEID
                    left join
                        dbo.TRIBUTESITE on TRIBUTE.ID = TRIBUTESITE.TRIBUTEID
                    where 
                        ACK.CONSTITUENTID = @CONSTITUENTID
                        and ((TRIBUTE.TRIBUTEEID is null) or (ACK.CONSTITUENTID <> TRIBUTE.TRIBUTEEID)) and
                        ((dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, TRIBUTESITE.SITEID) = 1) and
                            (@SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where TRIBUTESITE.SITEID = SITEFILTER.SITEID)
                            )
                        )
                    order by
                        TRIBUTE.DATEADDED, TRIBUTE.TRIBUTETEXT;