USP_DATALIST_REVENUEBATCHREGISTRANTCOMMITMENTS

            CREATE procedure dbo.USP_DATALIST_REVENUEBATCHREGISTRANTCOMMITMENTS (
                @CONSTITUENTID uniqueidentifier,
                @BATCHID uniqueidentifier
            ) as 

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());    

                declare @HOUSEHOLDID uniqueidentifier;

                select 
                    top(1) @HOUSEHOLDID = GROUPMEMBER.GROUPID
                from 
                    dbo.GROUPMEMBER
                left outer join 
                    dbo.GROUPDATA as GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                where 
                    GROUPMEMBER.MEMBERID = @CONSTITUENTID
                and 
                    GROUPDATA.GROUPTYPECODE = 0
                and 
                    dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1;

                if @HOUSEHOLDID is null
                    select top(1) 
                        @HOUSEHOLDID = BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID
                    from
                        dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                    left outer join
                        dbo.BATCHREVENUECONSTITUENT [GROUPCONSTITUENT] on BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = [GROUPCONSTITUENT].ID
                    where
                        BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID = @CONSTITUENTID
                    and
                        ([GROUPCONSTITUENT].ISGROUP = 1 and [GROUPCONSTITUENT].GROUPTYPECODE = 0);

                with CONSTITUENT_CTE as
                (
                    select
                        @CONSTITUENTID as ID

                    union

                    select
                        GROUPMEMBER.MEMBERID
                    from
                        dbo.GROUPMEMBER
                    left outer join
                        dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    where
                        (GROUPMEMBER.GROUPID = @HOUSEHOLDID or GROUPMEMBER.GROUPID = @CONSTITUENTID)
                    and ((GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE))
                            or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE)) 
                            or (GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE and GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE))

                    union

                    select
                        coalesce(BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID, BATCHREVENUECONSTITUENT.ID)
                    from
                        dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                    inner join
                        dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID = BATCHREVENUECONSTITUENT.ID
                    where
                        (BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @HOUSEHOLDID or BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @CONSTITUENTID)
                )
                select
                    BATCHREVENUEREGISTRANT.ID,
                    coalesce(BATCHREVENUEREGISTRANT.CONSTITUENTID, BATCHREVENUECONSTITUENT.ID),
                    coalesce(CONSTITUENT_NF.NAME, BATCHREVENUECONSTITUENT.NAME),
                    0,
                    dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBALANCE(BATCHREVENUEREGISTRANT.ID),
                    dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBALANCE(BATCHREVENUEREGISTRANT.ID),
                    [EVENT].STARTDATE,
                    6, --Event Registration

                    [EVENT].NAME,
                    case when coalesce(BATCHREVENUEREGISTRANT.CONSTITUENTID, BATCHREVENUECONSTITUENT.ID) = @CONSTITUENTID then 1 else 0 end,
                    EVENT.BASECURRENCYID APPLICATIONCURRENCYID
                from 
                    dbo.BATCHREVENUEREGISTRANT
                inner join
                    CONSTITUENT_CTE on BATCHREVENUEREGISTRANT.CONSTITUENTID = CONSTITUENT_CTE.ID
                outer apply
                    dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUEREGISTRANT.CONSTITUENTID) CONSTITUENT_NF
                left outer join 
                    dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = BATCHREVENUEREGISTRANT.CONSTITUENTID
                inner join 
                    dbo.[EVENT] on [EVENT].ID = BATCHREVENUEREGISTRANT.EVENTID
                where   
                    dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETBALANCE(BATCHREVENUEREGISTRANT.ID) > 0  
                    and BATCHREVENUEREGISTRANT.BATCHID = @BATCHID