Goods Receipt Purchase Order FMS

[satinath_biswas]

Dear Experts,

I Have one UDF in OPDN named OPDN.U_TotalQuantity. Now I have selected 3 line items with different quantity in the line level of GRPO. I want the sum of PDN1.Quantity in the OPDN.U_TotalQuantity field by FMS before saving the GRPO transaction.

Please help me out with FMS query by which I can get the total quantity of the line level in header UDF.

Thanks & Regards,

Satinath Biswas

[narayanis · Accepted solution]

Hi,

When you write a FMS for a header level field, system provides only the header level fields on the auto refresh check box of the FMS setup window.

For having the cumulative total for all lines in the document, FMS needs to be refreshed on change of field at each line.

so, this is practically not possible. You can achieve this with the help of SDK or post transaction notification.

[satinath_biswas]

I have tried and the same is achievable by creating a temporary table and stored procedures, please check the below queries -

TEMP TABLE CREATION :

CREATE ROW TABLE "NIRMALLIVE2021"."TEMPQTY" ( "Quantity" DECIMAL(18,

6) CS_FIXED ) 

TO SAVE DATA INTO TEMP TABLE :

CREATE PROCEDURE "QTYCAL" (IN Quantity Decimal(18,6), IN ROWID2 INTEGER)  

AS

BEGIN

dECLARE ID INT;

ID:=ROWID2;

IF :ID = 1 

THEN  

DELETE FROM "TEMPQTY";

end if;

    insert into "TEMPQTY" VALUES (Quantity);

    SELECT cast( Quantity as Decimal(18,6))  FROM dummy;

END;

TO GET RESULT IN TOTAL QTY FIELD :

CREATE PROCEDURE "QTYCAL2"  

AS

BEGIN

    SELECT sum(cast( "Quantity" as Decimal(18,6)))  FROM "TEMPQTY";

END;

CALL BELOW QUERY IN MATRIX QUANTITY COUMNS (FMS)

CALL  "QTYCAL"(CAST(replace($[$38.11],',','') AS DECIMAL(18,6)) ,$[$38.0])

IN TOTAL QUANTITY FMS :

CALL "QTYCAL2"