Unreconciled AR Transactions Query

[bbranco]

Hi,
I am not sure if this is the right group for this...

I have this query that shows the first part shows me all the AR invoices that were paid with the reconcilation from the 2nd part of the query.

This all works fine. However I am trying to get the check number from the 2nd part of the query to be on each line for the payment  So the DocNum on ScrObjTyp = 24 should be on all of the lines where the ScrObjTyp = 13

SELECT T2."TotalExpns" AS "Total Freight",T2."NumAtCard", T6."ItemCode" AS "ItemCode", T6."Dscription" AS "ItemDesc", T6."Quantity", T6."LineTotal",T7."SlpName", '' AS "CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum", '' AS "DocDate", '' AS "CheckNo" FROM OITR T0 

INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum"

INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"

INNER JOIN INV1 T6 ON T2."DocEntry" = T6."DocEntry"

INNER JOIN OSLP T7 ON T2."SlpCode" = T7."SlpCode"

WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C' AND T1."SrcObjTyp" = '13'

UNION ALL

SELECT 0 AS "Total Freight", '' AS "NumAtCard",'' AS "ItemCode", '' AS "ItemDesc", 0 AS "Quantity", 0 AS "LineTotal", '' AS "SlpName", T4."CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum", T3."DocDate", T3."CounterRef" AS "CheckNo" FROM OITR T0  INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum" INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"

INNER JOIN ORCT T3 ON T3."DocEntry" = T1."SrcObjAbs"

INNER JOIN OINV T4 ON T2."DocNum" = T4."DocNum"

WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C'  AND T1."SrcObjTyp" = '24'

ORDER BY T0."ReconNum

[jitin_chawla]

Hi,

CounterRef field in the ORCT is not the check number field. I created a check payment and no value appears in it in the DEMO Database.

Better join the RCT1 table and there you can find the check number field.

Kr,

Jitin