USP_PAYMENTAPPLICATION_MULTICURRENCYGLDISTRIBUTION
Generate the multicurrency gain/loss system distribution.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PAYMENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure [dbo].[USP_PAYMENTAPPLICATION_MULTICURRENCYGLDISTRIBUTION]
(
@PAYMENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
begin
set nocount on;
if @CHANGEAGENTID is null
exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @PDACCOUNTSYSTEMID uniqueidentifier = null;
declare @PAYMENTCURRENCYID uniqueidentifier = null;
declare @BASECURRENCYID uniqueidentifier = null;
declare @CREDIT bit = 1;
declare @DEBIT bit = 0;
declare @PLEDGEAPPLICATIONCODE integer = 2;
declare @PLANNEDGIFTAPPLICATIONCODE integer = 6;
declare @GRANTAWARDCODE integer = 8;
declare @ERRORMESSAGE nvarchar(max);
declare @DEFAULTACCOUNTID uniqueidentifier;
declare @DEFAULTACCOUNT nvarchar(100);
declare @ACCOUNTSYSTEM nvarchar(50);
select
@PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID,
@PAYMENTCURRENCYID = PAYMENT.TRANSACTIONCURRENCYID,
@BASECURRENCYID = PAYMENT.BASECURRENCYID
from dbo.REVENUE AS PAYMENT with (nolock)
inner join dbo.PDACCOUNTSYSTEMFORREVENUE on PAYMENT.ID = PDACCOUNTSYSTEMFORREVENUE.ID
where PAYMENT.ID = @PAYMENTID;
select @DEFAULTACCOUNTID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID, @DEFAULTACCOUNT = GLACCOUNT.ACCOUNTNUMBER, @ACCOUNTSYSTEM = PDACCOUNTSYSTEM.NAME
from dbo.PDACCOUNTSYSTEM
inner join dbo.GLACCOUNT on GLACCOUNT.ID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID
where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
declare @ApplicationInfo table (SPLITID uniqueidentifier,
APPLICATIONCURRENCYID uniqueidentifier,
APPLICATIONEXCHANGERATEID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier)
-- Get the Currency and exchange rates from the applied to record.
insert @ApplicationInfo (SPLITID, APPLICATIONCURRENCYID, APPLICATIONEXCHANGERATEID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID )
select distinct split.ID,
pledge.TRANSACTIONCURRENCYID,
sp.APPLICATIONEXCHANGERATEID,
pledge.BASEEXCHANGERATEID,
pledge.ORGANIZATIONEXCHANGERATEID
from REVENUESPLIT as split
inner join INSTALLMENTSPLITPAYMENT as sp on split.ID = sp.PAYMENTID
inner join REVENUE as pledge on pledge.ID = sp.PLEDGEID
where split.APPLICATIONCODE in (@PLEDGEAPPLICATIONCODE, @PLANNEDGIFTAPPLICATIONCODE, @GRANTAWARDCODE) and split.REVENUEID = @PAYMENTID
and pledge.DONOTPOST = 0
if (select count(SPLITID) from @ApplicationInfo) > 0
begin
--Get the intercurrency account.
declare @INTERCURRENCYACCTID uniqueidentifier = null;
declare @INTERCURRENCYACCTSEGMENTVALUEID uniqueidentifier = null;
declare @INTERCURRENCYACCTCODE nvarchar(30) = null;
declare @INTERCURRENCYACCT nvarchar(100) = null;
declare @MAPPINGVALUES XML;
if exists(select SPLITID from @ApplicationInfo where APPLICATIONCURRENCYID <> @PAYMENTCURRENCYID)
begin
exec dbo.USP_GET_INTERCURRENCY_BALANCINGACCOUNT @PDACCOUNTSYSTEMID, @INTERCURRENCYACCTID output, @INTERCURRENCYACCTSEGMENTVALUEID output, @ERRORMESSAGE output, @MAPPINGVALUES output
if nullif(@ERRORMESSAGE, '') is not null
if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @PAYMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@PAYMENTID, (select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @PAYMENTID), @ERRORMESSAGE, @MAPPINGVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if @INTERCURRENCYACCTID is null
select @INTERCURRENCYACCTCODE = SHORTDESCRIPTION from PDACCOUNTSEGMENTVALUE where ID = @INTERCURRENCYACCTSEGMENTVALUEID;
else
select @INTERCURRENCYACCT = ACCOUNTNUMBER from dbo.GLACCOUNT where ID = @INTERCURRENCYACCTID;
end
declare @NewAccounts table (ID uniqueidentifier,
GLACCOUNTID uniqueidentifier,
ACCOUNTNUMBERS nvarchar(100),
REVENUEID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
POSTSTATUSCODE tinyint,
POSTDATE datetime,
REFERENCE nvarchar(100),
SPLITID uniqueidentifier,
ERRORMESSAGE nvarchar(max),
MAPPEDVALUES xml);
insert into @NewAccounts(ID, ACCOUNTNUMBERS, GLACCOUNTID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, POSTSTATUSCODE, POSTDATE, REFERENCE, SPLITID)
select T.ID,
case when AI.APPLICATIONCURRENCYID <> @PAYMENTCURRENCYID
then ISNULL(@INTERCURRENCYACCT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @INTERCURRENCYACCTCODE, @PDACCOUNTSYSTEMID))
else A.ACCOUNTNUMBER
end as ACCOUNTNUMBERS,
case when AI.APPLICATIONCURRENCYID <> @PAYMENTCURRENCYID
then ISNULL(@INTERCURRENCYACCTID, (select A.ID from GLACCOUNT A with (nolock) where A.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and A.ACCOUNTNUMBER = dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @INTERCURRENCYACCTCODE, @PDACCOUNTSYSTEMID)))
else T.GLACCOUNTID
end as GLACCOUNTID,
@PAYMENTID as REVENUEID,
R.GLPAYMENTMETHODREVENUETYPEMAPPINGID as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
T.POSTSTATUSCODE as POSTSTATUSCODE,
T.POSTDATE as POSTDATE,
case when AI.APPLICATIONCURRENCYID <> @PAYMENTCURRENCYID
then 'Intercurrency Balancing'
else T.REFERENCE
end as REFERENCE,
AI.SPLITID
from dbo.REVENUEGLDISTRIBUTION as R with (nolock)
inner join dbo.GLTRANSACTION as T with (nolock) on R.GLTRANSACTIONID = T.ID
inner join dbo.GLACCOUNT as A with (nolock) on A.ID = T.GLACCOUNTID
inner join dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP as RS with (nolock) on RS.ID = R.ID
inner join @ApplicationInfo as AI on AI.SPLITID= RS.REVENUESPLITID
where R.TRANSACTIONTYPECODE = @CREDIT and T.POSTSTATUSCODE != 0 AND R.REVENUEID = @PAYMENTID AND T.SYSTEMDISTRIBUTION = 0
if exists(select ID from @NewAccounts where GLACCOUNTID is null)
begin
declare @ACCOUNT varchar(100)
if @DEFAULTACCOUNTID is null
begin
select TOP 1 @ACCOUNT = ACCOUNTNUMBERS from @NewAccounts where GLACCOUNTID is null;
raiserror('In account system "%s", the account "%s" does not exist. The action could not be completed. ', 13, 1, @ACCOUNTSYSTEM, @ACCOUNT);
end
else
begin
update @NewAccounts set
GLACCOUNTID = @DEFAULTACCOUNTID
,ACCOUNTNUMBERS = @DEFAULTACCOUNT
,ERRORMESSAGE = 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNTNUMBERS + '" does not exist. The action could not be completed.'
,MAPPEDVALUES = (select tv1.*, 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNTNUMBERS + '" does not exist. The action could not be completed.', 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 18, null, null, null, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
where GLACCOUNTID is null
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
select distinct
@PAYMENTID
,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @PAYMENTID)
,D.ERRORMESSAGE
,convert(nvarchar(max), D.MAPPEDVALUES)
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CHANGEDATE
,@CHANGEDATE
from @NewAccounts D where nullif(D.ERRORMESSAGE, '') is not null
and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @PAYMENTID and DELETED = 0)
end
end
declare @DISTRIBUTION table(
ID uniqueidentifier,
ACCOUNT nvarchar(100),
GLACCOUNTID uniqueidentifier,
AMOUNT money,
PROJECT nvarchar(100),
REFERENCE nvarchar(255),
POSTSTATUSCODE tinyint,
POSTDATE datetime,
TRANSACTIONTYPECODE tinyint,
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
REVENUEID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
ORIGINATINGTRANSACTIONID uniqueidentifier,
ORIGINALACCOUNT nvarchar(100),
ORIGINALGLACCOUNTID uniqueidentifier,
ORGINALREFERENCE nvarchar(100),
ERRORMESSAGE nvarchar(max),
MAPPEDVALUES xml);
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
-- Add Intercurrency distribution lines as needed.
insert into @DISTRIBUTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, GLACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ORIGINATINGTRANSACTIONID, ORIGINALACCOUNT, ORIGINALGLACCOUNTID, ORGINALREFERENCE)
select NEWID()
,@DEBIT
,T.ACCOUNTNUMBERS
,CASE WHEN AI.APPLICATIONCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID), AI.BASEEXCHANGERATEID)
end
,GLTRANSACTION.PROJECT
,'Intercurrency Balancing'
,GLTRANSACTION.POSTSTATUSCODE
,GLTRANSACTION.POSTDATE
,T.GLACCOUNTID
,dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID)
,CASE WHEN @ORGAMOUNTORIGINCODE = 0
THEN CASE WHEN @ORGCURRENCYID = @BASECURRENCYID
THEN
CASE WHEN AI.APPLICATIONCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID), AI.BASEEXCHANGERATEID)
end
ELSE
CASE WHEN AI.APPLICATIONCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID), AI.ORGANIZATIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID), AI.BASEEXCHANGERATEID), AI.ORGANIZATIONEXCHANGERATEID)
end
END
ELSE
CASE WHEN @ORGCURRENCYID = AI.APPLICATIONCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(GLTRANSACTION.TRANSACTIONAMOUNT, AI.APPLICATIONEXCHANGERATEID), AI.ORGANIZATIONEXCHANGERATEID)
end
end
,AI.APPLICATIONCURRENCYID
,@BASECURRENCYID
,AI.BASEEXCHANGERATEID
,AI.ORGANIZATIONEXCHANGERATEID
,T.REVENUEID
,T.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,T.ID
,GLTRANSACTION.ACCOUNT as ORIGINALACCOUNT
,GLTRANSACTION.GLACCOUNTID as ORIGINALGLACCOUNTID
,GLTRANSACTION.REFERENCE as ORIGINALREFERENCE
from dbo.GLTRANSACTION
inner join @NewAccounts T on T.ID = GLTRANSACTION.ID
inner join @ApplicationInfo as AI on AI.SPLITID=T.SPLITID
where @PAYMENTCURRENCYID <> AI.APPLICATIONCURRENCYID
insert into @DISTRIBUTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, GLACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ORIGINATINGTRANSACTIONID)
select NEWID()
,@CREDIT
,t.ORIGINALACCOUNT
,T.AMOUNT
,T.PROJECT
,T.ORGINALREFERENCE
,T.POSTSTATUSCODE
,T.POSTDATE
,T.ORIGINALGLACCOUNTID
,T.TRANSACTIONAMOUNT
,T.ORGANIZATIONAMOUNT
,T.TRANSACTIONCURRENCYID
,T.BASECURRENCYID
,T.BASEEXCHANGERATEID
,T.ORGANIZATIONEXCHANGERATEID
,T.REVENUEID
,T.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,T.ORIGINATINGTRANSACTIONID
from @DISTRIBUTION T
-- Compute Gain losses with Intercurrency accounts.
declare @AMOUNTS UDT_GAINLOSS_AMOUNTS;
insert into @AMOUNTS (ID, ORIGINALBASEAMOUNT, ORIGINALORGANIZATIONAMOUNT, NEWBASEAMOUNT, NEWORGANIZATIONAMOUNT, GLACCOUNTID)
select T.ID, GL.BASEAMOUNT, GL.ORGAMOUNT, D.AMOUNT, D.ORGANIZATIONAMOUNT, T.GLACCOUNTID
from @NewAccounts T
inner join dbo.JOURNALENTRY GL on GL.ID = T.ID
inner join @DISTRIBUTION D on T.ID = D.ORIGINATINGTRANSACTIONID
where D.TRANSACTIONTYPECODE = @DEBIT
insert into @DISTRIBUTION(ORIGINATINGTRANSACTIONID, ID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, ERRORMESSAGE, MAPPEDVALUES)
exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS @AMOUNTS, 0, @PAYMENTCURRENCYID, @PDACCOUNTSYSTEMID
insert into @DISTRIBUTION(ORIGINATINGTRANSACTIONID, ID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE)
select T.ORIGINATINGTRANSACTIONID
,NEWID()
,A.ACCOUNTNUMBERS
,A.GLACCOUNTID
,CASE WHEN T.TRANSACTIONTYPECODE = 0 then 1 else 0 end
,T.AMOUNT
,T.ORGANIZATIONAMOUNT
,'Intercurrency Balancing'
from @DISTRIBUTION T
inner join @NewAccounts A on T.ORIGINATINGTRANSACTIONID = A.ID
where T.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null
-- Compute Gain/Losses for applications that do not have Intercurrency accounts.
insert into @AMOUNTS (ID, ORIGINALBASEAMOUNT, ORIGINALORGANIZATIONAMOUNT, NEWBASEAMOUNT, NEWORGANIZATIONAMOUNT, GLACCOUNTID)
select T.ID, GL.BASEAMOUNT, GL.ORGAMOUNT, 0, 0, T.GLACCOUNTID
from @NewAccounts T
inner join dbo.JOURNALENTRY GL on GL.ID = T.ID
inner join @ApplicationInfo as AI on AI.SPLITID=T.SPLITID
where @PAYMENTCURRENCYID = AI.APPLICATIONCURRENCYID
update dbo.JOURNALENTRY set
--,BASEEXCHANGERATEID = ai.BASEEXCHANGERATEID
--,ORGANIZATIONEXCHANGERATEID = AI.ORGANIZATIONEXCHANGERATEID
BASEAMOUNT = CASE WHEN AI.APPLICATIONCURRENCYID <> @BASECURRENCYID
THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, AI.BASEEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END
,ORGAMOUNT = CASE WHEN @ORGAMOUNTORIGINCODE = 0
THEN CASE WHEN @BASECURRENCYID <> @ORGCURRENCYID
THEN CASE WHEN AI.APPLICATIONCURRENCYID <> @BASECURRENCYID
THEN dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, AI.BASEEXCHANGERATEID), AI.ORGANIZATIONEXCHANGERATEID)
ELSE dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, AI.ORGANIZATIONEXCHANGERATEID)
END
ELSE CASE WHEN AI.APPLICATIONCURRENCYID <> @BASECURRENCYID
THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, AI.BASEEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END
END
ELSE CASE WHEN AI.APPLICATIONCURRENCYID <> @ORGCURRENCYID
THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, AI.ORGANIZATIONEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END
END
-- Boilerplate
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CHANGEDATE
from @NewAccounts as T
inner join @ApplicationInfo as AI on AI.SPLITID= T.SPLITID
where AI.APPLICATIONCURRENCYID = @PAYMENTCURRENCYID and T.ID = JOURNALENTRY.ID;
-- With the FTM These are just one table so the following stmt is not needed
--update dbo.REVENUEGLDISTRIBUTION set
-- BASECURRENCYID = @BASECURRENCYID
-- ,BASEEXCHANGERATEID = AI.BASEEXCHANGERATEID
-- ,ORGANIZATIONEXCHANGERATEID = AI.ORGANIZATIONEXCHANGERATEID
-- ,AMOUNT = GLTRANSACTION.AMOUNT
-- ,ORGANIZATIONAMOUNT = GLTRANSACTION.ORGANIZATIONAMOUNT
-- ,CHANGEDBYID = @CHANGEAGENTID
-- ,DATECHANGED = @CHANGEDATE
--from @NewAccounts as T
--inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = T.ID
--inner join @ApplicationInfo as AI on AI.SPLITID = T.SPLITID
--where AI.APPLICATIONCURRENCYID = @PAYMENTCURRENCYID and T.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
update @AMOUNTS set
NEWBASEAMOUNT = T.AMOUNT
,NEWORGANIZATIONAMOUNT = T.ORGANIZATIONAMOUNT
from dbo.GLTRANSACTION T
inner join @AMOUNTS A on A.ID = T.ID
insert into @DISTRIBUTION(ORIGINATINGTRANSACTIONID, ID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, ERRORMESSAGE, MAPPEDVALUES)
exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS @AMOUNTS, 0, @PAYMENTCURRENCYID, @PDACCOUNTSYSTEMID
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE], [MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
select distinct @PAYMENTID, (select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @PAYMENTID), D.ERRORMESSAGE, convert(nvarchar(max), D.MAPPEDVALUES), @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION D
where nullif(D.ERRORMESSAGE, '') is not null and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @PAYMENTID and DELETED = 0);
-- Update the GLTransactions & Revenue Distributions.
update dbo.JOURNALENTRY set
--GLTRANSACTION.ACCOUNT = T.ACCOUNTNUMBERS -- No longer necessary
JOURNALENTRY.COMMENT = T.REFERENCE
,JOURNALENTRY.GLACCOUNTID = T.GLACCOUNTID
-- Boilerplate
,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
,JOURNALENTRY.DATECHANGED = @CHANGEDATE
from @NewAccounts as T
where T.ID = JOURNALENTRY.ID;
-- In the FTM this is no longer necessary
--update dbo.REVENUEGLDISTRIBUTION set
-- REVENUEGLDISTRIBUTION.ACCOUNT = T.ACCOUNTNUMBERS
-- ,REVENUEGLDISTRIBUTION.REFERENCE = T.REFERENCE
-- ,REVENUEGLDISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID
-- ,REVENUEGLDISTRIBUTION.DATECHANGED = @CHANGEDATE
--from @NewAccounts as T
--where T.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID;
update @DISTRIBUTION set
PROJECT = ' '
,TRANSACTIONAMOUNT = 0
,BASECURRENCYID = @BASECURRENCYID
,POSTDATE = A.POSTDATE
,POSTSTATUSCODE = A.POSTSTATUSCODE
,GLPAYMENTMETHODREVENUETYPEMAPPINGID = A.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,REVENUEID = A.REVENUEID
from @DISTRIBUTION T
inner join @NewAccounts A on A.ID = T.ORIGINATINGTRANSACTIONID
where T.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null
insert into dbo.GLTRANSACTION
(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, JOURNAL, GLACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, SYSTEMDISTRIBUTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
T.ID
,T.TRANSACTIONTYPECODE
,T.ACCOUNT
,T.AMOUNT
,T.PROJECT
,T.REFERENCE
,T.POSTSTATUSCODE
,T.POSTDATE
,'Blackbaud Enterprise'
,T.GLACCOUNTID
,T.TRANSACTIONAMOUNT
,T.ORGANIZATIONAMOUNT
,T.TRANSACTIONCURRENCYID
,T.BASECURRENCYID
,T.BASEEXCHANGERATEID
,T.ORGANIZATIONEXCHANGERATEID
,1
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION T
insert into dbo.REVENUEGLDISTRIBUTION
(ID, GLTRANSACTIONID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, BASECURRENCYID, TRANSACTIONCURRENCYID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
NEWID()
,T.ID
,T.REVENUEID
,T.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,T.PROJECT
,T.REFERENCE
,T.AMOUNT
,T.ACCOUNT
,T.TRANSACTIONTYPECODE
,T.BASECURRENCYID
,T.TRANSACTIONCURRENCYID
,T.TRANSACTIONAMOUNT
,T.ORGANIZATIONAMOUNT
,T.BASEEXCHANGERATEID
,T.ORGANIZATIONEXCHANGERATEID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION T
insert into dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP
(ID, REVENUESPLITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ND.ID, M.REVENUESPLITID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION T
inner join dbo.JOURNALENTRY D on D.ID = T.ORIGINATINGTRANSACTIONID
inner join dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP M on D.ID = M.ID
inner join dbo.JOURNALENTRY ND on ND.ID = T.ID
end
end