UFN_REGISTRATIONPACKAGEPRICE_VALIDEVENTPRICE

            CREATE function dbo.UFN_REGISTRATIONPACKAGEPRICE_VALIDEVENTPRICE
            (
                @REGISTRATIONPACKAGEID uniqueidentifier = null,
                @EVENTPRICEID uniqueidentifier = null                
            ) 
            returns bit with execute as caller
            as
            begin                

                declare @EVENTHIERARCHYPATH hierarchyid;
                select 
                    @EVENTHIERARCHYPATH = EVENTHIERARCHY.HIERARCHYPATH
                from 
                    dbo.REGISTRATIONPACKAGE
                    inner join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = REGISTRATIONPACKAGE.EVENTID
                where 
                    REGISTRATIONPACKAGE.ID = @REGISTRATIONPACKAGEID

                if exists(
                        select 1
                        from 
                            dbo.EVENTHIERARCHY
                            inner join dbo.EVENTPRICE on EVENTPRICE.EVENTID = EVENTHIERARCHY.ID
                        where
                            EVENTHIERARCHY.HIERARCHYPATH.IsDescendantOf(@EVENTHIERARCHYPATH) = 1
                            and EVENTPRICE.ID = @EVENTPRICEID
                    )
                    return 1;

                -- To maintain backwards compatibility with old customizations that don't use the hierarchy

                if exists(
                        select 
                            1
                        from 
                            dbo.EVENT
                            inner join dbo.REGISTRATIONPACKAGE on REGISTRATIONPACKAGE.EVENTID = EVENT.ID or REGISTRATIONPACKAGE.EVENTID = EVENT.MAINEVENTID
                            inner join dbo.EVENTPRICE ON EVENTPRICE.EVENTID = EVENT.ID
                        where
                            EVENTPRICE.ID = @EVENTPRICEID and
                            REGISTRATIONPACKAGE.ID = @REGISTRATIONPACKAGEID
                    )
                    return 1;

                return 0;
            end