Hi Forum,
I have a BOI script to import AP Invoices. All works fine, except the invoices with multiple lines.
I add the total and matches the total of the invoice but i get the message "Out of Balance".
I couldn't find anything related to my issue anywhere ...
Any suggestions ?
Here is the script ....
-------------------------------------------------------------------------------------------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("\\Sageserv3\ACCT\Scripts\import.csv", ForReading)
strInvoiceNum = "" : strInvDate = "" : strDueDate = "" : strPartNo = "" : PONum = "" : strPfxPart = "" : sFiber = "" : sWeave = "" : sShiptoName = "" : sShiptoAddress = "" : sShiptoAddress2 = "" : sShiptoAddress3 = "" : sShiptoAddress4 = "" : sShiptoAddress5 = "" : sShiptocsz = "" : sContactInfo = "" : sQualityCode = "" : sLongDescrption = ""
sUOM = "" : nQTY = 0 : nPrice = 0 : nLineAmt = 0 : sContract = "" : sLineItem = "" : strSFX = "" : nPos = 0 : sShipTo = "" : sCustNo = "" : LineInvoiceNo = "" : InvoiceNo = "" : strComment = "" : sUsage = ""
' Create a new header record using the next available order number
InvoiceNo = "" : BatchNo = "" : sDiv = "" : sVendor = ""
IF o.nBatchEnabled = 1 Then
retVal = o.nSelectBatch(BatchNo)
'msgBox "retVal: " & retVal & " Batch no: " & BatchNo
end if
If retVal = 0 Then
MsgBox(o.sLastErrorMsg & vbCRLF & "Batch")
End If
Do While objFile.AtEndOfStream = False
strLine = objFile.ReadLine
'msgbox strLine
'arrFields = Split(strLine, vbTab)
arrFields = Split(strLine, ",")
strInvoiceNum = arrFields(0)
If LineInvoiceNo = "" then
LineInvoiceNo = strInvoiceNum
end if
if LineInvoiceNo <> strInvoiceNum then
'msgbox " total invoice: " & nTotalInvoice
r = o.nSetValue("Comment$", strComment)
r = o.nSetValue("InvoiceAmt", nTotalInvoice)
r = o.nSetValue("NetInvoiceAmt", nTotalInvoice)
'r = o.nSetValue("NonTaxableAmt", nTotalInvoice)
retval = o.nWrite()
nTotalInvoice = 0
If retVal = 0 Then
MsgBox(o.sLastErrorMsg & vbCRLF & "Write Header")
End If
end if
strShipTo = arrFields(2)
strInvDate = arrFields(3)
strDueDate = arrFields(4)
strPartNo = arrFields(5)
strComment = arrFields(6)
res = "" : res = IsNumeric(trim(arrFields(9)))
if res = "True" then
nLineAmt = CDbl(trim(arrFields(9)))
else
nLineAmt = 0
End if
strSFX = left(strShipTo,1) & right(strShipTo,1)
nPos = InStr(strPartNo, "-")
if nPos = 0 then
strPfxPart = strPartNo
else
strPfxPart = left(strPartNo, nPos-1)
strPFX = left(strPartNo, nPos-1)
end if
' read UDT
r=0 : strPfxDesc = "" : strItemCode = "" : GLAccountKey = ""
'r = oPFX.nSetKeyValue("UDF_MISC_PREFIXE_CODE$", strPFX)
r = oPFX.nFind(strPFX)
if r <> 0 then
r = oPFX.nGetValue("UDF_PREFIX_3_CHAR$", strPfxDesc)
strItemCode = "/RM" & strPfxDesc & strSFX
else
strItemCode = "/" & strPfxPart
end if
'msgbox strInvoiceNum & " - " & strInvDate & " - " & strPartNo & " - " & nLineAmt & " ItemCode: " & strItemCode
r = 0
r = oCIItem.nFind(strItemCode)
If r = 0 Then
MsgBox(oCIItem.sLastErrorMsg & vbCRLF & strItemCode & " ItemCode Not Found")
End If
r = oCIItem.nGetValue("PurchaseAcctKey$", GLAccountKey)
' Create a new header record using the next available Invoice number
InvoiceNo = "" : BatchNo = "" : sDiv = "00" : sVendor = ""
'r = o.nGetNextInvoiceNo(InvoiceNo)
sDiv = "00" : sVendor = "CVS"
r = o.nSetKeyValue("APDivisionNo$", sDiv)
r = o.nSetKeyValue("VendorNo$", sVendor)
r = o.nSetKeyValue("InvoiceNo$", strInvoiceNum)
'r = o.nSetKeyValue("InvoiceType$", "IN")
InvoiceKey = "" : InvoiceKey = sDiv & sVendor & strInvoiceNum
'msgbox "Record Key: " & InvoiceKey & " Acct: " & GLAccountKey
r = o.nSetKey()
If r = 0 Then
MsgBox(o.sLastErrorMsg & vbCRLF & "Header Key not Correct")
End If
' Add Header values
r = 0
'r = o.nSetValue("APDivisionNo$", sDiv)
'r = o.nSetValue("VendorNo$", sVendor)
LineInvoiceNo = strInvoiceNum
r = o.oLines.nAddLine()
r = o.oLines.nSetValue("AccountKey$",GLAccountKey)
r = o.oLines.nSetValue("DistributionAmt", nLineAmt)
r = o.oLines.nSetValue("CommentText$", strComment)
nTotalInvoice = nTotalInvoice + nLineAmt
'msgbox " total invoice: " & nTotalInvoice & " Line Amt: " & nLineAmt
r = o.oLines.nWrite()
If r = 0 Then
MsgBox(o.oLines.sLastErrorMsg & vbCRLF & "Writing Lines to Memory")
End If
s=1
'r = objFile.nMoveNext()
'end if
loop
retval = o.nWrite()
If retVal = 0 Then
MsgBox(o.sLastErrorMsg & vbCRLF & "Write Header Error")
End If
-------------------------------------------------------------------------------------------------
Regards,
Manuel Roman