Wednesday, May 1, 2013

Duplicated GL Transactions between Work and Open tables

One of my customers got a case that Journals were not deleting from GL10000 upon posting, this caused the transactions to duplicate between open and work tables and been annoying end users.

To fix this miss, I wrote a script that looks into GL10000 and GL10001 and compares the totals between both tables for each journal and generate the result, you can use this script to company these and make sure that all journals were successfully posted and none posted partially.

Below the script code:

Historical Stock Sttus Summary
  1. SELECT UNPOSTED.JRNENTRY, UNPOSTED.AMOUNT, POSTED.JRNENTRY, POSTED.AMOUNT FROM
  2.     (SELECT JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
  3.     FROM GL20000 GROUP BY JRNENTRY) AS POSTED
  4. INNER JOIN
  5.     (SELECT GL10000.JRNENTRY, SUM(CRDTAMNT) AS AMOUNT
  6.     FROM GL10000 LEFT OUTER JOIN GL10001 ON GL10000.JRNENTRY = GL10001.JRNENTRY
  7.     GROUP BY GL10000.JRNENTRY) AS UNPOSTED
  8. ON POSTED.JRNENTRY = UNPOSTED.JRNENTRY
  9. WHERE UNPOSTED.AMOUNT IS NULL

Hope that this helps.


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

1 comment:

moataz said...

Good Morning,
Please You can Help me , I need to know how get the current company Id using VBA

Related Posts:

Related Posts with Thumbnails