UFN_DDRETURN_MATCHINGREVENUES

            CREATE function dbo.UFN_DDRETURN_MATCHINGREVENUES(@INDIVIDUALIDENTIFICATIONNUMBER nvarchar(15), @INDIVIDUALNAME nvarchar(22), @KEYNAME nvarchar(50), @FIRSTNAME nvarchar(50), @ROUTINGNUMBER nvarchar(9), @ACCOUNTNUMBER nvarchar(50), @AMOUNT money)
                returns @REVENUES TABLE
                (
                    REVENUEID uniqueidentifier,
                    NAME nvarchar(154),
                    AMOUNT money,
                    REVENUEDATE datetime,
                    ACCOUNTNUMBER nvarchar(50)
                )
            as
                begin

                    --USP_GET_KEY_ACCESS must be called before this function to obtain results


                    with CA_CTE (ID, ACCOUNTNUMBER)
                    AS
                    (
                    select CA.ID as ID,
                            convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)) as ACCOUNTNUMBER
                        from dbo.CONSTITUENTACCOUNT as CA
                        left join dbo.FINANCIALINSTITUTION as FI 
                        on CA.FINANCIALINSTITUTIONID = FI.ID
                        where (@ROUTINGNUMBER is null or @ROUTINGNUMBER = FI.ROUTINGNUMBER)
                        and (@ACCOUNTNUMBER is null or 
                            (CA.ACCOUNTNUMBERINDEX = dbo.UFN_GET_MAC_FOR_TEXT(@ACCOUNTNUMBER, 'dbo.CONSTITUENTACCOUNT')  or CA.ACCOUNTNUMBERINDEX  is null
                            and convert(nvarchar(50), DecryptByKey(CA.ACCOUNTNUMBER)) = @ACCOUNTNUMBER))
                    UNION
                    select BRCA.ID as ID,
                            convert(nvarchar(50), DecryptByKey(BRCA.ACCOUNTNUMBER)) as ACCOUNTNUMBER
                        from dbo.BATCHREVENUECONSTITUENTACCOUNT as BRCA
                        left join dbo.FINANCIALINSTITUTION as BRFI 
                        on BRCA.FINANCIALINSTITUTIONID = BRFI.ID
                        where (@ROUTINGNUMBER is null or @ROUTINGNUMBER = BRFI.ROUTINGNUMBER)
                        and (@ACCOUNTNUMBER is null or 
                            (BRCA.ACCOUNTNUMBERINDEX = dbo.UFN_GET_MAC_FOR_TEXT(@ACCOUNTNUMBER, 'dbo.BATCHREVENUECONSTITUENTACCOUNT') or BRCA.ACCOUNTNUMBERINDEX  is null
                            and convert(nvarchar(50), DecryptByKey(BRCA.ACCOUNTNUMBER)) = @ACCOUNTNUMBER))
                    ),
                    CONSTIT_CTE (ID)
                    AS
                    (
                    select ID as ID
                        from dbo.CONSTITUENT
                        where (@INDIVIDUALIDENTIFICATIONNUMBER is null 
                            or replace(LOOKUPID,'-','') = replace(@INDIVIDUALIDENTIFICATIONNUMBER,'-',''))
                        and (@INDIVIDUALNAME is null 
                            or KEYNAME like case when CHARINDEX(',',@INDIVIDUALNAME) = 0 
                                            then @INDIVIDUALNAME 
                                            else LEFT(@INDIVIDUALNAME,CHARINDEX(',',@INDIVIDUALNAME) - 1) 
                                            end + '%')
                        and (@KEYNAME is null 
                            or KEYNAME like @KEYNAME + '%')
                        and (@FIRSTNAME is null 
                            or FIRSTNAME like @FIRSTNAME + '%')
                    UNION
                    select ID as ID
                        from dbo.BATCHREVENUECONSTITUENT
                        where @INDIVIDUALIDENTIFICATIONNUMBER is null
                        and (@INDIVIDUALNAME is null 
                            or KEYNAME like case when CHARINDEX(',',@INDIVIDUALNAME) = 0 
                                            then @INDIVIDUALNAME 
                                            else LEFT(@INDIVIDUALNAME,CHARINDEX(',',@INDIVIDUALNAME) - 1) 
                                            end + '%')
                        and (@KEYNAME is null 
                            or KEYNAME like @KEYNAME + '%')
                        and (@FIRSTNAME is null 
                            or FIRSTNAME like @FIRSTNAME + '%')
                    ),
                    REVENUE_CTE (ID, CONSTITUENTID, CONSTITUENTACCOUNTID, AMOUNT, DATE)
                    AS
                    (
                    select R.ID as ID,
                            R.CONSTITUENTID as CONSTITUENTID,
                            DDPMD.CONSTITUENTACCOUNTID as CONSTITUENTACCOUNTID,
                            R.AMOUNT as AMOUNT,
                            R.DATE
                        from dbo.REVENUE as R
                        inner join dbo.REVENUEPAYMENTMETHOD as RPM on RPM.REVENUEID = R.ID
                        inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as DDPMD on DDPMD.ID = RPM.ID
                        where RPM.PAYMENTMETHODCODE = 3
                        and R.TRANSACTIONTYPECODE = 0
                        and (@AMOUNT is null or R.AMOUNT = @AMOUNT)
                    UNION
                    select BR.ID as ID,
                            BR.CONSTITUENTID as CONSTITUENTID,
                            BR.CONSTITUENTACCOUNTID as CONSTITUENTACCOUNTID,
                            BR.AMOUNT as AMOUNT,
                            BR.DATE
                        from dbo.BATCHREVENUE as BR
                        join dbo.batch as B 
                        on BR.BATCHID = B.ID
                        where BR.PAYMENTMETHODCODE = 3
                        and BR.TYPECODE = 0
                        and (@AMOUNT is null or BR.AMOUNT = @AMOUNT)
                        and B.STATUSCODE = 0
                    )

                    insert into @REVENUES (REVENUEID, NAME, AMOUNT, REVENUEDATE, ACCOUNTNUMBER)
                    select REVENUE_CTE.ID as REVENUEID,
                        dbo.UFN_CONSTITUENT_BUILDNAME(CONSTIT_CTE.ID) as NAME,
                        REVENUE_CTE.AMOUNT as AMOUNT,
                        REVENUE_CTE.DATE as REVENUEDATE,
                        CA_CTE.ACCOUNTNUMBER as ACCOUNTNUMBER
                    from REVENUE_CTE 
                    inner join CA_CTE
                    on REVENUE_CTE.CONSTITUENTACCOUNTID = CA_CTE.ID
                    inner join CONSTIT_CTE 
                    on REVENUE_CTE.CONSTITUENTID = CONSTIT_CTE.ID;

                    return;
                end