automatically assign GL-Master Account strings the values for aaTrxDimID, aaTrxDim, and aaTrxDimCode

  • automatically assign GL-Master Account strings the values for aaTrxDimID, aaTrxDim, and aaTrxDimCode

    Posted by DSC Communities on December 20, 2016 at 1:55 pm
    • Rod Carlson

      Member

      December 20, 2016 at 1:55 PM

      Hello,

      I would like to automatically assign GL-Master Account strings the values for aaTrxDimID, aaTrxDim, and aaTrxDimCode and was very close but have gotten to a bottle neck. 

      I found the following query on this web site. However, it returns far more rows than what is shown on the “Account Access To Transaction Dimension Codes” screen. 

      Has anyone had experience with this kind of project, and more specifically, what should be eliminated from this query to match the screen?

      The table-finder tool from eOne Solutions is quite helpful showing the tables behind the screen but not the query behind the screen.

      Any ideas?

      http://mahmoudsaadi.blogspot.com/2014/03/analytical-accounting-gl-accounts-and.html

      SELECT aaAcctClassID ,
      aaAccountClass ,
      aaTrxDimID ,
      ACTINDX ,
      ACTNUMST ,
      Master_TB ,
      aaTrxDim ,
      aaTrxDimCode
      FROM ( SELECT A.aaAcctClassID ,
      A.aaAccountClass ,
      B.aaTrxDimID ,
      C.ACTINDX ,
      D.ACTNUMST ,
      CASE F.TPCLBLNC
      WHEN 0 THEN ‘Debit’
      WHEN 1 THEN ‘Credit’
      ELSE ‘ ‘
      END AS Master_TB ,
      E.aaTrxDim ,
      E.aaTrxDimCode
      FROM AAG00201 AS A
      INNER JOIN AAG00202 AS B ON A.aaAcctClassID = B.aaAcctClassID
      LEFT OUTER JOIN AAG00200L AS C ON B.aaAcctClassID = C.aaAcctClassID
      LEFT OUTER JOIN GL00105 AS D ON C.ACTINDX = D.ACTINDX
      LEFT OUTER JOIN GL00100 AS F ON D.ACTINDX = F.ACTINDX
      LEFT OUTER JOIN ( SELECT A.aaTrxDimID ,
      A.aaTrxDim ,
      B.aaTrxDimCode
      FROM AAG00400 AS A
      INNER JOIN AAG00401 AS B ON A.aaTrxDimID = B.aaTrxDimID
      ) AS E ON B.aaTrxDimID = E.aaTrxDimID
      ) AS AA

      ——————————
      Rod Carlson
      Information Technology – Software Developer
      YMCA of Metro Chicago
      Chicago IL
      ——————————

    • Rod Carlson

      Member

      December 20, 2016 at 4:01 PM

      One step closer…. Now, I need to research the ramifications and best practices to insert into AAG02001

      On my own, I think I answered the simpler question of joining the tables together as follows:

      SELECT distinct AAG00400.aaTrxDimID
      ,AAG00400.aaTrxDim
      ,AAG00401.aaTrxDimCode
      ,AAG00202.aaAcctClassID
      ,AAG02001.ACTINDX
      ,GL00105.ACTNUMST
      — , AAG00201.*
      FROM AAG00400
      INNER JOIN AAG00401 ON AAG00400.aaTrxDimID = AAG00401.aaTrxDimID
      left outer join AAG00202 on AAG00401.aaTrxDimID = AAG00202.aaTrxDimID
      left outer join AAG02001 on AAG02001.aaTrxDimCodeID = AAG00401.aaTrxDimCodeID and AAG02001.aaTrxDimID = AAG00401.aaTrxDimID
      –LEFT OUTER JOIN AAG00200 ON AAG00200.aaAcctClassID = AAG00200.aaAcctClassID
      –INNER JOIN GL00100 ON AAG00200.ACTINDX = GL00100.ACTINDX
      LEFT OUTER JOIN GL00105 ON AAG02001.ACTINDX = GL00105.ACTINDX
      order by GL00105.ACTNUMST

      ——————————
      Rod Carlson
      Information Technology – Software Developer
      YMCA of Metro Chicago
      Chicago IL
      ————————————————————————-

    DSC Communities replied 7 years, 4 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘automatically assign GL-Master Account strings the values for aaTrxDimID, aaTrxDim, and aaTrxDimCode’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!