spBulkUpdate_EmailList_Subscription

                CREATE      PROCEDURE [dbo].[spBulkUpdate_EmailList_Subscription]

                      (

                            @ClientUsersID int,

                            @PeopleID int,

                            @XML ntext

                      )

                      AS

                DECLARE @idoc int

                      EXEC sp_xml_preparedocument @idoc OUTPUT, @XML



                      -- Whack existing based on UserID



                      IF (@ClientUsersID <= 0)

                            SET @ClientUsersID = NULL



                      IF (@ClientUsersID IS NULL) AND (@PeopleID > 0) 

                            SET @ClientUsersID = dbo.fnGetUserIDFromLinkedPeopleID(@PeopleID)



                      IF (@ClientUsersID IS NOT NULL) 

                            DELETE FROM [dbo].EmailList_Subscription

                            WHERE UserID = @ClientUsersID

                            AND EmailListID IN

                            ( SELECT [ID] 

                                  FROM OPENXML (@idoc, '/UserSubscriptions/EmailCatIds/Item', 1) 

                                  WITH(

                                        [ID] int, 

                                        [OnOff] bit

                                  )

                            )


                      -- Whack existing based on BackOfficePeopleID passed in

                      -- this will also whack the ones that are tied to known linked recs from other systems


                      if (@PeopleID > 0) 

                            Delete from [dbo].EmailList_Subscription

                            where BackOfficeSystemPeopleID = @PeopleID 

                --             (select BackOfficePeopleID from vwBackofficeLinkedUsers where userID=dbo.fnGetUserIDFromLinkedPeopleID(@PeopleID))

                            AND EmailListID IN

                            ( SELECT [ID] 

                                  FROM OPENXML (@idoc, '/UserSubscriptions/EmailCatIds/Item', 1) 

                                  WITH(

                                        [ID] int, 

                                        [OnOff] bit

                                  )

                            )


                      -- Finnaly the case where we have just a userid - and possibly have other backoffice people linked to 

                      -- it. Clean those out too.


                      IF (@ClientUsersID IS NOT NULL)

                            Delete from [dbo].EmailList_Subscription

                            where BackOfficeSystemPeopleID in 

                             (select BackOfficePeopleID from vwBackofficeLinkedUsers where userID=@ClientUsersID)

                            AND EmailListID IN

                            ( SELECT [ID] 

                                  FROM OPENXML (@idoc, '/UserSubscriptions/EmailCatIds/Item', 1) 

                                  WITH(

                                        [ID] int, 

                                        [OnOff] bit

                                  )

                            )


                      -- All possible realted subscriptions should be gone now. Add what we got passed in.

                      INSERT INTO [dbo].EmailList_Subscription

                      ([UserID],[BackOfficeSystemPeopleID],[EmailListID],[HasOpted])

                      SELECT @ClientUsersID, @PeopleID, [ID], [OnOff]

                            FROM OPENXML (@idoc, '/UserSubscriptions/EmailCatIds/Item', 1) 

                            WITH([ID] int, [OnOff] bit)

                      -- Update EmailJob_Recipient table GlobalOptedOut column
                      DECLARE @opted bit, @ejrid integer;      
                      SET @opted = 0;
                      SET @ejrid = 0;

                      SELECT @opted = [Opted], @ejrid = [EjrID] 

                        FROM OPENXML (@idoc, '/UserSubscriptions', 1)

             WITH([Opted] bit, [EjrID] int)

                      UPDATE dbo.EmailJob_Recipient set [GlobalOptedOut] = @opted where [ID] = @ejrid

                      EXEC sp_xml_removedocument @idoc