How to join GRPO with AP invoice thru query ?

[former_member187989]

hi all,

How to join GRPO with AP invoice in sql query ?

Jeyakanthan

[former_member583013]

If the AP Invoice is copied from the GRPO then there would be a reference and you could link them in a query. If the AP Invoice was created independent of the GRPO then the links between the two document do not exist.

[dbo\].[PCH1\] T0 INNER JOIN [dbo\].[OPDN\] T1 ON T1.DocEntry = T0.BaseEntry

[Former Member]

Hi Jeyakanthan

Are you using query in SAP Business One or outside the system? If you select the tables in SAP Business One as OPCH and OPDN then no inner join will appear, as the links sit on the line level. You will need PCH1 and PDN1. The link can be found in both directions, but bear in mind that 1 AP Invoice could be based on more than 1 Goods Receipt PO. For this reason the best approach is to start at the AP Invoice line level and use the BaseType and BaseRef fields to view which lines were pulled from which Goods Receipt PO's.

Kind regards

Peter Juby