USP_SPONSORSHIPOPPORTUNITY_VIEW

The load procedure used by the view dataform template "Sponsorship Opportunity Detail View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SPONSORSHIPOPPORTUNITYTYPECODE	int	INOUT	Sponsorship opportunity type
@FIRSTNAME	nvarchar(100)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@LASTNAME	nvarchar(100)	INOUT	Last name
@CHILDNAME	nvarchar(700)	INOUT	Name
@ISHIVPOSITIVE	bit	INOUT	HIV positive
@ISORPHANED	bit	INOUT	Orphaned
@CONDITION	nvarchar(100)	INOUT	Disability/Illness
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@GENDERCODE	int	INOUT	Gender
@PICTURE	varbinary	INOUT	Image
@PICTURETHUMBNAIL	varbinary	INOUT	Image thumbnail
@AGE	int	INOUT	Age
@NAME	nvarchar(700)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@GOAL	money	INOUT	Amount goal
@SPONSORGOAL	int	INOUT	Sponsors goal
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SPONSORSHIPOPPORTUNITYLOCATION	nvarchar(max)	INOUT	Location
@CHILDDATECHANGED	date	INOUT	Date changed
@CHILDCHANGEDBY	nvarchar(128)	INOUT	Changed by
@PROJECTDATECHANGED	date	INOUT	Date changed
@PROJECTCHANGEDBY	nvarchar(128)	INOUT	Changed by
@DESIGNATION	nvarchar(255)	INOUT	Designation
@SPROPPPROJECTCATEGORYCODE	nvarchar(255)	INOUT	Category
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@ELIGIBILITYCODE	tinyint	INOUT	Eligibility
@SHOWPROCESSPAGE	bit	INOUT	SHOWPROCESSPAGE
@SMARTFIELDDEFINED	bit	INOUT	SMARTFIELDDEFINED
@AMOUNT	money	INOUT	AMOUNT
@PROGRAMID	uniqueidentifier	INOUT	PROGRAMID
@GROUPID	uniqueidentifier	INOUT	GROUPID
@CONTACTEMAIL	nvarchar(100)	INOUT	Contact Email
@ISRESERVED	bit	INOUT	Is reserved
@OFFERSOLESPONSORSHIP	bit	INOUT	Offer sole sponsorship
@LOOKUPID	nvarchar(100)	INOUT	LOOKUPID
@SPONSORSPEROPPORTUNITY	int	INOUT	SPONSORSPEROPPORTUNITY
@SPONSORSHIPLOCATIONCOMMENT	nvarchar(255)	INOUT	SPONSORSHIPLOCATIONCOMMENT
@SPONSORSHIPLOCATIONTYPE	nvarchar(100)	INOUT	SPONSORSHIPLOCATIONTYPE
@PROGRAMNAME	nvarchar(100)	INOUT	PROGRAMNAME
@CURRENTMEMBERCOUNT	int	INOUT	CURRENTMEMBERCOUNT
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@HASRELATIONSHIPS	bit	INOUT	HASRELATIONSHIPS
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@CMSLOCATION	nvarchar(max)	INOUT	CMSLOCATION

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_VIEW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,

  @SPONSORSHIPOPPORTUNITYTYPECODE int = null output,

    @FIRSTNAME nvarchar(100)=null output,
    @MIDDLENAME nvarchar(50)=null output,
    @LASTNAME nvarchar(100) =null  output,
    @CHILDNAME nvarchar(700) = null output,
    @ISHIVPOSITIVE bit=null output,
    @ISORPHANED bit=null output,
    @CONDITION nvarchar(100)=null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @GENDERCODE int = null output,
    @PICTURE varbinary(max) = null output,
    @PICTURETHUMBNAIL varbinary(max) = null output,
    @AGE int = null output,
    @NAME nvarchar(700)=null output,
    @DESCRIPTION nvarchar(255)=null output,
    @STARTDATE datetime = null output,
    @ENDDATE datetime = null output,
    @GOAL money=null output,
    @SPONSORGOAL int=null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SPONSORSHIPOPPORTUNITYLOCATION nvarchar(max) = null output,
    @CHILDDATECHANGED date  = null output,
    @CHILDCHANGEDBY nvarchar(128) = null output,
    @PROJECTDATECHANGED date  = null output,
    @PROJECTCHANGEDBY nvarchar(128) = null output,
  @DESIGNATION nvarchar(255) = null output,
  @SPROPPPROJECTCATEGORYCODE nvarchar(255) = null output,
  @ATTRIBUTEDEFINED bit = null output,
  @ELIGIBILITYCODE tinyint=null output,
  @SHOWPROCESSPAGE bit = null output,
  @SMARTFIELDDEFINED bit = null output,
  @AMOUNT money = null output,
  @PROGRAMID uniqueidentifier = null output,
  @GROUPID uniqueidentifier = null output,
  @CONTACTEMAIL nvarchar(100) = null output,
  @ISRESERVED bit= null output,
  @OFFERSOLESPONSORSHIP bit = null output,
  @LOOKUPID nvarchar(100) = null output,
  @SPONSORSPEROPPORTUNITY int = null output,
  @SPONSORSHIPLOCATIONCOMMENT nvarchar(255) = null output,
  @SPONSORSHIPLOCATIONTYPE nvarchar(100) = null output,
  @PROGRAMNAME nvarchar(100) = null output,
  @CURRENTMEMBERCOUNT integer = null output,
  @HOUSEHOLDID uniqueidentifier = null output,
  @HASRELATIONSHIPS bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CMSLOCATION nvarchar(max) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.


    if exists(select ID from dbo.SPONSORSHIPOPPORTUNITYCHILD where ID=@ID) 
    begin
      set @SPONSORSHIPOPPORTUNITYTYPECODE =1
      set @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('Sponsorship Opportunity Child',@CURRENTAPPUSERID)
    end
    else
    begin
      set @SPONSORSHIPOPPORTUNITYTYPECODE =2
      set @ATTRIBUTEDEFINED = 0
    end

    set @SHOWPROCESSPAGE = 0

    select @SHOWPROCESSPAGE = 1
    from dbo.SPONSORSHIPOPPORTUNITYTRANSFERPROCESS
    where ID = @ID;

    select @DATALOADED = 1,
           @FIRSTNAME = CONSTITUENT.FIRSTNAME,
             @MIDDLENAME = CONSTITUENT.MIDDLENAME,
             @LASTNAME = CONSTITUENT.KEYNAME,
             @CHILDNAME = NF.NAME,
             @ISHIVPOSITIVE = SPONSORSHIPOPPORTUNITYCHILD.ISHIVPOSITIVE,
             @CONDITION = SPROPPCHILDCONDITIONCODE.DESCRIPTION,
             @ISORPHANED = SPONSORSHIPOPPORTUNITYCHILD.ISORPHANED,
             @GENDERCODE = CONSTITUENT.GENDERCODE,
             @AGE = CONSTITUENT.AGE,
             @BIRTHDATE = CONSTITUENT.BIRTHDATE,
             @PICTURE = CONSTITUENT.PICTURE,
             @PICTURETHUMBNAIL = CONSTITUENT.PICTURETHUMBNAIL,
             @NAME = NF.NAME,
         @CHILDDATECHANGED = SPONSORSHIPOPPORTUNITYCHILD.DATECHANGED,
             @CHILDCHANGEDBY = (select USERNAME from CHANGEAGENT where ID = SPONSORSHIPOPPORTUNITYCHILD.CHANGEDBYID),
       @SMARTFIELDDEFINED = dbo.UFN_SMARTFIELD_DEFINEDFORRECORDTYPE('Sponsorship Opportunity Child')
        from dbo.SPONSORSHIPOPPORTUNITYCHILD
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID
        left outer join dbo.SPROPPCHILDCONDITIONCODE on SPROPPCHILDCONDITIONCODE.ID = SPONSORSHIPOPPORTUNITYCHILD.SPROPPCHILDCONDITIONCODEID  
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
      where SPONSORSHIPOPPORTUNITYCHILD.ID = @ID

    select @DATALOADED = 1,
           @NAME = SPONSORSHIPOPPORTUNITYPROJECT.NAME,
           @DESCRIPTION = SPONSORSHIPOPPORTUNITYPROJECT.DESCRIPTION,
           @STARTDATE = SPONSORSHIPOPPORTUNITYPROJECT.STARTDATE,
           @ENDDATE = SPONSORSHIPOPPORTUNITYPROJECT.ENDDATE,
           @GOAL = SPONSORSHIPOPPORTUNITYPROJECT.GOAL,
             @SPONSORGOAL = SPONSORSHIPOPPORTUNITYPROJECT.SPONSORGOAL,
       @DESIGNATION = dbo.UFN_DESIGNATION_GETNAME(DESIGNATIONID),
       @SPROPPPROJECTCATEGORYCODE = SPROPPPROJECTCATEGORYCODE.DESCRIPTION,
             @PROJECTDATECHANGED = SPONSORSHIPOPPORTUNITYPROJECT.DATECHANGED,
             @PROJECTCHANGEDBY = (select USERNAME from CHANGEAGENT where ID = SPONSORSHIPOPPORTUNITYPROJECT.CHANGEDBYID),
       @SMARTFIELDDEFINED = dbo.UFN_SMARTFIELD_DEFINEDFORRECORDTYPE('Sponsorship Opportunity Project'),
       @BASECURRENCYID = BASECURRENCYID
      from dbo.SPONSORSHIPOPPORTUNITYPROJECT
        left join dbo.SPROPPPROJECTCATEGORYCODE on SPROPPPROJECTCATEGORYCODE.ID = SPONSORSHIPOPPORTUNITYPROJECT.SPROPPPROJECTCATEGORYCODEID
      where SPONSORSHIPOPPORTUNITYPROJECT.ID = @ID

    select
      @ELIGIBILITYCODE = SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE,
      @LOOKUPID = SPONSORSHIPOPPORTUNITY.LOOKUPID,
      @SPONSORSHIPOPPORTUNITYLOCATION = DBO.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID),
      @CMSLOCATION = DBO.UFN_SPONSORSHIPLOCATION_GETNAME(dbo.UFN_SPONSORSHIPOPPORTUNITY_GETLOCATIONFORCMS(SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID))
    from dbo.SPONSORSHIPOPPORTUNITY 
      where SPONSORSHIPOPPORTUNITY.ID = @ID

  select  @AMOUNT = SPONSORSHIPPROGRAM.AMOUNT,
            @PROGRAMNAME = SPONSORSHIPPROGRAM.NAME,
            @PROGRAMID = SPONSORSHIPPROGRAM.ID,
            @GROUPID = SPONSORSHIPOPPORTUNITYGROUP.ID,
            @OFFERSOLESPONSORSHIP = SPONSORSHIPOPPORTUNITYGROUP.OFFERSOLESPONSORSHIP,
      @SPONSORSPEROPPORTUNITY = SPONSORSPEROPPORTUNITY
    FROM     dbo.SPONSORSHIPOPPORTUNITY inner join
            dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITYGROUP.ID
            inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID
    WHERE SPONSORSHIPOPPORTUNITY.ID = @ID

 select @CONTACTEMAIL = EMAILADDRESS.EMAILADDRESS, 
        @SPONSORSHIPLOCATIONCOMMENT = SPONSORSHIPLOCATION.COMMENT,
        @SPONSORSHIPLOCATIONTYPE = SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION
    from dbo.SPONSORSHIPOPPORTUNITY
      inner join dbo.SPONSORSHIPLOCATION
      on SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID = SPONSORSHIPLOCATION.ID
      left join dbo.EMAILADDRESS on SPONSORSHIPLOCATION.FIELDOFFICEID = EMAILADDRESS.CONSTITUENTID
      left join dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID = SPONSORSHIPLOCATIONTYPECODE.ID
      where SPONSORSHIPOPPORTUNITY.ID = @ID

  if @SPONSORSHIPOPPORTUNITYTYPECODE=1
  begin
    declare @CURRENTDATE date;
      set @CURRENTDATE = getdate();

    select @HOUSEHOLDID = GM.GROUPID
      from dbo.GROUPMEMBER GM
    inner join dbo.CONSTITUENT C on C.ID = GM.MEMBERID
    inner join dbo.SPONSORSHIPOPPORTUNITYCHILD SC on SC.CONSTITUENTID = C.ID
    where SC.ID = @ID

    select @CURRENTMEMBERCOUNT = count(GM.ID) 
      from dbo.GROUPMEMBER as GM
      left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
      where ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
          or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
          or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
      and GM.GROUPID = @HOUSEHOLDID  

    if exists(select R.ID from dbo.RELATIONSHIP R inner join dbo.SPONSORSHIPOPPORTUNITYCHILD SC on SC.CONSTITUENTID = R.RELATIONSHIPCONSTITUENTID where SC.ID = @ID)
      set @HASRELATIONSHIPS=1
    else
      set @HASRELATIONSHIPS=0
  end
  else
  begin
    set @CURRENTMEMBERCOUNT=0
    set @HASRELATIONSHIPS= 0
  end


  select @ISRESERVED=case when RESERVATIONKEYID is null then 0 else 1 end from dbo.SPONSORSHIPOPPORTUNITY where ID=@ID

    return 0;