USP_DATALIST_RECONCILE_MATCHINGGIFTS

                CREATE procedure dbo.USP_DATALIST_RECONCILE_MATCHINGGIFTS
                (
                    @NUMBERTOSHOWCODE smallint = 0,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @STARTDATE datetime;
                if @NUMBERTOSHOWCODE = 1 --Last 30 Days

                    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
                else if @NUMBERTOSHOWCODE = 2 --Last 60 Days

                    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-59,@CURRENTDATE));
                else if @NUMBERTOSHOWCODE = 3 --Last 90 Days

                    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
                else if @NUMBERTOSHOWCODE = 4 --Last Year

                    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)))

                declare @ISADMIN bit = 0;

                if @CURRENTAPPUSERID is not null
                  select @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                if @ISADMIN = 0
                  select 
                    REVENUESPLIT.ID AS ID,
                    CONSTITUENT.ID AS CONSTITUENTID,
                    CONSTITUENT.NAME AS CONSTITUENTNAME,
                    REVENUESPLIT.TRANSACTIONAMOUNT as AMOUNT,
                    CONVERT(Date, REVENUE.DATE) as DATE,
                    CASE     
                    WHEN (dl5.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + isnull(dl4.NAME, '<Unspecified>') + ' \ ' + dl5.NAME
                    WHEN (dl4.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + dl4.NAME
                    WHEN (dl3.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + dl3.NAME
                    WHEN (dl2.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + dl2.NAME
                    ELSE dl1.NAME
                    END  as DESIGNATIONNAME,
                    REVENUE.ID as REVENUEID,
                    REVENUE.TRANSACTIONCURRENCYID
                    from dbo.FINANCIALTRANSACTION as REVENUE
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                    inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                    inner join dbo.DESIGNATIONLEVEL dl1 on DESIGNATION.DESIGNATIONLEVEL1ID = dl1.id
                    left join dbo.DESIGNATIONLEVEL dl2 on DESIGNATION.DESIGNATIONLEVEL2ID = dl2.id
                    left join dbo.DESIGNATIONLEVEL dl3 on DESIGNATION.DESIGNATIONLEVEL3ID = dl3.id
                    left join dbo.DESIGNATIONLEVEL dl4 on DESIGNATION.DESIGNATIONLEVEL4ID = dl4.id
                    left join dbo.DESIGNATIONLEVEL dl5 on DESIGNATION.DESIGNATIONLEVEL5ID = dl5.id
                    where REVENUE.DELETEDON is null and REVENUESPLIT.DELETEDON is null and
                    REVENUE.TYPECODE = 0
                    and REVENUESPLIT_EXT.APPLICATIONCODE = 7 --MGCLAIM 

                    and (@NUMBERTOSHOWCODE = 0 or (REVENUE.DATE >= @STARTDATE))
                    -- Verify the payment isn't fully applied

                    and (select coalesce(sum(case when APPLICATIONEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERTINVERSE(AMOUNT,APPLICATIONEXCHANGERATEID) end), 0) from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = REVENUESPLIT.ID) < REVENUESPLIT.TRANSACTIONAMOUNT 
                    and dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATION.ID,@CURRENTAPPUSERID) = 1 -- Check site security

                else
                  select 
                    REVENUESPLIT.ID AS ID,
                    CONSTITUENT.ID AS CONSTITUENTID,
                    CONSTITUENT.NAME AS CONSTITUENTNAME,
                    REVENUESPLIT.TRANSACTIONAMOUNT as AMOUNT,
                    CONVERT(Date, REVENUE.DATE) as DATE,
                    CASE     
                    WHEN (dl5.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + isnull(dl4.NAME, '<Unspecified>') + ' \ ' + dl5.NAME
                    WHEN (dl4.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + dl4.NAME
                    WHEN (dl3.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + dl3.NAME
                    WHEN (dl2.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + dl2.NAME
                    ELSE dl1.NAME
                    END  as DESIGNATIONNAME,
                    REVENUE.ID as REVENUEID,
                    REVENUE.TRANSACTIONCURRENCYID
                    from dbo.FINANCIALTRANSACTION as REVENUE
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                    inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                    inner join dbo.DESIGNATIONLEVEL dl1 on DESIGNATION.DESIGNATIONLEVEL1ID = dl1.id
                    left join dbo.DESIGNATIONLEVEL dl2 on DESIGNATION.DESIGNATIONLEVEL2ID = dl2.id
                    left join dbo.DESIGNATIONLEVEL dl3 on DESIGNATION.DESIGNATIONLEVEL3ID = dl3.id
                    left join dbo.DESIGNATIONLEVEL dl4 on DESIGNATION.DESIGNATIONLEVEL4ID = dl4.id
                    left join dbo.DESIGNATIONLEVEL dl5 on DESIGNATION.DESIGNATIONLEVEL5ID = dl5.id
                    where REVENUE.DELETEDON is null and REVENUESPLIT.DELETEDON is null and
                    REVENUE.TYPECODE = 0
                    and REVENUESPLIT_EXT.APPLICATIONCODE = 7 --MGCLAIM 

                    and (@NUMBERTOSHOWCODE = 0 or (REVENUE.DATE >= @STARTDATE))
                    -- Verify the payment isn't fully applied

                    and (select coalesce(sum(case when APPLICATIONEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERTINVERSE(AMOUNT,APPLICATIONEXCHANGERATEID) end), 0) from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = REVENUESPLIT.ID) < REVENUESPLIT.TRANSACTIONAMOUNT