Skip to Content
Author's profile photo Kennedy Thomas

Transaction Notification Samples

An example of SP that must be filled or input by user before adding a document :

USe [DBName]

Go

/****** Object:  StoredProcedure [dbo].[SBO_SP_TransactionNotification]    Script Date:  20120721 Kennedy******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[SBO_SP_TransactionNotification]

@object_type nvarchar(20),                 — SBO Object Type

@transaction_type nchar(1),            — [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

@num_of_cols_in_key int,

@list_of_key_cols_tab_del nvarchar(255),

@list_of_cols_val_tab_del nvarchar(255)

AS

begin

— Return values

declare @error  int                — Result (0 for no error)

declare @error_message nvarchar (200)         — Error string to be displayed

declare @dno nvarchar (200)

select @error = 0

select @error_message = N’Ok’

——————————————————————————————————————————–

/* Start */

if @transaction_type in (‘A’, ‘U’)

and @object_type in (’23’,’17’,’15’,’16’,’203′,’13’,’14’)

begin

    if exists(

        –Sales Quotation

        select DocEntry

        from OQUT with (nolock)

        where @object_type = ’23’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –SO

        select DocEntry

        from ORDR with (nolock)

        where @object_type = ’17’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –DO

        select DocEntry

        from ODLN with (nolock)

        where @object_type = ’15’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –Return

        select DocEntry

        from ORDN with (nolock)

        where @object_type = ’16’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –A/R DP

        select DocEntry

        from ODPI with (nolock)

        where @object_type = ‘203’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –A/R Invoice

        select DocEntry

        from OINV with (nolock)

        where @object_type = ’13’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

        union all

        –A/R Credit Memo

        select DocEntry

        from ORIN with (nolock)

        where @object_type = ’14’

            and @list_of_key_cols_tab_del = N’DocEntry’

            and DocEntry = @list_of_cols_val_tab_del

            and isnull(U_SO_TRANS_TYPE,”) = ”

    )

    begin

        set @error = -1

        set @error_message = ‘SO Transaction Type must be filled’

    end

end

/* End */

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Prasanna s
      Prasanna s

      Hi Kennedy,

      Can you give me an example in which situation we have to use this SP. So that I can make use of it..

      Author's profile photo Kennedy T
      Kennedy T
      Blog Post Author

      If you required to make a field or UDF to be mandatory for the End user you can try this

      Rgds

      Kennedy

      Author's profile photo Former Member
      Former Member

      Hi Kennedy,

      I have two question:

      1) Is it possible to use SP just for warning and no blocking action?

      2) How can mandatory the value of a field to fill just based on a dynamic list of data?

      Would you please help me?

      thank you

      Author's profile photo Balaji Sampath
      Balaji Sampath

      Hi

      This SP is useful

      Thanks

      Author's profile photo Michael Atienza
      Michael Atienza

      Hi expert,

      i need help for this Transaction notification, all i want is the button add in a/p invoice if the user click this button, the docstatus on my RFP ad-on change to Close,

       

       

      IF @object_type =’13’AND @transaction_type IN(‘A’)

      BEGIN

      DECLARE @State varchar(32),@DocNum varchar (32)
      SELECT @State =DocStatus ,@DocNum =U_APP_DocNum FROM [OPCH]
      IF @State =’1′
      UPDATE [@APP_ORFP] SET U_APP_DocStatus =’2′ WHERE U_APP_DocNum =@DocNum
      END

      thankx

      Author's profile photo Former Member
      Former Member

      Hello,

      What condition should I need to use to trigger my script only on normal document adding or updating but not on adding cancellation document?

       

      Help is much appreciated.