Skip to Content
0
Sep 10 at 09:50 AM

Query modification SAP Business One HANA

56 Views Last edit Sep 10 at 10:00 AM 3 rev

Dear all,

Help me on the query modification.

SELECT distinct T0."DocNum", (CASE WHEN IFNULL(T0."NumAtCard",'')='' THEN CAST(T0."DocNum" AS VARCHAR(50)) ELSE T0."NumAtCard" END) AS "PO No.",

T0."BPLName", T0."U_DELIVERY",

T0."ShipToCode" AS "Ship From", T0."CardCode",

T0."CardName", T0."DocDate",

T1."ShipDate" AS "Row Level Delivery Date",

T0."NumAtCard", T1."ItemCode",

T1."Dscription",

T1."Quantity" AS "PO QTY",

T4."Quantity" AS "Standlone AP Credit Memo Qty",

T4."NoInvtryMv" AS "WithoutQtyPosting",

T4."U_UNE_PONUM" AS "PO Number",

T1."OpenQty" AS "Non-Supplied Qty",

T2."USER_CODE" AS "CREATED BY USER ID",

T2."U_NAME" AS "USER NAME"

FROM OPOR T0 INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"

LEFT JOIN OUSR T2 ON T0."UserSign"=T2."USERID"

LEFT JOIN OBPL T3 ON T3."BPLId"=T0."BPLId"

LEFT JOIN RPC1 T4 ON T1."DocEntry" = T4."DocEntry" AND T1."BaseRef"= T4."BaseRef"

----LEFT JOIN ORPC T5 ON T4."DocEntry" = T5."DocEntry"

WHERE T0."DocType" ='I' --AND T4."DocEntry"='13237'

AND T0."DocDate" >= [%0] AND T0."DocDate" <=[%1]

AND T4."U_UNE_PONUM" <> ''

I need to match T4."U_UNE_PONUM" (This is row level UDF) match this field with Purchase Order Document number. If it's match then data comes.


How to match row level UDF value AP credit note with Purchase Order Document Number SAP Business one?

If row level UDF values match with Purchase order document number then result comes.

E.G. I have created one standalone AP Credit Note and mentioned Row level UDF 123.

Now , This 123 match with Purchase order document number and data comes.

Regards,

Nikunj