Transactions posted through GL with batch date instead of document date

(0) Share

Report

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

All responses (4)

Answers (0)

BC 640 on at

Like (0)

Report

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 2,172 on at

Like (1)

Report

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 640 on at

Like (0)

Report

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 2,172 on at

Like (0)

Report

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