USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITY_CHILD

The save procedure used by the add dataform template "Sponsorship Opportunity Child Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN	Child group
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@FIRSTNAME	nvarchar(100)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@LASTNAME	nvarchar(100)	IN	Last name
@GENDERCODE	tinyint	IN	Gender
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@PICTURE	varbinary	IN	Image
@PICTURETHUMBNAIL	varbinary	IN	Image thumbnail
@PICTURECHANGED	bit	IN	Picture changed
@ISHIVPOSITIVE	bit	IN	HIV positive
@ISORPHANED	bit	IN	Orphaned
@SPROPPCHILDCONDITIONCODEID	uniqueidentifier	IN	Disability/Illness

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITY_CHILD
(
    @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @FIRSTNAME nvarchar(100) = '',
    @MIDDLENAME nvarchar(50) = '',
    @LASTNAME nvarchar(100)='',
    @GENDERCODE tinyint=0,
    @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
    @PICTURE varbinary(max)=null,
    @PICTURETHUMBNAIL varbinary(max)= null,
    @PICTURECHANGED bit=null,
    @ISHIVPOSITIVE bit=null,
    @ISORPHANED bit=null,
    @SPROPPCHILDCONDITIONCODEID uniqueidentifier=null
)
as

set nocount on;

if dbo.UFN_AGEFROMFUZZYDATE(@BIRTHDATE,getdate()) < 0
begin
    raiserror('BBERR_BIRTHDATEINFUTURE',13,1)
    return 1
end
if dbo.UFN_SPONSORSHIPOPPORTUNITY_AGEVALID(@SPONSORSHIPOPPORTUNITYGROUPID,@BIRTHDATE) = 2
begin
    raiserror('BBERR_CHILDOPPORTUNITYTOOOLD',13,1)
    return 1
end

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @AVAILABILITYCODE tinyint

select @AVAILABILITYCODE = case STATUSCODE when 0 then 0 else 2 end
from dbo.SPONSORSHIPLOCATION
where ID = @SPONSORSHIPLOCATIONID

if @@ROWCOUNT = 0
begin
    raiserror('BBERR_LOCATIONNOTFOUND',13,1)
    return 1
end

begin try

    --Get default eligibility

    declare @ELIGIBILITYCODE bit = null;
    select @ELIGIBILITYCODE = DEFAULTCHILDELIGIBILITYCODE from dbo.SPONSORSHIPINFO order by DATEADDED;

    insert into dbo.SPONSORSHIPOPPORTUNITY
    (ID, SPONSORSHIPOPPORTUNITYGROUPID,SPONSORSHIPLOCATIONID,ELIGIBILITYCODE,AVAILABILITYCODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
    values
    (@ID,@SPONSORSHIPOPPORTUNITYGROUPID,@SPONSORSHIPLOCATIONID,1 ,@AVAILABILITYCODE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

  insert into dbo.SPONSORSHIPOPPORTUNITYLOCK
    (ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
  values
    (@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

  insert into dbo.CONSTITUENT
    (ID,KEYNAME,FIRSTNAME,MIDDLENAME,GENDERCODE,BIRTHDATE,ISCONSTITUENT,PICTURE,PICTURETHUMBNAIL,ADDEDBYID,CHANGEDBYID)
  values 
    (@ID,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@GENDERCODE,@BIRTHDATE,0,@PICTURE,@PICTURETHUMBNAIL,@CHANGEAGENTID,@CHANGEAGENTID);

  insert into dbo.SPONSORSHIPOPPORTUNITYCHILD
        (ID, FIRSTNAME, MIDDLENAME, LASTNAME, GENDERCODE, BIRTHDATE, PICTURE, PICTURETHUMBNAIL, ISHIVPOSITIVE, ISORPHANED, SPROPPCHILDCONDITIONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,CONSTITUENTID)
    values
        (@ID, @FIRSTNAME, @MIDDLENAME, @LASTNAME, ISNULL(@GENDERCODE,0), @BIRTHDATE, @PICTURE, @PICTURETHUMBNAIL, @ISHIVPOSITIVE, @ISORPHANED, @SPROPPCHILDCONDITIONCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@ID)

        --If eligibility is pending add reasons.

        if @ELIGIBILITYCODE = 1
          exec dbo.USP_SPONSORSHIPSETTINGS_GETNEWDEFAULTS 'CHILD',@ID;

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0