SO Invoice Line Addition

Question

I have a good idea of how I picture this script working, never dealt with UDT's but from previous post it looks like the same rules. I am just not positive on how to "loop" through the SO Invoice lines to find the value on the UDT in order to enter a value from the UDT to the SO Invoice lines. Situation; we have a UDT_ArtCharges with columns SO#, Customer Name, ART_TOTAL. When a SO is invoiced through "SO Invoice Data Entry" I want to loop through the lines to see if any of the lines contain a miscellaneous item code of "/ART" (user should add this in SO entry but sometimes doesn't), if no lines contain "/ART" I want to pull up the UDT and find that SO number being invoiced in the UDT and add a line to the invoice with the corresponding "ART_TOTAL" for that SO number. The other feature I would like is to add a "yes" or "no" option before adding the line to the invoice, something else I haven't played with yet. I've complied a mess of code from past post, which part should I focus on first to clean it up.


sCompany=oSession.CompanyCode
If oSession.Updating > 0 or oSession.StartProgram <> "SO_INVOICE_UI" THEN 

Exit Sub

	if sCompany = "ROI" Then

	retVal = 0
	invoiceType  = ""
	retVal = oHeaderObj.GetValue("InvoiceType$", invoiceType)

		If invoiceType = "IN" Then

		strTemp = ""
		Set oLines = oBusObj.AsObject(oBusObj.Lines)

		retVal = oLines.MoveFirst()

		Do While Not(cBool(oLines.EOF))
    		retVal = oLines.GetValue("ItemCode$", strTemp)
    		retVal = oSession.AsObject(oSession.UI).MessageBox(strTemp)
   		retVal = oLines.MoveNext()
		Loop

			If strTemp <> "/ART" then

			Set oMyObj = oSession.AsObject(oSession.GetObject("CM_UDTMaint_bus","CI_UDT_ArtCharges"))

			retVal = oMyObj.Find(SalesOrder)

			retVal = 0
			order = ""

			retVal = oBusObJ.GetValue("SalesOrderNo$", order)

				If retVal = order then

				retVal =0
				oArt = ""

				oArt = oSession.GetObject("CM_UDTMaint_bus", "CI_UDT_ArtCharges")
				retVal = oBusObj.AddLine()
				retVal = oBusObj.SetValue("ItemCode$", "/ART")
				retVal = oBusObj.SetValue("ItemType$", "5")
				retVal = oBusObj.SetValue("Ordered$", "1")
				retVal = oBusObj.SetValue("UnitPrice$", "UDT_ART_TOTAL")
				retVal = oBusObj.Write()
				r = oScript.ActivateProcedure("*ALL*")
					If retVal<>0 Then
					oScript.LinesAdded = 1

    					End If
    				End If
    			End If
    		End If
    	End If

jland47 · Accepted Answer

Just tested every possible example and all worked accordingly, probably will enable debug mode to learn more about it. Also took out the extra lines since item was just a charge as you mentioned. Final code below, greatly appreciated!

aValidCompanies = Array("ROI")
aValidStartPrograms = Array("SO_Invoice_UI")
aValidInvoiceTypes = Array("IN")
sItemCodeToFind = "/ART"
If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
	If oSession.Updating = 0 Then
		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
				If sSalesOrderNo <> "" Then
					Set oLines = oSession.AsObject(oBusObj.Lines)
					oLines.MoveFirst
					bItemCodeToFindLineExists = False
					Do Until CBool(oLines.Eof)
						If Not(bItemCodeToFindLineExists) Then
							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
								bItemCodeToFindLineExists = True
							End If
						End If
						oLines.MoveNext
					Loop
					If Not(bItemCodeToFindLineExists) Then
						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) = 0 Then
							nSO_UDT_ARTCHARGES_Svc = 0 : nSO_UDT_ARTCHARGES_Svc = oSession.GetObject("CM_UDT_Svc", "SO_UDT_ARTCHARGES")
							If nSO_UDT_ARTCHARGES_Svc <> 0 Then
								oSession.DropObject nSO_UDT_ARTCHARGES_Svc
								nSO_UDT_ARTCHARGES_Svc_ChildCollection = 0 : nSO_UDT_ARTCHARGES_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ARTCHARGES", "CM_UDT_Svc", "SalesOrderNo$",	"kPrimary", "SO_UDT_ARTCHARGES")
							End If
						End If
						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) > 0 Then
							nSalesOrderNo_ARTCHARGES = 0 : nSalesOrderNo_ARTCHARGES = oBusObj.GetChildHandle("SalesOrderNo_ARTCHARGES")
							If nSalesOrderNo_ARTCHARGES <> 0 Then
								Set oSalesOrderNo_ARTCHARGES = oSession.AsObject(nSalesOrderNo_ARTCHARGES)
								nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ARTCHARGES")
								If nRetVal <> 0 Then
									sUDF_Art_OrderNo = "" : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_ORDERNO$", sUDF_Art_OrderNo
									If bArgDebugPrint Then oScript.DebugPrint "sUDF_Art_OrderNo: " & sUDF_Art_OrderNo
									If UCase(sSalesOrderNo) = UCase(sUDF_Art_OrderNo) Then
										bAddLine = False
										If oSession.UI = 0 Then
											bAddLine = True
										Else
											sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?", "Style=YesNo, Icon=?")
											If UCase(sRetVal) = UCase("Yes") Then
												bAddLine = True
											End If
										End If
										If bAddLine Then
											nUDF_Art_Charge = 0 : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_CHARGE", nUDF_Art_Charge
											If bArgDebugPrint Then oScript.DebugPrint "nUDF_Art_Charge: " & nUDF_Art_Charge
											nRetVal = 0 : nRetVal = oLines.AddLine()
											If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal
											nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
											oLines.SetValue "ExtensionAmt", nUDF_Art_Charge
											nRetVal = 0 : nRetVal = oLines.Write()
											If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal
										End If
									End If
								End If
								Set oSalesOrderNo_ARTCHARGES = Nothing
							End If
						End If
					End If
				End If
			End If
		End If
	End If
End If

David Speck · Answer

Something like this should get you on the right track. I don't know the structure of your CI_UDT_ArtChanges UDT so I just guessed the field names that would hold the sales order number and art total (assuming you are getting the art total from the UDT even though your script was using a hardcoded string). You need to replace C h r with Chr because the forum doesn't like the function in code. This code uses an experimental method of mine to create the object handle as a child collection object so you don't have to constantly drop and get a new handle every time the script is triggered. I would also suggest assigning this script to the SO_InvoiceHeader's Pre-Write or Pre-Total event as that is where I think it is most appropriate based on your post. aValidCompanies = Array("ROI") aValidStartPrograms = Array("SO_Invoice_UI") aValidInvoiceTypes = Array("IN") sItemCodeToFind = "/ART" If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then If oSession.Updating = 0 Then If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo If sSalesOrderNo <> "" Then Set oLines = oSession.AsObject(oBusObj.Lines) oLines.MoveFirst bItemCodeToFindLineExists = False Do Until CBool(oLines.Eof) If Not(bItemCodeToFindLineExists) Then sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode If UCase(sItemCode) = UCase(sItemCodeToFind) Then bItemCodeToFindLineExists = True End If End If oLines.MoveNext Loop If Not(bItemCodeToFindLineExists) Then bAddLine = False If oSession.UI = 0 Then bAddLine = True Else sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?", "Style=YesNo, Icon=?") If UCase(sRetVal) = UCase("Yes") Then bAddLine = True End If End If If bAddLine Then If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ArtCharges")) = 0 Then nCI_UDT_ArtCharges_Svc = 0 : nCI_UDT_ArtCharges_Svc = oSession.GetObject("CM_UDT_Svc", "CI_UDT_ArtCharges") If nCI_UDT_ArtCharges_Svc <> 0 Then oSession.DropObject nCI_UDT_ArtCharges_Svc nCI_UDT_ArtCharges_Svc_ChildCollection = 0 : nCI_UDT_ArtCharges_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ArtCharges", "CM_UDT_Svc", "SalesOrderNo$", "kPrimary", "CI_UDT_ArtCharges") End If End If If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ArtCharges")) > 0 Then nSalesOrderNo_ArtCharges = 0 : nSalesOrderNo_ArtCharges = oBusObj.GetChildHandle("SalesOrderNo_ArtCharges") If nSalesOrderNo_ArtCharges <> 0 Then Set oSalesOrderNo_ArtCharges = oSession.AsObject(nSalesOrderNo_ArtCharges) nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ArtCharges") If nRetVal <> 0 Then sUDF_SalesOrderNo = "" : oSalesOrderNo_ArtCharges.GetValue "UDF_SalesOrderNo$", sUDF_SalesOrderNo If bArgDebugPrint Then oScript.DebugPrint "sUDF_SalesOrderNo: " & sUDF_SalesOrderNo If UCase(sSalesOrderNo) = UCase(sUDF_SalesOrderNo) Then nUDF_ArtTotal = 0 : oSalesOrderNo_ArtCharges.GetValue "UDF_ArtTotal", nUDF_ArtTotal If bArgDebugPrint Then oScript.DebugPrint "nUDF_ArtTotal: " & nUDF_ArtTotal nRetVal = 0 : nRetVal = oLines.AddLine() If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind) oLines.SetValue "QuantityOrdered", 1 oLines.SetValue "QuantityShipped", 1 oLines.SetValue "UnitPrice", nUDF_ArtTotal nRetVal = 0 : nRetVal = oLines.Write() If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal End If End If Set oSalesOrderNo_ArtCharges = Nothing End If End If End If End If End If End If End If End If End If If you have issues with the above code, here is my other method to get an object handle once and allow it to be reused by multiple iterations of the script. aValidCompanies = Array("ROI") aValidStartPrograms = Array("SO_Invoice_UI") aValidInvoiceTypes = Array("IN") sItemCodeToFind = "/ART" If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then If oSession.Updating = 0 Then If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo If sSalesOrderNo <> "" Then Set oLines = oSession.AsObject(oBusObj.Lines) oLines.MoveFirst bItemCodeToFindLineExists = False Do Until CBool(oLines.Eof) If Not(bItemCodeToFindLineExists) Then sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode If UCase(sItemCode) = UCase(sItemCodeToFind) Then bItemCodeToFindLineExists = True End If End If oLines.MoveNext Loop If Not(bItemCodeToFindLineExists) Then bAddLine = False If oSession.UI = 0 Then bAddLine = True Else sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?", "Style=YesNo, Icon=?") If UCase(sRetVal) = UCase("Yes") Then bAddLine = True End If End If If bAddLine Then nCI_UDT_ArtCharges_Svc = 0 : oScript.GetStorageVar "nCI_UDT_ArtCharges_Svc", nCI_UDT_ArtCharges_Svc If IsNumeric(nCI_UDT_ArtCharges_Svc) Then nCI_UDT_ArtCharges_Svc = CLng(nCI_UDT_ArtCharges_Svc) Else nCI_UDT_ArtCharges_Svc = 0 End If If nCI_UDT_ArtCharges_Svc = 0 Then nCI_UDT_ArtCharges_Svc = 0 : nCI_UDT_ArtCharges_Svc = oSession.GetObject("CM_UDT_Svc", "CI_UDT_ArtCharges") If nCI_UDT_ArtCharges_Svc <> 0 Then oScript.SetStorageVar "nCI_UDT_ArtCharges_Svc", nCI_UDT_ArtCharges_Svc End If End If If nCI_UDT_ArtCharges_Svc <> 0 Then Set oCI_UDT_ArtCharges_Svc = oSession.AsObject(nCI_UDT_ArtCharges_Svc) nRetVal = 0 : nRetVal = oCI_UDT_ArtCharges_Svc.Find(sSalesOrderNo) If nRetVal <> 0 Then nUDF_ArtTotal = 0 : oCI_UDT_ArtCharges_Svc.GetValue "UDF_ArtTotal", nUDF_ArtTotal nRetVal = 0 : nRetVal = oLines.AddLine() nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind) oLines.SetValue "QuantityOrdered", 1 oLines.SetValue "QuantityShipped", 1 oLines.SetValue "UnitPrice", nUDF_ArtTotal nRetVal = 0 : nRetVal = oLines.Write() End If Set oCI_UDT_ArtCharges_Svc = Nothing End If End If End If End If End If End If End If End If The script uses arrays at the beginning to define multiple valid values for the company code, start program, and invoice type in case you need to expand it in the future. It uses a single string variable to hold the item code you are looking for. Your script didn't really handle locating the line correctly because you were setting strTemp every time in your loop of the lines and only checked it against "/ART" AFTER the loop, so whatever line was read last would be in your strTemp variable. The code I posted uses a boolean variable to track whether or not the line was found during the loop and it is set to True only if a line is found where the item code matches the value in the sItemCodeToFind variable. After the loop, the boolean variable is evaluated to determine if it needs to bother with the UDT. Indentation goes a long way towards readability so you want to make sure your code is indented appropriately to make it easier to identify logical branches. I like to put criteria evaluated by If statements on their own line when possible instead of combining them so I separated the checks against oSession.Updating and oSession.StartProgram. I don't like to use Exit Sub because multiple scripts assigned to the same business object, event, and with the same priority get compiled into a single subroutine. So using Exit Sub will cause it to exit the entire subroutine and you may have subsequent logic that doesn't execute because of this. This is another case where proper If statements and indentation go a long way. Because of the sensitivity to data types in Sage 100's scripting feature (string and numeric) when interacting with the BOI methods, it is a common practice to prefix the variable with a character to indicate the intended data type (n = numeric, s = string) and you need to make sure to initialize the variable with a default value matching its intended data type before using it with a BOI method because the BOI methods do not support VBScript's Variant data type which is the default for new declared variables. For this reason, I like to initialize the variable at the beginning of the line as seen in my code above. I then use VBScript's compound line indicating character, a colon (":"), to perform the action that interacts with the variable. This makes sure every variable is initialized before being used and in the case of large scripts, eliminates the chance of forgetting to initialize a variable at the beginning of the script. In the case of variables repeated, it also makes sure they are reset to a default value if evaluating them is important. EDIT: Corrected a few issues with both code examples around 11pm central on 4/27/2022 so if you grabbed the code samples before then, you should grab the latest revisions.

Sage 100

SO Invoice Line Addition

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING