How to delete all GL unposted post through transactions from sales ledger

(0) Share

Report

Posted on by Community Member

We have been using only the GP Sales Ledger for the last 5 years and are now about to move to using the GL. Unfortunately post to GL was on, but post through turned off from the sales ledger.

We now want to do a mass delete of all of the unposted transactions that have been posted from the sales ledger and are sitting waiting to be posted in the GL.

We have no posted transactions at all in the GL currently and just want to make sure we can cleanly remove the unposted transactions so we can start from fresh with the GL.

I have read that the affected tables are

GL10000 Transaction Work

GL10001 Transaction Amounts Work

SY00500 Posting Definitions Master (Batch headers)

We would be really appreciate some confirmation on the SQL we need to do this if we can't do it through the GP 2013 interface and whether we should be concerned that it would affect anything else. Just to confirm we haven't used the GL at all and have significant Sales Ledger and Collections Management data that we need to keep.

thank you in advance.

*This post is locked for comments

All responses (5)

Answers (0)

Frank Hamelly | MVP... 46,527 Super User 2025 Season 1 on at

Like (0)

Report

RE: How to delete all GL unposted post through transactions from sales ledger

Leslie = Genius! I love it . . .
Suggested answer

L Vail 65,271 on at

Like (0)

Report

RE: How to delete all GL unposted post through transactions from sales ledger

Hi,

Another way you could do it using just a little SQL is to change the Batch ID on every transaction so that it is the same. Doing that will essentially move all of the transactions into a single batch. Then, you could delete that GL batch and it would clean up everything using GP's native code.

After that, you could use SQL to delete all of the abandoned batch IDs from the SY00500 table.

Kind regards,

Leslie
Frank Hamelly | MVP... 46,527 Super User 2025 Season 1 on at

Like (0)

Report

RE: How to delete all GL unposted post through transactions from sales ledger

Dynamics, your proposed solution should work. After you delete the batches via SQL, run Check Links on the Financial transaction tables. Make sure you back up your database prior to making any changes in SQL, just in case you need to revert back.
Community Member on at

Like (0)

Report

RE: How to delete all GL unposted post through transactions from sales ledger

Mike thank you for your feedback. The main issue for us is how to delete these thousands of waiting batches, we could only see how to delete one by one. In the old system (20+ years old) our Financial Controller creates manual paper journals to enter into the GL from Purchases and Sales Ledgers and wants to continue this way at least initially with GP (so the beginning balances is the way forward), hence the requirement to find out how to delete the GL batches from the sales ledger en-masse. I couldn't see how through the interface hence my reference to direct deletion via SQL. Any more input you can provide would be greatly appreciated.
Mike Smith 6,840 on at

Like (0)

Report

RE: How to delete all GL unposted post through transactions from sales ledger

With the "Post Through" turned off, the posted Sales Order Processing transactions should be in a batch(es) accessed from Financial > Transactions > Batches. Deleting these will remove the general ledger side of the transactions that have been posted from the subsidiary modules. As a side note, you will need to enter in General Ledger beginning balances though, and if everything was setup correctly in the subsidiary modules, this would more or less just be recreating all of the entries that you want to delete. I would create/update a test company with this data and in the test company, post the General Ledger batches to see if everything falls into place. From this, you could determine if this is a feasible option, or if deleting the batch(es) and entering beginning balances in General Ledger is the best way to move forward.