You’re offline. This is a read only version of the page.
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
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 ANDGL20000
.ORCTRNUM = PM00400.CNTRLNUMWHERE
(GL20000.ORGNTSRC BETWEEN 'PMTRX00021065' AND 'PMTRX00021095')Hope this helps!
Cheers,
Dencio
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.ORTRXSRCFROM
GL20000 INNER JOINPM00400 ON GL20000.ORMSTRID = PM00400.VENDORID AND GL20000.ORTRXSRC = PM00400.TRXSORCE AND GL20000.ORDOCNUM = PM00400.DOCNUMBR ANDGL20000
.ORCTRNUM = PM00400.CNTRLNUMWHERE
(GL20000.ORGNTSRC BETWEEN 'PMTRX00021065' AND 'PMTRX00021095')ORDER
BY GL20000.ORGNTSRCHi 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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,219
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
