USP_DATAFORMTEMPLATE_ADD_GRANTAWARDFROMOPPORTUNITY
The save procedure used by the add dataform template "Grant Award From Opportunity 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. |
| @OPPORTUNITYID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @DATE | datetime | IN | Date |
| @AMOUNT | money | IN | Amount |
| @POSTDATE | datetime | IN | Post date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @FREQUENCYCODE | tinyint | IN | Frequency |
| @NUMBEROFINSTALLMENTS | int | IN | No. installments |
| @STARTDATE | datetime | IN | Starting on |
| @SPLITS | xml | IN | Designations |
| @INSTALLMENTS | xml | IN | |
| @DONOTACKNOWLEDGE | bit | IN | Do not acknowledge |
| @CATEGORYCODEID | uniqueidentifier | IN | Revenue category |
| @ISREIMBURSABLE | bit | IN | Grant award is reimbursable |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | Account system |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Transaction currency |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GRANTAWARDFROMOPPORTUNITY
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@OPPORTUNITYID uniqueidentifier,
@DATE datetime,
@AMOUNT money,
@POSTDATE datetime = null,
@POSTSTATUSCODE tinyint = 1,
@FREQUENCYCODE tinyint = 5,
@NUMBEROFINSTALLMENTS int = 1,
@STARTDATE datetime = null,
@SPLITS xml,
@INSTALLMENTS xml = null,
@DONOTACKNOWLEDGE bit = 0,
@CATEGORYCODEID uniqueidentifier = null,
@ISREIMBURSABLE bit = 0,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@TRANSACTIONCURRENCYID uniqueidentifier = null,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = 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();
if @PDACCOUNTSYSTEMID is null
set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
--Multicurrency - Retrieve base currency from the account system's currency set.
declare @CURRENCYSETID uniqueidentifier
select
@CURRENCYSETID = CURRENCYSETID
from
dbo.PDACCOUNTSYSTEM
where
ID = @PDACCOUNTSYSTEMID
declare @BASECURRENCYID uniqueidentifier;
select
@BASECURRENCYID = BASECURRENCYID
from
dbo.CURRENCYSET
where
ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET())
--Multicurrency - Add a spot exchange rate if an existing rate hasn't
--been selected, the base and transaction currencies are different, and the rate
--entered isn't zero (which indicates that the user wants to enter the record without a rate).
if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @BASEEXCHANGERATEID = newid()
insert into dbo.CURRENCYEXCHANGERATE(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@EXCHANGERATE,
@DATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
begin try
-- Check GL business rule for this account system and set to 'Do not post' if needed.
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @POSTSTATUSCODE = 2 -- Do not post
set @POSTDATE = null
end
--Validate splits
exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 6, default, @TRANSACTIONCURRENCYID;
-- Multicurrency - Ensure that the installment's transaction currency is the same as its revenue.
if exists(
select 1
from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS)
where TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
)
begin
raiserror('An installment''s transaction currency must match that of its revenue.',13,1);
end
--Validate installments
declare @SUM money;
declare @COUNT int;
select
@SUM = sum(AMOUNT),
@COUNT = count(AMOUNT)
from
dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS);
if @COUNT = 0
raiserror('ATLEASTONEINSTALLMENT',13,1);
if @SUM <> @AMOUNT
raiserror('INSTALLMENTTOTALAMOUNTVALID',13,1);
if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
raiserror('INSTALLMENTDATESOVERLAP',13,1);
declare @DONOTPOST bit;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @CONSTITUENTID uniqueidentifier;
select
@CONSTITUENTID = PROSPECTPLAN.PROSPECTID
from
dbo.OPPORTUNITY
inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
where OPPORTUNITY.ID = @OPPORTUNITYID;
--Multicurrency - Retrieve and calculate the necessary multicurrency values.
declare @BASEAMOUNT money;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONAMOUNT money;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@AMOUNT,
@DATE,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID output,
@BASEAMOUNT output,
@ORGANIZATIONCURRENCYID output,
@ORGANIZATIONAMOUNT output,
@ORGANIZATIONEXCHANGERATEID output,
1;
--Add the grant award record
insert into dbo.REVENUE (ID,CONSTITUENTID,DATE,DONOTPOST,POSTDATE,DONOTRECEIPT,AMOUNT,TRANSACTIONTYPECODE,RECEIPTAMOUNT, DONOTACKNOWLEDGE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,ISREIMBURSABLE,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
values (@ID,@CONSTITUENTID,@DATE,@DONOTPOST,@POSTDATE,1,@BASEAMOUNT,6,0,@DONOTACKNOWLEDGE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@ISREIMBURSABLE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);
--Add origination source
exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
--Add the payment method record
insert into dbo.REVENUEPAYMENTMETHOD (ID,REVENUEID,PAYMENTMETHODCODE,AMOUNT,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values (newid(),@ID,9,@AMOUNT,@CHANGEAGENTID, @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
--Add the revenue schedule record
insert into dbo.REVENUESCHEDULE (ID,STARTDATE,FREQUENCYCODE,NUMBEROFINSTALLMENTS,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values (@ID,@STARTDATE,@FREQUENCYCODE,@NUMBEROFINSTALLMENTS,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
--Multicurrency - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.
set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;
--Multicurrency - AdamBu 3/30/10 - Process the installments xml to calculate the base and organization amounts and place them in proper nodes.
set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @REVENUEID = @ID,@CATEGORYCODEID = @CATEGORYCODEID,@CHANGEAGENTID = @CHANGEAGENTID,@CURRENTDATE = @CURRENTDATE;
--Pull in default solicitors before adding the opportunity record
exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE
--Add the revenue opportunity record
insert into dbo.REVENUEOPPORTUNITY
(
ID,
OPPORTUNITYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID,
@OPPORTUNITYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.REVENUESPLIT
where
REVENUEID = @ID
exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @ID,null,@CHANGEAGENTID,@CURRENTDATE;
if dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0
raiserror('INSTALLMENTSPLITSBALANCE', 13, 1);
-- Link revenue to Account System
exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @ID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID
--Save the GL distributions
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;