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

Sample Query for using in Tranaction Notification SP

Hi,

Does anybody know how to write a query in Transaction Notification SP to control the value of a field based on a dynamic list of data?

This request is different from using linked table in UDFs definition.

For example, I need to control the "account code" field based on a formatted query list that I set in "Goods Issue" document and I want to block adding document how users can not enter other accounts out of the list.

Please consider that the list is based on a query and it is dynamic and when "IN" function is not usable.

Thank you

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Sep 05, 2013 at 05:49 AM

    Hi,

    You can use below script

    declare @QueryStr nvarchar(max)
    select @QueryStr=QString from OUQR where QName = 'name of your defined query'
    create table #tempAct(accountCode nvarchar(60))
    insert into #tempAct
    exec sp_executesql @QueryStr

    select * from #tempAct

    Then #tempAct contains all account code which is valid. You can do any check based on it.

    But your defined query can't any contain parameter , such as [%0] or value from form such as $[$4.0.0]

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 05, 2013 at 05:51 AM

    Hi,

    Try this:

        • IF @object_type = '60' and @transaction_type in ('A')

        • Begin

        • If exists

        • (Select t0.docentry FROM OIGN T0 INNER JOIN IGN1 T1 ON
          T0.DocEntry = T1.DocEntry

        • where t0.docentry = @list_of_cols_val_tab_del

        • and T1.[AcctCode] <> '10001-000' )

        • Select @error =1,

        • @error_message = 'Check account Code '

        • End

        • End

        Note : Add your account code in above SP.

        Thanks & Regards,

        Nagarajan

        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.