SedonaOffice - SQL Script - 6.2.0.6 - ACH error for CC payments - Script for REV 4

05/31/2024 9:19 am EDT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SEFT_ACH_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SEFT_ACH_Add]
GO

Create PROCEDURE [dbo].[SEFT_ACH_Add]
@customer_number varchar(15),
@description varchar(50),
@credit_card char(1),
@type_uaim char(1),
@invoice_number int = 0,
@misc_account_code varchar(25),
@amount money,
@submitted char(1),
@submit_date datetime,
@batch_code varchar(25),
@usercode varchar(25),
@ready_to_post char(1),
@response_type char(1),
@response_code varchar(3),
@posted char(1),
@hold_date datetime,
@category_code varchar(25),
@batch_or_live char(1),
@authorization_code varchar(25),
@trace_number varchar(60),
@bankname varchar(25),
@bank_account varchar(20),
@bank_account_name varchar(22),
@checking_or_savings char(1),
@card_type varchar(4),
@card_number varchar(20),
@expiration_month varchar(2),
@expiration_year varchar(4),
@name_on_card varchar(22),
@security_code varchar(8),
@payment_method varchar(25),
@customer_address varchar(35),
@customer_postal varchar(10),
@last_four varchar(4),
@check_number varchar(15),
@ach_direct_token varchar(50),
@merchant_id nvarchar(10) = 'X',
@is_sale char(1) = 'N',
@is_credit char(1) = 'N',
@transStatus nvarchar(50),
@jobNumber nvarchar(25) = '',
@ach_id int OUTPUT

AS

DECLARE @customer_id int,
@misc_account_id int,
@enter_date datetime,
@category_id int,
@bank_id int,
@payment_method_id int,
@invoice_id int

EXEC Get_Customer_Id @customer_number, @customer_id OUTPUT
EXEC Get_Account_Id @misc_account_code, @misc_account_id OUTPUT
EXEC Get_Category_Id @category_code, @category_id OUTPUT
EXEC Get_Payment_Method_Id @payment_method, @payment_method_id OUTPUT
exec Get_Invoice_Id @invoice_number, @invoice_id output

SELECT @ach_id = 0
SELECT @enter_date = GETDATE()
select @expiration_year = right(ltrim(rtrim(@expiration_year)),2)

SELECT @bank_id = ISNULL((SELECT TOP 1 Bank_Id FROM AR_Bank WHERE Code = @bankname), 1)

IF ((NULLIF(@merchant_id,'') IS NULL) OR (@merchant_id = 'X'))
BEGIN
SELECT @merchant_id = Merchant_Id FROM dbo.AR_ACH_Direct ach
WHERE Setup_Id = ISNULL((SELECT ach.Setup_Id FROM AR_Customer cu INNER JOIN AR_Branch br ON cu.Branch_Id = br.Branch_Id
INNER JOIN AR_ACH_Direct ach ON br.ACH_Direct_MerchantId = ach.Merchant_Id WHERE cu.Customer_Number = @Customer_Number), 1);
END

INSERT AR_ACH( Customer_Id, Entered_Date, Description, Credit_Card, Type_UAIM, Invoice_Id, Misc_Account_Id, Amount, Submitted, Submit_Date, Batch_Code, UserCode, Ready_To_Post, Response_Type, Response_Code, Posted, Deposit_Check_Id, Hold_Date, Category_Id, Batch_Or_Live, Authorization_Code, Trace_Number, Trans_Reference, Bank_Id, Bank_Account, Bank_Account_Name,
Checking_Or_Savings, Card_Type, Card_Number, Expiration_Month, Expiration_Year, Name_On_Card, Security_Code, Payment_Method_Id, Customer_Address, Customer_PostalCode, Voided,
Void_Reason, Last_Four_Digits, Check_Number, Second_Response_Code, Second_Response_Type, Second_Response_Date, AVS_Response, ACH_Direct_Token, Merchant_Id, Is_Sale, Is_Credit, Trans_Status, JobNumber)

VALUES ( @customer_id, @enter_date, @description, @credit_card, @type_uaim, @invoice_id, @misc_account_id, @amount, @submitted, @submit_date, @batch_code, @usercode, @ready_to_post,
@response_type, @response_code, @posted, 1, @hold_date, @category_id, @batch_or_live, @authorization_code, @trace_number, @trace_number, @bank_id, @bank_account, @bank_account_name,
@checking_or_savings, @card_type, @card_number, @expiration_month, @expiration_year, @name_on_card, @security_code, @payment_method_id, @customer_address, @customer_postal,
'N','', @last_four, @check_number, '', '', '12 AM', 0, @ach_direct_token, @merchant_id, @is_sale, @is_credit, @transStatus, @jobNumber)

IF @@error <> 0
BEGIN
RETURN
END

SELECT @ach_id = SCOPE_IDENTITY()

RETURN
Was this article helpful?