USP_DATAFORMTEMPLATE_ADD_BANKACCOUNTADJUSTMENT
The save procedure used by the add dataform template "Bank Account Adjustment Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @BANKACCOUNTID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @BANKACCOUNTADJUSTMENTCATEGORYID | uniqueidentifier | IN | Category |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @AMOUNT | decimal(19, 4) | IN | Amount |
| @ADJUSTMENTTYPECODE | tinyint | IN | Type |
| @REFERENCE | nvarchar(100) | IN | Reference |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @TRANSFERBANKACCOUNTID | uniqueidentifier | IN | Transfer account |
| @TRANSFERADJUSTMENTID | uniqueidentifier | IN | Transfer adjustment ID |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | Account System |
| @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_ADD_BANKACCOUNTADJUSTMENT
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@BANKACCOUNTID uniqueidentifier,
@BANKACCOUNTADJUSTMENTCATEGORYID uniqueidentifier = null,
@ADJUSTMENTDATE datetime = null,
@AMOUNT decimal(19,4) = null,
@ADJUSTMENTTYPECODE tinyint = 1,
@REFERENCE nvarchar(100) = '',
@POSTSTATUSCODE tinyint = 1,
@POSTDATE datetime = null,
@TRANSFERBANKACCOUNTID uniqueidentifier = null,
@TRANSFERADJUSTMENTID uniqueidentifier = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = null,
@TRANSFERBASEEXCHANGERATEID uniqueidentifier = null,
@TRANSFEREXCHANGERATE decimal(20,8) = null,
@TRANSFERBANKACCOUNTBASEEXCHANGERATEID uniqueidentifier = null,
@TRANSFERBANKACCOUNTEXCHANGERATE decimal(20,8) = null
)
as
set nocount on;
-- Check GL business rule for this account system and set to 'Do not post' if needed.
-- ####
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID)
if @ALLOWGLDISTRIBUTIONS = 0
set @POSTSTATUSCODE = 2 -- Do not post
else
begin
-- Added a new business rule to disable general leger which will allow a user
-- to save a bank account without any cash account information. If a user
-- enables general ledger with a bank account that has no cash account information
-- we need to stop them from adding any 'not yet posted' adjustments.
-- ****
declare @GLACCOUNTID uniqueidentifier
declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier
select
@GLACCOUNTID=GLACCOUNTID,
@PDACCOUNTSEGMENTVALUEID=PDACCOUNTSEGMENTVALUEID
from dbo.BANKACCOUNT
where ID = @BANKACCOUNTID
if @GLACCOUNTID is null and @PDACCOUNTSEGMENTVALUEID is null and @POSTSTATUSCODE = 1
raiserror('ERR_MUSTHAVECASHACCOUNTINFORMATION', 16, 1);
-- ****
end
-- ####
--You can not do a 'transfer in' if multicurrency is enabled since exchange rates
--are not bi-directional. You start with the transaction you are on and exchange from it.
--A transfer in would start with another transaction and exchange to the current transaction
--which is not allowed.
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency')=1
if @ADJUSTMENTTYPECODE = 33
raiserror ('Can not create a transfer in when multicurrency is turned on.', 16, 1);
if @ID is null
set @ID = newid()
if @ADJUSTMENTTYPECODE in (32, 33) and @TRANSFERADJUSTMENTID is null --assign new GUID for transfer in/out transaction
set @TRANSFERADJUSTMENTID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @TRANSACTIONFLAGCODE int
set @TRANSACTIONFLAGCODE = 3 --Adjustment
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
-- Multicurrency --
declare @TRANSACTIONCURRENCYID uniqueidentifier
declare @BASECURRENCYID uniqueidentifier
-- @PDACCOUNTSYSTEMID can be set by the user but is should not be...this parameter has to remain due to
-- binary compatibility. We will always set it to the PDACCOUNTSYSTEMID of the bank account.
-- TRANSACTIONCURRENCYID is defined by the bank so we always retrieve it from the bank.
-- BASECURRENCYID is pulled from the PDACCOUNTSYSTEM of the bank.
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@BASECURRENCYID = BASECURRENCYID
from dbo.BANKACCOUNT as BA
inner join dbo.PDACCOUNTSYSTEM as PAS on BA.PDACCOUNTSYSTEMID = PAS.ID
inner join dbo.CURRENCYSET as CS on PAS.CURRENCYSETID = CS.ID
where BA.ID = @BANKACCOUNTID
--Get Multicurrency values.
declare @BASEAMOUNT money;
declare @ORGANIZATIONAMOUNT money;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
--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 @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
if @ADJUSTMENTTYPECODE in (32, 33)
-- Is a transfer not a regular adjustment.
begin
-- Using PDACCOUNTSYSTEID as they account system of Bank A (no need for an extra variable).
declare @PDACCOUNTSYSTEMID_BANKB uniqueidentifier
declare @TRANSACTIONCURRENCYID_BANKB uniqueidentifier
declare @BASECURRENCYID_BANKB uniqueidentifier
-- @PDACCOUNTSYSTEMID can be set by the user but is should not be...this parameter has to remain due to
-- binary compatibility. We will always set it to the PDACCOUNTSYSTEMID of the bank account.
-- TRANSACTIONCURRENCYID is defined by the bank so we always retrieve it from bank b (@TRANSFERBANKACCOUNTID).
-- BASECURRENCYID is pulled from the PDACCOUNTSYSTEM of bank b (@TRANSFERBANKACCOUNTID).
select @PDACCOUNTSYSTEMID_BANKB = PDACCOUNTSYSTEMID,
@TRANSACTIONCURRENCYID_BANKB = TRANSACTIONCURRENCYID,
@BASECURRENCYID_BANKB = BASECURRENCYID
from dbo.BANKACCOUNT as BA
inner join dbo.PDACCOUNTSYSTEM as PAS on BA.PDACCOUNTSYSTEMID = PAS.ID
inner join dbo.CURRENCYSET as CS on PAS.CURRENCYSETID = CS.ID
where BA.ID = @TRANSFERBANKACCOUNTID
--If the accounting system of Bank A (BANKACCOUNTID) is different from the accounting system of Bank B (TRANSFERBANKACCOUNTID) raise an error.
if @PDACCOUNTSYSTEMID <> @PDACCOUNTSYSTEMID_BANKB
raiserror ('Can not transfer between two banks of different accounting systems.', 16, 1);
-- 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 ('An exchange rate from the transfer out bank account currency to the transfer in bank account currency is required.', 16, 1);
if (@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID_BANKB) and (@TRANSFERBANKACCOUNTBASEEXCHANGERATEID is not null)
set @TRANSFERBANKACCOUNTBASEEXCHANGERATEID = null
declare @AMOUNT_BANKB money;
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
--Get Multicurrency values.
declare @BASEAMOUNT_BANKB money;
declare @ORGANIZATIONAMOUNT_BANKB money;
declare @ORGANIZATIONEXCHANGERATEID_BANKB uniqueidentifier;
--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
end
-- End Multicurrency --
-- handle inserting the data
insert into dbo.BANKACCOUNTTRANSACTION
(ID
,BANKACCOUNTID
,TRANSACTIONNUMBER
,AMOUNT
,TRANSACTIONDATE
,TRANSACTIONTYPECODE
,REFERENCE
,TRANSACTIONFLAGCODE
,POSTSTATUSCODE
,POSTDATE
,TRANSFERBANKACCOUNTID
,TRANSACTIONAMOUNT
,BASEEXCHANGERATEID
,BASECURRENCYID
,ORGANIZATIONAMOUNT
,ORGANIZATIONEXCHANGERATEID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID
,@BANKACCOUNTID
,dbo.UFN_BANKACCOUNTTRANSACTION_GETNEXTNUMBER(@BANKACCOUNTID, @TRANSACTIONFLAGCODE)
,@BASEAMOUNT
,@ADJUSTMENTDATE
,@ADJUSTMENTTYPECODE
,@REFERENCE
,@TRANSACTIONFLAGCODE
,@POSTSTATUSCODE
,@POSTDATE
,@TRANSFERBANKACCOUNTID
,@AMOUNT
,@BASEEXCHANGERATEID
,@BASECURRENCYID
,@ORGANIZATIONAMOUNT
,@ORGANIZATIONEXCHANGERATEID
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.BANKACCOUNTADJUSTMENT
(ID
,BANKACCOUNTADJUSTMENTCATEGORYID
,TRANSFERADJUSTMENTID
,ISORIGINALADJUSTMENT
,EXCHANGERATEID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID
,@BANKACCOUNTADJUSTMENTCATEGORYID
,@TRANSFERADJUSTMENTID
,1
,@TRANSFERBANKACCOUNTBASEEXCHANGERATEID
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
if @ADJUSTMENTTYPECODE in (32, 33) --insert transfer in/out transactions
begin
insert into dbo.BANKACCOUNTTRANSACTION
(ID
,BANKACCOUNTID
,TRANSACTIONNUMBER
,AMOUNT
,TRANSACTIONDATE
,TRANSACTIONTYPECODE
,REFERENCE
,TRANSACTIONFLAGCODE
,POSTSTATUSCODE
,POSTDATE
,TRANSFERBANKACCOUNTID
,TRANSACTIONAMOUNT
,BASEEXCHANGERATEID
,BASECURRENCYID
,ORGANIZATIONAMOUNT
,ORGANIZATIONEXCHANGERATEID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@TRANSFERADJUSTMENTID
,@TRANSFERBANKACCOUNTID
,dbo.UFN_BANKACCOUNTTRANSACTION_GETNEXTNUMBER(@TRANSFERBANKACCOUNTID, @TRANSACTIONFLAGCODE)
,@BASEAMOUNT_BANKB
,@ADJUSTMENTDATE
,case when @ADJUSTMENTTYPECODE = 32 then 33 else 32 end
,@REFERENCE
,@TRANSACTIONFLAGCODE
,2
,null
,@BANKACCOUNTID
,@AMOUNT_BANKB
,@TRANSFERBASEEXCHANGERATEID
,@BASECURRENCYID_BANKB
,@ORGANIZATIONAMOUNT_BANKB
,@ORGANIZATIONEXCHANGERATEID_BANKB
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
insert into dbo.BANKACCOUNTADJUSTMENT
(ID, BANKACCOUNTADJUSTMENTCATEGORYID, TRANSFERADJUSTMENTID, ISORIGINALADJUSTMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@TRANSFERADJUSTMENTID, null, @ID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if @POSTSTATUSCODE = 1
exec dbo.USP_BANKACCOUNTADJUSTMENT_CREATEDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE, @PDACCOUNTSYSTEMID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0