USP_EDIT_SPONSORSHIPPROGRAM

CREATE procedure dbo.USP_EDIT_SPONSORSHIPPROGRAM (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @NAME nvarchar(100),
  @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
  @SPONSORSHIPGREATESTNEEDRULESETID uniqueidentifier,
  @AMOUNT money,
  @FILTERLOCATIONCODE tinyint = 0,
  @FILTERLOCATIONS xml = null,
  @ISAFFILIATEPROGRAM bit = 0,
  @AFFILIATEID uniqueidentifier = null,
  @CONSTITUENCYCODEID uniqueidentifier = null
)
as

  set nocount on;

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  begin try

    --Bug 88854 TonyKn - Check if locations still exist in DB before inserting the XML. 

    declare @TempTbl table (
    [SPONSORSHIPLOCATIONID] uniqueidentifier
    )

    insert into @TempTbl select 
    [SPONSORSHIPLOCATIONID] uniqueidentifier
    from dbo.UFN_SPONSORSHIPLOCATIONS_FROMITEMLISTXML(@FILTERLOCATIONS)

    declare @LOCATIONCOUNT integer = (select count(SPONSORSHIPLOCATIONID) from @TempTbl);
    if @LOCATIONCOUNT <> (select count(SPONSORSHIPLOCATIONID) from @TempTbl inner join dbo.SPONSORSHIPLOCATION on ID=SPONSORSHIPLOCATIONID)
      raiserror('BBERR_LOCATIONDELETED',13,1);

    if exists(select 'x' from @TempTbl group by SPONSORSHIPLOCATIONID having count(SPONSORSHIPLOCATIONID) > 1)
      raiserror('BBERR_DUPLICATELOCATION',13,1)

    update dbo.SPONSORSHIPPROGRAM set
      NAME = @NAME,
      SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID,
      SPONSORSHIPGREATESTNEEDRULESETID = @SPONSORSHIPGREATESTNEEDRULESETID,
      AMOUNT = @AMOUNT,
      FILTERLOCATIONCODE = @FILTERLOCATIONCODE,
      FILTERLOCATIONS = @FILTERLOCATIONS,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @ID;

    if @ISAFFILIATEPROGRAM = 1
    begin
        if exists(select 'x' from dbo.SPONSORSHIPAFFILIATEPROGRAM where ID = @ID)
            update dbo.SPONSORSHIPAFFILIATEPROGRAM
            set AFFILIATEID = @AFFILIATEID,
                CONSTITUENCYCODEID = @CONSTITUENCYCODEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;
        else
            insert into dbo.SPONSORSHIPAFFILIATEPROGRAM
              (ID,
               AFFILIATEID,
               CONSTITUENCYCODEID,
               ADDEDBYID,
               CHANGEDBYID,
               DATEADDED,
               DATECHANGED)
            values
              (@ID,
               @AFFILIATEID,
               @CONSTITUENCYCODEID,
               @CHANGEAGENTID,
               @CHANGEAGENTID,
               @CURRENTDATE,
               @CURRENTDATE)
    end
    else
        exec USP_SPONSORSHIPAFFILIATEPROGRAM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

return 0;