SedonaOffice - SQL Script - WS_Account Error Fix - New Customer Registration - Web 1.0 (2.7.80)

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

CREATE PROCEDURE [dbo].[WS_Account_Register]
@customer_number varchar(15),
@invoice_number int,
@customer_name varchar(60),
@address_1 varchar(60),
@billing_zip_code varchar(50),
@inactive char(1),
@account_name varchar(256),
@contact_name varchar(255),
@title varchar(50),
@phone varchar(12),
@extension varchar(5),
@fax varchar(12),
@email varchar(50),
@pager varchar(12),
@all_sites char(1),
@salutation varchar(10),
@first_name varchar(50),
@middle varchar(50),
@last_name varchar(50),
@suffix varchar(12),
@mobile varchar(12),
@collection_contact char(1),
@invoice_contact char(1),
@Notes varchar(255),
@AddToBillContacts bit = 1,
@AddToAllSites bit = 1,
@Contact_Id int OUTPUT,
@Bill_Contact_Id int OUTPUT,
@Account_Id int OUTPUT,
@ErrCode varchar(50) OUTPUT
AS
BEGIN
-- preset outputs as zero meaning error
SET @Contact_Id = 0;
SET @Bill_Contact_Id = 0;
SET @Account_Id = 0;

if isnull(@customer_number, '') = ''
begin
SET @ErrCode = 'NoCustomerNumber';
RETURN;
end

if @invoice_number < 2
begin
SET @ErrCode = 'NoInvoiceNumber';
RETURN;
end

if isnull(@customer_name, '') = ''
begin
SET @ErrCode = 'NoCustomerName';
RETURN;
end

if isnull(@address_1, '') = ''
begin
SET @ErrCode = 'NoAddress1';
RETURN;
end

if isnull(@billing_zip_code, '') = ''
begin
SET @ErrCode = 'NoBillingZip';
RETURN;
end

DECLARE @Cust_Num varchar(15), @Cust_Id int, @Bill_Id int, @Invoice_Num int, @Business_Name varchar(60);
DECLARE @First_Nm varchar(30), @Last_Nm varchar(30), @Addr1 varchar(60), @Zip varchar(50), @Cust_Stat int;

SELECT @Invoice_Num = inv.Invoice_Number, @Cust_Num = cust.Customer_Number, @Cust_Id = inv.Customer_Id, @Bill_Id = cBill.Customer_Bill_Id,
@First_Nm = cBill.First_Name, @Last_Nm = cBill.Last_Name, @Business_Name = cBill.Business_Name, @Addr1 = cBill.Address_1,
@Zip = cBill.GE3_Description, @Cust_Stat = cust.Customer_Status_Id
FROM [dbo].AR_Invoice inv
JOIN [dbo].AR_Customer_Bill cBill on cBill.Customer_Bill_Id = inv.Customer_Bill_Id
JOIN [dbo].AR_Customer cust on cust.Customer_Id = inv.Customer_Id
WHERE inv.Invoice_Number = @invoice_number

if @Invoice_Num is null
-- Requested invoice number not found.
begin
SET @ErrCode = 'InvoiceNotFound';
RETURN;
end

if @Cust_Num <> @customer_number
-- Customer number of invoice does not match specified customer number.
begin
SET @ErrCode = 'CustomerNumMismatch';
RETURN;
end

if @customer_name <> @Business_Name and @customer_name <> @First_Nm + ' ' + @Last_Nm
-- Customer bill name on file does not match specified customer name.
begin
SET @ErrCode = 'CustomerNameMismatch';
RETURN;
end

if @Addr1 <> @address_1
-- Customer bill address 1 does not match specified address 1.
begin
SET @ErrCode = 'CustomerAddressMismatch';
RETURN;
end

if @Zip <> @billing_zip_code
-- Customer billing zip does not match specified zip.
begin
SET @ErrCode = 'CustomerZipMismatch';
RETURN;
end

if @Cust_Stat = 3
begin
SET @ErrCode = 'CustomerCancelled';
RETURN;
end

IF EXISTS (SELECT * FROM WS_Account WHERE Account_Name = @account_name)
BEGIN
SET @ErrCode = 'AcctExists';
RETURN;
END

BEGIN TRANSACTION;

EXECUTE [dbo].[Customer_Contact_ADD] @contact_name, @title, @phone, @extension,
@fax, @email, @pager, @all_sites, @salutation, @first_name,
@middle, @last_name, @suffix, @mobile, @collection_contact,
@invoice_contact, @notes, 'N', @Contact_Id OUTPUT;

IF (@Contact_Id = 1)
BEGIN
ROLLBACK TRANSACTION;
SET @ErrCode = 'ContAddFailed';
RETURN;
END

IF @AddToBillContacts = 1
BEGIN
EXECUTE [dbo].[Bill_Contact_ADD] @bill_id, @Contact_Id, @Bill_Contact_Id OUTPUT;

IF (@Bill_Contact_Id = 1)
BEGIN
ROLLBACK TRANSACTION;
SET @ErrCode = 'BillContAddFailed';
RETURN;
END
END

IF @inactive = 'Y'
BEGIN
EXECUTE [dbo].[WS_Account_ADD2] @Customer_Number, @Account_Name,
@Contact_Id, @Inactive, @AddToAllSites, @Account_Id OUTPUT;

IF (@Account_Id = 0)
BEGIN
ROLLBACK TRANSACTION;
SET @ErrCode = 'AcctAddFailed';
RETURN;
END
END
ELSE
BEGIN
INSERT INTO AR_Site_Contact (Site_Id, Contact_Id)
SELECT cs.Customer_Site_Id, @Contact_Id
FROM AR_Customer_Site cs
WHERE (Customer_Id = @Cust_Id);
END

COMMIT TRANSACTION;

SET @ErrCode = 'OK';
END
GO
Was this article helpful?