USP_DATAFORMTEMPLATE_EDIT_REVENUEGLDISTRIBUTIONADJUST_2
The save procedure used by the edit dataform template "Posted Revenue GL Distribution Edit Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @GLDISTRIBUTION | xml | IN | Revenue GL distribution |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjustment 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_REVENUEGLDISTRIBUTIONADJUST_2
(
@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 @PAYMENTMETHODCODE tinyint;
declare @AUCTIONPURCHASE bit = 0;
declare @ADJUST bit;
declare @ADJUSTMENTEXISTS bit;
declare @ADJUSTORIGINALPOSTSTATUS tinyint;
set @ADJUST = 0
set @ADJUSTORIGINALPOSTSTATUS = 0;
if exists(select 1 from FINANCIALTRANSACTIONLINEITEM T1 join REVENUESPLIT_EXT T2 on T1.ID = T2.ID where T2.TYPECODE = 12 and T2.APPLICATIONCODE = 12 and T1.FINANCIALTRANSACTIONID = @ID)
set @AUCTIONPURCHASE = 1;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS_2(@GLDISTRIBUTION,@ID) = 0)
raiserror('BBERR_ACCOUNTDOESNOTEXIST', 13, 1);
/* For payments that are linked to deposits there are limitation for what you can change
about the debit account for the gl distribution.
- If the deposit is linked to a bank account that has a cash account then you can
not change anything about the debit account for the gl distribution.
- If the deposit is linked to a bank account that has a cash code then you can
change anything about the debit account except for the account code for the gl distribution.
*/
if exists(select * from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @ID and DEPOSITID is not null)
begin
declare @ORIGINALADJUSTMENTPOSTDATE datetime
select @ORIGINALADJUSTMENTPOSTDATE = POSTDATE
from dbo.BANKACCOUNTDEPOSITPAYMENT
inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
where BANKACCOUNTDEPOSITPAYMENT.ID = @ID;
if @ORIGINALADJUSTMENTPOSTDATE <> @ADJUSTMENTPOSTDATE
update dbo.BANKACCOUNTDEPOSITPAYMENT set DEPOSITID = null where ID = @ID;
else
begin
declare @ACCOUNT nvarchar(100);
declare @ACCOUNTCODE nvarchar(30);
select
@ACCOUNT = CASE WHEN BANKACCOUNT.GLACCOUNTID IS NOT NULL THEN GLACCOUNT.ACCOUNTNUMBER END,
@ACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
from
dbo.BANKACCOUNTDEPOSITPAYMENT
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
inner join dbo.REVENUE on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
left outer join dbo.GLACCOUNT on BANKACCOUNT.GLACCOUNTID = GLACCOUNT.ID
left outer join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID
where
BANKACCOUNTDEPOSITPAYMENT.ID = @ID and BANKACCOUNT.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID;
if @ACCOUNT is not null
begin
if exists(select * from dbo.UFN_REVENUE_GETGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) where ACCOUNT <> @ACCOUNT and TRANSACTIONTYPECODE = 0)
raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
end
else
begin
declare @BANKCASHACCOUNTS table (
GLACCOUNTID uniqueidentifier,
ACCOUNT nvarchar(100),
GENERATEDACCOUNT nvarchar(100)
);
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.FINANCIALTRANSACTION where ID = @ID
insert into @BANKCASHACCOUNTS (GLACCOUNTID, ACCOUNT, GENERATEDACCOUNT)
select
GLACCOUNT.ID, GLACCOUNT.ACCOUNTNUMBER, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(GLACCOUNT.ID, @ACCOUNTCODE,@PDACCOUNTSYSTEMID)
from
dbo.UFN_REVENUE_GETGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) DIST
inner join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = DIST.ACCOUNT
where
DIST.TRANSACTIONTYPECODE = 0
if exists(select * from @BANKCASHACCOUNTS where ACCOUNT != GENERATEDACCOUNT)
raiserror('ERR_DEBITACCOUNTCODE_MUST_BE_SAME_AS_BANK.', 13, 1)
end
end
end
declare @CHANGEDATE datetime;
set @CHANGEDATE = getdate();
select top 1
@PAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID;
/* Already adjusted */
if (select COUNT(ADJUSTMENT.ID)
from dbo.ADJUSTMENT
where REVENUEID = @ID and POSTSTATUSCODE > 0) > 0
begin
set @ADJUST = 1;
set @ADJUSTMENTEXISTS = 1;
end
/* Check to see if original adjustment was Do not post */
if (select COUNT(ADJUSTMENT.ID)
from dbo.ADJUSTMENT
where REVENUEID = @ID and POSTSTATUSCODE = 2) > 0
begin
set @ADJUSTORIGINALPOSTSTATUS = 2;
end
/* distributions Changed */
if @ADJUST = 0
set @ADJUST = dbo.UFN_CHECKDETAIL_DISTRIBUTIONCHANGED(@ID, @GLDISTRIBUTION)
/* If there was a change to GL related data log an adjustment for each revenue in the transaction */
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_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, default, 2;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @DEPOSITBASEEXCHANGERATEID uniqueidentifier;
declare @DEPOSITORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @DEPOSITID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
--BASECURRENCYID value calculation taken from REVENUE view
@BASECURRENCYID = case when FINANCIALTRANSACTION.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end,
@BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
@ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
@DEPOSITBASEEXCHANGERATEID = case when isnull(BA.TRANSACTIONCURRENCYID, newid()) = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID then T.BASEEXCHANGERATEID else FINANCIALTRANSACTION.BASEEXCHANGERATEID end,
@DEPOSITORGANIZATIONEXCHANGERATEID = case when isnull(BA.TRANSACTIONCURRENCYID, newid()) = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID then T.ORGANIZATIONEXCHANGERATEID else FINANCIALTRANSACTION.ORGEXCHANGERATEID end
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT DP on DP.ID = FINANCIALTRANSACTION.ID
left outer join dbo.BANKACCOUNTTRANSACTION T on T.ID = DP.DEPOSITID
left outer join dbo.BANKACCOUNT BA on BA.ID = T.BANKACCOUNTID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTION.ID = @ID;
--Set the currency values in the GLDISTRIBUTION collection.
select @GLDISTRIBUTION = dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML
(
@GLDISTRIBUTION,
@BASECURRENCYID,
@DEPOSITORGANIZATIONEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@DEPOSITBASEEXCHANGERATEID,
@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
delete from dbo.GLTRANSACTION
where GLTRANSACTION.ID in (select REVENUEGLDISTRIBUTION.GLTRANSACTIONID
from dbo.REVENUEGLDISTRIBUTION
where REVENUEGLDISTRIBUTION.REVENUEID = @ID and REVENUEGLDISTRIBUTION.OUTDATED = 0)
and GLTRANSACTION.SYSTEMDISTRIBUTION = 1;
delete from dbo.DEPOSITGLDISTRIBUTIONLINK
where DEPOSITGLDISTRIBUTIONLINK.ID in (select REVENUEGLDISTRIBUTION.GLTRANSACTIONID
from dbo.REVENUEGLDISTRIBUTION
where REVENUEGLDISTRIBUTION.REVENUEID = @ID and REVENUEGLDISTRIBUTION.OUTDATED = 0)
exec dbo.USP_REVENUE_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2 @ID, @GLDISTRIBUTION, @ADJUSTMENTPOSTDATE, @CHANGEAGENTID, @CHANGEDATE;
end
else
begin
declare @JOURNAL nvarchar(50);
set @JOURNAL = 'Blackbaud Enterprise';
set @CHANGEDATE = getdate();
declare @DISTRIBUTIONS table(
GLTRANSACTIONID uniqueidentifier,
ACCOUNT nvarchar(100),
AMOUNT money,
PROJECT nvarchar(100),
REFERENCE nvarchar(255),
TRANSACTIONTYPECODE tinyint,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
FINANCIALTRANSACTIONLINEITEMID uniqueidentifier
);
-- Get the user-defined GL distributions for the revenue record.
insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,
FINANCIALTRANSACTIONLINEITEMID)
select
newid(),
REVDISTRIBUTIONS.ACCOUNT,
REVDISTRIBUTIONS.AMOUNT,
REVDISTRIBUTIONS.PROJECT,
REVDISTRIBUTIONS.REFERENCE,
REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
REVDISTRIBUTIONS.BASECURRENCYID,
REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
REVDISTRIBUTIONS.BASEEXCHANGERATEID,
REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
LI.REVERSEDLINEITEMID
from
dbo.UFN_REVENUE_GETGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) REVDISTRIBUTIONS
left join dbo.JOURNALENTRY JE on JE.ID = REVDISTRIBUTIONS.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID;
if exists(select 1 from @DISTRIBUTIONS where FINANCIALTRANSACTIONLINEITEMID is null)
begin
declare @FTLIID uniqueidentifier;
select top 1 @FTLIID = ID from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.FINANCIALTRANSACTIONID = @ID and LI.POSTSTATUSCODE = 1 and LI.TYPECODE in (0, 98) and LI.DELETEDON is null;
if @FTLIID is null
begin
set @FTLIID = NEWID();
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(
ID
,FINANCIALTRANSACTIONID
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,VISIBLE
,[DESCRIPTION]
,SEQUENCE
,TYPECODE
,POSTSTATUSCODE
,POSTDATE
,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
-- Boilerplate
,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
)
select
@FTLIID
,@ID
,0 AMOUNT
,0 BASEAMOUNT
,0 ORGAMOUNT
,0 VISIBLE
,'' [DESCRIPTION]
,0 SEQUENCE
,98 TYPECODE -- combined
,1 --Not posted
,@ADJUSTMENTPOSTDATE
,@ADJUSTMENTID
-- Boilerplate
,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE;
end
update @DISTRIBUTIONS set FINANCIALTRANSACTIONLINEITEMID = @FTLIID where FINANCIALTRANSACTIONLINEITEMID is null;
end
insert into dbo.JOURNALENTRY
(ID,FINANCIALTRANSACTIONLINEITEMID,TRANSACTIONTYPECODE,SUBLEDGERTYPECODE, TRANSACTIONAMOUNT, BASEAMOUNT,ORGAMOUNT,COMMENT,POSTDATE,GLACCOUNTID,SEQUENCE ,TYPECODE,TRANSACTIONCURRENCYID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
source.GLTRANSACTIONID
,source.FINANCIALTRANSACTIONLINEITEMID
,source.TRANSACTIONTYPECODE
,source.TRANSACTIONTYPECODE
,source.TRANSACTIONAMOUNT
,source.AMOUNT
,source.ORGANIZATIONAMOUNT
,source.REFERENCE
,@ADJUSTMENTPOSTDATE
,dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE(source.ACCOUNT,@ID)
,0
,case when source.TRANSACTIONCURRENCYID is null then 1 else 0 end
,source.TRANSACTIONCURRENCYID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTIONS as source
insert into dbo.JOURNALENTRY_EXT
(ID,PROJECT,ACCOUNT,JOURNAL,TABLENAMECODE,PRECALCORGANIZATIONEXCHANGERATEID,PRECALCBASEEXCHANGERATEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,DISTRIBUTIONTABLEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
source.GLTRANSACTIONID
,source.PROJECT
,source.ACCOUNT
,@JOURNAL
,1 -- REVENUEGLDISTRIBUTION table
,source.ORGANIZATIONEXCHANGERATEID
,source.BASEEXCHANGERATEID
,source.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,source.GLTRANSACTIONID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTIONS as source
end
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID;
if @DEPOSITID is not null
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEDATE;
end
if @ADJUSTORIGINALPOSTSTATUS = 2
begin
update GLTRANSACTION set POSTSTATUSCODE = 1
where REVERSEDGLTRANSACTIONID in
(select GLTRANSACTIONID from dbo.REVENUEGLDISTRIBUTION
where REVENUEID = @ID and OUTDATED = 1)
update GLTRANSACTION set POSTSTATUSCODE = 1
where ID in
(select GLTRANSACTIONID from dbo.REVENUEGLDISTRIBUTION
where REVENUEID = @ID and OUTDATED = 0)
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;