Skip to Content
0

How can I join ORCT, RCT2, OINV, INV1 and OACT tables of SAP B1?.

Jul 31, 2017 at 10:32 AM

573

avatar image

Hi all,

I want to do query which will display data from A/R Invoice and Incoming Payment,

How can I join ORCT, RCT2, OINV, INV1 and OACT tables of SAP B1

I tried to do something like this but it brings duplication

SELECT T4.[price] FROM ORCT T0 LEFT JOIN RCT2 T1 ON T0.DocNum = T1.DocNum LEFT Join OINV T2 On T2.DocEntry=T1.DocEntry INNER JOIN OACT T3 ON T0.[CashAcct] = T3.[AcctCode] inner join INV1 T4 ON T2.[DocEntry]=T4.[DocEntry]

Please anyone can help me.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Balaji Selvaraj Jul 31, 2017 at 11:10 AM
1

Hi ,

The query will repeat Incoming payment multiple times based on number of invoice selected in row of incoming payment. Similarly Invoice Number will repeat based on number of row items of the invoice.

Share
10 |10000 characters needed characters left characters exceeded
Muatez Abdu Jul 31, 2017 at 11:38 AM
0

Hi Rurangwa Schadrack,

I was faced the same issue before in my Query, but it solved by added the Group & Summary in the Crystal Report:

SELECT T0.[DocNum] PaymentNo, T0.[DocDate] PaymentDate, T0.[CardCode], T0.[CardName], T1.[BfDcntSum] PaymentTot, T2.[DocNum] InvNo, T3.[SlpName],T2.[U_Branch], T2.[DocTotal] InvTot, T2.[DiscSum] TotDiscount, T4.[AcctName] InvAcct, T2.[DocDate] InvDate FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.[DocEntry] = T1.[DocNum] Left Outer Join OINV T2 ON T1.[DocEntry] =T2.[DocEntry] And T0.[CardCode] =T2.[CardCode] INNER JOIN OSLP T3 ON T2.[SlpCode] = T3.[SlpCode] INNER JOIN OACT T4 ON T2.[CtlAccount]= T4.[AcctCode] WHERE T2.[CANCELED] ='N' And T0.[Canceled] ='N'

Thanks.

Share
10 |10000 characters needed characters left characters exceeded