USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSITCORRECTION
The save procedure used by the edit dataform template "Bank Account Deposit Correction Edit 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. |
| @DATE | datetime | IN | Date |
| @REFERENCE | nvarchar(100) | IN | Reference |
| @PAYMENTMETHODCODE | tinyint | IN | Method |
| @AMOUNT | money | IN | Amount |
| @CORRECTIONTYPECODE | tinyint | IN | Correction type |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @BASEEXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSITCORRECTION
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@DATE datetime,
@REFERENCE nvarchar(100),
@PAYMENTMETHODCODE tinyint,
@AMOUNT money,
@CORRECTIONTYPECODE tinyint,
@BASEEXCHANGERATEID uniqueidentifier,
@BASEEXCHANGERATE decimal(20,8)
)
as
set nocount on;
declare @CURRENTDATE datetime;
declare @ORIGINALPAYMENTMETHOD tinyint;
declare @ORIGINALCORRECTIONTYPE tinyint;
declare @ORIGINALAMOUNT money;
declare @ORIGINALREFERENCE nvarchar(100);
declare @ORIGINALBASEEXCHANGERATEID uniqueidentifier;
declare @ORIGINALBASEEXCHANGERATE decimal(20,8);
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BANKACCOUNTTRANSACTIONCURRENCYID uniqueidentifier;
declare @ORIGINALORGEXCHANGERATEID uniqueidentifier;
declare @ORIGINALEXCHANGERATEISSPOTRATE bit = 0;
declare @POSTSTATUSCODE tinyint;
begin try
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
select
@ORIGINALPAYMENTMETHOD = PAYMENTMETHODCODE,
@ORIGINALCORRECTIONTYPE = CORRECTIONTYPECODE,
@ORIGINALAMOUNT = BADC.TRANSACTIONAMOUNT,
@ORIGINALREFERENCE = BADC.REFERENCE,
@ORIGINALBASEEXCHANGERATEID = ORIGINALTOBASE.ID,
@ORIGINALBASEEXCHANGERATE = ORIGINALTOBASE.RATE,
@TRANSACTIONCURRENCYID = BAD.TRANSACTIONCURRENCYID,
@BASECURRENCYID = BADC.BASECURRENCYID,
@BANKACCOUNTTRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID,
@ORIGINALORGEXCHANGERATEID = BADC.ORGANIZATIONEXCHANGERATEID,
@ORIGINALEXCHANGERATEISSPOTRATE = CASE WHEN ORIGINALTOBASE.TYPECODE = 2 THEN 1 ELSE 0 END
,@POSTSTATUSCODE = BAT.POSTSTATUSCODE
from dbo.BANKACCOUNTDEPOSITCORRECTION as BADC
inner join dbo.BANKACCOUNTDEPOSIT as BAD on BADC.DEPOSITID = BAD.ID
inner join dbo.BANKACCOUNTTRANSACTION as BAT on BAD.ID = BAT.ID
inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
left outer join dbo.CURRENCYEXCHANGERATE as ORIGINALTOBASE on ORIGINALTOBASE.ID = BADC.BASEEXCHANGERATEID
where BADC.ID = @ID;
if (@BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
and ((not @ORIGINALBASEEXCHANGERATE is null) and @ORIGINALBASEEXCHANGERATE = @BASEEXCHANGERATE))
set @BASEEXCHANGERATEID = @ORIGINALBASEEXCHANGERATEID;
--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,
@BASEEXCHANGERATE,
@DATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
declare @BASEAMOUNT money;
declare @ORGAMOUNT money;
declare @ORGEXCHANGERATEID uniqueidentifier;
declare @ORGCURRENCYID uniqueidentifier;
declare @ORGAMOUNTORIGINCODE tinyint;
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, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGCURRENCYID output, @ORGAMOUNT output, @ORGEXCHANGERATEID output, 1;
if (@ORGAMOUNTORIGINCODE = 1 and @TRANSACTIONCURRENCYID <> @ORGCURRENCYID) or (@ORGAMOUNTORIGINCODE = 0 and @BASECURRENCYID <> @ORGCURRENCYID)
if @ORGEXCHANGERATEID 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
update
dbo.BANKACCOUNTDEPOSITCORRECTION
set
DATE = @DATE,
REFERENCE = @REFERENCE,
PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
AMOUNT = @BASEAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CORRECTIONTYPECODE = @CORRECTIONTYPECODE
,TRANSACTIONAMOUNT = @AMOUNT
,ORGANIZATIONAMOUNT = @ORGAMOUNT
,BASECURRENCYID = @BASECURRENCYID
,ORGANIZATIONEXCHANGERATEID = @ORGEXCHANGERATEID
,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where
ID = @ID;
declare @DUMMYID uniqueidentifier = newid();
if (@ORIGINALPAYMENTMETHOD != @PAYMENTMETHODCODE) or (@ORIGINALCORRECTIONTYPE != @CORRECTIONTYPECODE) or (@ORIGINALAMOUNT != @AMOUNT) or (isnull(@ORIGINALBASEEXCHANGERATEID, @DUMMYID) != isnull(@BASEEXCHANGERATEID, @DUMMYID)) or (isnull(@ORIGINALORGEXCHANGERATEID, @DUMMYID) != isnull(@ORGEXCHANGERATEID, @DUMMYID))
begin
delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION where BANKACCOUNTDEPOSITCORRECTIONID = @ID) and POSTSTATUSCODE = 1;
delete from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION where BANKACCOUNTDEPOSITCORRECTIONID = @ID;
if @POSTSTATUSCODE = 1
exec dbo.USP_BANKACCOUNTDEPOSITCORRECTION_CREATEDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
else if (@ORIGINALREFERENCE != @REFERENCE)
begin
if len(@REFERENCE) = 0
set @REFERENCE = 'Bank Account Deposit Correction';
declare @References table (ID uniqueidentifier, Reference nvarchar(255))
insert into @References (ID, Reference)
select GLTRANSACTION.ID,
CASE WHEN BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
THEN CASE WHEN GLTRANSACTION.TRANSACTIONTYPECODE = 0
THEN @REFERENCE
ELSE CASE WHEN len(BANKACCOUNTTRANSACTION.REFERENCE) = 0
THEN @REFERENCE
ELSE BANKACCOUNTTRANSACTION.REFERENCE END
END
ELSE CASE WHEN GLTRANSACTION.TRANSACTIONTYPECODE = 1
THEN @REFERENCE
ELSE CASE WHEN len(BANKACCOUNTTRANSACTION.REFERENCE) = 0
THEN @REFERENCE
ELSE BANKACCOUNTTRANSACTION.REFERENCE END
END
END
from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION
inner join dbo.BANKACCOUNTDEPOSITCORRECTION on BANKACCOUNTDEPOSITCORRECTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
where BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID = @ID and GLTRANSACTION.POSTSTATUSCODE = 1
and (
(
--Short, non-system generated
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
)
or
(
--Over, non-system generated
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
)
or
(
--Short, non-system generated, same currencies (between bank account and deposit/payment currency)
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
and @TRANSACTIONCURRENCYID = @BANKACCOUNTTRANSACTIONCURRENCYID
)
or
(
--Over, non-system generated, same currencies (between bank account and deposit/payment currency)
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
and @TRANSACTIONCURRENCYID = @BANKACCOUNTTRANSACTIONCURRENCYID
)
or
(
--Short, system generated, different currencies (between bank account and deposit/payment currency), non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION=1
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
and @TRANSACTIONCURRENCYID <> @BANKACCOUNTTRANSACTIONCURRENCYID
and not BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID is null
)
or
(
--Over, system generated, different currencies (between bank account and deposit/payment currency), non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION=1
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
and @TRANSACTIONCURRENCYID <> @BANKACCOUNTTRANSACTIONCURRENCYID
and not BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.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 BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ID,
CASE WHEN BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
THEN CASE WHEN GLTRANSACTION.TRANSACTIONTYPECODE = 0
THEN @REFERENCE
ELSE CASE WHEN len(BANKACCOUNTTRANSACTION.REFERENCE) = 0
THEN @REFERENCE
ELSE BANKACCOUNTTRANSACTION.REFERENCE END
END
ELSE CASE WHEN GLTRANSACTION.TRANSACTIONTYPECODE = 1
THEN @REFERENCE
ELSE CASE WHEN len(BANKACCOUNTTRANSACTION.REFERENCE) = 0
THEN @REFERENCE
ELSE BANKACCOUNTTRANSACTION.REFERENCE END
END
END
from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION
inner join dbo.BANKACCOUNTDEPOSITCORRECTION on BANKACCOUNTDEPOSITCORRECTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
where BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID = @ID and GLTRANSACTION.POSTSTATUSCODE = 1
and
(
(
--Short, non-system generated
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
)
or
(
--Over, non-system generated
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
)
or
(
--Short, non-system generated, same currencies (between bank account and deposit/payment currency)
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
and @TRANSACTIONCURRENCYID = @BANKACCOUNTTRANSACTIONCURRENCYID
)
or
(
--Over, non-system generated, same currencies (between bank account and deposit/payment currency)
GLTRANSACTION.SYSTEMDISTRIBUTION=0
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
and @TRANSACTIONCURRENCYID = @BANKACCOUNTTRANSACTIONCURRENCYID
)
or
(
--Short, system generated, different currencies (between bank account and deposit/payment currency), non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION=1
and GLTRANSACTION.TRANSACTIONTYPECODE = 1
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2)
and @TRANSACTIONCURRENCYID <> @BANKACCOUNTTRANSACTIONCURRENCYID
and not BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID is null
)
or
(
--Over, system generated, different currencies (between bank account and deposit/payment currency), non-gain/loss
GLTRANSACTION.SYSTEMDISTRIBUTION=1
and GLTRANSACTION.TRANSACTIONTYPECODE = 0
and BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3)
and @TRANSACTIONCURRENCYID <> @BANKACCOUNTTRANSACTIONCURRENCYID
and not BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.TRANSACTIONCURRENCYID is null
)
);
update dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION
set REFERENCE = (select Reference from @References where ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.ID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID in (select ID from @References)
end
if @ORIGINALBASEEXCHANGERATEID <> @BASEEXCHANGERATEID and @ORIGINALEXCHANGERATEISSPOTRATE = 1
delete from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @ORIGINALBASEEXCHANGERATEID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;