GP2018 Summary Posting to GL

Tagged: 

  • Dynamics GP

    GP2018 Summary Posting to GL

    Posted by Thomas Hill on February 27, 2025 at 11:40 am

    We upgraded to GP2018 to start the new year. During the month of January, a posting setting was switched from Detail to Summary. This affected the batches posted from Payables and Receiving.

    I know manually I can drill down on the Journal Entry to see the detail. Did that. But since February was done this way for the whole month, I need to see if I can find the table that has the details.

    I use FileMaker Pro, to access GP tables (read-only). So I am comfortable doing this. I do not know if it is a PMXXXXX table or GLXXXXX table. Any help would be appreciated.

    Jump to the accepted answer
    Thomas Hill replied 1 month, 1 week ago 4 Members · 8 Replies
  • 8 Replies

  • Elaine Salyer

    Member
    February 27, 2025 at 12:00 pm
    Up
    0
    Down
    ::

    Did you try Table Finder in GP?

    Microsoft Dynamics GP – Tools – Table Finder

    • Thomas Hill

      Member
      February 27, 2025 at 1:32 pm
      Up
      0
      Down
      ::

      No. For all of the tables in GP2018, I am not sure if I am looking FINANCIAL or PURCHASING.

      I have used the table listing from Victoria Yodin’s website. That has helped me tremendously in the past.

  • David Musgrave MVP, GPUG All Star, Community Legend

    Member
    February 27, 2025 at 11:23 pm
    Up
    0
    Down
    ::

    Hi Thomas

    First of all, posting in summary is a legacy feature which goes back to the days when hard disk space was expensive. I would never recommend that nowadays as it makes it very difficult to reconcile GL to subsidiary modules such as RM/SOP and PM/POP.

    History data for the PM module should be found in the PM_Paid_Transaction_HIST (PM30200) table and PM_Distribution_HIST (PM30600) table.

    History data for the POP module should be found in the POP_ReceiptHist (POP30300) table and the POP_ReceiptLineHist (POP30310) table along with the POP_DistributionHist (POP30390).

    Finally, The Table Finder is a tool in SmartList Builder and not available to all installs. In the same way GP Power Tools Resource Information and Resource Finder windows are only available from the Tools menu if the product is installed.

    Hope this helps

    David

  • Thomas Hill

    Member
    February 28, 2025 at 10:33 am
    Up
    0
    Down
    ::

    When I joined this company they were on GP2010, then upgraded to GP2015. Everything has been detail posted during the time. We upgraded to GP2018 to start January. Reviewing the data, the detail was posted until mid-month. Then a change put it at summary. January finished in summary and all of February has been summary posted. There was a change from GP2015 to GP2018 on the posting parameters. Automatically posting payables and receivables to the general ledger did not work as the settings from GP2015 were.

  • Thomas Hill

    Member
    February 28, 2025 at 11:19 am
    Up
    0
    Down
    ::

    @WinthropDC Thanks for those table references. You do not happen to know if there is a GL_table with fields for the JRNENTRY and the BATCHNUM from the module? GL20000 does not have it as such.

    • Amber Bell

      Member
      March 19, 2025 at 3:15 pm
      Up
      0
      Down
      ::

      Hello Thomas, I worked with Microsoft Copilot to create a SQL View to help you! I included all 10 Account segment columns. You can edit that out: 

      SELECT 
    PM10100.TRXSORCE AS [Transaction Source],
    PM10100.VCHRNMBR AS [Voucher Number],
    PM10100.VENDORID AS [Vendor ID],
    PM10100.DEBITAMT AS [Debit Amount],
    PM10100.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number]
FROM PM10100
JOIN GL00100 ON PM10100.DSTINDX = GL00100.ACTINDX

UNION ALL

SELECT 
    PM30600.TRXSORCE AS [Transaction Source],
    PM30600.VCHRNMBR AS [Voucher Number],
    PM30600.VENDORID AS [Vendor ID],
    PM30600.DEBITAMT AS [Debit Amount],
    PM30600.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number]
FROM PM30600
JOIN GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX;

      • Amber Bell

        Member
        March 19, 2025 at 3:32 pm
        Up
        0
        Down Accepted answer
        ::

        I thought you might want the Journal Entry number and date. I added that table. I limited it to just pull the first journal entry number and then create a column to let you know how many journal entries are part of that Transaction Source. Also…I made a query…not a view. If you need it as a view, let me know.

        WITH GLData AS (
    SELECT 
        ORGNTSRC,
        JRNENTRY,
        TRXDATE,
        ROW_NUMBER() OVER (PARTITION BY ORGNTSRC ORDER BY TRXDATE) AS rn
    FROM GL20000
)

SELECT 
    PM10100.TRXSORCE AS [Transaction Source],
    PM10100.VCHRNMBR AS [Voucher Number],
    PM10100.VENDORID AS [Vendor ID],
    PM10100.DEBITAMT AS [Debit Amount],
    PM10100.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number],
    GLData.JRNENTRY AS [Journal Entry],
    GLData.TRXDATE AS [GL Trx Date],
    (SELECT COUNT(DISTINCT JRNENTRY) FROM GL20000 WHERE ORGNTSRC = PM10100.TRXSORCE) AS [Unique Journal Entry Count]
FROM PM10100
JOIN GL00100 ON PM10100.DSTINDX = GL00100.ACTINDX
LEFT JOIN GLData ON PM10100.TRXSORCE = GLData.ORGNTSRC AND GLData.rn = 1

UNION ALL

SELECT 
    PM30600.TRXSORCE AS [Transaction Source],
    PM30600.VCHRNMBR AS [Voucher Number],
    PM30600.VENDORID AS [Vendor ID],
    PM30600.DEBITAMT AS [Debit Amount],
    PM30600.CRDTAMNT AS [Credit Amount],
    GL00100.ACTDESCR AS [GL Account Description],
    RTRIM(
        ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') +
        ISNULL(RTRIM(GL00100.ACTNUMBR_10), '')
    ) AS [Account Number],
    GLData.JRNENTRY AS [Journal Entry],
    GLData.TRXDATE AS [GL Trx Date],
    (SELECT COUNT(DISTINCT JRNENTRY) FROM GL20000 WHERE ORGNTSRC = PM30600.TRXSORCE) AS [Unique Journal Entry Count]
FROM PM30600
JOIN GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX
LEFT JOIN GLData ON PM30600.TRXSORCE = GLData.ORGNTSRC AND GLData.rn = 1;

        • Thomas Hill

          Member
          March 20, 2025 at 2:08 pm
          Up
          0
          Down
          ::

          Thank you for the WORK you did. However, I got the data I needed for Jan and Feb. March started with summary posting turned off.

Log in to reply.

Start of Discussion
0 of 0 replies June 2018
Now

Report

Harassment or bullying behavior
Contains mature or sensitive content
Contains misleading or false information
Contains abusive or derogatory content
Contains spam, fake content or potential malware

Block Member?

Please confirm you want to block this member.

You will no longer be able to:

  • See blocked member's posts
  • Mention this member in posts
  • Invite this member to groups
  • Message this member
  • Add this member as a connection

Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.

Report

You have already reported this .

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!