USP_DATAFORMTEMPLATE_EDITSAVE_R68ONLINESETTINGS

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_R68ONLINESETTINGS
                    (
                        @SETTINGSID 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()

                    if @CCRNID is null
                    begin
                        select @CCRNID = CHARITYCLAIMREFERENCENUMBER.ID
                        from dbo.CHARITYCLAIMREFERENCENUMBER
                        left join CHARITYCLAIMREFERENCENUMBERSITE on CHARITYCLAIMREFERENCENUMBERSITE.CHARITYCLAIMREFERENCENUMBERID = CHARITYCLAIMREFERENCENUMBER.ID
                        where CHARITYCLAIMREFERENCENUMBERSITE.ID is null

                        --If still null, then CCRN does not exist and we must error out
                        if @CCRNID is null
                            raiserror('BBERR_CCRNNOTSET', 13, 1)
                    end

                    if @SETTINGSID is null
                    begin
                        set @SETTINGSID = newid()

                        insert into dbo.R68ONLINESUBMISSIONSETTINGS
                            (ID, ORGANISATION, CCRNID, REGULATORCODE, OTHERREGULATORNAME, REGULATORNUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values
                            (@SETTINGSID, @ORGANISATION, @CCRNID, @REGULATORCODE, @OTHERREGULATORNAME, @REGULATORNUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                    end
                    else
                        update dbo.R68ONLINESUBMISSIONSETTINGS
                        set ORGANISATION = @ORGANISATION,
                            CCRNID = @CCRNID,
                            REGULATORCODE = @REGULATORCODE,
                            OTHERREGULATORNAME = @OTHERREGULATORNAME,
                            REGULATORNUMBER = @REGULATORNUMBER,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @SETTINGSID

                    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;

                    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

                            if @RECORDEXISTS = 1 and @PASSWORD = N'Qha8$2Jgs!'
                            begin
                                set @PASSWORDENCRYPTED = @EXISTINGPASSWORDENCRYPTED;
                                set @HASH = @EXISTINGHASH
                            end
                            else
                            begin
                                set @PASSWORDENCRYPTED = EncryptByKey(Key_GUID('sym_BBInfinity'), @PASSWORD);
                                set @HASH = @PASSWORDHASH
                            end

                            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, @SETTINGSID, @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