USP_REPORT_RECONCILEDEPOSITS_MAIN

            CREATE procedure dbo.USP_REPORT_RECONCILEDEPOSITS_MAIN 
            (
                @DATETOUSE int = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @INCLUDETRANSACTIONAMOUNT bit = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            with execute as owner
            as
set nocount on;

declare @CURRENTAPPUSERID uniqueidentifier;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;

set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

declare @SQLTOEXEC nvarchar(max);

declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;

if @REVENUETRANSACTIONQUERY is not null begin
    if not exists(select ID from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
    if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
    else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
end


set @SQLTOEXEC = 
        'with RC as
        (
    select 
        R.ID,
        R.[BATCHNUMBER],
        C_NF.NAME,
        C.LOOKUPID,
        RP.PAYMENTMETHODCODE,
        RP.PAYMENTMETHOD,
        R.[AMOUNT],
        R.[DATE],
        RP.ID RPID,
        C.KEYNAME KN,
        C.FIRSTNAME FN,
        C.MIDDLENAME MN,
        R.TRANSACTIONAMOUNT TA,
        B.ISO4217 [BISO],
        B.CURRENCYSYMBOL [BCS],
        B.SYMBOLDISPLAYSETTINGCODE [BDC],
        B.DECIMALDIGITS [BDD],
        T.ISO4217 [TISO],
        T.CURRENCYSYMBOL [TCS],
        T.SYMBOLDISPLAYSETTINGCODE [TDS],
        T.DECIMALDIGITS [TDD]
    from dbo.REVENUE as R with (nolock)
    left join dbo.CONSTITUENT as C with (nolock) on C.ID = R.CONSTITUENTID
    inner join dbo.REVENUEPAYMENTMETHOD as RP with (nolock) on RP.REVENUEID = R.ID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(R.BASECURRENCYID) B
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(R.TRANSACTIONCURRENCYID) T
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF' + nchar(13);

if @REVENUETRANSACTIONQUERY is not null
    set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on R.[ID] = SELECTION.[ID]' + nchar(13);

set @SQLTOEXEC = @SQLTOEXEC + 
    'where RP.PAYMENTMETHODCODE in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

      and R.TRANSACTIONTYPECODE = 0 --Limit to payments

      and ' + nchar(13);

if @DATETOUSE = 0
    set @SQLTOEXEC = @SQLTOEXEC + 
        ' R.DATE between @STARTDATE and @ENDDATE ' + nchar(13);
else
    set @SQLTOEXEC = @SQLTOEXEC + 
        ' R.POSTDATE between @STARTDATE and @ENDDATE ' + nchar(13);

if @CONSTITUENTID is not null
    set @SQLTOEXEC = @SQLTOEXEC + 'and R.CONSTITUENTID = @CONSTITUENTID' + nchar(13);

if @ISADMIN = 0 
begin 
    if @APPUSER_IN_NONRACROLE = 0
        set @SQLTOEXEC = @SQLTOEXEC + '
          and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13);

    set @SQLTOEXEC = @SQLTOEXEC + '
      and exists (select 1 from dbo.REVENUESPLIT where REVENUEID = R.ID 
            -- Check site security

            and exists
            (
                select HASPERMISSION
                from dbo.UFN_SITEID_MAPFROM_REVENUEID(R.ID) REVSITES
                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''9A014198-B7EB-4A06-8FDC-951207AE6D03'', REVSITES.SITEID)
            ))' + nchar(13);
end    

set @SQLTOEXEC = @SQLTOEXEC + ')' + nchar(13);


set @SQLTOEXEC = @SQLTOEXEC +',
    REFUNDS_CTE as (
    select
        [CREDIT].[ID] as [CREDITID],
        '''' as [BATCHNUMBER],
        [CREDITPAYMENT].[ID],
        [CREDITPAYMENT].[REVENUEID],
        [C_NF].[NAME],
        [C].[LOOKUPID],
        [CREDITPAYMENT].[PAYMENTMETHODCODE],
        [CREDITPAYMENT].[PAYMENTMETHOD],
        -1 * [CREDITPAYMENT].[AMOUNT] as [AMOUNT],
        [CREDIT].[DATEADDED] as [DATE],
        [C].[KEYNAME],
        [C].[FIRSTNAME],
        [C].[MIDDLENAME],
        [CREDITPAYMENT].[AMOUNT] [TA],
        [B].[ISO4217] [BISO],
        [B].[CURRENCYSYMBOL] [BCS],
        [B].[SYMBOLDISPLAYSETTINGCODE] [BDC],
        [B].[DECIMALDIGITS] [BDD],
        [T].[ISO4217] [TISO],
        [T].[CURRENCYSYMBOL] [TCS],
        [T].[SYMBOLDISPLAYSETTINGCODE] [TDS],
        [T].[DECIMALDIGITS] [TDD]
    from dbo.[CREDIT]
    inner join dbo.[CREDITPAYMENT] on [CREDIT].[ID] = [CREDITPAYMENT].[CREDITID]
    left join dbo.[CONSTITUENT] as [C] with (nolock) on C.[ID] = [CREDIT].[CONSTITUENTID]
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME([C].[ID]) [C_NF]
    left join dbo.[REVENUESPLIT] on [REVENUESPLIT].[ID] = [CREDITPAYMENT].[REVENUESPLITID]
    inner join dbo.[REVENUE] on ([REVENUE].[ID] = [CREDITPAYMENT].[REVENUEID] or [REVENUE].[ID] = [REVENUESPLIT].[REVENUEID])
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES([REVENUE].[BASECURRENCYID]) B
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES([REVENUE].[TRANSACTIONCURRENCYID]) T' + nchar(13);

if @DATETOUSE = 0
    set @SQLTOEXEC +=
        'where [CREDIT].[DATEADDED] between @STARTDATE and @ENDDATE ' + nchar(13);
else
    set @SQLTOEXEC +=
        '    left join dbo.[CREDITGLDISTRIBUTION] on [CREDITGLDISTRIBUTION].[CREDITPAYMENTID] = [CREDITPAYMENT].[ID]
            left join dbo.[GLTRANSACTION] on [CREDITGLDISTRIBUTION].[GLTRANSACTIONID] = [GLTRANSACTION].[ID]
            where [GLTRANSACTION].[POSTDATE] between @STARTDATE and @ENDDATE ' + nchar(13);

set @SQLTOEXEC += 
    'and [CREDITPAYMENT].[PAYMENTMETHODCODE] in (0, 1, 2, 3) --Limit to Cash, Check, Credit Card, and Direct Debit

    ' + nchar(13);

if @CONSTITUENTID is not null
    set @SQLTOEXEC += 'and [CREDIT].[CONSTITUENTID] = @CONSTITUENTID' + nchar(13);

if @APPUSER_IN_NONRACROLE = 0 and @ISADMIN = 0
    set @SQLTOEXEC += 'and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, [CREDIT].[ID], @APPUSER_IN_NOSECGROUPROLE) = 1)' + nchar(13);

set @SQLTOEXEC +=
    'group by [CREDIT].[ID], [CREDITPAYMENT].[ID], [CREDITPAYMENT].[REVENUEID], [C_NF].[NAME], [C].[LOOKUPID], [CREDITPAYMENT].[PAYMENTMETHODCODE],
        [CREDITPAYMENT].[PAYMENTMETHOD], [CREDITPAYMENT].[AMOUNT], [CREDIT].[DATEADDED], [C].[KEYNAME], [C].[FIRSTNAME],
        [C].[MIDDLENAME], [B].[ISO4217], [B].[CURRENCYSYMBOL], [B].[SYMBOLDISPLAYSETTINGCODE], [B].[DECIMALDIGITS],
        [T].[ISO4217], [T].[CURRENCYSYMBOL], [T].[SYMBOLDISPLAYSETTINGCODE], [T].[DECIMALDIGITS]' + nchar(13);



set @SQLTOEXEC += ')' + nchar(13);


set @SQLTOEXEC = @SQLTOEXEC + 'select 
        ID,
        [BATCHNUMBER],
        NAME,
        LOOKUPID,
        PAYMENTMETHODCODE,
        PAYMENTMETHOD,
        [AMOUNT],
        CN CHECKNUMBER,
        CD CHECKDATE,
        CHN CARDHOLDERNAME,
        CCN CREDITCARDPARTIALNUMBER,
        CT CREDITTYPE,
        [DATE],
        TA TRANSACTIONAMOUNT,
        BISO BASEISOCURRENCYCODE,
        BCS BASECURRENCYSYMBOL,
        BDC BASECURRENCYSYMBOLDISPLAYSETTINGCODE,
        BDD BASEDECIMALDIGITS,
        TISO TRANSACTIONISOCURRENCYCODE,
        TCS TRANSACTIONCURRENCYSYMBOL,
        TDS TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
        TDD TRANSACTIONDECIMALDIGITS
    from
    (
        select 
                RC.ID,
                RC.[BATCHNUMBER],
                RC.NAME,
                RC.LOOKUPID,
                RC.PAYMENTMETHODCODE,
                RC.PAYMENTMETHOD,
                [AMOUNT],
                [CHECK].CHECKNUMBER CN,
                [CHECK].CHECKDATE CD,
                '''' as CHN,
                '''' as CCN,
                '''' as CT,
                RC.[DATE],
                RC.KN, 
                RC.FN, 
                RC.MN,
                RC.TA,
                RC.BISO, 
                RC.BCS, 
                RC.BDC, 
                RC.BDD,
                RC.TISO,
                RC.TCS,
                RC.TDS,
                RC.TDD
            from RC
            inner join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] with (nolock) on [CHECK].ID = RC.RPID
            where PAYMENTMETHODCODE  = 1
            union all 
            select 
                    RC.ID,
                    RC.[BATCHNUMBER],
                    RC.NAME,
                    RC.LOOKUPID,
                    RC.PAYMENTMETHODCODE,
                    RC.PAYMENTMETHOD,
                    RC.[AMOUNT],
                    '''' as CN,
                    '''' as CD,
                    [CC].CARDHOLDERNAME CHN,
                    [CC].CREDITCARDPARTIALNUMBER CCN,
                    [CCTYPE].DESCRIPTION as CT,
                    RC.[DATE],
                    RC.KN, 
                    RC.FN, 
                    RC.MN,
                RC.TA,
                RC.BISO, 
                RC.BCS, 
                RC.BDC, 
                RC.BDD,
                RC.TISO,
                RC.TCS,
                RC.TDS,
                RC.TDD
                from RC
                inner join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CC] with (nolock) on [CC].ID = RC.RPID
                left join dbo.CREDITTYPECODE as [CCTYPE] with (nolock) on [CCTYPE].ID = [CC].CREDITTYPECODEID
                where PAYMENTMETHODCODE  = 2
            union all
            select 
                    RC.ID,
                    RC.[BATCHNUMBER],
                    RC.NAME,
                    RC.LOOKUPID,
                    RC.PAYMENTMETHODCODE,
                    RC.PAYMENTMETHOD,
                    RC.[AMOUNT],
                    '''' as CN,
                    '''' as CD,
                    '''' as CHN,
                    '''' as CCN,
                    '''' as CT,
                    RC.[DATE],
                    RC.KN, 
                    RC.FN, 
                    RC.MN,
                RC.TA,
                RC.BISO, 
                RC.BCS, 
                RC.BDC, 
                RC.BDD,
                RC.TISO,
                RC.TCS,
                RC.TDS,
                RC.TDD
                from RC
                where PAYMENTMETHODCODE in (0, 3)

            union all
            select 
                [REFUNDS].[CREDITID],
                [REFUNDS].[BATCHNUMBER],
                [REFUNDS].[NAME],
                [REFUNDS].[LOOKUPID],
                [REFUNDS].[PAYMENTMETHODCODE],
                [REFUNDS].[PAYMENTMETHOD],
                [REFUNDS].[AMOUNT],
                [CHECK].CN,
                [CHECK].CD,
                [CC].CHN,
                [CC].CCN,
                [CC].CT,
                [REFUNDS].[DATE],
                [REFUNDS].[KEYNAME] as [KN],
                [REFUNDS].[FIRSTNAME] as [FN],
                [REFUNDS].[MIDDLENAME] as [MS],
                [REFUNDS].[TA],
                [REFUNDS].[BISO],
                [REFUNDS].[BCS],
                [REFUNDS].[BDC],
                [REFUNDS].[BDD],
                [REFUNDS].[TISO],
                [REFUNDS].[TCS],
                [REFUNDS].[TDS],
                [REFUNDS].[TDD]
            from [REFUNDS_CTE] [REFUNDS]
            outer apply (
                select 
                    [CHECK].CHECKNUMBER CN,
                    [CHECK].CHECKDATE CD
                from dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL [CHECK]
                where 
                    [CHECK].ID = [REFUNDS].ID and
                    [REFUNDS].[PAYMENTMETHODCODE] = 1
            ) [CHECK]
            outer apply (
                select
                    [CC].CARDHOLDERNAME CHN,
                    [CC].CREDITCARDPARTIALNUMBER CCN,
                    [CCTYPE].DESCRIPTION as CT
                from dbo.REVENUEPAYMENTMETHOD
                inner join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CC] with (nolock) on [CC].ID = REVENUEPAYMENTMETHOD.ID
                left join dbo.CREDITTYPECODE as [CCTYPE] with (nolock) on [CCTYPE].ID = [CC].CREDITTYPECODEID
                where 
                    REVENUEPAYMENTMETHOD.REVENUEID = [REFUNDS].REVENUEID and
                    [REFUNDS].[PAYMENTMETHODCODE] = 2
            ) [CC]
        ) a
    order by KN, FN, MN, [DATE], AMOUNT';

exec sp_executesql @SQLTOEXEC, 
    N'@DATETOUSE int, @STARTDATE datetime, @ENDDATE datetime, @CONSTITUENTID uniqueidentifier, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
    @DATETOUSE=@DATETOUSE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @CONSTITUENTID=@CONSTITUENTID, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;