USP_DATALIST_REGISTRANTBENEFIT_SUMMARY_2

Displays a summary of benefits for a given registrant and all of that registrant's guests.
Parameters

Parameter	Parameter Type	Mode	Description
@REGISTRANTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_REGISTRANTBENEFIT_SUMMARY_2
                (
                    @REGISTRANTID uniqueidentifier,
          @CURRENTAPPUSERID uniqueidentifier
                )
                as
                set nocount on;

        declare @EVENTID uniqueidentifier = (select EVENTID from dbo.REGISTRANT where ID = @REGISTRANTID)

        declare @REGISTRANTS table(ID uniqueidentifier);
        insert into @REGISTRANTS
        select ID 
        from dbo.REGISTRANT 
        where CONSTITUENTID = (select CONSTITUENTID from dbo.REGISTRANT where ID = @REGISTRANTID)
        and 
        (
        EVENTID in (select ID from dbo.UFN_CHILDEVENTSWITHSITEACCESS((select MAINEVENTID from dbo.EVENT where ID = @EVENTID), @CURRENTAPPUSERID))
        or 
        EVENTID = @EVENTID
        )

                select 
                    REGISTRANT.ID,
                    dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) NAME,
                    REGISTRANT.BENEFITSWAIVED,
                    ISNULL(dbo.UFN_BENEFIT_GETNAME (BENEFITID),''),
                    QUANTITY,
                    UNITVALUE,
                    TOTALVALUE,
                    DETAILS,
                    REGISTRANTBENEFIT.BASECURRENCYID,
                    /*
                    case
                        when (select count(ID) from dbo.REGISTRANTBENEFIT where REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID) = 0 then '<None>'
                        else (select dbo.UDA_BUILDLIST(BENEFIT.NAME) from dbo.BENEFIT inner join dbo.REGISTRANTBENEFIT on BENEFIT.ID = REGISTRANTBENEFIT.BENEFITID where REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID)
                    end                    
                    */
          EVENT.NAME EVENTNAME,
            EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL
                from dbo.REGISTRANT
                left outer join dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
        inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        left join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
                where REGISTRANT.ID in (select ID from @REGISTRANTS)

                union all

                select 
                    REGISTRANT.ID,
                    dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) NAME,
                    REGISTRANT.BENEFITSWAIVED,
                    ISNULL(dbo.UFN_BENEFIT_GETNAME (BENEFITID),''),
                    QUANTITY,
                    UNITVALUE,
                    TOTALVALUE,
                    DETAILS,
                    REGISTRANTBENEFIT.BASECURRENCYID,
                    /*case
                        when (select count(ID) from dbo.REGISTRANTBENEFIT where REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID) = 0 then '<None>'
                        else (select dbo.UDA_BUILDLIST(BENEFIT.NAME) from dbo.BENEFIT inner join dbo.REGISTRANTBENEFIT on BENEFIT.ID = REGISTRANTBENEFIT.BENEFITID where REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID)
                    end    */
          EVENT.NAME EVENTNAME,
            EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL
                from dbo.REGISTRANT
                left outer join dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
        inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        left join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
                where REGISTRANT.GUESTOFREGISTRANTID in (select ID from @REGISTRANTS)

        order by EVENTLEVEL, EVENTNAME, NAME