USP_DATALIST_GIFTAIDREFUNDSUMMARY

Returns a list of all Gift Aid refunds for a constituent.
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
@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.
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_GIFTAIDREFUNDSUMMARY
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @CURRENCYCODE tinyint = 2
                )
                as
                    set nocount on;

                    declare @CURRENCYID uniqueidentifier
                    declare @ORGTOMYBASEEXCHANGERATE uniqueidentifier = null

                    declare @MULTICURRENCYENABLED bit;
                    set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
                    if @MULTICURRENCYENABLED = 0 
                      set @CURRENCYCODE = 1;

                    if @CURRENCYCODE = 1
                      set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    if @CURRENCYCODE = 3
                    begin
                      set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
                      if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                      begin     
                        set @CURRENCYCODE = 1
                      end
                    end;                    

                    select
                        REVENUESPLITGIFTAIDREFUND.ID,
                        REVENUESPLITGIFTAIDREFUND.REVENUESPLITID,
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                        ORIGINALTAXCLAIMNUMBER,
                        case @CURRENCYCODE
                          when 2 then REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSACTIONTAXCLAIMAMOUNT
                          when 1 then REVENUESPLITGIFTAIDREFUND.ORIGINALORGANIZATIONTAXCLAIMAMOUNT
                          when 3 then dbo.UFN_REVENUESPLITGIFTAIDREFUND_GETAMOUNTINCURRENCY(REVENUESPLITGIFTAIDREFUND.ID,@CURRENCYID)
                          else REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMAMOUNT
                        end as ORIGINALTAXCLAIMAMOUNT,                        
                        REFUNDTAXCLAIMNUMBER,
                        DATEREFUNDED,
                        REFUNDSOURCECODE,
                        REFUNDSOURCE,
                        REASON,
                        case @CURRENCYCODE
                            when 2 then REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSACTIONCURRENCYID
                            when 0 then REVENUESPLITGIFTAIDREFUND.ORIGINALBASECURRENCYID
                            else @CURRENCYID
                        end as DISPLAYCURRENCY
                    from
                        dbo.REVENUESPLITGIFTAIDREFUND
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM
                            on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.REVENUESPLIT_EXT
                            on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID                
                    where
                        REVENUESPLITGIFTAIDREFUND.CONSTITUENTID = @CONSTITUENTID
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                        and (
                            select count(*) 
                            from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.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)))
                            ) > 0
                        and    (
                            @SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                        inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES on REVSITES.SITEID = SITEFILTER.SITEID
                                )
                        )                                        

                    union all

                    -- Refunds created for deleted applications

                    select
                        REVENUESPLITGIFTAIDREFUND.ID,
                        null,
                        null,
                        REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMNUMBER,
                        case @CURRENCYCODE
                          when 2 then REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSACTIONTAXCLAIMAMOUNT
                          when 1 then REVENUESPLITGIFTAIDREFUND.ORIGINALORGANIZATIONTAXCLAIMAMOUNT
                          when 3 then dbo.UFN_REVENUESPLITGIFTAIDREFUND_GETAMOUNTINCURRENCY(REVENUESPLITGIFTAIDREFUND.ID,@CURRENCYID)                          
                          else REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMAMOUNT
                        end as ORIGINALTAXCLAIMAMOUNT,
                        REVENUESPLITGIFTAIDREFUND.REFUNDTAXCLAIMNUMBER,
                        REVENUESPLITGIFTAIDREFUND.DATEREFUNDED,
                        REVENUESPLITGIFTAIDREFUND.REFUNDSOURCECODE,
                        REVENUESPLITGIFTAIDREFUND.REFUNDSOURCE,
                        REVENUESPLITGIFTAIDREFUND.REASON,
                        case @CURRENCYCODE
                            when 2 then REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSACTIONCURRENCYID
                            when 0 then REVENUESPLITGIFTAIDREFUND.ORIGINALBASECURRENCYID
                            else @CURRENCYID
                        end as DISPLAYCURRENCY                        
                    from
                        dbo.REVENUESPLITGIFTAIDREFUND
                    left join 
                        dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    where REVENUESPLITGIFTAIDREFUND.CONSTITUENTID = @CONSTITUENTID 
                        and (REVENUESPLITGIFTAIDREFUND.REVENUESPLITID is null or 
                                (FINANCIALTRANSACTIONLINEITEM.DELETEDON is not null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1))

                    order by DATEREFUNDED ASC