AR Invoice insert from staging table to ACCPAC

[SMI168]

Hi there, I need to insert invoices from a staging table into ACCPAC but seem to be getting the error : "Error HRESULT E_FAIL has been returned from a call to a COM component" on the insert statement. I've coded my app similarly to way the the VBA macro was created. Please can someone help me and point out my error. Below is the code. Thanking you in advance...

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
'Create session to accpac...
session = New Session()

'Initialise session and login...
session.Init("", "XX", "XX0001", "55")
session.Open("USER1", "ACCPAC", "CLDDAT", System.DateTime.Now, 0)

InitializeDBLink()
OpenInvoice()

Dim temp As Boolean
temp = ARINVOICE1batch.Exists
ARINVOICE1batch.Init()

temp = ARINVOICE1header.Exists
ARINVOICE1header.Init()

temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.Init()

temp = ARINVOICE1detail2.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail3.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail4.Exists
ARINVOICE1detail2.Init()

Dim objconnection As New SqlConnection("server=mssql-fin.hosting.local;database=INT_TCC_INTEGRATION;user id=*****;password=****")
Dim SQL As String

SQL = "select * from AI_Invoices"

objconnection.Open()

Dim cmd As New SqlCommand(SQL, objconnection)

Dim ds As New DataSet
Dim SqlDataAdapter = New SqlDataAdapter(cmd).Fill(ds, "AI_Invoices")

For Each dr As DataRow In ds.Tables(0).Rows


ARINVOICE1batch.Browse("((BTCHSTTS = 1) OR (BTCHSTTS = 7))", 1)
temp = ARINVOICE1batch.Exists
ARINVOICE1batch.RecordCreate(1)

ARINVOICE1batchFields.FieldByName("PROCESSCMD").SetValue("1", False) ' Process Command

ARINVOICE1batch.Process()
ARINVOICE1batch.Read(0)
temp = ARINVOICE1header.Exists
ARINVOICE1header.RecordCreate(2)
ARINVOICE1detail1.Cancel()
ARINVOICE1batchFields.FieldByName("BTCHDESC").SetValue(dr("BatchDesc").ToString(), False) ' Description
ARINVOICE1batch.Update()
ARINVOICE1headerFields.FieldByName("INVCTYPE").SetValue(dr("HeaderInvType").ToString(), False) ' Invoice Type

ARINVOICE1headerFields.FieldByName("IDCUST").SetValue(dr("HeaderCustNumber").ToString(), False) ' Customer Number

ARINVOICE1headerFields.FieldByName("IDINVC").SetValue(dr("HeaderDocNumber").ToString(), False) ' Document Number

temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.RecordClear()
temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.RecordCreate(0)

ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False) ' Revenue Account
'ARINVOICE1headerFields.FieldByName("AMTEXTN").SetValue(dr("DetailTaxStatus").ToString(), False) ' Extended Amount w/ TIP

ARINVOICE1detail1.Insert()

ARINVOICE1detail1Fields("CNTLINE").SetValue("-1", False) ' Line Number

ARINVOICE1detail1.Read(0)
temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.RecordCreate(0)

ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False) ' Revenue Account
ARINVOICE1headerFields.FieldByName("AMTEXTN").SetValue(dr("DetailTaxStatus").ToString(), False) ' Extended Amount w/ TIP

temp = ARINVOICE1detail1.Exists

'ARINVOICE1detail1Fields("TAXSTTS1").SetValue = ("3", False) ' Tax Class 1

ARINVOICE1detail1.Insert()

ARINVOICE1detail1Fields("CNTLINE").SetValue("-2", False) ' Line Number

ARINVOICE1detail1.Read(0)
ARINVOICE1headerFields.FieldByName("SPECINST").SetValue(dr("HeaderInstructions").ToString(), False) ' Special Instructions

ARINVOICE1headerFields.FieldByName("ORDRNBR").SetValue(dr("HeaderOrderNumber").ToString(), False) ' Order Number
ARINVOICE1headerFields.FieldByName("CUSTPO").SetValue(dr("HeaderPO").ToString(), False) ' PO Number
'ARINVOICE1headerFields("INVCDESC").PutWithoutVerification("Summary Entry") ' Invoice Description
ARINVOICE1headerFields.FieldByName("IDSHIPNBR").SetValue(dr("HeaderShipNumber").ToString(), False) ' Shipment Number

ARINVOICE1header.Insert()
ARINVOICE1detail1.Read(0)
ARINVOICE1detail1.Read(0)
ARINVOICE1batch.Read(1)
temp = ARINVOICE1header.Exists
ARINVOICE1header.RecordCreate(2)
ARINVOICE1detail1.Cancel()

Next

Try

'Display a message that the record was added...
MessageBox.Show("Invoices Added", "Click OK to continue", MessageBoxButtons.OK, _
MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
Catch SqlExceptionErr As SqlException
MessageBox.Show("ACCPAR ERROR", "ACCPAC IE Customers", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try


DisposeConnection()
End Sub

 

[Jay Converse]

Your views aren't composed.

 

[SMI168]

The Views are composed in a function just before the button insert code. It looks like this :

Public Sub OpenInvoice()

ARINVOICE1batch = mDBLinkCmpRW.OpenView("AR0031")
ARINVOICE1batchFields = ARINVOICE1batch.Fields

ARINVOICE1header = mDBLinkCmpRW.OpenView("AR0032")
ARINVOICE1headerFields = ARINVOICE1header.Fields

ARINVOICE1detail1 = mDBLinkCmpRW.OpenView("AR0033")
ARINVOICE1detail1Fields = ARINVOICE1detail1.Fields

ARINVOICE1detail2 = mDBLinkCmpRW.OpenView("AR0034")
ARINVOICE1detail2Fields = ARINVOICE1detail2.Fields

ARINVOICE1detail3 = mDBLinkCmpRW.OpenView("AR0402")
ARINVOICE1detail3Fields = ARINVOICE1detail3.Fields

ARINVOICE1detail4 = mDBLinkCmpRW.OpenView("AR0401")
ARINVOICE1detail4Fields = ARINVOICE1detail4.Fields

Dim vb As ACCPAC.Advantage.View() = {ARINVOICE1header}
ARINVOICE1batch.Compose(vb)

Dim vh As ACCPAC.Advantage.View() = {ARINVOICE1batch, ARINVOICE1detail1, ARINVOICE1detail2, ARINVOICE1detail3}
ARINVOICE1header.Compose(vh)

Dim vd1 As ACCPAC.Advantage.View() = {ARINVOICE1header, ARINVOICE1batch, ARINVOICE1detail4}
ARINVOICE1detail1.Compose(vd1)

Dim vd2 As ACCPAC.Advantage.View() = {ARINVOICE1header}
ARINVOICE1detail2.Compose(vd2)

Dim vd3 As ACCPAC.Advantage.View() = {ARINVOICE1header}
ARINVOICE1detail3.Compose(vd3)

Dim vd4 As ACCPAC.Advantage.View() = {ARINVOICE1detail1}
ARINVOICE1detail4.Compose(vd4)

End Sub

If you look in the insert code, i call up this function "OpenInvoice()" there...

 

[Jay Converse]

First, take out all the "temp=" statements, they're just noise from the macro recording.

Second, you're creating a new batch with each input record, ARINVOICE1batch.RecordCreate(1) should be outside the loop.

Third, which insert is failing, the detail or the header?

Fourth, take a look a this VB snippet that runs flawlessly:

ArInvoiceHeader.RecordClear
ArInvoiceHeader.RecordGenerate False
ArInvoiceHeader.Fields("IDCUST") = recOrder.Fields(H_CustomerID)
ArInvoiceHeader.Fields("IDINVC") = recOrder.Fields(H_transactionId)
ArInvoiceHeader.Fields("ORDRNBR") = recOrder.Fields(H_transactionId)

Dim iLine As Integer
ArInvoiceDetail1.GoBottom
iLine = ArInvoiceDetail1.Fields("CNTLINE")
ArInvoiceDetail1.RecordClear
ArInvoiceDetail1.Fields("CNTLINE").PutWithoutVerification (iLine)
ArInvoiceDetail1.RecordGenerate False
ArInvoiceDetail1.Fields("IDITEM").Value = recOrder.Fields(D_ITEM)
ArInvoiceDetail1.Fields("TEXTDESC").Value = recOrder.Fields(D_desc)
ArInvoiceDetail1.Fields("AMTPRIC") = Abs(recOrder.Fields(D_Price))
ArInvoiceDetail1.Fields("amtCOST") = Abs(recOrder.Fields(D_cost))
ArInvoiceDetail1.Fields("amtCOgs") = Abs(recOrder.Fields(D_cost))
ArInvoiceDetail1.Insert
ArInvoiceHeader.Insert

 

[SMI168]

Hi tuba2007, thanks a million for this code and advise, my app is now adding invoices to accpac successfully...

 

[SMI168]

Hi Tuba2007, as i mentioned earlier my code seems to be inserting invoices correctly, but the problem i have now is that when I have 2 or more invoices on my staging table with the same invoice number and same customer number but with different product lines i get an error on my insert again. How do i go about adding multiple lines to one one invoice header? Also, I do not make use of the COMAPI dll. My code thus far looks like this...

'Create session to accpac...
session = New Session()

'Initialise session and login...
session.Init("", "XX", "XX0001", "55")
session.Open("USER1", "ACCPAC", "CLDDAT", System.DateTime.Now, 0)

InitializeDBLink()
OpenInvoice()

Dim temp As Boolean
temp = ARINVOICE1batch.Exists
ARINVOICE1batch.Init()

temp = ARINVOICE1header.Exists
ARINVOICE1header.Init()

temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.Init()

temp = ARINVOICE1detail2.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail3.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail4.Exists
ARINVOICE1detail2.Init()
Dim objconnection As New SqlConnection("server=mssql-fin.hosting.local;database=INT_TCC_INTEGRATION;user id=****;password=****")
Dim SQL As String

SQL = "select * from AI_Invoices"

objconnection.Open()

Dim cmd As New SqlCommand(SQL, objconnection)

Dim ds As New DataSet
Dim ds1 As New DataSet
Dim SqlDataAdapter = New SqlDataAdapter(cmd).Fill(ds, "AI_Invoices")

ARINVOICE1batchFields.FieldByName("PROCESSCMD").SetValue("1", False)

For Each dr As DataRow In ds.Tables(0).Rows

ARINVOICE1header.RecordClear()
ARINVOICE1header.RecordGenerate(False)
ARINVOICE1headerFields.FieldByName("INVCTYPE").SetValue(dr("HeaderInvType").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDCUST").SetValue(dr("HeaderCustNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDINVC").SetValue(dr("HeaderDocNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("TAXSTTS1").SetValue(dr("DetailTaxStatus").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("AMTEXTN").SetValue(dr("DetailExclValue").ToString(), False)
ARINVOICE1headerFields.FieldByName("SPECINST").SetValue(dr("HeaderInstructions").ToString(), False)
ARINVOICE1headerFields.FieldByName("ORDRNBR").SetValue(dr("HeaderOrderNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("CUSTPO").SetValue(dr("HeaderPO").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDSHIPNBR").SetValue(dr("HeaderShipNumber").ToString(), False)

ARINVOICE1detail1.GoBottom()
ARINVOICE1detail1.RecordClear()
ARINVOICE1detail1.RecordGenerate(False)
ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("AMTEXTN").SetValue(dr("DetailExclValue").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("TAXSTTS1").SetValue(dr("DetailTaxStatus").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("SWDISCABL").SetValue("0".ToString(), False)
ARINVOICE1detail1.Insert()
ARINVOICE1header.Insert()

Next
End Sub

 

[ettienne]

Your code creates 1 invoice per detail line, you need to loop through your details for the same invoice before you insert the header for that invoice.