USP_DATALIST_FINANCIALTRANSACTIONDOCUMENTATION

This datalist returns all documentation for a financial transaction.
Parameters

Parameter	Parameter Type	Mode	Description
@FINANCIALTRANSACTIONID	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.
Definition

Copy


                create procedure dbo.USP_DATALIST_FINANCIALTRANSACTIONDOCUMENTATION 
                (
                    @FINANCIALTRANSACTIONID uniqueidentifier = null,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = '',
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @ISSYSADMIN bit;
                    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    -- Get the notes

                    select
                        FINANCIALTRANSACTIONNOTE.ID,
                        '810e231b-af0a-4877-ae7a-9014a5cb5526' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        FINANCIALTRANSACTIONNOTE.DATEENTERED,
                        FINANCIALTRANSACTIONNOTETYPECODE.DESCRIPTION as [TYPE],
                        FINANCIALTRANSACTIONNOTE.TITLE,
                        CONSTITUENT.NAME,
                        CAST(FINANCIALTRANSACTIONNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Financial transaction note',FINANCIALTRANSACTIONNOTE.ID) as ANNOTATIONEXISTS
                    from
                        dbo.FINANCIALTRANSACTIONNOTE
                    inner join
                        dbo.FINANCIALTRANSACTIONNOTETYPECODE
                    on
                        FINANCIALTRANSACTIONNOTETYPECODE.ID = FINANCIALTRANSACTIONNOTE.FINANCIALTRANSACTIONNOTETYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = FINANCIALTRANSACTIONNOTE.AUTHORID
                    where 
                        FINANCIALTRANSACTIONNOTE.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or FINANCIALTRANSACTIONNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,FINANCIALTRANSACTIONNOTETYPECODE.ID) = 1)

                    -- Get the media links

                    union all
                    select
                        FINANCIALTRANSACTIONMEDIALINK.ID,
                        '6e6c790f-e8bb-4b32-954d-445aed44f6a8' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        FINANCIALTRANSACTIONMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        FINANCIALTRANSACTIONMEDIALINK.DATEENTERED,
                        FINANCIALTRANSACTIONMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        FINANCIALTRANSACTIONMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        CAST(FINANCIALTRANSACTIONMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.FINANCIALTRANSACTIONMEDIALINK
                    inner join
                        dbo.FINANCIALTRANSACTIONMEDIALINKTYPECODE
                    on
                        FINANCIALTRANSACTIONMEDIALINKTYPECODE.ID = FINANCIALTRANSACTIONMEDIALINK.FINANCIALTRANSACTIONMEDIALINKTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = FINANCIALTRANSACTIONMEDIALINK.AUTHORID
                    where 
                        FINANCIALTRANSACTIONMEDIALINK.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or FINANCIALTRANSACTIONMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,FINANCIALTRANSACTIONMEDIALINKTYPECODE.ID) = 1)

                    -- Get the attachments

                    union all
                    select
                        FINANCIALTRANSACTIONATTACHMENT.ID,
                        '3f4127d9-a970-4027-aaea-0fadf767ac19' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        FINANCIALTRANSACTIONATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        FINANCIALTRANSACTIONATTACHMENT.DATEENTERED,
                        FINANCIALTRANSACTIONATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        FINANCIALTRANSACTIONATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        CAST(FINANCIALTRANSACTIONATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.FINANCIALTRANSACTIONATTACHMENT
                    inner join
                        dbo.FINANCIALTRANSACTIONATTACHMENTTYPECODE
                    on
                        FINANCIALTRANSACTIONATTACHMENTTYPECODE.ID = FINANCIALTRANSACTIONATTACHMENT.FINANCIALTRANSACTIONATTACHMENTTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = FINANCIALTRANSACTIONATTACHMENT.AUTHORID
                    where 
                        FINANCIALTRANSACTIONATTACHMENT.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or FINANCIALTRANSACTIONATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,FINANCIALTRANSACTIONATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;