USP_SPONSORSHIPOPPORTUNITY_ADD_PROJECT

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN	Project group
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@PROJECTNAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@GOAL	money	IN	Amount
@SPONSORGOAL	int	IN	Sponsors
@DESIGNATIONID	uniqueidentifier	IN	Designation
@SPROPPPROJECTCATEGORYCODE	uniqueidentifier	IN	Category
@BASECURRENCYID	uniqueidentifier	IN	Base currency ID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_ADD_PROJECT
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier =null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @PROJECTNAME nvarchar(100),
    @DESCRIPTION nvarchar(255) = '',
    @GOAL money=0,
    @SPONSORGOAL int=0,
  @DESIGNATIONID uniqueidentifier=null,
  @SPROPPPROJECTCATEGORYCODE uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as

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

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
   declare @STATUS int = 1;
     --Get defaults

     declare @STATUSCODE bit = null;
     select @STATUSCODE = DEFAULTPROJECTSTATUSCODE from dbo.SPONSORSHIPINFO order by DATEADDED;


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

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

  declare @ORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;  
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  if @BASECURRENCYID is null
    set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

    if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
    begin 
        set @ORGANIZATIONAMOUNT = @GOAL;
    end
    else
    begin
        set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@GOAL, @ORGANIZATIONEXCHANGERATEID);
    end

    insert into dbo.SPONSORSHIPOPPORTUNITYPROJECT
        (ID, NAME, DESCRIPTION, STARTDATE, GOAL, SPONSORGOAL, DESIGNATIONID, SPROPPPROJECTCATEGORYCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID)
    values
        (@ID, @PROJECTNAME, @DESCRIPTION, case @STATUSCODE when 0 then @CURRENTDATE end, @GOAL, @SPONSORGOAL, @DESIGNATIONID, @SPROPPPROJECTCATEGORYCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID)

        --If default status is pending add reasons.

        if @STATUSCODE = 1
          exec dbo.USP_SPONSORSHIPSETTINGS_GETNEWDEFAULTS 'PROJECT',@ID;    
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0