Skip to Content
0
May 03, 2013 at 11:07 AM

Transaction notification, comparing Project field between Purchase Order and Good Receipt

73 Views

Hello everyone

I am trying to make a query for TN which detects when a user is trying to create a GR with a different Project than the one updated in the reference PO and shows an error message to the user not allowing him to create the document. This is the code that I have been able to make so far by my self (I have to explain that I am just a newbie in SQL and also in SAP B1):

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

BEGIN

IF(( SELECT COUNT(1) from [PDN1] T0 INNER JOIN [OPDN] T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN POR1 T2 ON T2.[DocEntry] = T0.[BaseEntry] AND T2.[BaseType] = '20' AND T2.[LineNum] = T0.[BaseLine] LEFT JOIN OPOR T3 ON T2.[Docentry] = T3.[DocEntry] WHERE T0.[DocEntry] = @list_of_cols_val_tab_del AND (T0.[Project] <> T2.[Project]))!=0)

BEGIN

SELECT @error = 1

SELECT @error_message = 'Mensaje sistema!: No se puede indicar un proyecto distinto al del pedido de referencia'

END

END

Activating this code on SQL Server Management Studio has no effect at all in SAP B1, as system still allows to create GRs with a different project than the one on the reference PO. Then I thought that the problem was on this part of the code:

(T0.[Project] <> T2.[Project]))!=0

So I wrote !=1 instead. By activating it and trying again system prevented me from creating the GR displaying the error message that I had set in the query. But by indicating the same project than the one in the PO the error was still being displayed.

In brief: by indicating !=0 in the query system doesn't detect nothing and by indicating !=1 system blocks all GRs no matter if they have the same project than the PO.

Do somebody know what am I missing?

Thanks in advance and regards