Scripting Tips for Sage 100 ERP

Blogger: Alnoor Cassim, xkzero Technical Services

A case for creating a script for line item discount calculations—
Let’s say you sell designer purses and handbags. During the month of June, you offer a 20% discount on Gucci orders of $300 or more—for loyalty program customers only.

Think of all the specific parameters involved in this promotion. Qualifications to receive the fixed percentage discount require the purchase be made during a limited timeframe, by a certain customer base, and only for orders of products by a particular vendor that exceed a designated amount.

Can Sage 100 ERP do the math?

The regular Sales Promotion feature has the option of applying a discount % to a date range. However, we cannot easily apply that percentage off to an entire product line, nor can we specify the customer type or add any other conditions.

To minimize the salesperson’s involvement in the calculation, you can automate Sage 100 ERP to set all the qualifying discount conditions. Yes, this is another case in which scripting can help!

Let’s assume the following:

1. We have set up this miscellaneous charge code for the promotion:  /DISC20
   We would like to reflect the discount by having the script automatically add /DISC20 line as a negative amount (to offset the order total, subtracting 20 percent for the Gucci products).
   We will not use the line item discount percentage feature nor use the Discount Rate/ Amount fields on the Totals tab.
2. In Customer Maintenance we will use the Customer Type field to represent the loyalty tier level where B=Blue, S=Silver, G=Gold, Z=Zero. For our promotion a value of B, S, or G qualifies.
3. In Product Line Maintenance, we will set up GUCC as the product line for all Gucci items. Several items have been created using GUCC. (Others designers in our collection include Louis Vuitton, Coach, Nine West, and LeSportsac.)

To accomplish this task, you can create a script. For your own company and industry, add your own business rules and other complexities.

What kind of script do you need to create?
Create an event script (a.k.a. User Defined Script) that runs when the Totals tab is clicked in Sales Order Entry. The event type we are using is called Pre-Totals. As the “Pre” implies, a Pre-Totals script will run before any of the standard Sage 100 ERP programming (such as when the totals tab is clicked (e.g. calculating Sales Tax)).

How do you create this script?

Follow these steps:

1. Go to the Custom Office/Main Menu, and make these selections:
   “User-Defined Field and Table Maintenance”
2. Under the “Sales Order” heading, choose: “SO Sales Order Header”
3. Right-click and choose “User Defined Scripts”
4. In the “User Defined Script” window, click the “Add” button. This will open the “Add Script” window.
5. In the “User-Defined Script–Add Script” window, as shown below [Image 1], make these choices:
   Event: “Table – Pre-Totals”
   Type in a name for your script, such as this (without the quotes):
   “Line Item Disc by Prod Line”
   

   Image 1

6. You will see a message indicating that the script file does not exist, followed by a question, “Do you want to create it? Yes/No?” Yes, you do want to create the script! Click: “Yes.”
7. Now the “Edit Script” window will appear. Copy the following code and paste it into your script editor window. Note: It is important to use the code exactly as shown below, including line breaks and spaces.

'Alnoor Cassim - xkzero - Line_Item_Disc_By_Prod_Line

'Approach:
'1 Check the Order Date for June Orders
'2 Check for a qualifying Customer Type
'3 Check if last line has /DISC20 and remove as we need to recalc
'4 Scan all lines for at least $300 of qualifying Gucci product
'5 If order qualifies, add the /DISC20 line and apply 20% off

'Init VARs
sItemType = "" : sItemCode = "" : nXAmt = 0 : nTotXAmt = 0
sProdLine = "" : sOrderDate = "" : sCustType = "" : sDiv = "" : sCust = ""
sDiscProdLine = "GUCC" : nAmtToQualify = 300 : nDP = .20

'Get the Order Date and if not in June exit the script -> No Discount
retVal = oBusObj.GetValue("OrderDate$", sOrderDate)
If sOrderDate < "20150601" or sOrderDate > "20150630" Then
	Exit Sub
End If

'Find out if we have a qualifying loyalty customer
retVal = oBusObj.GetValue("ARDivisionNo$", sDiv)
retVal = oBusObj.GetValue("CustomerNo$", sCust)

'AR_Customer table available as Data Source so use GetChildHandle()
'The other way is to use GetObject()
Set oCustomer=oBusObj.AsObject(oBusObj.GetChildHandle("CustomerNo"))

'Concantenate the key fields for AR_Customer
FullCust = sDiv & sCust

'Find the Customer record. Note how oCustomer is referenced now.
retVal = oCustomer.Find(FullCust)

'Get the value of the Customer Type (Loyalty Tier)
retVal = oCustomer.GetValue("CustomerType$", sCustType)

'If not Blue, Silver, or Gold exit script -> No discount
If sCustType = "Z" or sCustType = "" Then
	Exit Sub
End If

'oLines passed in but we must explicity set as an object in VB
Set oLines = oSession.AsObject(oBusObj.Lines)

'Avoid Double Discount
'Move to last line first to check if /DISC20 already exists.
'If so delete as we need to recalc if order qualifies for discount
retVal = oLines.MoveLast()
retVal = oLines.GetValue("ItemCode$", sItemCode)
If sItemCode = "/DISC20" Then
	retVal = oLines.Delete()
End If

'Start at the top of the grid
retVal = oLines.MoveFirst()

'Loop through Lines grid. Check for lines that qualify for discount
Do Until cBool(oLines.EOF) = True

	retVal = oLines.GetValue("ItemType$", sItemType)
	retVal = oLines.GetValue("ItemCode$", sItemCode)
	
	'Check for regular inventory items only: ItemType = 1
	If sItemType = "1" Then

		'CI_Item table is available as a Data Source to get Prod Line
		'Note how we use oLines instead of oBusObj here
		If IsObject(oItem) = False Then
			Set oItem=oLines.AsObject(oLines.GetChildHandle("ItemCode"))
		End If
	
		'Get the Product Line and if qualifiying increment total
		retVal = oItem.Find(sItemCode)
		retVal = oItem.GetValue("ProductLine$", sProdLine)
		If sProdLine = sDiscProdLine Then
			
			'We have reached a qualifying line yea!
			'Get the Extension Amt and increment the total.
			retVal = oLines.GetValue("ExtensionAmt", nXAmt)
			nTotXAmt = nTotXAmt + nXAmt
		End If
	End If
	
	'Move to next line in the grid
	retVal = oLines.MoveNext()

Loop

'Final Test
'Check if we have at least $300 of qualifying product
'If so add the /DISC20 negative misc charge code

If nTotXAmt >= nAmtToQualify Then
	retVal = oLines.AddLine()
	retVal = oLines.SetValue("ItemCode$", "/DISC20")
	retVal = oLines.SetValue("ItemType$", "3")
	sCommentText = "Congrats you qualify for a discount"
	retVal = oLines.SetValue("CommentText$", sCommentText)
	retVal=oLines.SetValue("ExtensionAmt",-1*(Round(nDP*nTotXAmt,2)))
	retVal = oLines.Write()
End If

To better understand the logic behind the script’s functionality, read this:

“Approach”
These comments lines represent the high level approach we will apply:

1. Check the order date for June orders.
2. Check for a qualifying customer type.
3. Check if last line already has /DISC20 and remove if we need to recalculate.
4. Scan all the lines for at least $300 of qualifying Gucci product.
5. If the order qualifies, add the /DISC20 line and apply 20% off the price.

“Init VARs”
In the first section, we “initialize” (init) the variables to prepare for use later in the script.
Note: We are hard-coding the qualifying product line, qualifying amount, and discount pct. However, if in July a new promo starts for a different designer, we could create a User Defined Table (UDT) to store the “designer of the month,” the “qualifying amount,” and “Disc Pct.” This would allow the script to grab the values from the UDT to prevent needing to modify that script every time.

The next block of code enables the following preliminary steps:

• If the Order Date is not June 1-30, 2015, it disqualifies the discount (exits the script).
• It attains values from the AR_Customer table, by using the GetChildHandle() function to take advantage of a Data Source called “CustomerNo”
  Note: This has exactly the same result as using Custom Office, where in UDF Maintenance you map a UDF from AR_Customer table via the “CustomerNo” data source to SO_SalesOrderHeader table.
• It locates the CustomerNo in AR_Customer table through the Find() function.
• It gets the value of the CustomerType field through the GetValue() function.
• It disqualifies the discount (exits the script) if CustomerType (the Loyalty Tier) is not Blue, Silver, or Gold.

Preventing Double Discounts
Normally we would scan the grid sequentially from top to bottom. However, we want to jump to the last line first and remove /DISC20 in case it was still there from the last time we clicked Totals for this order. We need to calculate the discount but at the same time not have more than one instance of /DISC20.

Scanning the Lines Grid
We are now ready to position ourselves at the first line in the grid and start the “Do Until Loop.” We continue to move sequentially through each line via the MoveNext() function until we reach “EOF.” This technically means “End of File,” but in this case refers to the last row in our grid. We use the VB function CBool() to convert the condition into a True/False test.

We now check if the ItemType field is set to “1.” This means we landed on a regular inventory item. If so, then we continue processing.

We can use the GetChildHandle() function again instead of GetObject(). Connect to the data source called “ItemCode” to get to the ProductLine field.
Note: This produces exactly the same results as using Custom Office, where in UDF Maintenance you map a UDF from CI_Item table via the “ItemCode” data source to SO_SalesOrderDetail table.

Next step—
Check to see if we landed on an item belonging to GUCC product line. If so, store the extension amount and increment the Gucci total in a counter. Finally, we do the MoveNext() function to move to the next line in the grid.

Final Test
If the totals for all GUCC lines are at least $300, then go through the steps to add a new line for /DISC20. We set the ItemType field to “3″ for Miscellaneous Charge, set the ExtensionAmt field to negative to reduce the order total, and set a comment to make it clear a discount was taken. Finally we write the new line.

You’re almost done! 

Now you can accept the script.
This will return you to the “Add Script” window.

1. To close this screen and return to the “User Defined Scripts” window, click “OK.”
2. Next, to go back to the main “User Defined Field” and “Table Maintenance” window, click “Close.” Here, you must also click “Close” again to see the “Script Compile” window.
3. IMPORTANT: When the “Script Compile” window appears, click the “Compile” button.

Note that you can further edit the script, if necessary. To do so, from the “Custom Office/Main” menu, choose the “User Defined Script Maintenance” task. When done editing the script, to syntax check the script, click the Check Script button:

To save the script, click the Accept button.

The next step is to click the Compile button in the lower-left of the screen. When the Script Compile window appears, click the Compile button again followed by the Close button.

This is one of many Sage 100 ERP scripting examples we will share through the xkzero blog. If you need help with scripting or other programming or technical issues with your Sage ERP, no matter how complex, please feel free to contact us: 

xkzero Technical Services
Email: info@xkzero.com
Call: 847-416-2009