web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Transactions posted th...
Microsoft Dynamics GP (Archived)

Transactions posted through GL with batch date instead of document date

(0) ShareShare
ReportReport
Posted on by BC 640

Hi - we have a large set of transactions from AP posted through to GL and we inadvertantly had the posting setting to batch date instaed of document date. is there a way to correct this from SQL?

Thanks

*This post is locked for comments

I have the same question (0)
  • Dencio Profile Picture
    Dencio 2,172 on at

    Hi BC,

    Yes, assuming your transaction/s are in the open Fiscal Year, you can run an update query on GL20000, updating GL20000.TRXDATE = PM00400.DOCDATE and join with PM00400 ON GL20000.ORMSTRID = PM00400.VENDORID and GL20000.ORTRXSRC = PM00400.TRXSORCE and GL20000.ORDOCNUM = PM00400.DOCNUMBR and GL20000.ORTRNUM = PM00400.CNTRLNUM, and WHERE clause on GL20000.ORTRXSRC for the specific Audit Trail Code you're deciding to update.

    Note:  Use PM00400 when you've completed the checklinks process on Purchasing Series to ensure this table lists all PM documents.

    Lastly, you need to run Reconcile to GL to update the periodic Account Summary balances.

    Hope this helps,

    Dencio

     

  • BC Profile Picture
    BC 640 on at

    Hi Dencio- thanks so much for your input. so I am not so great with SQl. I used the query editor to design the following according to your instructions. Looks very good as I have zeroed in on exactly thw data that needs updating. the only command i need to add is the the "Update GL20000.TRXDATE = PM00400.DOCDATE".

    I cant quite figuer out how and where to insert the update command in the statement below. your help in adding this final command is greatly apprecited.

    BC 

    SELECT GL20000.ORGNTSRC, PM00400.VENDORID, PM00400.DOCDATE, GL20000.TRXDATE, GL20000.JRNENTRY, GL20000.TRXSORCE, GL20000.ORCTRNUM,

    GL20000.ORMSTRNM, GL20000.ORDOCNUM, GL20000.ORTRXSRC

    FROM GL20000 INNER JOIN

    PM00400 ON GL20000.ORMSTRID = PM00400.VENDORID AND GL20000.ORTRXSRC = PM00400.TRXSORCE AND GL20000.ORDOCNUM = PM00400.DOCNUMBR AND

    GL20000.ORCTRNUM = PM00400.CNTRLNUM

    WHERE (GL20000.ORGNTSRC BETWEEN 'PMTRX00021065' AND 'PMTRX00021095')

    ORDER BY GL20000.ORGNTSRC

  • Dencio Profile Picture
    Dencio 2,172 on at

    Hi BC,

    Congratulations and you are doing good!

    Just replace your SELECT query in the first group with your UPDATE query and remove the ORDER BY clause and you should be OK.  In this case, it will look like the script below: 

    UPDATE GL20000 SET GL20000.TRXDATE = PM00400.DOCDATE

    FROM GL20000 INNER JOIN

    PM00400 ON GL20000.ORMSTRID = PM00400.VENDORID AND GL20000.ORTRXSRC = PM00400.TRXSORCE AND GL20000.ORDOCNUM = PM00400.DOCNUMBR AND

    GL20000.ORCTRNUM = PM00400.CNTRLNUM

    WHERE (GL20000.ORGNTSRC BETWEEN 'PMTRX00021065' AND 'PMTRX00021095')

    Hope this helps!

    Cheers,

    Dencio

     

  • BC Profile Picture
    BC 640 on at

    Hi Dencio- followed your instructions and it worked like a charm.

    Thanks for all your help.

    BC

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans