USP_DATAFORMTEMPLATE_ADD_R68ONLINESUBMISSIONSETTINGSBYREFERENCENUMBER

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

                    set nocount on;

                    if @ID is null
                        set @ID = newid()

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

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    insert into dbo.R68ONLINESUBMISSIONSETTINGS
                        (ID, ORGANISATION, CCRNID, REGULATORCODE, OTHERREGULATORNAME, REGULATORNUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                        (@ID, @ORGANISATION, @CCRNID, @REGULATORCODE, @OTHERREGULATORNAME, @REGULATORNUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                    --open the symmetric key for encryption
                    exec dbo.USP_GET_KEY_ACCESS;

                    begin try
                        insert into dbo.R68ONLINESUBMISSIONSETTINGSSENDERINFO
                            (ID, R68ONLINESUBMISSIONSETTINGSID, USERID, PASSWORD, PASSWORDHASH, TYPECODE, ISTRUSTEE, TRUSTEENAME, OFFICIALTITLE, OFFICIALFORENAME, OFFICIALSURNAME, PHONENUMBER, ISOVERSEAS, POSTCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        select
                            ID,
                            @ID,
                            USERID,
                            EncryptByKey(Key_GUID('sym_BBInfinity'), PASSWORD),
                            PASSWORDHASH,
                            TYPECODE,
                            ISTRUSTEE,
                            TRUSTEENAME,
                            OFFICIALTITLE,
                            OFFICIALFORENAME,
                            OFFICIALSURNAME,
                            PHONENUMBER,
                            ISOVERSEAS,
                            POSTCODE,
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CURRENTDATE
                        from (
                            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)
                        ) as SENDERINFO

                        close symmetric key sym_BBInfinity;
                    end try

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

                    return 0