x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 133 subscribers
  • Views 732 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
756
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
408
replies
Philippe Couture

ACCPACCOMAPI Order Prepayment not working

Posted By

Hi,

I'm trying to automate order ->prepayment->shipment->invoice with COMAPI.

I did what I usually do by recording a macro and see the code.

I'm able to create the shipment and the invoice but I cant see the prepayment in ARBTA.

Sub MainSub()
'
' Sage 300 Macro file: \\sql01\SAGE300\Macros\prepiment4.AVB
' Recorded at: Wed Oct 12 15:46:58 2022
'

On Error GoTo ACCPACErrorHandler

' TODO: To increase efficiency, comment out any unused DB links.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim temp As Boolean
Dim OEORD1header As AccpacCOMAPI.AccpacView
Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0520", OEORD1header
Set OEORD1headerFields = OEORD1header.Fields

Dim OEORD1detail1 As AccpacCOMAPI.AccpacView
Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0500", OEORD1detail1
Set OEORD1detail1Fields = OEORD1detail1.Fields

Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0740", OEORD1detail2
Set OEORD1detail2Fields = OEORD1detail2.Fields

Dim OEORD1detail3 As AccpacCOMAPI.AccpacView
Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0180", OEORD1detail3
Set OEORD1detail3Fields = OEORD1detail3.Fields

Dim OEORD1detail4 As AccpacCOMAPI.AccpacView
Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0526", OEORD1detail4
Set OEORD1detail4Fields = OEORD1detail4.Fields

Dim OEORD1detail5 As AccpacCOMAPI.AccpacView
Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0522", OEORD1detail5
Set OEORD1detail5Fields = OEORD1detail5.Fields

Dim OEORD1detail6 As AccpacCOMAPI.AccpacView
Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0508", OEORD1detail6
Set OEORD1detail6Fields = OEORD1detail6.Fields

Dim OEORD1detail7 As AccpacCOMAPI.AccpacView
Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0507", OEORD1detail7
Set OEORD1detail7Fields = OEORD1detail7.Fields

Dim OEORD1detail8 As AccpacCOMAPI.AccpacView
Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0501", OEORD1detail8
Set OEORD1detail8Fields = OEORD1detail8.Fields

Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0502", OEORD1detail9
Set OEORD1detail9Fields = OEORD1detail9.Fields

Dim OEORD1detail10 As AccpacCOMAPI.AccpacView
Dim OEORD1detail10Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0504", OEORD1detail10
Set OEORD1detail10Fields = OEORD1detail10.Fields

Dim OEORD1detail11 As AccpacCOMAPI.AccpacView
Dim OEORD1detail11Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0506", OEORD1detail11
Set OEORD1detail11Fields = OEORD1detail11.Fields

Dim OEORD1detail12 As AccpacCOMAPI.AccpacView
Dim OEORD1detail12Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0503", OEORD1detail12
Set OEORD1detail12Fields = OEORD1detail12.Fields

OEORD1header.Compose Array(OEORD1detail1, Nothing, OEORD1detail3, OEORD1detail2, OEORD1detail4, OEORD1detail5)

OEORD1detail1.Compose Array(OEORD1header, OEORD1detail8, OEORD1detail12, OEORD1detail9, OEORD1detail6, OEORD1detail7)

OEORD1detail2.Compose Array(OEORD1header)

OEORD1detail3.Compose Array(OEORD1header, OEORD1detail1)

OEORD1detail4.Compose Array(OEORD1header)

OEORD1detail5.Compose Array(OEORD1header)

OEORD1detail6.Compose Array(OEORD1detail1)

OEORD1detail7.Compose Array(OEORD1detail1)

OEORD1detail8.Compose Array(OEORD1detail1)

OEORD1detail9.Compose Array(OEORD1detail1, OEORD1detail10, OEORD1detail11)

OEORD1detail10.Compose Array(OEORD1detail9)

OEORD1detail11.Compose Array(OEORD1detail9)

OEORD1detail12.Compose Array(OEORD1detail1)



OEORD1headerFields("DRIVENBYUI").Value = "1"                          ' Driven by UI

OEORD1header.Cancel
OEORD1header.Cancel
OEORD1header.Init
OEORD1detail2.Browse "", 1

OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number

OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
temp = OEORD1header.Exists

OEORD1headerFields("ORDNUMBER").Value = "W000047185"                  ' Order Number

OEORD1header.Order = 1
temp = OEORD1header.Exists
OEORD1header.Read
OEORD1header.Order = 0
OEORD1detail1Fields("LINENUM").PutWithoutVerification ("-32767")      ' Line Number
OEORD1detail1.Browse "", 1

OEORD1detail1Fields("LINENUM").PutWithoutVerification ("-32767")      ' Line Number

OEORD1detail1.Browse "", 1
OEORD1detail1.Fetch
OEORD1detail9Fields("PRNCOMPNUM").PutWithoutVerification ("-2147483647")   ' Parent Component Number

OEORD1detail9Fields("COMPNUM").PutWithoutVerification ("-2147483647")   ' Component Number

OEORD1detail9.Browse "", 1
OEORD1detail9.Fetch
OEORD1detail3Fields("UNIQUIFIER").PutWithoutVerification ("-32767")   ' Uniquifier
OEORD1detail3.Browse "", 1
OEORD1detail3.Fetch
OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number
OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
OEORD1detail2.Browse "", 1

OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number

OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
temp = OEORD1header.Exists
OEORD1detail1Fields("LINENUM").PutWithoutVerification ("32")          ' Line Number
OEORD1detail1.Read
OEORD1headerFields("GOSHIPALL").Value = "1"                           ' Perform Ship All
OEORD1header.Process

OEORD1headerFields("OECOMMAND").Value = "18"                          ' Process O/E Command

OEORD1header.Process

OEORD1headerFields("REBATCHNUM").Value = "12657"                      ' Receipt Batch Number
OEORD1headerFields("BANKRECTYP").Value = "MONERIS"                    ' Receipt Type
OEORD1headerFields("CHECKNUM").Value = "000099999-0000004"            ' Check Number
OEORD1headerFields("BANKCODE").Value = "TRANSIT"                      ' Bank Code
OEORD1headerFields("PARATEDATE").Value = DateSerial(2022, 10, 5)      ' Payment Rate Date
OEORD1headerFields("BANKPAYMNT").Value = "73.000"                     ' Payment In Bank Currency
OEORD1headerFields("PAYMTYPE").Value = "3"                            ' Payment Type
OEORD1headerFields("OECOMMAND").Value = "4"                           ' Process O/E Command
OEORD1headerFields("CHECKDATE").PutWithoutVerification (DateSerial(2022, 10, 5))  ' Check Date

OEORD1header.Process
temp = OEORD1header.Exists
OEORD1header.Update
OEORD1header.Order = 1
OEORD1header.Read
OEORD1header.Order = 0
OEORD1detail1Fields("LINENUM").PutWithoutVerification ("-32767")      ' Line Number
OEORD1detail1.Browse "", 1
OEORD1detail1.Fetch
OEORD1detail9Fields("PRNCOMPNUM").PutWithoutVerification ("-2147483647")   ' Parent Component Number

OEORD1detail9Fields("COMPNUM").PutWithoutVerification ("-2147483647")   ' Component Number

OEORD1detail9.Browse "", 1
OEORD1detail9.Fetch
OEORD1detail3Fields("UNIQUIFIER").PutWithoutVerification ("-32767")   ' Uniquifier
OEORD1detail3.Browse "", 1
OEORD1detail3.Fetch
OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number
OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
OEORD1detail2.Browse "", 1

OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number

OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
temp = OEORD1header.Exists
Dim ARPRTUPDT2 As AccpacCOMAPI.AccpacView
Dim ARPRTUPDT2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0076", ARPRTUPDT2
Set ARPRTUPDT2Fields = ARPRTUPDT2.Fields



OEORD1header.Order = 1
OEORD1header.Read
OEORD1header.Order = 0
OEORD1detail1Fields("LINENUM").PutWithoutVerification ("-32767")      ' Line Number
OEORD1detail1.Browse "", 1
OEORD1detail1.Fetch
OEORD1detail9Fields("PRNCOMPNUM").PutWithoutVerification ("-2147483647")   ' Parent Component Number

OEORD1detail9Fields("COMPNUM").PutWithoutVerification ("-2147483647")   ' Component Number

OEORD1detail9.Browse "", 1
OEORD1detail9.Fetch
OEORD1detail3Fields("UNIQUIFIER").PutWithoutVerification ("-32767")   ' Uniquifier
OEORD1detail3.Browse "", 1
OEORD1detail3.Fetch
OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number
OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
OEORD1detail2.Browse "", 1

OEORD1detail2Fields("PAYMENT").PutWithoutVerification ("-32767")      ' Payment Number

OEORD1detail2.Browse "", -1
OEORD1detail2.Fetch
temp = OEORD1header.Exists


Exit Sub

ACCPACErrorHandler:
  Dim lCount As Long
  Dim lIndex As Long

  If Errors Is Nothing Then
       MsgBox Err.Description
  Else
      lCount = Errors.Count

      If lCount = 0 Then
          MsgBox Err.Description
      Else
          For lIndex = 0 To lCount - 1
              MsgBox Errors.Item(lIndex)
          Next
          Errors.Clear
      End If
      Resume Next

  End If

End Sub

This is very suffering seriously. Is there any real documentation about programming with .NET/ACCPACCOMAPI/COMPOSITION ? 

Developping by recording macro is not the best thing I've send in my developper career.

  • 0

    There's some sparse documentation but recording macros and asking questions are how many of us learned.

    With regards to Prepayments this post should help get to closer:

    https://www.sagecity.com/us/sage300_erp/f/sage-300-reports-macros-and-customizations/53445/oe-prepayments

    In general, you create the receipt batch, create the entry in the receipt batch and then link to it by updating some of the fields in the order header.

    Something along these lines (but this might not be all of the fields and certainly not the values):
    OEORD1headerFields("REBATCHNUM").Value = "39"                         ' Receipt Batch Number
    OEORD1headerFields("BANKRECTYP").Value = "CHECK"                      ' Receipt Type
    OEORD1headerFields("CHECKNUM").Value = "TEST123"                      ' Check Number
    OEORD1headerFields("BANKCODE").Value = "CCB"                          ' Bank Code
    OEORD1headerFields("BANKPAYMNT").Value = "100.000"                    ' Payment In Bank Currency
    OEORD1headerFields("PAYMTYPE").Value = "2"                            ' Payment Type

    I would also post and order, copy the record from SQL Management Studio and paste in Excel.  Go to the same order, create a pre-payment, post order.  Go back to SQL Management Studio, copy the record and paste in Excel.  Then see what changed.

*Community Hub is the new name for Sage City