Relationship between ORCT and OINV

[Former Member]

Dear Experts,

I need to create a report of history transaction, and need to get :

-INVOICE / DP NO

-AMOUNT INVOICE / DP

-INCOMING PAYMENT NO

-AMOUNT INCOMING PAYMENT PER INVOICE / DP

Does anyone can help the relationship table between ORCT and OINV/ODPI?

Thank you,

-Flo

[Former Member · Accepted solution]

Ah, i think i got it :

SELECT *

FROM ORCT T0

LEFT JOIN RCT2 T1 ON T1.DocNum=T0.DocNum

LEFT JOIN OINV T2 ON T2.DocNum=T1.DocEntry

That one is for invoice

[Former Member · Accepted solution]

Hi.....

Try this Relationship......

I guess this works perfect for you.....

Select * From ORCT T0  LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum 
LEFT Join OINV T2 On T2.DocNum=T1.BaseAbs

Regards,

Rahul

[Former Member · Accepted solution]

hi Rahul, ah that one is Base Ref Number eh? Thanks !!!

[former_member627710]

the correct relationship is....

SELECT * FROM

ORCT T0

LEFT JOIN RCT2 T1 ON T1."DocNum" = T0."DocEntry"

LEFT JOIN OINV T2 ON T2."DocEntry" = T1."DocEntry"

[anrodse]

Correct answer is:

SELECT
FROM
   OINV T0
   LEFT JOIN RCT2 TC1 ON T0."DocEntry" = TC1."baseAbs" AND TC1."InvType" = 13
   LEFT JOIN ORCT TC0 ON TC0."DocEntry" = TC1."DocNum"
WHERE T0."Canceled"='N' AND TC0."Canceled"='N'

Yo need to tell the base document type for payment. 13 for invoices.

[henry_le]

Hi, I think matching TC0."DocNum" = TC1."DocNum" would be better because sometimes DocNum and DocEntry of a single record are different

[mk3]

Hii. Please refer to this link.

https://answers.sap.com/questions/11829509/how-to-use-opch-and-ovpm-tables-in-query-in-sap-b1.html?c...

Hope it answers your question.

Regards

[mk3]

Hii,

Try this:

SELECT *

FROM OINV T0 LEFT JOIN ORCT T1 ON T0.[ReceiptNum] = T1.[DocEntry] LEFT JOIN INV1 T2 ON T0.[DocEntry] = T2.[DocEntry] LEFT JOIN RCT2 T3 ON T1.[DocEntry]=T3.[DocNum]

Thanks

[marcelovicmb]

Great, for my ok, so I do not undestand using ORCT T1 ON T0.[ReceiptNum]

[jorgedonoso2]

sorry by me english, but reply is bad the correct answer is :

OINV.DocEntry = INV1.DocEntry

OINV.DocEntry = RCT2.DocEntry

ORCT.DocNum = RCT2.DocNum