USP_DATAFORMTEMPLATE_EDIT_PLEDGE_2
The save procedure used by the edit dataform template "Pledge Edit Form 2".
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 |
| @SENDPLEDGEREMINDER | bit | IN | Send pledge reminders |
| @SPLITS | xml | IN | Designations |
| @FREQUENCYCODE | tinyint | IN | Frequency |
| @NUMBEROFINSTALLMENTS | int | IN | No. installments remaining |
| @NEXTTRANSACTIONDATE | datetime | IN | Next installment date |
| @INSTALLMENTS | xml | IN | |
| @SOURCECODE | nvarchar(50) | IN | Source code |
| @APPEALID | uniqueidentifier | IN | Appeal |
| @BENEFITS | xml | IN | Benefits |
| @BENEFITSWAIVED | bit | IN | Benefits waived |
| @GIVENANONYMOUSLY | bit | IN | Pledge is anonymous |
| @MAILINGID | uniqueidentifier | IN | Mailing |
| @CHANNELCODEID | uniqueidentifier | IN | Channel |
| @DONOTACKNOWLEDGE | bit | IN | Do not acknowledge |
| @PLEDGESUBTYPEID | uniqueidentifier | IN | Subtype |
| @OPPORTUNITYID | uniqueidentifier | IN | Opportunity |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGE_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@DATE datetime,
@AMOUNT money,
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@SENDPLEDGEREMINDER bit,
@SPLITS xml,
@FREQUENCYCODE tinyint,
@NUMBEROFINSTALLMENTS int,
@NEXTTRANSACTIONDATE datetime,
@INSTALLMENTS xml,
@SOURCECODE nvarchar(50),
@APPEALID uniqueidentifier,
@BENEFITS xml,
@BENEFITSWAIVED bit,
@GIVENANONYMOUSLY bit,
@MAILINGID uniqueidentifier,
@CHANNELCODEID uniqueidentifier,
@DONOTACKNOWLEDGE bit,
@PLEDGESUBTYPEID uniqueidentifier,
@OPPORTUNITYID uniqueidentifier
)
as
begin
set nocount on;
declare @CURRENTDATE datetime;
declare @SUM money;
declare @COUNT int;
declare @FIRSTINSTALLMENTDATE datetime;
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
begin try
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = GetDate();
exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 1;
select
@SUM = sum(AMOUNT),
@COUNT = count(AMOUNT),
@FIRSTINSTALLMENTDATE = min([DATE])
from
dbo.UFN_INSTALLMENT_GETINSTALLMENTS_FROMITEMLISTXML(@INSTALLMENTS);
if @COUNT = 0
raiserror('INSTALLMENTCOUNT',13,1);
if @SUM <> @AMOUNT
raiserror('INSTALLMENTSUM',13,1);
if dbo.UFN_DATE_GETEARLIESTTIME(@FIRSTINSTALLMENTDATE) < dbo.UFN_DATE_GETEARLIESTTIME(@DATE)
raiserror('INSTALLMENTINVALIDSTARTDATE', 13, 1);
/* You can only edit unposted pledges here */
if (select count(REVENUE.ID) from dbo.REVENUE
inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
where REVENUE.ID = @ID) > 0
raiserror('You cannot edit a posted pledge.', 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_FROMITEMLISTXML(@INSTALLMENTS) XMLINST
on INSTALLMENT.ID = XMLINST.ID
where XMLINST.AMOUNT < XMLINST.APPLIED) > 0
raiserror('PLEDGEPAYMENT_INSTALLMENTAPPLIED',13,1);
declare @TRIBUTEAMOUNT money;
select @TRIBUTEAMOUNT = sum(AMOUNT) from dbo.REVENUETRIBUTE where REVENUEID = @ID;
-- do not allow the gift amount to be adjusted less than the applied tribute amount
if (@TRIBUTEAMOUNT is not null) and (@AMOUNT < @TRIBUTEAMOUNT)
begin
raiserror('The pledge amount cannot be less than the sum of the tribute amounts applied to this pledge.', 13, 1)
end
declare @DONOTPOST bit;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @CLEARGLDISTRIBUTION bit;
declare @CLEARWRITEOFFGLDISTRIBUTION bit;
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;
end
-- check to see if amount, postdate, or post status has changed
if @CLEARGLDISTRIBUTION = 0
if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and AMOUNT = @AMOUNT and POSTDATE = @POSTDATE and ((@POSTSTATUSCODE = 2 and DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and DONOTPOST = 0))) = 0
set @CLEARGLDISTRIBUTION = 1;
-- check to see if the revenue record needs to be re-acknowledged
if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
begin
declare @FIELDCHANGED bit;
set @FIELDCHANGED = 0;
-- check to see if amount have changed
if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and AMOUNT = @AMOUNT) = 0
set @FIELDCHANGED = 1;
-- check to see if designations have changed
if @FIELDCHANGED = 0
if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
set @FIELDCHANGED = 1;
-- if a field has changed, mark the revenue letters for this record out of date, if necessary
if @FIELDCHANGED = 1
exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;
end
update
dbo.REVENUE
set
DATE = @DATE,
DONOTPOST = @DONOTPOST,
POSTDATE = @POSTDATE,
AMOUNT = @AMOUNT,
SOURCECODE = @SOURCECODE,
APPEALID = @APPEALID,
BENEFITSWAIVED = @BENEFITSWAIVED,
GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
MAILINGID = @MAILINGID,
CHANNELCODEID = @CHANNELCODEID,
DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
if exists (select ID from dbo.REVENUEPOSTED where ID = @ID) begin
if @POSTSTATUSCODE <> 0
delete dbo.REVENUEPOSTED where ID = @ID;
end
else begin
if @POSTSTATUSCODE = 0
insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
end
update
dbo.REVENUESCHEDULE
set
FREQUENCYCODE = @FREQUENCYCODE,
NUMBEROFINSTALLMENTS = @COUNT,
NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE,
PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.REVENUESCHEDULE
where
REVENUESCHEDULE.ID = @ID;
exec dbo.USP_REVENUE_GETSPLITS_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PLEDGE_UPDATEINSTALLMENT @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE
--MMR not sure we still need this?
exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_REVENUE_GETBENEFITS_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE;
-- 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
if @OPPORTUNITYID is null
delete REVENUEOPPORTUNITY
from dbo.REVENUEOPPORTUNITY
inner join dbo.REVENUESPLIT
on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
where REVENUESPLIT.REVENUEID = @ID
else begin
insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.REVENUESPLIT where REVENUEID = @ID
and not exists(select top 1 REVENUEOPPORTUNITY.ID from REVENUEOPPORTUNITY where REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID);
update
dbo.OPPORTUNITY
set
STATUSCODE = 3, -- accepted
ASKDATE = case when ASKDATE is null then dbo.UFN_DATE_GETEARLIESTTIME(getdate()) else ASKDATE end,
RESPONSEDATE = case when RESPONSEDATE is null then dbo.UFN_DATE_GETEARLIESTTIME(getdate()) else RESPONSEDATE end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where
ID = @OPPORTUNITYID
and
STATUSCODE in (4, 5);
end
exec dbo.USP_PLEDGE_VALIDATE @ID;
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;
end