USP_DATAFORMTEMPLATE_ADD_MISCELLANEOUSPAYMENT
The save procedure used by the add dataform template "Miscellaneous Payment Add Data 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. |
| @PAYMENTSOURCE | nvarchar(100) | IN | Reference |
| @PAYMENTDATE | datetime | IN | Payment date |
| @AMOUNT | money | IN | Amount |
| @POSTDATE | datetime | IN | Post date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @DEPOSITID | uniqueidentifier | IN | Deposit |
| @PAYMENTMETHODCODE | tinyint | IN | Payment method |
| @REFERENCENUMBER | nvarchar(20) | IN | Reference number |
| @REFERENCEDATE | UDT_FUZZYDATE | IN | Reference date |
| @CHECKDATE | UDT_FUZZYDATE | IN | Check date |
| @CHECKNUMBER | nvarchar(20) | IN | Check number |
| @CARDHOLDERNAME | nvarchar(255) | IN | Name on card |
| @CREDITCARDNUMBER | nvarchar(4) | IN | Card number |
| @CREDITTYPECODEID | uniqueidentifier | IN | Card type |
| @AUTHORIZATIONCODE | nvarchar(20) | IN | Authorization code |
| @EXPIRESON | UDT_FUZZYDATE | IN | Expires on |
| @OTHERPAYMENTMETHODCODEID | uniqueidentifier | IN | Other method |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | Account system |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Transaction currency |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MISCELLANEOUSPAYMENT
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@PAYMENTSOURCE nvarchar(100) = '',
@PAYMENTDATE datetime,
@AMOUNT money,
@POSTDATE datetime = null,
@POSTSTATUSCODE tinyint = 1,
@DEPOSITID uniqueidentifier = null,
@PAYMENTMETHODCODE tinyint = null,
@REFERENCENUMBER nvarchar(20) = '',
@REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
@CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
@CHECKNUMBER nvarchar(20) = '',
@CARDHOLDERNAME nvarchar(255) = '',
@CREDITCARDNUMBER nvarchar(4) = '',
@CREDITTYPECODEID uniqueidentifier = null,
@AUTHORIZATIONCODE nvarchar(20) = '',
@EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
@OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@TRANSACTIONCURRENCYID uniqueidentifier = null,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = null,
@CURRENTAPPUSERID uniqueidentifier = 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 = getdate()
declare @DEPOSITREFERENCE nvarchar(100);
declare @BATRANSACTIONCURRENCYID uniqueidentifier;
set @TRANSACTIONCURRENCYID = nullif(@TRANSACTIONCURRENCYID,'00000000-0000-0000-0000-000000000000');
-- The deposit post info. overrides the misc. payment info.
declare @DEPOSIT_TRANSACTIONCURRENCYID uniqueidentifier
if @DEPOSITID is not null
begin
select
@POSTDATE = bat.POSTDATE,
@POSTSTATUSCODE = bat.POSTSTATUSCODE,
@DEPOSITREFERENCE = bat.REFERENCE,
@PDACCOUNTSYSTEMID = ba.PDACCOUNTSYSTEMID,
@DEPOSIT_TRANSACTIONCURRENCYID = bad.TRANSACTIONCURRENCYID,
@BATRANSACTIONCURRENCYID = ba.TRANSACTIONCURRENCYID
from
dbo.BANKACCOUNTTRANSACTION as bat
inner join dbo.BANKACCOUNT as ba on bat.BANKACCOUNTID = ba.ID
inner join dbo.BANKACCOUNTDEPOSIT as bad on bat.id = bad.id
where
bat.ID = @DEPOSITID
if @TRANSACTIONCURRENCYID is not null
begin
if @TRANSACTIONCURRENCYID <> @DEPOSIT_TRANSACTIONCURRENCYID
raiserror ('ERR_TRASACTIONCURRENCYID_MUSTMATCHDEPOSIT', 16, 1);
end
else
set @TRANSACTIONCURRENCYID = @DEPOSIT_TRANSACTIONCURRENCYID
end
-- 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
-- ****
-- Get the BaseCurrencyID correctly for the user.
-- ****
declare @BASECURRENCYID uniqueidentifier
-- BASECURRENCYID is pulled from the PDACCOUNTSYSTEM that the user selected or inferred from the deposit they selected.
if @PDACCOUNTSYSTEMID is null
select @PDACCOUNTSYSTEMID = ID from PDACCOUNTSYSTEM where ISDEFAULT = 1;
select @BASECURRENCYID = BASECURRENCYID
from dbo.PDACCOUNTSYSTEM as PAS
inner join dbo.CURRENCYSET as CS on PAS.CURRENCYSETID = CS.ID
where PAS.ID = @PDACCOUNTSYSTEMID
-- ****
begin try
if @PAYMENTMETHODCODE is null
raiserror('Payment method is required.', 13, 1)
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGCURRENCYNAME nvarchar(100);
select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
select @ORGCURRENCYID = ID, @ORGCURRENCYNAME = NAME from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGCURRENCYID, @PAYMENTDATE, null, @TRANSACTIONCURRENCYID);
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 ' + isnull(@CURRENCYNAME, '') + ' to ' + isnull(@ORGCURRENCYNAME, '')
raiserror (@errormessage, 16, 1);
end
-- handle inserting the data
exec dbo.USP_PAYMENT_ADDBASE
@ID = @ID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE,
@CONSTITUENTID = null,
@DATE = @PAYMENTDATE,
@AMOUNT = @AMOUNT,
@RECEIPTAMOUNT = @AMOUNT,
@PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
@CHECKDATE = @CHECKDATE,
@CHECKNUMBER = @CHECKNUMBER,
@REFERENCEDATE = @REFERENCEDATE,
@REFERENCENUMBER = @REFERENCENUMBER,
@CARDHOLDERNAME = @CARDHOLDERNAME,
@CREDITCARDNUMBER = @CREDITCARDNUMBER,
@CREDITTYPECODEID = @CREDITTYPECODEID,
@AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
@EXPIRESON = @EXPIRESON,
@POSTSTATUSCODE = @POSTSTATUSCODE,
@POSTDATE = @POSTDATE,
@OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
@SOURCECODE = '',
@REFERENCE = @PAYMENTSOURCE,
@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
@BASECURRENCYID = @BASECURRENCYID,
@BASEEXCHANGERATEID = @BASEEXCHANGERATEID output,
@EXCHANGERATE = @EXCHANGERATE,
@CURRENTAPPUSERID = @CURRENTAPPUSERID
exec dbo.USP_MISCELLANEOUS_ADDPAYMENT
@REVENUEID = @ID,
@AMOUNT = @AMOUNT,
@CREATIONDATE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID
if @PDACCOUNTSYSTEMID is null
set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';
exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
@ID = @ID,
@PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
@CHANGEDATE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID
insert into dbo.BANKACCOUNTDEPOSITPAYMENT
(ID, DEPOSITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
--Only add distributions if the post status is Not posted
if @POSTSTATUSCODE = 1
begin
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
if @DEPOSITID is not null
begin
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
if len(@DEPOSITREFERENCE) = 0
set @DEPOSITREFERENCE = @PAYMENTSOURCE;
if len(@DEPOSITREFERENCE) > 0
begin
/*update dbo.GLTRANSACTION set REFERENCE = @DEPOSITREFERENCE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from dbo.REVENUE
inner join dbo.REVENUEGLDISTRIBUTION on REVENUE.ID = REVENUEGLDISTRIBUTION.REVENUEID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
where REVENUE.ID = @ID and GLTRANSACTION.TRANSACTIONTYPECODE = 0 and GLTRANSACTION.POSTSTATUSCODE = 1
and GLTRANSACTION.TRANSACTIONCURRENCYID = @BATRANSACTIONCURRENCYID;
*/
update dbo.REVENUEGLDISTRIBUTION set
REFERENCE = @DEPOSITREFERENCE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where REVENUEGLDISTRIBUTION.ID in
(select REVENUEGLDISTRIBUTION.ID
from dbo.REVENUE
inner join dbo.REVENUEGLDISTRIBUTION on REVENUE.ID = REVENUEGLDISTRIBUTION.REVENUEID
inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
where REVENUE.ID = @ID and REVENUEGLDISTRIBUTION.TRANSACTIONTYPECODE = 0 and GLTRANSACTION.POSTSTATUSCODE = 1
and GLTRANSACTION.TRANSACTIONCURRENCYID = @BATRANSACTIONCURRENCYID)
;
end
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0