USP_INVITATIONHISTORY_UPDATEORCREATE_2

Updates the invitation history record with the specified business process status identifier or creates one if no such record exists.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@BUSINESSPROCESSSTATUSID	uniqueidentifier	IN
@PARAMETERSETID	nvarchar(36)	IN
Definition

Copy


            CREATE procedure dbo.USP_INVITATIONHISTORY_UPDATEORCREATE_2
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @BUSINESSPROCESSSTATUSID uniqueidentifier = null,
                @PARAMETERSETID nvarchar(36) = null
            )
            as                    
                set nocount on;

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

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

                begin try
                    update dbo.INVITATIONHISTORY set
                        BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID,
                        PARAMETERSETID = @PARAMETERSETID,                                          
                        NAME = INVITATION.NAME,
                        EVENTID = INVITATION.EVENTID,
                        BUSINESSPROCESSVIEWID = INVITATION.BUSINESSPROCESSVIEWID,
                        CHANNELCODE = INVITATION.CHANNELCODE,
                        CHANNELPREFERENCECODE = INVITATION.CHANNELPREFERENCECODE,
                        MAILPACKAGEID = INVITATION.MAILPACKAGEID,
                        EMAILPACKAGEID = INVITATION.EMAILPACKAGEID,
                        ADDRESSPROCESSINGOPTIONID = MKTSEGMENTATION.ADDRESSPROCESSINGOPTIONID,
                        NAMEFORMATPARAMETERID = MKTSEGMENTATION.NAMEFORMATPARAMETERID,
                        HOUSEHOLDINGTYPECODE = MKTSEGMENTATION.HOUSEHOLDINGTYPECODE,
                        HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = MKTSEGMENTATION.HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
                        HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = MKTSEGMENTATION.HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
                        HOUSEHOLDINGONERECORDPERHOUSEHOLD = MKTSEGMENTATION.HOUSEHOLDINGONERECORDPERHOUSEHOLD,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
            MAILDATE = INVITATION.MAILDATE
                    from
                        dbo.INVITATIONHISTORY
                    inner join dbo.INVITATION
                        on INVITATIONHISTORY.PARAMETERSETID = INVITATION.ID
                    inner join dbo.MKTSEGMENTATION
                        on INVITATIONHISTORY.PARAMETERSETID = MKTSEGMENTATION.ID
                    where
                        BUSINESSPROCESSSTATUSGUID = @BUSINESSPROCESSSTATUSID;

                    if @@ROWCOUNT > 0
                        select
                            @ID = ID
                        from
                            dbo.INVITATIONHISTORY
                        where
                            BUSINESSPROCESSSTATUSGUID = @BUSINESSPROCESSSTATUSID;

                    else
                    begin
                        if @ID is null
                            set @ID = newid();

                        insert into dbo.INVITATIONHISTORY
                            (ID, BUSINESSPROCESSSTATUSGUID, BUSINESSPROCESSSTATUSID, PARAMETERSETID, NAME, EVENTID, BUSINESSPROCESSVIEWID, CHANNELCODE, CHANNELPREFERENCECODE, MAILPACKAGEID, EMAILPACKAGEID, ADDRESSPROCESSINGOPTIONID, NAMEFORMATPARAMETERID, HOUSEHOLDINGTYPECODE, HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD, HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS, HOUSEHOLDINGONERECORDPERHOUSEHOLD, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, MAILDATE)
                        select
                            @ID, 
                            @BUSINESSPROCESSSTATUSID, 
                            @BUSINESSPROCESSSTATUSID, 
                            @PARAMETERSETID, 
                            INVITATION.NAME, 
                            INVITATION.EVENTID, 
                            INVITATION.BUSINESSPROCESSVIEWID, 
                            INVITATION.CHANNELCODE, 
                            INVITATION.CHANNELPREFERENCECODE, 
                            INVITATION.MAILPACKAGEID, 
                            INVITATION.EMAILPACKAGEID, 
                            INVITATION.ADDRESSPROCESSINGOPTIONID, 
                            INVITATION.NAMEFORMATPARAMETERID, 
                            INVITATION.HOUSEHOLDINGTYPECODE, 
                            INVITATION.HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD, 
                            INVITATION.HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS, 
                            INVITATION.HOUSEHOLDINGONERECORDPERHOUSEHOLD, 
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CURRENTDATE,
              INVITATION.MAILDATE
                        from dbo.INVITATION
                        where INVITATION.ID = @PARAMETERSETID;

                    end
                end try

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

                return 0;