FMS not running in sap b1 hanna

[Former Member]

Dear All,

I create a UDF on Inventory Transfer screen and make a FMS Query but a error occurred. please see below mention query and advice.

IF $[OWTR."U_grn"] > 0

SELECT T1.["Quantity"] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and  T0.["DocNum"] = $[OWTR."U_grn"]

else if

$[OWTR."U_RFPN"] > 0

SELECT T1.["Quantity"] FROM OIGN T0  INNER JOIN "VISIONTEST"."IGN1" T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]

[former_member184146]

Hi Asad,

               Try this FMS

IF ($[OWTR."U_grn"] > 0) then

SELECT T1."Quantity" FROM OPDN T0  INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and  T0.["DocNum"] = $[OWTR."U_grn"];

elseif

($[OWTR."U_RFPN"] > 0) then

SELECT T1."Quantity" FROM OIGN T0  INNER JOIN "VISIONTEST"."IGN1" T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1."ItemCode" = $[WTR1."ItemCode"];

end if;

--Manish

[Former Member]

internal error  (1004) occurred

[former_member184146]

Sorry i forget to remove the []  from T0.["DocNum"]

IF ($[OWTR."U_grn"] > 0) then

SELECT T1."Quantity" FROM OPDN T0  INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T1."ItemCode" = $[WTR1."ItemCode"] and  T0."DocNum" = $[OWTR."U_grn"];

elseif

($[OWTR."U_RFPN"] > 0) then

SELECT T1."Quantity" FROM OIGN T0  INNER JOIN "VISIONTEST"."IGN1" T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T0."DocNum"= $[OWTR."U_RFPN"] and T1."ItemCode" = $[WTR1."ItemCode"];

end if;

now try

--Manish

[Former Member]

same error

[Former Member]

Manish, now FMS throwing no error but no results getting and if i click on search field icon  it get 0.0000 value .

[former_member184146]

it means FMS is working !

[Former Member]

yes but this FMS need full if its get all data like itemcode and quantity , which is in GRN doc .

[kothandaraman_nagarajan]

Did you check my last reply?

[Former Member]

yes,

$[OWTR."U_RFPN"] not  $[OWTR."U_RFP"]

[kothandaraman_nagarajan]

Did you check my reply?

[Former Member]

yes i check but same error which is show in the image.

[Former Member]

Hi,

Try this:

SELECT CASE WHEN $[OWTR."U_grn"] > 0 THEN T1.["Quantity"] ELSE WHEN $[OWTR."U_RFPN"] > 0 THEN T2.["Quantity"] END

FROM "OPDN" T0 INNER JOIN "PDN1" T1 ON T0.["DocEntry"] = T1.["DocEntry"] and T0.["DocNum"] = (SELECT $[OWTR."U_grn"] FROM DUMMY), "OIGN" T2 INNER JOIN "IGN1" T3 ON T2.["DocEntry"] = T3.["DocEntry"]

WHERE T2.["DocNum"] = $[OWTR."U_RFPN"] and T3.["ItemCode"] =  (SELECT $[WTR1."ItemCode"] FROM DUMMY)

Thanks,

Gordon

[Former Member]

Dear Gordon Du,

please see error in  the attach image .

[kothandaraman_nagarajan]

Please change $[OWTR."U_RFPN"] to $[OWTR."U_RFP"]. Because your UDF is RFP not RFPN as per attached screen shot.

[Former Member]

Remove all brackets from table name (such as ["Quantity"] ) and try again.

[Former Member]

this error occurred show when remove all brackets

incorrect syntax near "WHEN": lin "(SWEI)

SELECT CASE WHEN $[OWTR."U_grn"] > 0 THEN T1."Quantity" ELSE WHEN $[OWTR."U_RFPN"] > 0 THEN T2."Quantity" END

FROM "OPDN" T0 INNER JOIN "PDN1" T1 ON T0."DocEntry" = T1."DocEntry" and T0."DocNum" = (SELECT $[OWTR."U_grn"] FROM DUMMY), "OIGN" T2 INNER JOIN "IGN1" T3 ON T2."DocEntry" = T3."DocEntry"

WHERE T2."DocNum" = $[OWTR."U_RFPN"] and T3."ItemCode" =  (SELECT $[WTR1."ItemCode"] FROM DUMMY)

[former_member184146]

Hi,

     Try this

IF $[OWTR."U_grn"] > 0

SELECT T1.["Quantity"] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and  T0.["DocNum"] = $[OWTR."U_grn"]

else if

$[OWTR."U_RFPN"] > 0

SELECT T1.["Quantity"] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]

from dummy

Regards,

Manish

[Former Member]

l

Please see the error while i run it.

[former_member184146]

Hi,

     Try this

IF $[OWTR."U_grn"]  > 0

SELECT T1.["Quantity"] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T1.["ItemCode"] = (SELECT $[WTR1."ItemCode"] FROM DUMMY ) and  T0.["DocNum"] = (SELECT $[OWTR."U_grn"] FROM DUMMY)

else if

$[OWTR."U_RFPN"] > 0

SELECT T1.["Quantity"] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T0.["DocNum"] = (SELECT $[OWTR."U_RFPN"] FROM DUMMY) and T1.["ItemCode"] = (SELECT $[WTR1."ItemCode"] FROM DUMMY)

Regards,

Manish

[Former Member]

Hi,

Why do you have "VISIONTEST" in the query?

Thanks,

Gordon

[Former Member]

Thanks for your reply Gordon ,, "VISIONTEST" is Schema Name.

[kothandaraman_nagarajan]

Hi,

Try:

IF $[OWTR."U_grn"] > 0

SELECT T1.["Quantity"] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and  T0.["DocNum"] = $[OWTR."U_grn"]

else if

$[OWTR."U_RFPN"] > 0

SELECT T1.["Quantity"] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]

WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]

Thanks & Regards,

Nagarajan