Skip to Content
0
Jan 12, 2023 at 10:39 AM

Payment query not working SAP Business One HANA

64 Views Last edit Jan 13, 2023 at 05:17 AM 3 rev

Dear all,

Below query in that incoming payment field DocNum and DocDate highlighted fields are not coming.

SELECT

T5."DocNum" AS "AR Invoice no",

T5."CardCode" AS "Customer Code",

T5."DocDate" as "Invoice Date",

T5."DocTotal" as "Invoice Total",

T0."DocNum" AS "Invoice No.",

T0."DocDate",

t9."TrsfrDate",

T9."DocDate",


--T9."DocTotal"

(T5."DocTotal" - T0."DocTotal") as "Payment amount"

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"

left JOIN INV1 T4 ON T1."BaseEntry" = T4."DocEntry" and T1."BaseLine" = T4."LineNum"

left JOIN OINV T5 ON T4."DocEntry" = T5."DocEntry"

LEFT join RCT2 T8 ON T8."DocEntry" = T0."DocEntry"

INNER JOIN ORCT T9 ON T9."DocNum" = T8."DocNum"


INNER JOIN OCRD T2 ON T0."CardCode"=T2."CardCode"

INNER JOIN OCRG T3 ON T2."GroupCode"=T3."GroupCode"

WHERE T0."DocDate" >=[%0] AND T0."DocDate" <=[%1] AND T0."CANCELED"='N'

GROUP BY

T9."DocDate", T9."DocTotal",T0."DocNum",T5."DocNum",T0."ObjType",T3."GroupName", T0."DocDate",

T0."CardCode", T0."CardName",T0."Address", T0."DocTotal",T0."VatSum",T1."AcctCode",

T5."CardCode",T5."DocTotal",T5."DocDate",t9."TrsfrDate",T9."DocDate"

ORDER BY T0."DocNum"

Regards,

Nikunj