USP_DATAFORMTEMPLATE_EDIT_GIFTFEEGLDISTRIBUTIONADJUST
The save procedure used by the edit dataform template "Posted Gift Fee GL Distribution Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @GLDISTRIBUTION | xml | IN | Gift fee GL distribution |
| @ADJUSTMENTDATE | datetime | IN | Adjusted date |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjusted post date |
| @ADJUSTMENTREASON | nvarchar(300) | IN | Adjustment details |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ADJUSTMENTREASONCODEID | uniqueidentifier | IN | Adjustment reason |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GIFTFEEGLDISTRIBUTIONADJUST
(
@ID uniqueidentifier,
@GLDISTRIBUTION xml,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTREASON nvarchar(300),
@CHANGEAGENTID uniqueidentifier = null,
@ADJUSTMENTREASONCODEID uniqueidentifier
)
as
begin try
set nocount on;
declare @ADJUSTMENTID uniqueidentifier;
declare @ADJUST bit;
declare @ADJUSTMENTEXISTS bit;
set @ADJUST = 0
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if (dbo.UFN_GLDISTRIBUTION_DEBITSEQUALCREDITS(@GLDISTRIBUTION) = 0)
raiserror('BBERR_SUMOFCREDITSMUSTEQUALSUMOFDEBITS', 13, 1);
if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS_2(@GLDISTRIBUTION, @ID) = 0)
raiserror('BBERR_ACCOUNTDOESNOTEXIST', 13, 1);
declare @CHANGEDATE datetime;
set @CHANGEDATE = getdate();
/* Already adjusted */
if ( select count(GIFTFEEADJUSTMENT.ID)
from dbo.GIFTFEEADJUSTMENT
where
GIFTFEEADJUSTMENT.REVENUEID = @ID and
GIFTFEEADJUSTMENT.POSTSTATUSCODE = 1) > 0
begin
set @ADJUST = 1;
set @ADJUSTMENTEXISTS = 1;
end
/* distributions Changed */
if @ADJUST = 0
set @ADJUST = dbo.UFN_CHECKDETAIL_GIFTFEEDISTRIBUTIONCHANGED(@ID, @GLDISTRIBUTION)
-- GLPAYMENTMETHODREVENUETYPEMAPPINGID and REFERENCE don't trigger an adjustment so if they changed,
-- but the fields that trigger an adjustment didn't, throw an error. We already know the row counts
-- match because of UFN_CHECKDETAIL_GIFTFEEDISTRIBUTIONCHANGED so we just need to see if the number
-- of rows that match equals the total number of rows.
if @ADJUST = 0
begin
declare @MATCHROWCOUNT integer;
select @MATCHROWCOUNT = count(NEWDISTRIBUTION.ID)
from dbo.UFN_REVENUE_GETGIFTFEEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) as NEWDISTRIBUTION
inner join dbo.GIFTFEEGLDISTRIBUTION OLDDISTRIBUTION on
(
NEWDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = OLDDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID or
(
NEWDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null and OLDDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null
)
) and
coalesce(NEWDISTRIBUTION.REFERENCE, '') = coalesce(OLDDISTRIBUTION.REFERENCE, '')
where REVENUEID = @ID and OUTDATED = 0
if @MATCHROWCOUNT <> (select count(*) from dbo.UFN_REVENUE_GETGIFTFEEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION))
raiserror('BBERR_ONLYREFERENCEORTYPEMAPPINGCHANGED', 13, 1);
end
if @ADJUST = 1
begin
if @ADJUSTMENTREASONCODEID is null
raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1);
-- update the payment information for each revenue record in the transaction
set @ADJUSTMENTID = null;
exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @CONSTITUENTID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
@ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID,
@CONSTITUENTID = REVENUE.CONSTITUENTID
from
dbo.REVENUE
where
REVENUE.ID = @ID;
--Set the currency values in the GLDISTRIBUTION collection.
select @GLDISTRIBUTION = dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML
(
@GLDISTRIBUTION,
@BASECURRENCYID,
@ORGANIZATIONEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
@ORGANIZATIONEXCHANGERATEID,
@BASEEXCHANGERATEID
)
-- If an unposted adjustment already exists, update the revenue GL distribution rows.
-- Otherwise, insert new revenue gl distribution rows using @GLDISTRIBUTION.
if @ADJUSTMENTEXISTS = 1
begin
exec dbo.USP_REVENUE_GETGIFTFEEPOSTEDGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2 @ID, @GLDISTRIBUTION, @ADJUSTMENTPOSTDATE, @CHANGEAGENTID, @CHANGEDATE;
end
else
begin
declare @JOURNAL nvarchar(50);
declare @PDACCOUNTSYSTEMID uniqueidentifier;
set @JOURNAL = 'Blackbaud Enterprise';
select @PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
from dbo.FINANCIALTRANSACTION FT
where FT.ID = @ID;
set @CHANGEDATE = getdate();
declare @DISTRIBUTIONS table(
GLTRANSACTIONID uniqueidentifier,
ACCOUNT nvarchar(100),
AMOUNT money,
REFERENCE nvarchar(255),
TRANSACTIONTYPECODE tinyint,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
GLACCOUNTID uniqueidentifier,
FINANCIALTRANSACTIONLINEITEMID uniqueidentifier
);
-- Get the user-defined GL distributions for the revenue record.
insert into @DISTRIBUTIONS(
GLTRANSACTIONID
,ACCOUNT
,AMOUNT
,REFERENCE
,TRANSACTIONTYPECODE
,GLPAYMENTMETHODREVENUETYPEMAPPINGID
,TRANSACTIONAMOUNT
,TRANSACTIONCURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONAMOUNT
,ORGANIZATIONEXCHANGERATEID
,GLACCOUNTID
,FINANCIALTRANSACTIONLINEITEMID)
select
newid(),
REVDISTRIBUTIONS.ACCOUNT,
REVDISTRIBUTIONS.AMOUNT,
REVDISTRIBUTIONS.REFERENCE,
REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
REVDISTRIBUTIONS.BASEEXCHANGERATEID,
REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
A.ID,
isnull(LI.REVERSEDLINEITEMID, LI.ID)
from dbo.UFN_REVENUE_GETGIFTFEEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) REVDISTRIBUTIONS
inner join dbo.GLACCOUNT A on A.ACCOUNTNUMBER = REVDISTRIBUTIONS.ACCOUNT and A.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
left join dbo.JOURNALENTRY_EXT JEX on JEX.DISTRIBUTIONTABLEID = REVDISTRIBUTIONS.ID
left join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
where
LI.DELETEDON is not null
if exists(select 1 from @DISTRIBUTIONS where FINANCIALTRANSACTIONLINEITEMID is null)
begin
declare @LIID uniqueidentifier = NEWID();
insert into dbo.FINANCIALTRANSACTIONLINEITEM (
ID
,FINANCIALTRANSACTIONID
,TRANSACTIONAMOUNT
,VISIBLE
,DESCRIPTION
,SEQUENCE
,TYPECODE
,POSTDATE
,POSTSTATUSCODE
,BASEAMOUNT
,ORGAMOUNT
,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values (
@LIID
,@ID
,0
,0
,'Unattached gif fee distributions'
,0
,7
,@ADJUSTMENTPOSTDATE
,1
,0
,0
,@ADJUSTMENTID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
)
update @DISTRIBUTIONS set
FINANCIALTRANSACTIONLINEITEMID = @LIID
where FINANCIALTRANSACTIONLINEITEMID is null;
end
insert into dbo.JOURNALENTRY (
ID
,FINANCIALTRANSACTIONLINEITEMID
,TRANSACTIONTYPECODE
,SUBLEDGERTYPECODE
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,COMMENT
,POSTDATE
,GLACCOUNTID
,TYPECODE
,TRANSACTIONCURRENCYID
,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
D.GLTRANSACTIONID
,D.FINANCIALTRANSACTIONLINEITEMID
,D.TRANSACTIONTYPECODE
,D.TRANSACTIONTYPECODE
,D.TRANSACTIONAMOUNT
,D.AMOUNT
,D.ORGANIZATIONAMOUNT
,D.REFERENCE
,@ADJUSTMENTPOSTDATE
,D.GLACCOUNTID
,0
,D.TRANSACTIONCURRENCYID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTIONS D;
insert into dbo.JOURNALENTRY_EXT (
ID
,DISTRIBUTIONTABLEID
,TABLENAMECODE
,LOGICALREVENUEID
,GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JOURNAL
,ACCOUNT
,PRECALCORGANIZATIONEXCHANGERATEID
,PRECALCBASEEXCHANGERATEID
,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
D.GLTRANSACTIONID
,D.GLTRANSACTIONID
,8 -- GIFTFEEGLDISTRIBUTION
,@ID
,D.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,@JOURNAL
,D.ACCOUNT
,D.ORGANIZATIONEXCHANGERATEID
,D.BASEEXCHANGERATEID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTIONS D;
end
if not (@ADJUSTMENTID is null)
begin
if not exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT where ID = @ADJUSTMENTID)
insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
ID
,ADJUSTMENTREASONCODEID
,REASON
,DATE
,CONSTITUENTID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(
@ADJUSTMENTID
,@ADJUSTMENTREASONCODEID
,@ADJUSTMENTREASON
,@ADJUSTMENTDATE
,@CONSTITUENTID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
update LI set
FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CHANGEDAtE
from dbo.FINANCIALTRANSACTIONLINEITEM LI
where LI.FINANCIALTRANSACTIONID = @ID and LI.TYPECODE = 7 and LI.DELETEDON is null and LI.POSTSTATUSCODE = 1;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;