USP_DATALIST_SPONSORSHIPOPPORTUNITYMATCH

Datalist which reserves and returns a single matching opportunity for a sponsorship.

Parameters

Parameter	Parameter Type	Mode
@CHANGEAGENTID	uniqueidentifier	IN
@SPONSORSHIPPROGRAMID	uniqueidentifier	IN
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN
@GENDERCODE	tinyint	IN
@SPROPPAGERANGEID	uniqueidentifier	IN
@ISHIVPOSITIVECODE	tinyint	IN
@HASCONDITIONCODE	tinyint	IN
@ISORPHANEDCODE	tinyint	IN
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	IN
@SPONSORID	uniqueidentifier	IN
@ISSOLESPONSORSHIP	bit	IN
@REVENUECONSTITUENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_SPONSORSHIPOPPORTUNITYMATCH(
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPPROGRAMID uniqueidentifier,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE tinyint = 0,
    @SPROPPAGERANGEID uniqueidentifier = null,
    @ISHIVPOSITIVECODE tinyint = 0,
    @HASCONDITIONCODE tinyint = 0,
    @ISORPHANEDCODE tinyint = 0,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
    @SPONSORID uniqueidentifier = null,
    @ISSOLESPONSORSHIP bit = 0,
    @REVENUECONSTITUENTID uniqueidentifier = null
)
as
begin
    set nocount on;

    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
  declare @LOCKTYPE tinyint;

    if @GENDERCODE is null
        set @GENDERCODE = 0
    if @ISHIVPOSITIVECODE is null
        set @ISHIVPOSITIVECODE = 0
    if @HASCONDITIONCODE is null
        set @HASCONDITIONCODE = 0
    if @ISORPHANEDCODE is null
        set @ISORPHANEDCODE = 0

  if @ISSOLESPONSORSHIP = 1
    set @LOCKTYPE = 1
  else
    set @LOCKTYPE = 2

  begin try
    exec dbo.USP_SPONSORSHIP_ACQUIREOPPORTUNITY
          @SPONSORSHIPOPPORTUNITYID output,
      @CHANGEAGENTID,
      0,
      @SPONSORID,
      @SPONSORSHIPPROGRAMID,
      @SPONSORSHIPLOCATIONID,
          @GENDERCODE,
          @SPROPPAGERANGEID,
          @ISHIVPOSITIVECODE,
          @HASCONDITIONCODE,
          @ISORPHANEDCODE,
          @SPROPPPROJECTCATEGORYCODEID,
      @ISSOLESPONSORSHIP,
      null,
      null,
      @REVENUECONSTITUENTID,
      @LOCKTYPE

            select SO.ID SPONSORSHIPOPPORTUNITYID,
             dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SO.ID) NAME,
             dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(SO.SPONSORSHIPOPPORTUNITYGROUPID,SO.SPONSORSHIPLOCATIONID) OFFERSOLESPONSORSHIP,
             C.GENDER,C.BIRTHDATE,SO.LOOKUPID,
             dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SO.SPONSORSHIPLOCATIONID) LOCATION,
             (select DESCRIPTION from dbo.SPROPPPROJECTCATEGORYCODE SPROC where SP.SPROPPPROJECTCATEGORYCODEID= SPROC.ID) CATEGORY
            from dbo.SPONSORSHIPOPPORTUNITY SO
            left outer join dbo.SPONSORSHIPOPPORTUNITYCHILD SC on SC.ID = SO.ID
      left outer join dbo.CONSTITUENT C on C.ID = SC.CONSTITUENTID
            left outer join dbo.SPONSORSHIPOPPORTUNITYPROJECT SP on SP.ID = SO.ID
            where SO.ID = @SPONSORSHIPOPPORTUNITYID;
    end try
    begin catch
    if error_message() = 'BBERR_NOMATCHINGOPPORTUNITY'
      -- No opportunity available to match.

          select null SPONSORSHIPOPPORTUNITYID,
             '<No matching opportunity found>' NAME,
             null OFFERSOLESPONSORSHIP,
             null GENDER,
             null BIRTHDATE,
             null LOOKUPID,
             null LOCATION,
             null CATEGORY
    else
    begin
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end
  end catch

  return 0;
end