Wiki Home

AccPac Automation With VFP


Namespace: WIN_COM_API
More Refactoring

AccPac for Windows Version 5.0A using PervasiveSQL

These particular classes are for adding new AR & AP Invoices to AccPac from a custom Visual Foxpro business application. The classes are totally dynamic now and I use fabricated ADO recordsets to pass my data in/out.


Using IntelliSense in VFP7 has made the testing of this much easier.

Note: I am not including the AP class and samples here as it is very similar to the AR code, just different fields, etc.
* [[Visual FoxPro]] test code
FUNCTION AccPacARTest

LOCAL oArInvoice, oInvHeader, oInvDetail

* create ar object
oARInvoice = CREATEOBJECT("a4winvoice.accpacarinvoice")

* Login
oArInvoice.cDBName = "PPPCOM"
oArInvoice.cLoginName = "ADMIN"
oArInvoice.cPassword = "ADMIN"
oArInvoice.dLoginDate = {04/01/2001}

oArinvoice.accpac_login()

* check for error
llError = IsError()

IF NOT llError

	* create invoice document objects
	oArInvoice.setup_objects()

	llError = IsError()

	IF NOT llError

		* Set batch to use
		oArInvoice.nBatchNo = 841

		* Create header ADO recordset
		oInvHeader = CREATEOBJECT("adodb.recordset")

		WITH oInvHeader

			.CursorLocation = 3  && adUseClient
			.LockType = 3  && adLockOptimistic
			.Fields.Append("IDCUST",129,12)
			.Fields.Append("IDINVC",129,15)
			.Fields.Append("INVCDESC",129,30)
			.Fields.Append("CUSTPO",129,22)
			.Fields.Append("ORDRNBR",129,22)
			.Fields.Append("TEXTOPFL1",129,2)
			.Open()

		ENDWITH


		* Create detail ADO recordset
		oInvDetail = CREATEOBJECT("adodb.recordset")
		WITH oInvDetail

			.CursorLocation = 3  && adUseClient
			.LockType = 3  && adLockOptimistic
			.Fields.Append("IDITEM",129,16)
			.Fields.Append("TEXTDESC",129,30)
			.Fields.Append("COMMENT",129,250)
			.Fields.Append("AMTPRIC",14,10)
			.Fields("AMTPRIC").NumericScale = 5
			.Open()

		ENDWITH


		* Add some data
		oInvHeader.AddNew

		WITH oInvHeader

			.Fields("IDCUST").Value = "131"  && customer.custno
			.Fields("IDINVC").Value = "75343PPP"  && tsfile.pr_number
			.Fields("INVCDESC").Value = "Sale Date: 3/22/96"  && sale date, cancellation info, etc.
			.Fields("CUSTPO").Value = "651795" && tsfile.ts_number
			.Fields("ORDRNBR").Value = "Madera, CA"
			.Fields("TEXTOPFL1").Value = "CA"

		ENDWITH

		oInvDetail.AddNew
		WITH oInvDetail

			.Fields("IDITEM").Value = "PUB"  && Item code
			.Fields("TEXTDESC").Value = "PUBLISH: SIERRA STAR PRESS"  && Description
			.Fields("COMMENT").Value = "02/29,03/07,03/14/96"  && Comment
			.Fields("AMTPRIC").Value = 163.12  && Price
			
			* .AddNew  && could add more detail here, too
			
		ENDWITH
			
		oARInvoice.oInvoiceHeader = oInvHeader
		oARInvoice.oInvoiceDetail = oInvDetail

		oArInvoice.Add_Invoice()
		llError = IsError()

		IF NOT llError

			oArInvoice.Add_Detail()
			llError = IsError()
			IF NOT llError
				oArInvoice.Save_Invoice()
				llError = IsError()
			ENDIF

		ENDIF

	ENDIF

ENDIF

oArInvoice.release_objects()

oARInvoice = NULL

RETURN

FUNCTION IsError(tARInvoice)
	IF NOT EMPTY(oArInvoice.cErrorMsg)
		=MESSAGEBOX(oArInvoice.cErrorMsg+" - "+ALLTRIM(STR(oArInvoice.intErrNum,20,0))+".",0,"Error")
		RETURN .T.
	ELSE
		RETURN .F.
	ENDIF
ENDFUNC



Note: The following code was derived by using the macro recorder in AccPac, very similar to how you would begin with Word or Excel automation. Very nifty. Very long object names are created, however. They do tie out to the actual "view" definitions. AccPac uses the "document" style verbage. An invoice "document" contains several views: header, detail, batch, etc. Because these object types are not supported in VFP (at least I wasn't sure how to do it) I chose to just create a VB wrapper.

' VB code
Option Explicit
Public Session As ACCPACXAPILib.xapiSession

Public ARINVOICE1batch As ACCPACXAPILib.xapiView
Public ARINVOICE1batchFields As ACCPACXAPILib.xapiFields


Public ARINVOICE1header As ACCPACXAPILib.xapiView
Public ARINVOICE1headerFields As ACCPACXAPILib.xapiFields

Public ARINVOICE1detail1 As ACCPACXAPILib.xapiView
Public ARINVOICE1detail1Fields As ACCPACXAPILib.xapiFields

Public ARINVOICE1detail2 As ACCPACXAPILib.xapiView
Public ARINVOICE1detail2Fields As ACCPACXAPILib.xapiFields

Dim ARINVOICE1batchComps(0) As ACCPACXAPILib.xapiView

Dim ARINVOICE1headerComps(2) As ACCPACXAPILib.xapiView

Dim ARINVOICE1detail1Comps(1) As ACCPACXAPILib.xapiView

Dim ARINVOICE1detail2Comps(0) As ACCPACXAPILib.xapiView

Public cErrorMsg As String
Public intErrNum As Long

Public cLoginName As String
Public cPassword  As String
Public cDBName    As String
Public dLoginDate As Date

Public cDocumentNo As String

Public nBatchNo    As Integer

Public oInvoiceHeader  As ADODB.Recordset
Public oInvoiceDetail  As ADODB.Recordset

Public oArBatch        As ADODB.Recordset


Public Sub Add_Invoice()

On Error GoTo ACCPACErrorHandler

cErrorMsg = ""

ARINVOICE1detail1.Cancel
ARINVOICE1detail2.Cancel
ARINVOICE1headerFields("CNTITEM").PutWithoutVerification ("1")        ' Entry Number
ARINVOICE1header.Init
ARINVOICE1headerFields("CNTITEM").PutWithoutVerification ("0")        ' Entry Number

ARINVOICE1batchFields("CNTBTCH").PutWithoutVerification (nBatchNo)    ' Batch Number
ARINVOICE1batch.Read
ARINVOICE1headerFields("CNTITEM").PutWithoutVerification ("0")        ' Entry Number
ARINVOICE1header.Browse "(CNTBTCH  = " & nBatchNo & "      )", 1
ARINVOICE1header.Fetch
ARINVOICE1detail1.Cancel
ARINVOICE1detail2.Cancel
ARINVOICE1headerFields("CNTITEM").PutWithoutVerification ("0")        ' Entry Number
ARINVOICE1header.Init

ARINVOICE1headerFields("IDCUST").Value = oInvoiceHeader.Fields("IDCUST").Value                        ' Customer Number

ARINVOICE1headerFields("IDINVC").Value = oInvoiceHeader.Fields("IDINVC").Value                        ' Document Number

ARINVOICE1headerFields("INVCDESC").Value = oInvoiceHeader.Fields("INVCDESC").Value                    ' Invoice Description

ARINVOICE1headerFields("CUSTPO").Value = oInvoiceHeader.Fields("CUSTPO").Value                        ' PO Number

ARINVOICE1headerFields("ORDRNBR").Value = oInvoiceHeader.Fields("ORDRNBR").Value                      ' Sales Order Number

ARINVOICE1headerFields("CODETAXGRP").Value = "ALL"                                                    ' Tax Group

ARINVOICE1headerFields("TEXTOPFL1").Value = oInvoiceHeader.Fields("TEXTOPFL1").Value                  ' Optional Field 1

Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub

Public Sub Add_Detail()

On Error GoTo ACCPACErrorHandler

ARINVOICE1detail1.Order = 0
ARINVOICE1detail1.Init

cErrorMsg = ""

oInvoiceDetail.MoveFirst

Do While Not oInvoiceDetail.EOF

   ARINVOICE1detail1Fields("IDITEM").Value = oInvoiceDetail.Fields("IDITEM").Value           ' Item Number
   ARINVOICE1detail1Fields("TEXTDESC").Value = oInvoiceDetail.Fields("TEXTDESC").Value
   ARINVOICE1detail1Fields("COMMENT").Value = oInvoiceDetail.Fields("COMMENT").Value
   ARINVOICE1detail1Fields("AMTPRIC").Value = oInvoiceDetail.Fields("AMTPRIC").Value
   ARINVOICE1detail1Fields("SWPRTSTMT").Value = 1


   oInvoiceDetail.MoveNext

   If oInvoiceDetail.EOF Then
        Exit Do
   End If

   ARINVOICE1detail1.Insert
   ARINVOICE1detail1.Init
   ARINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("-1")      ' Line Number

Loop

ARINVOICE1detail1.Insert


Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub

Public Sub Save_Invoice()

On Error GoTo ACCPACErrorHandler

cErrorMsg = ""

' Save invoice

ARINVOICE1header.Insert

Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub

Public Sub accpac_login()

On Error GoTo ACCPACErrorHandler

cErrorMsg = ""

Set Session = CreateObject("ACCPAC.xapiSession")
Session.Open cLoginName, cPassword, cDBName, dLoginDate, 0

Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub

Public Sub setup_objects()

On Error GoTo ACCPACErrorHandler

cErrorMsg = ""


Set ARINVOICE1batch = Session.OpenView("AR0031", "AR")
Set ARINVOICE1batchFields = ARINVOICE1batch.Fields

Set ARINVOICE1header = Session.OpenView("AR0032", "AR")
Set ARINVOICE1headerFields = ARINVOICE1header.Fields

Set ARINVOICE1detail1 = Session.OpenView("AR0033", "AR")
Set ARINVOICE1detail1Fields = ARINVOICE1detail1.Fields

Set ARINVOICE1detail2 = Session.OpenView("AR0034", "AR")
Set ARINVOICE1detail2Fields = ARINVOICE1detail2.Fields

Set ARINVOICE1batchComps(0) = ARINVOICE1header
ARINVOICE1batch.Compose ARINVOICE1batchComps

Set ARINVOICE1headerComps(0) = ARINVOICE1batch
Set ARINVOICE1headerComps(1) = ARINVOICE1detail1
Set ARINVOICE1headerComps(2) = ARINVOICE1detail2
ARINVOICE1header.Compose ARINVOICE1headerComps

Set ARINVOICE1detail1Comps(0) = ARINVOICE1header
Set ARINVOICE1detail1Comps(1) = ARINVOICE1batch
ARINVOICE1detail1.Compose ARINVOICE1detail1Comps

Set ARINVOICE1detail2Comps(0) = ARINVOICE1header
ARINVOICE1detail2.Compose ARINVOICE1detail2Comps

' Initialize batch recordset
Set oArBatch = CreateObject("ADODB.RECORDSET")
oArBatch.LockType = adLockBatchOptimistic
oArBatch.CursorLocation = adUseClient
oArBatch.Fields.Append "CNTBTCH", adDecimal, 5
oArBatch.Fields("CNTBTCH").NumericScale = 0
oArBatch.Fields.Append "BTCHDESC", adChar, 30
oArBatch.Fields.Append "DATEBTCH", adDate
oArBatch.Fields.Append "BTCHTYPE", adInteger
oArBatch.Fields.Append "BTCHSTTS", adInteger
oArBatch.Fields.Append "CNTINVCENT", adDecimal, 4
oArBatch.Fields("CNTINVCENT").NumericScale = 0
oArBatch.Fields.Append "AMTENTR", adDecimal, 10
oArBatch.Fields("AMTENTR").NumericScale = 3
oArBatch.Open


Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub
Public Sub Batch_List()

On Error GoTo ACCPACErrorHandler


Dim lStatus As Boolean

ARINVOICE1batch.Cancel
' ARINVOICE1batch.Order = 0
ARINVOICE1batch.Browse "BTCHSTTS = 7 OR BTCHSTTS = 1", True
lStatus = ARINVOICE1batch.Fetch

' Clear ADO recordset
If oArBatch.RecordCount > 0 Then
  ' oArBatch.Delete adAffectGroup
  oArBatch.CancelBatch
End If

' Populate ADO recordset
Do While lStatus = True
     oArBatch.AddNew
    oArBatch.Fields("CNTBTCH").Value = ARINVOICE1batch.Fields("CNTBTCH").Value
    oArBatch.Fields("BTCHDESC").Value = ARINVOICE1batch.Fields("BTCHDESC").Value
    oArBatch.Fields("DATEBTCH").Value = ARINVOICE1batch.Fields("DATEBTCH").Value
    oArBatch.Fields("BTCHTYPE").Value = ARINVOICE1batch.Fields("BTCHTYPE").Value
    oArBatch.Fields("BTCHSTTS").Value = ARINVOICE1batch.Fields("BTCHSTTS").Value
    oArBatch.Fields("CNTINVCENT").Value = ARINVOICE1batch.Fields("CNTINVCENT").Value
    oArBatch.Fields("AMTENTR").Value = ARINVOICE1batch.Fields("AMTENTR").Value
    lStatus = ARINVOICE1batch.Fetch
Loop

ARINVOICE1batch.Cancel

Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub
Public Sub Release_Objects()

On Error GoTo ACCPACErrorHandler

cErrorMsg = ""

Set ARINVOICE1detail2Comps(0) = Nothing

Set ARINVOICE1detail1Comps(1) = Nothing
Set ARINVOICE1detail1Comps(0) = Nothing

Set ARINVOICE1headerComps(2) = Nothing
Set ARINVOICE1headerComps(1) = Nothing
Set ARINVOICE1headerComps(0) = Nothing

Set ARINVOICE1batchComps(0) = Nothing

Set ARINVOICE1detail2Fields = Nothing
Set ARINVOICE1detail2 = Nothing

Set ARINVOICE1detail1Fields = Nothing
Set ARINVOICE1detail1 = Nothing

Set ARINVOICE1headerFields = Nothing
Set ARINVOICE1header = Nothing

Set ARINVOICE1batchFields = Nothing
Set ARINVOICE1batch = Nothing

Exit Sub

ACCPACErrorHandler:    'Display error messages

    SetErrorMessage
    Exit Sub

End Sub

Public Sub SetErrorMessage()

    Dim Errors As Variant
    Dim Error As Variant


    If Session Is Nothing Then
       cErrorMsg = "AccPac AR module is not installed or is not configured properly."
       Exit Sub
    End If


    If Session.Database = "" Then

        cErrorMsg = "Accpac Login failed. Check user name and password OR another user in Review Billing"
        intErrNum = Err.Number
        ' cErrorMsg = Err.Description

    Else

         Set Errors = Session.Errors
         If Errors.Count = 0 Then
             cErrorMsg = Err.Description
             intErrNum = Err.Number
         Else
           For Each Error In Errors
                cErrorMsg = cErrorMsg + Error.Description + Chr(13)
                intErrNum = Err.Number
            Next
            Errors.Clear
         End If

         Set Errors = Nothing

    End If

End Sub
Public Property Get oInvHeaderRS() As Variant

End Property

Public Property Let oInvHeaderRS(ByVal vNewValue As Variant)
   Set oInvoiceHeader = vNewValue
End Property

Public Property Get oInvDetailRS() As Variant

End Property

Public Property Let oInvDetailRS(ByVal vNewValue As Variant)
   Set oInvoiceDetail = vNewValue
End Property



Contributors: Randy Jean
Category C _ O _ M, Category Code Samples, Category Project Peer Review
( Topic last updated: 2006.04.07 04:29:36 PM )