Help in setting up SBO Transaction Notification

Hello experts,

I need to set up an SBO Transaction Notificatiion in SAP B1 9 which looks at NULL fields and displays to the user the actual field that has null values. The query itself should be looking at several fields but only displaying to the user as an indicator of the fields that are supposed to be filled up.

Please assist.

Regards,

Isaac K.

SAP Managed Tags:
SAP Business One

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi,

Please explain your requirement with an example.

Thanks.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Nagarajan,

Thanks for your reply. I have the below SBO control set up:


IF @object_type= '22' AND @transaction_type in ('A','U') 
Begin
IF  EXISTS(SELECT T0.DocEntry FROM dbo.[@SBO_SP_NOTIFICATION] T0 where IsNull(T0.U_Active,'')in('Yes') and T0.U_QueryNo = 'PO013')  
BEGIN
IF EXISTS (SELECT T2.DocEntry FROM dbo.[OPOR] T1, dbo.POR1 T2 WHERE  T2.DocEntry = T1.DocEntry AND  T1.CardCode in ('SF000004','SF000002') AND 
(IsNull (T2.U_SerialNo,'')in('') or ISNULL (T2.U_WtyCardNo,'')in('') or ISNULL (T1.U_BLNo,'')in('') or ISNULL (T1.U_ContNo,'')in('') or ISNULL (T1.U_TotalQty,'')in('') or ISNULL (T1.U_Region,'')in('') or ISNULL (T1.U_AdvPayment,'')in('') or ISNULL (T1.U_Consignor,'')in('') or ISNULL (T1.U_Consignee,'')in('')) and T2.DOCENTRY= @list_of_cols_val_tab_del)
BEGIN
SELECT @Error = 1, @error_message = 'SBO: PO013 - Incomplete Data For Import'
END
END
END

I need the above control to display the specific fields that are null when a user tries to ADD/UPDATE a Purchase Order in SAP instead of the error "PO013 - Incomplete Data For Import" being displayed.

Regards,

Isaac K.

Hi,

I think it can be done through by adding set of statement in above SP.

Please search in this forum for similar requirement.

Basically, you take out "

AND

(IsNull (T2.U_SerialNo,'')in('') or ISNULL (T2.U_WtyCardNo,'')in('') or ISNULL (T1.U_BLNo,'')in('') or ISNULL (T1.U_ContNo,'')in('') or ISNULL (T1.U_TotalQty,'')in('') or ISNULL (T1.U_Region,'')in('') or ISNULL (T1.U_AdvPayment,'')in('') or ISNULL (T1.U_Consignor,'')in('') or ISNULL (T1.U_Consignee,'')in(''))"

this part from your original IF.

Declare many variables to match with your UDFs, change the original IF to a select statement, you will need to select those UDFs into those variables.

And then you can create a lot of IF to check those variables, and give different error_message based on these series of IF statement result.

Frank

Hi Frank,

Thanks for your reply and your great idea. I have redefined the query and I'm sure it will display the messages required if indeed there are null values, but what condition would one put if the IF condition results into FALSE (i.e if user has filled the value fields) so that the PO can add (because it will still block regardless)?

Kindly assist further.

Regards,

Isaac K.

if all your if returns false, which means no null value found, at the end of TN, @error = 0 will lead the transaction successful.

Hi Frank,

This is the query which seems to be giving a syntax error; please have a look at it and kindly assist in refining it if possible.

Thanks.


IF @object_type= '22' AND @transaction_type in ('A','U') 
Begin
IF  EXISTS(SELECT T0.DocEntry FROM dbo.[@SBO_SP_NOTIFICATION] T0 where IsNull(T0.U_Active,'')in('Yes') and T0.U_QueryNo = 'PO013')  
BEGIN
declare @Type nvarchar (200)         -- Error string to be displayed
declare @Type1 nvarchar (200)
declare @Type2 nvarchar (200)
declare @Type3 nvarchar (200)
declare @Type4 int
declare @Type5 nvarchar (200)
declare @Type6 int
declare @Type7 nvarchar (200)
declare @Type8 nvarchar (200)
select @Type = (SELECT T1.U_SerialNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type1 = (SELECT T1.U_WtyCardNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type2 = (SELECT T4.U_BLNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type3 = (SELECT T4.U_ContNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type4 = (SELECT T4.U_TotalQty FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type5 = (SELECT T4.U_Region FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type6 = (SELECT T4.U_AdvPayment FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type7 = (SELECT T4.U_Consignor FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type8 = (SELECT T4.U_Consignee FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

IF @Type = ''
BEGIN
SELECT 'Seal Number is Blank'
end
else

IF @Type1 = ''
BEGIN
SELECT 'Container Number is Blank'
end
else

IF @Type2 = ''
BEGIN
SELECT 'BL No is Blank'
end
else

IF @Type3 = ''
BEGIN
SELECT 'Container Ref No is Blank'
end
else

IF @Type4 = 0
BEGIN
SELECT 'Total Sum is Blank'
end
else

IF @Type5 = ''
BEGIN
SELECT 'Region/Import From is Blank'
end
else

IF @Type6 = 0
BEGIN
SELECT '% Duty/Adv. Payment is Blank'
end
else

IF @Type7 = ''
BEGIN
SELECT 'Vessel Name is Blank'
end
else

IF @Type8 = ''
BEGIN
SELECT 'Item Group is Blank'
end
else

BEGIN
SELECT @Error = 0
END
END
END

Regards,

Isaac K.

IF EXISTS(SELECT T0.DocEntry FROM dbo.[@SBO_SP_NOTIFICATION] T0 where

what's this? You are giving the wrong table. it should be OPOR.

Frank

The table is a UDT that we have configured for ease of enabling and/or disabling the control. We can as well remove it so that we are only left with the below:

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

Begin

declare @Type nvarchar (200) -- Error string to be displayed

declare @Type1 nvarchar (200)

declare @Type2 nvarchar (200)

declare @Type3 nvarchar (200)

declare @Type4 int

declare @Type5 nvarchar (200)

declare @Type6 int

declare @Type7 nvarchar (200)

declare @Type8 nvarchar (200)

select @Type = (SELECT T1.U_SerialNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type1 = (SELECT T1.U_WtyCardNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type2 = (SELECT T4.U_BLNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type3 = (SELECT T4.U_ContNo FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type4 = (SELECT T4.U_TotalQty FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type5 = (SELECT T4.U_Region FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type6 = (SELECT T4.U_AdvPayment FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type7 = (SELECT T4.U_Consignor FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

select @Type8 = (SELECT T4.U_Consignee FROM POR1 T1 inner join [dbo].[OPOR] T4 on T1.DocEntry = T4.DocEntry where T4.CardCode in ('SF000004','SF000002') and T4.DOCENTRY = @list_of_cols_val_tab_del)

IF @Type = ''

BEGIN

SELECT 'Seal Number is Blank'

end

else

IF @Type1 = ''

BEGIN

SELECT 'Container Number is Blank'

end

else

IF @Type2 = ''

BEGIN

SELECT 'BL No is Blank'

end

else

IF @Type3 = ''

BEGIN

SELECT 'Container Ref No is Blank'

end

else

IF @Type4 = 0

BEGIN

SELECT 'Total Sum is Blank'

end

else

IF @Type5 = ''

BEGIN

SELECT 'Region/Import From is Blank'

end

else

IF @Type6 = 0

BEGIN

SELECT '% Duty/Adv. Payment is Blank'

end

else

IF @Type7 = ''

BEGIN

SELECT 'Vessel Name is Blank'

end

else

IF @Type8 = ''

BEGIN

SELECT 'Item Group is Blank'

end

else

BEGIN

SELECT @Error = 0

END

Ask a Question

Top Q&A Solution Author

User

Count

104