USP_DATAFORMTEMPLATE_EDIT_DONORCHALLENGE_CLAIM
The save procedure used by the edit dataform template "Donor Challenge Revenue 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 |
| @SPLITS | xml | IN | Designations |
| @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 |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DONORCHALLENGE_CLAIM
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DATE datetime,
@AMOUNT money,
@SPLITS xml,
@BASECURRENCYID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@HADSPOTRATE bit,
@RATECHANGED bit,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
declare @CHANGEDATE datetime;
set @CHANGEDATE = getdate();
declare @OLDDATE datetime;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
--Multicurrency - AdamBu 5/10/10 - 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()
--Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future
/*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
begin
raiserror('User does not have the right to add a new spot rate.', 13, 1);
return 1;
end*/
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,
@CHANGEDATE,
@CHANGEDATE
);
end
exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID, @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID;
select
@OLDDATE = DATE
from dbo.REVENUE
where ID = @ID;
-- make sure we aren't trying to modify revenue in a locked/closed deposit/bank
exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;
--Multicurrency - AdamBu 5/7/10 - 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 output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
update dbo.REVENUE
set
DATE = @DATE,
AMOUNT = @BASEAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT = @AMOUNT,
TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where ID = @ID;
if @DATE <> @OLDDATE
update dbo.REVENUESCHEDULE
set STARTDATE = @DATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID;
update dbo.INSTALLMENT
set AMOUNT = @BASEAMOUNT,
DATE = @DATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT = @AMOUNT,
TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where REVENUEID = @ID;
declare @SPLITSCHANGED bit
set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)
set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
exec dbo.USP_REVENUE_GETSPLITS_2_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CHANGEDATE;
exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CHANGEDATE;
if @SPLITSCHANGED = 1
begin
--assume one installment
declare @INSTALLMENTSPLITS xml;
set @INSTALLMENTSPLITS = (
select distinct
INSTALLMENTSPLIT.ID,
INSTALLMENT.ID
INSTALLMENTID,
REVENUESPLIT.DESIGNATIONID,
REVENUESPLIT.AMOUNT,
REVENUESPLIT.BASECURRENCYID,
REVENUESPLIT.ORGANIZATIONAMOUNT,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
REVENUESPLIT.TRANSACTIONAMOUNT,
REVENUESPLIT.TRANSACTIONCURRENCYID,
REVENUESPLIT.BASEEXCHANGERATEID,
REVENUESPLIT.ID as REVENUESPLITID
from REVENUESPLIT
inner join INSTALLMENT on INSTALLMENT.REVENUEID = REVENUESPLIT.REVENUEID
left outer join INSTALLMENTSPLIT
on REVENUESPLIT.ID = INSTALLMENTSPLIT.REVENUESPLITID and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
where REVENUESPLIT.REVENUEID = @ID
for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
);
exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_2_UPDATEFROMXML @ID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CHANGEDATE;
exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CHANGEDATE = @CHANGEDATE
end
exec dbo.USP_PLEDGE_VALIDATE_2 @ID, 0;
--Multicurrency - AdamBu 5/10/10 - 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
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
return 0;