SBO tables Relationship (AR INVOICE-INCOMING Payment)

am making a query that bring Incoming Payments details , that include payment means , details of payment means , and then some UDFS from the related A/R invoice(s) , in addition to some UDF from an object that relate to a UDF in the AR INVOICE ,

now every time am running my query it show no result.

Am sure there is something I missing here or incorrect but so far couldn’t find it .

if any one can help me with this i will be thankful

here is the query :

SELECT T1.[baseAbs] AS INVOICENO, T0.[DocDate],t0.[trsfrdate],t0.[trsfrref], T0.[CardName],T0.[Doctotal],T4.[VoucherNum] ,
T0.[Comments], T1.[DocNum] AS PAYMENTNO, T2.[Phone1],
T0.[CashSum], T0.[CreditSum], T0.[CheckSum], T0.[TrsfrSum],
T3.[DueDate] AS CHECKDATE, T3.[CheckNum] AS CHECKNO, T3.[Details] AS MAYBEBANKNAME
, t5.[U_UnitCode],t5.[U_Type],t7.[WhsName],t7.[city] ,
t8.U_FloorNo
FROM ORCT T0  
inner JOIN RCT2 T1 ON T0.[DocEntry]  = T1.[DocNum]
inner JOIN OINV T5 ON T5.[docnum] =T1.[BaseAbs]
INNER JOIN RCT1 T3 ON T0.[DocNum] = T3.[DocNum]
INNER JOIN RCT3 T4 ON T0.[DocNum] = T4.[DocNum]
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
INNER JOIN INV1 T6 ON T5.[DocEntry] = T6.[DocEntry]
INNER JOIN OWHS T7 ON T6.[WhsCode] = T7.[WhsCode]
INNER JOIN [dbo].[@AUND] T8 ON T5.[U_UnitCode] = T8.[Code]

Answer

I think there’s a few issues with your query, starting with the join from ORCT to RCT2.

I’ve created similar queries in the past, here’s one which I know works, maybe you can use it to adjust yours. For one thing, you’ll definitely need to adjust a lot of those inner joins to outer joins, as a lot of the relations between Payments and it’s parent business objects (like Invoices) are very loose and may not always apply.

Note that the query below is looking specifically for Invoices in the RCT2 table (this is the “lines” section of the Incoming Payment object), hence the J002.InvType = 13 condition.

SELECT *
FROM [ORCT] J001
LEFT OUTER JOIN [RCT2] J002 ON J002.DocNum = J001.DocNum AND J002.InvType = 13
LEFT OUTER JOIN [OINV] J003 ON J003.DocEntry = J002.DocEntry
LEFT OUTER JOIN [OACT] J004 ON J004.AcctCode = J001.CashAcct      
LEFT OUTER JOIN [RCT1] J005 ON J005.DocNum = J001.DocNum