Skip to Content
author's profile photo Former Member
Former Member

Closed SO shown in Open Item List

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

-


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 21, 2009 at 10:30 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.