on 07-16-2009 3:44 AM
Hi Experts
I want to make a field in the purchase order mandatory for data entering. how can i do this?
Do i have to use the SDK or a Formatted search please tell me the proper way of doing this.
Thanks & Regards
Kanishka Wickrama
The following is the full procedure i use, let me know what is wrong in it
SET ANSI_NULLS ON
GO
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'
-
-- ADD YOUR CODE HERE
IF (@object_type = N'22' AND @transaction_type in (N'A', N'U') )
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
SELECT @error , @error_message
END
END
-
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this one, it is working. (And see the difference) ::
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
IF (@object_type = N'22' AND @transaction_type in (N'A', N'U') )
BEGIN
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode IS NULL or T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
END
END
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message
end
The following is the full procedure i use, let me know what is wrong in it
SET ANSI_NULLS ON
GO
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'
-
-- ADD YOUR CODE HERE
IF (@object_type = N'22' AND @transaction_type in (N'A', N'U') )
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
SELECT @error , @error_message
END
END
-
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The following is the full procedure i use, let me know what is wrong in it
SET ANSI_NULLS ON
GO
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'
-
-- ADD YOUR CODE HERE
IF (@object_type = N'22' AND @transaction_type in (N'A', N'U') )
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
SELECT @error , @error_message
END
END
-
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The following is the full procedure i use, let me know what is wrong in it
SET ANSI_NULLS ON
GO
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'
-
-- ADD YOUR CODE HERE
IF (@object_type = N'22' AND @transaction_type in (N'A', N'U') )
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
SELECT @error , @error_message
END
END
-
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Suda
I have the below code in the SP SBO_SP_TransactionNotification but nothing happens what could be the reason ?
IF (@object_type = '22' AND @transaction_type in (N'A', N'U') )
BEGIN
IF EXISTS (SELECT T1.DocEntry FROM OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
WHERE (T1.OcrCode IS NULL OR T1.OcrCode = '') AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Set @error =1
set @error_message = 'Profit Center cannot be blank'
SELECT @error , @error_message
END
END
Does this only work for SAP B1 2007?
Thanks & Regards
Kanishka Wickrama
Edited by: kanishka wickrama on Oct 7, 2009 12:35 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Oh sorry my mistake i changed it to 22 but no result yet.
Thanks & Regards
Kanishka Wickrama
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you are talking about Purchase Order then the table in your SQL should be POR1 and not PDN1
OPDN and PDN1 refers to Goods Receipts
OPOR and POR1 are table for Purchase Orders
IF (@object_type = '22' AND @transaction_type= 'A')
BEGIN
IF EXISTS (SELECT DocEntry FROM dbo.POR1 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
Suda
Hi experts
I used the following code in my SP SBO_SP_TransactionNotification, which seems to be working for others when i read the other threads.
But this does not work for me at all. Am i missing something here ?
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
Thanks & Regards
Kanishka Wickrama
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Gordon & Joseph
I tried both the options you gave but nothing seems working. Do i have to do any settings change or something, i cant understand why it is not working.
Thanks & Regards
Kanishka Wickrama
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kanishka Wickrama,
Try this one:
If @object_type = '22' and @transaction_type in (N'A', N'U')
Begin
if exists (SELECT DocEntry FROM POR1 T0 WHERE (T0.OcrCode is NULL or T0.OcrCode ='') and
T0.DocEntry=@list_of_cols_val_tab_del)
begin
begin
set @Error = 50
set @error_message = N'Please Fill Section'
end
End
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI
I changed the object type to be 22 but still nothing happens.
Thanks & Regards
Kanishka Wickrama
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Experts
I wrote a code like below to make the profit center code mandatory in the purchase order but it does not work.
If @object_type = '14' and @transaction_type in ( 'A','U')
Begin
if exists (SELECT T0.DocEntry FROM POR1 T0 inner join OPOR T1 on T0.DocEntry = T1.DocEntry
WHERE T0.OcrCode is null and T1.DocEntry=@list_of_cols_val_tab_del)
begin
select @Error = 50 , @error_message = 'Please Fill Section'
end
End
Can some one tell me what would be the problem here.
Thanks & Regards
Kanishka Wickrama
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kanishka,
Please try this and let me know if it works:
If @object_type = '22' and @transaction_type in (N'A', N'U')
Begin
if exists (SELECT T0.DocEntry FROM POR1 T0 inner join OPOR T1 on T0.DocEntry = T1.DocEntry
WHERE T0.OcrCode is null and T1.DocEntry=@list_of_cols_val_tab_del)
begin
begin
set @Error = 50
set @error_message = N'Please Fill Section'
end
End
Regards,
Joseph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you are referring to a SAP table field, you could use either SDK or SBO_SP_TransactionNotification stored Procedure.
Formatted Search would not help with this.
Suda
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kanishka,
Use stored procedure to make a field mandatory.
*Close the thread if issue solved.
Regards
Jambulingam.P
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hai!
Wheather the field is a udf or what?
The previous thread links guide you to a SP method to make a field mandatory.
you can also try using a UDF.
1. Add a new UDF in Header/Row - if your mandatory field is in header/row respectively
2. mark the udf as mandatory.
3. Assign a FMS on that UDF on PO form like, when BP code (Header)/ ItemCode (Row) get refreshed make it null
select ''
this is the FMS..
Try it, it will work fine..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.