Update the SQL tab for AR - Basic Receipt - SO 6.x to Add Registration Code

04/29/2024 10:59 am EDT

Description of Issue:

The Registration Code is needed to create a new account on the SedonaCloud site. This allows customers to view and pay bills online.

The code is printed on the standard invoice form used within SedonaOffice. The registration does not show on the invoice reports sent out through SedonaSync.

Resolution:

The Registration code is in the AR_Customer table. It will need to be added to the script that pulls the information for the invoice report and email.

If they have modified the script from the standard script, the extra line can be added to the existing script.

Open SedonaSync.

Open the Menu for Event Designer,

Select the menu for Application Events on the left.

Click on the Application Events Pack where the event is saved.

Click the blue edit button next to the invoice event.

The Event Designer screen should open below.

In the Triggers section highlight the query that is listed and then click the blue edit button next to the query on the right side.

When the Query Trigger Editor screen opens click on the SQL tab.

Click the flag to Manually edit the query.

Put the cursor after the comma on the line for Business_Name and hit enter to start a new line.

Copy and paste the line below be sure to keep the comma.

dbo.AR_Customer.Registration_Code,

After adding the line so it looks like the image above, click update to save the change.

Click Yes on the confirmation screen to save the query.

The registration code now needs to be added to the body of the email that is sent to the customer.

Under Deliverables click on the Email,

A screenshot of a computer
Description automatically generated  

Insert the registration number.

In the search field above the available fields search for Registration_Code. It should now be available to be inserted into the email body. The field can be added where they like but you will need to add some qualifying text.

Add the text: Registration Code for creating your online account:

With your cursor at the end of your new text line, double-click on the field in the selection window to add it to the email.

A screenshot of a mail
Description automatically generated

After making the necessary changes be sure to update the event using the button at the top.

The registration code will now be printed the next time the invoices are generated and emailed.

Below is the full script used for getting the invoice information for the event if it is needed.

SELECT   
dbo.AR_Customer.Customer_Number AS 'Customer_Number',   
dbo.AR_Customer.Customer_Name AS 'Customer_Name',   
dbo.AR_Customer_Bill.Business_Name AS 'Business_Name',   
dbo.AR_Customer.Registration_Code,  
dbo.AR_Customer_Bill.Address_1 AS 'Address_1',   
dbo.AR_Customer_Bill.Address_2 AS 'Address_2',   
dbo.AR_Customer_Bill.Address_3 AS 'Address_3',   
dbo.AR_Customer_Bill.GE1_Description AS 'GE1_Description',   
dbo.AR_Customer_Bill.GE2_Short AS 'GE2_Short',   
dbo.AR_Customer_Bill.GE3_Description AS 'GE3_Description',   
dbo.AR_Customer_Bill_Email.EMail_Address AS 'E_Mail',   
dbo.AR_Invoice.Invoice_Id AS 'Invoice_Id',   
dbo.AR_Invoice.Invoice_Number AS 'Invoice_Number',   
dbo.AR_Invoice.Memo AS 'Memo',   
dbo.AR_Invoice.Amount AS 'Amount',   
dbo.AR_Invoice.Net_Due AS 'Net_Due',   
dbo.AR_Invoice_Description.Description AS 'InvoiceDescription',   
CONVERT(varchar, dbo.AR_Invoice.Invoice_Date, 101) AS 'Invoice_Date',   
CASE   
WHEN dbo.AR_Invoice.Type_JSCO = 'J' THEN 'Job'  
WHEN dbo.AR_Invoice.Type_JSCO = 'S' THEN 'Service'  
WHEN dbo.AR_Invoice.Type_JSCO = 'C' THEN 'Cycle'  
ELSE 'Other' END AS 'InvoiceType', dbo.AR_Branch.Description AS 'BranchDescription', dbo.SY_AlternateAddress.MailCompanyName AS 'MailCompanyName',   
CASE   
WHEN dbo.SY_AlternateAddress.MailAddress1 <> '' THEN dbo.SY_AlternateAddress.MailAddress1   
WHEN dbo.SY_AlternateAddress.MailAddress1 = '' AND dbo.SY_AlternateAddress.MailAddress2 <> '' THEN dbo.SY_AlternateAddress.MailAddress2  
WHEN dbo.SY_AlternateAddress.MailAddress1 = '' AND dbo.SY_AlternateAddress.MailAddress2 = '' AND dbo.SY_AlternateAddress.MailAddress3 <> '' THEN dbo.SY_AlternateAddress.MailAddress3  
ELSE '' END AS 'MailAddress1',  
CASE   
WHEN dbo.SY_AlternateAddress.MailAddress1 <> '' AND dbo.SY_AlternateAddress.MailAddress2 <> '' THEN dbo.SY_AlternateAddress.MailAddress2  
WHEN dbo.SY_AlternateAddress.MailAddress1 <> '' AND dbo.SY_AlternateAddress.MailAddress2 = '' AND dbo.SY_AlternateAddress.MailAddress3 <> '' THEN dbo.SY_AlternateAddress.MailAddress3  
ELSE '' END AS 'MailAddress2',   
CASE   
WHEN dbo.SY_AlternateAddress.MailAddress1 <> '' AND dbo.SY_AlternateAddress.MailAddress2 <> '' AND dbo.SY_AlternateAddress.MailAddress3 <> '' THEN dbo.SY_AlternateAddress.MailAddress3  
ELSE '' END AS 'MailAddress3', dbo.SY_AlternateAddress.OverViewMessage AS 'OverViewMessage', (CASE LEN(dbo.SY_AlternateAddress.MailPhone1)  WHEN 10 THEN '(' + 
LEFT(dbo.SY_AlternateAddress.MailPhone1, 3) + ') ' + 
RIGHT(LEFT(dbo.SY_AlternateAddress.MailPhone1, 6), 3) + '-' + 
RIGHT(dbo.SY_AlternateAddress.MailPhone1, 4) WHEN 7 THEN 
LEFT(dbo.SY_AlternateAddress.MailPhone1, 3) + '-' + 
RIGHT(dbo.SY_AlternateAddress.MailPhone1, 4) ELSE dbo.SY_AlternateAddress.MailPhone1 END) 
AS 'MailPhone1'  
FROM dbo.AR_Invoice   
LEFT OUTER JOIN dbo.AR_Invoice_Description ON dbo.AR_Invoice.Invoice_Description_Id = dbo.AR_Invoice_Description.Inv_Desc_ID  
LEFT OUTER JOIN dbo.AR_Branch ON dbo.AR_Invoice.Branch_id = dbo.AR_Branch.Branch_Id  
LEFT OUTER JOIN dbo.SY_AlternateAddress ON dbo.AR_Branch.AlternateAddress_Id = dbo.SY_AlternateAddress.AlternateAddress_Id  
LEFT OUTER JOIN dbo.AR_Customer ON dbo.AR_Invoice.Customer_Id = dbo.AR_Customer.Customer_Id   
LEFT OUTER JOIN dbo.AR_Customer_Bill ON dbo.AR_Invoice.Customer_Bill_Id = dbo.AR_Customer_Bill.Customer_Bill_Id  
LEFT OUTER JOIN dbo.AR_Customer_Bill_Email ON  dbo.AR_Customer_Bill_Email.Customer_Bill_Id = dbo.AR_Customer_Bill.Customer_Bill_Id   
WHERE dbo.AR_Invoice.Invoice_Id  <> 1    
AND dbo.AR_Customer.Customer_Id  <> 1    
AND dbo.AR_Customer_Bill.Customer_Bill_Id  <> 1    
AND dbo.AR_Customer.Customer_ID NOT IN (SELECT Customer_ID FROM AR_Master_Account)  
AND dbo.AR_Customer_Bill_Email.EMail_Address like '%@%.%'   
AND dbo.AR_Customer_Bill_Email.Invalid <> 'Y'  
AND dbo.AR_Customer_Bill_Email.Inactive <> 'Y'   
AND dbo.AR_Invoice.In_Email_Queue  = 'Y'    
AND dbo.AR_Invoice.Complete  = 'Y'    
AND dbo.AR_Invoice.Net_Due > 0   
AND dbo.AR_Invoice.Has_Pending_EFT = 'N'   
AND  
((dbo.AR_Invoice.Type_JSCO = 'C' AND dbo.AR_Customer_Bill.Email_Cycle_Invoice  = 'Y') 
OR  
(dbo.AR_Invoice.Type_JSCO = 'J' AND dbo.AR_Customer_Bill.Email_job_Invoice  = 'Y') OR  
(dbo.AR_Invoice.Type_JSCO = 'S' AND dbo.AR_Customer_Bill.Email_Service_Invoice  = 'Y') OR
  (dbo.AR_Invoice.Type_JSCO = 'O' AND dbo.AR_Customer_Bill.Email_Other_Invoice  = 'Y'))

Was this article helpful?