Hello,

Are there any standard fields available in Baan that will show when a record was added or last changed and by which user?

Thanks

PJ

You can get this information only via audit files.

Depending on the Table / Session used to add/ update the record, you may actually find it under that table or a history table associated with it.
sometimes custom tables are designed to record this in the background

if these are not present, then audit files are the places to look.

tdsls050 and tdpur050 are the only tables I've ever used with the user field on it.

Our previous system used to contain a user and date time stamp shown at the bottom of the screen on all static data. Something which the users miss due to the ability to quickly track any problems associated with static data issues to a department.

Now these issues have to be solved via audit which means IT always have to have an involvement.

Cheers
Neal

...for your comments.

I have found a solution.

In my table I will create some additional fields for 'Record Added' and 'Record Updated' and then have a trigger on the table which will update these fields. The only field I can not have is the one relating to the user as we have one Oracle user to many Baan users.

Thanks again.

Regards

PJ

=========
The only field I can not have is the one relating to the user as we have one Oracle user to many Baan users.
=====
what exactly do you mean by this?
if you have a new field say 'usrt' or any other name in your table,you can write a simple program to update that field everytime someone modifies a record.
the user name is present in 'logname$' at any time.

Pjohns is referring to using oracle triggers, all system users within baan generally have the same oracle username (depending on the implementation). But you can actually get the user id by retrieving the os user. I attached some samples from triggers we did on purchasing and item master. The triggers populate new custom tables (I prefer this to adding on to BaaN standard tables, it is a little cleaner).

CREATE OR REPLACE TRIGGER TTDPUR041100_UPDATE
BEFORE UPDATE ON TTDPUR041100
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
select osuser into username from v$session where audsid = (select userenv ('sessionid') from dual);
IF :old.T$ITEM <> :new.T$ITEM OR :old.T$OQUA <> :new.T$OQUA
OR :old.T$CWAR <> :new.T$CWAR OR :old.T$PRIC <> :new.T$PRIC OR :old.T$DDTA <> :new.T$DDTA THEN
insert into ttdpur941100 values ( :new.T$ORNO,:new.T$PONO,'UPD' , sysdate,username,:old.T$ITEM,:new.T$ITEM,:old.T$OQUA,:new.T$OQUA,:old.T$CWAR, :new.T$CWAR, :old.T$PRIC,:new.T$PRIC,:old.T$DDTA, :new.T$DDTA,0,0);
END IF;
END TTDPUR04100_UDPATE;

CREATE OR REPLACE TRIGGER TTDPUR041100_INSERT
BEFORE INSERT ON TTDPUR041100
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
select osuser into username from v$session where audsid = (select userenv ('sessionid') from dual);
insert into ttdpur941100 values ( :new.T$ORNO,:new.T$PONO,'ADD' , sysdate,username,:new.T$ITEM,:new.T$ITEM,:new.T$OQUA,:new.T$OQUA,:new.T$CWAR, :new.T$CWAR, :new.T$PRIC,:new.T$PRIC,:new.T$DDTA, :new.T$DDTA,0,0);
END TTDPUR04100_INSERT;



CREATE OR REPLACE TRIGGER TTDPUR041100_DELETE
BEFORE DELETE ON TTDPUR041100
FOR EACH ROW
DECLARE
username varchar2(15);
BEGIN
select osuser into username from v$session where audsid = (select userenv ('sessionid') from dual);
insert into ttdpur941100 values ( :old.T$ORNO,:old.T$PONO,'DEL' , sysdate,username,:old.T$ITEM,:old.T$ITEM,:old.T$OQUA,:old.T$OQUA,:old.T$CWAR, :old.T$CWAR, :old.T$PRIC,:old.T$PRIC,:old.T$DDTA, :old.T$DDTA,0,0);
END TTDPUR04100_DELETE;

Spot on nneilitz!

This is exactly what I want!

Thank you

PJ

Notes
You will need to add access to the V$SESSION table to the standard baan user for this to work.

The modify function, along with the table definition of the custom audit table will need to be changed according to what fields you want to audit changes (in our case we are only auditing 3). In any case the field order must match the custom table. (in this case tdpur941)