USP_SAVE_BENEFITGLDISTRIBUTION_FULLYPAID
Adds distribution for benefit of type fully paid.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FULLYPAID
(
@REVENUEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @JOURNAL nvarchar(50);
set @JOURNAL = 'Blackbaud Enterprise';
declare @CUSTOMIZED integer = 0;
exec @CUSTOMIZED = dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FULLYPAID_CUSTOMIZE @REVENUEID, @CHANGEAGENTID, @CHANGEDATE
if @CUSTOMIZED = 0
begin
declare @REVENUESPLITID uniqueidentifier
select top 1 @REVENUESPLITID = ID from REVENUESPLIT where REVENUEID = @REVENUEID
declare @DISTRIBUTIONS table(
GLTRANSACTIONID uniqueidentifier,
REVENUEBENEFITID uniqueidentifier,
ACCOUNT nvarchar(100),
AMOUNT money,
PROJECT nvarchar(100),
REFERENCE nvarchar(255),
TRANSACTIONTYPECODE tinyint,
POSTDATE datetime,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
ACCOUNTID uniqueidentifier,
ERRORMESSAGE nvarchar(max),
REVENUESPLITID uniqueidentifier,
FULLYPAIDSTATUS tinyint,
BASECURRENCYID uniqueidentifier,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
BENEFITTYPECODE tinyint,
MAPPEDVALUES xml
);
-- Generate GL distributions for the revenue record.
insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUEBENEFITID,ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,FULLYPAIDSTATUS,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, BENEFITTYPECODE, MAPPEDVALUES)
select
newid(),
REVDISTRIBUTIONS.REVENUEBENEFITID,
REVDISTRIBUTIONS.ACCOUNTSTRING,
REVDISTRIBUTIONS.AMOUNT,
REVDISTRIBUTIONS.PROJECT,
REVDISTRIBUTIONS.REFERENCE,
REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
REVDISTRIBUTIONS.POSTDATE,
REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
REVDISTRIBUTIONS.ACCOUNTID,
REVDISTRIBUTIONS.ERRORMESSAGE,
REVDISTRIBUTIONS.REVENUESPLITID,
1,
REVDISTRIBUTIONS.BASECURRENCYID,
REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
REVDISTRIBUTIONS.BASEEXCHANGERATEID,
REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
REVDISTRIBUTIONS.BENEFITTYPECODE,
REVDISTRIBUTIONS.MAPPEDVALUES
from
(
select
REVENUEBENEFIT.ID as REVENUEBENEFITID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
case when BENEFITADJUSTMENT.ID is null then REVENUE.POSTDATE
else BENEFITADJUSTMENT.POSTDATE
end as POSTDATE,
case when BENEFITADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end) -- same reason as above.
else BENEFITADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
REVENUEBENEFIT.TOTALVALUE as AMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Benefit liability' ,'Pledge') as REFERENCE,
tf.ERRORMESSAGE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUEBENEFIT.BASECURRENCYID,
REVENUEBENEFIT.TRANSACTIONTOTALVALUE as TRANSACTIONAMOUNT,
REVENUEBENEFIT.TRANSACTIONCURRENCYID,
REVENUEBENEFIT.BASEEXCHANGERATEID,
REVENUEBENEFIT.ORGANIZATIONTOTALVALUE as ORGANIZATIONAMOUNT,
REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID,
2 as BENEFITTYPECODE,
tf.MAPPEDVALUES
from dbo.REVENUE
inner join dbo.REVENUEBENEFIT on REVENUE.ID = REVENUEBENEFIT.REVENUEID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.ID = @REVENUESPLITID
left join dbo.BENEFITADJUSTMENT on REVENUE.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE,87, 9, @REVENUEID) as tf
where REVENUE.ID = @REVENUEID and REVENUE.TRANSACTIONTYPECODE = 1 and REVENUEBENEFIT.SENDBENEFIT = 1
) as REVDISTRIBUTIONS
-- Generate GL distributions for the revenue record.
insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUEBENEFITID,ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,FULLYPAIDSTATUS,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, BENEFITTYPECODE, MAPPEDVALUES)
select
newid(),
REVDISTRIBUTIONS.REVENUEBENEFITID,
REVDISTRIBUTIONS.ACCOUNTSTRING,
REVDISTRIBUTIONS.AMOUNT,
REVDISTRIBUTIONS.PROJECT,
REVDISTRIBUTIONS.REFERENCE,
REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
REVDISTRIBUTIONS.POSTDATE,
REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
REVDISTRIBUTIONS.ACCOUNTID,
REVDISTRIBUTIONS.ERRORMESSAGE,
REVDISTRIBUTIONS.REVENUESPLITID,
0,
REVDISTRIBUTIONS.BASECURRENCYID,
REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
REVDISTRIBUTIONS.BASEEXCHANGERATEID,
REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
REVDISTRIBUTIONS.BENEFITTYPECODE,
REVDISTRIBUTIONS.MAPPEDVALUES
from
(
select
REVENUEBENEFIT.ID as REVENUEBENEFITID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
case when BENEFITADJUSTMENT.ID is null then REVENUE.POSTDATE
else BENEFITADJUSTMENT.POSTDATE
end as POSTDATE,
case when BENEFITADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end) -- same reason as above.
else BENEFITADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
REVENUEBENEFIT.TOTALVALUE as AMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Benefit Expense' ,'Pledge') as REFERENCE,
tf.ERRORMESSAGE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUEBENEFIT.BASECURRENCYID,
REVENUEBENEFIT.TRANSACTIONTOTALVALUE as TRANSACTIONAMOUNT,
REVENUEBENEFIT.TRANSACTIONCURRENCYID,
REVENUEBENEFIT.BASEEXCHANGERATEID,
REVENUEBENEFIT.ORGANIZATIONTOTALVALUE as ORGANIZATIONAMOUNT,
REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID,
1 as BENEFITTYPECODE,
tf.MAPPEDVALUES
from dbo.REVENUE
inner join dbo.REVENUEBENEFIT on REVENUE.ID = REVENUEBENEFIT.REVENUEID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.ID = @REVENUESPLITID
left join dbo.BENEFITADJUSTMENT on REVENUE.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE,85, 9, REVENUEBENEFIT.BENEFITID) as tf
where REVENUE.ID = @REVENUEID and REVENUE.TRANSACTIONTYPECODE = 1 and REVENUEBENEFIT.SENDBENEFIT = 1
) as REVDISTRIBUTIONS
declare @ERRORMESSAGE nvarchar(max);
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
--Trap the error when a holding account exists so we can show it to the user later.
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)
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)
--Check to see if there is an adjustment, if so, use adjustment poststatuscode
DECLARE @ADJUSTMENTPOSTSTATUSCODE tinyint
SET @ADJUSTMENTPOSTSTATUSCODE = 1
if EXISTS (SELECT ID FROM BENEFITADJUSTMENT WHERE REVENUEID = @REVENUEID)
BEGIN
SELECT @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE FROM BENEFITADJUSTMENT WHERE REVENUEID = @REVENUEID
END
-- Insert rows in GLTRANSACTION table
insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE,
JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID, POSTSTATUSCODE,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
select
GLTRANSACTIONID,
TRANSACTIONTYPECODE,
ACCOUNT,
AMOUNT,
PROJECT,
REFERENCE,
POSTDATE,
@JOURNAL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ACCOUNTID,
@ADJUSTMENTPOSTSTATUSCODE,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID
from
@DISTRIBUTIONS;
-- Insert rows in BENEFITGLDISTRIBUTION table
insert into dbo.BENEFITGLDISTRIBUTION
(ID, REVENUEBENEFITID,REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE,
GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,FULLYPAIDSTATUS,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,BENEFITTYPECODE)
select
newid(),
REVENUEBENEFITID,
@REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
PROJECT,
REFERENCE,
AMOUNT,
ACCOUNT,
TRANSACTIONTYPECODE,
GLTRANSACTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
FULLYPAIDSTATUS,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
BENEFITTYPECODE
from
@DISTRIBUTIONS;
end