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

[former_member183402]

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.

[former_member312729 · Accepted solution]

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.

[tazoo08]

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.