GROP Quantity must not be greater then PO Open Quantity SAP B1

[owaisk]

Hello Fam,

i made a stored procedure, that defines GRPO item quantity must not be greater then PO Open Quantity.

but it is only triggering if PO has target GRPO Document.

e:g PO qty = 5

Open PO Qty = 3 then it is triggering.

if

PO Qty = 5

Open PO Qty = 5 then it is not triggering.

Kingly help, sp copied below.

IF :object_type = '20' and (:transaction_type = 'A' OR :transaction_type = 'U') THEN

Select Count (*) INTO cnt From OPDN a

Inner Join PDN1 b on a."DocEntry" = b."DocEntry"

Where a."DocEntry" = :list_of_cols_val_tab_del;

For v_Index IN 1..:cnt DO

SELECT COUNT(*) INTO cnt FROM "OPDN" a INNER JOIN "PDN1" b ON a."DocEntry" = b."DocEntry"

INNER JOIN POR1 T2 ON b."BaseEntry" = T2."DocEntry" AND b."BaseType" = '22'

INNER JOIN OPOR T3 ON T2."DocEntry" = T3."DocEntry"

WHERE a."DocEntry" = :list_of_cols_val_tab_del and b."Quantity" > T2."OpenQty" and b."ItemCode" = T2."ItemCode" and b."LineNum" = T2."LineNum"

AND b."VisOrder" = v_index - 1 ;

IF :cnt > 0 THEN

error := 2803;

error_message := N'GRPO Quantity is not matched with PO Quantity' || ' on Line# ' || :V_Index;

Break;

END IF ;

End For ;

END IF ;

[narayanis]

Hi,

That is because you have joined purchase order tables using inner join. So the code works when there is a GRPO against purchase order. In case of a first GRPO inner join does not return any rows and TN does not trigger.

Regards

[SonTran]

Hi,

Try this

IF :object_type = N'20' AND :transaction_type = N'A' THEN
BEGIN
	declare line int;
	select T1."LineNum" + 1 into line 
				from PDN1 T1 inner join OPDN T2 on T1."DocEntry" = T2."DocEntry"
				where T2."DocEntry" = :list_of_cols_val_tab_del and T1."Quantity" > T1."BaseOpnQty";
	if :line>0 then
		begin
			error := 1;
			error_message := N'Line '|| :line ||' has received Qty > Open PO Qty';
		end;
	end if;
	
END;		
END IF;

Hope this helps,

Son Tran

[owaisk]

Thanks for the response Tran, i tried another way and it worked. *mentioned below

IF :object_type = '20' and (:transaction_type = 'A' OR :transaction_type = 'U') THEN

Select Count (*) INTO cnt From OPDN a

Inner Join PDN1 b on a."DocEntry" = b."DocEntry"

Where a."DocEntry" = :list_of_cols_val_tab_del;

For v_Index IN 1..:cnt DO

SELECT COUNT (*) INTO CNT

FROM "PDN1" T0

INNER JOIN "POR1" T1 ON T0."BaseType" = T1."ObjType"

AND T0."BaseEntry" = T1."DocEntry"

AND T0."BaseLine" = T1."LineNum"

WHERE T0."DocEntry" = :list_of_cols_val_tab_del

AND T0."Quantity" +

IFNULL((Select SUM(T2."Quantity")FROM "PDN1" T2 INNER JOIN "OPDN" T5 on T2."DocEntry" = T5."DocEntry" WHERE

T2."BaseType" = T1."ObjType"

AND T2."BaseEntry" = T1."DocEntry"

AND T2."BaseLine" = T1."LineNum"

AND T2."DocEntry" <> :list_of_cols_val_tab_del AND T5."CANCELED" ='N' ),0) >

T1."Quantity" AND T0."VisOrder" = v_index - 1 ;

IF :CNT>0 then

error := 4003;

error_message := 'GRPO Quantity is not matched with PO Quantity' || ' on Line# ' || :V_Index;

CNT := 0 ;

END IF ;

END For;

END IF;