SP_TRANSACTION NOTIFICATION NOT FIRING

[former_member218051]

Hi All,

I've written a SP TRANSACTION NOTOFICATION.

ON pressing F5 SQL server 2008 is giving COMMAND(S) COMPLETELY SUCCESSFULLY.

BUT IN FRONT END NO RESULT.

I TRIED SQL PROFILER BUT THAT PROCEDURE IS NOT VISIBLE THERE.

Any help about calling it would be greatly appreciated.

Thanks

Malhaar

[former_member204969 · Accepted solution]

The system executes the SBO_SP_TransactionNotification procedures at the end of the transactions! So you should insert your code into this procedure to check the transaction!

(Or call your own procedure from this one.)

Edited by: István Körös on Jun 6, 2011 12:48 PM

[former_member218051 · Accepted solution]

Hi ,

I inserted above code into SBO_SP_TRANSACTIONNOTIFCATION and syste mthrew an error

SBO_SP_TRANSACTIONNOTIFICATION has too many arguements

below is my code

USE [Audio_Test]

GO

/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 06/07/2011 11:06:27 ******/

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

-

---

-- Select the return values

IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))

BEGIN

IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum != 4 or T0.GroupCode != 116) and T0.UserSign = 1 and T0.CardCode =@list_of_cols_val_tab_del)

Begin

SET @error = 30

SET @error_message =N'You are not authorized to change payment terms and customer group'

End

END

-- select @error,@error_message

end

[Former Member · Accepted solution]

Hi Malhaar....

Forget Your code and just Run this code as it is.......

set ANSI_NULLS ON
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
IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))
BEGIN
IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum != 4 And T0.GroupCode != 116) and T0.UserSign = 16 and T0.CardCode =@list_of_cols_val_tab_del)
Begin
Select @error = -1,
@error_message ='You are not authorized to change payment terms and customer group'
End
END



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

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

end

Regards,

Rahul

[former_member218051 · Accepted solution]

Hi Rahul,

Many many thanks to all of you who helped me in every sense.

The procedure worked perfectly.

By the way apart from commenting few variables the procedure is same.

What was wrong with my code ?

Thanks

Malhaar

[former_member204969 · Accepted solution]

Theses variables define the parameters for the procedure. The system sets these parameters when activates the procedure, so you should not delete them.

[Former Member]

Hi Malhaar,

You can only test SP when you enter some data in front end. What is the goal of this SP? What do you want to block?

Thanks,

Gordon

[former_member218051]

Hi Gordon,

One particular user should select payment term as 'ADVANCE PAYMENT' and group as 'SERVICE CUSTOMER' only while adding a business partner.

but when i added a business partner with payment term and group other than mentioned above, the BP got added without any error message.

I'm stuck as to how to make this call.

Thanks

Malhaar

[Former Member]

Hi Malhar.....

Your SP might be correct but somewhere condition is going false.

So can you please paste your code here so that we can modify?....

Regards,

Rahul

[former_member218051]

H Riahul,

Here it is

CREATE proc [dbo].[SBO_SP_RESTRICTBP]

@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

-

---

-- Select the return values

if (@object_type = '13') and (@transaction_type IN ('A', 'U'))

BEGIN

IF exists (select T0.GroupNum FROM OCRD T0 Where T0.GroupNum != 4 and T0.GroupCode != 116 and T0.UserSign = 16 and T0.CardCode =@list_of_cols_val_tab_del)

Begin

SET @error = 30

SET @error_message =N'You are not authorized to change payment terms and customer group'

end

END

end

GO

[Former Member]

Hi Malhaar.....

I think Object ID you have taken is wrong for OCRD.

It should be 2 in instead of 13.

Please replace that and then try to add Business Partner.....

Regards,

Rahul

[former_member218051]

Hi Rahul,

You are write. 13 is for invoice.

I tried with two still no results.

Thanks

Malhaar

[Former Member]

Hi,

You may try:

IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))

BEGIN

IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum != 4 or T0.GroupCode != 116) and T0.UserSign = 16 and T0.CardCode =@list_of_cols_val_tab_del)

Begin

SET @error = 30

SET @error_message =N'You are not authorized to change payment terms and customer group'

End

END

Thanks,

Gordon

[Former Member]

Hi Try this........

IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))
BEGIN
IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum <> 4 And T0.GroupCode <> 116) and T0.UserSign = 16 and T0.CardCode =@list_of_cols_val_tab_del)
Begin
Select @error = -1,
@error_message ='You are not authorized to change payment terms and customer group'
End
END

Regards,

Rahul

[former_member218051]

Hi Rahul,

Thanks for the prompt modified code. It is giving me error "INCORRECT SYNTAX NEAR 4".

Thanks

Malhaar

[Former Member]

Hi ........

Try this......

IF (@object_type = '2') and (@transaction_type IN ('A', 'U'))
BEGIN
IF exists (select T0.CardCode FROM OCRD T0 Where (T0.GroupNum != 4 And T0.GroupCode != 116) and T0.UserSign = 16 and T0.CardCode =@list_of_cols_val_tab_del)
Begin
Select @error = -1,
@error_message ='You are not authorized to change payment terms and customer group'
End
END

Regards,

Rahul