Skip to Content

Modifying Transaction Notification Stored Procedure to Edit a Certain Row

Hello Everyone,

I have came across a situation that I have two UDFs in AP Invoice PCH1 named "UDF1" and "UDF2", UDF1 having set of values in drop down called "Value1" and "Value2" and UDF2 is like Flag.

Now when I entered the document AR Invoice I have a UDF that have FMS, using this FMS the list of UDF1 and UDF2 that are in PCH1 is populated on some condition. Now what I want is that if I have entered one entry in AR Invoice that should be flagged "1" in PCH1, so that next time when I populate the FMS it will only show me those entries whose flag is "0". In short I want if One entry is added in AR invoice document once it will vanish or exclude from the FMS List next time.

So what would be the Procedure to be added in Transaction Notification so that on the click of Add or Update button I can Flagged the relative entries as 0 or 1.

Thanks in advance

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on May 31, 2014 at 12:13 PM

    Hi,

    I don't believe you need a TNSP script in this case.

    You can write the FMS to exclude that values what are contained by INV1 lines.

    like this:

    SELECT T0.U_UDF1, T0.U_UDF2 FROM PCH1 T0 LEFT JOIN INV1 T1 ON T0.U_UDF1 = T1.U_UDF1 WHERE T1.DocEntry is null

    Regards,

    Csaba

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Idress,

      It is possible, but not supported by SAP.

      Create the following store procedure:

      CREATE PROCEDURE SP_CLOSE_PCH1_LINES (
        @list_of_cols_val_tab_del nvarchar(254)
      ) AS 
      BEGIN
        DECLARE @PCH_DE INT
        DECLARE @PCH_LN INT
        DECLARE @Value nvarchar(254)
      
        DECLARE LineCursor CURSOR FOR 
        SELECT U_UDF1 FROM [INV1] WHERE ISNULL(U_UDF1,N'') <> N'' AND DocEntry = @list_of_cols_val_tab_del
      
        OPEN LineCursor
        FETCH NEXT FROM LineCursor
        INTO @Value
      
      
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SELECT @PCH_DE = null, @PCH_LN = null
      
        SELECT TOP 1
        @PCH_DE = DocEntry, @PCH_LN = LineNum 
        FROM PCH1
        WHERE isnull(U_UDF2,0) = 0 AND ISNULL(U_UDF1,N'') = @Value
      
      
        IF (@PCH_DE is not null) AND (@PCH_LN is not null)
        UPDATE PCH1
        SET U_UDF2 = 1
        WHERE DocEntry = @PCH_DE AND LineNum = @PCH_LN
      
        FETCH NEXT FROM LineCursor
        INTO @Value
        END
        CLOSE LineCursor
        DEALLOCATE LineCursor
      END
      

      and insert the following lines into the SBO_SP_TransactionNotification:

      IF (@object_type = N'18') AND (@transaction_type IN (N'A')) 
        EXEC SP_CLOSE_PCH1_LINES @list_of_cols_val_tab_del
      

      Regards,

      Csaba

  • Posted on Jun 03, 2014 at 01:06 AM

    Hi,

    How do you link AP & AR invoice? There is no direct link between these two invoices.

    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.