USP_DATAFORMTEMPLATE_ADD_MATCHINGGIFTCONDITION_2
The save procedure used by the add dataform template "Matching Gift Condition Add Form 2".
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. |
| @ORGANIZATIONID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @MATCHINGGIFTCONDITIONTYPECODEID | uniqueidentifier | IN | Type |
| @MATCHINGFACTOR | decimal(5, 2) | IN | Matching factor |
| @MINMATCHPERGIFT | money | IN | Min match per gift |
| @MAXMATCHPERGIFT | money | IN | Max match per gift |
| @MAXMATCHANNUAL | money | IN | Max match per year |
| @MAXMATCHTOTAL | money | IN | Max match total |
| @NOTES | nvarchar(max) | IN | Notes |
| @RELATIONSHIPS | xml | IN | Apply to |
| @MATCHTYPECODE | tinyint | IN | Matching amount |
| @REVENUETYPECODE | smallint | IN | Revenue type |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MATCHINGGIFTCONDITION_2
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@ORGANIZATIONID uniqueidentifier,
@MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier = null,
@MATCHINGFACTOR decimal(5,2) = 0,
@MINMATCHPERGIFT money = 0,
@MAXMATCHPERGIFT money = 0,
@MAXMATCHANNUAL money = 0,
@MAXMATCHTOTAL money = 0,
@NOTES nvarchar(max) = '',
@RELATIONSHIPS xml = null,
@MATCHTYPECODE tinyint = 1,
@REVENUETYPECODE smallint = 0,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
declare @BASECURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONMINMATCHPERGIFT money
declare @ORGANIZATIONMAXMATCHPERGIFT money
declare @ORGANIZATIONMAXMATCHANNUAL money
declare @ORGANIZATIONMAXMATCHTOTAL money
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier
if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
begin
set @ORGANIZATIONMINMATCHPERGIFT = @MINMATCHPERGIFT
set @ORGANIZATIONMAXMATCHPERGIFT = @MAXMATCHPERGIFT
set @ORGANIZATIONMAXMATCHANNUAL = @MAXMATCHANNUAL
set @ORGANIZATIONMAXMATCHTOTAL = @MAXMATCHTOTAL
end
else
begin
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENTDATE,0,null);
set @ORGANIZATIONMINMATCHPERGIFT = dbo.UFN_CURRENCY_CONVERT(@MINMATCHPERGIFT,@ORGANIZATIONEXCHANGERATEID)
set @ORGANIZATIONMAXMATCHPERGIFT = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHPERGIFT,@ORGANIZATIONEXCHANGERATEID)
set @ORGANIZATIONMAXMATCHANNUAL = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHANNUAL,@ORGANIZATIONEXCHANGERATEID)
set @ORGANIZATIONMAXMATCHTOTAL = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHTOTAL,@ORGANIZATIONEXCHANGERATEID)
end
begin try
insert into dbo.MATCHINGGIFTCONDITION
(
ID,
ORGANIZATIONID,
MATCHINGGIFTCONDITIONTYPECODEID,
MATCHINGFACTOR,
MINMATCHPERGIFT,
MAXMATCHPERGIFT,
MAXMATCHANNUAL,
MAXMATCHTOTAL,
NOTES,
MATCHTYPECODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
REVENUETYPECODE,
[SEQUENCE],
BASECURRENCYID,
ORGANIZATIONMINMATCHPERGIFT,
ORGANIZATIONMAXMATCHPERGIFT,
ORGANIZATIONMAXMATCHANNUAL,
ORGANIZATIONMAXMATCHTOTAL,
ORGANIZATIONEXCHANGERATEID
)
values
(
@ID,
@ORGANIZATIONID,
@MATCHINGGIFTCONDITIONTYPECODEID,
@MATCHINGFACTOR,
@MINMATCHPERGIFT,
@MAXMATCHPERGIFT,
@MAXMATCHANNUAL,
@MAXMATCHTOTAL,
@NOTES,
@MATCHTYPECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@REVENUETYPECODE,
coalesce((select max(SEQUENCE) from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @ORGANIZATIONID)+1,0),
@BASECURRENCYID,
@ORGANIZATIONMINMATCHPERGIFT,
@ORGANIZATIONMAXMATCHPERGIFT,
@ORGANIZATIONMAXMATCHANNUAL,
@ORGANIZATIONMAXMATCHTOTAL,
@ORGANIZATIONEXCHANGERATEID
);
if @RELATIONSHIPS is not null begin
--JamesWill WI 13119 Fix relationships
with [CTE] as
(
select
T.c.value('(ID)[1]', 'nvarchar(36)') as [ID],
T.c.value('(RELATIONSHIPTYPECODEID)[1]', 'nvarchar(36)') as [RELATIONSHIPTYPECODEID],
T.c.value('(JOBSCHEDULECODEID)[1]', 'nvarchar(36)') as [JOBSCHEDULECODEID],
T.c.value('(CAREERLEVELCODEID)[1]', 'nvarchar(36)') as [CAREERLEVELCODEID]
from @RELATIONSHIPS.nodes('/RELATIONSHIPS/ITEM') T(c)
)
select
@RELATIONSHIPS =
( select
case when len([CTE].ID) > 0 then cast([CTE].ID as uniqueidentifier) else newid() end as [ID],
cast([CTE].RELATIONSHIPTYPECODEID as uniqueidentifier) as [RELATIONSHIPTYPECODEID],
cast([CTE].JOBSCHEDULECODEID as uniqueidentifier) as [JOBSCHEDULECODEID],
cast([CTE].CAREERLEVELCODEID as uniqueidentifier) as [CAREERLEVELCODEID]
from [CTE]
where len([CTE].RELATIONSHIPTYPECODEID) > 0
for xml raw('ITEM'), type, elements, root('RELATIONSHIPS'), binary base64
)
exec dbo.USP_MATCHINGGIFTCONDITIONRELATIONSHIP_VALIDATERELATIONSHIPTYPES @ID, @RELATIONSHIPS;
exec dbo.USP_MATCHINGGIFTCONDITIONRELATIONSHIP_GETRELATIONSHIPS_UPDATEFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID;
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;