Personalization, Customization and Productivity Tools

[wakeet]

Think I fixed it.  I changed two things:

1.)  I'm now using the read-only service object instead of the business object.  Also, using the .Find() in replace of the .SetKey()

Set oItemPrice = Session.AsObject(oSession.GetObject("IM_PriceCode_svc"))

Thanks to Steve for pointing it out in this thread.

2.)  Before searching for a product line discount, i check to see if the item has any item pricing records setup.  if it does, the script ends.  if it doesn't, runs the script previously mentioned to find and calculate the new price.  (i would have needed to do this step anyway to establish a pricing hierarchy.  if the item pricing is setup, it takes precedence over the product line pricing.)

I can't even begin to explain how happy this has made my organization. 

I love the new advanced scripting of 4.4.

[connex]

I didn't have the same problem that you experienced with IM_PriceCode_bus and .SetKey().  However I did include a SetKeyValue on PriceCode and CustomerPriceLevel using blanks prior to the SetKey.

My logic was different with regards to hierarchy.  I check if the price on the sales order line is different than standard price in CI_Item. If it is, I leave it as is which makes this pricing the lowest priority.

[wjezewski]

Evan,

We too needed to do product line discounts in 4.4. What I did was to add a UDF to the SO_PurchaseCtrlByCustByProdLn table called UDF_DISCOUNT_PERCENT. Then I added the following script to the QuantityOrdered Column Post Validate event of the SO_SalesOrderDetail table...

trace = false

' Setup variables
retval = 0
retMsg = ""
oItemCode = 0
oPurchCtrl = 0
ARDivisionNo = ""
CustomerNo = ""
ProductLine = ""
LineDiscountPcnt = 0

If trace Then
	retval = oScript.DebugPrint("Variables setup ok")

End If


' Get object handles
oItemCode = oBusObj.GetChildHandle("ItemCode")
oPurchCtrl = oSession.GetObject("SO_PurchaseCtrlByCustByProdLn_bus")


If oItemCode <> 0 AND oPurchCtrl <> 0 Then
	If trace Then
		retval = oScript.DebugPrint("Objects handles ok")

	End If

	' Set objects from handles
	Set oItemCode = oScript.AsObject(oItemCode)
	Set oPurchCtrl = oScript.AsObject(oPurchCtrl)

	If trace Then
		retval = oScript.DebugPrint("Objects set ok")

	End If

	' Get ARDivisionNo and CustomerNo from SalesOrderHeader object
	retval = oHeaderObj.GetValue("ARDivisionNo$", ARDivisionNo)

	If retval <> 0 Then
		retval = oHeaderObj.GetValue("CustomerNo$", CustomerNo)

	End If

	' Get ProductLine from ItemCode child of SalesOrderDetail object
	If retval <> 0 Then
		retval = oItemCode.GetValue("ProductLine$", ProductLine)

	End If

	If retval <> 0 Then
		retval = oScript.DebugPrint("ARDivisionNo+CustomerNo+ProductLine read")

		' Set the lookup keys for SO Purchase Control Customer by Product Line object
		retval = oPurchCtrl.SetKeyValue("ARDivisionNo$", ARDivisionNo)
		If retval <> 0 Then
			retval = oPurchCtrl.SetKeyValue("CustomerNo$", CustomerNo)

		End If

		If retval <> 0 Then
			retval = oPurchCtrl.SetKeyValue("ProductLine$", ProductLine)

		End If

		If retval <> 0 Then
			If trace Then
				retval = oScript.DebugPrint("oPurchCtrl.SetKeyValue ok")

			End If

			' Do the SO Purchase Control Customer by Product Line lookup
			If oPurchCtrl.Find() = 1 Then
				' Record found
				If trace Then
					retval = oScript.DebugPrint("oPurchCtrl record found")

				End If

				retval = oPurchCtrl.GetValue("UDF_DISCOUNT_PERCENT",LineDiscountPcnt)

				If retval <> 0 Then
					'Record read
					retval = oPurchCtrl.Clear()
					Set oPurchCtrl = Nothing

					If trace Then
						retval = oScript.DebugPrint("UDF_DISCOUNT_PERCENT read")

					End If

					If LineDiscountPcnt <> 0 Then
						' Discount is specified
						If trace Then
							retval = oScript.DebugPrint("UDF_DISCOUNT_PERCENT is " & CStr(LineDiscountPcnt))

						End If

						retval = oBusObj.SetValue("LineDiscountPercent", LineDiscountPcnt)
						If retval <> 0 Then
							' Discount applied
							retval = oBusObj.SetValue("CommentText$",CStr(LineDiscountPcnt) & "% discount applied to this item.")
							msg = "A " & CStr(LineDiscountPcnt) & "% discount has been automatically applied to this line."
							'retMsg = oSession.AsObject(oSession.UI).MessageBox("",msg,"Style=Ok,Icon=Info,Beep")

							If trace Then
								retval = oScript.DebugPrint("Discount Applied")

							End If

						Else
							' Could not apply discount
							If trace Then
								retval = oScript.DebugPrint("Could not apply discount")

							End If

						End If

					Else
						' No discount specified
						If trace Then
							retval = oScript.DebugPrint("UDF_DISCOUNT_PERCENT is 0")

						End If

					End If

				Else
					' Could not read discount field
					retval = oPurchCtrl.Clear()
					Set oPurchCtrl = Nothing

					If trace Then
						retval = oScript.DebugPrint("UDF_DISCOUNT_PERCENT not read")

					End If

				End If

			Else
				' No SO Purchase Control Customer by Product Line record found
				retval = oPurchCtrl.Clear()
				Set oPurchCtrl = Nothing

				If trace Then
					retval = oScript.DebugPrint("oPurchCtrl record not found")

				End If

			End If

		Else
			' Error setting oPurchCtrl key values
			If trace Then
				retval = oScript.DebugPrint("oPurchCtrl.SetKeyValue fail")

			End If

		End If

	Else
		If trace Then
			retval = oScript.DebugPrint("ARDivisionNo+CustomerNo+ProductLine fail")

		End If

	End If

Else
	'Did not get object handles
	If trace Then
		retval = oScript.DebugPrint("Object handles fail")

	End If

End If

If trace Then
	retval = oScript.DebugPrint("ErrMsg: " & oScript.LastErrorMsg)

End If

Set oPurchCtrl = Nothing
Set oItemCode = Nothing

This worked great for us as we are using the Purchase Control options in MAS and it makes for a nice and easy interface to setup the discounts as I included the UDF in the grid on the Purchase Control by Product Line task panel.

EDIT: I should add that the MessageBox line is commented out as the MessageBox can cause stability issues in the current release of MAS. See this thread for more information.

Regards,

Walter Jezewski

Technical Director

Tandem Marketing and Allnet Distributing

[wakeet]

Thanks for posting Walter.

My company doesn't use Purchase Control Maintenance.  That never crossed my mind as a possibility.

I appreciate the help as it looks like a good alternative!

[Ebrown]

Evan,

If you are looping through your script, you need to reset the variable numDiscount to 0 before you loop back to the top. You are only making a change to the numDiscount variable if a record is found. If one is not found, then whatever is populated in the variable from the last record found will show up as the discount for the current record.

-Ed

[wakeet]

Good catch Ed!  Thanks for looking out!

I orginally only posted parts of the script.  Here is the entire script.

If the product line pricing record is found, the numDiscounts is reset to 0, the discount is retrieved from IM_PriceCode, the new Item price is calculated and written to the record.

So if no product line pricing records are found the numDiscount will not be used.

'Used to implement product line pricing discounts in the IM_PriceCode file
'Discontinued items must be added to CI_Item with the same name as the
'product line, plus a sorting character. i.e. "~AET" for the AET Prod Line

strCustomer = ""
strProdLine = ""
strItem = ""
retVal = 0


'retrieves the customer name from the header
	retVal = oHeaderObj.GetValue("CustomerNo$", strCustomer)

'retrieves the product line from the detail object
	retVal = oBusObj.GetValue("UDF_ProductLine$", strProdLine)

'retrieves the item code from the detail object
	retVal = oBusObj.GetValue("ItemCode$", strItem)
	'retMSG = oSession.AsObject(oSession.UI).MessageBox("", "Item: " & strItem)

'creates the IM_PriceCode business object
oItemPrice = 0
Set oItemPrice = oSession.AsObject(oSession.GetObject("IM_PriceCode_svc"))

'looks for the item pricing record in IM_PriceCode.  this constructs the key and finds the row
retVal = oItemPrice.SetKeyValue("PriceCodeRecord$", "2")
retVal = oItemPrice.setKeyValue("ItemCode$", strItem)
retVal = oItemPrice.SetKeyValue("ARDivisionNo$", "00")
retVal = oItemPrice.SetKeyValue("CustomerNo$", strCustomer)
retVal = oItemPrice.Find()

'if no item pricing records are found then look for product line pricing
if retVal <> 1 then


	'looks for the product line pricing record in IM_PriceCode.  this constructs the key and finds the row
	retVal = oItemPrice.SetKeyValue("PriceCodeRecord$", "2")
	retVal = oItemPrice.setKeyValue("ItemCode$", "~" & strProdLine)
	retVal = oItemPrice.SetKeyValue("ARDivisionNo$", "00")
	retVal = oItemPrice.SetKeyValue("CustomerNo$", strCustomer)
	retVal = oItemPrice.Find()


	'if 1 then the product line pricing record was found
	if retVal = 1 then
		numDiscount = 0
		numPrice = 0	

		retVal = oItemPrice.GetValue("DiscountMarkup1", numDiscount)
		'retMSG = oSession.AsObject(oSession.UI).MessageBox("", "Discount: " & numDiscount)
		retVal = oBusObj.GetValue("UnitPrice", numPrice)
		numDiscount = 1 - (numDiscount / 100) 'converts the discount amount to the decimal multiplier
		retVal = oBusObj.SetValue("UnitPrice", numPrice * numDiscount) 'calculates the new unit price
		retVal = oBusObj.SetValue("CommentText$", cStr(numDiscount)) 'testing to display current discount value
	end if
end if

If you see any potential issues with this script I would welcome the help!

Now my next challenge is to test to see if the user has security to the IM_PriceCode file before executing the script.  If the user does not have access they received an error 88 when they attempt to enter the Qty Ordered.

[bretR]

This is a great thread with a lot of great script code!

Regarding the error 88 when doing a oSession.GetObject() when the user does not have access...

I am working on a fix for a related issue and used two methods to avoid the hard error during my testing.

1. Use the oSession.CheckSecurity() method.  The problem with this is you need to know the "programName" used internally for the object task.  Sometimes this will be obvious, other times may be not obvious, or not available at all.

2. I recommend using On Error Resume Next - since there is no Try Catch in vbs

Err.Clear

On Error Resume Next

Set oVendor = oSession.AsObject(oSession.GetObject("AP_Vendor_svc"))

If not(cbool(Err.Number)) Then

    ' User has access

Else

    ' Unable to create object - vbs reports an err 13 type mismatch on the Set oVendor =

End If

Hope this helps.  Gotta get back to fixing the GetObject() when user has only read access issue.

[wakeet]

That is precisely what I am looking for.

Thanks so much. 

I just subscribed to the other thread so I change my users to read only once it is fixed!