Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Transactions posted through GL with batch date instead of document date

Posted on by 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

  • BC Profile Picture
    BC 640 on at
    Re: Re: Re: Re: Transactions posted through GL with batch date instead of document date

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

    Thanks for all your help.

    BC

  • Dencio Profile Picture
    Dencio 2,172 on at
    Re: Re: Re: Transactions posted through GL with batch date instead of document date

    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
    Re: Re: Transactions posted through GL with batch date instead of document date

    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
    Re: Transactions posted through GL with batch date instead of document date

    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

     

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans