Open Forum

Linking APTran and GLTran

  • 1.  Linking APTran and GLTran

    TOP CONTRIBUTOR
    Posted Jun 18, 2019 01:04 PM
    We are working on an integration that uses GLTran as the source data table, but it also requires some information that only originates in APTran if the module is Jrnltype = AP (e.g. InvtID, PONbr, etc)

    Back in the olden days, you could link an APTran record to the corresponding GLTran record using APTran fields Cpnyid/Batnbr/TranType/Refnbr/LineID and link those to the GLTran fields Cpnyid/Batnbr/TranType/Refnbr/LineID resulting in a one-to-one match.  I actually remember this feature working and using it in very early days to confirm that all AP transactions arrived in  the GLTran table.   This was a really nice audit feature.

    However, since at least 2002, the GLTran.LineID field is not populated when AP batches are released.    Without having that  GLTran.LineID field populated, I can't find a way to directly link the two tables.    Some may wonder why I don't use APTran.Linenbr = GLTran.LineNbr.    These are two entirely different sequence numbers and do not match at all.

    So I need to find a way to update the GLTran table to populate the GLTran.LineID field correctly for new transactions - i.e. from the APTran.LineID.    My first question to the community is whether anyone else has tried this and already has a solution?

    My plan to fix the issue going forward by adding a trigger on APTran to populate APTran.S4Future10 = APTran.LineID at original creation of the AP Line.    I have tested whether S4Future10 copies into GLTran at batch release, and it does.     Next I would need to add a trigger on GLTran to copy GLTran.S4Future10 to GLTran.LineID.

    So my next question is whether anyone has already written a similar trigger and would be willing to share the syntax for that SQL statement?

    Finally, has anyone ever written anything to correct historical records to  populate this field?     I'd be interested in any findings on that!

    Thanks in advance for any comments or insight.

    Best regards,

    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------


  • 2.  RE: Linking APTran and GLTran

    BRONZE CONTRIBUTOR
    Posted Jun 19, 2019 03:53 PM
    Hi Gail,

    We also have had this need, but I have not spent the time to remedy.  However, spending 30 minutes thinking about it this morning, this is what I would do.   There is a unique integer value in APTran called RecordID.  If we can get this RecordID value to populate the GLTran table, we would have this one-to-one transaction relationship you are looking for.  Instead of triggers, I would actually go to the source and update the AP Release Stored Procedure (pp_03400).  There is a "working" GLTran table (Wrk_gltran) that has the APTran.RecordID stored in it, but it is not used when it inserts into the GLTran table.  I would go ahead and change the INSERT GLTran statement (there are two starting around line 1775).  In the select half, I would replace either the gltran.PC_ID field (not used) or the gltran.s4future10 field (integer field matching aptran.recordid) with gl.recordid.  Then you would be able to query between the two using gltran.s4future10 = aptran.recordid.

    Of course document your changes and save an original version.  This will also probably need to be re-applied after upgrades.

    Hope this helps.  Shoot me a message if you have any questions.

    Aloha, ​

    ------------------------------
    Scott Johnson
    Sr. Business Application Developer
    W. M. Keck Observatory
    Kamuela HI
    ------------------------------



  • 3.  RE: Linking APTran and GLTran

    SILVER CONTRIBUTOR
    Posted Jun 20, 2019 08:23 AM
    Scott - I like your approach to this - the RecordID field on APTran is part of the clustered index and another index (APTran99) as the only column.

    GLTran has a ton of indexes (half of the indexes in our instance see no usage and I keep meaning to disable them) - but depending on query performance you might want to add an index on S4Future10 on GLTran.

    ------------------------------
    Drew Skwiers-Koballa
    Director of IT
    Inside Edge CIS
    Eagan MN
    ------------------------------