Importing Invoices in BOI doesn't import SO number

Question

Hi Forum, 
 I am importing SO Invoices with a BOI script, imports fine but i cannot get the Sales Order Number to update. 
 I have 2 scripts, one to import invoices (I cannot set the SO # here, no error just no SO value in the Invoice Header). 
 the second script imports the Tracking numbers and also works fine except, if I try to update the SO # here i get an error message the i need to set the Batch number and here is the problem. 
 How can I get the batch number, i know that GL_CompanyActiveBatch table has the Batch numbers but unfortunately there are other users entering invoices manually and there are a few SO Invoice Entry batches. 
 Here is the code that i am using on the second script: 
 r = oInv.nSetKeyValue("InvoiceNo$", sInvoice_Num) r = oInv.nSetKey() if r = 0 then MsgBox(oInv.sLastErrorMsg & sInvoice_Num & vbCRLF & "Inv Header Failed") end if r = oInv.nSetValue("SalesOrderNo$", strOrderNo) r = oInv.nWrite() 
 *** this are the error messages ***

Why i cannot update the sales order when i am importing the Invoices. I am Importing the order from a text file with all the lines with invoice and SO numbers. 
 This is the code i use in the invoice import script: 
 retHdrKey = o.nClear() msgbox "Last Order: " & sOrdNum r = oInv.nSetValue("SalesOrderNo$", sOrdNum) r = oInv.nWrite() 
 
 I think i asked this same question time ago but i couldn't find any notes about it. 
 Regards, 
 Manuel Roman

Kevin M · Answer

I am not sure of the scripting implications, but I've run into similar problems creating invoices from SO with VI imports. 
 If batches are enabled, it is a required field. For a script you probably need to create / take ownership of a batch # separately. (VI does that when adding the field to a job). 
 Get / set the invoice #, then the SO# (which cannot be locked). 
 As you add lines, be sure to not set ItemCode... but instead use the LineKey from the SO, set as the OrderLineKey in the invoice detail table (then quantity shipped...). This allows you to invoice lines from the SO, instead of adding new lines to the SO.

David Speck · Answer

Any entry class that can have batches enabled will have a BatchEnabled property that you can check, if it is not equal to 0 then you need to use the SelectBatch method. 
 
 It should be inherited from the SY_BatchDataEntry class. 
 File Layouts and Program Information - SY_BatchDataEntry (sage.com) 
 If you are having to update a previously saved invoice record, then you will need to get the batch number first. You can do this with GetResultSets or using ODBC. GetResultSets will be a little more streamlined since you'll just be using the object to call the method. In your case, something like the following should work. Change Ch r to Chr. 
 If oInv.nBatchEnabled <> 0 Then
	sBatchNo = ""
	sColumns1 = "BatchNo$"
	sColumns2 = "InvoiceNo$"
	sResults1 = ""
	sResults2 = ""
	sFilter = "UCS(InvoiceNo$)=UCS(""" & sInvoiceNo & """)"
	sKeyBegin = UCase(sInvoiceNo)
	sKeyEnd = UCase(sInvoiceNo) & Ch r(255)
	oInv.MoveFirst
	nRetVal = 0 : nRetVal = oInv.nGetResultSets(sColumns1, sColumns2, sResults1, sResults2, sFilter, sKeyBegin, sKeyEnd)
	If nRetVal = 1 Then
 sBatchNo = Mid(sResults1, 3, Len(sResults1) - 3)
	End If
	nRetVal = 0 : oInv.sLastErrorMsg = "" : nRetVal = oInv.nSelectBatch(sBatchNo)
	If nRetVal = 0 Then 
 MsgBox "oInv.nSelectBatch(""" & sBatchNo & """): " & nRetVal & vbCrLf & "oInv.sLastErrorMsg: " & oInv.sLastErrorMsg
	End If
End If 
 More info on GetResultSets can be found here. 
 https://help-sage100.na.sage.com/2020/FLOR/index.htm#Object_Reference/BaseSystem/SY_Service.htm 
 
 For the sales order number, as Kevin said if you are manually adding the lines to the invoice for a sales order, you need to have the OrderLineKey. You can also use the CopyLinesFromSalesOrder method. 
 https://help-sage100.na.sage.com/2020/FLOR/index.htm#Object_Reference/SO/SO_InvoiceDetail_Bus.html 
 
 You pass the sales order number as the first argument and can optionally pass "YES" as the second argument to ship all lines completely. 
 However, if you are on version 2019+ where they added the feature to invoice multiple sales orders on one invoice, you may need to revise this to work with those new methods. 
 Check this thread Receipt of Invoice adding lines using BOI with multiple POs - Business Object Interface - Sage 100 - Sage City Community , it is for receipts for purchase orders so just swap the "purchaseorder" in the method names for "salesorder". 
 More info on those methods can be found here. 
 https://help-sage100.na.sage.com/2020/FLOR/index.htm#Object_Reference/SO/SO_Invoice_bus.html

Sage 100

Importing Invoices in BOI doesn't import SO number

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING