GRPO quntity not exceeding Purchase Blanket Agreement quantity Stored Procedure

[arif_siddique12]

Dear All,

I am working on SAP B1 2019 PL 9.3. Now I have a issue regarding Qty in GRPO.

So is there any way within SAP Business One that the help me to create Stored Procedure for GRPO Qty should not exceed the Purchase Blanket Agreement quntity.

Please guide me in this regard.

Regards

M Arif Siddique

[kothandaraman_nagarajan]

Hi,

Did you check standard option under Document Settings-->General Tab-->When Quantity/Amount Exceeds Planned Quantity/Amount for Documents?

Regards,

Nagarajan

[mgregur]

Hi,

if you know what syntax you need, you can always use ALTER on the SP_TransactionNotification for your needs. You need to do this through HANA Studio. See note 1320484 on working with Stored Procedures.

BR,

Matija

[arif_siddique12]

Dear,

I know how to create SP. I just wanted to know what is logical answer of my question.

Thanks

[mgregur]

Hi Muhammad,

If I understand correctly, you need the code for this? This is not complicated:

IF :object_type = N'20' AND (:transaction_type = N'A' OR :transaction_type = N'U') THEN
   SELECT IFNULL(COUNT(*),0) INTO Agr_cnt 
   FROM OPDN T0 
   INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"
   INNER JOIN OAT1 T2 ON T1."AgrNo" = T2."AgrNo" AND T1."AgrLnNum" = T2."AgrLineNum"
   INNER JOIN OOAT T3 ON T2."AgrNo" = T3."AbsID" AND T3."Method" = 'I'
   WHERE T0."DocEntry"= :list_of_cols_val_tab_del AND ((T2."PlanQty"-T2."CumQty"-T2."UndlvQty")< '0');
   if :Agr_cnt > 0 THEN
               begin
               error := 1;
               error_message := N'Quantity exceeds remaining open quantity';
               end;
   end if;
end if;

BR,

Matija

[arif_siddique12]

Dear Matija,

Yeah I need code. The code you have provided is not working for my question.How compare PDN1.Quantity and OAT1.PlanQty

if PDN1 quantity exceeds quantity of OAT1 SP works?

Thanks

[mgregur]

Hi Muhammad,

the code I provided checks in case you have partial delivers per that Agreement, and should work. It links the Agreement for each line and at the time of posting the document checks if the quantity remaining falls below zero. In that case it will block adding of the document.

Also, did you declare variable Agr_cnt in your code, and reset it at the beginning of the code?

Note that I'm working on HANA, if you're on MS SQL you might need some changes.

BR,

Matija