General Discussion Topics

[markazali]

Our company is running Sage MAS 500 December 2010 Update (Version 7.30.5.0)

I've been trying to parse Data Import Manager Accounts Payable Job History using the following view/table: vdvDataTrnsfrJobExecute and tdmMigrationLogWrk

The info in tdmMigrationLogWrk does not contain all the key fields I need together for a single import error:

ColumnID ~ColumnValue ~Comment ~Duplicate ~EntityID ~EntryNo ~SessionKey ~Status VouchNo|TranNo ~0001746407|00669169070 ~Duplicate record was not copied ~0 ~00669169070 - IN ~1498285 ~227 ~Failure

Notice there is no way to ID the vendor.

Sorry if the issue seems a little abstract. I'm not to MAS 500 and trying to understand how it stores data better

[jmiles]

Are you looking for an automated solution for parsing the tdmMigrationLogWrk table? If not, couldn't you derive the vendor from the voucher number (or, to a lesser extent, the invoice number)? I'm not familiar with the Job History import job, but, if the voucher already exists in the database, the voucher has a reference back to the vendor.

[JohnHanrahan]

I've had the same problem before.  But the solution is somewhat difficult.  I modified the stored procedures to have better reporting.  This sort of solved the problem but it was a fair amount of work.  I ended up developing a good solution but it requires a developer.  I added a column to the staging tables then preprocessed the data writing to the new column data that was incorrect.  I have asked Sage to do this and got a positive response from their developers but since no one else has asked about it they don't have it on their radar to do (Hint hint).

J

[markazali]

I ended up finding a workaround using some joins on parsed data between the staging/log tables. By parse, I mean the join uses a substring function. It's not elegant but works.

It looks like this:

select

....

....

from

  mas500.dbo.vdvDataTrnsfrJobExecute job with(nolock)   join mas500.dbo.tdmMigrationLogWrk joblines with(nolock) on job.executionid = joblines.sessionkey   join mas500.dbo.stgpendvoucher stg with(nolock) on columnvalue like '%'+stg.vouchno+'|'+left(joblines.entityid, len(joblines.entityid)-5)