USP_DATAFORMTEMPLATE_ADD_DEPOSIT
The save procedure used by the add dataform template "Deposit Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @BANKACCOUNTID | uniqueidentifier | IN | Bank account |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @REFERENCE | nvarchar(100) | IN | Reference |
| @DEPOSITDATE | datetime | IN | Deposit date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @POSTDATE | datetime | IN | Post date |
| @PROJECTEDNUMBEROFPAYMENTS | int | IN | Number of payments |
| @PROJECTEDAMOUNT | money | IN | Amount |
| @STATUSCODE | tinyint | IN | Status |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Payment currency |
| @TRANSACTIONEXCHANGERATEID | uniqueidentifier | IN | Payment exchange rate |
| @TRANSACTIONEXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @BASEEXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DEPOSIT
(
@ID uniqueidentifier = null output,
@BANKACCOUNTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@REFERENCE nvarchar(100) = '',
@DEPOSITDATE datetime = null,
@POSTSTATUSCODE tinyint = 1,
@POSTDATE datetime = null,
@PROJECTEDNUMBEROFPAYMENTS int = 0,
@PROJECTEDAMOUNT money = null,
@STATUSCODE tinyint = 1
,@TRANSACTIONCURRENCYID uniqueidentifier = null
,@TRANSACTIONEXCHANGERATEID uniqueidentifier = null
,@TRANSACTIONEXCHANGERATE decimal(20,8) = null
,@BASEEXCHANGERATEID uniqueidentifier = null
,@BASEEXCHANGERATE decimal(20,8) = null
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @TRANSACTIONFLAGCODE int
set @TRANSACTIONFLAGCODE = 1 --Deposit
declare @TRANSACTIONTYPECODE int
set @TRANSACTIONTYPECODE = 8 --Deposit
declare @BANKTRANSACTIONID uniqueidentifier
begin try
declare @BANKACCOUNTCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID,
@BANKACCOUNTCURRENCYID = BA.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
-- 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
begin
set @POSTSTATUSCODE = 2; -- Do not post
set @POSTDATE = null; -- bug 140415
end
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
-- ####
--Get Multicurrency values.
declare @BANKACCOUNTTRANSACTIONAMOUNT money;
declare @BASEAMOUNT money;
declare @ORGANIZATIONAMOUNT money;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
if @TRANSACTIONCURRENCYID <> @BANKACCOUNTCURRENCYID
--Add a spot exchange rate if an existing rate hasn't
--been selected, the bank 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 @TRANSACTIONEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @TRANSACTIONEXCHANGERATEID = newid()
insert into dbo.CURRENCYEXCHANGERATE(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values(
@TRANSACTIONEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BANKACCOUNTCURRENCYID,
@TRANSACTIONEXCHANGERATE,
@DEPOSITDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @BANKACCOUNTCURRENCYID <> @BASECURRENCYID
--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,
@BANKACCOUNTCURRENCYID,
@BASECURRENCYID,
@BASEEXCHANGERATE,
@DEPOSITDATE,
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 0, @DEPOSITDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @BANKACCOUNTCURRENCYID output, @BANKACCOUNTTRANSACTIONAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
if (@ORGAMOUNTORIGINCODE = 1 and @BANKACCOUNTCURRENCYID <> @ORGCURRENCYID) or (@ORGAMOUNTORIGINCODE = 0 and @BASECURRENCYID <> @ORGCURRENCYID)
if @ORGANIZATIONEXCHANGERATEID is null
begin
declare @CURRENCYNAME nvarchar(100);
select @CURRENCYNAME = NAME from dbo.CURRENCY where (ID = @BANKACCOUNTCURRENCYID 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
-- create the bank transaction
-- depositnumber will be added soon (design will update the design doc)
insert into dbo.BANKACCOUNTTRANSACTION
(ID, BANKACCOUNTID, TRANSACTIONNUMBER, TRANSACTIONDATE, TRANSACTIONTYPECODE, REFERENCE, TRANSACTIONFLAGCODE, POSTSTATUSCODE, POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, BASECURRENCYID)
values
(@ID, @BANKACCOUNTID, dbo.UFN_BANKACCOUNTTRANSACTION_GETNEXTNUMBER(@BANKACCOUNTID, @TRANSACTIONFLAGCODE), @DEPOSITDATE, @TRANSACTIONTYPECODE, @REFERENCE, @TRANSACTIONFLAGCODE, @POSTSTATUSCODE, case @POSTSTATUSCODE when 2 then null else @POSTDATE end, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @BASECURRENCYID)
-- create the bank account deposit record
insert into dbo.BANKACCOUNTDEPOSIT
(ID, PROJECTEDNUMBEROFPAYMENTS, PROJECTEDAMOUNT, STATUSCODE, TRANSACTIONCURRENCYID, TRANSACTIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, coalesce(@PROJECTEDNUMBEROFPAYMENTS, 0), @PROJECTEDAMOUNT, @STATUSCODE, @TRANSACTIONCURRENCYID, @TRANSACTIONEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0