USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONPROGRAMCURRENCY
The save procedure used by the edit dataform template "Recognition Program Base Currency Edit Data 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. |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONPROGRAMCURRENCY (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@BASECURRENCYID uniqueidentifier
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
if exists(select 1 from dbo.CONSTITUENTRECOGNITIONREVENUE where RECOGNITIONPROGRAMID = @ID) or
exists(select 1 from dbo.CONSTITUENTRECOGNITION where RECOGNITIONPROGRAMID = @ID)
begin
raiserror('The base currency of this recognition program cannot be changed.', 13, 1);
return 1;
end
declare @OLDBASECURRENCYID uniqueidentifier;
select
@OLDBASECURRENCYID = BASECURRENCYID
from
dbo.RECOGNITIONPROGRAM
where
ID = @ID;
--If the user saves without changing the base currency, don't update org amounts.
if @OLDBASECURRENCYID <> @BASECURRENCYID
begin
declare @ORGCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
--If the new base currency is the org currency, org amount equals amount, so don't bother getting
-- an exchange rate or doing a conversion.
if @BASECURRENCYID = @ORGCURRENCYID
begin
update
dbo.RECOGNITIONPROGRAM
set
ORGANIZATIONMAXIMUMAMOUNT = MAXIMUMAMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
update
dbo.RECOGNITIONLEVEL
set
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
RECOGNITIONPROGRAMID = @ID;
update
dbo.RECOGNITIONLEVELAMOUNT
set
ORGANIZATIONAMOUNT = RECOGNITIONLEVELAMOUNT.AMOUNT,
ORGANIZATIONPLANNEDGIFTAMOUNT = RECOGNITIONLEVELAMOUNT.PLANNEDGIFTAMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RECOGNITIONLEVELAMOUNT
inner join
dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = RECOGNITIONLEVELAMOUNT.RECOGNITIONLEVELID
where
RECOGNITIONLEVEL.RECOGNITIONPROGRAMID = @ID;
end
else --Base currency has changed and isn't the org currency
begin
update
dbo.RECOGNITIONPROGRAM
set
ORGANIZATIONMAXIMUMAMOUNT = dbo.UFN_CURRENCY_CONVERT(MAXIMUMAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, RECOGNITIONPROGRAM.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, RECOGNITIONPROGRAM.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
update
dbo.RECOGNITIONLEVEL
set
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
RECOGNITIONPROGRAMID = @ID;
update
dbo.RECOGNITIONLEVELAMOUNT
set
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(RECOGNITIONLEVELAMOUNT.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, RECOGNITIONLEVELAMOUNT.DATEADDED, 0, null)),
ORGANIZATIONPLANNEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(RECOGNITIONLEVELAMOUNT.PLANNEDGIFTAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, RECOGNITIONLEVELAMOUNT.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, RECOGNITIONLEVELAMOUNT.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.RECOGNITIONLEVELAMOUNT
inner join
dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = RECOGNITIONLEVELAMOUNT.RECOGNITIONLEVELID
where
RECOGNITIONLEVEL.RECOGNITIONPROGRAMID = @ID;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;