UFN_SMARTQUERY_POTENTIALMATCHINGGIFTS

                create function dbo.UFN_SMARTQUERY_POTENTIALMATCHINGGIFTS
                (
                    @CONSTITUENTSELECTIONID uniqueidentifier,
                    @ORGANIZATIONID uniqueidentifier,
                    @STARTDATE datetime,
                    @ENDDATE datetime,
                    @CURRENTAPPUSERID uniqueidentifier = null,           
                    @MAXROWS int
                )
                returns table
                as
                return
                (

                    with REVENUE_CTE as
                    (
                        select
                            FINANCIALTRANSACTION.ID,
                            FINANCIALTRANSACTION.DATE,
                            FINANCIALTRANSACTION.CONSTITUENTID,
                            FINANCIALTRANSACTION.TYPECODE,
                            FINANCIALTRANSACTION.BASEAMOUNT,
                            FINANCIALTRANSACTION.TYPE,
                            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
                            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                        from
                            dbo.FINANCIALTRANSACTION
                                with (INDEX (IX_FINANCIALTRANSACTION_CONSTITUENTID_TYPECODE_DATE))
                              left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID                                
                              left join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                              left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                                
                        where
                            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORREVENUE(@CURRENTAPPUSERID, '778D5449-AA1E-4E30-9FB2-E8181E53F45F', FINANCIALTRANSACTION.ID) = 1
                    )
                    select top (@MAXROWS)
                        [ORGC].ID as [ORGID],
                        [ORGC].NAME as [ORGNAME], 
                        [INDC].ID as [INDIVIDUALID],
                        [INDC].NAME as [INDIVIDUALNAME],
                        sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) as [REVENUEAMOUNT],
                        sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) as [TRANSACTIONREVENUEAMOUNT],
                        sum(FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT) as [ORGANIZATIONREVENUEAMOUNT],
                        cast(REVENUE.DATE as datetime) [REVENUEDATE],
                        REVENUE.ID as [REVENUEID],
                        REVENUE.TYPECODE as [REVENUETYPECODE],
                        REVENUE.TYPE as [REVENUETYPE],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as [REVENUEPAYMENTMETHODCODE],
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD as [REVENUEPAYMENTMETHOD],
                        dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) as [DESIGNATIONLIST],
                        REVENUE.BASECURRENCYID,
                        REVENUE.TRANSACTIONCURRENCYID,
                        CURRENCY.ID as ORGANIZATIONCURRENCYID
                    from 
                        dbo.CONSTITUENT as [ORGC]
                        inner join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = [ORGC].ID
                        inner join dbo.CONSTITUENT as [INDC] on [INDC].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                        left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) as [IDSET] on [IDSET].ID = [INDC].ID
                        left join REVENUE_CTE as REVENUE on REVENUE.CONSTITUENTID = [INDC].ID and REVENUE.ID not in (select MGSOURCEREVENUEID from dbo.REVENUEMATCHINGGIFT inner join dbo.REVENUE on REVENUE.ID = REVENUEMATCHINGGIFT.ID where REVENUE.CONSTITUENTID = [ORGC].ID)
                        left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
                        left join dbo.CURRENCY on CURRENCY.ISORGANIZATIONCURRENCY = 1
                    where 
                        (@ORGANIZATIONID is null or [ORGC].ID = @ORGANIZATIONID)
                        and ((select count(ID) from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = [ORGC].ID) > 0)
                        and [ORGC].ISORGANIZATION = 1
                        and [INDC].ISORGANIZATION = 0
                        and (@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                        and (@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                        and (@CONSTITUENTSELECTIONID is null or not [IDSET].ID is null)
                        and REVENUE.TYPECODE not in (1, 2, 3)
                        and REVENUESPLIT_EXT.TYPECODE = 0
                        and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE not in (5, 6, 8, 9) --JamesWill 01/03/2008 CR290624-122807 

                        and (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 4 or not exists(select ID from dbo.STOCKSALE where STOCKDETAILID = REVENUEPAYMENTMETHOD.ID)) -- Sold stock

                        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                                (
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '778D5449-AA1E-4E30-9FB2-E8181E53F45F', ORGC.ID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '778D5449-AA1E-4E30-9FB2-E8181E53F45F', ORGC.ID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '778D5449-AA1E-4E30-9FB2-E8181E53F45F', INDC.ID) = 1)
                                    and
                                    (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '778D5449-AA1E-4E30-9FB2-E8181E53F45F', INDC.ID) = 1)
                                )
                            )
                    group by
                        REVENUE.ID,
                        ORGC.ID,
                        ORGC.NAME,
                        INDC.ID,
                        INDC.NAME,
                        REVENUE.DATE,
                        REVENUE.TYPECODE,
                        REVENUE.TYPE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        RELATIONSHIP.ID,
                        REVENUE.BASECURRENCYID,
                        REVENUE.TRANSACTIONCURRENCYID,
                        CURRENCY.ID
                    order by
                        ORGNAME,
                        INDIVIDUALNAME
                )