Weekly Dynamic: GL/MDA SQL

This week’s Weekly Dynamic comes from Jeremy Lowell of DataRealized.com.
This SQL code is designed to join Multi-Dimensional Analysis (MDA) tables back to the GL. It’s a little complicated but it seems to work fine.
select distinct dta10100.GROUPID,
dta10200.CODEID,dta10200.POSTDESC,
gl20000.* from GL20000
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX AND DTA10100.SEQNUMBR = GL20000.SEQNUMBR AND GL20000.ORCTRNUM = DTA10100.DOCNUMBR LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF and GL20000.SEQNUMBR = GL20000.OrigSeqNum
UNION
select dta10100.GROUPID,dta10200.CODEID,dta10200.POSTDESC,
gl20000.* from GL20000 LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX AND DTA10100.SEQNUMBR = GL20000.OrigSeqNum AND GL20000.ORCTRNUM = DTA10100.DOCNUMBR LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF and GL20000.SEQNUMBR <> GL20000.OrigSeqNum

Jeremy has an update to this that I’ve promoted from the comments:

In looking at the data in the GL20000 tables and the DTA10100 table, the SDK calls for joining the following columns in order to obtain the MDA data for the GL transaction: JRNENTRY, ACTINDX, SEQNUMBR.

In my experience, I had a terribly Cartesian product when I made that join. While I haven’t yet tracked down why that’s the case, I ended up doing the following:

I took all of the data out of each of the GP companies ~ 50 and persist them into tables in my staging database. For MDA data, I have one table for the DTA10100 and DTA10200 data and another table for the GL20000 and 30000 data.

Once I have that data, I update a column that I created (dexrowid_GL) via 8 different steps… Basically creating a “validity” of each join. That logic is below.

-8 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber, DocNumber and amount(dollars).

-7 — Must match on AccountNumber, JournalEntry, TRXDate, DocNumber and amount(dollars).

-6 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber and DocNumber

-5 — Must match on AccountNumber, JournalEntry, TRXDate and DocNumber

-4 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumbrer, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber.

-3 — Must match on AccountNumber, JournalEntry, TRXDate, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber

-2 — Must match on AccountNumber, JournalEntry, TRXDate and amount(dollars).

42 — There is not enough data to join the MDA transaction to the GL transaction.

Thus far, I have less than one percent of records that will not join with the logic mentioned above. I’m still working on ironing out why I have to jump through all of these hoops and I’m quite certain that it’s a 3rd party application but I have yet to prove that out.

Good luck in accurately joining your MDA transactions to your GL transactions…. what should be fairly straight forward per the SDK has proven to be anything but for the dataset that I’m working against

6 Replies to “Weekly Dynamic: GL/MDA SQL”

  1. Jeremy,

    Below is the email you sent as part of a back and forth we had. If somehow I misunderstood “feel free to use the query on your blog” let me know, I’ll be happy to take it down. When folks send me things I don’t post them without attribution.

    Feel free to email or call me about this. Both are the front page of the blog on the right.

    Mark

    From: Jeremy Lowell [mailto:Removed to prevent span]
    Sent: Tuesday, December 14, 2010 10:27 AM
    To: ‘Mark Polino’
    Subject: RE: MDA’s & GL question

    Mark,

    Thank you for your reply.

    Here’s what I’ve come up with thus far… Curious to see if you think I’ve nailed it, or not.

    If it’s right, feel free to use the query on your blog… there’s quite a bit of mis-information out there on this specific scenario.

    My biggest question is around the docnumber join. That doesn’t make sense but I’ve added it to get what I believe the correct data for all of the scenarios I’ve found.

  2. Since that’s off my chest, I believe it valuable for your readers to understand why this join did not work for my client.

    In looking at the data in the GL20000 tables and the DTA10100 table, the SDK calls for joining the following columns in order to obtain the MDA data for the GL transaction: JRNENTRY, ACTINDX, SEQNUMBR.

    In my experience, I had a terribly Cartesian product when I made that join. While I haven’t yet tracked down why that’s the case, I ended up doing the following:

    I took all of the data out of each of the GP companies ~ 50 and persist them into tables in my staging database. For MDA data, I have one table for the DTA10100 and DTA10200 data and another table for the GL20000 and 30000 data.

    Once I have that data, I update a column that I created (dexrowid_GL) via 8 different steps… Basically creating a “validity” of each join. That logic is below.

    -8 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber, DocNumber and amount(dollars).

    -7 — Must match on AccountNumber, JournalEntry, TRXDate, DocNumber and amount(dollars).

    -6 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber and DocNumber

    -5 — Must match on AccountNumber, JournalEntry, TRXDate and DocNumber

    -4 — Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumbrer, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber.

    -3 — Must match on AccountNumber, JournalEntry, TRXDate, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber

    -2 — Must match on AccountNumber, JournalEntry, TRXDate and amount(dollars).

    42 — There is not enough data to join the MDA transaction to the GL transaction.

    Thus far, I have less than one percent of records that will not join with the logic mentioned above. I’m still working on ironing out why I have to jump through all of these hoops and I’m quite certain that it’s a 3rd party application but I have yet to prove that out.

    Good luck in accurately joining your MDA transactions to your GL transactions…. what should be fairly straight forward per the SDK has proven to be anything but for the dataset that I’m working against.

  3. I know its old, but for the next person who comes along:

    I used the following:

    SELECT
    * — Chose the fields you require
    FROM GL20000 AS GL WITH (NOLOCK) ON L.ACTINDX = GL.ACTINDX
    LEFT OUTER JOIN DTA10100 WITH (NOLOCK) ON DTA10100.JRNENTRY = GL.JRNENTRY
    AND DTA10100.ACTINDX = GL.ACTINDX
    AND DTA10100.SEQNUMBR = GL.OrigSeqNum
    AND GL.ORCTRNUM = DTA10100.DOCNUMBR
    LEFT OUTER JOIN DTA10200 WITH (NOLOCK) ON
    DTA10100.DTAREF = DTA10200.DTAREF
    AND DTA10100.SEQNUMBR = DTA10200.SEQNUMBR

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.