PO remaining balance quantity

(0) Share

Report

Posted on by Community Member

I am trying to create a purchasing report showing the remaining PO quantity. In other words, I have a PO for 10 items and receive 8. I want to see a column with the remaining quantity of 2. I've seen the Remaining PO Subtotal, but the quantity is more important to me than the dollars.

Also, I'd like to list each PO line in the report. If PO 123 has 3 lines, I want to see all of them listed (regardless of the status of the PO). Is there a field for that?

Thanks in advance!

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (2)

Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

Check this out Purchase Order Receiving and Invoices Details, You might need to add a column for the "remaining quantity"

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Mahmoud,

Thanks for the reply. Unfortunately, I don't have access to SQL to run this script. I was hoping for an actual field that I could use. I'll forward the script to the DBA and see if they can work their magic with it!

Thanks again.

Vance

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

Let us know if you need any further assistance,

Was this reply helpful? Yes No
Verified answer

babubaskaran@outloo... 12 on at

Like (0)

Report

Hi Vance,

Have you had a look at the GP Purchasing report.

Purchasing >>Reports >> Analysis >> New or Modify button

Select Purchase order Status which will give detailed level of each PO.

You can also build smartlist using the table mentioned by Mahmoud.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Navigation List builder in GP might be able to help you too in that exercise... The advantage like Smart List builder is that it remains within GP and is accessible to every GP user

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Babu,

Thanks for the reply. This should be sufficient for what we need.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Béat,

Thanks for the reply. We're still new to GP, so I'm not completely familiar with Navigation Builder. I'll work with the DBA to get better educated with this. Thanks again.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Try:

SELECT A.PONUMBER,

A.ORD,

A.QTYORDER,

A.QTYCMTBASE,

A.QTYCANCE,

QTYINVCD=ISNULL(B.QTYINVCD,0),

QTYREJ=ISNULL(B.QTYREJ,0),

QTYREPLACED=ISNULL(B.QTYREPLACED,0),

QTYSHPPD=ISNULL(B.QTYSHPPD,0),

REMAINING=A.QTYORDER-A.QTYCANCE-ISNULL(B.QTYSHPPD,0)

FROM POP10110 A --Purchase Order Line Work

LEFT OUTER JOIN (

SELECT A.PONUMBER, A.POLNENUM, QTYINVCD=SUM(A.QTYINVCD), QTYSHPPD=SUM(A.QTYSHPPD), QTYREJ=SUM(A.QTYREJ), QTYREPLACED=SUM(A.QTYREPLACED)

FROM POP10500 A

GROUP BY A.PONUMBER ,A.POLNENUM

) B --Purchase Order Receiving Line Work

ON A.PONUMBER=B.PONUMBER AND A.ORD = B.POLNENUM

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Here is better looking query:

SELECT [PO Number]=RTRIM(A.PONUMBER),

[PO Line Number]=A.LineNumber,

[Item Number]=RTRIM(A.ITEMNMBR),

[Site ID]=RTRIM(A.LOCNCODE),

[Unit of Measure]=RTRIM(A.UOFM),

[Qty Ordered]=A.QTYORDER,

[Qty Canceled]=A.QTYCANCE,

[Qty Invoiced]=ISNULL(B.QTYINVCD,0),

[Qty Rejected]=ISNULL(B.QTYREJ,0),

[Qty Replaced]=ISNULL(B.QTYREPLACED,0),

[Qty Shipped]=ISNULL(B.QTYSHPPD,0),

[Qty Remaining]=A.QTYORDER-A.QTYCANCE-ISNULL(B.QTYSHPPD,0)

FROM POP10110 A --Purchase Order Line Work

LEFT OUTER JOIN (

SELECT A.PONUMBER, A.POLNENUM, QTYINVCD=SUM(A.QTYINVCD), QTYSHPPD=SUM(A.QTYSHPPD), QTYREJ=SUM(A.QTYREJ), QTYREPLACED=SUM(A.QTYREPLACED)

FROM POP10500 A

GROUP BY A.PONUMBER ,A.POLNENUM

) B --Purchase Order Receiving Line Work

ON A.PONUMBER=B.PONUMBER AND A.ORD = B.POLNENUM

ORDER BY A.PONUMBER, A.LineNumber

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Shane,

Thanks for the information. Unfortunately, I don't have SQL access (nor speak SQL). I believe we now have a report that provides what we need. I will forward this to the DBA to see if this provides more than what we already have.

Thanks again.

Vance

Was this reply helpful? Yes No