USP_INVITATION_COPY

            CREATE procedure dbo.USP_INVITATION_COPY
            (
                @SOURCEEVENTID uniqueidentifier,
                @DESTINATIONEVENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,                            
                @COPYINVITEES bit = 0                
            )
            with execute as caller
            as
                set nocount on;

                -- Cannot copy if the source invitation does not exist

                if not exists (select ID from dbo.EVENT where ID = @SOURCEEVENTID)
                    raiserror('The source event specified does not exist.',13,1);

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

                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                --cache out invitations with new IDs                    

                declare @INVITATIONSTEMPTABLE as Table(
                    ID uniqueidentifier,
                    NAME nvarchar(100), 
                    EVENTID uniqueidentifier, 
                    BUSINESSPROCESSVIEWID uniqueidentifier, 
                    DESCRIPTION nvarchar(255), 
                    MAILDATE datetime, 
                    NEWINVITATIONID uniqueidentifier,
                    CHANNELCODE tinyint,
                    CHANNELPREFERENCECODE tinyint,
                    MAILPACKAGEID uniqueidentifier,
                    EMAILPACKAGEID uniqueidentifier,
                    ADDRESSPROCESSINGOPTIONID uniqueidentifier,
                    NAMEFORMATPARAMETERID uniqueidentifier,
                    HOUSEHOLDINGTYPECODE tinyint,
                    HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit,
                    HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit,
                    HOUSEHOLDINGONERECORDPERHOUSEHOLD bit

                );

                insert into @INVITATIONSTEMPTABLE
                    select 
                        ID, 
                        NAME, 
                        EVENTID, 
                        BUSINESSPROCESSVIEWID, 
                        DESCRIPTION, 
                        MAILDATE, 
                        newID(),
                        CHANNELCODE,
                        CHANNELPREFERENCECODE,
                        MAILPACKAGEID,
                        EMAILPACKAGEID,
                        ADDRESSPROCESSINGOPTIONID,
                        NAMEFORMATPARAMETERID,
                        HOUSEHOLDINGTYPECODE,
                        HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
                        HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
                        HOUSEHOLDINGONERECORDPERHOUSEHOLD
                    from dbo.INVITATION
                    where INVITATION.EVENTID = @SOURCEEVENTID;

                if @COPYINVITEES = 1
                begin
                    --cache out invitees for this event 

                    declare @INVITEESTEMPTABLE as Table(ID uniqueidentifier, CONSTITUENTID uniqueidentifier, EVENTID uniqueidentifier, INVITATIONSENTON datetime, DECLINED bit, INVITATIONID uniqueidentifier);

                    insert into @INVITEESTEMPTABLE
                    select ID, CONSTITUENTID, EVENTID, INVITATIONSENTON, DECLINED, INVITATIONID
                    from dbo.INVITEE
                    where INVITEE.EVENTID = @SOURCEEVENTID;
                end                

                insert into dbo.INVITATION
                (
                    ID,
                    NAME,
                    EVENTID,
                    BUSINESSPROCESSVIEWID,
                    DESCRIPTION,
                    MAILDATE,
                    CHANNELCODE,
                    CHANNELPREFERENCECODE,
                    MAILPACKAGEID,
                    EMAILPACKAGEID,
                    ADDRESSPROCESSINGOPTIONID,
                    NAMEFORMATPARAMETERID,
                    HOUSEHOLDINGTYPECODE,
                    HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
                    HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
                    HOUSEHOLDINGONERECORDPERHOUSEHOLD,
                       ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    NEWINVITATIONID,
                    NAME,
                    @DESTINATIONEVENTID,
                    BUSINESSPROCESSVIEWID,
                    DESCRIPTION,
                    MAILDATE,
                    CHANNELCODE,
                    CHANNELPREFERENCECODE,
                    MAILPACKAGEID,
                    EMAILPACKAGEID,
                    ADDRESSPROCESSINGOPTIONID,
                    NAMEFORMATPARAMETERID,
                    HOUSEHOLDINGTYPECODE,
                    HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
                    HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
                    HOUSEHOLDINGONERECORDPERHOUSEHOLD,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from @INVITATIONSTEMPTABLE

                if @COPYINVITEES = 1
                begin
                    insert into dbo.INVITEE
                    (
                        ID,
                        CONSTITUENTID,
                        EVENTID,
                        INVITATIONID,                            
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    select
                        newID(),
                        invitees.CONSTITUENTID,
                        @DESTINATIONEVENTID,
                        invitations.NEWINVITATIONID,                                   
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from
                        @INVITEESTEMPTABLE invitees 
                        inner join @INVITATIONSTEMPTABLE invitations
                            on invitees.INVITATIONID = invitations.ID
                    where invitees.EVENTID = @SOURCEEVENTID;
                end 

                return 0;