SedonaWeb 01/22 Security Script

02/13/2024 11:40 am EST
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?