cancel
Showing results for 
Search instead for 
Did you mean: 

SP Code to make plant field mandatory in GRPO ?

former_member187989
Active Contributor
0 Kudos

hi all,

How to make plant field mandatory in GRPO using Stored procedure notification ?

Expecting your valuable reply.

Jeyakanthan

Accepted Solutions (1)

Accepted Solutions (1)

former_member187989
Active Contributor
0 Kudos

No it is not an user defined field(UDF),it's profit center(OcrCode)

in row level of GRPO.

Jeyakanthan

former_member583013
Active Contributor
0 Kudos

IF (@object_type = '20' AND @transaction_type= 'A')

BEGIN

IF EXISTS (SELECT DocEntry FROM [dbo\].[OPDN\] WHERE (OcrCode IS NULL OR OcrCode = '') AND DocEntry = @list_of_cols_val_tab_del)

BEGIN

SELECT @error =1, @error_message = 'Profit Center cannot be blank'

END

END

Former Member
0 Kudos

I suggest create it for update too (not only adding) as

IF (@object_type = '20' AND @transaction_type in (N'A', N'U'))

BTW, Suda, when you sleep?

former_member583013
Active Contributor
0 Kudos

Petr,

A GRPO row cannot be updated once added and thats why I did not check the Update mode?

Where are you in the GLOBE?

Former Member
0 Kudos

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(25), 				-- 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'
declare @SO_Price numeric(10,2)
declare @SO_U_MRP numeric(10,2)


IF @transaction_type = 'A' AND @object_type = '20' 
BEGIN

IF (SELECT max(ISNULL(T0.ocrcode,-1))  FROM PDN1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del) != -1

	begin
		SET @error = 10
		SET @error_message = N'Please Enter Profit Centre'
	end
END


select @error, @error_message
END

Former Member
0 Kudos

You are testing it in header level and not row level, header level may be updated. I think that you must test it in row level PDN1.

former_member583013
Active Contributor
0 Kudos

Petr,

That is exactly correct, thanks for pointing..... it was a oversight

IF (@object_type = '20' AND @transaction_type= 'A')

BEGIN

IF EXISTS (SELECT DocEntry FROM [dbo\].[PDN1\] WHERE (OcrCode IS NULL OR OcrCode = '') AND DocEntry = @list_of_cols_val_tab_del)

BEGIN

SELECT @error =1, @error_message = 'Profit Center cannot be blank'

END

END

Answers (6)

Answers (6)

former_member187989
Active Contributor
0 Kudos

It's not working for grpo without plant created from purchase order.

Anway thanks a lot everyone quick responses.

Posts answered question.

Jeyakanthan

former_member187989
Active Contributor
0 Kudos

Thanks Sridharan,

It works well when delivery created from sales order/grpo created from purchase order.

Jeyakanthan

former_member187989
Active Contributor
0 Kudos

Sridharan,

I tested SP code it's not blocking delivery/grpo without entering plant.

Jeyakanthan

Former Member
0 Kudos

try it as

IF @transaction_type = 'A' AND @object_type = '15'

BEGIN

IF exists (SELECT t0.docentry FROM DLN1 T0 WHERE (T0.OcrCode is null or T0.OcrCode = '') and T0.DocEntry = @list_of_cols_val_tab_del)

begin

SET @error = 10

SET @error_message = N'Please Enter Profit Centre'

end

END

Former Member
0 Kudos

Hi

It works fine for me. Without 'Profit Centre' (Plant) it would not allow you to add GRPO and Delivery.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(25), 				-- 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'

IF @transaction_type = 'A' AND @object_type = '20' 
BEGIN

IF (SELECT max(ISNULL(T0.ocrcode,-1))  FROM PDN1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del) != -1


	begin
		SET @error = 10
		SET @error_message = N'Please Enter Profit Centre'
	end
END

IF @transaction_type = 'A' AND @object_type = '15' 
BEGIN
 
IF (SELECT max(ISNULL(T0.ocrcode,-1))  FROM DLN1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del) != -1
 
	begin
		SET @error = 10
		SET @error_message = N'Please Enter Profit Centre'
	end
END



select @error, @error_message
END

Former Member
0 Kudos

Dear Sir

I wrote a same type of query to make the Vendor Reference No Field (NumAtCard) in GRPO and one another query for make the Project code as mandatory in the line item for the same document.

i applied the query through user defined value set up, but i am unable to solve the issue. I would like to know, how to apply the same query in to the document to make the field mandatory .

Please help me to sort out the same.

Thanks

Regards

Kashi

former_member187989
Active Contributor
0 Kudos

Thanks sudha,

SP code is working,We also want plant to be mandatory in delivery note,Whether it is possible to alter in same SP ?

Jeyakanthan

Former Member
0 Kudos

Append this code to the Stored Procedure


IF @transaction_type = 'A' AND @object_type = '15' 
BEGIN

IF (SELECT max(ISNULL(T0.ocrcode,-1))  FROM DLN1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del) != -1

	begin
		SET @error = 10
		SET @error_message = N'Please Enter Profit Centre'
	end
END

Former Member
0 Kudos

Do you mean you need a UDF plant have to input value by SP TN?

Thanks,

Gordon

former_member583013
Active Contributor
0 Kudos

Is the Plant field a User field that you have defined?

If it is a UDF then in the definition itself you have the option to make it mandatory?

Suda