USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTPAYOUTGLDISTRIBUTIONADJUST
The save procedure used by the edit dataform template "Posted Planned Gift Payout 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 | Payout 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_PLANNEDGIFTPAYOUTGLDISTRIBUTIONADJUST
(
@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 @ADJUST bit;
declare @ADJUSTMENTEXISTS bit;
declare @PLANNEDGIFTPAYOUTID uniqueidentifier;
select @PLANNEDGIFTPAYOUTID = ID from PLANNEDGIFTPAYOUT where REVENUEID = @ID
set @ADJUST = 0
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if (dbo.UFN_GLDISTRIBUTION_DEBITSEQUALCREDITS(@GLDISTRIBUTION) = 0)
raiserror('The sum of the debit accounts must equal the sum of the credit accounts.', 13, 1)
--if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS(@GLDISTRIBUTION) = 0)
-- raiserror('One or more of the edited accounts do not exist.', 13, 1)
if (dbo.UFN_GLDISTRIBUTION_ACCOUNTEXISTS_2(@GLDISTRIBUTION,@ID) = 0)
raiserror('One or more of the edited accounts do not exist.', 13, 1)
declare @CHANGEDATE datetime;
set @CHANGEDATE = getdate();
select top 1
@PAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID;
/* Already adjusted */
if (select COUNT(ID)
from dbo.PLANNEDGIFTPAYOUTADJUSTMENT
where REVENUEID = @ID and POSTSTATUSCODE = 1) > 0
begin
set @ADJUST = 1;
set @ADJUSTMENTEXISTS = 1;
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_PLANNEDGIFTPAYOUTADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
@ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID
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_PLANNEDGIFTPAYOUT_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
);
-- 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)
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
from
dbo.UFN_PLANNEDGIFTPAYOUT_GETGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) REVDISTRIBUTIONS
-- Insert rows in GLTRANSACTION table
insert into dbo.GLTRANSACTION
(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,GLACCOUNTID)
select
GLTRANSACTIONID,
TRANSACTIONTYPECODE,
ACCOUNT,
AMOUNT,
PROJECT,
REFERENCE,
@ADJUSTMENTPOSTDATE,
@JOURNAL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE([ACCOUNT],@ID)
from
@DISTRIBUTIONS;
insert into dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION
(ID, PLANNEDGIFTPAYOUTID, REVENUEID,GLTRANSACTIONID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,PROJECT,REFERENCE,AMOUNT,ACCOUNT,TRANSACTIONTYPECODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
select
newid(),
@PLANNEDGIFTPAYOUTID,
@ID,
GLTRANSACTIONID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
PROJECT,
REFERENCE,
AMOUNT,
ACCOUNT,
TRANSACTIONTYPECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID
from
@DISTRIBUTIONS;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;