USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTADJUSTMENT2
The save procedure used by the edit dataform template "Bank Account Adjustment Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @AMOUNT | decimal(19, 4) | IN | Amount |
| @REFERENCE | nvarchar(100) | IN | Reference |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @TRANSFERBASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate |
| @TRANSFEREXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @TRANSFERBANKACCOUNTBASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate |
| @TRANSFERBANKACCOUNTEXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTADJUSTMENT2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ADJUSTMENTDATE datetime,
@AMOUNT decimal(19,4),
@REFERENCE nvarchar(100),
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@TRANSFERBASEEXCHANGERATEID uniqueidentifier,
@TRANSFEREXCHANGERATE decimal(20,8),
@TRANSFERBANKACCOUNTBASEEXCHANGERATEID uniqueidentifier,
@TRANSFERBANKACCOUNTEXCHANGERATE decimal(20,8)
) as
set nocount on;
-- Check GL business rule for this account system and set to 'Do not post' if needed.
-- ****
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.BANKACCOUNTTRANSACTION as BAT
inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
where BAT.ID = @ID;
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
set @POSTSTATUSCODE = 2 -- Do not post
-- ****
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @ADJUSTMENTTYPECODE tinyint;
declare @ISORIGINALADJUSTMENT bit;
declare @ORIGINALAMOUNT money;
declare @ORIGINALREFERENCE nvarchar(100);
declare @ORIGINALPOSTSTATUSCODE tinyint;
declare @ORIGINALBASEEXCHANGERATEID uniqueidentifier;
declare @ORIGINALEXCHANGERATE decimal(20,8);
declare @ORIGINALTRANSFERBASEEXCHANGERATEID uniqueidentifier;
declare @HADBANKACCOUNTTRANSFERSPOTRATE bit = 0;
declare @HADORIGINALTOBASESPOTRATE bit = 0;
declare @HADCOPYTOBASESPOTRATE bit = 0;
declare @ORIGINALTRANSFEREXCHANGERATE decimal(20,8);
declare @ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID uniqueidentifier;
declare @ORIGINALTRANSFERBANKACCOUNTEXCHANGERATE decimal(20,8);
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @RECALCULATEDISTRIBUTION bit;
declare @ORIGINALORGEXCHANGERATEID uniqueidentifier;
declare @ORIGINALTRANSFERORGEXCHANGERATEID uniqueidentifier;
select
@ADJUSTMENTTYPECODE = BAT.TRANSACTIONTYPECODE,
@ISORIGINALADJUSTMENT = BAA.ISORIGINALADJUSTMENT,
@ORIGINALAMOUNT = BAT.TRANSACTIONAMOUNT,
@ORIGINALREFERENCE = BAT.REFERENCE,
@ORIGINALPOSTSTATUSCODE = BAT.POSTSTATUSCODE,
@ORIGINALBASEEXCHANGERATEID = ORIGINALTOBASE.ID,
@ORIGINALEXCHANGERATE = ORIGINALTOBASE.RATE,
@ORIGINALTRANSFERBASEEXCHANGERATEID = COPYTOBASE.ID,
@ORIGINALTRANSFEREXCHANGERATE = COPYTOBASE.RATE,
@ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID = BANKACCOUNTTRANSFER.ID,
@ORIGINALTRANSFERBANKACCOUNTEXCHANGERATE = BANKACCOUNTTRANSFER.RATE,
@TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID,
@BASECURRENCYID = BAT.BASECURRENCYID,
@ORIGINALORGEXCHANGERATEID = BAT.ORGANIZATIONEXCHANGERATEID,
@ORIGINALTRANSFERORGEXCHANGERATEID = BAT_COPY.ORGANIZATIONEXCHANGERATEID,
@HADBANKACCOUNTTRANSFERSPOTRATE = case when BANKACCOUNTTRANSFER.TYPECODE = 2 then 1 else 0 end,
@HADORIGINALTOBASESPOTRATE = case when ORIGINALTOBASE.TYPECODE = 2 then 1 else 0 end,
@HADCOPYTOBASESPOTRATE = case when COPYTOBASE.TYPECODE = 2 then 1 else 0 end
from dbo.BANKACCOUNTTRANSACTION as BAT
inner join dbo.BANKACCOUNTADJUSTMENT as BAA on BAT.ID = BAA.ID
inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
left outer join dbo.CURRENCYEXCHANGERATE as BANKACCOUNTTRANSFER on BANKACCOUNTTRANSFER.ID = BAA.EXCHANGERATEID
left outer join dbo.CURRENCYEXCHANGERATE as ORIGINALTOBASE on ORIGINALTOBASE.ID = BAT.BASEEXCHANGERATEID
left outer join dbo.BANKACCOUNTTRANSACTION as BAT_COPY on BAT_COPY.ID = BAA.TRANSFERADJUSTMENTID
left outer join dbo.BANKACCOUNT as BA_COPY on BA_COPY.ID = BAT_COPY.BANKACCOUNTID
left outer join dbo.CURRENCYEXCHANGERATE as COPYTOBASE on COPYTOBASE.ID = BAT_COPY.BASEEXCHANGERATEID
where BAT.ID = @ID;
if (@BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
and ((not @ORIGINALEXCHANGERATE is null) and @ORIGINALEXCHANGERATE = @EXCHANGERATE))
set @BASEEXCHANGERATEID = @ORIGINALBASEEXCHANGERATEID;
if (@TRANSFERBASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
and ((not @ORIGINALTRANSFEREXCHANGERATE is null) and @ORIGINALTRANSFEREXCHANGERATE = @TRANSFEREXCHANGERATE))
set @TRANSFERBASEEXCHANGERATEID = @ORIGINALTRANSFERBASEEXCHANGERATEID;
if (@TRANSFERBANKACCOUNTBASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
and ((not @ORIGINALTRANSFERBANKACCOUNTEXCHANGERATE is null) and @ORIGINALTRANSFERBANKACCOUNTEXCHANGERATE = @TRANSFERBANKACCOUNTEXCHANGERATE))
set @TRANSFERBANKACCOUNTBASEEXCHANGERATEID = @ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID;
declare @DUMMYID uniqueidentifier = newid();
if isnull(@BASEEXCHANGERATEID, @DUMMYID) != isnull(@ORIGINALBASEEXCHANGERATEID, @DUMMYID)
or isnull(@TRANSFERBASEEXCHANGERATEID, @DUMMYID) != isnull(@ORIGINALTRANSFERBASEEXCHANGERATEID, @DUMMYID)
or isnull(@TRANSFERBANKACCOUNTBASEEXCHANGERATEID, @DUMMYID) != isnull(@ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID, @DUMMYID)
set @RECALCULATEDISTRIBUTION = 1;
begin try
--Add a spot exchange rate if an existing rate hasn't
--been selected, the base and transaction currencies are different, and the rate
--entered isn't zero (which indicates that the user wants to enter the record without a rate).
if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @BASEEXCHANGERATEID = newid()
insert into dbo.CURRENCYEXCHANGERATE(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@EXCHANGERATE,
@ADJUSTMENTDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
declare @BASEAMOUNT money;
declare @ORGANIZATIONAMOUNT money;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
declare @ORGCURRENCYNAME nvarchar(100);
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID, @ORGCURRENCYNAME = NAME from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @ADJUSTMENTDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
if (@ORGAMOUNTORIGINCODE = 1 and @TRANSACTIONCURRENCYID <> @ORGCURRENCYID) or (@ORGAMOUNTORIGINCODE = 0 and @BASECURRENCYID <> @ORGCURRENCYID)
if @ORGANIZATIONEXCHANGERATEID is null
begin
declare @CURRENCYNAME nvarchar(100);
select @CURRENCYNAME = NAME from dbo.CURRENCY where (ID = @TRANSACTIONCURRENCYID and @ORGAMOUNTORIGINCODE = 1) or (ID = @BASECURRENCYID and @ORGAMOUNTORIGINCODE = 0)
declare @errormessage nvarchar(200);
set @errormessage = 'A corporate exchange rate does not exist for ' + @CURRENCYNAME + ' to ' + @ORGCURRENCYNAME
raiserror (@errormessage, 16, 1);
end
else if isnull(@ORIGINALORGEXCHANGERATEID, @DUMMYID) != isnull(@ORGANIZATIONEXCHANGERATEID, @DUMMYID)
set @RECALCULATEDISTRIBUTION = 1;
-- handle updating the data
if @ISORIGINALADJUSTMENT = 1
begin
update dbo.BANKACCOUNTTRANSACTION set
TRANSACTIONDATE = @ADJUSTMENTDATE,
AMOUNT = @BASEAMOUNT,
REFERENCE = @REFERENCE,
POSTSTATUSCODE = @POSTSTATUSCODE,
POSTDATE = @POSTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT = @AMOUNT,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where ID = @ID;
end
else
update dbo.BANKACCOUNTTRANSACTION set
TRANSACTIONDATE = @ADJUSTMENTDATE,
AMOUNT = @BASEAMOUNT,
REFERENCE = @REFERENCE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
BASECURRENCYID = @BASECURRENCYID,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT = @AMOUNT,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where ID = @ID
if @ADJUSTMENTTYPECODE in (32, 33)
begin
declare @TRANSFERADJUSTMENTID uniqueidentifier;
declare @AMOUNT_BANKB money;
declare @TRANSACTIONCURRENCYID_BANKB uniqueidentifier;
declare @BASECURRENCYID_BANKB uniqueidentifier;
declare @BASEAMOUNT_BANKB money;
declare @ORGANIZATIONAMOUNT_BANKB money;
declare @ORGANIZATIONEXCHANGERATEID_BANKB uniqueidentifier;
select @TRANSFERADJUSTMENTID = TRANSFERADJUSTMENTID from BANKACCOUNTADJUSTMENT where ID = @ID;
select
@TRANSACTIONCURRENCYID_BANKB = BA.TRANSACTIONCURRENCYID,
@BASECURRENCYID_BANKB = BAT.BASECURRENCYID
from dbo.BANKACCOUNTTRANSACTION as BAT
inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
where BAT.ID = @TRANSFERADJUSTMENTID
-- If the transaction currency of Bank A is different from the transaction currency of Bank B then TRANSFERBANKACCOUNTBASEEXCHANGERATEID is required
-- when entering the original adjustment.
if (@TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID_BANKB) and (@TRANSFERBANKACCOUNTBASEEXCHANGERATEID is null)
raiserror ('A transfer in or transfer out adjustment with multicurrency where the transaction currencies are different requires a transfer bank account base exchange rate ID.', 16, 1);
if (@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID_BANKB) and (@TRANSFERBANKACCOUNTBASEEXCHANGERATEID is not null)
set @TRANSFERBANKACCOUNTBASEEXCHANGERATEID = null
if @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID_BANKB
set @AMOUNT_BANKB = @AMOUNT
else
begin
--Add a spot exchange rate if an existing rate hasn't
--been selected, the base and transaction currencies are different, and the rate
--entered isn't zero (which indicates that the user wants to enter the record without a rate).
if @TRANSFERBANKACCOUNTBASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @TRANSFERBANKACCOUNTBASEEXCHANGERATEID = newid()
insert into dbo.CURRENCYEXCHANGERATE(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
@TRANSFERBANKACCOUNTBASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@TRANSACTIONCURRENCYID_BANKB,
@TRANSFERBANKACCOUNTEXCHANGERATE,
@ADJUSTMENTDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
set @AMOUNT_BANKB = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @TRANSFERBANKACCOUNTBASEEXCHANGERATEID)
end
--Add a spot exchange rate if an existing rate hasn't
--been selected, the base and transaction currencies are different, and the rate
--entered isn't zero (which indicates that the user wants to enter the record without a rate).
if @TRANSFERBASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @TRANSFERBASEEXCHANGERATEID = newid()
insert into dbo.CURRENCYEXCHANGERATE(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
@TRANSFERBASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID_BANKB,
@BASECURRENCYID_BANKB,
@TRANSFEREXCHANGERATE,
@ADJUSTMENTDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT_BANKB, @ADJUSTMENTDATE, @BASECURRENCYID_BANKB, @TRANSFERBASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID_BANKB output, @BASEAMOUNT_BANKB output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT_BANKB output, @ORGANIZATIONEXCHANGERATEID_BANKB output, 1;
if (@ORGAMOUNTORIGINCODE = 1 and @TRANSACTIONCURRENCYID_BANKB <> @ORGCURRENCYID) or (@ORGAMOUNTORIGINCODE = 0 and @BASECURRENCYID_BANKB <> @ORGCURRENCYID)
if @ORGANIZATIONEXCHANGERATEID_BANKB is null
begin
select @CURRENCYNAME = NAME from dbo.CURRENCY where (ID = @TRANSACTIONCURRENCYID_BANKB and @ORGAMOUNTORIGINCODE = 1) or (ID = @BASECURRENCYID_BANKB and @ORGAMOUNTORIGINCODE = 0)
set @errormessage = 'A corporate exchange rate does not exist for ' + @CURRENCYNAME + ' to ' + @ORGCURRENCYNAME
raiserror (@errormessage, 16, 1);
end
else if isnull(@ORIGINALTRANSFERORGEXCHANGERATEID, @DUMMYID) != isnull(@ORGANIZATIONEXCHANGERATEID_BANKB, @DUMMYID)
set @RECALCULATEDISTRIBUTION = 1;
update dbo.BANKACCOUNTTRANSACTION set
TRANSACTIONDATE = @ADJUSTMENTDATE,
AMOUNT = @BASEAMOUNT_BANKB,
REFERENCE = @REFERENCE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
BASECURRENCYID = @BASECURRENCYID_BANKB,
ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT_BANKB,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID_BANKB,
TRANSACTIONAMOUNT = @AMOUNT_BANKB,
BASEEXCHANGERATEID = @TRANSFERBASEEXCHANGERATEID
where ID = @TRANSFERADJUSTMENTID
update dbo.BANKACCOUNTADJUSTMENT set
EXCHANGERATEID = @TRANSFERBANKACCOUNTBASEEXCHANGERATEID
where ID = @ID;
end
if @ORIGINALAMOUNT != @AMOUNT or @RECALCULATEDISTRIBUTION = 1 or (@ORIGINALPOSTSTATUSCODE != @POSTSTATUSCODE and @POSTSTATUSCODE = 1)
begin
delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION where BANKACCOUNTTRANSACTIONID = @ID or BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID) and POSTSTATUSCODE >= 1;
delete from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION where BANKACCOUNTTRANSACTIONID = @ID or BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID;
declare @TEMPID uniqueidentifier;
set @TEMPID = CASE WHEN @ISORIGINALADJUSTMENT = 1 THEN @ID ELSE @TRANSFERADJUSTMENTID END;
if @POSTSTATUSCODE = 1
exec dbo.USP_BANKACCOUNTADJUSTMENT_CREATEDISTRIBUTION @TEMPID, @CHANGEAGENTID, @CURRENTDATE;
end
else if @ORIGINALREFERENCE != @REFERENCE
begin
declare @References table (ID uniqueidentifier, Reference nvarchar(255))
insert into @References (ID, Reference)
select GLTRANSACTION.ID,
case when len(@REFERENCE) > 0 then @REFERENCE
when BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE = 16 then 'Bank Adjustment - Deposit'
when BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE = 17 then 'Bank Adjustment - Payment'
when GLTRANSACTION.TRANSACTIONTYPECODE = 0 then 'Bank Adjustment - Transfer In'
else 'Bank Adjustment - Transfer Out'
end
from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
where (
BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @ID
or BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID
)
and (
(
-- Non-system generated, adjustment
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
)
or
(
-- Non-system generated, transfer, same currencies between banks
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID_BANKB
)
or
(
-- Non-system generated, transfer, difference currencies between banks
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and @TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID_BANKB
)
or
(
-- System generated, transfer, different currencies between banks, non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION = 1
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = 0
and @TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID_BANKB
and not BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID is null
)
);
update dbo.GLTRANSACTION
set REFERENCE = (select REFERENCE from @References where ID = GLTRANSACTION.ID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID in (select ID from @References)
delete from @References
insert into @References (ID, Reference)
select BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID,
case when len(@REFERENCE) > 0 then @REFERENCE
when BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE = 16 then 'Bank Adjustment - Deposit'
when BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE = 17 then 'Bank Adjustment - Payment'
when GLTRANSACTION.TRANSACTIONTYPECODE = 0 then 'Bank Adjustment - Transfer In'
else 'Bank Adjustment - Transfer Out'
end
from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
where (
BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @ID
or BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID
)
and (
(
-- Non-system generated, adjustment
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
)
or
(
-- Non-system generated, transfer, same currencies between banks
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID_BANKB
)
or
(
-- Non-system generated, transfer, difference currencies between banks
GLTRANSACTION.SYSTEMDISTRIBUTION = 0
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and @TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID_BANKB
)
or
(
-- System generated, transfer, different currencies between banks, non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION = 1
and not BANKACCOUNTTRANSACTION.TRANSFERBANKACCOUNTID is null
and BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONTYPECODE = 0
and @TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID_BANKB
and not BANKACCOUNTTRANSACTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID is null
)
);
update dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
set REFERENCE = (select REFERENCE from @References where ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.ID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID in (select ID from @References)
end
if @ORIGINALPOSTSTATUSCODE != @POSTSTATUSCODE
if @POSTSTATUSCODE !=2
--kwb Not ANSI syntax
/*
update dbo.GLTRANSACTION
set GLTRANSACTION.POSTSTATUSCODE = BANKACCOUNTTRANSACTION.POSTSTATUSCODE,
CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
where BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @ID
or BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID;
*/
update dbo.GLTRANSACTION
set POSTSTATUSCODE = (select BANKACCOUNTTRANSACTION.POSTSTATUSCODE
from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
where BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
and (BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @ID
or BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID)),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where GLTRANSACTION.ID in (select BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID
where BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @ID
or BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID = @TRANSFERADJUSTMENTID)
else
begin
delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION where BANKACCOUNTTRANSACTIONID = @ID);
delete from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION where BANKACCOUNTTRANSACTIONID = @ID;
end
if @ORIGINALBASEEXCHANGERATEID <> @BASEEXCHANGERATEID and @HADORIGINALTOBASESPOTRATE = 1
delete from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @ORIGINALBASEEXCHANGERATEID;
if @ORIGINALTRANSFERBASEEXCHANGERATEID <> @TRANSFERBASEEXCHANGERATEID and @HADCOPYTOBASESPOTRATE = 1
delete from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @ORIGINALTRANSFERBASEEXCHANGERATEID;
if @ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID <> @TRANSFERBANKACCOUNTBASEEXCHANGERATEID and @HADBANKACCOUNTTRANSFERSPOTRATE = 1
delete from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @ORIGINALTRANSFERBANKACCOUNTBASEEXCHANGERATEID;
update dbo.BANKACCOUNTADJUSTMENT set
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;