USP_RECEIVABLEPAYMENT_EDIT
Allows for the editing of a given receivable payment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @AMOUNT | money | IN | |
| @PAYMENTDATE | datetime | IN | |
| @POSTSTATUSCODE | tinyint | IN | |
| @POSTDATE | datetime | IN | |
| @PAYMENTMETHODCODE | tinyint | IN | |
| @CONSTITUENTACCOUNTID | uniqueidentifier | IN | |
| @CHECKDATE | UDT_FUZZYDATE | IN | |
| @CHECKNUMBER | nvarchar(20) | IN | |
| @REFERENCEDATE | UDT_FUZZYDATE | IN | |
| @REFERENCENUMBER | nvarchar(20) | IN | |
| @CARDHOLDERNAME | nvarchar(255) | IN | |
| @CREDITCARDPARTIALNUMBER | nvarchar(4) | IN | |
| @CREDITTYPECODEID | uniqueidentifier | IN | |
| @EXPIRESON | UDT_FUZZYDATE | IN | |
| @AUTHORIZATIONCODE | nvarchar(20) | IN | |
| @DIRECTDEBITRESULTCODE | nvarchar(10) | IN | |
| @OTHERPAYMENTMETHODCODEID | uniqueidentifier | IN | |
| @RECEIVABLEPAYMENTID | nvarchar(60) | IN |
Definition
Copy
CREATE procedure dbo.USP_RECEIVABLEPAYMENT_EDIT
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@AMOUNT money = 0,
@PAYMENTDATE datetime = null,
@POSTSTATUSCODE tinyint = 1,
@POSTDATE datetime = null,
@PAYMENTMETHODCODE tinyint = 1,
@CONSTITUENTACCOUNTID uniqueidentifier = null,
@CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
@CHECKNUMBER nvarchar(20) = '',
@REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
@REFERENCENUMBER nvarchar(20) = '',
@CARDHOLDERNAME nvarchar(255) = '',
@CREDITCARDPARTIALNUMBER nvarchar(4) = '',
@CREDITTYPECODEID uniqueidentifier = null,
@EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
@AUTHORIZATIONCODE nvarchar(20) = '',
@DIRECTDEBITRESULTCODE nvarchar(10) = '',
@OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
@RECEIVABLEPAYMENTID nvarchar(60) = null
)
as
begin
set nocount on;
begin try
begin tran
-- Setup the basic information
if (@CHANGEAGENTID is null)
begin
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
end
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
-- Setup the next receivable payment ID
if ((@RECEIVABLEPAYMENTID is null) or (len(@RECEIVABLEPAYMENTID) = 0))
begin
set @RECEIVABLEPAYMENTID = dbo.UFN_FINANCIALTRANSACTION_GETNEXTFINANCIALTRANSACTIONID(105);
end
declare @CONSTITUENTID uniqueidentifier;
select @CONSTITUENTID = CONSTITUENTID
from dbo.FINANCIALTRANSACTION
where ID = @ID;
-- Update the financial transaction
exec dbo.USP_FINANCIALTRANSACTION_EDIT @ID=@ID, @CHANGEAGENTID=@CHANGEAGENTID, @CONSTITUENTID=@CONSTITUENTID, @USERDEFINEDID=@RECEIVABLEPAYMENTID, @AMOUNT=@AMOUNT, @DATE=@PAYMENTDATE, @POSTDATE=@POSTDATE, @POSTSTATUSCODE=@POSTSTATUSCODE;
-- Update the basic line items with the new description
update dbo.FINANCIALTRANSACTIONLINEITEM
set TRANSACTIONAMOUNT = @AMOUNT,
BASEAMOUNT = @AMOUNT,
ORGAMOUNT = @AMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
POSTDATE = @POSTDATE,
POSTSTATUSCODE = @POSTSTATUSCODE
from dbo.FINANCIALTRANSACTIONLINEITEM
where (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID) and
(FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0) and
(FINANCIALTRANSACTIONLINEITEM.DELETEDON is null);
-- Get the current payment method
declare @CURRENTPAYMENTMETHODCODE tinyint;
select @CURRENTPAYMENTMETHODCODE = PAYMENTMETHODCODE
from dbo.RECEIVABLEPAYMENT
where ID = @ID;
-- Check to see if we are changing payment methods
if (@CURRENTPAYMENTMETHODCODE != @PAYMENTMETHODCODE)
begin
-- Since we are changing payment methods remove the old payment method information
if (@CURRENTPAYMENTMETHODCODE = 1) -- Check
begin
delete from dbo.RECEIVABLEPAYMENTCHECK
where ID = @ID;
end
else if (@CURRENTPAYMENTMETHODCODE = 2) -- Credit card
begin
delete from dbo.RECEIVABLEPAYMENTCREDITCARD
where ID = @ID;
end
else if (@CURRENTPAYMENTMETHODCODE = 3) -- Direct debit
begin
delete from dbo.RECEIVABLEPAYMENTDIRECTDEBIT
where ID = @ID;
end
else if (@CURRENTPAYMENTMETHODCODE = 10) -- Other
begin
delete from dbo.RECEIVABLEPAYMENTOTHERMETHOD
where ID = @ID;
end
end
-- Update into the receivable payment
update dbo.RECEIVABLEPAYMENT
set PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
REFERENCEDATE = @REFERENCEDATE,
REFERENCENUMBER = @REFERENCENUMBER,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
-- Based on the payment method save to a different "child" table
if (@PAYMENTMETHODCODE = 1) -- Check
begin
if (@CURRENTPAYMENTMETHODCODE != @PAYMENTMETHODCODE)
begin
-- Insert the extra check information
insert into dbo.RECEIVABLEPAYMENTCHECK
(ID, CHECKNUMBER, CHECKDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @CHECKNUMBER, @CHECKDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
else
begin
-- Update the extra check information
update dbo.RECEIVABLEPAYMENTCHECK
set CHECKNUMBER = @CHECKNUMBER,
CHECKDATE = @CHECKDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end
end
else if (@PAYMENTMETHODCODE = 2) -- Credit card
begin
if (@CURRENTPAYMENTMETHODCODE != @PAYMENTMETHODCODE)
begin
-- Insert the extra credit card information
insert into dbo.RECEIVABLEPAYMENTCREDITCARD
(ID, CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON, AUTHORIZATIONCODE, TRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @CARDHOLDERNAME, @CREDITTYPECODEID, @CREDITCARDPARTIALNUMBER, @EXPIRESON, @AUTHORIZATIONCODE, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
else
begin
-- Update the extra credit card information
update dbo.RECEIVABLEPAYMENTCREDITCARD
set CARDHOLDERNAME = @CARDHOLDERNAME,
CREDITTYPECODEID = @CREDITTYPECODEID,
CREDITCARDPARTIALNUMBER = @CREDITCARDPARTIALNUMBER,
AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
EXPIRESON = @EXPIRESON,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end
end
else if (@PAYMENTMETHODCODE = 3) -- Direct debit
begin
if (@CURRENTPAYMENTMETHODCODE != @PAYMENTMETHODCODE)
begin
-- Insert the extra direct debit information
insert into dbo.RECEIVABLEPAYMENTDIRECTDEBIT
(ID, DIRECTDEBITRESULTCODE, CONSTITUENTACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @DIRECTDEBITRESULTCODE, @CONSTITUENTACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
else
begin
-- Update the extra direct debit information
update dbo.RECEIVABLEPAYMENTDIRECTDEBIT
set DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end
end
else if (@PAYMENTMETHODCODE = 10) -- Other
begin
if (@CURRENTPAYMENTMETHODCODE != @PAYMENTMETHODCODE)
begin
-- Insert the extra direct debit information
insert into dbo.RECEIVABLEPAYMENTOTHERMETHOD
(ID, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
else
begin
-- Update the extra direct debit information
update dbo.RECEIVABLEPAYMENTOTHERMETHOD
set OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end
end
end try
begin catch
rollback tran
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
commit tran
return 0;
end