Conditions in SP Trans Notif

[Former Member]

Hi All,

I have a SP which blocks the addition of AR Invoice based on values in two udf:

if @transaction_type = 'A' and @object_type = '13'

begin

if exists(select T.docentry from OINV T inner join nnm1 P on T.Series = P.Series where T.docentry = @list_of_cols_val_tab_del and

T.U_InvType = 'T' and T.U_City = 'Hyderabad' and p.SeriesName not like '%APTI%')

begin

SET @error = 17

SET @error_message = N'Please select the right series for Tax Type Invoice!! '

end

end

Here, I have different values in City and I want to update it in a single SP, is it possible or do we have to create multiple sp's??

Ex. T.U_InvType = 'T' and T.U_City = 'Cochin' and p.SeriesName not like '%KETI%'  in this case, if the city is Cochin, then the series should be KETI%.

Thanks,

Joseph

[Former Member · Accepted solution]

Hi Joseph

You can try the following:

AND ((T.U_InvType = 'T' AND T.U_City = 'Hyderabad' AND P.SeriesName NOT LIKE '%APTI%')

OR (T.U_InvType = 'T' AND T.U_City = 'Cochin' AND P.SeriesName NOT LIKE '%KEIT%')

OR (......))

Kind regards

Peter Juby

[Former Member · Accepted solution]

Hi Peter,

I have made the following changes but its blocking the addition of AR Invoice inspite of the right series selected.

if @transaction_type = 'A' and @object_type = '13'

begin

if exists(select T.docentry from OINV T inner join nnm1 P on T.Series = P.Series where T.docentry = @list_of_cols_val_tab_del and

(T.U_InvType = 'T' AND T.U_City = 'Hyderabad' AND P.SeriesName NOT LIKE 'APTI%')

OR

(T.U_InvType = 'T' AND T.U_City = 'Cochin' AND P.SeriesName NOT LIKE 'KETI%'))

begin

SET @error = 17

SET @error_message = N'Please select the right series for Tax Type Invoice!! '

end

end

Thanks,

Joseph

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try above code with following query (tested for approval procedure).

SELECT T0.docentry FROM OINV T0  INNER JOIN NNM1 T1 ON T0.Series = T1.Series WHERE T0.[U_InvType] = 't' and  T0.[U_City] = 'Hyderabad' or T0.[U_City] = 'Cochin'  and  T1.[SeriesName] not Like '%%manu%%' or T1.[SeriesName] not Like '%%AR%%'

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Joseph,

Try:

if @transaction_type = 'A' and @object_type = '13'

BEGIN

if exists (select T.docentry from OINV T inner join nnm1 P on T.Series = P.Series where

T.docentry = @list_of_cols_val_tab_del and

T.U_InvType = 'T' AND ((T.U_City = 'Hyderabad' AND P.SeriesName NOT LIKE 'APTI%') OR

(T.U_City = 'Cochin' AND P.SeriesName NOT LIKE 'KETI%')))

Begin

SET @error = 13

SET @error_message = N'Please select the right series for Tax Type Invoice!! '

End

END

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon,

This works!!!

Thanks,

Joseph

[Former Member]

Hi Kamlesh & Taruna,

I have tried the SP's provided by you guys but it doesnt work.

Any other suggestions?

Thanks,

Joseph

[kothandaraman_nagarajan]

Hi Joseph,

If you wish, I will check it through team viewer. If OK, send ID and password.

Thanks & Regards,

Nagarajan

[former_member227598]

Hi Joseph ,

Try this below SP

if @transaction_type = 'A' and @object_type = '13'

begin

if exists(select T.docentry from OINV T inner join nnm1 P on T.Series = P.Series

where T.docentry = @list_of_cols_val_tab_del and

(T0.[U_InvType] = 't') and ( T0.[U_City] = 'Hyderabad' or T0.[U_City] = 'Cochin' ) and ( T1.[SeriesName] not Like 'APTI%%' or T1.[SeriesName] not Like 'KETI%'))

begin

SET @error = 10

SET @error_message = N 'Please select the right series for Tax Type Invoice!! '

end

end

Regards

Kamlesh Naware

[Former Member]

Hi ,

Try This

if @transaction_type = 'A' and @object_type = '13'

begin

Declare @city as Varchar(50)

Declare @Type as Varchar(50)

Select @city=u_city from OINV t0 where T0.docentry = @list_of_cols_val_tab_del

Select @Type=T0.U_InvType from OINV t0 where T0.docentry = @list_of_cols_val_tab_del

if exists(select T.docentry from OINV T inner join nnm1 P on T.Series = P.Series

where T.docentry = @list_of_cols_val_tab_del and

(T0.[U_InvType] = @Type) and ( T0.[U_City] = @City ) and ( T1.[SeriesName] not Like 'APTI%%' or T1.[SeriesName] not Like 'KETI%'))

begin

SET @error = 10

SET @error_message = N 'Please select the right series for Tax Type Invoice!! '

end

end

[Former Member]

Hi Joseph ,

From where are you picking values of U_City .

U can pass variable like

Declare @city Nvarchar

Select @City=U_city from your table name and where you are picking values ???

and pass the same in your SP.

Thanks

TAruna