USP_DATALIST_CONSTITUENTREVENUEDOCUMENTATION

Returns a list of all revenue documentation for the given constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DOCUMENTATIONTYPECODE	tinyint	IN	Documentation type
@TITLE	nvarchar(50)	IN	Title
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@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_CONSTITUENTREVENUEDOCUMENTATION
                (
                    @CONSTITUENTID uniqueidentifier,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = '',
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    select    
                        REVENUENOTE.ID,
                        cast(FINANCIALTRANSACTION.DATE as datetime) as REVENUEDATE,
                        FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE,
                        FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT,
                        '3a42d039-00b1-4ac4-9f96-2cd65e80e9fd' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        REVENUENOTE.DATEENTERED,
                        REVENUENOTETYPECODE.DESCRIPTION as [TYPE],
                        REVENUENOTE.TITLE,
                        CONSTITUENT.NAME,
                        FINANCIALTRANSACTION.ID as REVENUEID,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Revenue Detail Note',REVENUENOTE.ID) as ANNOTATIONEXISTS,
                        dbo.UFN_REVENUE_BUILDSITELIST(FINANCIALTRANSACTION.ID) as SITES,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
                    from
                        dbo.REVENUENOTE
                        inner join dbo.FINANCIALTRANSACTION on REVENUENOTE.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.REVENUENOTETYPECODE on REVENUENOTETYPECODE.ID = REVENUENOTE.REVENUENOTETYPECODEID
                        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUENOTE.AUTHORID
                    where 
                        FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID
                        and FINANCIALTRANSACTION.DELETEDON is null
                        and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) 
                        and (@TITLE = '' or REVENUENOTE.TITLE like @TITLE + '%') 
                        and exists 
                            (
                                select top 1 RSSUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                                inner join dbo.REVENUESPLIT_EXT on RSSUB.ID = REVENUESPLIT_EXT.ID
                                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                where RSSUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                and RSSUB.DELETEDON is null
                                and RSSUB.TYPECODE <> 1
                                and (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)))
                                and 
                                (
                                    @SITEFILTERMODE = 0
                                    or 
                                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                                )
                            )

                    union all

                    select    
                        REVENUEMEDIALINK.ID,
                        cast(FINANCIALTRANSACTION.DATE as datetime) as REVENUEDATE,
                        FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE,
                        FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT,
                        'f91aec3c-60d2-46e3-b875-59424b4f0155' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        REVENUEMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        REVENUEMEDIALINK.DATEENTERED,
                        REVENUEMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        REVENUEMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        FINANCIALTRANSACTION.ID as REVENUEID,
                        cast(0 as bit) as ANNOTATIONEXISTS,
                        dbo.UFN_REVENUE_BUILDSITELIST(FINANCIALTRANSACTION.ID) as SITES,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
                    from 
                        dbo.REVENUEMEDIALINK
                        inner join dbo.FINANCIALTRANSACTION on REVENUEMEDIALINK.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        inner join dbo.REVENUEMEDIALINKTYPECODE on REVENUEMEDIALINKTYPECODE.ID = REVENUEMEDIALINK.REVENUEMEDIALINKTYPECODEID
                        left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEMEDIALINK.AUTHORID
                    where 
                        FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID and
                        FINANCIALTRANSACTION.DELETEDON is null and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or REVENUEMEDIALINK.TITLE like @TITLE + '%')
                        and exists 
                            (
                                select top 1 RSSUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                                inner join dbo.REVENUESPLIT_EXT on RSSUB.ID = REVENUESPLIT_EXT.ID
                                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                where RSSUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                and RSSUB.DELETEDON is null
                                and RSSUB.TYPECODE <> 1
                                and (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)))
                                and 
                                (
                                    @SITEFILTERMODE = 0
                                    or 
                                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                                )
                            )

                    union all

                    select    
                        REVENUEATTACHMENT.ID,
                        cast(FINANCIALTRANSACTION.DATE as datetime) as REVENUEDATE,
                        FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE,
                        FINANCIALTRANSACTION.BASEAMOUNT as AMOUNT,
                        'a6ddac7d-a2e1-4990-be72-765443238c90' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        REVENUEATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        REVENUEATTACHMENT.DATEENTERED,
                        REVENUEATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        REVENUEATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        FINANCIALTRANSACTION.ID as REVENUEID,
                        cast(0 as bit) as ANNOTATIONEXISTS,
                        dbo.UFN_REVENUE_BUILDSITELIST(FINANCIALTRANSACTION.ID) as SITES,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
                    from
                        dbo.REVENUEATTACHMENT
                        inner join dbo.FINANCIALTRANSACTION on REVENUEATTACHMENT.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        inner join dbo.REVENUEATTACHMENTTYPECODE on REVENUEATTACHMENTTYPECODE.ID = REVENUEATTACHMENT.REVENUEATTACHMENTTYPECODEID
                        left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEATTACHMENT.AUTHORID
                    where 
                        FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID and
                        FINANCIALTRANSACTION.DELETEDON is null and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or REVENUEATTACHMENT.TITLE like @TITLE + '%')
                        and exists 
                            (
                                select top 1 RSSUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                                inner join dbo.REVENUESPLIT_EXT on RSSUB.ID = REVENUESPLIT_EXT.ID
                                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                where RSSUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                and RSSUB.DELETEDON is null
                                and RSSUB.TYPECODE <> 1
                                and (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)))
                                and 
                                (
                                    @SITEFILTERMODE = 0
                                    or 
                                    exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                                )
                            )

                    order by
                        DATEENTERED desc, DOCUMENTATIONTYPE;