Closed SO shown in Open Item List

[Former Member]

Hi experts,

Unfortunately, the below problem (mentioned in Note 948670) occurred in my client's database.

My question is --> In order to hide this kind of CLOSED sales orders in the Open Item List, can I just change RDR6.status from "O" to "C" ?

Note 948670 :

-

---

****Closed documents shown in Open Item List - XXX6 table****

Symptom

There are cases where the document is closed, but the sixth table of the document is still open.

As a result the document is shown in the Open Item List.

Other terms

Open document, open item list, 6 table, sixth table, SAP Business One

Reason and Prerequisites

--

Solution

In order to track these documents the following query should be run on the database:

USE *DEST*

-- change DEST to the name of customer's DB.

declare @doc varchar(3),@fld varchar(10)

-

---

Put document----

---

-- enter here the name of the table acording to the list below.

Set @doc = 'rdr'

-

---

Set @fld = CASE

WHEN @doc = 'pdn' THEN 'opencreqty'

ELSE 'openqty'

END

exec ('

SELECT T0.DocNum, T0.DocEntry, T0.DocStatus as ''DocStatus'', T1.Status AS ''Doc6Status''

FROM O' + @doc + ' T0 INNER JOIN ' + @doc + '6 T1 ON T0.DocEntry = T1.DocEntry

Where T0.DocStatus = ''C'' AND T1.Status = ''O''

AND NOT EXISTS

(SELECT 1 FROM ' + @doc + '1 T2 WHERE T0.DocEntry = T2.DocEntry AND

(T2.' + @fld + ' <> 0 OR T2.LineStatus <> ''C''))

')

You should run the query above on the following tables (documents):

Rdr - in order to track Sales Order that remained open,

Dln - in order to track Deliveries that remained open,

Rdn - in order to Returns that remained open,

Por - in order to track Purchase Order that remained open,

Pdn - in order to track Good Receipt PO that remained open,

Rpd - in order to track Good Returns that remained open.

If you get results after running the select query, please contact your Support Center

-

---

[former_member187989 · Accepted solution]

hi,

If u update status from open to close directly in db you will lose support,

Raise support ticket as per note in service market place.

Jeyakanthan