USP_PROGRAMEVENT_COPYPROGRAMEVENTPREFERENCES

            create procedure dbo.USP_PROGRAMEVENT_COPYPROGRAMEVENTPREFERENCES
            (
                @EVENTS xml = null,
                @PROGRAMID uniqueidentifier = null,
                @CONFLICTSEXIST bit = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as
            begin
                set nocount on;

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate()

                begin try

                    declare @PREFERENCESGROUPTABLE table (OLDID uniqueidentifier, NEWID uniqueidentifier, NAME nvarchar(100), EVENTID uniqueidentifier);

                    insert into @PREFERENCESGROUPTABLE
                    select PP.ID,
                        newid(),
                        PP.NAME,
                        T.events.value('(EVENTID)[1]', 'uniqueidentifier')
                    from dbo.PROGRAMPREFERENCEGROUP PP
                    inner join @EVENTS.nodes('/EVENTS/ITEM') T(events) on T.events.value('(PROGRAMID)[1]', 'uniqueidentifier') = PP.PROGRAMID
                    where PROGRAMID = @PROGRAMID
                        and T.events.value('(ISCONFLICT)[1]', 'bit') = 0

                    insert into dbo.EVENTPREFERENCEGROUP
                    (
                        ID, 
                        EVENTID, 
                        NAME, 
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        PP.NEWID,
                        EVENTID,
                        PP.NAME,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from @PREFERENCESGROUPTABLE PP

                    insert into dbo.EVENTPREFERENCE
                    (
                        ID, 
                        NAME, 
                        SEQUENCE,
                        EVENTPREFERENCEGROUPID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED                        
                    )
                    select newid(),
                        PP.NAME,
                        PP.SEQUENCE,                        
                        PGT.NEWID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                            
                    from dbo.PROGRAMPREFERENCE PP
                    inner join @PREFERENCESGROUPTABLE PGT
                        on PGT.OLDID = PP.PROGRAMPREFERENCEGROUPID

                    if @CONFLICTSEXIST = 1
                    begin
                            declare @PREFERENCESGROUPCONFLICTTABLE table (OLDID uniqueidentifier, NEWID uniqueidentifier, NAME nvarchar(100), CONFLICTID uniqueidentifier);

                            insert into @PREFERENCESGROUPCONFLICTTABLE
                            select ID,
                                newid(),
                                NAME,
                                T.events.value('(CONFLICTID)[1]', 'uniqueidentifier')
                            from dbo.PROGRAMPREFERENCEGROUP PP
                            inner join @EVENTS.nodes('/EVENTS/ITEM') T(events) on T.events.value('(PROGRAMID)[1]', 'uniqueidentifier') = PP.PROGRAMID
                            where PROGRAMID = @PROGRAMID
                                and T.events.value('(ISCONFLICT)[1]', 'bit') = 1

                            insert into dbo.PREFERENCEGROUPCONFLICT
                            (
                                ID, 
                                EVENTCONFLICTID, 
                                NAME, 
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                            )
                            select
                                NEWID,
                                CONFLICTID,
                                NAME,
                                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                            from @PREFERENCESGROUPCONFLICTTABLE

                            insert into dbo.PREFERENCECONFLICT
                            (
                                ID, 
                                NAME, 
                                SEQUENCE,
                                PREFERENCEGROUPCONFLICTID,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED                        
                            )
                            select newid(),
                                PP.NAME,
                                PP.SEQUENCE,                        
                                PGT.NEWID,
                                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                            
                            from dbo.PROGRAMPREFERENCE PP
                            inner join @PREFERENCESGROUPCONFLICTTABLE PGT
                                on PGT.OLDID = PP.PROGRAMPREFERENCEGROUPID                
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR
                    return 1
                end catch    
            end