cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure Delivery

mari_outtaleb22
Participant
0 Kudos
95

Hi,

I'm trying to create a stored procedure to limit the creation of deliveries to quantities greater than the open sales order quantity.

Can you please correcte my query ?

Regards .

USE [DB_TEST]
GO
/****** Object:  StoredProcedure [dbo].[SBO_SP_TransactionNotification]    Script Date: 25/09/2023 11:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(30), 				-- 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),
@QTY NUMERIC (19,6)

AS

begin

-- Return values
declare @error  int				-- Result (0 for no error)

-------------------		Limiter les livraisons		-----------------------------------

declare @error_message nvarchar (200) 		-- Error string to be displayed
select @error = 0
select @error_message = N'Ok'

--------------------------------------------------------------------------------------------------------------------------------

 if @object_type in('20') and   @transaction_type in ('A','U')
 
begin
 
  if ( (select count(*) from ORDR b inner join RDR1 a on a.DocEntry=b.DocEntry inner join DLN1 d on d.BaseEntry=a.DocEntry  inner join ODLN c on d.DocEntry=c.DocEntry
   where c.docentry = @list_of_cols_val_tab_del and @QTY < a.OpenQty and a.ItemCode=d.ItemCode  
    ) > 0)
 
     begin
          select @error =5
          select @error_message = 'La quantité livrée supérieure à la quantité commandée.'
     end
 
end

--------------------------------------------------------------------------------------------------------------------------------

-- Select the return values
select @error, @error_message

end

Accepted Solutions (1)

Accepted Solutions (1)

SonTran
Active Contributor

Hi,

Try this

IF @Object_type = N'15' and @transaction_type = N'A'
	BEGIN
			 If exists
				(select 1 From DLN1 T1 inner join ODLN T2 on T1.Docentry = T2.DocEntry
				 where T2.DocEntry = @list_of_cols_val_tab_del and T1.Quantity > T1.BaseOpnQty)
			
				begin
				  Set @error = 2004
				  Set @error_message = N'La quantité livrée supérieure à la quantité commandée.'
				end
	END

Hope this helps,

Son Tran

mari_outtaleb22
Participant
0 Kudos

Hi Son Tran Van,

This procedure works for my case.

Thanks.

Answers (0)