Query to view all Open Sales Orders on Past Dates

[former_member1015475]

Can a query be made that would show all Open Orders and Rows based on a given/searchable date? For example, I want to see a list of all Open Sales Orders on Jan 1, 2020.

[davidalexandre]

Olá! Dustin, segue.

SELECT

A."DocEntry",

A."DocDate",

A."DocDueDate",

A."TaxDate",

(CASE

WHEN A."DocStatus" = 'O'

THEN 'Open'

ELSE 'Closed'

END) AS "Status",

A."CardCode",

A."CardName",

B."VisOrder",

B."ItemCode",

B."Price",

B."PriceBefDi",

B."Quantity",

B."WhsCode",

B."LineTotal"

FROM ORDR A

INNER JOIN RDR1 B ON A."DocEntry" = B."DocEntry"

WHERE A."DocStatus" = '[%0]'

AND A."DocDate" BETWEEN [%1] AND [%2]

[nikunjmehta2290]

Kindly refer Open Item list report provided by SAP and select the Sales order option.

[Maya_Shiff]

hi,

not sure which version you are using, but as of SAP Business One 10.0 you may generate easily the required list and many more reports using the Web Client. For further details check out this page:https://learnbusinessonewebclient.com/

regards,

Maya

[Johan_H]

How about this:

SELECT DISTINCT h.DocNum, h.CardCode, h.CardName, h.DocDate, h.DocDueDate, h.DocTotal
FROM ORDR h
     INNER JOIN ADOC hi ON h.DocEntry = hi.DocEntry AND hi.ObjType = 17
WHERE hi.DocStatus = 'O' AND hi.DocDate BETWEEN [%0] AND [%1]

Regards,

Johan

[former_member1015475]

Thanks Johan. This only shows orders entered in that date range, and not all open orders on the given date.

[former_member744551]

the part missing would be any that were created in the parameter date range but were closed after the end date parameter. based on this it would infer it was open in the date range.
I can find where to get the closed date. Thanks, jim

[Johan_H]

Hi dustin.barnhart and ndpace,

The query shows a history entry of each order that was open at least once during the given time frame.

The dates however are from the current document status, and due to the use of DISTINCT, you only see one entry per order.

Please try this to see what I mean:

SELECT h.DocNum, h.CardCode, h.CardName, hi.LogInstanc as [Version], h.DocDate, hi.DocDueDate AS [Due date then], h.DocDueDate AS [Due date now], hi.DocTotal AS [Total then], h.DocTotal AS [Total now]
FROM ORDR h
     INNER JOIN ADOC hi ON h.DocEntry = hi.DocEntry AND hi.ObjType = 17
WHERE hi.DocStatus = 'O' AND hi.DocDate BETWEEN [%0] AND [%1]
ORDER BY h.DocNum, [Version]

Regards,

Johan

[former_member744551]

Did you find a solution to this?

[former_member1015475]

Not yet

[former_member1015475]

This seems to meet my requirements:

SELECT T0.[DocNum], T0.[DocDate], T0.[UpdateDate] as [Closed Date], T0.[DocTotal] FROM ORDR T0 WHERE T0.[DocStatus] ='c' and T0.[DocDate] <=[%0] and T0.[UpdateDate] >[%0]

[edy_simon]

Your query have 2 issues:

1. your query shows only Sales Order with Document Status 'Closed' only. NOT 'OPEN" as your original question.
2. 'UpdateDate' is not 'ClosedDate'. You can close the Order on 1 Jan 2020, user then update the order Remark on 2 Jan 2020. Your query will show you 'ClosedDate' as 2 Jan 2020.

[kothandaraman_nagarajan]

Hi,

Try this query,

SELECT T1.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocStatus] = 'O' and T0.[DocDate] between [%0] and [%1]

Regards,

Nagarajan

[former_member1015475]

Thank you but this query returns a list of orders entered in that date range. I am trying to come up with a list of Open Sales Orders on a historical date. Is it possible to know the doc status of a specific order on a specific historical date?