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