Audit trail for Item Branch

Hi List,

Our company setup is very weird, whereby our Item Master & Branch are created/editted by Europe office. Local offices only edit the Item Branch.

But someone(we dont know who) keeps changing our Item Branch stocking type to OBSOLETE status. And when we ask around, nobody admits to making such changes

The 5 audit fields in F4102 do not help, as they will be override by subsequent user transactions. Is there any way to track the changes, aside from making customizations? My company has forbidden customizations due to freeze of IT investment.

Thanks for your input

World A7.3

You might consider adding a trigger to the file. then any add/change/delete can be captured. if you want I can give you some examples on how to do that. Let me know.

you might consider adding a trigger to f4102, that will show all add/chnage/delete actions with date/time stamp. Let me know if you need help on this

You didn't mention your cume level, but if memory serves, DBAM (Database Audit Manager) is available on A73 (not sure at which cume level it can be used). You can set up audting for any files in which you are interested in tracking.

First of all, I know I am straying. I saw this post on the main screen and took a look. I also know that I don't know how/if this would work in World, but I am going to throw it out there.

We had exactly the same issue, including the most aggravating fact that nobody intended to make the change. The problem seemed to be the fact that the Stocking Type field is the field where the cursor sits on entry. There are a lot of people who need to see info but not change it. We determined it had to be one of these people and we would never know.

Once we secured the screen so it was read only for everyone (except those who should be changing things) the problem immediately went away.

Just a thought from a fish in the wrong pond.

Jer

You could use the IBM journaling function.=C2=A0 You would have to set up t he Journal and the Journal Receiver and then set up this file to be journal ed.=C2=A0 It could be possible that this journaling is already setup, if yo u have a mirrored computer used for disaster recovery.=C2=A0 You would need to study the IBM manuals if you are not familiar with how IBM journaling w orks.=C2=A0 Basically that would capture each change and perhaps tell you w ho made the change.=C2=A0 JDE also has some kind of database monitoring too l, if I remember correctly, so you might ask JDE support about that.
=C2=A0
John Dickey

You need DBAM, Database Audit Manger, from Oracle. It is free.

You can take a lot of shortcuts since you only need to identify one or maybe a few cases. <font class="small">Code:</font><hr /><pre>-- query to find the next change in the given branch (MCU) when IBSTKT is made 'O' (Obsolete)
-- query will only run until midnight
Declare @varBranch varchar(20)
Set @varBranch = <your branch#>
Declare @JDEToday int
Set @JDEToday = 109278 -- today's JDE date, 109278 = '2009-10-05'

If object_id('tempdb..#tempF4102') is not null drop table #tempF4102
Select IBITM as tempIBITM, IBSTKT as tempIBSTKT
Into #tempF4102
From TESTDTA.F4102
Where IBMCU = @varBranch
and IBUPMJ > 109180 -- just need to catch one case, don't need all data, this is 180 days into 2009

Create index #xxxyyy on #tempF4102(tempIBITM)

Declare @now datetime
Set @now = getdate()
Declare @intIBITM int
Declare @varUser varchar(30)
Set @intIBITM = Null

While convert(varchar(10),@now,121) = convert(varchar(10),getdate(),121) -- while the date, i.e., '2009-10-05', hasn't changed
Begin
Select Top 1 @intIBITM = IBITM, @varUser = IBUSER
From testdta.f4102 (nolock)
Join #tempF4102
On tempIBITM = IBITM
Where tempIBSTKT <> IBSTKT
And IBMCU = @varBranch -- IBMCU is a primary index on F4102
And IBSTKT = 'O' -- only looking to find when item was made obsolete
And IBUPMJ = @JDEToday -- only look at updates for today

If @intIBITM is not null Break

Waitfor delay '00:10:00' -- wait for ten minutes
End
Select @intIBITM, @varuser

--select * from #tempF4102</pre><hr />

I did a quick test but understand that any code like this is AS-IS.. Also, please accept my apologies for the way that this site removes white space.

Customizations are frozen so not able to add/modify programs in JDE. Last time when i worked on Enterprise 1, i could EASILY create a table trigger and track these changes. But on World, i am scratching my head on RPG. Thanks anyway.

DBAM is something new.... i need to research on this to see if we have this in our system. Need to explore on this option, thanks!

Actually we hv already secured the screen to be used only by ppl who are authorized to make the changes. Someone within the small grp is causing the problem but wont own up. Sigh.

Hi VeggyPizza, thanks for ur suggestions. I am not allowed to make code changes in JDE and at the moment, trying to use existing programs to resolve. If its really going worse, then i will need to persuade mgt to allow outside program coding to catch.

DBAM will do the job without any custom coding..and it is FREE!

JDEBlues, No problem.. To clarify, the SQL code shown is a query, which is interpreted code.. It uses one temp table.. When you close the query screen, the code and the temp table disappear.. So it is a solution that meets your requirement of no customization.. Maybe your DBA would be willing to run the query.. If you haven't read the SQL code you will need to know that it can give a false positive report if two people update the table within a ten minute period.