USP_INVITATIONHISTORY_UPDATEORCREATE

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
@NAME	nvarchar(100)	IN
@EVENTID	uniqueidentifier	IN
@BUSINESSPROCESSVIEWID	uniqueidentifier	IN
@CHANNELCODE	tinyint	IN
@CHANNELPREFERENCECODE	tinyint	IN
@MAILPACKAGEID	uniqueidentifier	IN
@EMAILPACKAGEID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@HOUSEHOLDINGTYPECODE	tinyint	IN
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	IN
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	IN
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	IN

Definition

Copy



            CREATE procedure dbo.USP_INVITATIONHISTORY_UPDATEORCREATE
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @BUSINESSPROCESSSTATUSID uniqueidentifier = null,
                @PARAMETERSETID nvarchar(36) = null,                            
                @NAME nvarchar(100) = null,
                @EVENTID uniqueidentifier = null,
                @BUSINESSPROCESSVIEWID uniqueidentifier = null,
                @CHANNELCODE tinyint = 0,
                @CHANNELPREFERENCECODE tinyint = 0,
                @MAILPACKAGEID uniqueidentifier = null,
                @EMAILPACKAGEID uniqueidentifier = null,
                @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
                @NAMEFORMATPARAMETERID uniqueidentifier = null,
                @HOUSEHOLDINGTYPECODE tinyint = 1,
                @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
                @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
                @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0
            )
            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 = @NAME,
                        EVENTID = @EVENTID,
                        BUSINESSPROCESSVIEWID = @BUSINESSPROCESSVIEWID,
                        CHANNELCODE = @CHANNELCODE,
                        CHANNELPREFERENCECODE = @CHANNELPREFERENCECODE,
                        MAILPACKAGEID = @MAILPACKAGEID,
                        EMAILPACKAGEID = @EMAILPACKAGEID,
                        ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
                        NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
                        HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
                        HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
                        HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
                        HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from
                        dbo.INVITATIONHISTORY
                    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)
                        values
                            (@ID, @BUSINESSPROCESSSTATUSID, @BUSINESSPROCESSSTATUSID, @PARAMETERSETID, @NAME, @EVENTID, @BUSINESSPROCESSVIEWID, @CHANNELCODE, @CHANNELPREFERENCECODE, @MAILPACKAGEID, @EMAILPACKAGEID, @ADDRESSPROCESSINGOPTIONID, @NAMEFORMATPARAMETERID, @HOUSEHOLDINGTYPECODE, @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD, @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS, @HOUSEHOLDINGONERECORDPERHOUSEHOLD, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
                    end
                end try

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

                return 0;