Incoming Payment matching Invoice Query

[kedalenechong]

Hi All

Please help improve my Query add another column to show paid AR Invoice Customer Reference, thanks! 

SELECT T0.[DocNum] as 'Incoming Payment#', T0.[DocDate], (case when T1.[InvType] = 13 then T2.[DocNum] else T3.[DocNum] end) as 'Inv/CN Num.', T0.[CardCode], T0.[CardName], T0.[CashSum], T0.[CashSumFC],  T0.[CheckSum], T0.[CheckSumFC],  T0.[TrsfrSum], T0.[TrsfrSumFC],  T0.[NoDocSum], T0.[NoDocSumFC],  T0.[Comments],  (case when T1.[InvType] = 13 then T2.[DocNum] when T1.[InvType] = 14 then T3.[DocNum] else T1.[DocEntry] end) as DocNum, (case when T1.InvType = 14 then (T1.[SumApplied] *-1) else T1.[SumApplied] end) as SumApplied, (case when T1.InvType = 14 then (T1.[AppliedFC] *-1) else T1.[AppliedFC] end) as SumAppliedFC FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum left outer JOIN OINV T2 ON T1.[DocEntry] = T2.[DocEntry] left outer JOIN ORIN T3 ON T1.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1]

Kedalene Chong

[jitin_chawla · Accepted solution]

Hi,

Please check if you are looking for this:

SELECT T0."DocNum" as 'Incoming Payment#', T0."DocDate", T2.NumAtCard,
(case 
when T1."InvType" = 13 
then T2."DocNum" 
else T3."DocNum" 
end) as 'Inv/CN Num.', 
T0."CardCode", T0."CardName", T0."CashSum", T0."CashSumFC",  T0."CheckSum", T0."CheckSumFC",  
T0."TrsfrSum", T0."TrsfrSumFC",  T0."NoDocSum", T0."NoDocSumFC",  T0."Comments",  
(case 
when T1."InvType" = 13 then T2."DocNum" 
when T1."InvType" = 14 then T3."DocNum" 
else T1."DocEntry" 
end) as DocNum, 
(case when T1.InvType = 14 then (T1."SumApplied" *-1) 
else T1."SumApplied" 
end) as SumApplied, 
(case when T1.InvType = 14 then (T1."AppliedFC" *-1) 
else T1."AppliedFC" end) as SumAppliedFC 
FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum 
left outer JOIN OINV T2 ON T1."DocEntry" = T2."DocEntry" 
left outer JOIN ORIN T3 ON T1."DocEntry" = T3."DocEntry" 
WHERE T0."DocDate" between "%0" and "%1"

Kr,

Jitin