Recorded Marco for Mysis Purchasing didn't process completely 2

[Tianjin]

I recorded a Marco for receiving material in Mysis purchasing module. When I tried to re-use this code to process receiving other material. It shows integrity issue where STOCK didn't get increased and ONORDER QTY didn't get decremented. The only changes I made to the recorded code are replacing Po number and line number with the one will be processed, and I also removed lines with “temp=bla bla” and view compose with blank parameter which caused code crash(why?). I don’t know if these changes are the problems and how to modify them correctly, or Macro recording itself missed the certain part of logic process. I monitored backend database and found two tables(MILOGH-MI0013 and MILOGD-MI0014) didn’t get new records inserted, but if I process receiving through accpac-mysis interface, two new records will be added into those two tables. My questions are
first, does macro record every thing happened while working through the accpac interface.
Second, if Marco recording do missed certain part of business logic, can it be manually amended by inserting two records into the tables directly.

System Info:
Accpac5.4
Mysis 5.4
Database: pervasive9.5

Recorded Code:
Sub MainSub()

On Error GoTo ACCPACErrorHandler

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 MIGLOB1 As AccpacCOMAPI.AccpacView
Dim MIGLOB1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0008", MIGLOB1
Set MIGLOB1Fields = MIGLOB1.Fields


MIGLOB1.Init
MIGLOB1.Browse "", 1
MIGLOB1.Fetch
Dim MIXPPO9header As AccpacCOMAPI.AccpacView
Dim MIXPPO9headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0110", MIXPPO9header
Set MIXPPO9headerFields = MIXPPO9header.Fields

Dim MIXPPO9detail As AccpacCOMAPI.AccpacView
Dim MIXPPO9detailFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0008", MIXPPO9detail
Set MIXPPO9detailFields = MIXPPO9detail.Fields

MIXPPO9header.Compose Array(MIXPPO9detail)

'MIXPPO9detail.Compose Array() 'if not remove this line, VBA Crashed


MIXPPO9header.Init
Dim MIPOH2header As AccpacCOMAPI.AccpacView
Dim MIPOH2headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0018", MIPOH2header
Set MIPOH2headerFields = MIPOH2header.Fields

Dim MIPOH2detail As AccpacCOMAPI.AccpacView
Dim MIPOH2detailFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0019", MIPOH2detail
Set MIPOH2detailFields = MIPOH2detail.Fields

MIPOH2header.Compose Array(MIPOH2detail, Nothing, Nothing, Nothing, Nothing)

MIPOH2detail.Compose Array(MIPOH2header, Nothing, Nothing)


Dim MIXTAX10header As AccpacCOMAPI.AccpacView
Dim MIXTAX10headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0118", MIXTAX10header
Set MIXTAX10headerFields = MIXTAX10header.Fields

Dim MIXTAX10detail1 As AccpacCOMAPI.AccpacView
Dim MIXTAX10detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0018", MIXTAX10detail1
Set MIXTAX10detail1Fields = MIXTAX10detail1.Fields

Dim MIXTAX10detail2 As AccpacCOMAPI.AccpacView
Dim MIXTAX10detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0019", MIXTAX10detail2
Set MIXTAX10detail2Fields = MIXTAX10detail2.Fields

MIXTAX10header.Compose Array(MIXTAX10detail1, MIXTAX10detail2)

MIXTAX10detail1.Compose Array(MIXTAX10detail2, Nothing, Nothing, Nothing, Nothing)

MIXTAX10detail2.Compose Array(MIXTAX10detail1, Nothing, Nothing)


Dim MIXCRU11header As AccpacCOMAPI.AccpacView
Dim MIXCRU11headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0100", MIXCRU11header
Set MIXCRU11headerFields = MIXCRU11header.Fields

Dim MIXCRU11detail1 As AccpacCOMAPI.AccpacView
Dim MIXCRU11detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0008", MIXCRU11detail1
Set MIXCRU11detail1Fields = MIXCRU11detail1.Fields

Dim MIXCRU11detail2 As AccpacCOMAPI.AccpacView
Dim MIXCRU11detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0029", MIXCRU11detail2
Set MIXCRU11detail2Fields = MIXCRU11detail2.Fields

Dim MIXCRU11detail3 As AccpacCOMAPI.AccpacView
Dim MIXCRU11detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "MI0030", MIXCRU11detail3
Set MIXCRU11detail3Fields = MIXCRU11detail3.Fields

MIXCRU11header.Compose Array(MIXCRU11detail1, MIXCRU11detail2, MIXCRU11detail3)

'MIXCRU11detail1.Compose Array() 'if not remove this line, VBA Crashed

MIXCRU11detail2.Compose Array(MIXCRU11detail3)

MIXCRU11detail3.Compose Array(MIXCRU11detail2)

' from here, I am going to proccess PO "27410" and line "3000"

MIPOH2detail.Init
MIPOH2detail.Browse "", 1
MIPOH2detail.Fetch
MIXTAX10detail1Fields("ID").Value = "27410" ' PO No. replace with Po No. will be processed
MIPOH2headerFields("ID").Value = "27410" ' PO No. replace with Po No. will be processed
MIPOH2header.Read
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch
temp = MIPOH2header.Exists
MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No.

MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No.

MIPOH2detail.Read
MIPOH2header.Read
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No.

MIPOH2detail.Read
MIXPPO9header.Init
MIXCRU11header.Init
MIXCRU11header.Process
MIPOH2header.Read
MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No., Change to 3000 from previous value
MIPOH2detail.Read
MIXPPO9header.Init
MIXCRU11header.Process

MIXPPO9headerFields("REAL").PutWithoutVerification ("1") ' Real Flag
MIXPPO9headerFields("TYPE").PutWithoutVerification ("12") ' Transfer Type

MIPOH2detailFields("RECEIVED").Value = "2.000000" ' Received
MIPOH2detail.Update
MIPOH2header.Update
MIPOH2detail.Init
MIXPPO9header.Init
MIPOH2detail.Browse "", 1

MIPOH2detailFields("PODID").PutWithoutVerification ("-2147483647") ' Detail No.

MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("1000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("2000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("4000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIXPPO9header.Init
MIPOH2detail.Fetch
MIXCRU11header.Init
MIXCRU11header.Process
MIPOH2header.Read
MIPOH2detailFields("PODID").PutWithoutVerification ("3000") ' Detail No.
MIPOH2detail.Read
MIXPPO9header.Init
MIPOH2detailFields("PODID").PutWithoutVerification ("1000") ' Detail No.


'*************************************************************************
'###########################################################################

MIPOH2detail.Read
MIPOH2header.Read
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Init
MIPOH2detail.Browse "", -1

MIPOH2detailFields("PODID").PutWithoutVerification ("5000") ' Detail No.

MIPOH2detail.Read
MIPOH2detail.Browse "", -1
MIPOH2detail.Fetch
MIPOH2detail.Fetch
MIPOH2detail.Fetch

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

 

[Jay Converse]

1. Macros do not always record fully. Bank Services records nothing at all.

2. MI0008 is not a composed view.

3. Never, I repeat, never insert records directly into the database, that means instant data corruption.

4. Rvspy will provide low-level details that macro recording does not.

 

[Tianjin]

Thanks tuba2007

I will try Rvspy and see what is in there. Can I interpret your idea into that as soon as I find low-level details, I can add extra code into recorded macro to accomplish the portion that original marco missed, or anything thing eles I can do for this actually a very common process in accpac.

Thanks again

 

[ettienne]

There is still a lot of noise in the macro.

In a simplified form this is what you need to do:

DIM all objects
Open DB and views
Compose views
Put header key
Read header
Put detail key
Read detail
Put detail values
Update detail
Update header
Close views
Close DB
Set objects = nothing

 

[Tianjin]

Thanks guys for the great points.

I just stepped into accpac utility RVspy and DBspy, and it is just too many information to be useful for me. It is way beyond my knowledge.
I still think Posting in receiving is just simplest and routine job and there should not be a problem to record a runnable macro in this popular spot. As I can see so many successful examples in this forum is much complex than mines.
I suspect maybe certain flaw in our system could be a reason. Can anyone do me favour do a testing in you system for me and if it is really macro recording problem. So I can decide which direction to go.
One more question, what view compose exactly do in the macro? I have read system manager user guide and still didn't get the point. if accpac logic handle data process, it should know in what scenario which views should be grabs together. why this has to be done externally by user? sorry for this newbie question.

Thank you very much

 

[Jay Converse]

The problem isn't your system, it records exactly the same on mine. Clearly, the Misys developers didn't expose their code for macro editing.

The reason views are composed manually is that you don't necessarily need them. For example, if you're just reading Ar invoice records from ARIBH, you don't need to compose ARIBC and ARIBD. But you do if you're inserting new batches.

 

[ettienne]

You only need 2 views in the macro: MIPOH2header and MIPOH2detail, the rest is not required.

 

[Tianjin]

Tuba2007,

Thank you very much for your testing. I know the macro you recorded should be the same as mines. However I am not sure you test the macro against a dummy receiving process. My concern and also a hope is looking forward that, as UI do, your macro can run through all necessary data process that wrapped in either update or process statement. This should at least includes resulting changes of stock and onorderqty. If you can accomplish it, I can work and correct on my system. otherwise, I will go and question mysis for this basic functionality. This really a serious responsibility that should make thing work, rather than screw thing up. just imaging how danger it is if you can run through macro without problem, but behind the scenes it could potentially lose part of business logic or corrupt your database.

Hi ettienne,
I will take your suggestion do a housekeeping with only two required views.

Any further feeding from both of you guys will be very vary appreciate.

 

[ettienne]

Misys support are very good when you need help with macros, they have helped me out more than once.