Modifying Transaction Notification Stored Procedure to Edit a Certain Row

[former_member609283]

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

[kothandaraman_nagarajan]

Hi,

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

Thanks & Regards,

Nagarajan

[former_member609283]

Hi Nagaa,

   Yes I know there is no any direct Link between AP and AR invoices, so I am creating a UDF say Flag and there are 3 fields which combine together is a unique value. So I want when I add the document in AR invoice it flagged the UDF in AP Invoice to 1 or 0 so that next time when FMS runs in AR invoice the values that flaaged 1 or 0 can be filtered out of the list.

Any input you can give is appreciated.

Regards,

Idrees

[kothandaraman_nagarajan]

Hi,

Not possible by FMS and Transaction notification.

Thanks & Regards,

Nagarajan

[csaba_fulopcsei2]

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

[former_member609283]

Hey Csaba,

   The above query does not fulfill my requirement as it shows all three entries in PCH1 as I have entered 3 entries in PCH1 and then I entered 1 entry in INV1 so next time when query runs it have to show 2 entries but it is showing 3 of them. Nothing change.

Regards,

Idrees

[csaba_fulopcsei2]

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