USP_BANKACCOUNTADJUSTMENT_CREATEDISTRIBUTION
Create a distribution for a given adjustment
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BANKACCOUNTADJUSTMENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure [dbo].[USP_BANKACCOUNTADJUSTMENT_CREATEDISTRIBUTION]
(
@BANKACCOUNTADJUSTMENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTDATE datetime = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
begin
set nocount on;
declare @JOURNAL nvarchar(50) = 'Blackbaud Enterprise';
declare @PAYMENTREFERENCE nvarchar(100) = 'Bank Adjustment - Payment';
declare @DEPOSITREFERENCE nvarchar(100) = 'Bank Adjustment - Deposit';
declare @DISTRIBUTION table(
ID uniqueidentifier,
GLTRANSACTIONID uniqueidentifier,
ACCOUNT nvarchar(100),
GLACCOUNTID uniqueidentifier,
AMOUNT money,
PROJECT nvarchar(100),
REFERENCE nvarchar(255),
TRANSACTIONTYPECODE tinyint
,TRANSACTIONAMOUNT money
,ORGANIZATIONAMOUNT money
,TRANSACTIONCURRENCYID uniqueidentifier
,BASECURRENCYID uniqueidentifier
,BASEEXCHANGERATEID uniqueidentifier
,ORGANIZATIONEXCHANGERATEID uniqueidentifier
,SYSTEMDISTRIBUTION bit
,ERRORMESSAGE nvarchar(255)
,MAPPEDVALUES XML);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CURRENTDATE is null
set @CURRENTDATE = getdate();
declare @ADJUSTMENTTYPECODE tinyint;
declare @TRANSFERADJUSTMENTID uniqueidentifier;
declare @AMOUNT1 money;
declare @AMOUNT2 money;
declare @GLACCOUNT1ID uniqueidentifier;
declare @ACCOUNT1 nvarchar(100);
declare @GLACCOUNT2ID uniqueidentifier;
declare @ACCOUNT2 nvarchar(100);
declare @PDACCOUNTSEGMENTVALUE1ID uniqueidentifier = null;
declare @PDACCOUNTSEGMENTVALUE2ID uniqueidentifier = null;
declare @POSTSTATUSCODE tinyint;
declare @POSTDATE datetime;
declare @UNMAPPEDCOMPSEGMENT tinyint;
declare @TRANSACTIONAMOUNT1 money;
declare @TRANSACTIONAMOUNT2 money;
declare @ORGANIZATIONAMOUNT1 money;
declare @ORGANIZATIONAMOUNT2 money;
declare @TRANSACTIONCURRENCYID1 uniqueidentifier;
declare @TRANSACTIONCURRENCYID2 uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID1 uniqueidentifier;
declare @BASEEXCHANGERATEID2 uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID1 uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID2 uniqueidentifier;
declare @REFERENCE nvarchar(100);
declare @DefaultAccountID uniqueidentifier;
declare @DefaultAccount nvarchar(100);
declare @ERRORMESSAGE nvarchar(255);
declare @SKIPACCOUNTERROR bit = 0;
declare @AccountSystem nvarchar(50);
declare @MAPPEDVALUES xml;
declare @PAYMENTMETHODTYPEID uniqueidentifier;
select @ADJUSTMENTTYPECODE = T.TRANSACTIONTYPECODE,
@TRANSFERADJUSTMENTID = BANKACCOUNTADJUSTMENT.TRANSFERADJUSTMENTID,
@AMOUNT1 = T.AMOUNT,
@AMOUNT2 = isnull(TRANSFER.AMOUNT, T.AMOUNT),
@GLACCOUNT1ID = BA1.GLACCOUNTID,
@ACCOUNT1 = A1.ACCOUNTNUMBER,
@GLACCOUNT2ID = BA2.GLACCOUNTID,
@ACCOUNT2 = A2.ACCOUNTNUMBER,
@PDACCOUNTSEGMENTVALUE1ID = BA1.PDACCOUNTSEGMENTVALUEID,
@PDACCOUNTSEGMENTVALUE2ID = BA2.PDACCOUNTSEGMENTVALUEID,
@POSTSTATUSCODE = T.POSTSTATUSCODE,
@POSTDATE = T.POSTDATE,
@PAYMENTREFERENCE = CASE WHEN Len(T.REFERENCE) > 0 THEN T.REFERENCE ELSE @PAYMENTREFERENCE END,
@DEPOSITREFERENCE = CASE WHEN Len(T.REFERENCE) > 0 THEN T.REFERENCE ELSE @DEPOSITREFERENCE END,
@PDACCOUNTSYSTEMID = BA1.PDACCOUNTSYSTEMID
,@TRANSACTIONAMOUNT1 = T.TRANSACTIONAMOUNT
,@TRANSACTIONAMOUNT2 = isnull(TRANSFER.TRANSACTIONAMOUNT, T.TRANSACTIONAMOUNT)
,@ORGANIZATIONAMOUNT1 = T.ORGANIZATIONAMOUNT
,@ORGANIZATIONAMOUNT2 = isnull(TRANSFER.ORGANIZATIONAMOUNT, T.ORGANIZATIONAMOUNT)
,@TRANSACTIONCURRENCYID1 = BA1.TRANSACTIONCURRENCYID
,@TRANSACTIONCURRENCYID2 = isnull(BA2.TRANSACTIONCURRENCYID, BA1.TRANSACTIONCURRENCYID)
,@BASECURRENCYID = T.BASECURRENCYID
,@BASEEXCHANGERATEID1 = T.BASEEXCHANGERATEID
,@BASEEXCHANGERATEID2 = isnull(TRANSFER.BASEEXCHANGERATEID, T.BASEEXCHANGERATEID)
,@ORGANIZATIONEXCHANGERATEID1 = T.ORGANIZATIONEXCHANGERATEID
,@ORGANIZATIONEXCHANGERATEID2 = isnull(TRANSFER.ORGANIZATIONEXCHANGERATEID, T.ORGANIZATIONEXCHANGERATEID)
,@REFERENCE = T.REFERENCE
from dbo.BANKACCOUNTADJUSTMENT
inner join dbo.BANKACCOUNTTRANSACTION T on BANKACCOUNTADJUSTMENT.ID = T.ID
inner join dbo.BANKACCOUNT BA1 on BA1.ID = T.BANKACCOUNTID
left outer join dbo.GLACCOUNT A1 on A1.ID = BA1.GLACCOUNTID
left outer join dbo.BANKACCOUNTTRANSACTION TRANSFER on TRANSFER.ID = BANKACCOUNTADJUSTMENT.TRANSFERADJUSTMENTID
left outer join dbo.BANKACCOUNT BA2 on BA2.ID = TRANSFER.BANKACCOUNTID
left outer join dbo.GLACCOUNT A2 on A2.ID = BA2.GLACCOUNTID
where BANKACCOUNTADJUSTMENT.ID = @BANKACCOUNTADJUSTMENTID;
if @ADJUSTMENTTYPECODE = 16
set @PAYMENTREFERENCE = @DEPOSITREFERENCE;
else if @ADJUSTMENTTYPECODE = 17
set @DEPOSITREFERENCE = @PAYMENTREFERENCE
if @ADJUSTMENTTYPECODE in (16, 17)
select @PAYMENTMETHODTYPEID = MT.ID from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE MT where MT.OFFICEID = 8 and MT.NAMEID = CASE @ADJUSTMENTTYPECODE WHEN 16 THEN 1 WHEN 17 THEN 2 END;
select @DefaultAccountID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID, @DefaultAccount = GLACCOUNT.ACCOUNTNUMBER, @AccountSystem = PDACCOUNTSYSTEM.NAME
from dbo.PDACCOUNTSYSTEM
left join dbo.GLACCOUNT on PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID = GLACCOUNT.ID
where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
if @GLACCOUNT2ID is null
begin
if @ADJUSTMENTTYPECODE in (16, 17)
begin
select @PDACCOUNTSEGMENTVALUE2ID = CREDITPDACCOUNTSEGMENTVALUEID,
@GLACCOUNT2ID = CREDITGLACCOUNTID
from PDACCOUNTCODEMAPPING
where OFFICEID = 8 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and (PAYMENTMETHOD & CASE @ADJUSTMENTTYPECODE WHEN 16 THEN 1 WHEN 17 THEN 2 END) > 0;
if @PDACCOUNTSEGMENTVALUE2ID is null and @GLACCOUNT2ID is null
begin
declare @OFFICENAME nvarchar(100);
declare @ADJUSTMENTTYPE nvarchar(100);
select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 8;
select @ADJUSTMENTTYPE = RIGHT(TRANSACTIONTYPE, len(TRANSACTIONTYPE) - 11) from dbo.BANKACCOUNTTRANSACTION where ID = @BANKACCOUNTADJUSTMENTID;
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", a mapping does not exist for Office: ' + @OFFICENAME + ', Adjustment type: ' + @ADJUSTMENTTYPE
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1)
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE], [MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
select @GLACCOUNT2ID = @DefaultAccountID, @ACCOUNT2 = @DefaultAccount
end
end
end
if (@GLACCOUNT1ID is null or @GLACCOUNT2ID is null)
begin
if ((select COUNT(ID) from dbo.PDACCOUNTSTRUCTURE where SEGMENTTYPE = 3 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)) != (select COUNT(a.ID) from dbo.PDACCOUNTSEGMENTMAPPING a join dbo.PDACCOUNTSTRUCTURE b on a.PDACCOUNTSTRUCTUREID = b.ID where ISDEFAULT = 1 and b.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSEGMENTVALUEID is not null)
begin
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", default segment mappings must be defined in order to generate GL distributions for this transaction.'
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1);
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 2 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
set @SKIPACCOUNTERROR = 1
end
select @UNMAPPEDCOMPSEGMENT = dbo.UFN_BANKACCOUNTADJUSTMENT_GETUNMAPPEDCOMPOSITESEGMENT_BYACCOUNTSYSTEM(@PDACCOUNTSYSTEMID)
if @UNMAPPEDCOMPSEGMENT = 1
begin
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", default segment mappings must be defined in order to generate GL distributions for this transaction.'
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1);
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 2 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
set @SKIPACCOUNTERROR = 1
end
end
if @GLACCOUNT1ID is null
begin
select @ACCOUNT1 = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@PDACCOUNTSEGMENTVALUE1ID,@PDACCOUNTSYSTEMID);
select @GLACCOUNT1ID = ID from GLACCOUNT where ACCOUNTNUMBER = @ACCOUNT1 and PDACCOUNTSYSTEMID=@PDACCOUNTSYSTEMID;
if @GLACCOUNT1ID is null
begin
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @ACCOUNT1 + '" does not exist. The action could not be completed.'
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1);
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
select @GLACCOUNT1ID = @DefaultAccountID, @ACCOUNT1 = @DefaultAccount
end
end
if @GLACCOUNT2ID is null
begin
select @ACCOUNT2 = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@PDACCOUNTSEGMENTVALUE2ID,@PDACCOUNTSYSTEMID);
select @GLACCOUNT2ID = ID from GLACCOUNT where ACCOUNTNUMBER = @ACCOUNT2 and PDACCOUNTSYSTEMID=@PDACCOUNTSYSTEMID;
if @GLACCOUNT2ID is null
begin
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @ACCOUNT2 + '" does not exist. The action could not be completed.'
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1);
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
select @GLACCOUNT2ID = @DefaultAccountID, @ACCOUNT2 = @DefaultAccount
end
end
if @ACCOUNT2 is null
select @ACCOUNT2 = ACCOUNTNUMBER from GLACCOUNT where ID = @GLACCOUNT2ID;
--Get the intercurrency account when the transaction currencies are not the same.
declare @CURRENCYACCOUNTID uniqueidentifier;
declare @CURRENCYACCOUNTSEGMENTVALUEID uniqueidentifier;
declare @CURRENCYACCOUNT nvarchar(100);
if @TRANSACTIONCURRENCYID1 <> @TRANSACTIONCURRENCYID2
begin
set @ERRORMESSAGE = null
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 = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
if @CURRENCYACCOUNTID is null
begin
select @CURRENCYACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@CURRENCYACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
select @CURRENCYACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @CURRENCYACCOUNT;
if @CURRENCYACCOUNTID is null
begin
set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @CURRENCYACCOUNT + '" does not exist. The action could not be completed.'
if @DefaultAccountID is null
raiserror(@ERRORMESSAGE, 13, 1);
else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
begin
set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 8, null, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
values (@BANKACCOUNTADJUSTMENTID, 100, @ERRORMESSAGE,@MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
set @CURRENCYACCOUNTID = @DefaultAccountID
set @CURRENCYACCOUNT = @DefaultAccount
end
end
else
select @CURRENCYACCOUNT = ACCOUNTNUMBER from dbo.GLACCOUNT where ID = @CURRENCYACCOUNTID;
end
if @AMOUNT1 <> @AMOUNT2 or @ORGANIZATIONAMOUNT1 <> @ORGANIZATIONAMOUNT2
begin
declare @AMOUNTS UDT_GAINLOSS_AMOUNTS;
insert into @AMOUNTS
(ID, NEWBASEAMOUNT, ORIGINALBASEAMOUNT, NEWORGANIZATIONAMOUNT, ORIGINALORGANIZATIONAMOUNT)
values
(@TRANSFERADJUSTMENTID, @AMOUNT1, @AMOUNT2, @ORGANIZATIONAMOUNT1, @ORGANIZATIONAMOUNT2)
declare @DEBITSIDE bit;
declare @CURRENCYID uniqueidentifier;
set @DEBITSIDE = CASE WHEN @ADJUSTMENTTYPECODE = 32 THEN 0 ELSE 1 END;
set @CURRENCYID = CASE WHEN @ADJUSTMENTTYPECODE = 32 THEN @TRANSACTIONCURRENCYID1 ELSE @TRANSACTIONCURRENCYID2 END;
insert into @DISTRIBUTION(ID, GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, ERRORMESSAGE, MAPPEDVALUES)
exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS
@AMOUNTS
,@DEBITSIDE
,@CURRENCYID
,@PDACCOUNTSYSTEMID
insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
select distinct @BANKACCOUNTADJUSTMENTID, 100, D.ERRORMESSAGE, convert(varchar(max),D.MAPPEDVALUES), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @DISTRIBUTION D
where nullif(D.ERRORMESSAGE, '') is not null and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @BANKACCOUNTADJUSTMENTID and DELETED = 0)
update @DISTRIBUTION
set PROJECT = ' '
,TRANSACTIONAMOUNT = 0
,BASECURRENCYID = @BASECURRENCYID
,SYSTEMDISTRIBUTION = 1
if @TRANSACTIONCURRENCYID1 <> @TRANSACTIONCURRENCYID2
begin
--Create a copy of the gain loss for the intercurrency balancing account
insert into @DISTRIBUTION(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, PROJECT, TRANSACTIONAMOUNT, BASECURRENCYID, SYSTEMDISTRIBUTION)
select
NEWID()
,@CURRENCYACCOUNT
,@CURRENCYACCOUNTID
,CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN 1 ELSE 0 END
,T.AMOUNT
,T.ORGANIZATIONAMOUNT
,'Intercurrency Balancing'
,T.PROJECT
,T.TRANSACTIONAMOUNT
,T.BASECURRENCYID
,1
from @DISTRIBUTION T
end
end
if @ADJUSTMENTTYPECODE in (32, 33)
begin
if len(@REFERENCE) > 0
begin
set @PAYMENTREFERENCE = @REFERENCE;
set @DEPOSITREFERENCE = @REFERENCE;
end
else
begin
set @PAYMENTREFERENCE = 'Bank Adjustment - Transfer Out';
set @DEPOSITREFERENCE = 'Bank Adjustment - Transfer In';
end
end
insert into @DISTRIBUTION
(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,SYSTEMDISTRIBUTION)
values
(NEWID(),
@ACCOUNT1,
@GLACCOUNT1ID,
@AMOUNT1,
' ',
CASE WHEN @ADJUSTMENTTYPECODE in (16, 33) THEN 0 ELSE 1 END
,@TRANSACTIONAMOUNT1
,@ORGANIZATIONAMOUNT1
,@TRANSACTIONCURRENCYID1
,@BASECURRENCYID
,@BASEEXCHANGERATEID1
,@ORGANIZATIONEXCHANGERATEID1
,0)
insert into @DISTRIBUTION
(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,SYSTEMDISTRIBUTION)
values
(NEWID(),
@ACCOUNT2,
@GLACCOUNT2ID,
@AMOUNT2,
' ',
CASE WHEN @ADJUSTMENTTYPECODE in (16, 33) THEN 1 ELSE 0 END
,@TRANSACTIONAMOUNT2
,@ORGANIZATIONAMOUNT2
,@TRANSACTIONCURRENCYID2
,@BASECURRENCYID
,@BASEEXCHANGERATEID2
,@ORGANIZATIONEXCHANGERATEID2
,CASE WHEN @TRANSACTIONCURRENCYID1 <> @TRANSACTIONCURRENCYID2 THEN 1 ELSE 0 END)
if @TRANSACTIONCURRENCYID1 <> @TRANSACTIONCURRENCYID2
begin
insert into @DISTRIBUTION
(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,REFERENCE
,SYSTEMDISTRIBUTION)
values
(NEWID(),
@CURRENCYACCOUNT
,@CURRENCYACCOUNTID
,@AMOUNT1,
' ',
CASE WHEN @ADJUSTMENTTYPECODE = 33 THEN 1 ELSE 0 END
,@TRANSACTIONAMOUNT1
,@ORGANIZATIONAMOUNT1
,@TRANSACTIONCURRENCYID1
,@BASECURRENCYID
,@BASEEXCHANGERATEID1
,@ORGANIZATIONEXCHANGERATEID1
,'Intercurrency Balancing'
,0)
insert into @DISTRIBUTION
(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,REFERENCE
,SYSTEMDISTRIBUTION)
values
(NEWID(),
@CURRENCYACCOUNT
,@CURRENCYACCOUNTID
,@AMOUNT2,
' ',
CASE WHEN @ADJUSTMENTTYPECODE = 33 THEN 0 ELSE 1 END
,@TRANSACTIONAMOUNT2
,@ORGANIZATIONAMOUNT2
,@TRANSACTIONCURRENCYID2
,@BASECURRENCYID
,@BASEEXCHANGERATEID2
,@ORGANIZATIONEXCHANGERATEID2
,'Intercurrency Balancing'
,1)
end
insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTSTATUSCODE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,SYSTEMDISTRIBUTION)
select
GLTRANSACTIONID,
TRANSACTIONTYPECODE,
ACCOUNT,
AMOUNT,
PROJECT,
isnull(REFERENCE, CASE WHEN TRANSACTIONTYPECODE = 0 THEN @DEPOSITREFERENCE ELSE @PAYMENTREFERENCE END),
@POSTSTATUSCODE,
@POSTDATE,
@JOURNAL,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
GLACCOUNTID
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
,SYSTEMDISTRIBUTION
from @DISTRIBUTION;
insert into dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION(ID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, BANKACCOUNTTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID)
select
NEWID(),
PROJECT,
isnull(REFERENCE, CASE WHEN TRANSACTIONTYPECODE = 0 THEN @DEPOSITREFERENCE ELSE @PAYMENTREFERENCE END),
AMOUNT,
ACCOUNT,
TRANSACTIONTYPECODE,
GLTRANSACTIONID,
@BANKACCOUNTADJUSTMENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
,TRANSACTIONAMOUNT
,ORGANIZATIONAMOUNT
,TRANSACTIONCURRENCYID
,BASECURRENCYID
,BASEEXCHANGERATEID
,ORGANIZATIONEXCHANGERATEID
from @DISTRIBUTION;
end