USP_DATAFORMTEMPLATE_EDIT_FUNDRAISINGPURPOSEBASECURRENCY
The save procedure used by the edit dataform template "Fundraising Purpose Base Currency 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. |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FUNDRAISINGPURPOSEBASECURRENCY (
@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 (select COUNT(ID) from dbo.DESIGNATION
where
(
DESIGNATION.DESIGNATIONLEVEL1ID = @ID or
DESIGNATION.DESIGNATIONLEVEL2ID = @ID or
DESIGNATION.DESIGNATIONLEVEL3ID = @ID or
DESIGNATION.DESIGNATIONLEVEL4ID = @ID or
DESIGNATION.DESIGNATIONLEVEL5ID = @ID
)
) > 1 or
exists(select 1 from dbo.BATCHDESIGNATIONLEVELFINANCIALINFO where DESIGNATIONLEVELID = @ID)
begin
raiserror('The base currency of this purpose cannot be changed.', 13, 1);
return 1;
end
declare @OLDBASECURRENCYID uniqueidentifier;
select
@OLDBASECURRENCYID = BASECURRENCYID
from
dbo.DESIGNATIONLEVEL
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.DESIGNATIONLEVEL set
ORGANIZATIONENDOWMENTMINAMOUNT = ENDOWMENTMINAMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
update dbo.DESIGNATIONLEVELGOAL
set
ORGANIZATIONGOAL = GOAL,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATION set
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
(
DESIGNATION.DESIGNATIONLEVEL1ID = @ID or
DESIGNATION.DESIGNATIONLEVEL2ID = @ID or
DESIGNATION.DESIGNATIONLEVEL3ID = @ID or
DESIGNATION.DESIGNATIONLEVEL4ID = @ID or
DESIGNATION.DESIGNATIONLEVEL5ID = @ID
)
update dbo.DESIGNATIONGOAL
set
DESIGNATIONGOAL.ORGANIZATIONGOAL = DESIGNATIONGOAL.GOAL,
DESIGNATIONGOAL.ORGANIZATIONEXCHANGERATEID = null,
DESIGNATIONGOAL.BASECURRENCYID = @BASECURRENCYID,
DESIGNATIONGOAL.CHANGEDBYID = @CHANGEAGENTID,
DESIGNATIONGOAL.DATECHANGED = @CURRENTDATE
from
dbo.DESIGNATIONGOAL
left join dbo.DESIGNATION
on DESIGNATIONGOAL.DESIGNATIONID = DESIGNATION.ID
where
(
DESIGNATION.DESIGNATIONLEVEL1ID = @ID or
DESIGNATION.DESIGNATIONLEVEL2ID = @ID or
DESIGNATION.DESIGNATIONLEVEL3ID = @ID or
DESIGNATION.DESIGNATIONLEVEL4ID = @ID or
DESIGNATION.DESIGNATIONLEVEL5ID = @ID
)
update dbo.DESIGNATIONLEVELDISBURSEMENT
set
ORGANIZATIONAMOUNT = AMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELDECAPITALIZATION
set
ORGANIZATIONAMOUNT = AMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELFINANCIALINFO
set
ORGANIZATIONBOOKVALUESTART = BOOKVALUESTART,
ORGANIZATIONBOOKVALUEEND = BOOKVALUEEND,
ORGANIZATIONMARKETVALUESTART = MARKETVALUESTART,
ORGANIZATIONMARKETVALUEEND = MARKETVALUEEND,
ORGANIZATIONTOTALGIFTREVENUE = TOTALGIFTREVENUE,
ORGANIZATIONPLEDGEBALANCE = PLEDGEBALANCE,
ORGANIZATIONTOTALDISBURSEMENTS = TOTALDISBURSEMENTS,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELRECIPIENT
set
ORGANIZATIONAMOUNT = AMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELGIFTFEESTRUCTURE
set
ORGANIZATIONFROMAMOUNT = FROMAMOUNT,
ORGANIZATIONEXCHANGERATEID = null,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID and USEORGANIZATIONCURRENCY = 0
end
else --Base currency has changed and isn't the org currency
begin
declare @CURRENCYEXCHANGERATEID uniqueidentifier;
select
@CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVEL.DATEADDED, 0, null)
from
dbo.DESIGNATIONLEVEL
where
ID = @ID
update dbo.DESIGNATIONLEVEL set
ORGANIZATIONENDOWMENTMINAMOUNT = dbo.UFN_CURRENCY_CONVERT(ENDOWMENTMINAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVEL.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = @CURRENCYEXCHANGERATEID,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID
update dbo.DESIGNATIONLEVELGOAL
set
ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERT(GOAL, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELGOAL.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELGOAL.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATION set
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
(
DESIGNATION.DESIGNATIONLEVEL1ID = @ID or
DESIGNATION.DESIGNATIONLEVEL2ID = @ID or
DESIGNATION.DESIGNATIONLEVEL3ID = @ID or
DESIGNATION.DESIGNATIONLEVEL4ID = @ID or
DESIGNATION.DESIGNATIONLEVEL5ID = @ID
)
update dbo.DESIGNATIONGOAL
set
DESIGNATIONGOAL.ORGANIZATIONGOAL = dbo.UFN_CURRENCY_CONVERT(DESIGNATIONGOAL.GOAL, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONGOAL.DATEADDED, 0, null)),
DESIGNATIONGOAL.ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONGOAL.DATEADDED, 0, null),
DESIGNATIONGOAL.BASECURRENCYID = @BASECURRENCYID,
DESIGNATIONGOAL.CHANGEDBYID = @CHANGEAGENTID,
DESIGNATIONGOAL.DATECHANGED = @CURRENTDATE
from
dbo.DESIGNATIONGOAL
left join dbo.DESIGNATION
on DESIGNATIONGOAL.DESIGNATIONID = DESIGNATION.ID
where
(
DESIGNATION.DESIGNATIONLEVEL1ID = @ID or
DESIGNATION.DESIGNATIONLEVEL2ID = @ID or
DESIGNATION.DESIGNATIONLEVEL3ID = @ID or
DESIGNATION.DESIGNATIONLEVEL4ID = @ID or
DESIGNATION.DESIGNATIONLEVEL5ID = @ID
)
update dbo.DESIGNATIONLEVELDISBURSEMENT
set
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELDISBURSEMENT.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELDISBURSEMENT.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELDECAPITALIZATION
set
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELDECAPITALIZATION.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELDECAPITALIZATION.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELFINANCIALINFO
set
ORGANIZATIONBOOKVALUESTART = dbo.UFN_CURRENCY_CONVERT(BOOKVALUESTART, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONBOOKVALUEEND = dbo.UFN_CURRENCY_CONVERT(BOOKVALUEEND, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONMARKETVALUESTART = dbo.UFN_CURRENCY_CONVERT(MARKETVALUESTART, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONMARKETVALUEEND = dbo.UFN_CURRENCY_CONVERT(MARKETVALUEEND, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONTOTALGIFTREVENUE = dbo.UFN_CURRENCY_CONVERT(TOTALGIFTREVENUE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONPLEDGEBALANCE = dbo.UFN_CURRENCY_CONVERT(PLEDGEBALANCE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONTOTALDISBURSEMENTS = dbo.UFN_CURRENCY_CONVERT(TOTALDISBURSEMENTS, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELFINANCIALINFO.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELRECIPIENT
set
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELRECIPIENT.DATEADDED, 0, null)),
ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, DESIGNATIONLEVELRECIPIENT.DATEADDED, 0, null),
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID
update dbo.DESIGNATIONLEVELGIFTFEESTRUCTURE
set
ORGANIZATIONFROMAMOUNT = dbo.UFN_CURRENCY_CONVERT(FROMAMOUNT, @CURRENCYEXCHANGERATEID),
ORGANIZATIONEXCHANGERATEID = @CURRENCYEXCHANGERATEID,
BASECURRENCYID = @BASECURRENCYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
DESIGNATIONLEVELID = @ID and USEORGANIZATIONCURRENCY = 0
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;