Dear all,
Below query is worked fine when A/R credit memo / payment is done against the Invoice. But A/R credit memo / payment is not created against this invoice then query is not reflecting only AR invoice data.
SELECT
T0."DocDate" AS "Invoice Posting Date" ,
T0."DocNum" AS "Invoice Document Number",
T2."CardCode" AS "Customer/Vendor Code",
T2."CardName" AS "Customer/Vendor Name",
T0."NumAtCard",
T0."DocTotal",
T1."SumApplied" AS "Paid to Invoice",
T2."DocNum",
T2."DocDate" AS "Posting Date",
T0."DocTotal"- T1."SumApplied",
t5."Balance"
, T0."DocNum",
T0."DocTotal" - T0."PaidToDate" as "Balance Due"
,t9."DocNum"
,t2."DocDate"
,t2."DocTotal"
FROM "OINV" T0
left JOIN "RCT2" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "OCRD" T5 ON T5."CardCode" = T0."CardCode"
left JOIN "ORCT" T2 ON T2."DocNum" = T1."DocNum"
INNER JOIN "INV1" T8 ON T8."DocEntry" = T0."DocEntry"
left JOIN "ORIN" t9 ON t9."ObjType" = T8."TargetType"
INNER JOIN "RIN1" t10 ON T10."DocEntry" = T9."DocEntry" AND T10."BaseEntry" = T0."DocEntry"
--WHERE t0."DocDate" = '20230117'
where T0."DocNum" = '2112000001'
SAP Business One HANA
Regards,
Nikunj Mehta