EXEC sp_rename 'WS_Account_Register', '1WS_Account_Register_old';
GO
/****** Object: StoredProcedure [dbo].[WS_Account_Register] Script Date: 2/22/2023 3:46:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WS_Account_Register]
@customer_number varchar(15),
@registration_code varchar(6),
@billing_zip_code varchar(50),
@inactive char(1),
@account_name varchar(256),
@contact_name varchar(255),
@title varchar(50),
@phone varchar(25),
@extension varchar(5),
@fax varchar(25),
@email varchar(50),
@pager varchar(25),
@all_sites char(1),
@salutation varchar(10),
@first_name varchar(50),
@middle varchar(50),
@last_name varchar(50),
@suffix varchar(10),
@mobile varchar(25),
@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 isnull(@registration_code, '') = ''
begin
SET @ErrCode = 'NoRegistrationCode';
RETURN;
end
/*
if isnull(@billing_zip_code, '') = ''
begin
set @Error_Text = '$0.00 invoice items not allowed'
goto ChkErr
end
*/
DECLARE @Cust_Id int, @Reg_Code varchar(6), @Bill_Id int, @Zip varchar(50), @Cust_Stat int;
SELECT @Cust_Id = cust.Customer_Id, @Reg_Code = cust.Registration_Code, @Bill_Id = cBill.Customer_Bill_Id,
@Zip = cBill.GE3_Description, @Cust_Stat = cust.Customer_Status_Id
FROM [dbo].AR_Customer cust
JOIN [dbo].AR_Customer_Bill cBill on cBill.Customer_Id = cust.Customer_Id and cBill.Is_Primary = 'Y'
WHERE cust.Customer_Number = @customer_number;
if @Cust_Id is null
-- Specified customer number not found.
begin
SET @ErrCode = 'CustomerNotFound';
RETURN;
end
if @Reg_Code <> @registration_code
begin
SET @ErrCode = 'RegistrationCodeMismatch';
RETURN;
end
/*
if @Zip <> @billing_zip_code
-- Customer primary 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 = 0)
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 = 0)
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
