USP_DATAFORMTEMPLATE_ADD_GRANTAWARD_PRELOAD

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GRANTAWARD_PRELOAD
                    (
                        @FUNDINGREQUESTID uniqueidentifier,
                        @GRANTNAME nvarchar(150) = null output,
                        @GRANTORNAME nvarchar(154) = null output,
                        @SINGLEDESIGNATIONID uniqueidentifier = null output,
                        @SPLITS xml = null output,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                        @SHOWACCOUNTSYSTEM bit = null output,
                        @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                        @EXCHANGERATE decimal(20,8) = null output,
                        @BASEEXCHANGERATEID uniqueidentifier = null output,
                        @USERGRANTEDSPOTRATE bit = null output
                    )
                    as
                        set nocount on;

                        select
                            @GRANTNAME = GRANTS.TITLE,
                            @GRANTORNAME = dbo.UFN_CONSTITUENT_BUILDNAME(GRANTS.GRANTORID)
                        from
                            dbo.FUNDINGREQUEST
                            inner join dbo.GRANTS on FUNDINGREQUEST.GRANTSID = GRANTS.ID                            
                        where
                            FUNDINGREQUEST.ID = @FUNDINGREQUESTID;


                        if dbo.UFN_VALID_BASICGL_INSTALLED() != 0
                            begin
                                declare    @NUMBEROFACCOUNTSYSTEMSFORUSER smallint 

                                set @NUMBEROFACCOUNTSYSTEMSFORUSER = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID)
                                if @NUMBEROFACCOUNTSYSTEMSFORUSER = 1
                                    begin
                                        set @SHOWACCOUNTSYSTEM = 0
                                        select @PDACCOUNTSYSTEMID =  T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) as T1
                                    end 
                                else
                                    begin
                                        set @SHOWACCOUNTSYSTEM = 1        
                                        set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)
                                    end 
                            end
                        else
                            begin
                                set @SHOWACCOUNTSYSTEM = 0
                                set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
                            end

                        --Lookup the base currency of the account system

                        declare @BASECURRENCYID uniqueidentifier;
                        select 
                            @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                        from 
                            dbo.PDACCOUNTSYSTEM
                            left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        where
                            PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID;

                        --Set the transaction currency initially to the transaction currency of the funding request, only if this currency is a member of the account systems currency set.

                        select 
                            @TRANSACTIONCURRENCYID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID
                        from 
                            dbo.PDACCOUNTSYSTEM
                            left join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            left join dbo.CURRENCYSETTRANSACTIONCURRENCY on CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID = CURRENCYSET.ID
                        where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
                            and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = (select TRANSACTIONCURRENCYID from dbo.FUNDINGREQUEST where FUNDINGREQUEST.ID = @FUNDINGREQUESTID)

                        --If the transaction currency of the funding request is not a member of the account system

                        if @TRANSACTIONCURRENCYID is null
                        begin
                            --set the transaction currency to the base currency of the account system

                            set @TRANSACTIONCURRENCYID = @BASECURRENCYID;
                            set @EXCHANGERATE = 1;
                        end
                        else
                        begin
                            --lookup an exchange rate between the transaction currency and the account system's base currency

                            set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, dbo.UFN_DATE_GETEARLIESTTIME(getdate()), 1, null);
                            if @BASEEXCHANGERATEID is null
                                set @EXCHANGERATE = 0
                            else
                                select @EXCHANGERATE = RATE from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @BASEEXCHANGERATEID
                        end


                        set @SPLITS = 
                        (
                            select 
                                null [ID], 
                                DESIGNATIONID, 
                                0 [AMOUNT],
                                3 TYPECODE,
                                8 APPLICATIONCODE,
                                @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
                            from
                                dbo.FUNDINGREQUESTDESIGNATION
                            where 
                                FUNDINGREQUESTDESIGNATION.FUNDINGREQUESTID = @FUNDINGREQUESTID
                            group by DESIGNATIONID
                            for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
                        )

                        --replace with commented code for PBI 102747

                        set @USERGRANTEDSPOTRATE = 1;
                        /*set @USERGRANTEDSPOTRATE = case 
                            when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                                dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '911f104d-ba5f-4469-b0ae-184c879aea99') = 1 
                                    then 1
                                    else 0
                            end;*/

                        return 0;