Document relationships

[Former Member]

Hi Experts,

What table or field do I need to track a Purchase Request through to it being made into an invoice, or the same with Sales Quotations through to Sales invoices?

I know this is possible as SAP B1 does it, but I can't find the common fields.

Thanks

Rory

[former_member188586 · Accepted solution]

hi

can you tell me what procedure r u following ?

means

PR-->PO-->>GRPO>>AP or any other please tell me

PR>>PQ>>PO>>GRPO>>AP

--Ramudu

[Former Member · Accepted solution]

Hi Ramudu,

I am tracking PR-->>(Draft)PO-->>PO-->>AP

Thanks

[former_member188586 · Accepted solution]

ok

please check bellow threads

Query for PO -> AP Invoice | SCN

https://scn.sap.com/thread/3194011

--Ramudu

[Former Member]

Hi Downey,

You may try this,

Select  F.DocNum[PO No]

  ,(Select P.U_NAME from OUSR P where P.USERID = F.UserSign)[PO User]

  , A.CardCode[Vendor Code], A.CardName[Vendor Name], F.DocDate[PO Date],F.DocTotal [PO Total]

  , D.DocNum[GRN No]

  ,(Select P.U_NAME from OUSR P where P.USERID = D.UserSign)[GRN User]

  , D.DocDate[GRN Date],D.DocTotal [GRN Total]

  , A.DocNum[Invoice No]

  ,(Select P.U_NAME from OUSR P where P.USERID = A.UserSign)[AP Invoice User]

  , A.DocDate[Invocie Date],A.DocType [Invoice Type]

  ,A.DocTotal[Inv Total], A.PaidSum [Amt Paid], H.Docnum,H.Docdate,A.PaidToDate[Applied Amt], (A.DocTotal-A.PaidToDate)[Balance Due]

  ,(Select P.U_NAME from OUSR P where P.USERID = H.UserSign)[Payment User]

From dbo.OPCH A

Inner Join PCH1 B on A.DocEntry = B.DocEntry

Left Outer Join PDN1 C on B.BaseType = C.ObjType and B.BaseEntry = C.DocEntry and B.BaseLine = C.LineNum

Left Outer Join OPDN D on C.DocEntry = D.DocEntry

Left Outer Join POR1 E on C.BaseType = E.ObjType and C.BaseEntry = E.DocEntry and C.BaseLine = E.LineNum and B.BaseLine = E.LineNum

Left Outer Join OPOR F on E.DocEntry = F.DocEntry

Left Outer Join VPM2 G on A.DocEntry = G.DocEntry

Left Outer Join OVPM H on G.DocNum = H.DocEntry

Where A.CANCELED='N'

    and A.CardName = [%0]

Thanks,

Harshal Makwana

[Former Member]

Thanks Harshal,

Would it be a left join of OPRQ to bring in the Purchase Request?

[Former Member]

Hi Downey,

Yes with left join of OPRQ will add Purchase Request in your report.

Thanks,

Harshal Makwana