Troubleshooting SOP Invoices that are in both Work and History

Symptoms

1) Error "This document has been removed from history"

2) Sop Invoice will not allow you to void it

3) Invoice will not post; invoice batch keeps going to batch recovery

What should happen when the SOP invoice is posted

• The invoice should be removed from the SOP10100 (SOP Header Work) and placed in the SOP30200 (SOP Header History)
• The invoice lines should be removed from the SOP10200 (Sop Line Work) and placed in the SOP30300 (SOP Line History)
• If the invoice has lots or serial numbered components, it will update the POSTED column in the SOP10201
• All supporting tables will have the Transaction Source and/or Posted column field updated.
• If the invoice had inventoried lines that track quantities, the IV10201 (Inventory Purchase Receipts Detail) and the IV30300 (Inventory Transactions Amount History) among a few other will be updated in inventory. If the items are non-inventoried or a service item, the IV10201 will not be updated.
• The invoice will post to the RM module and update the RM00401 (RM Keys) and the RM20101 (RM Open)
• The invoice will then update the GL10000 (GL Work) or GL20000 (GL Open)
• In a nut shell, it will move your invoice lies to history, update inventory, update RM, and update the GL

How to troubleshoot SOP documents in both work and history

1. Use the All SOP script

2. In the AllSO.sql script, enter the SOP type of 3 and the SOP invoice number in the below section of the script

Select @SOPTYPE = 'X'

Select @SOPNUMBE = 'XXXX'

3. In SQL, select Query | Results To | Results to Text and execute the script

4. In the results, if the document is in both the SOP10100 and SOP30300, you have documents in both work and history

5. Check to see what other modules have records for the SOP invoice. Does it have records in Inventory, RM, and GL, along with the SOP records

Resolution

Note: Run all the SQL scripts in your test environment before running them in you live environment.

Determine which scenario fits your data.

Scenario 1

If IV is updated, the GL posted, the RM posted, in SOP work and history tables.

This means that everything was updated correctly in the History and in the other modules. However, the document is still in work.

1. Delete transactions out of the SOP10100 and SOP10200 work tables.

     Delete SOP10100 where DEX_ROW_ID = 'xxx'

              Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

        2.  If a batch id exists, you can delete it from Sales Batches or through SQL from the SY00500.

Scenario 2

If IV is updated, GL is not posted, RM posted, in SOP work and history tables

This means only the GL was not updated. Everything else was updated correctly in the History and in the other modules. However, the document is still in work.

1. Make manual entry in GL. NOTE: You will lose drill back capability to SOP transaction
2. Delete transactions out of the SOP10100 and SOP10200.

            Delete SOP10100 where DEX_ROW_ID = 'xxx'

            Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Scenario 3

If IV is updated, GL posted, RM is not posted, in SOP work and history tables

This means only the RM was not updated so you cannot apply a payment to the invoice. Everything else was updated correctly in the History and in the other modules. However, the document is still in work.

1. Make a manual entry to Receivables so it post to the GL. (check if GL was posted for RM so you will know to post to GL or not) NOTE: You will lose drill back capability to SOP transaction.
2. Delete transactions out of the SOP10100 and SOP10200.

            Delete SOP10100 where DEX_ROW_ID = 'xxx'

            Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Scenario 4

If IV is depleted, GL is not posted, RM not posted, in SOP work and history tables.

This means only the RM and GL were not updated so you cannot apply a payment to the invoice. Everything else was updated correctly in the History and in the other modules. However, the document is still in work.

1. Make manual entry in RM. (check if GL was posted for RM so you will know to post to GL or not)
2. Make a manual adjustment to the GL
3. Delete the document out of the SOP10100 and SOP10200

           Delete SOP10100 where DEX_ROW_ID = 'xxx'

           Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Scenario 5

If IV is not depleted, GL posted, RM posted, in SOP work and history tables.

1. Void RM transaction.
2. Delete GL batch or do reversing journal entry in GL if posted.
3. Remove transaction out of SOP history tables; SOP30200/SOP30300.
4. Repost transaction in SOP.

            Delete SOP30200 where DEX_ROW_ID = 'xxx'

            Delete SOP30300 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Note: If you repost the invoice, verify the Transaction Source and/or the Posted columns are cleared in all SOP tables.  Also verify the Batch Source says "Sales Entry" and not "*Sales Entry" in the SOP10100.  

OR

     1. Do IV adjustment (check if GL was posted so you will know to post to GL or not)

            NOTE: You will lose drill back capability to SOP transaction.

     2. Delete the document out of the SOP10100 and SOP10200

            Delete SOP10100 where DEX_ROW_ID = 'xxx'

            Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Scenario 6

If IV is not depleted, GL posted, RM is not posted, in SOP work and history tables.

1. Delete GL batch or do reversing journal entry in GL if posted.
2. Remove transaction out of SOP history tables; SOP30200/SOP30300.

            Delete SOP30200 where DEX_ROW_ID = 'xxx'

            Delete SOP30300 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice) 

      3. Follow the steps in Scenario 8 to verify the information in the SOP10100 and repost transaction in SOP.

OR

     1. Do IV adjustment (check if GL was posted so you will know to post to GL or not) NOTE: You will lose drill back capability to SOP transaction.

     2. Make manual entry in RM. (check if GL was posted for RM so you will know to post to GL or not) NOTE: You will lose drill back capability to SOP transaction.

     3. Remove transaction out of SOP work tables; SOP10100/SOP10200.

            Delete SOP10100 where DEX_ROW_ID = 'xxx'

            Delete SOP10200 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

Scenario 7

If IV is not depleted, GL is not posted, RM posted, in SOP work and history tables.

1. Void RM transaction and delete the GL batch created.
2. Remove transaction out of SOP history tables; SOP30200/SOP30300.
3. Follow the steps in Scenario 8 to verify the information in the SOP10100 and repost transaction in SOP.

OR

1. Make a manual adjustment to Inventory to remove the items from inventory.
2. Make a manual adjustment to the GL
3. Delete the SOP work table information.

Scenario 8

If IV is not depleted, GL is not posted, RM is not posted, in SOP work and history tables.

1. Remove transaction out of SOP history tables; SOP30200/SOP30300.

            Delete SOP30200 where DEX_ROW_ID = 'xxx'

            Delete SOP30300 where DEX_ROW_ID = 'xxx' (delete all rows associated with invoice)

2. Verify the information in the SOP10100 has correct information so that it can be reposted. Run the below script against the company database.        

            PSTGSTUS (this should be 0)

            BCHSOURC (this should read Sales Entry)

            VOIDSTTS (this should be 0)

            TRXSORCE (this should be blank)

          Below are scripts to correct these values. You will need to enter the dex_row_id for the line in the SOP10100. The dex_row_id is the last column in the SOP10100

            --Update SOP10100 set PSTGSTUS = 0 where DEX_ROW_ID = 'xxx’

            --Update SOP10100 Set BCHSOURC = 'Sales Entry' Where DEX_ROW_ID = 'xxx'

            --Update SOP10100 Set VOIDSTTS = 0 Where DEX_ROW_ID = 'xxx'

            --Update SOP10100 Set TRXSORCE = '' Where DEX_ROW_ID = 'xxx’

     3.  Verify the TRXSORCE and POSTED columns in the SOP10200, SOP10201, SOP10105, SOP10102, SOP10101 are blank for all lines.

           --Update SOPXXXXX Set TRXSORCE = '' Where DEX_ROW_ID = 'xxx’

           --Update SOPXXXXX Set POSTED = '' Where DEX_ROW_ID = 'xxx’

     4.  Next,  we need to check the SY00500 Batch Master Table.

          Run the below script.

            select BCHSOURC, MKDTOPST, BCHSTTUS, * from SY00500 where BACHNUMB = 'YYYYY’

            Below is the information that the tables should have:

                   The “Batch Source” (BCHSOURC) should be “Sales Entry”.

                   The “Posting Status” (PSTGSTUS) should be “0”.

                   The “Marked To Post” (MKDTOPST) should be “0”.

                   The “Batch Status” (BCHSTTUS) should be “0

            --update SY00500 set BCHSOURC = 'Sales Entry', MKDTOPST=0, BCHSTTUS=0 where DEX_ROW_ID = 'xxx’

5. Print the Edit list to verify there are no issues.
6. Attempt to report the invoice