on 2019 Jan 23 7:19 AM
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.
Request clarification before answering.
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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
hi..it seems old topic.
but for above query, it doesn't show batch and attribute itself.
any update about this query?
thank you
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.