USP_DATAFORMTEMPLATE_EDIT_GRANTAWARD_2
The save procedure used by the edit dataform template "Grant Award Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @DATE | datetime | IN | Date |
| @AMOUNT | money | IN | Amount |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @SPLITS | xml | IN | Designations |
| @FREQUENCYCODE | tinyint | IN | Frequency |
| @NUMBEROFINSTALLMENTS | int | IN | No. installments remaining |
| @NEXTTRANSACTIONDATE | datetime | IN | Next installment date |
| @INSTALLMENTS | xml | IN | |
| @DONOTACKNOWLEDGE | bit | IN | Do not acknowledge |
| @CATEGORYCODEID | uniqueidentifier | IN | Revenue category |
| @ISREIMBURSABLE | bit | IN | Grant award is reimbursable |
| @OPPORTUNITYID | uniqueidentifier | IN | Opportunity |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Transaction currency |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @HADSPOTRATE | bit | IN | Had spot rate |
| @RATECHANGED | bit | IN | Rate changed |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GRANTAWARD_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@DATE datetime,
@AMOUNT money,
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@SPLITS xml,
@FREQUENCYCODE tinyint,
@NUMBEROFINSTALLMENTS int,
@NEXTTRANSACTIONDATE datetime,
@INSTALLMENTS xml,
@DONOTACKNOWLEDGE bit,
@CATEGORYCODEID uniqueidentifier,
@ISREIMBURSABLE bit,
@OPPORTUNITYID uniqueidentifier,
@BASECURRENCYID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@HADSPOTRATE bit,
@RATECHANGED bit
)
as
set nocount on;
-- Check GL business rule for this account system and set to 'Do not post' if needed.
-- ****
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.PDACCOUNTSYSTEMFORREVENUE
where ID = @ID;
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
begin
set @POSTSTATUSCODE = 2 -- Do not post
set @POSTDATE = null
end
-- ****
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
declare @SUM money;
declare @COUNT int;
declare @contextCache varbinary(128);
-- Multicurrency - If the revenue previously used a spot rate, but
-- its rate has changed, store the old rate's ID, so we can remove it later.
declare @OLDSPOTRATE uniqueidentifier
if @HADSPOTRATE = 1 and @RATECHANGED = 1
begin
select
@OLDSPOTRATE = BASEEXCHANGERATEID
from dbo.REVENUE
where ID = @ID
end
--If the record uses a new spot rate, create it and set the rate ID.
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
exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID, @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID;
select
@SUM = sum(AMOUNT),
@COUNT = count(AMOUNT)
from
dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS);
if @COUNT = 0
raiserror('INSTALLMENTCOUNT',13,1);
if @SUM <> @AMOUNT
raiserror('INSTALLMENTSUM',13,1);
/* You can only edit unposted grant awards here */
if exists (select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @ID)
raiserror('You cannot edit a posted grant award.', 13, 1);
if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
raiserror('Installment dates are out of sequence.',13,1);
if (select count(INSTALLMENT.ID) from dbo.INSTALLMENT
inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) XMLINST
on INSTALLMENT.ID = XMLINST.ID
where XMLINST.AMOUNT < XMLINST.APPLIED) > 0
raiserror('GRANTAWARDPAYMENT_INSTALLMENTAPPLIED',13,1);
declare @DONOTPOST bit;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @REACKNOWLEDGEFIELDCHANGED bit;
declare @CLEARGLDISTRIBUTION bit;
declare @CLEARWRITEOFFGLDISTRIBUTION bit;
set @REACKNOWLEDGEFIELDCHANGED = 0;
set @CLEARGLDISTRIBUTION = 0;
set @CLEARWRITEOFFGLDISTRIBUTION = 0;
-- check to see if designations have changed
if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
begin
set @CLEARGLDISTRIBUTION = 1;
set @CLEARWRITEOFFGLDISTRIBUTION = 1;
set @REACKNOWLEDGEFIELDCHANGED = 1;
end
declare @OLDCATEGORYCODEID uniqueidentifier
select top 1
@OLDCATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
from
dbo.REVENUECATEGORY
inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
where
REVENUESPLIT.REVENUEID = @ID;
/* Check if revenue category changed */
if (@CATEGORYCODEID is null and @OLDCATEGORYCODEID is not null)
or (@CATEGORYCODEID is not null and @OLDCATEGORYCODEID is null)
or (@CATEGORYCODEID <> @OLDCATEGORYCODEID)
begin
set @CLEARGLDISTRIBUTION = 1;
set @CLEARWRITEOFFGLDISTRIBUTION = 1;
end
--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;
-- check to see if amounts, postdate, post status, or exchange rates has changed
if @CLEARGLDISTRIBUTION = 0
if (
select
count(REVENUE.ID)
from
dbo.REVENUE
where
REVENUE.ID = @ID
and TRANSACTIONAMOUNT = @AMOUNT
and POSTDATE = @POSTDATE
and ((@POSTSTATUSCODE = 2 and DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and DONOTPOST = 0))
and AMOUNT = @BASEAMOUNT
and ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT
and BASEEXCHANGERATEID = @BASEEXCHANGERATEID
and ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
and TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
and BASECURRENCYID = @BASECURRENCYID
) = 0
begin
set @CLEARGLDISTRIBUTION = 1;
end
-- check to see if the revenue record needs to be re-acknowledged
if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
begin
-- check to see if amount have changed
if @REACKNOWLEDGEFIELDCHANGED = 0
if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and TRANSACTIONAMOUNT = @AMOUNT) = 0
set @REACKNOWLEDGEFIELDCHANGED = 1;
-- if a field has changed, mark the revenue letters for this record out of date, if necessary
if @REACKNOWLEDGEFIELDCHANGED = 1
exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;
end
update
dbo.REVENUE
set
DATE = @DATE,
DONOTPOST = @DONOTPOST,
POSTDATE = @POSTDATE,
AMOUNT = @BASEAMOUNT,
DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
ISREIMBURSABLE = @ISREIMBURSABLE,
BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
TRANSACTIONAMOUNT = @AMOUNT,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
where
ID = @ID;
update
dbo.REVENUESCHEDULE
set
FREQUENCYCODE = @FREQUENCYCODE,
NUMBEROFINSTALLMENTS = @COUNT,
NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.REVENUESCHEDULE
where
REVENUESCHEDULE.ID = @ID;
declare @SPLITSCHANGED bit
set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)
-- 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_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @CATEGORYCODEID, @CHANGEAGENTID, @CURRENTDATE;
if (@OPPORTUNITYID is not null) and (@OPPORTUNITYID <> '00000000-0000-0000-0000-000000000000')
begin
--Pull in default solicitors before changing the opportunity record.
exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE
update dbo.REVENUEOPPORTUNITY
set
OPPORTUNITYID = @OPPORTUNITYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID in (select ID from dbo.REVENUESPLIT where REVENUEID = @ID)
insert into dbo.REVENUEOPPORTUNITY
(
ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from (
select REVENUESPLIT.ID [ID]
from dbo.REVENUESPLIT
left join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID
where REVENUEID = @ID and REVENUEOPPORTUNITY.ID is null
) [NEWSPLITS];
exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE
end
else
begin
exec dbo.USP_REVENUE_PULLSOLICITORSFROMFUNDINGREQUEST_ONLYNEWSPLITS @SPLITS, @ID, @CHANGEAGENTID, @CURRENTDATE;
end
--MMR not sure we still need this?
exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Multicurrency - Pass in multicurrency information so we can convert the amount in the xml
-- once installment splits are pulled out of their installments.
exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE,
null, null, null, '',
@BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID;
-- clear the user-defined gl distributions
if @CLEARGLDISTRIBUTION = 1
begin
-- Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new GL distributions
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
if @CLEARWRITEOFFGLDISTRIBUTION = 1 and exists (select GL.ID from dbo.WRITEOFFGLDISTRIBUTION GL inner join dbo.WRITEOFF on GL.WRITEOFFID = WRITEOFF.ID where WRITEOFF.REVENUEID = @ID)
begin
delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID) and OUTDATED = 0;
-- Add new writeoff GL distributions
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
end
exec dbo.USP_PLEDGE_VALIDATE @ID;
if @SPLITSCHANGED = 1
exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @ID,null,@CHANGEAGENTID,@CURRENTDATE;
-- Multicurrency - If we stored an old spot rate earlier, now is the time to remove it.
if @OLDSPOTRATE is not null
begin
delete CURRENCYEXCHANGERATE where ID = @OLDSPOTRATE;
end
end try
begin catch
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
return 0;