Skip to Content
0

Query to check Open PO

Oct 26, 2017 at 06:51 AM

79

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Oct 26, 2017 at 07:08 AM
0

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

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Johan

Thanks for enlighting me. I managed to modify the query to get the result i needed

SELECT T0.DocNum,
T0.CardCode, 
T0.CardName, 
T0.DocStatus, 
T0.DocDate, 
T0.DocTotal FROM OPOR T0  INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.DocStatus = 'O' AND 
 T0.[DocDueDate]   < (GETDATE()-14) AND
 T1.[OpenQty] > '0' ORDER BY T0.[DocDate]

Thanks

1

Hi Rahul,

Glad you got it to work, however this new query will give you the same result as the original one, except that it will filter out the Freight cost scenario I described earlier.

If you have POs that are open even though all lines' open quantities are zero, it seems that you are using a faulty addon. In that case, I would recommend that you correct the addon to prevent future problems.

Regards,

Johan

0

Hi Johan

I missed the freight bit, yes your right. My query is faulty because it doesn't check for PO's open due to the freight

How do I include the freight checking into the query?

Regards

Rahul

0

Hi Rahul,

The main question is, do you use an addon to draw Goods Receipts from POs?

When you manually draw a Goods Receipt from a PO, and the PO is received completely, the PO will automatically be closed. When you use an addon for this, that addon needs to handle freight in the correct way. If it doesn't the PO will remain open, even though it shouldn't.

Please try the following version of your query, and note that the Total Open Quantity field should be larger than zero. Those are the cases that you are looking for. Where this value is zero, the PO should be closed:

SELECT T0.DocNum,
       T0.CardCode, 
       T0.CardName, 
       T0.DocStatus, 
       T0.DocDate, 
       T0.DocTotal,
       CAST(SUM(T1.[OpenQty]) AS INT) AS [Total Open Quantity]
FROM OPOR T0
    INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.DocStatus = 'O'
  AND T0.[DocDueDate]   < (GETDATE()-14)
GROUP BY T0.DocNum,
         T0.CardCode, 
         T0.CardName, 
         T0.DocStatus, 
         T0.DocDate, 
         T0.DocTotal
ORDER BY T0.[DocDate]

Regards,

Johan

0

Thanks Johan

1