USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED
The save procedure used by the edit dataform template "Revenue Transaction Change Constituent Posted 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. |
| @NEWCONSTITUENTID | uniqueidentifier | IN | New constituent |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjustment post date |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @ADJUSTMENTREASON | nvarchar(100) | IN | Adjustment details |
| @RESETRECOGNITIONCREDITS | bit | IN | Recognition credit |
| @RESETSOLICITORS | bit | IN | Solicitors |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NEWCONSTITUENTID uniqueidentifier,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTREASON nvarchar(100),
@RESETRECOGNITIONCREDITS bit,
@RESETSOLICITORS bit
)
as
set nocount on
-- Verify the new constituent isn't the same as the previous one
declare @PREVIOUSCONSTITUENTID uniqueidentifier
select top 1
@PREVIOUSCONSTITUENTID = CONSTITUENTID
from dbo.REVENUE
where ID = @ID
if @PREVIOUSCONSTITUENTID = @NEWCONSTITUENTID
begin
raiserror('NEWCONSTITUENTCANNOTBECURRENTCONSTITUENT', 13, 1)
return 1
end
declare @CHANGEDATE datetime
set @CHANGEDATE = getdate()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
-- Verify the transaction has already been posted
declare @ISPOSTED bit
select
@ISPOSTED =
case
when exists (select 1 from dbo.REVENUEPOSTED RP where RP.ID = @ID) then 1
else 0
end
if @ISPOSTED = 0
begin
raiserror('TRANSACTIONMUSTBEPOSTED', 13, 1)
return 1
end
-- Verify the adjustment dates are set
if @ADJUSTMENTPOSTDATE is null
begin
raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
return 1
end
if @ADJUSTMENTDATE is null
begin
raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
return 1
end
declare @REVENUEID uniqueidentifier
declare @ADJUSTMENTID uniqueidentifier
/* Cursor to use for logging adjustments */
declare REVENUECURSOR cursor local fast_forward for
select REVENUE.ID
from dbo.REVENUE
where REVENUE.ID = @ID
declare @ADJUSTED table(REVENUEID uniqueidentifier, ADJUSTMENTID uniqueidentifier)
/* Cursor to use for logging history adjustments */
declare HISTORYCURSOR cursor local fast_forward for
select REVENUEID, ADJUSTMENTID from @ADJUSTED
begin try
-- update the payment information for each revenue record in the transaction
open REVENUECURSOR;
fetch next from REVENUECURSOR into @REVENUEID;
while @@FETCH_STATUS = 0
begin
set @ADJUSTMENTID = null;
exec dbo.USP_SAVE_ADJUSTMENT @REVENUEID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON
insert into @ADJUSTED(REVENUEID, ADJUSTMENTID)
values(@REVENUEID, @ADJUSTMENTID)
fetch next from REVENUECURSOR into @REVENUEID;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close REVENUECURSOR;
deallocate REVENUECURSOR;
exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;
exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID, @CHANGEDATE;
-- Update the constituent for all revenue entries belonging to the transaction
/* CMC
update dbo.FINANCIALTRANSACTION set
CONSTITUENTID = @NEWCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where FINANCIALTRANSACTION.ID = @ID
*/
update dbo.REVENUE set
CONSTITUENTID = @NEWCONSTITUENTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where REVENUE.ID = @ID
open HISTORYCURSOR
fetch next from HISTORYCURSOR into @REVENUEID, @ADJUSTMENTID
while @@FETCH_STATUS = 0
begin
/*call USP_ADJUSTMENTHISTORY_*_SAVEHISTORY after the revenue tables are updated */
if exists(select top 1 ID from dbo.REVENUE where ID = @REVENUEID)
begin
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @ADJUSTMENTID
end
fetch next from HISTORYCURSOR into @REVENUEID, @ADJUSTMENTID
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close HISTORYCURSOR;
deallocate HISTORYCURSOR;
declare @contextCache varbinary(128);
if @RESETRECOGNITIONCREDITS = 1
begin
--Cache CONTEXT INFO
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- Remove previous recognition credits
delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select ID from dbo.REVENUESPLIT where REVENUEID = @ID)
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- Create the new default recognition credits
insert into dbo.REVENUERECOGNITION
(
REVENUESPLITID,
CONSTITUENTID,
AMOUNT,
REVENUERECOGNITIONTYPECODEID,
EFFECTIVEDATE,
ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
RS.ID,
RR.CONSTITUENTID,
RR.AMOUNT,
RR.REVENUERECOGNITIONTYPECODEID,
R.DATE,
@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE
from dbo.REVENUE R
inner join dbo.REVENUESPLIT RS
on RS.REVENUEID = R.ID
left join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = R.ID
cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(R.GIVENANONYMOUSLY, R.CONSTITUENTID, RS.AMOUNT, R.DATE, RGA.SOURCEREVENUEID) RR
where R.ID = @ID
end
if @RESETSOLICITORS = 1
begin
--Cache CONTEXT INFO
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- Remove previous solicitors
delete from dbo.REVENUESOLICITOR where REVENUESPLITID in (select ID from dbo.REVENUESPLIT where REVENUEID = @ID)
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0