Skip to Content
avatar image
Former Member

FMS not running in sap b1 hanna

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"]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • avatar image
    Former Member
    Jan 06, 2015 at 01:53 AM

    Hi,

    Why do you have "VISIONTEST" in the query?

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2015 at 12:30 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2015 at 06:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jan 06, 2015 at 07:04 AM

    Did you check my reply?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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)

  • Jan 08, 2015 at 06:04 AM

    Did you check my last reply?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06, 2015 at 09:14 PM

    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

    Add comment
    10|10000 characters needed characters exceeded