how to link the purchase order tables to GRN tables

[vivekmangalwedhekar]

I want to raise a query so that I can find out the various GRN's made against a line item from the purchase order. How can I link the OPOR, POR1, OPDN AND PDN1 TABLES. If anyone has a query already made, it would be helpful.

[Former Member]

Hi Vivek

It depends on the direction you need the information in. If you want to start from the Purchase Order and see the Goods Receipt PO then use the target fields on POR1, but if you want to see the Goods Receipt PO first and his Purchase Order, then use BaseRef, BaseType & BaseLine from PDN1 and link to DocEntry, LineNum of Purchase Order.

Try the following query:

SELECT T0. AS {GRPO Doc No}, T0. + 1 AS {GRPO Line Num}, T1. AS {PO Doc No}, T1. + 1 AS {PO Line Num}, T0., T0., T0. AS {GRPO Qty}, T1. AS {PO Open Qty} FROM PDN1 T0 , POR1 T1 WHERE T0. = T1. and T0. = T1. and T0. = 22

Replace the squiggle brackets with square brackets. I have added a 1 to each line number as SAP Business One always begins with 0, and it is easier to read if the first line is 1.

Kind regards

Peter Juby

[vivekmangalwedhekar]

Thanks, I shall try it out

[Former Member]

You can try one example to start:

Select t0.docentry 'GRPO #', t0.linenum, t3.DocNum 'PO #', t0.itemcode, t0.Quantity, t0.shipdate from pdn1 t0

inner join opdn t1 on t1.docentry = t0.docentry

left join por1 t2 on t2.docentry = t0.baseref

left join opor t3 on t3.docentry = t2.docentry

where t1.cardcode = '[%0\]'

Thanks,

Gordon

[vivekmangalwedhekar]

Thanks, I shall try it out