USP_EVENT_DELETE

                    CREATE procedure dbo.USP_EVENT_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as
                        set nocount on;

                        declare @contextCache varbinary(128);

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

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

                        begin try
                            exec USP_EVENT_VALIDATEEVENTDELETE @ID

                            /* cache current context information */
                            set @contextCache = CONTEXT_INFO();

                            /* set CONTEXT_INFO to @CHANGEAGENTID */
                            set CONTEXT_INFO @CHANGEAGENTID;

                            -- Delete lodging room instances associated with this event

                            delete 
                            from 
                                EVENTLODGINGROOMINSTANCE
                            where
                                EVENTLODGINGID in (select ID from EVENTLODGING where EVENTID = @ID)

                            -- Delete lodging rooms associated with this event

                            delete 
                                from EVENTLODGINGROOM 
                                where EVENTLODGINGID in
                                    (select ID
                                        from EVENTLODGING
                                        where EVENTID = @ID)

                            -- Delete lodging options associated with this event

                            delete 
                                from dbo.EVENTLODGING 
                                where EVENTID = @ID

                            -- Delete reference to event in any prospect research requests

                            update dbo.PROSPECTRESEARCHREQUEST
                            set
                                EVENTID = null
                            where
                                EVENTID = @ID

                            -- Delete any registrant registration maps

                            -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

                            delete from
                                dbo.REGISTRANTREGISTRATIONMAP
                            from
                                dbo.REGISTRANTREGISTRATIONMAP
                                inner join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
                            where
                                REGISTRANT.EVENTID = @ID;

                            --Delete Invitees

                            delete INVITEE 
                            from dbo.INVITEE
                            inner join dbo.INVITATION on INVITEE.INVITATIONID = INVITATION.ID
                            where INVITATION.EVENTID = @ID;

                            delete from
                                dbo.INVITATION
                            where
                                EVENTID = @ID;

                            -- Clean up revenue batch event registrations

                            -- remove any revenue batch registrant guests of the this registrant

                            -- Delete any registrant registration maps for this registrant and their guests

                            -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

                            delete from
                                dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
                            from
                                dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
                            inner join
                                dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID
                            where
                                BATCHREVENUEREGISTRANT.EVENTID = @ID;

                            -- set application's batch registrant ID to null, mimic existing event registrations

                            update dbo.BATCHREVENUEAPPLICATION set
                                BATCHREVENUEREGISTRANTID = null,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID in (select ID from dbo.BATCHREVENUEREGISTRANT where EVENTID = @ID);

                            --Remove the registrant

                            delete from
                                dbo.BATCHREVENUEREGISTRANT
                            where
                                BATCHREVENUEREGISTRANT.EVENTID = @ID;


              --Remove meta data associated with Friends Asking Friends event

              if exists (select EVENT.ID from dbo.EVENT join dbo.EVENTEXTENSION on EVENT.ID= EVENTEXTENSION.EVENTID where EVENT.ID= @ID)
              begin
                exec dbo.USP_FAFEVENT_METADATACLEANUP @ID, @CHANGEAGENTID                
              END

                            -- If top of hierarchy, delete all related management options

                            if exists (select 1 from dbo.EVENT where ID = @ID and MAINEVENTID is null)
                            begin
                                delete from
                                    dbo.EVENTMANAGEMENTOPTIONS
                                where
                                    EVENTID = @ID

                                delete from
                                    dbo.EVENTMANAGEMENTINSTANCE
                                from
                                    dbo.EVENTMANAGEMENTINSTANCE
                                    inner join dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTLEVELINSTANCE.EVENTMANAGEMENTINSTANCEID = EVENTMANAGEMENTINSTANCE.ID
                                    inner join dbo.EVENTMANAGEMENTOPTIONS on EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID = EVENTMANAGEMENTLEVELINSTANCE.ID
                                where
                                    EVENTMANAGEMENTOPTIONS.EVENTID = @ID
                            end

                            /* reset CONTEXT_INFO to previous value */
                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            exec USP_EVENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

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