USP_MULTILEVELEVENT_ADD

CREATE procedure dbo.USP_MULTILEVELEVENT_ADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @EVENTMANAGEMENTTEMPLATEID uniqueidentifier = null,
    @ATTRIBUTEDEFINED bit = 0,
    @EVENTHIERARCHY xml = null,
    @SOURCEEVENTID uniqueidentifier = null
)
as
begin
    set nocount on;

    if @ID is null
        set @ID = newid()

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try

        if not exists (
            select 
                SITEID 
            from 
                dbo.EVENTMANAGEMENTTEMPLATE
            left outer join
                dbo.EVENTMANAGEMENTTEMPLATESITE on EVENTMANAGEMENTTEMPLATESITE.EVENTMANAGEMENTTEMPLATEID = EVENTMANAGEMENTTEMPLATE.ID
            where 
                EVENTMANAGEMENTTEMPLATE.ID = @EVENTMANAGEMENTTEMPLATEID
            and
                (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, SITEID) = 1 or SITEID is null)
        )
        begin
            raiserror('ERR_EVENTMANAGEMENTTEMPLATEID_NOSITEACCESS',13,1);
            return 1;
        end

        declare @HierarchyTable table
        (
            ID uniqueidentifier,
            PARENTEVENTID uniqueidentifier,
            NAME nvarchar(100),
            DESCRIPTION nvarchar(255),
            STARTDATE datetime,
            STARTTIME dbo.UDT_HOURMINUTE,
            ENDDATE datetime,
            ENDTIME dbo.UDT_HOURMINUTE,
            CAPACITY int,
            EVENTLOCATIONID uniqueidentifier,
            EVENTLOCATIONROOMID uniqueidentifier,
            EVENTLOCATIONCONTACTID uniqueidentifier,
            SITES xml,
            EVENTCATEGORYCODEID uniqueidentifier,
            APPEALID uniqueidentifier,
            DISPLAYORDER int,
            ISAUCTION bit,
            COPYFROMEVENTID uniqueidentifier,
            COPYTASKS bit,
            COPYINVITATIONS bit,
            COPYINVITEES bit,
            COPYEXPENSES bit,
            COPYPRICES bit,
            COPYATTRIBUTES bit,
            COPYPREFERENCES bit,
            COPYTEAMSTRUCTURE bit,
            COPYTEAMFUNDRAISERS bit,
            COPYLODGINGOPTIONS bit,
            COPYJOBOCCURRENCES bit,
            --COPYCAMPAIGNS bit,

            EVENTMANAGEMENTOPTIONSCONFLICT bit,
            HIDEFROMCALENDAR bit,
            ISEXISTINGEVENT bit,
            DESIGNATIONSONFEES bit,
            COPYDESIGNATIONS bit
        );

        insert into 
            @HierarchyTable
        select
            T.c.value('(ID)[1]','uniqueidentifier'),
            T.c.value('(PARENTEVENTID)[1]','uniqueidentifier'),
            T.c.value('(NAME)[1]','nvarchar(100)'),
            T.c.value('(DESCRIPTION)[1]','nvarchar(255)'),
            T.c.value('(STARTDATE)[1]','datetime'),
            T.c.value('(STARTTIME)[1]','dbo.UDT_HOURMINUTE'),
            T.c.value('(ENDDATE)[1]','datetime'),
            T.c.value('(ENDTIME)[1]','dbo.UDT_HOURMINUTE'),
            T.c.value('(CAPACITY)[1]','int'),
            T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier'),
            T.c.value('(EVENTLOCATIONROOMID)[1]','uniqueidentifier'),
            T.c.value('(EVENTLOCATIONCONTACTID)[1]','uniqueidentifier'),
            case when T.c.exist('./SITES/ITEM') = 1 then T.c.query('(SITES)[1]') else null end as SITES,
            T.c.value('(EVENTCATEGORYCODEID)[1]','uniqueidentifier'),
            T.c.value('(APPEALID)[1]','uniqueidentifier'),
            T.c.value('(DISPLAYORDER)[1]','int'),
            T.c.value('(ISAUCTION)[1]','bit'),
            T.c.value('(COPYFROMEVENTID)[1]','uniqueidentifier'),
            T.c.value('(COPYTASKS)[1]','bit'),
            T.c.value('(COPYINVITATIONS)[1]','bit'),
            T.c.value('(COPYINVITEES)[1]','bit'),    
            T.c.value('(COPYEXPENSES)[1]','bit'),
            T.c.value('(COPYPRICES)[1]','bit'),
            T.c.value('(COPYATTRIBUTES)[1]','bit'),
            T.c.value('(COPYPREFERENCES)[1]','bit'),
            T.c.value('(COPYTEAMSTRUCTURE)[1]','bit'),
            T.c.value('(COPYTEAMFUNDRAISERS)[1]','bit'),
            T.c.value('(COPYLODGINGOPTIONS)[1]','bit'),
            T.c.value('(COPYJOBOCCURRENCES)[1]','bit'),
            --T.c.value('(COPYCAMPAIGNS)[1]','bit'),

            0,
            T.c.value('(HIDEFROMCALENDAR)[1]','bit'),
            T.c.value('(ISEXISTINGEVENT)[1]','bit'),
            T.c.value('(DESIGNATIONSONFEES)[1]','bit'),
            T.c.value('(COPYDESIGNATIONS)[1]','bit')
        from
            @EVENTHIERARCHY.nodes('/EVENTHIERARCHY/ITEM') T(c);

        --JamesWill 2010-10-18

        update @HierarchyTable
            set DESIGNATIONSONFEES = 0 
        where DESIGNATIONSONFEES is null;

        -- validate that there's at least one event in the structure

        if ((select count(ID) from @HierarchyTable) = 0)
            raiserror('ERR_EVENTHIERARCHY_NOEVENTS',13,1)

        -- validate that there's only one root in the structure

        if ((select count(ID) from @HierarchyTable where PARENTEVENTID is null) > 1)
            raiserror('ERR_EVENTHIERARCHY_MULTIPLEROOT',13,1);    

        -- validate that auction events do not have children

        if exists (
            select
                [CHILDEVENT].ID
            from
                @HierarchyTable [AUCTIONEVENT]
            inner join
                @HierarchyTable [CHILDEVENT] on [CHILDEVENT].PARENTEVENTID = [AUCTIONEVENT].ID
            where
                [AUCTIONEVENT].ISAUCTION = 1
        )
            raiserror('ERR_EVENTHIERARCHY_AUCTIONEVENT_CANNOTHAVECHILDREN',13,1);

        if exists (
            select
                [CHILDEVENT].ID
            from
                @HierarchyTable [TEAMFUNDRAISINGEVENT]
            inner join
                @HierarchyTable [CHILDEVENT] on [CHILDEVENT].PARENTEVENTID = [TEAMFUNDRAISINGEVENT].ID
            where
                [TEAMFUNDRAISINGEVENT].APPEALID is not null
        )
            raiserror('ERR_EVENTHIERARCHY_TEAMFUNDRAISINGEVENT_CANNOTHAVECHILDREN',13,1);

        -- validate that the user has site access to each existing event

        if exists(
                select 1
                from
                    @HierarchyTable [HIERARCHYEVENT]
                where
                    HIERARCHYEVENT.ISEXISTINGEVENT = 1
                    and not exists (
                        select 1 
                        from 
                            dbo.EVENT 
                            left join dbo.EVENTSITE on EVENTSITE.EVENTID = EVENT.ID
                        where 
                            EVENT.ID = HIERARCHYEVENT.ID
                            and dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, SITEID, '8e0dc59c-f36e-4144-aa44-4043119a6380', 1) = 1
                    )
                )
            raiserror('ERR_EVENT_SITEACCESSREQUIRED', 13, 1);

        if exists(
                select 1
                from
                    @HierarchyTable [HIERARCHYEVENT]
                where
                    HIERARCHYEVENT.ISEXISTINGEVENT = 1
                    and exists (select 1 from dbo.EVENTHIERARCHY where EVENTHIERARCHY.ID = HIERARCHYEVENT.ID)
                )
            raiserror('ERR_EVENT_EXISTINGEVENTSMUSTNOTBEINAHIERARCHY', 13, 1);

        select
            @ID = ID
        from
            @HierarchyTable
        where
            PARENTEVENTID is null;

        if @SOURCEEVENTID is not null and exists (select 1 from dbo.EVENTHIERARCHY where ID = @ID)
            raiserror('ERR_EVENTHIERARCHY_MAINEVENTALREADYINHIERARCHY',13,1);

        declare @MAINEVENTID uniqueidentifier;
        declare @EVENTID uniqueidentifier;
        declare @PARENTEVENTID uniqueidentifier;
        declare @NAME nvarchar(100);
        declare @DESCRIPTION nvarchar(255);
        declare @STARTDATE datetime;
        declare @STARTTIME dbo.UDT_HOURMINUTE;
        declare @ENDDATE datetime;
        declare @ENDTIME dbo.UDT_HOURMINUTE;
        declare @CAPACITY int;
        declare @EVENTLOCATIONID uniqueidentifier;
        declare @EVENTLOCATIONROOMID uniqueidentifier;
        declare @EVENTLOCATIONCONTACTID uniqueidentifier;
        declare @SITES xml;
        declare @EVENTCATEGORYCODEID uniqueidentifier;
        declare @APPEALID uniqueidentifier;
        declare @DISPLAYORDER int;
        declare @ISAUCTION bit;
        declare @COPYFROMEVENTID uniqueidentifier;
        declare @COPYTASKS bit;
        declare @COPYINVITATIONS bit;
        declare @COPYINVITEES bit;
        declare @COPYEXPENSES bit;
        declare @COPYPRICES bit;
        declare @COPYATTRIBUTES bit;
        declare @COPYPREFERENCES bit;
        declare @COPYTEAMSTRUCTURE bit;
        declare @COPYTEAMFUNDRAISERS bit;
        declare @COPYLODGINGOPTIONS bit;
        declare @COPYJOBOCCURRENCES bit;
        declare @COPYCAMPAIGNS bit;
        declare @HIDEFROMCALENDAR bit;
        declare @ISEXISTINGEVENT bit;
        declare @COPYDESIGNATIONS bit;
        declare @DESIGNATIONSONFEES bit;

        declare EVENTCURSOR cursor local fast_forward for
            select
                ID,
                PARENTEVENTID,
                NAME,
                DESCRIPTION,
                STARTDATE,
                STARTTIME,
                ENDDATE,
                ENDTIME,
                CAPACITY,
                EVENTLOCATIONID,
                EVENTLOCATIONROOMID,
                EVENTLOCATIONCONTACTID,
                SITES,
                EVENTCATEGORYCODEID,
                APPEALID,
                DISPLAYORDER,
                ISAUCTION,
                COPYFROMEVENTID,
                COPYTASKS,
                COPYINVITATIONS,
                COPYINVITEES,
                COPYEXPENSES,
                COPYPRICES,
                COPYATTRIBUTES,
                COPYPREFERENCES,
                COPYTEAMSTRUCTURE,
                COPYTEAMFUNDRAISERS,
                COPYLODGINGOPTIONS,
                COPYJOBOCCURRENCES,
                --,COPYCAMPAIGNS

                HIDEFROMCALENDAR,
                ISEXISTINGEVENT,
                DESIGNATIONSONFEES,
                COPYDESIGNATIONS
            from
                @HierarchyTable
            -- ensure we create the main event first so the MAINEVENTID foreign key doesn't fail

            order by
                (case when PARENTEVENTID is null then 0 else 1 end) asc; 

        open EVENTCURSOR;
        fetch next from EVENTCURSOR into @EVENTID,
            @PARENTEVENTID,
            @NAME,
            @DESCRIPTION,
            @STARTDATE,
            @STARTTIME,
            @ENDDATE,
            @ENDTIME,
            @CAPACITY,
            @EVENTLOCATIONID,
            @EVENTLOCATIONROOMID,
            @EVENTLOCATIONCONTACTID,
            @SITES,
            @EVENTCATEGORYCODEID,
            @APPEALID,
            @DISPLAYORDER,
            @ISAUCTION,
            @COPYFROMEVENTID,
            @COPYTASKS,
            @COPYINVITATIONS,
            @COPYINVITEES,
            @COPYEXPENSES,
            @COPYPRICES,
            @COPYATTRIBUTES,
            @COPYPREFERENCES,
            @COPYTEAMSTRUCTURE,
            @COPYTEAMFUNDRAISERS,
            @COPYLODGINGOPTIONS,
            @COPYJOBOCCURRENCES,
            --,@COPYCAMPAIGNS

            @HIDEFROMCALENDAR,
            @ISEXISTINGEVENT,
            @DESIGNATIONSONFEES,
            @COPYDESIGNATIONS

        while @@FETCH_STATUS = 0
        begin

            if @EVENTID = @ID
                set @MAINEVENTID = null;
            else
                set @MAINEVENTID = @ID;

            -- Do not add the main event if it exists

            if (@MAINEVENTID is null and @SOURCEEVENTID is not null) or @ISEXISTINGEVENT = 1
                update 
                    dbo.EVENT 
                set 
                    NAME = @NAME,
                    DESCRIPTION = @DESCRIPTION,    
                    STARTDATE = @STARTDATE,
                    STARTTIME = @STARTTIME,
                    ENDDATE = @ENDDATE,
                    ENDTIME = @ENDTIME,
                    CAPACITY = @CAPACITY,
                    EVENTLOCATIONID = @EVENTLOCATIONID,
                    EVENTLOCATIONROOMID = @EVENTLOCATIONROOMID,
                    EVENTLOCATIONCONTACTID = @EVENTLOCATIONCONTACTID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE,
                    EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID,
                    HIDEFROMCALENDAR = @HIDEFROMCALENDAR,
                    MAINEVENTID = @MAINEVENTID,
                    DESIGNATIONSONFEES=@DESIGNATIONSONFEES
                where 
                    ID = @EVENTID
            else
                exec dbo.USP_EVENT_ADD
                        @ID = @EVENTID output,
                        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @NAME = @NAME,
                        @DESCRIPTION = @DESCRIPTION,
                        @STARTDATE = @STARTDATE,
                        @STARTTIME = @STARTTIME,
                        @ENDDATE = @ENDDATE,
                        @ENDTIME = @ENDTIME,
                        @CAPACITY = @CAPACITY,
                        @EVENTLOCATIONID = @EVENTLOCATIONID,
                        @EVENTLOCATIONCONTACTID = @EVENTLOCATIONCONTACTID,
                        @MAINEVENTID = @MAINEVENTID,
                        @COPYFROMEVENTID = @COPYFROMEVENTID,
                        @COPYTASKS = @COPYTASKS,
                        @COPYEXPENSES = @COPYEXPENSES,
                        @COPYPRICES = @COPYPRICES,
                        @COPYINVITATIONS = @COPYINVITATIONS,
                        @COPYINVITEES = @COPYINVITEES,
                        @COPYATTRIBUTES = @COPYATTRIBUTES,
                        @ATTRIBUTEDEFINED = @ATTRIBUTEDEFINED,
                        @SITES = @SITES,
                        @COPYPREFERENCES = @COPYPREFERENCES,
                        @EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID,
                        @COPYTEAMSTRUCTURE = @COPYTEAMSTRUCTURE,
                        @COPYTEAMFUNDRAISERS = @COPYTEAMFUNDRAISERS,
                        @APPEALID = @APPEALID,
                        @ADDQUICKCOMPARE = 0,
                        @COPYLODGINGOPTIONS = @COPYLODGINGOPTIONS,
                        @EVENTLOCATIONROOMID = @EVENTLOCATIONROOMID,
                        @COPYJOBOCCURRENCES = @COPYJOBOCCURRENCES,
                        @ISAUCTION = @ISAUCTION,
                        --,@COPYCAMPAIGNS = @COPYCAMPAIGNS

                        @HIDEFROMCALENDAR = @HIDEFROMCALENDAR,
                        @DESIGNATIONSONFEES = @DESIGNATIONSONFEES,
                        @COPYDESIGNATIONS = @COPYDESIGNATIONS

            fetch next from EVENTCURSOR into @EVENTID,
                @PARENTEVENTID,
                @NAME,
                @DESCRIPTION,
                @STARTDATE,
                @STARTTIME,
                @ENDDATE,
                @ENDTIME,
                @CAPACITY,
                @EVENTLOCATIONID,
                @EVENTLOCATIONROOMID,
                @EVENTLOCATIONCONTACTID,
                @SITES,
                @EVENTCATEGORYCODEID,
                @APPEALID,
                @DISPLAYORDER,
                @ISAUCTION,
                @COPYFROMEVENTID,
                @COPYTASKS,
                @COPYINVITATIONS,
                @COPYINVITEES,
                @COPYEXPENSES,
                @COPYPRICES,
                @COPYATTRIBUTES,
                @COPYPREFERENCES,
                @COPYTEAMSTRUCTURE,
                @COPYTEAMFUNDRAISERS,
                @COPYLODGINGOPTIONS,
                @COPYJOBOCCURRENCES,
                --,@COPYCAMPAIGNS

                @HIDEFROMCALENDAR,
                @ISEXISTINGEVENT,
                @DESIGNATIONSONFEES,
                @COPYDESIGNATIONS;
        end        
        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

        close EVENTCURSOR;
        deallocate EVENTCURSOR;

        -- Use a recursive CTE to build up paths for the events (i.e. '/2/1/3/')

        with EVENT_ROWNUMBER_CTE as (
            select
                ID,
                PARENTEVENTID,
                row_number() over (partition by PARENTEVENTID order by DISPLAYORDER) as ROWNUM,
                DISPLAYORDER
            from
                @HierarchyTable
        ),
        EVENT_PATHS_CTE as (
            select
                ID,
                PARENTEVENTID,
                cast(dbo.UFN_EVENT_GETHIERARCHYPATHINSERTPOSITION(null) as varchar(900)) as HIERARCHYPATH,
                DISPLAYORDER
            from
                @HierarchyTable
            where
                PARENTEVENTID is null

            union all

            select
                EVENT_ROWNUMBER_CTE.ID,
                EVENT_ROWNUMBER_CTE.PARENTEVENTID,
                cast(HIERARCHYPATH + cast(EVENT_ROWNUMBER_CTE.ROWNUM as varchar(20)) + '/' as varchar(900)),
                EVENT_ROWNUMBER_CTE.DISPLAYORDER
            from
                EVENT_PATHS_CTE
            inner join
                EVENT_ROWNUMBER_CTE on EVENT_ROWNUMBER_CTE.PARENTEVENTID = EVENT_PATHS_CTE.ID
        )
        insert into dbo.EVENTHIERARCHY (
            ID,
            HIERARCHYPATH,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            EVENT_PATHS_CTE.ID,
            EVENT_PATHS_CTE.HIERARCHYPATH,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            EVENT_PATHS_CTE;

        declare @EVENTMANAGEMENTINSTANCEID uniqueidentifier;    
        exec dbo.USP_EVENTMANAGEMENTTEMPLATE_CREATEINSTANCE @EVENTMANAGEMENTTEMPLATEID, @EVENTMANAGEMENTINSTANCEID output, @CHANGEAGENTID;

        -- Check for conflicts before setting properties

        update 
            @HierarchyTable
        set 
            EVENTMANAGEMENTOPTIONSCONFLICT = 1
        where 
            (
                ID = @ID 
                and @SOURCEEVENTID is not null 
                and dbo.UFN_EVENT_CONFLICTSWITHMANAGEMENTLEVELINSTANCE(ID, @EVENTMANAGEMENTINSTANCEID,1,1,1,1,1,1,1,1,1) = 1
            ) 
            or 
            (
                ISEXISTINGEVENT = 1
                and dbo.UFN_EVENT_CONFLICTSWITHMANAGEMENTLEVELINSTANCE(ID, @EVENTMANAGEMENTINSTANCEID,1,1,1,1,1,1,1,1,1) = 1
            ) 
            or 
            (
                (ID <> @ID or @SOURCEEVENTID is null)
                and COPYFROMEVENTID is not null
                and dbo.UFN_EVENT_CONFLICTSWITHMANAGEMENTLEVELINSTANCE(
                    ID,
                    @EVENTMANAGEMENTINSTANCEID,
                    COPYTASKS, 
                    case when COPYPRICES = 1 or COPYPREFERENCES = 1 then 1 end,
                    0,
                    COPYEXPENSES,
                    case when COPYINVITATIONS = 1 or COPYINVITEES = 1 then 1 end,
                    0,
                    0,
                    COPYJOBOCCURRENCES,
                    COPYLODGINGOPTIONS
                ) = 1
            );


        with EVENTMANAGEMENTOPTIONS_CTE as (
            select
                ID,
                LEVEL,
                HASREGISTRANTSANDOPTIONS,
                HASSPEAKERS,
                HASEXPENSES,
                HASINVITATIONS,
                HASTASKSANDCOORDINATORS,
                HASLODGINGOPTIONS,
                HASJOBOCCURRENCES,
                HASAPPEALS,
                HASDOCUMENTATION,
                HASCAMPAIGNS
            from
                dbo.EVENTMANAGEMENTLEVELINSTANCE
            where
                EVENTMANAGEMENTLEVELINSTANCE.EVENTMANAGEMENTINSTANCEID = @EVENTMANAGEMENTINSTANCEID
        )
        insert into dbo.EVENTMANAGEMENTOPTIONS
        (
            ID,
            EVENTMANAGEMENTLEVELINSTANCEID,
            EVENTID,
            HASREGISTRANTSANDOPTIONS,
            HASSPEAKERS,
            HASEXPENSES,
            HASINVITATIONS,
            HASTASKSANDCOORDINATORS,
            HASLODGINGOPTIONS,
            HASJOBOCCURRENCES,
            HASAPPEALS,
            HASDOCUMENTATION,
            HASCAMPAIGNS,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            EVENTMANAGEMENTOPTIONS_CTE.ID,
            EVENT.ID,
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASREGISTRANTSANDOPTIONS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASREGISTRANTSANDOPTIONS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASSPEAKERS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASSPEAKERS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASEXPENSES, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASEXPENSES end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASINVITATIONS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASINVITATIONS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASTASKSANDCOORDINATORS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASTASKSANDCOORDINATORS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASLODGINGOPTIONS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASLODGINGOPTIONS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASJOBOCCURRENCES, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASJOBOCCURRENCES end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASAPPEALS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASAPPEALS end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASDOCUMENTATION, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASDOCUMENTATION end,
                1),
            coalesce(
                EVENTMANAGEMENTOPTIONS.HASCAMPAIGNS, 
                case when HIERARCHYTABLE.EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTOPTIONS_CTE.HASCAMPAIGNS end,
                1),                
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            dbo.EVENT
        inner join
            dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
        inner join 
            @HierarchyTable HIERARCHYTABLE on HIERARCHYTABLE.ID = EVENT.ID
        left outer join 
            EVENTMANAGEMENTOPTIONS_CTE on EVENTMANAGEMENTOPTIONS_CTE.LEVEL = EVENTHIERARCHY.HIERARCHYPATH.GetLevel()
        left outer join
            dbo.EVENTMANAGEMENTOPTIONS on EVENTMANAGEMENTOPTIONS.EVENTID = HIERARCHYTABLE.COPYFROMEVENTID
        where
            ((EVENT.MAINEVENTID = @ID) or (EVENT.ID = @ID));

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch


    return 0;
end