Purchase Request Status?

[saad_omair]

need to trace Item status from Purchase Request level,

like; let say; there is a PR with 5 items on it, then i need to find the relevant Quantity on Quotation, on Purchase Order, GRPO, Return or issuace (if any).

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query except Purchase request. I don't have table to link:

SELECT T1.DocEntry as 'Link', T1.[DocNum] as 'PO No.', T1.[DocDate] as 'PO Date', T1.DocStatus as ' PO Status', T1.[CardName] as 'Customer Name',   
T0.[Dscription] as 'Part Name', T0.[Quantity] as 'PO Qty',T0.[Price] as 'Purchase Price', T0.[OpenQty], T0.[OpenSum], T3.DocNum as 'GRPO Doc Num', T2.[Quantity] as 'Received Quantity', 
T12.DocNum AS 'Return No', T12.DocDate as 'Return Date', T11.Quantity as 'Retuen Qty' , 
T5.DocNum as 'Invoice No', T5.DocDate as 'Invoice Date', T5.DocStatus as 'Invoice Status' , T4.Quantity as 'Invoice Qty', T5.DocTotal,T5.PaidToDate as 'Applied Amt', 
T7.DocNum as 'Credit Note No.', T7.DocDate as 'Credit Note date',T6.Quantity as 'Credit Note Qty' 

  FROM POR1 T0  INNER JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry 
   left outer join PDN1 T2 on T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Linenum  
   left outer join OPDN T3 on T2.DocEntry = T3.DocEntry 
  left Outer join PCH1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum  and T4.BaseType = 20 
       OR (T4.Basetype=22 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum) 
LEFT outer join RPD1 T11 on T11.BaseEntry = T2.DocEntry and T11.BaseLine = T2.LineNum 
LEFT outer join ORPD T12 on T11.DocEntry = T12.DocEntry 

   left outer join OPCH T5 on T5.DocEntry = T4.DocEntry 
   left Outer join RPC1 T6 on T6.BaseEntry = T5.DocEntry and T6.BaseLine = T4.Linenum  
  left outer join ORPC T7 on T6.DocEntry = T7.DocEntry 
   left outer join OITM T8 on T0.ItemCode = T8.ItemCode 
   
 
  WHERE T1.[DocDate] >=[%0] and  T1.[DocDate] <=[%1]  
  Group by T1.DocEntry, T1.DocNum,T1.DocDate,T1.DocStatus,T1.CardName, T0.[Dscription] , T0.[Quantity] ,T0.[Price], T0.[OpenQty], T0.[OpenSum], T3.DocNum , T2.[Quantity] , 
T5.DocNum , T5.DocDate, T5.DocStatus  , T4.Quantity, T5.DocTotal,T5.PaidToDate , 
T7.DocNum , T7.DocDate ,T6.Quantity,T12.DocNum,T12.DocDate,T11.Quantity

Thanks & Regards,

Nagarajan

[saad_omair · Accepted solution]

Thank you very much Nagarajan,

although it is missing purchase request, but it could be a great starter to get the status of PR Document.

[kothandaraman_nagarajan]

Hi,

I don't have PR table in my DB to link and advice.

@Mahesh Bramharoutu---> This is SAP business one forum and your reply is not relevant to SAP b1.

Thanks & Regards,

Nagarajan

[former_member189209]

Hi,

Please Use this FM MM_PURREQ_HISTORY_GET  and table for PR status data

EBAN Purchase Requisition

EBKN Purchase Requisition Account Assignment

Thanking you

Regards

Mahesh

[saad_omair]

Dear Mahesh im relatively new to Business One Application, i didn't get your answer, can you please elaborate, what is this EBAN, EBKN