USP_BANKACCOUNTDEPOSIT_OVERWRITEMULTIPLEPAYMENTSDEBITACCOUNTS
Overwrite the payments' debit accounts with the bank account's default cash account
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DEPOSITID | uniqueidentifier | IN | |
| @XML | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure [dbo].[USP_BANKACCOUNTDEPOSIT_OVERWRITEMULTIPLEPAYMENTSDEBITACCOUNTS]
(
@DEPOSITID uniqueidentifier,
@XML xml,
@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();
if exists(select ID from dbo.BANKACCOUNTDEPOSITPAYMENT where DEPOSITID = @DEPOSITID)
begin
declare @GLACCOUNTID uniqueidentifier = null;
declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null;
declare @ACCOUNT nvarchar(100) = '';
declare @ACCOUNTCODE nvarchar(30) = '';
declare @PDACCOUNTSYSTEMID uniqueidentifier = null;
declare @BANKACCOUNTCURRENCYID uniqueidentifier = null;
declare @PAYMENTCURRENCYID uniqueidentifier = null;
declare @BASECURRENCYID uniqueidentifier = null;
declare @BASEEXCHANGERATEID uniqueidentifier = null;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null;
declare @BANKEXCHANGERATEID uniqueidentifier = null;
declare @DEPOSITREFERENCE nvarchar(255);
declare @ERRORMESSAGE nvarchar(255);
declare @DEFAULTACCOUNTID uniqueidentifier;
declare @DEFAULTACCOUNT nvarchar(100);
declare @ACCOUNTSYSTEM nvarchar(50);
declare @DEPOSITPOSTDATE datetime = null;
select
@GLACCOUNTID = BANKACCOUNT.GLACCOUNTID,
@PDACCOUNTSEGMENTVALUEID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID,
@ACCOUNT = GLACCOUNT.ACCOUNTNUMBER,
@ACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
@PDACCOUNTSYSTEMID = BANKACCOUNT.PDACCOUNTSYSTEMID
,@BANKACCOUNTCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID
,@BASEEXCHANGERATEID = FT.BASEEXCHANGERATEID
,@ORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID
,@BASECURRENCYID = V.BASECURRENCYID
,@PAYMENTCURRENCYID = BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID
,@BANKEXCHANGERATEID = BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID
,@DEPOSITREFERENCE = nullif(FT.DESCRIPTION, '')
,@DEPOSITPOSTDATE = FT.POSTDATE
from dbo.FINANCIALTRANSACTION FT with (nolock)
inner join dbo.BANKACCOUNTTRANSACTION_EXT BATX with (nolock) on BATX.ID = FT.ID
inner join dbo.BANKACCOUNTDEPOSIT with (nolock) on FT.ID = BANKACCOUNTDEPOSIT.ID
inner join dbo.BANKACCOUNT with (nolock) on BATX.BANKACCOUNTID = BANKACCOUNT.ID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
left outer join dbo.GLACCOUNT with (nolock) on GLACCOUNT.ID = BANKACCOUNT.GLACCOUNTID
left outer join PDACCOUNTSEGMENTVALUE with (nolock) on PDACCOUNTSEGMENTVALUE.ID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID
where FT.ID = @DEPOSITID;
select
@DEFAULTACCOUNTID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID,
@DEFAULTACCOUNT = GLACCOUNT.ACCOUNTNUMBER,
@ACCOUNTSYSTEM = PDACCOUNTSYSTEM.NAME
from dbo.PDACCOUNTSYSTEM
left join dbo.GLACCOUNT on GLACCOUNT.ID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID
where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
--Get the intercurrency account when the transaction currencies are not the same.
declare @CURRENCYACCOUNTID uniqueidentifier = null;
declare @CURRENCYACCOUNTSEGMENTVALUEID uniqueidentifier = null;
declare @CURRENCYACCOUNTCODE nvarchar(30) = null;
declare @CURRENCYACCOUNT nvarchar(100) = null;
declare @MAPPEDVALUES XML;
if @BANKACCOUNTCURRENCYID <> @PAYMENTCURRENCYID
begin
exec dbo.USP_GET_INTERCURRENCY_BALANCINGACCOUNT @PDACCOUNTSYSTEMID, @CURRENCYACCOUNTID output, @CURRENCYACCOUNTSEGMENTVALUEID output, @ERRORMESSAGE output, @MAPPEDVALUES output
if nullif(@ERRORMESSAGE, '') is not null
if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@DEPOSITID, 102, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if @CURRENCYACCOUNTID is null
select @CURRENCYACCOUNTCODE = SHORTDESCRIPTION from PDACCOUNTSEGMENTVALUE where ID = @CURRENCYACCOUNTSEGMENTVALUEID;
else
select @CURRENCYACCOUNT = ACCOUNTNUMBER from dbo.GLACCOUNT where ID = @CURRENCYACCOUNTID;
end
declare @NewAccounts table (ID uniqueidentifier,
GLACCOUNTID uniqueidentifier,
ACCOUNTNUMBERS nvarchar(100)
,ISAUCTION bit
,REVENUEID uniqueidentifier
,GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier
,POSTSTATUSCODE tinyint
,POSTDATE datetime
,REVENUEPURCHASEID uniqueidentifier
,ORIGINALREFERENCE nvarchar(255)
,ERRORMESSAGE nvarchar(255)
,MAPPEDVALUES xml
,TRANSACTIONTYPECODE tinyint
);
insert into @NewAccounts(
ID, ACCOUNTNUMBERS, GLACCOUNTID, ISAUCTION, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID,
POSTSTATUSCODE, POSTDATE, REVENUEPURCHASEID, ORIGINALREFERENCE,TRANSACTIONTYPECODE)
SELECT
JOURNALENTRY.ID,
case
when @BANKACCOUNTCURRENCYID <> @PAYMENTCURRENCYID
then ISNULL(@CURRENCYACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(JOURNALENTRY.GLACCOUNTID, @CURRENCYACCOUNTCODE, @PDACCOUNTSYSTEMID))
else ISNULL(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(JOURNALENTRY.GLACCOUNTID, @ACCOUNTCODE, @PDACCOUNTSYSTEMID))
end,
case
when @BANKACCOUNTCURRENCYID != @PAYMENTCURRENCYID
then @CURRENCYACCOUNTID
else @GLACCOUNTID
end,
0,
PAYMENTS.ID,
JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
case FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE
when 2 then 0
when 3 then 2
else 1 end,
@DEPOSITPOSTDATE,
null,
JOURNALENTRY.COMMENT,
JOURNALENTRY.TRANSACTIONTYPECODE
from
dbo.UFN_BANKACCOUNTDEPOSIT_UNLINKEDPAYMENTS_FROMITEMLISTXML(@XML) as PAYMENTS
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = PAYMENTS.ID
inner join dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and (JOURNALENTRY_EXT.TABLENAMECODE = 1 or JOURNALENTRY_EXT.TABLENAMECODE = 2)
inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING with (nolock) on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
where
JOURNALENTRY.TRANSACTIONTYPECODE = 0
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
and JOURNALENTRY.TYPECODE = 0
and GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE not in (203,204);
if (@CURRENCYACCOUNTID is null and @BANKACCOUNTCURRENCYID <> @PAYMENTCURRENCYID) or (@GLACCOUNTID is null and @BANKACCOUNTCURRENCYID = @PAYMENTCURRENCYID)
begin
update
@NewAccounts
set
GLACCOUNTID = (select A.ID from GLACCOUNT A with (nolock) where A.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and A.ACCOUNTNUMBER = ACCOUNTNUMBERS);
if exists(select ID from @NewAccounts where GLACCOUNTID is null)
begin
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.' as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, null, 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
@DEPOSITID
,102
,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 = @DEPOSITID and DELETED = 0)
end
end
end
update dbo.JOURNALENTRY set
GLACCOUNTID =
case
when T.TRANSACTIONTYPECODE = 0 then T.GLACCOUNTID
else JOURNALENTRY.GLACCOUNTID end,
COMMENT = case when T.TRANSACTIONTYPECODE = 0 then CASE WHEN @BANKACCOUNTCURRENCYID <> @PAYMENTCURRENCYID THEN 'Intercurrency Balancing' else isnull(@DEPOSITREFERENCE, COMMENT) end else JOURNALENTRY.COMMENT end,
POSTDATE = @DEPOSITPOSTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
@NewAccounts as T
inner join dbo.JOURNALENTRY on JOURNALENTRY.ID = T.ID
delete @NewAccounts where TRANSACTIONTYPECODE != 0;
update
dbo.JOURNALENTRY_EXT
set
ACCOUNT = T.ACCOUNTNUMBERS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
@NewAccounts as T
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = T.ID
update dbo.DEPOSITGLDISTRIBUTIONLINK set
DEPOSITID = @DEPOSITID
from @NewAccounts A
inner join dbo.DEPOSITGLDISTRIBUTIONLINK L on A.ID = L.ID
--Add entries to the bank account transaction gl distribution table so the deposit can easily keep track of the related distributions
insert into dbo.DEPOSITGLDISTRIBUTIONLINK(ID, DEPOSITID)
select A.ID, @DEPOSITID
from @NewAccounts A
left join dbo.DEPOSITGLDISTRIBUTIONLINK L on A.ID = L.ID
where L.ID is null
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
,ISAUCTION bit
,REVENUEID uniqueidentifier
,GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier
,REVENUEPURCHASEID uniqueidentifier
,ORIGINATINGTRANSACTIONID uniqueidentifier
,ERRORMESSAGE nvarchar(255)
,MAPPEDVALUES xml);
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
if @PAYMENTCURRENCYID <> @BANKACCOUNTCURRENCYID
begin
insert into @DISTRIBUTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, GLACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ISAUCTION, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEPURCHASEID, ORIGINATINGTRANSACTIONID)
select
NEWID()
,1
,JEX.ACCOUNT
,CASE WHEN @BANKACCOUNTCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID), @BASEEXCHANGERATEID)
end
,JEX.PROJECT
,'Intercurrency Balancing'
,LI.POSTSTATUSCODE
,@DEPOSITPOSTDATE --GLTRANSACTION.POSTDATE
,JE.GLACCOUNTID
,dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID)
,CASE WHEN @ORGAMOUNTORIGINCODE = 0
THEN CASE WHEN @ORGCURRENCYID = @BASECURRENCYID
THEN
CASE WHEN @BANKACCOUNTCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID), @BASEEXCHANGERATEID)
end
ELSE
CASE WHEN @BANKACCOUNTCURRENCYID = @BASECURRENCYID
then dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID), @ORGANIZATIONEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID), @BASEEXCHANGERATEID), @ORGANIZATIONEXCHANGERATEID)
end
END
ELSE
CASE WHEN @ORGCURRENCYID = @BANKACCOUNTCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID)
else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JE.TRANSACTIONAMOUNT, @BANKEXCHANGERATEID), @ORGANIZATIONEXCHANGERATEID)
end
end
,@BANKACCOUNTCURRENCYID
,@BASECURRENCYID
,@BASEEXCHANGERATEID
,@ORGANIZATIONEXCHANGERATEID
,T.ISAUCTION
,T.REVENUEID
,T.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,T.REVENUEPURCHASEID
,T.ID
from @NewAccounts T
inner join dbo.JOURNALENTRY JE on T.ID = JE.ID
inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
insert into @DISTRIBUTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, GLACCOUNTID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, ISAUCTION, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEPURCHASEID, ORIGINATINGTRANSACTIONID)
select NEWID()
,0
,ISNULL(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @ACCOUNTCODE, @PDACCOUNTSYSTEMID))
,T.AMOUNT
,T.PROJECT
,isnull(@DEPOSITREFERENCE, A.ORIGINALREFERENCE)
,T.POSTSTATUSCODE
,@DEPOSITPOSTDATE --T.POSTDATE
,@GLACCOUNTID
,T.TRANSACTIONAMOUNT
,T.ORGANIZATIONAMOUNT
,T.TRANSACTIONCURRENCYID
,T.BASECURRENCYID
,T.BASEEXCHANGERATEID
,T.ORGANIZATIONEXCHANGERATEID
,T.ISAUCTION
,T.REVENUEID
,T.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,T.REVENUEPURCHASEID
,T.ORIGINATINGTRANSACTIONID
from @DISTRIBUTION T
inner join @NewAccounts A on A.ID = T.ORIGINATINGTRANSACTIONID
if @GLACCOUNTID is null
begin
update @DISTRIBUTION set
GLACCOUNTID = (select A.ID from GLACCOUNT A with (nolock) where A.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and A.ACCOUNTNUMBER = ACCOUNT)
where GLACCOUNTID is null;
if exists(select ID from @DISTRIBUTION where GLACCOUNTID is null)
begin
if @DEFAULTACCOUNTID is null
begin
select TOP 1 @ACCOUNT = ACCOUNT from @DISTRIBUTION 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
update @DISTRIBUTION set
GLACCOUNTID = @DEFAULTACCOUNTID
,ACCOUNT = @DEFAULTACCOUNT
,ERRORMESSAGE = 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNT + '" does not exist. The action could not be completed.'
,MAPPEDVALUES = (select tv1.*, 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNT + '" does not exist. The action could not be completed.' as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, null, null, null, null, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
where GLACCOUNTID is null
end
end
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 dbo.JOURNALENTRY_EXT on GL.ID = JOURNALENTRY_EXT.ID
inner join @DISTRIBUTION D on T.ID = D.ORIGINATINGTRANSACTIONID
where D.TRANSACTIONTYPECODE = 0
and JOURNALENTRY_EXT.TABLENAMECODE = 1
union all
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 dbo.JOURNALENTRY_EXT on GL.ID = JOURNALENTRY_EXT.ID
inner join @DISTRIBUTION D on T.ID = D.ORIGINATINGTRANSACTIONID
where D.TRANSACTIONTYPECODE = 0
and JOURNALENTRY_EXT.TABLENAMECODE = 2
insert into @DISTRIBUTION(ORIGINATINGTRANSACTIONID, ID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, ERRORMESSAGE, MAPPEDVALUES)
exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS @AMOUNTS, 1, @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
end
else if @BANKACCOUNTCURRENCYID <> @BASECURRENCYID or @BASECURRENCYID <> @ORGCURRENCYID or @BANKACCOUNTCURRENCYID <> @ORGCURRENCYID
begin
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 dbo.JOURNALENTRY_EXT on GL.ID = JOURNALENTRY_EXT.ID
where JOURNALENTRY_EXT.TABLENAMECODE = 1
union all
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 dbo.JOURNALENTRY_EXT on GL.ID = JOURNALENTRY_EXT.ID
where JOURNALENTRY_EXT.TABLENAMECODE = 2
update dbo.JOURNALENTRY
set BASEAMOUNT = CASE
WHEN @BANKACCOUNTCURRENCYID <> @BASECURRENCYID THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, @BASEEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END,
ORGAMOUNT = CASE
WHEN @ORGAMOUNTORIGINCODE = 0 THEN CASE
WHEN @BASECURRENCYID <> @ORGCURRENCYID THEN CASE
WHEN @BANKACCOUNTCURRENCYID <> @BASECURRENCYID THEN dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, @BASEEXCHANGERATEID), @ORGANIZATIONEXCHANGERATEID)
ELSE dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, @ORGANIZATIONEXCHANGERATEID)
END
ELSE CASE
WHEN @BANKACCOUNTCURRENCYID <> @BASECURRENCYID THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, @BASEEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END
END
ELSE CASE
WHEN @BANKACCOUNTCURRENCYID <> @ORGCURRENCYID THEN dbo.UFN_CURRENCY_CONVERT(JOURNALENTRY.TRANSACTIONAMOUNT, @ORGANIZATIONEXCHANGERATEID)
ELSE JOURNALENTRY.TRANSACTIONAMOUNT
END
END,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from @NewAccounts as T inner join dbo.JOURNALENTRY on T.ID = JOURNALENTRY.ID
update @AMOUNTS set
NEWBASEAMOUNT = T.BASEAMOUNT
,NEWORGANIZATIONAMOUNT = T.ORGAMOUNT
from dbo.JOURNALENTRY 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, 1, @PAYMENTCURRENCYID, @PDACCOUNTSYSTEMID
end
if exists (select 1 from @DISTRIBUTION)
begin
update @DISTRIBUTION set
PROJECT = ' '
,TRANSACTIONAMOUNT = 0
,BASECURRENCYID = @BASECURRENCYID
,POSTDATE = @DEPOSITPOSTDATE --A.POSTDATE
,POSTSTATUSCODE = A.POSTSTATUSCODE
,GLPAYMENTMETHODREVENUETYPEMAPPINGID = A.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,ISAUCTION = A.ISAUCTION
,REVENUEPURCHASEID = A.REVENUEPURCHASEID
,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
,@DEPOSITPOSTDATE --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
where T.ISAUCTION = 0
insert into dbo.AUCTIONPURCHASEGLDISTRIBUTION
(ID, GLTRANSACTIONID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, BASECURRENCYID, TRANSACTIONCURRENCYID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, REVENUEPURCHASEID, 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
,T.REVENUEPURCHASEID
,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION T
where T.ISAUCTION = 1
insert into dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP (ID, REVENUESPLITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select ND.DISTRIBUTIONTABLEID, M.REVENUESPLITID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTION T inner join dbo.JOURNALENTRY_EXT D on D.ID = T.ORIGINATINGTRANSACTIONID
inner join dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP M on D.DISTRIBUTIONTABLEID = M.ID
inner join dbo.JOURNALENTRY_EXT ND on ND.ID = T.ID
where T.ISAUCTION = 0
update dbo.DEPOSITGLDISTRIBUTIONLINK set
DEPOSITID = @DEPOSITID
from @DISTRIBUTION A
inner join dbo.DEPOSITGLDISTRIBUTIONLINK L on A.ID = L.ID
--Add entries to the bank account transaction gl distribution table so the deposit can easily keep track of the related distributions
insert into dbo.DEPOSITGLDISTRIBUTIONLINK(ID, DEPOSITID)
select A.ID, @DEPOSITID
from @DISTRIBUTION A
left join dbo.DEPOSITGLDISTRIBUTIONLINK L on A.ID = L.ID
where L.ID is null
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
select distinct
@DEPOSITID
,102
,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 = @DEPOSITID and DELETED = 0)
end
end
end