USP_DATALIST_FAFREGISTRANTBENEFIT_SUMMARY

Displays a summary of benefits, incentive 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_FAFREGISTRANTBENEFIT_SUMMARY    (
          @REGISTRANTID uniqueidentifier,
          @CURRENTAPPUSERID uniqueidentifier
                )
                as
                set nocount on;

                WITH GET_REGISTRANTBENEFITEXTENSION
        AS
        (    
        SELECT    REGISTRANT.ID, dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) NAME
                  , case when REGISTRANTBENEFITEXTENSION.BENEFITTYPECODE = 0 then REGISTRANT.BENEFITSWAIVED else 0 end as BENEFITSWAIVED
                    , CASE    WHEN ISNULL(BENEFITOPTION.NAME, '')= ''        THEN ISNULL(dbo.UFN_BENEFIT_GETNAME (REGISTRANTBENEFITEXTENSION.BENEFITID),'')  
                                  WHEN ISNULL(BENEFITOPTION.NAME, '')<> ''    THEN ISNULL(dbo.UFN_BENEFIT_GETNAME (REGISTRANTBENEFITEXTENSION.BENEFITID),'')  + '- ' + ISNULL(BENEFITOPTION.NAME, '') 
                              END AS BENEFITNAME
                      , REGISTRANTBENEFITEXTENSION.BENEFITTYPE, RB.QUANTITY --, RB.UNITVALUE

                      , REGISTRANTBENEFITEXTENSION.BENEFITPREFERENCE as UNITVALUE, RB.TOTALVALUE, RB.DETAILS, RB.BASECURRENCYID
                      , EVENT.NAME EVENTNAME,EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL,REGISTRANTBENEFITEXTENSION.BENEFITTYPECODE, RB.BENEFITID, RB.UNITVALUE UNITVALUE2
        FROM              dbo.REGISTRANT
        INNER JOIN    dbo.REGISTRANTBENEFITEXTENSION    ON REGISTRANTBENEFITEXTENSION.REGISTRANTID = REGISTRANT.ID
        LEFT JOIN        dbo.REGISTRANTBENEFIT    RB                ON RB.BENEFITID = dbo.REGISTRANTBENEFITEXTENSION.BENEFITID AND REGISTRANTBENEFITEXTENSION.REGISTRANTID = RB.REGISTRANTID
        INNER JOIN    dbo.EVENT                                        ON REGISTRANT.EVENTID = EVENT.ID
        LEFT JOIN        dbo.EVENTHIERARCHY                        ON EVENTHIERARCHY.ID = EVENT.ID
        LEFT JOIN        BENEFITOPTION                                 ON REGISTRANTBENEFITEXTENSION.BENEFITOPTIONID= BENEFITOPTION.ID
        WHERE              REGISTRANT.ID =@REGISTRANTID
        )
        ,  GET_REGISTRANTBENEFIT
        AS
        ( 
        SELECT          REGISTRANT.ID, dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID) NAME, 
                    case when RB2.BENEFITTYPECODE = 0 then REGISTRANT.BENEFITSWAIVED else 0 end as BENEFITSWAIVED,
                    B.NAME AS BENEFITNAME, RB2.BENEFITTYPE, QUANTITY, '' as UNITVALUE, 
                    TOTALVALUE, DETAILS, RB2.BASECURRENCYID, EVENT.NAME EVENTNAME, EVENTHIERARCHY.HIERARCHYPATH.GetLevel() EVENTLEVEL, '' AS BENEFITTYPECODE, RB2.BENEFITID, RB2.UNITVALUE UNITVALUE2
        FROM              dbo.REGISTRANT 
        LEFT JOIN        dbo.REGISTRANTBENEFIT   RB2 ON RB2.REGISTRANTID = REGISTRANT.ID
        LEFT JOIN        dbo.BENEFIT                        B       ON RB2.BENEFITID = B.ID
        INNER JOIN    dbo.EVENT                               ON REGISTRANT.EVENTID = EVENT.ID
        LEFT JOIN        dbo.EVENTHIERARCHY               ON EVENTHIERARCHY.ID = EVENT.ID
        WHERE              REGISTRANT.ID = @REGISTRANTID and RB2.ID is not null
        AND                  RB2.BENEFITID NOT IN (SELECT BENEFITID FROM GET_REGISTRANTBENEFITEXTENSION)
         )
        SELECT * FROM GET_REGISTRANTBENEFIT UNION ALL SELECT * FROM GET_REGISTRANTBENEFITEXTENSION  ORDER BY EVENTLEVEL, EVENTNAME, NAME