IM - Integration Manager skipping records despite proper query relationships

Just when you thought there wasn't anything more to learn about Integration Manager something else comes along to demistify that theory. I have been involved in a JD Edwards on DB2 and AS/400 systems migration for almost 4 months now and last month I blogged about how Integration Manager can be a powerful tool in multiplatform systems integrations.

Part of disengaging JD Edwards is to write a series of integrations to existing systems running on the AS/400 platform. In the process, the client needed a simple to use tool which required limited programming and maintenance, hence the choice of IM. After unit testing the integrations, everything was A-Ok to begin with the week long systems testing, which would exhause the integrations while allowing the customer to get the overall "feel" for GP and how it would address the existing business processes.

One particular business process -- Expense Reimbursements -- required an integration to Payables Management. How difficult could this be, right? I have done over 100 PM integrations in the past so this could not be any different. During the system testing, the end-user reported incomplete distributions throughout the integrations of hundreds of expense reports into GP and provided the following log file:

Integration Log
Integration: SC-ExpenseReimbursement (ID: 116)
Action: None
Start Time: 11/20/2009 12:09:14 PM

  11/20/2009 12:09:15 PM Source: IIntegrationEngine_Run, Status Code: 0    Opening source query...
  11/20/2009 12:09:16 PM Source: IIntegrationEngine_Run, Status Code: 0    Establishing source record count...
  11/20/2009 12:09:28 PM Source: IIntegrationEngine_Run, Status Code: 0    Beginning integration...
1:  2 Insert Succeeded 5.31 Seconds
2:  3 Insert Succeeded 1.53 Seconds
3:  4 Insert Succeeded 1.86 Seconds
4:  5 Insert Succeeded 2.25 Seconds
5:  6 Insert Succeeded 3.56 Seconds
6:  7 Insert Succeeded 1.47 Seconds
7:  8 Insert Succeeded 1.44 Seconds
8:  9 Insert Succeeded 1.47 Seconds
9:  10 Insert Succeeded 1.34 Seconds
10:  11 Insert Succeeded 1.91 Seconds
11:  12 Insert Succeeded 1.53 Seconds
12:  13 Insert Succeeded 1.64 Seconds
13:  14 Insert Succeeded 2.63 Seconds
14:  15 Insert Succeeded 2.94 Seconds
15:  16 Insert Warning 3.22 Seconds
    DOC 15 WARNING: Distributions for this transaction contain errors.
    DOC 15 WARNING: This transaction will not post; it includes distributions with errors.
16:  17 Insert Warning 0.95 Seconds
    DOC 16 WARNING: Distributions for this transaction contain errors.
    DOC 16 WARNING: This transaction will not post; it includes distributions with errors.
17:  18 Insert Warning 1.17 Seconds
    DOC 17 WARNING: Distributions for this transaction contain errors.
    DOC 17 WARNING: This transaction will not post; it includes distributions with errors.
18:  19 Insert Warning 1. Seconds
    DOC 18 WARNING: Distributions for this transaction contain errors.
    DOC 18 WARNING: This transaction will not post; it includes distributions with errors.
19:  20 Insert Warning 0.98 Seconds
    DOC 19 WARNING: Distributions for this transaction contain errors.
    DOC 19 WARNING: This transaction will not post; it includes distributions with errors.
20:  21 Insert Warning 1.83 Seconds
    DOC 20 WARNING: Distributions for this transaction contain errors.
    DOC 20 WARNING: This transaction will not post; it includes distributions with errors.
21:  22 Insert Warning 0.95 Seconds
    DOC 21 WARNING: Distributions for this transaction contain errors.
    DOC 21 WARNING: This transaction will not post; it includes distributions with errors.
22:  23 Insert Succeeded 2.66 Seconds
  11/20/2009 12:10:50 PM Source: IMProv.IntegrationContext.IIntegrationEngine_Run, Status Code: -2147221495     ERROR: Integration canceled during document integration.
  11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3    Integration Failed
  11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3    Integration Results
    307 documents were read from the source query.
    22 documents were attempted:
        15 integrated without warnings.
        7 integrated with warnings.
        0 failed to integrate.

Finish Time: 11/20/2009 12:10:50 PM

Source Total: 22
Successfully Integrated: 22
Integrated With Warning: 7
Failed: 0

At first glance this would seem typical of transactions that are actually missing distributions, but I checked against the source staging table in DB2 and all distribution records for each transaction reported as missing were actually present. The only distributions in the source were those for the expenses. In addition, this integration had been setup to default any missing distributions in the source recordset, which would automatically create the payables side.

I then checked each vendor (employee) record in GP for a missing payables account. After all, this would explain the case of the missing distributions. All vendor records were properly setup. Now comes the actual troubleshooting process after checking the obvious. My source queries were very simple too:

ExpenseHeader

SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'

ExpenseDetail

SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0

I proceeded to limit the source queries to just a specific employee and expense document number reported in the log as failing and re-ran the integration. Voila! The record went through to GP without any issues. Now the question was, why is IM kicking back valid records for the integration without any apparent reason?

I also checked to make sure the UseOptimizedFiltering switch was set to False and that we were running IM service pack 4 -- more on IM switches here. After a while of looking at the source records in the staging table, I realized that the first records that IM imported were in the same physical order in the table were data was being retrieved from. This was very simple as DB2 tables are VSAM tables, making them very different from SQL Server tables in the way the store data physically. However, for the records that failed, IM was attempting to locate a distribution record in the same order specified by the records being read from the header query.

To correct the problem, I decided to enforce the order of the records in both source queries by adding an ORDER BY clause to the queries, by VendorID and InvoiceNum. This way, as IM advanced through the header source query reading the records, it would consecutively find these in the distributions source query tables as specified by the query relationships (1..M). Now my source queries looked like this:

ExpenseHeader

SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'
ORDER BY VENDORID, INVOICENUM

ExpenseDetail

SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0
ORDER BY VENDORID, INVOICENUM

Upon executing the integration once more, all expense transactions were properly integrated without any missing distributions.

Now, I cannot say I experienced this before with SQL Server sources, but this happens to be a DB2 source and just maybe the ODBC engine works differently, but I can say that by reordering the source queries, I was able to resolve these missing distributions issues and improve performance two-fold.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/