How to check all base documents in one place?

[former_member556809]

Hi All,

In our company we have approval procedures applied on Purchase Orders, the approver reviews each line item before approving or rejecting a document. We have some POs that are copied from single Purchase Requests and some that are copied from multiple PRs.

The problem we are facing is that if a PO is issued with multiple PRs and have long list of line items, it is becoming very time consuming to check each line item for their base document.

My question is, is there a way to show all base documents(PRs) that a PO is issed from in one place?

[zal_parchem2]

Hello Abid...perfect timing - take a look at this blog. Tells you how to connect those Purchase Requistions with the Purchase Order by Line. Duplicates the function you see as "Relationship Map" in SAP B1 at that lower level.

Re-Create the Relationship Map in SAP B1

https://blogs.sap.com/2018/12/30/sap-business-one-recreate-the-relationship-map-with-linenum-and-bas...

Here is your basic code:

SELECT DISTINCT

T1.DocNum, T1.DocDate, T1.CardCode, T3.NumAtCard,
T1.DocEntry AS 'OPRQ DocEntry', 
T0.LineNum AS 'PRQ1 LineNum',
T0.ItemCode, 
T2.BaseEntry AS 'POR1 BaseEntry', 
T2.BaseLine AS 'POR1 BaseLine', 
T2.ItemCode,
T0.TrgetEntry AS 'PRQ1TrgetEntry', 
T3.DocEntry AS 'OPOR DocEntry'

FROM PRQ1 T0  

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

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

LEFT OUTER JOIN OPOR T3
ON T2.DocEntry = T3.DocEntry

And here are the results - some POs created with "Copy To" on the Purchase Request Window and some created with "Copy From" on the Purchase Order window.

Good luck and let us know how it goes for you...

Zal