Skip to Content
0
Jan 18, 2023 at 05:21 AM

Query not getting Invoice details SAP Business One HANA

121 Views

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