Query showing Purchase Order, Delivery Receipt

[former_member601364]

Hi,

Just want to ask what would be the SQL code for the query showing the list of Purchase Order number and what is the corresponding delivery receipt number per vendor per item.

We are using SAP Business One 9.1

Thanks.

[zal_parchem2 · Accepted solution]

Hello Raymond - try the SQL below to get the following results:

A few points for us to consider:

I used LEFT OUTER JOIN to show those Purchase Orders which have not been received. If you want to show only those Purchase Orders having a Goods Receipt PO connected to them, then change the LEFT OUTER JOIN in the SQL to say INNER JOIN.

Please check out the following blogs on doing this type of SQL:

Recreate Relationship Map

LineNum or VisOrder

Also, not sure what additional information you needed, so please make whatever changes you want and post it here so others can be helped to have an SQL such as yours. Or let me know and we can update the one below:

SELECT DISTINCT 

T1.CardName AS 'Vendor Name', 
T1.CardCode AS 'Vendor Code', 
T1.NumAtCard AS 'Doc Reference Numb', 
T1.DocNum AS 'PO Numb', 
T1.DocDate, 
T0.VisOrder +1 AS 'Line Numb', 
T0.ItemCode, 
T3.DocNum AS 'GRPO'

FROM POR1 T0  

LEFT OUTER JOIN OPOR T1 
ON T0.DocEntry = T1.DocEntry

LEFT OUTER JOIN PDN1 T2 
ON T1.DocEntry = T2.BaseEntry 
AND T0.LineNum = T2.BaseLine

LEFT OUTER JOIN OPDN T3 
ON T2.DocEntry = T3.DocEntry 
AND T0.TrgetEntry = T3.DocEntry

WHERE 
T1.DocDate >= '[%0]' 
AND T1.DocDate <= '[%1]'
<br>

Many Thanks and Best Regards, Zal

[former_member601364 · Accepted solution]

Hi Zal,

Thanks for your response.

Will try this one.

Also, since on your sample it indicates GRPO, can i change it to Delivery Note/Delivery Receipt Number?

Thanks again.

[former_member601364 · Accepted solution]

Hi Zal,

Tried the code and there was an error.

It says: "An expression of non-boolean type specified in a context where a condition is expected, near 'FOR' ".

Please advise.

Thanks. 🙂

[zal_parchem2 · Accepted solution]

Raymond - it has that "<br>" at the end - no idea where that came from!!!

Delivery??? A Purchase Order is not associated with a Delivery, but a Goods Receipt PO. A Purchase Order is in the Purchasing AP Module and a Delivery is in the Sales AR Module.

Let's try again with this:

SELECT DISTINCT 

T1.CardName AS 'Vendor Name', 
T1.CardCode AS 'Vendor Code', 
T1.NumAtCard AS 'Doc Reference Numb', 
T1.DocNum AS 'PO Numb', 
T1.DocDate, 
T0.VisOrder +1 AS 'Line Numb', 
T0.ItemCode, 
T3.DocNum AS 'GRPO'

FROM POR1 T0  

LEFT OUTER JOIN OPOR T1 
ON T0.DocEntry = T1.DocEntry

LEFT OUTER JOIN PDN1 T2 
ON T1.DocEntry = T2.BaseEntry 
AND T0.LineNum = T2.BaseLine

LEFT OUTER JOIN OPDN T3 
ON T2.DocEntry = T3.DocEntry 
AND T0.TrgetEntry = T3.DocEntry

WHERE 
T1.DocDate >= '[%0]' 
AND T1.DocDate <= '[%1]'

Regards, Zal

[former_member601364 · Accepted solution]

Thanks Zal.

I thought there's a connection between the two separate modules.

How about this one (apologies for having too many questions):

How can I create a query combining the above and below table?

Thanks again. 🙂

Raymond

[zal_parchem2 · Accepted solution]

Hello Raymond - this is different than what you asked for. You are looking for information from OITL, ITL1, OBTQ, OBTN and others.

You are looking for the information on Batch Details. Remember, a Goods Receipt PO can go to several Batches, and an AR Delivery can be completed with several Batches. Plus a Batch can be affected by many more different types of transactions (Inventory Transfers, Returns, Goods Issues, etc.).

This can get complicated - I have some SQL covering Batches which are five pages long in a Microsoft Word document. This is NOT an easy thing to do in the forum because of all the variations which can happen, so...to see if the community here can help out...

What you need to do is to create a list of what you want to see from the two sections you have in your screen print. Put them in the order you want to see them. Maybe, just maybe I can get you something you can use...

Best Regards, Zal

[former_member601364 · Accepted solution]

Hi Zal,

Thanks for your response.

This is the actual table that I need. The headers in blue is the above table and the green is the below table in SAP.

Really appreciate the help as this will ease my reporting.

Raymond

[supjkt]

hi..it seems old topic.

but for above query, it doesn't show batch and attribute itself.

any update about this query?

thank you