USP_AUCTIONPURCHASEDELETE_UPDATEGLDISTRIBUTION
This function is used to update auction purchase GL distributions when a payment is deleted.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_AUCTIONPURCHASEDELETE_UPDATEGLDISTRIBUTION
(
@REVENUEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
begin
set nocount on;
if not exists(select 1 from dbo.REVENUESPLIT where REVENUEID = @REVENUEID and APPLICATIONCODE = 12 and TYPECODE = 12)
return;
declare @PAYMENTPOSTSTATUSCODE tinyint;
select @PAYMENTPOSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
from dbo.REVENUE left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID where REVENUE.ID = @REVENUEID
if @PAYMENTPOSTSTATUSCODE = 2 --Do Not Post
return; --If this payment was set to Do Not Post, there should not be any distributions affected by deleting it
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @ERRORMESSAGE nvarchar(max);
declare @JOURNAL nvarchar(50);
set @JOURNAL = 'Blackbaud Enterprise';
declare @ADJUSTMENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)
declare @GLTRANSACTIONSTODELETE table (GLTRANSACTIONID uniqueidentifier,AUCTIONPURCHASEGLDISTRIBUTIONID uniqueidentifier);
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
);
-- 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)
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
from
dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2(@REVENUEID,@REVENUEID) as DISTRIBUTIONS
if @@rowcount > 0
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where nullif(ERRORMESSAGE,'') is not null and ACCOUNTID is null;
if nullif(@ERRORMESSAGE,'') is not null
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)
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)
declare @AFFECTEDAUCTIONITEMS table
(
AUCTIONITEMID uniqueidentifier,
REVENUEAUCTIONDONATIONID uniqueidentifier,
DONOTPOST bit
)
insert into @AFFECTEDAUCTIONITEMS
select distinct AUCTIONITEM.ID,AUCTIONITEM.REVENUEAUCTIONDONATIONID,REVENUE.DONOTPOST
from dbo.AUCTIONITEM inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID inner join dbo.REVENUE on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID
where AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = @REVENUEID
--Get the affected revenue (payments that paid towards items that were changed in the current payment)
declare @AFFECTEDREVENUE table
(
REVENUEPURCHASEID uniqueidentifier,
POSTSTATUSCODE tinyint
)
insert into @AFFECTEDREVENUE(REVENUEPURCHASEID,POSTSTATUSCODE)
(
select @REVENUEID,@PAYMENTPOSTSTATUSCODE
union all
select distinct
REVENUE.ID,
case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
from @AFFECTEDAUCTIONITEMS AFFECTEDAUCTIONITEMS
inner join dbo.AUCTIONITEMREVENUEPURCHASE on AFFECTEDAUCTIONITEMS.AUCTIONITEMID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
inner join dbo.REVENUE on REVENUE.ID = AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID
where AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID <> @REVENUEID
)
declare @PAYMENTCURSOR_REVENUEPURCHASEID uniqueidentifier,@PAYMENTCURSOR_POSTSTATUSCODE tinyint;
declare PAYMENTCURSOR cursor local fast_forward
for select REVENUEPURCHASEID,POSTSTATUSCODE from @AFFECTEDREVENUE
open PAYMENTCURSOR
fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEPURCHASEID, @PAYMENTCURSOR_POSTSTATUSCODE
while @@FETCH_STATUS = 0
begin
--delete @DISTRIBUTIONS
delete @GLTRANSACTIONSTODELETE
--Delete all distributions not posted
/* delete dbo.AUCTIONPURCHASEGLDISTRIBUTION
output DELETED.GLTRANSACTIONID into @GLTRANSACTIONSTODELETE
from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
left join dbo.GLTRANSACTION on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and
GLTRANSACTION.POSTSTATUSCODE <> 0;
*/
insert into @GLTRANSACTIONSTODELETE (GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTIONID)
select AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID ,AUCTIONPURCHASEGLDISTRIBUTION.ID from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
left join dbo.GLTRANSACTION on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and
GLTRANSACTION.POSTSTATUSCODE <> 0;
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)
if((select
count(*)
from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
left join dbo.GLTRANSACTION on
AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID and
GLTRANSACTION.POSTSTATUSCODE = 0)
> 0)
begin
exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT
@REVENUEID = @PAYMENTCURSOR_REVENUEPURCHASEID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CHANGEDATE = @CHANGEDATE,
@DATE = @ADJUSTMENTDATE,
@POSTDATE = @ADJUSTMENTDATE
end
if @PAYMENTCURSOR_POSTSTATUSCODE <> 2 and @PAYMENTCURSOR_REVENUEPURCHASEID <> @REVENUEID
begin
----Insert New Distributions----
insert into dbo.GLTRANSACTION
(
ID,
TRANSACTIONTYPECODE,
ACCOUNT,
AMOUNT,PROJECT,
REFERENCE,
POSTDATE,
POSTSTATUSCODE,
JOURNAL,
GLACCOUNTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID
)
select
DISTRIBUTIONS.GLTRANSACTIONID,
DISTRIBUTIONS.TRANSACTIONTYPECODE,
DISTRIBUTIONS.ACCOUNT,
DISTRIBUTIONS.AMOUNT,
DISTRIBUTIONS.PROJECT,
DISTRIBUTIONS.REFERENCE,
DISTRIBUTIONS.POSTDATE,
DISTRIBUTIONS.POSTSTATUSCODE,
@JOURNAL,
DISTRIBUTIONS.ACCOUNTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
DISTRIBUTIONS.BASECURRENCYID,
DISTRIBUTIONS.TRANSACTIONAMOUNT,
DISTRIBUTIONS.TRANSACTIONCURRENCYID,
DISTRIBUTIONS.BASEEXCHANGERATEID,
DISTRIBUTIONS.ORGANIZATIONAMOUNT,
DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID
from
@DISTRIBUTIONS DISTRIBUTIONS
where
DISTRIBUTIONS.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
insert into dbo.AUCTIONPURCHASEGLDISTRIBUTION
(
ID,
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
PROJECT,
REFERENCE,
AMOUNT,
ACCOUNT,
TRANSACTIONTYPECODE,
GLTRANSACTIONID,
REVENUEPURCHASEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT
)
select
newid(),
DISTRIBUTIONS.REVENUEID,
DISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
DISTRIBUTIONS.PROJECT,
DISTRIBUTIONS.REFERENCE,
DISTRIBUTIONS.AMOUNT,
DISTRIBUTIONS.ACCOUNT,
DISTRIBUTIONS.TRANSACTIONTYPECODE,
DISTRIBUTIONS.GLTRANSACTIONID,
DISTRIBUTIONS.REVENUEPURCHASEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
DISTRIBUTIONS.BASECURRENCYID,
DISTRIBUTIONS.TRANSACTIONCURRENCYID,
DISTRIBUTIONS.BASEEXCHANGERATEID,
DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
DISTRIBUTIONS.TRANSACTIONAMOUNT,
DISTRIBUTIONS.ORGANIZATIONAMOUNT
from @DISTRIBUTIONS DISTRIBUTIONS
where DISTRIBUTIONS.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
end
fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEPURCHASEID, @PAYMENTCURSOR_POSTSTATUSCODE
end
close PAYMENTCURSOR;
deallocate PAYMENTCURSOR;
end