USP_ADD_SPONSORSHIPPROGRAM

Adds a new sponsorship program.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN
@AMOUNT	money	IN
@SPONSORSHIPGREATESTNEEDRULESETID	uniqueidentifier	IN
@FILTERLOCATIONCODE	tinyint	IN
@FILTERLOCATIONS	xml	IN
@MATCHFUNCTIONID	uniqueidentifier	IN
@SEQUENCE	int	IN
@ISAFFILIATEPROGRAM	bit	IN
@AFFILIATEID	uniqueidentifier	IN
@CONSTITUENCYCODEID	uniqueidentifier	IN
@QUERYVIEWID	uniqueidentifier	IN
@BASECURRENCYID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_ADD_SPONSORSHIPPROGRAM
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100) = null,
  @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null,
  @AMOUNT money = null,
  @SPONSORSHIPGREATESTNEEDRULESETID uniqueidentifier = null,
  @FILTERLOCATIONCODE tinyint = 0,
  @FILTERLOCATIONS xml = null,
  @MATCHFUNCTIONID uniqueidentifier = null,
  @SEQUENCE int = 1,
  @ISAFFILIATEPROGRAM bit = 0,
  @AFFILIATEID uniqueidentifier = null,
  @CONSTITUENCYCODEID uniqueidentifier = null,
  @QUERYVIEWID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null
)
as
begin

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()

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)

  insert into dbo.SPONSORSHIPPROGRAM
    (ID,
     NAME,
     SPONSORSHIPOPPORTUNITYGROUPID,
     AMOUNT,
     SPONSORSHIPGREATESTNEEDRULESETID,
     FILTERLOCATIONCODE,
     FILTERLOCATIONS,
     MATCHFUNCTIONID,
     QUERYVIEWID,
     SEQUENCE,
     BASECURRENCYID,
     ADDEDBYID,
     CHANGEDBYID,
     DATEADDED,
     DATECHANGED)
    values
    (@ID,
     @NAME,
     @SPONSORSHIPOPPORTUNITYGROUPID,
     @AMOUNT,
     @SPONSORSHIPGREATESTNEEDRULESETID,
     @FILTERLOCATIONCODE,
     @FILTERLOCATIONS,
     @MATCHFUNCTIONID,
     @QUERYVIEWID,
     @SEQUENCE,
     @BASECURRENCYID,
     @CHANGEAGENTID,
     @CHANGEAGENTID,
     @CURRENTDATE,
     @CURRENTDATE)

  if @ISAFFILIATEPROGRAM = 1
    insert into dbo.SPONSORSHIPAFFILIATEPROGRAM
      (ID,
       AFFILIATEID,
       CONSTITUENCYCODEID,
       ADDEDBYID,
       CHANGEDBYID,
       DATEADDED,
       DATECHANGED)
    values
      (@ID,
       @AFFILIATEID,
       @CONSTITUENCYCODEID,
       @CHANGEAGENTID,
       @CHANGEAGENTID,
       @CURRENTDATE,
       @CURRENTDATE)

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0        
end