USP_DATALIST_CONSTITUENTSUBSIDIARYREVENUE

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTSUBSIDIARYREVENUE
                (
                    @CONSTITUENTID uniqueidentifier = null    
                )
                as
                set nocount on;
        select
            ID,
            NAME,
            count(REVENUEID)
        from(

            select
                CONSTITUENT.ID,
                CONSTITUENT.NAME,
                REVENUE.ID REVENUEID
            from dbo.REVENUE                
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                inner join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
                inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                left outer join dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
                left outer join dbo.REVENUE MATCHINGGIFTREVENUE on MATCHINGGIFTREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
                left outer join dbo.CONSTITUENT MATCHINGGIFTCONSTITUENT on MATCHINGGIFTREVENUE.CONSTITUENTID = MATCHINGGIFTCONSTITUENT.ID
            where ORGANIZATIONDATA.PARENTCORPID = @CONSTITUENTID
                and REVENUESCHEDULE.ISPENDING = 0    --Isn't Pending

                and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TRANSACTIONTYPECODE <> 3)
                and (dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0 or (REVENUE.TRANSACTIONTYPECODE = 2             --Recurring Gift

                and REVENUESCHEDULE.STATUSCODE in (0,5)    --Active or lapsed

                and REVENUE.AMOUNT > 0))                    --Has Value???


            union all

            -- Event Registrations --


            select                    
                CONSTITUENT.ID,
                CONSTITUENT.NAME,
                REGISTRANT.ID REVENUEID
            from 
                dbo.REGISTRANT
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
                inner join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
                inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
            where 
                ORGANIZATIONDATA.PARENTCORPID =  @CONSTITUENTID
                and dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) > 0
        ) REVENUE
        group by ID, NAME;