Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Alan Sweet]

?

During a transfer from SO to INV we sometimes we have orphan invoices that have been generated due to a process that we’re in the middle of fixing.  The resulting invoice has no header information and it can’t be pulled up or deleted by GP, however, GP still allocates the inventory. Reconciling and Check Links do not resolve the problem. When we enter the invoice number the system says that ‘This document has been posted’.  The only tables that I seem to be able to find any information regarding said invoices is in SOP10200, SOP10201 where if I know the SOPNUMBE I can look it up. These have not been posted and cannot be reconciled and are sort of just hanging out, with us unable to void the transaction.

Instead of just adjusting the inventory to the correct level I would like to remove these from our database itself. My concern is what would might happen to other db items if I just delete the existing rows it won’t necessarily fix the allocated quantity. What might I need to do if I wanted to remove all information with regards to a specific SOPNUMBE and have the allocated inventory adjust itself?

——————————
Alan Sweet
Metropolitan Sales
Lynbrook NY
——————————

[Windi Epperson]

Alan,
Not sure if this would help, but you could try rebuilding the RM Keys table (RM00401):  https://community.dynamics.com/gp/f/32/t/57125
Make sure you have all users out and make a backup.  It can take a while to run if you have a lot of history.
Thanks
Windi

——————————
Windi Epperson
President/GP Senior Consultant
Advanced Integrators, Inc.
Norman OK
405-946-1774 Ext 102
——————————
——————————————-

[Andrew Kennedy]

?Alan,
If you’ve confirmed that nothing from these invoices posted and they only exist in those 2 tables you should be ok deleting them from the database.  Also check the SOP101** tables to confirm they don’t exist.  You can find queries online that will search the entire database for a specific value if you want to check further.  Just be patient.  Depending on the size of your DB these can take a while to run.
After deleting the line items, inventory reconcile should fix the allocation issue.
Make sure you do a backup before hand.  I also like to back up the table first just in case.  For example: 
select * into SOP10200_save from SOP10200

——————————
Andrew Kennedy
Principal Consultant
Socius
Brecksville OH
——————————
——————————————-

[Andrew Kennedy]

?
Alan,
I also just noticed that you mentioned the record exists in SOP10201 which, means it is a serialized item.  Check the POSTED flag for those records to confirm it still set to 0 to reflect not posted.
Reconcile should fix the serial number allocation as well.

——————————
Andrew Kennedy
Principal Consultant
Socius
Brecksville OH
——————————
——————————————-

[David Clayton]

Hi Alan,

I have seen this issue occur where the users depend on an unstable power supply. If the power goes while they are processing an order or invoice, orphaned records will occur.   A reliable UPS tends to resolve the issue.

I usually take the following approach.  Make sure to test it properly in a test company to ensure it is working for you.  

Backup your data and run checklinks on the sales series.   Some of these orphaned records may have data in the header table.  This will remove the headers.

Run the following script to identify the orphaned records.  These records will exist in the sop10200 but not in the sop10100

select * from sop10200 where sop10200.sopnumbe not in (select sopnumbe from sop10100)

Verify that these are the orphaned records.

select * from sop10200 where sopnumbe = ‘000030785’

Delete the orphaned records  from the sop10200.  I delete them one by one so there are no surprises.

delete sop10200 where sop10200.sopnumbe not in (select sopnumbe from sop10100) and sop10200.sopnumbe = ‘000030785’

Once the orphaned records have been deleted, the inventory reconcile process should remove the allocated quantities.

——————————
David Clayton
DMC Consultants Limited
Kingston, Jamaica
http://www.dmcjm.com
——————————
——————————————-

[David Clayton]

It would be a good idea to run checklinks on the sales series after you have run the scripts.

——————————
David Clayton
DMC Consultants Limited
Kingston, Jamaica
http://www.dmcjm.com
——————————
——————————————-