SBO_SP_TransactionNotification and few items on the invoice

[Former Member]

Hello there!

I'm beginner in SAP B1 2004C and noticed situation that have never experienced before.

There are some lines of code in SBO_SP_TransactionNotification procedure made by me in order to check some dates written by user on the invoice, ex. right numeric series (for header) or prices under cost (for items).

When user finishes invoice with one item only everything works fine but if he enter more than one item SBO_SP_TransactionNotification acts as doesn't work for @object_type = 13.

Why?

I used SQL Profiler and it looks that SBO_SP_TransactionNotification runs then, set @error = 1 and @error_message = N'Wrong numeric series!' but it doesn't appear on the bottom of screen.

Can anyone help me solve it?

This situation happens only on warehouse documents with more than one item where @object_type = 58 is working also.

[Gianluigi]

Maciej -

you could consider using DI Event Server instead of

accessing directly the Transaction Notification. You

can find DI Event Server in SDN under "SAP Business One

SDK tools".

It gives you an easier interface to events. You can

install it (the installation procedure is described

in the documentation) and you have access to the source

code as well.

Gianluigi

[Former Member]

Gianluigi,

Thank you, I'm just reading about it.

Please remember that I'm beginner in SAP B1 technology and if someone's find out what is wrong with SBO_SP_ I'm still waiting for help.

All the best!

[Former Member]

Hi,

Just a hint. Could be that any of the select statement return a null value? If this happens, the @error would not get the value, and no error would be displayed (though it should in your opinion).

Hope helps,

Ibai Peñ

[Former Member]

Hello Ibai,

It's impossible because @error variable gets zero value at the begining of SBO_SP_TransactionNotification and in case of IF section work, it gets one value.

The code you could see above is just sector of the procedure.

[Former Member]

Hi again,

You didn´t say why it is impossible.

In this statement:

SET @Comments = ( SELECT Remark FROM NNM1 WHERE ObjectCode = 13 AND

Series = ( SELECT Series FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) ) --zastosowanie dok.

If there isn´t any Remark on the result of the select, the @Comments variable will have NULL value. And then, when the If statement bellow is executed:

IF ( @GroupCode = 107 AND @Comments != 'InvType1' ) OR ( @GroupCode = 108 AND @Comments != 'InvType2' )

This will not be true, (NULL = anything --> NULL) it will be Null. And then the @error won´t get changed, thought you think it should.

To test this easyly, create a dummy table, and insert the @Comments and the @Groupcode values on it before the if statement.

Hope helps,

Ibai Peñ

[Former Member]

I did think you wrote about @error variable only.

Every field in NNM1 in the area which is affected by query is fulfill, so cannot has NULL value.

[Former Member]

I have noticed that if there is any runtime error that can occur in SBO_SP_TransactionNotification, it will never be reported and the whole procedure/error will be ignored, so you have to be very careful in writing code for SBO_SP_TransactionNotification that you do not cause any errors. You also have to be very careful not to return any rows other than the one that returns the error code. So if you have any select statements the don't output all their results to variables or temp tables, you will have to eliminate/change those, and you should also double check the procedure to see that it can run without errors in all cases.

[Former Member]

What is wrong if I use statement in IF without variable, ex.

IF ( SELECT DocNum FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) = 250

??

[AlexGrebennikov]

Ok, Maciej, could you catch the SBO_SP_TransactionNotification call with all the parameters using SQL Profiler and run it from Query Analizer? Will be there any error messages?

[Former Member]

Hi,

Try to use:

@object_type = N'13'

In 6.5 version this variable was numeric but in 2004 change to nvarchar.

Best Regards.

[Former Member]

I've tried it for available ways but it still doesn't work.

[former_member185703]

Well, it is difficult to find an error in something we don't see...

Since it apparently works, if there's just one item in the document lines I would expect the error to be somewhere in your SQL code...

Could you post (at least) the section where you expect the error?

[Former Member]

Here you have code I use:

DECLARE @GroupCode smallint

DECLARE @Comments nvarchar(254)

IF @object_type = '13'

BEGIN

IF @transaction_type = 'A'

BEGIN

--check for the right numeric series

SET @Comments = ( SELECT Remark FROM NNM1 WHERE ObjectCode = 13 AND

Series = ( SELECT Series FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) ) --zastosowanie dok.

SET @GroupCode = ( SELECT GroupCode FROM OCRD WHERE CardCode = ( SELECT CardCode FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) )

IF ( @GroupCode = 107 AND @Comments != 'InvType1' ) OR ( @GroupCode = 108 AND @Comments != 'InvType2' )

BEGIN

SELECT @error = 1

SELECT @error_message = N'Wrong numeric series for choosen customer.'

END

END

END

[Former Member]

Here you have code I use:

DECLARE @GroupCode smallint

DECLARE @Comments nvarchar(254)

IF @object_type = '13'

BEGIN

IF @transaction_type = 'A'

BEGIN

--check for the right numeric series

SET @Comments = ( SELECT Remark FROM NNM1 WHERE ObjectCode = 13 AND

Series = ( SELECT Series FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) ) --zastosowanie dok.

SET @GroupCode = ( SELECT GroupCode FROM OCRD WHERE CardCode = ( SELECT CardCode FROM OINV WHERE DocEntry = @list_of_cols_val_tab_del ) )

IF ( @GroupCode = 107 AND @Comments != 'InvType1' ) OR ( @GroupCode = 108 AND @Comments != 'InvType2' )

BEGIN

SELECT @error = 1

SELECT @error_message = N'Wrong numeric series for choosen customer.'

END

END

END

[AlexGrebennikov]

Hi Maciej!

Try to use ISNULL function and Unicode strings. That should help you:

IF ( @GroupCode = 107 AND <b>ISNULL</b>(@Comments, '') != <b>N</b>'InvType1' ) OR ( @GroupCode = 108 AND <b>ISNULL</b>(@Comments, '') != <b>N</b>'InvType2' )

@Frank: the problem mentioned by Ben Marty is too important for all of us. I suppose the trouble is in B1 Client core, more specifically - in C++ recordset exceptions handling. Frank, could you ask RnD to comment it?

Thanx

[Former Member]

I'v tried it before.... still doesn't work

Please note that SQL code is not wrong - it works when there is one item only on the invoice!

[AlexGrebennikov]

Frank, could you please answer something?

>> the problem mentioned by Ben Marty is too important

>> for all of us. I suppose the trouble is in B1 Client core,

>> more specifically - in C++ recordset exceptions

>> handling. Frank, could you ask RnD to comment it?