block Goods Return qty not Equal GRPO qty

[MD1]

Hi All,

block Goods Return qty not Equal GRPO qty

Please assist.

SAP B1 HANA /SQL

Danish

[Former Member · Accepted solution]

Hi Danish,

Would the transaction notification of sql help?

I dont have knowledge of HANA yet..

Thanks,

Joseph

[MD1 · Accepted solution]

----------------------Block Goods Return Qty is less then base document----------------------

IF :Object_type = '21' AND :transaction_type = 'A' THEN

    Declare line integer;

    SELECT ("LineNum" - 1) INTO line FROM RPD1;

    IF (NOT IFNULL(:line, 0) = 0) THEN

        error := 10;

        error_message := 'Line quantity' || CAST(:line AS nvarchar(4)) || ' is Less then  open quantity !';

    END IF;

END IF;

Thanks

[KennedyT21 · Accepted solution]

Hi Mohd

convert this to hana

IF @transaction_type IN (N'A', N'U') AND

(@Object_type = N'21')

BEGIN

IF EXISTS (SELECT T1.BaseEntry

FROM dbo.Orpd T0

INNER JOIN dbo.rpd1 T1 ON T1.DOCENTRY = T0.DocEntry

INNER JOIN dbo.pdn1 T2 ON T2.DOCENTRY = T1.BaseEntry

WHERE T1.BaseType = 20 AND

T1.ItemCode = T2.ItemCode AND

T1.BaseLine = T2.LineNum AND

T0.DOCENTRY = @list_of_cols_val_tab_del

GROUP BY T1.BaseEntry

HAVING SUM(T1.Quantity) > SUM(T2.OpenQty))

Begin

SELECT @Error = 10, @error_message = Return quantity is greater than GRPO open quantity'

End

END

Regards

Kennedy

[Former Member · Accepted solution]

Hi Danish,

The below SP in SQL blocks the return if retrun Qty is not equal to GRPO qty

IF @transaction_type IN (N'A', N'U') AND

(@Object_type = N'21')

begin

if exists (SELECT T0.BaseEntry, SUM(T0.Quantity)

FROM [dbo].[RPD1] T0 INNER

JOIN [dbo].[PDN1] T1 ON T1.DOCENTRY =

T0.BASEENTRY

WHERE T0.BaseType = 20 AND T0.ItemCode =

T1.ItemCode AND T0.BaseLine = T1.LineNum

and T0.DOCENTRY = @list_of_cols_val_tab_del

GROUP BY T0.BaseEntry

HAVING (SUM(T0.Quantity) <> SUM(T1.Quantity)) or sum(t0.quantity) > sum(t0.BaseOpnQty))

begin

select @Error = 10, @error_message = 'Return Quantity is not equal to GRPO quantity'

end

end

you may change it as per HANA.

Hope this helps.

Thanks,

Joseph

[MD1 · Accepted solution]

Thanks Dear

SAP HANA 9.1 Query...

IF (:transaction_type ='A' or :transaction_type='U') AND (:Object_type = '21') THEN

    Declare temp_var_0 integer;

    SELECT

    (SELECT Count (T0."BaseEntry")

    FROM "RPD1" T0

        INNER JOIN "PDN1" T1 ON T1."DocEntry" = T0."BaseEntry"

    WHERE T0."BaseType" = 20 AND T0."ItemCode" = T1."ItemCode" AND T0."BaseLine" = T1."LineNum" AND

         T0."DocEntry" = :list_of_cols_val_tab_del

    GROUP BY T0."BaseEntry" HAVING (SUM(T0."Quantity") <> SUM(T1."Quantity")) OR

     SUM(t0."Quantity") > SUM(t0."BaseOpnQty")) INTO temp_var_0 FROM DUMMY;

        IF :temp_var_0 > 0 THEN

        SELECT 10, 'Return Quantity is not equal to GRPO quantity' INTO Error, error_message FROM DUMMY;

    END IF;

END IF;