Can´t create projects because of transaction_notification

[former_member325743]

Hi everyone, recently I made some fields as mandatory with transaction_notification for the "Project Management" window.This one

This is my code:

----Proyectos
IF (@object_type = '234000021') AND (@transaction_type IN ('A','U'))
declare @proy nvarchar(20)
declare @territory nvarchar (100)
declare @employee nvarchar (100)
declare @duedate nvarchar(100)
declare @closedate nvarchar (100)
set @proy=(select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.FIPROJECT is null )
set @territory = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.territory is null)
set @employee = (select  EMPLOYEE from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del )
set @duedate = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.DUEDATE is null)
set @closedate = (select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.CLOSING is null) 
BEGIN
/**** Proyecto Financiero ****/
If  @proy != ''
set @error = 111

/**** Territorio ****/
if @territory != ''
set @error = 112
/**** Empleado de Ventas ****/
if @employee <= 0
set @error = 113
/**** Fechas de Vencimiento ****/
if @duedate !=''
set @error=114
/**** Fechas de Cierre ****/
if @duedate !=''
set @error=115
 SET @error_message=
   CASE @error
WHEN 111 THEN 'Falta asignar Proyecto financiero'
        WHEN 112 THEN 'El territorio no ha sido especificado'
    WHEN 113 THEN 'Asignar Empleado de Ventas'
WHEN 114 THEN 'Falta Fecha de Vencimiento y Cierre'
WHEN 115 THEN 'Falta Fecha de Vencimiento y Cierre'
          END
End 

The code is working well but when I try to do a new project, you know, from this window

This message appears

Now, when I comment these lines the error stop appearing

set @proy=(select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.FIPROJECT is null )
set @territory = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.territory is null)
set @employee = (select  EMPLOYEE from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del )
set @duedate = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.DUEDATE is null)
set @closedate = (select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.CLOSING is null) 

The lines are the ones that select the info to check if the fields are empty when creating a Project on the "Project Management" window (the one above).

So, It seems that something in those 'selects' are interfering with creating new projects but I don´t know what can be. If anyone has an idea, please tell me because I really need that blocking on those fields but also create new projects.

Thanks, best regards!

[former_member185682 · Accepted solution]

Hi Anderson,

The error occured because you have a little mistake in your transaction code. You placed your instruction BEGIN in wrong place. In this case you need set your BEGIN instruction after the if instruction, like below:

IF (@object_type = '234000021') AND (@transaction_type IN ('A','U'))
BEGIN
	declare @proy nvarchar(20)
	declare @territory nvarchar (100)
	declare @employee nvarchar (100)
	declare @duedate nvarchar(100)
	declare @closedate nvarchar (100)
	set @proy=(select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.FIPROJECT is null )
	set @territory = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.territory is null)
	set @employee = (select  EMPLOYEE from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del )
	set @duedate = (select   (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.DUEDATE is null)
	set @closedate = (select  (t0.CardCode) from OPMG T0  WHERE T0.AbsEntry=@list_of_cols_val_tab_del AND T0.CLOSING is null) 


	/**** Proyecto Financiero ****/
	If  @proy != ''
		set @error = 111


	/**** Territorio ****/
	if @territory != ''
		set @error = 112
	/**** Empleado de Ventas ****/
	if @employee <= 0
		set @error = 113
	/**** Fechas de Vencimiento ****/
	if @duedate !=''
		set @error=114
	/**** Fechas de Cierre ****/
	if @duedate !=''
		set @error=115
	SET @error_message=
    CASE @error
		WHEN 111 THEN 'Falta asignar Proyecto financiero'
        WHEN 112 THEN 'El territorio no ha sido especificado'
		WHEN 113 THEN 'Asignar Empleado de Ventas'
		WHEN 114 THEN 'Falta Fecha de Vencimiento y Cierre'
		WHEN 115 THEN 'Falta Fecha de Vencimiento y Cierre'
    END
END 

Hope it helps.

Kind Regards,

Diego Lother

[former_member325743 · Accepted solution]

I changed all of my code structure to be like yours, it seems like one thing was interfering with another one and so on.

But now everything works fine.

I lost the count of how many times have you helped me, Thanks again Diego!

[former_member185682 · Accepted solution]

Great.

I'm glad to help.

Kind Regards,

Diego Lother

[KennedyT21]

Hi Try this

IF (@object_type='234000021')
AND (@transaction_type IN ('A' ,'U'))
BEGIN
IF EXISTS(
SELECT (t0.CardCode)
FROM OPMG T0
WHERE T0.AbsEntry = @list_of_cols_val_tab_del
AND ISNULL(T0.FIPROJECT ,'') = ''
)
BEGIN
SET @error = -2
SET @error_message = 'Falta asignar Proyecto financiero'
END


IF EXISTS(
(
SELECT (t0.CardCode)
FROM OPMG T0
WHERE T0.AbsEntry = @list_of_cols_val_tab_del
AND T0.territory IS NULL
)
)
BEGIN
SET @error = -3
SET @error_message = 'El territorio no ha sido especificado'
END

IF EXISTS(
SELECT EMPLOYEE
FROM OPMG T0
WHERE T0.AbsEntry = @list_of_cols_val_tab_del
AND T0.EMPLOYEE IS NULL
)
BEGIN
SET @error = -4
SET @error_message = 'Asignar Empleado de Ventas'
END


IF EXISTS(
SELECT (t0.CardCode)
FROM OPMG T0
WHERE T0.AbsEntry = @list_of_cols_val_tab_del
AND T0.DUEDATE IS NULL
)
BEGIN
SET @error = -5
SET @error_message = 'Falta Fecha de Vencimiento y Cierre'
END

IF EXISTS(
SELECT (t0.CardCode)
FROM OPMG T0
WHERE T0.AbsEntry = @list_of_cols_val_tab_del
AND T0.CLOSING IS NULL
)
BEGIN
SET @error = -6
SET @error_message = 'Falta Fecha de Vencimiento y Cierre'
END
END

Cheers!

Kennedy

[former_member325743]

Nope 😕 still can´t make new projects. The code works fine but still interferes with making new projects.

[KennedyT21]

Can you post the error.. it works fine for me.

[former_member325743]

Is the one above

[KennedyT21]

I think you need to check other transcation notification in the DB... we have not used @p1 in the TNSP...