Change Log for user defined tables(with out UDO)

[Former Member]

Hi,

I am customizing some screen were i am not using UDO.I want to maintain change log for the user screens. how can i do that. pls help me out in that.

Thanks,

OmPrakash.

[Former Member · Accepted solution]

Hi Om,

In SAP B1, when ever a document or a master data is updated the history is stored in different tables depending on the Document that is updated EX: If u update BP Master the log data is stored in ACRD table, if u take the invoices its stored in the ADOC table...

So i advise u have a UDT in which u store similar information and display on the menu CLick of Change LOG.

Hope this info helps u..

Vasu Natari.

[Former Member]

Hey Andrei Surujiu,

In the first method you've showed us (using the SBO_SP_TransactionNotification), we only get the object's name and key name/value - but we do not get the field name or value that was updated/added. Is there a way to get that information as well?

[Former Member]

Hi

To see what has added, changed, deleted, ... at every transaction save data in this table using SBO_SP_TransactionNotification procedure.

1) Create an NO OBJECT table @userlog, for example, with this fields:

-

---

Code, -- SAP field

Name, -- SAP field

-- User Defined Fields

EntityName VARCHAR(20) NOT NULL,

UserName VARCHAR(20) NOT NULL,

DateTimeIns DATETIME NOT NULL,

Operation CHAR(1) NOT NULL,

WhatChangedLog CHAR(4096)

-

---

Add UNIQUE INDEX on @userlog (Code, EntityName, UserName, DateTimeIns)

*To autoincrement Code make GetNextCode() function.

2) Change SAP procedure SBO_SP_TransactionNotification(...) to save data in your log table [@userlog].

3) Set some permition for table (manager only).

Best regards

Sierdna S.

[Former Member]

Hi Sierdna S ,

I did not understand the solution properly.can you pls explain and is it possible.can you give some document or clear example.

thanks,

OmPrakash

[Former Member]

Hi Om Prakash Akarapu.

Case 1: using of SBO_SP_TransactionNotification procedure:

-

---

I know what on SAP database is present standard SAP procedure [SBO_SP_TransactionNotification]:

CREATE 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
select @error = 0
select @error_message = N'Ok'
--------------------------------------------------------------------------------------------------------------------------------
--	ADD	YOUR	CODE	HERE
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select @error, @error_message
end

You can create the table to log all transactions.

Modify procedure with this insert stmt (use some filter to have only what you need, for example only on your table).

INSERT INTO [SBO-Common].[dbo].[@O99_DBOPERATIONS] (
  DBName, ObjectType, TransactionType, NumOfColsInKey, ListOfKeyCols, ListOfColsVal, TimeOperation
) VALUES (
  db_name(0), @object_type, @transaction_type, @num_of_cols_in_key, @list_of_key_cols_tab_del, @list_of_cols_val_tab_del, getdate()
)

Case 2: Using of triggers (insert, update, delete) on your table.

-

---

You have 2 tables:

- O99_TableA, what contain data;

- O99_TableA_LOG what contain history or log of changes in TableA.

O99_TableA

CREATE TABLE [dbo].[@099_TableA]
(
	[Code] [nvarchar](8) NOT NULL, ' SAP Field
	[Name] [nvarchar](30) NOT NULL,  ' SAP Field
	[U_BPCod] [nvarchar](15) NOT NULL,
	[U_ItemCode] [nvarchar](20) NOT NULL
	[U_Price] [numeric](19, 6) NOT NULL,
	[U_Note] [ntext]
  CONSTRAINT [K099_TableA_PR] PRIMARY KEY 
  (
	[Code] ASC
  )
) 

O99_TableA_LOG

CREATE TABLE [dbo].[@099_TableA_LOG]
(
	[Code] [nvarchar](8) NOT NULL, ' SAP Field
	[U_BPCod] [nvarchar](15) NOT NULL,
	[U_ItemCode] [nvarchar](20) NOT NULL
	[U_DTMod] [datetime],
	[U_Price] [numeric](19, 6) NOT NULL,
	[U_Note] [ntext]
  CONSTRAINT [K099_TableA_LOG_PR] PRIMARY KEY 
  (
        [Code],
	[U_BPCod],
        [U_ItemCode],
        [U_DTMod]
  )
) 

Add triggers to table [@099_TableA] what create storical changes on @O99_TableA.

Change only table to retrive data:

- insert ( select from inserted 😞

CREATE TRIGGER O99_TABLEA_INS
   ON [@O99_TableA]
   AFTER INSERT --,DELETE,UPDATE
AS
BEGIN
  DECLARE @Code		varchar(8)
  DECLARE @Name		varchar(30)
  DECLARE @BPCode	varchar(15)
  DECLARE @ItemCode	varchar(20)
  DECLARE @DateTimeMod	datetime
  DECLARE @Price	decimal(19, 6)

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for trigger here
  DECLARE curs_ CURSOR LOCAL FOR
   SELECT [Code],
	  [U_BPCod],
	  [U_ItemCode],
	  [U_Price]
     FROM inserted
  OPEN curs_
  FETCH NEXT FROM curs_
  INTO @Code,
       @BPCode,
       @ItemCode,
       @DateTimeMod,
       @Price
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @DateTimeMod = GetDate()
    INSERT INTO [@O99_TableA_LOG]
    (
	[Code], [U_BPCod], [U_ItemCode], [U_DTMod], [U_Price]
    ) VALUES (
      @Code, @BPCode, @ItemCode, @DateTimeMod, @Price
    )
    FETCH NEXT FROM curs_ INTO @Code, @BPCode, @ItemCode, @DateTimeMod, @Price
  END
  CLOSE curs_
  DEALLOCATE curs_

-- TRIGGER O99_TABLEA_INS
END
GO

- update ( select from inserted );

- delete ( select from deleted ).

HTH

Regards

Sierdna S.

Edited by: Sierdna S on Sep 30, 2008 12:17 PM

[Former Member]

Hi Sierdna,

You mean to say that we need to insert in to user table and log table when user screen is updated.

I am doing the same thing by coding.

But my problem is if in my screen there are 25 fileds.and if i update two fileds then i need to show only the two changed fields ,their old values and new values.is this functionality possible.?. Please confirm me.

Thanks,

OmPrakash.

[Former Member]

I has used 2° method.

In my form I have 2 sincronized matrixes:

- 1° visualize data TableA;

- 2° visualize data of TableA_LOG.

When I select row in 1-st matrix I see log data from TableA_LOG.

You can call other form using button, for example "Show Log", and open second form. It's may be solution.

Edited by: Sierdna S on Sep 30, 2008 12:40 PM

[Former Member]

detailed change log is not captured

[Nussi]

Hi,

i would try to make a own interface to insert/delete the data in the udt.

than you move the old data to another table and save the new data to the main udt.

lg David