Skip to Content
author's profile photo Former Member
Former Member

Conditions in SP Trans Notif

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 02, 2014 at 07:04 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 02, 2014 at 07:07 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 02, 2014 at 11:15 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jun 02, 2014 at 01:14 PM

    Hi Kamlesh & Taruna,

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

    Any other suggestions?

    Thanks,

    Joseph

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.