limit the total amount of a purchase order

[jfpeguero]

is there a way to limit the total amount of a purchase order before go into authorization process in SAP B1?

[mgregur · Accepted solution]

Hi Juan,

you can create an Approval process (Administration => Approval Process => Approval Templates) linked to the Purchase Order (under tab Documents) and with set Document total limit (under Terms, select "When The Following Applies" and then find "Total Document").

BR,

Matija

[Johan_Hakkesteegt]

Hi Juan,

You could use the SBO_SP_TransactionNotification stored procedure. Here you could determine the maximum limit, and if the purchase order goes over that limit, this stored procedure will block the transaction and show an error message to the user.

Please use the MS SQL Server Management Studio to adapt and test this code:

USE [your company database]
GO

/****** Object:  StoredProcedure [dbo].[SBO_SP_TransactionNotification]    Script Date: 11.9.2020 8:41:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(20), 	-- SBO Object Type.
@transaction_type nchar(1),	-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)

AS
begin

-- Return values
declare @error  int			-- Result (0 for no error)
declare @error_message nvarchar (200) 	-- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
-------------------------------------------------------------------------------
--	ADD	YOUR	CODE	HERE
-- Check Purchase orders (22) on Add (A) and Update (U)
IF @object_type = '22' AND @transaction_type IN ('A', 'U') 
  BEGIN
   IF (SELECT ISNULL(dbo.OPOR.DocTotal, 0) 
       FROM dbo.OPOR 
       WHERE dbo.OPOR.DocEntry = @list_of_cols_val_tab_del) > 10000 
       --replace 10000 with the desired maximum amount
   BEGIN
	   SET @error = -99
	   SET @error_message = 'The total is too high, the maximum is 10000'
   END
  END
-------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message

end
GO

Regards,

Johan

[jfpeguero]

Excellent, works very well