USP_DATAFORMTEMPLATE_ADD_FUNDINGREQUEST

The save procedure used by the add dataform template "Funding Request 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.
@FUNDINGPLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@GRANTID	uniqueidentifier	IN	Grant program
@FUNDINGREQUESTSTATUSCODEID	uniqueidentifier	IN	Status
@AMOUNTREQUESTED	money	IN	Amount requested
@DATESUBMITTED	datetime	IN	Date submitted
@COMMENT	nvarchar(500)	IN	Comment
@PRIMARYMANAGERID	uniqueidentifier	IN	Primary manager
@SECONDARYMANAGERID	uniqueidentifier	IN	Secondary manager
@SPONSORS	xml	IN	Secondary solicitors
@STEPS	xml	IN
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FUNDINGREQUEST
                        (
                        @ID uniqueidentifier output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @FUNDINGPLANID uniqueidentifier,
                        @GRANTID uniqueidentifier = null,
                        @FUNDINGREQUESTSTATUSCODEID uniqueidentifier = null,
                        @AMOUNTREQUESTED money = null,
                        @DATESUBMITTED datetime = null,
                        @COMMENT nvarchar(500) = null,
                        @PRIMARYMANAGERID uniqueidentifier = null,
                        @SECONDARYMANAGERID uniqueidentifier = null,
                        @SPONSORS xml = null,
                        @STEPS xml = null     
                    )
                    as
                        set nocount on;        
                        if @ID is null
                        set @ID = newID();

                        declare @BASECURRENCYID uniqueidentifier
                        select @BASECURRENCYID = FUNDINGPLAN.BASECURRENCYID
                            from dbo.FUNDINGPLAN
                        where
                            FUNDINGPLAN.ID = @FUNDINGPLANID

                        declare @TRANSACTIONCURRENCYID uniqueidentifier
                        select @TRANSACTIONCURRENCYID = GRANTS.TRANSACTIONCURRENCYID
                            from dbo.GRANTS
                        where
                            GRANTS.ID = @GRANTID
                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

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


                        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                        declare @BASEEXCHANGERATEID uniqueidentifier;
                        declare @BASEAMOUNTREQUESTED money;

                        declare @ORGANIZATIONAMOUNTREQUESTED money;

                        set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @CURRENTDATE, 1, null);                                                                    
                        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNTREQUESTED, @CURRENTDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNTREQUESTED output,null, @ORGANIZATIONAMOUNTREQUESTED output, @ORGANIZATIONEXCHANGERATEID output, 1;


                        begin try
                            insert into dbo.FUNDINGREQUEST
                            (
                                ID,
                                FUNDINGPLANID,
                                GRANTSID,
                                FUNDINGREQUESTSTATUSCODEID,
                                AMOUNTREQUESTED,
                                DATESUBMITTED,
                                PRIMARYMANAGERID,
                                SECONDARYMANAGERID,
                                COMMENT,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED,
                                TRANSACTIONCURRENCYID,
                                ORGANIZATIONAMOUNTREQUESTED,
                                BASEEXCHANGERATEID,
                                ORGANIZATIONEXCHANGERATEID,
                                BASECURRENCYID,
                                TRANSACTIONAMOUNTREQUESTED

                            )
                            values
                            (
                                @ID,
                                @FUNDINGPLANID,
                                @GRANTID,
                                @FUNDINGREQUESTSTATUSCODEID,
                                @BASEAMOUNTREQUESTED,
                                @DATESUBMITTED,
                                @PRIMARYMANAGERID,
                                @SECONDARYMANAGERID,
                                @COMMENT,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @TRANSACTIONCURRENCYID,
                                @ORGANIZATIONAMOUNTREQUESTED,
                                @BASEEXCHANGERATEID,
                                @ORGANIZATIONEXCHANGERATEID,
                                @BASECURRENCYID,
                                @AMOUNTREQUESTED
                            );

                            exec dbo.USP_FUNDINGREQUEST_GETSPONSORS_ADDFROMXML @ID, @SPONSORS, @CHANGEAGENTID;

                            exec dbo.USP_FUNDINGREQUEST_STEPSWITHCHILDREN_UPDATEFROMXML @ID, @STEPS, @CHANGEAGENTID;
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;