Skip to Content

Query to check Open PO

Hi Experts

Have a query that displays all open purchase orders in the system has been open for longer than two weeks. It works but does not meet my criteria

The result also displays PO that has been goods received, but the PO is still open because the Goods Receipt PO hasn't been invoiced

What I am looking for is the query result only shows PO that hasn't been goods receipted

They are actually open and we haven't received the goods

I have tried the Open Items List and it also shows PO that has been goods receipted but hasn't been invoiced

The query I am using is below

SELECT 
T0.DocNum,
T0.CardCode, 
T0.CardName, 
T0.DocStatus, 
T0.DocDate, 
T0.DocTotal

FROM OPOR T0

WHERE 
T0.DocStatus = 'O' AND 
T0.CreateDate < (GETDATE()-14)

ORDER BY 
T0.CardName, 
T0.DocDate

Please help

Thanks

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 26, 2017 at 07:08 AM

    Hi Rahul,

    Your query is good. If it shows a PO that has been delivered, then that means that the PO was only partially delivered. In other words, some lines were not delivered or not the entire quantity of a line.

    If you want the query to show only POs that have no deliveries at all, then you should include the POR1 table, and check that any or all lines are closed (POR1.LineStatus).

    There is one cause for POs to remain open, even though they were delivered completely: when you use Freight costs, and an addon to draw the deliveries, if the addon does not draw the freight costs correctly, the PO will remain open.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded