Skip to Content

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 */

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Kayvan Mohitmafi

    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

    (0) 
  2. 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

    (0) 

Leave a Reply