USP_DATALIST_EVENTSPONSORBENEFIT_SUMMARY

CREATE procedure dbo.USP_DATALIST_EVENTSPONSORBENEFIT_SUMMARY
(
  @EVENTSPONSORID uniqueidentifier
)
as
    set nocount on;

    select 
        ES.ID,
        isnull(OC.NAME, IC.NAME) as NAME,
        ES.BENEFITSWAIVED,
        B.NAME,
        SB.QUANTITY,
        SB.UNITVALUE,
        SB.TOTALVALUE,
        SB.DETAILS,
        B.BASECURRENCYID,
        E.NAME,
        EH.HIERARCHYPATH.GetLevel() EVENTLEVEL
    from dbo.EVENTSPONSOR ES(nolock) 
    inner join dbo.SPONSORBENEFITEXTENSION SB(nolock)
        on ES.ID = SB.SPONSORID
    inner join dbo.BENEFIT B(nolock)
        on B.ID = SB.BENEFITID
    left outer join dbo.CONSTITUENT IC(nolock)
        on IC.ID = ES.CONSTITUENTID
    left outer join dbo.CONSTITUENT OC(nolock)
        on OC.ID = ES.ORGANIZATIONCONSTITUENTID
    left outer join dbo.[EVENT] E(nolock)
        on ES.EVENTID = E.ID
    left join dbo.EVENTHIERARCHY EH(nolock)
        on EH.ID = E.ID
    where ES.ID = @EVENTSPONSORID;