USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION
Saves auction purchase GL distributions when a payment is added.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure [dbo].[USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION]
(
@REVENUEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
begin
set nocount on;
if not exists(select 1 from dbo.REVENUESPLIT where REVENUEID = @REVENUEID and APPLICATIONCODE = 12 and TYPECODE = 12)
return;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
if @PDACCOUNTSYSTEMID is null
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID
from dbo.REVENUE inner join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
where REVENUE.ID = @REVENUEID
declare @CUSTOMIZED integer = 0;
exec @CUSTOMIZED = dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE,@PDACCOUNTSYSTEMID
if @CUSTOMIZED = 0
begin
declare @DISTRIBUTIONS table(
GLTRANSACTIONID uniqueidentifier,
ACCOUNT nvarchar(100),
AMOUNT money,
PROJECT nvarchar(100),
REFERENCE nvarchar(255),
TRANSACTIONTYPECODE tinyint,
POSTDATE datetime,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
ACCOUNTID uniqueidentifier,
REVENUEID uniqueidentifier,
REVENUESPLITTYPECODE tinyint,
POSTSTATUSCODE tinyint,
AUCTIONITEMPOSTSTATUSCODE tinyint,
REVENUEPURCHASEID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
ERRORMESSAGE nvarchar(max),
MAPPEDVALUES xml,
SYSTEMDISTRIBUTION bit default 0,
REVENUESPLITID uniqueidentifier
);
declare @JOURNAL nvarchar(50);
set @JOURNAL = 'Blackbaud Enterprise';
-- Generate GL distributions for the auction purchase record.
insert into @DISTRIBUTIONS(
GLTRANSACTIONID,
ACCOUNT,
AMOUNT,
PROJECT,
REFERENCE,
TRANSACTIONTYPECODE,
POSTDATE,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
ACCOUNTID,
REVENUEID,
REVENUESPLITTYPECODE,
POSTSTATUSCODE,
AUCTIONITEMPOSTSTATUSCODE,
REVENUEPURCHASEID,
BASECURRENCYID,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
ERRORMESSAGE,
MAPPEDVALUES,
REVENUESPLITID
)
select
newid(),
DISTRIBUTIONS.ACCOUNTSTRING,
DISTRIBUTIONS.AMOUNT,
DISTRIBUTIONS.PROJECT,
DISTRIBUTIONS.REFERENCE,
DISTRIBUTIONS.TRANSACTIONTYPECODE,
DISTRIBUTIONS.POSTDATE,
DISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
DISTRIBUTIONS.ACCOUNTID,
DISTRIBUTIONS.REVENUEID,
DISTRIBUTIONS.REVENUESPLITTYPECODE,
1,--DISTRIBUTIONS.POSTSTATUSCODE,
DISTRIBUTIONS.AUCTIONITEMPOSTSTATUSCODE,
DISTRIBUTIONS.REVENUEPURCHASEID,
DISTRIBUTIONS.BASECURRENCYID,
DISTRIBUTIONS.TRANSACTIONCURRENCYID,
DISTRIBUTIONS.BASEEXCHANGERATEID,
DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
DISTRIBUTIONS.TRANSACTIONAMOUNT,
DISTRIBUTIONS.ORGANIZATIONAMOUNT,
DISTRIBUTIONS.ERRORMESSAGE,
DISTRIBUTIONS.MAPPEDVALUES,
DISTRIBUTIONS.REVENUESPLITID
from
dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2(@REVENUEID,null) as DISTRIBUTIONS
declare @ERRORMESSAGE nvarchar(max);
select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where ACCOUNTID is null
if @ERRORMESSAGE <> ''
raiserror('%s', 13, 1, @ERRORMESSAGE);
else
insert into dbo.GLACCOUNTMAPPINGERROR (
[TRANSACTIONID]
,[TRANSACTIONTYPECODE]
,[ERRORMESSAGE]
,[MAPPEDVALUES]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED])
select distinct
@REVENUEID
,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @REVENUEID)
,D.ERRORMESSAGE
,convert(varchar(max),D.MAPPEDVALUES)
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CHANGEDATE
,@CHANGEDATE
from @DISTRIBUTIONS D
where nullif(D.ERRORMESSAGE,'') is not null
and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @REVENUEID and DELETED = 0)
if (select count(*) from @DISTRIBUTIONS) > 0
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
declare @Cache table (HashCode uniqueidentifier, ErrorMessage nvarchar(max), GLAccountID uniqueidentifier, TransactionTypeCode tinyint, ErrorNumber tinyint, MappedValues nvarchar(max), PDAccountSystemID uniqueidentifier)
insert into @Cache(HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID)
select
v.MD5HASHCODE
,d.ERRORMESSAGE
,d.ACCOUNTID
,d.TRANSACTIONTYPECODE
,v.ERRORCODE
,v.MAPPEDVALUES
,v.PDACCOUNTSYSTEMID
from @DISTRIBUTIONS d
cross apply dbo.UFN_PDACCOUNTLOOKUPCACHE_PARSE_MAPPEDVALUES(MAPPEDVALUES) v
where v.MD5HASHCODE is not null
insert into dbo.PDACCOUNTLOOKUPCACHE (MD5HASHCODE, ERRORMESSAGE, GLACCOUNTID, TRANSACTIONTYPECODE, ERRORNUMBER, MAPPEDVALUES, PDACCOUNTSYSTEMID)
select HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID
from @Cache tt
where not exists (select MD5HASHCODE from dbo.PDACCOUNTLOOKUPCACHE with (NOLOCK) where MD5HASHCODE = tt.HashCode and TRANSACTIONTYPECODE = tt.TransactionTypeCode and MAPPEDVALUES = tt.MappedValues)
if (select count(*) from @DISTRIBUTIONS) > 0
begin
declare @AUCTIONITEMDONATIONID uniqueidentifier;
declare @AUCTIONITEMPOSTSTATUSCODE tinyint;
declare @PAYMENTPOSTSTATUSCODE tinyint;
declare @REVENUEPURCHASEID uniqueidentifier;
declare @ADJUSTMENTDATE datetime;
set @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)
end
--Create a cursor to go through all of the auction items that received a payment with this revenue
declare AUCTIONITEM_CURSOR cursor local fast_forward
for
select distinct
DISTRIBUTIONS.REVENUEID,
DISTRIBUTIONS.AUCTIONITEMPOSTSTATUSCODE,
DISTRIBUTIONS.POSTSTATUSCODE,
DISTRIBUTIONS.REVENUEPURCHASEID
from
@DISTRIBUTIONS DISTRIBUTIONS
inner join dbo.AUCTIONITEM on
AUCTIONITEM.REVENUEAUCTIONDONATIONID = DISTRIBUTIONS.REVENUEID
open AUCTIONITEM_CURSOR
fetch next from AUCTIONITEM_CURSOR into
@AUCTIONITEMDONATIONID,
@AUCTIONITEMPOSTSTATUSCODE,
@PAYMENTPOSTSTATUSCODE,
@REVENUEPURCHASEID
while @@FETCH_STATUS = 0
begin
--Previously we only added/updated distributions if the original auction
-- item donation was not set to DONOTPOST and the payment was not set to DONOTPOST.
-- However, we now only check the auction purchase payment post status; per
-- Bug 285104 we now know the auction donation post status should not impact
-- auction purchase GL distributions (in the same way pledge post status does not
-- impact pledge payment GL distributions).
if @PAYMENTPOSTSTATUSCODE <> 2
begin
----Handle Old Distributions----
declare @GLTRANSACTIONSTODELETE table (GLTRANSACTIONID uniqueidentifier,AUCTIONPURCHASEGLDISTRIBUTIONID uniqueidentifier);
insert into @GLTRANSACTIONSTODELETE (GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTIONID)
select AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTION.ID from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
left join dbo.AUCTIONITEMPURCHASE on
AUCTIONITEMPURCHASE.PURCHASEID = AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID
left join dbo.GLTRANSACTION on
AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @REVENUEPURCHASEID and
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEID = @AUCTIONITEMDONATIONID and
GLTRANSACTION.POSTSTATUSCODE = 1
delete
dbo.AUCTIONPURCHASEGLDISTRIBUTION
where
AUCTIONPURCHASEGLDISTRIBUTION.ID in (select AUCTIONPURCHASEGLDISTRIBUTIONID from @GLTRANSACTIONSTODELETE)
--Delete the GL Transactions
delete
dbo.GLTRANSACTION
where
GLTRANSACTION.ID in (select GLTRANSACTIONID from @GLTRANSACTIONSTODELETE)
--Determine if any posted distributions exist
if((select
count(*)
from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
left join dbo.GLTRANSACTION on
AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @REVENUEPURCHASEID and
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEID = @AUCTIONITEMDONATIONID and
GLTRANSACTION.POSTSTATUSCODE = 0)
> 0)
begin
exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT
@REVENUEID = @REVENUEID,
@DATE = @ADJUSTMENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@CHANGEDATE = @CHANGEDATE,
@POSTDATE = @ADJUSTMENTDATE
end
--Insert New Distributions----
declare @ORGDECIMALDIGITS tinyint;
declare @ORGROUNDINGTYPECODE tinyint;
select
@ORGDECIMALDIGITS = ORGCURRENCY.DECIMALDIGITS,
@ORGROUNDINGTYPECODE = ORGCURRENCY.ROUNDINGTYPECODE
--from @DISTRIBUTIONS DISTRIBUTION
--inner join dbo.CURRENCY ORGCURRENCY on ORGCURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
from dbo.CURRENCY ORGCURRENCY
where ORGCURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, BASEAMOUNT, COMMENT,
POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, ORGAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, TYPECODE, SUBLEDGERTYPECODE)
select GLTRANSACTIONID, REVENUESPLITID, TRANSACTIONTYPECODE, AMOUNT, REFERENCE,
POSTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, ACCOUNTID, ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, case when SYSTEMDISTRIBUTION = 1 then 1 else 0 end, TRANSACTIONTYPECODE
from @DISTRIBUTIONS
where
REVENUEPURCHASEID = @REVENUEPURCHASEID and
REVENUEID = @AUCTIONITEMDONATIONID
and
(AMOUNT != 0 or TRANSACTIONAMOUNT != 0 or
(dbo.UFN_AUCTIONPACKAGE_CURRENCY_ROUND(ORGANIZATIONAMOUNT, @ORGDECIMALDIGITS, @ORGROUNDINGTYPECODE) != 0));
insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCPOSTSTATUSCODE, PRECALCORGANIZATIONEXCHANGERATEID,
PRECALCBASEEXCHANGERATEID, ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEPURCHASEID)
select GLTRANSACTIONID,GLTRANSACTIONID,2,PROJECT, @JOURNAL, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, POSTSTATUSCODE, ORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID, ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, @REVENUEPURCHASEID
from @DISTRIBUTIONS
where
REVENUEPURCHASEID = @REVENUEPURCHASEID and
REVENUEID = @AUCTIONITEMDONATIONID
and
(AMOUNT != 0 or TRANSACTIONAMOUNT != 0 or
(dbo.UFN_AUCTIONPACKAGE_CURRENCY_ROUND(ORGANIZATIONAMOUNT, @ORGDECIMALDIGITS, @ORGROUNDINGTYPECODE) != 0));
end
fetch next from AUCTIONITEM_CURSOR into
@AUCTIONITEMDONATIONID,
@AUCTIONITEMPOSTSTATUSCODE,
@PAYMENTPOSTSTATUSCODE,
@REVENUEPURCHASEID
end
close AUCTIONITEM_CURSOR;
deallocate AUCTIONITEM_CURSOR;
--Save the realized gain/loss on the currency exchange rates
exec dbo.USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION @REVENUEID, @PDACCOUNTSYSTEMID, @CHANGEAGENTID;
end
end