You’re offline. This is a read only version of the page.
Had an instance where a SOP clerk created an invoice from an order and then posted it directly (i.e. not via a batch) She was able to print it but then could not go back into Sales Transaction Entry as GP claimed there was a transaction that had not completed. As it was a "posted invoice" I did not want to just delete the invoice form SOP10100 and SOP10200 and clear SY00500 and have her start again as I did not what had occurred and how far GP had gone in the posting process before it hung. After many days of deleting the stuck batch in SY00500 each morning I found a script that allowed me to search the entire database for my SOP Number. It also brought up some JE numbers and DEX_ID numbers that were identical (another reason to use Alpha Numeric for sop numbers). I satisfied myself that the process had not hit any historical or posting tables and also showed me what work tables the invoice had hit. Basically it only was in SOP10100 and SOP10200 plus tax and udf tables in the work tables.
I found that the BCHSOURC field only allows certain values. The proper value for that type of transaction is 'Sales Entry' however in my SOP10100.BCHSOURC record for the messed up invoice it was '*Sales Entry'. Where the star came from I have no idea. After checking my tax tables to make sure that the information was correct and all items were accounted for I did a sql update to remove the star on the SOP10100.BCHSOURC record for the SOPNUMBE record of the invoice. I was then able to open the invoice as an open and not posted transaction. I saved the invoice to a newly created batch and then posted the batch. The posting routine gave me a distribution record that was spot on. Of course I would also recommend doing this first on a sample or backup copy first and the usual rules about directly editing the database via sql apply. Hope this helps someone from spending way too many hours over what should be a relatively easy solution.
*This post is locked for comments
Thanks a lot for sharing this. It really came in handy on a similar support issue I was having.
Good morning! I have a perfect copy of your situation here.
I was hoping to get the update script that you ran to remove the * from the BCHSOURC? I can do a little "damage" in SQL, but I am not sure on this one. We usually don't have to touch our database, but alas, I cannot unstick this no matter what I try.
Thanks in advance!
Crystal
Thank you for the valuable information provided above,
Allow me to add up, the search all database stored procedure can be found on spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database.
I totally agree with you about the approach you have taken to tackle down this issue, such posting corruption occurs quite often for various reasons, and the search all db stored procedure takes you through the tables to let you know how records were stored and any potentials errors.
You have mentioned a very important point as well, which is using the alpha numeric for SOP numbers, if you run the stored procedure and provide an SOP number such as (INVS0000134) would be quite easier than providing (000000134). This is why the configuration of modules include setting up the document numbers and the associated prefix values.
Please never hesitate to share any further inquiries,
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,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (