USP_DATAFORMTEMPLATE_EDIT_R68ONLINESUBMISSIONSETTINGSBYREFERENCENUMBER

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_R68ONLINESUBMISSIONSETTINGSBYREFERENCENUMBER 
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @ORGANISATION nvarchar(160),
                        @CCRNID uniqueidentifier,
                        @REGULATORCODE tinyint,
                        @OTHERREGULATORNAME nvarchar(160),
                        @REGULATORNUMBER nvarchar(20),
                        @SENDERINFO xml
                    )
                    as

                        set nocount on;

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

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        update dbo.R68ONLINESUBMISSIONSETTINGS
                        set ORGANISATION = @ORGANISATION,
                            CCRNID = @CCRNID,
                            REGULATORCODE = @REGULATORCODE,
                            OTHERREGULATORNAME = @OTHERREGULATORNAME,
                            REGULATORNUMBER = @REGULATORNUMBER,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID

                        declare @PASSWORDENCRYPTED nvarchar(max);
                        declare @HASH nvarchar(200);
                        declare @EXISTINGPASSWORDENCRYPTED nvarchar(max);
                        declare @EXISTINGHASH nvarchar(200);

                        declare @SENDERINFOID uniqueidentifier;
                        declare @USERID nvarchar(100);
                        declare @PASSWORD nvarchar(200);
                        declare @PASSWORDHASH nvarchar(200);
                        declare @TYPECODE tinyint;
                        declare @ISTRUSTEE bit;
                        declare @TRUSTEENAME nvarchar(160);
                        declare @OFFICIALTITLE nvarchar(4);
                        declare @OFFICIALFORENAME nvarchar(35);
                        declare @OFFICIALSURNAME nvarchar(35);
                        declare @PHONENUMBER nvarchar(35);
                        declare @ISOVERSEAS bit;
                        declare @POSTCODE nvarchar(8);
                        declare @RECORDEXISTS bit;

                        delete from dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO
                        where R68ONLINESUBMISSIONSETTINGSID = @ID and
                        ID not in (
                            select T.c.value('(ID)[1]','uniqueidentifier') as ID
                            from @SENDERINFO.nodes('SENDERINFO/ITEM') T(c)
                        )

                        declare SENDERINFO_CURSOR cursor local fast_forward for
                            select T.c.value('(ID)[1]','uniqueidentifier') as ID,
                                T.c.value('(USERID)[1]','nvarchar(100)') as USERID,
                                T.c.value('(PASSWORD)[1]','nvarchar(200)') as PASSWORD,
                                T.c.value('(PASSWORDHASH)[1]','nvarchar(200)') as PASSWORDHASH,
                                T.c.value('(TYPE)[1]','tinyint') as TYPECODE,
                                T.c.value('(ISTRUSTEE)[1]','bit') as ISTRUSTEE,
                                T.c.value('(TRUSTEENAME)[1]','nvarchar(160)') as TRUSTEENAME,
                                T.c.value('(OFFICIALTITLE)[1]','nvarchar(4)') as OFFICIALTITLE,
                                T.c.value('(OFFICIALFORENAME)[1]','nvarchar(35)') as OFFICIALFORENAME,
                                T.c.value('(OFFICIALSURNAME)[1]','nvarchar(35)') as OFFICIALSURNAME,
                                T.c.value('(PHONENUMBER)[1]','nvarchar(35)') as PHONENUMBER,
                                T.c.value('(ISOVERSEAS)[1]','bit') as ISOVERSEAS,
                                T.c.value('(POSTCODE)[1]','nvarchar(8)') as POSTCODE
                            from @SENDERINFO.nodes('SENDERINFO/ITEM') T(c)

                        open SENDERINFO_CURSOR;

                        --open the symmetric key for encryption

                        exec dbo.USP_GET_KEY_ACCESS;

                        begin try
                            fetch next from SENDERINFO_CURSOR into @SENDERINFOID, @USERID, @PASSWORD, @PASSWORDHASH, @TYPECODE, @ISTRUSTEE, @TRUSTEENAME, @OFFICIALTITLE, @OFFICIALFORENAME, @OFFICIALSURNAME, @PHONENUMBER, @ISOVERSEAS, @POSTCODE;

                            while @@FETCH_STATUS = 0
                            begin
                                set @RECORDEXISTS = 0
                                set @EXISTINGPASSWORDENCRYPTED = ''
                                set @PASSWORDENCRYPTED = ''
                                set @HASH = ''
                                set @EXISTINGHASH = ''

                                if exists(select 1 from dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO where ID = @SENDERINFOID)
                                begin
                                    set @RECORDEXISTS = 1;
                                    select @EXISTINGPASSWORDENCRYPTED = PASSWORD,
                                            @EXISTINGHASH = PASSWORDHASH
                                    from dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO 
                                    where ID = @SENDERINFOID
                                end

                                    set @PASSWORDENCRYPTED = EncryptByKey(Key_GUID('sym_BBInfinity'), @PASSWORD);
                                    set @HASH = @PASSWORDHASH

                                if @RECORDEXISTS = 1
                                begin
                                    update dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO
                                    set USERID = @USERID, 
                                        PASSWORD = @PASSWORDENCRYPTED, 
                                        PASSWORDHASH = @HASH, 
                                        TYPECODE = @TYPECODE, 
                                        ISTRUSTEE = @ISTRUSTEE, 
                                        TRUSTEENAME = @TRUSTEENAME, 
                                        OFFICIALTITLE = @OFFICIALTITLE, 
                                        OFFICIALFORENAME = @OFFICIALFORENAME, 
                                        OFFICIALSURNAME = @OFFICIALSURNAME, 
                                        PHONENUMBER = @PHONENUMBER, 
                                        ISOVERSEAS = @ISOVERSEAS, 
                                        POSTCODE = @POSTCODE, 
                                        CHANGEDBYID = @CHANGEAGENTID, 
                                        DATECHANGED = @CURRENTDATE
                                    where ID = @SENDERINFOID
                                end
                                else
                                begin
                                    insert into dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO
                                        (ID, R68ONLINESUBMISSIONSETTINGSID, USERID, [PASSWORD], PASSWORDHASH, TYPECODE, ISTRUSTEE, TRUSTEENAME, OFFICIALTITLE, OFFICIALFORENAME, OFFICIALSURNAME, PHONENUMBER, ISOVERSEAS, POSTCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    values
                                        (@SENDERINFOID, @ID, @USERID, @PASSWORDENCRYPTED, @HASH, @TYPECODE, @ISTRUSTEE, @TRUSTEENAME, @OFFICIALTITLE, @OFFICIALFORENAME, @OFFICIALSURNAME, @PHONENUMBER, @ISOVERSEAS, @POSTCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                                end

                                fetch next from SENDERINFO_CURSOR into @SENDERINFOID, @USERID, @PASSWORD, @PASSWORDHASH, @TYPECODE, @ISTRUSTEE, @TRUSTEENAME, @OFFICIALTITLE, @OFFICIALFORENAME, @OFFICIALSURNAME, @PHONENUMBER, @ISOVERSEAS, @POSTCODE;
                            end

                            close symmetric key sym_BBInfinity;
                            close SENDERINFO_CURSOR;
                            deallocate SENDERINFO_CURSOR;
                        end try

                        begin catch
                            close symmetric key sym_BBInfinity;
                            close SENDERINFO_CURSOR;
                            deallocate SENDERINFO_CURSOR;
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                    return 0;