Auditing JDE: Column for Scheduler Server Status

In JDE the scheduler server is identified by the control record in the SYSXXX.F91300 table with a *SCHEDULER value in its SJSCHJBMN column.

The status of the scheduler server, as in, its active or inactive status, is represented in the SJSCHCTCD01 column.

 SJSCHCTCD01 has 000 if the scheduler server is down.

It can have the following values if the server is up and running:

111 - neither the job launcher nor the job monitor is paused

011 - the job launcher is paused but the job monitor is not paused

101 - the job launcher is not paused but the job monitor is paused

001 - both the job launcher and the job monitor are paused

Auditing JDE: Correcting Production Data and being Sox Compliant

To err is human. To update wrong data in the wrong field at the wrong time is the hallmark of every business analyst's career.  More often than not the responsibility to correct this data falls on the head of the IT team and for that they need UPDATE ACCESS TO PRODUCTION!

This brings us to the nightmare scenario of a developer who may need update access in production to make updates as and when needed. Even though this scenario is legitimate and the data needs to be updated, a SOX auditor would not agree to the fact that a DEV has update access on production data. To make the auditor happy and maintain his faith in the integrity of your financial data you would need to design a process that not only helps you get the job done, but also keeps you Sox Compliant.

Let's examine one such process:

1. For this to work perfectly you would need the Help-desk team, the dba team and the application support team.
2. Let the dba team design a functional account on the database with update access and self expiring password. The account should be such that the password, once generated, should expire in 4 hours
3. Add all your support team members who may update data in prod to a list which captures responses for few secret questions personalized for each member of the team. 
4. Share this list with help-desk team. They should be trained to challenge the caller with the secret question and tally their response before forwarding their request for password to the dba team.
5. Once a data issue is identified, the Business Analyst raises a High Priority incident in the Incident Management System.
6. The Incident gets assigned to the app support team, who then raise an Emergency Preventive Change Order to update the data, citing the Incident that was just created by the Business Analyst.
7.  Once the CO is raised, the app Support team member calls the Help-Desk to get the password for the functional account.
8. The Help-Desk team co-ordinates with the DBA team to get the password generated and then share the same with the app support team member.
9. Once the password is generated, the app support team member updates the data that was requested in the INC, closes the INC and closes the CO, leaving the password to expire.
10. This maintains chain of control over the account being used to make the update and also makes sure the account was used for intended purposes.

This 10 step plan, not only maintains data integrity but also helps document each update , commonly referred to as back-end update to the database.

Auditing JDE: Let's look at the Packages

I have always stated that auditors need to have a basic understanding of the JDE system before they set about designing their "Test of Design" documents for gathering evidence from a JDE system.

In that light, lets talk about the area of interest: Package information in JDE


Simply put, the below query when run on the database shall give you the information you are looking for as an auditor:

SELECT * FROM SYSXXX.F96215 where UPMJ > '116000' 

Let's look at what the query entails. In plain English, the query is looking for data in the table F96215 in the SYSXXX schema of the database where the build date is greater than 1st Jan 2015.

SYSXXX stands for the System schema in a JDE database, XXX denotes the version number of the JDE product in question. For instance, an E812 system will have a System schema named SYS812.A Schema in a database is a specialized area which has a particular type of tables characterized by the type of data they hold. A simple JDE setup will have the following schema in its database which may be of interest to the auditor:

SYS812 - System Schema
SVM812 - Server Map Schema
PRODDTA - Business Data
PRODCTL - Production Control tables

The list provided by the query should have the package names that were built during the past year and it should be fairly simple for the auditor to match the names with those on the list of Change Orders that they collect as evidence of approval for the changes that led to those packages being built.

It's always a good practice as a CNC to put the name of the package built, on the change order that was raised for the change being carried out. This makes it easier for all the parties involved to gather evidence showing only approved changes made their way into the system.

Auditing JDE : Let's look at the Users

I was recently asked by a peer about F00925 and it's value in JDE audit. The specific question was about a particular column on it and why and when that column populates.

When I answered that question for him, it occurred to me that may be this is something that a lot of auditors and CNC's alike would like to know about.

A typical Test of Design document for JDE talks about getting extracts from the following tables to get to know about the User population of a particular JDE setup:

F0092 - Library Lists - User
F0101 - Address Book
F98OWSEC - One World Security
F00950 - Security WorkBench Table

The general idea is that if you have records in the User profile Table (F0092) and can match it with the Address Book (F0101), you would be able to judge the access a user has based upon the roles in the security work bench (F00950). F98OWSEC, is used to judge whether the user is active, when was the security last updated along with the frequency of password change set in the system.

In theory the above practice would give you enough opportunity to look through any unscrupulous access a system may have provided to any given user or a role. However, JDE is a much more nuanced system to be judged based upon just 4 tables. They may be able to lead you to the particular set of fields in a given table a given user may or may not have access to, but you would not be able to answer what all a given user can see, based upon data in just the above four tables concerned.

For that level of knowledge, you would need to have an idea of the normalized tables in the Address book as well as the Control tables that are involved in the setup of the Address Book. The default Address Book application provides 30 category codes. These codes can be altered via the UDC application to point to various aspects of business, like Profit Centers, Customer type, etc. For all of this to make any sort of sense the following list of tables need to be extracted from the JDE setup:

F00924 - User Install Packages
F00922 - User Display Preferences Tag File
F00921 - User Display Preferences
F0093 - Library List Control
F98OWSEC - One World Security
F00950  - Security Workbench Table
F00925 - User Access Definition
F95921 - Role Relationships Table
F00926 - Anonymous User Access Table
F9005 - Variant Description
F9006 - Variant Detail
F0092 - Library Lists - User
F0005 - User Defined Codes
F0004 - User Defined Code Type

 Armed with the above tables you can now determine any nook and corner that a user of interest may have access to in the JDE system. It still will not tell you whether the user gets powers to change statuses of projects etc, buts that's topic  for a different blog post :)

Work with Activity Rules

One of the core feature's of OMW(Object Management Workbench) administration in JDE is the Activity Rules and their setup.

Activity rules govern the type of actions that are feasible for a given status of a project in OMW.

Let's begin with a specific example. My test setup has a status 71 which is used by the developers to denote that the project is ready for production move. This status however does not allow the CNC to do a "get", which is a good practice to ensure the latest code is promoted to production.

So if 71 is prod-ready, the CNC will have to move the code to 65 (QA) to do a get and then send it to 71 and finally send it to 72 (Production).

If the CNC now decides to update the activity rule so that "get" can be done for all the projects in status 71, following are the steps the CNC would have to carry out:

1. Go to GH9081
2. P98230
3. Launch the Activity Rules
4.  Click find
5. Highlight Status 71
6. Click Add 
7. On the Project Status Activity Rule screen, put 71 in the From Project Status
8. Add an entry for the role for which the "get" needs to be enabled
9. Click OK
10. Click Find
11. Double click the new entry for 71 that just got created
12. Add the Object Types that are needed for Allowed action type 05
13. Click OK

This  enabled get for status 71. Just make sure all the object types are covered in the list for the action that is implemented.

The following SQL should give all the entries in F98225  for our scenario:

SELECT TROMWFPS, TROMWTPS, TROMWUSER, TROMWOT, TRSRCRLS, TRTRGRLS, TROMWSRCLOC, TROMWTRGLOC, TROMWAC, TROMWRLSTK, TROMWACTFLG, TROMWTMS1, TROMWTMS2, TROMWTMN1, TROMWTMN2, TROMWTMN3, TRPID, TRMKEY, TRUSER, TRUPMJ, TRUPMT, TRTIMEZONES, TRDSAVNAME FROM SYSXXX.F98225  WHERE  ( TROMWTPS = '71' AND TROMWFPS = '71' )  ORDER BY TROMWOT ASC,TROMWSRCLOC ASC

TAMTOOL: Slow Checkin and Get in OMW

One of the major issues with OMW use over a period of time, is deteriorating performance of Checkin and get methods.

I have seen systems where the screen almost freezes white when you try to do a Get.

This is caused by empty spaces in the spec files that may come in due to constant add and delete of data. These files can be defragmented using the TAMTOOL.exe to eliminate this crawling checkin and get.





To get an idea of the amount of free space that's there, just run the following from the command prompt :

c:\b7\system\bin32>tamtool -countfreenodes -a -p c:\b7\dv7334\spec\



If there are a lot of free nodes in the spec files, following would eliminate them:

c:\b7\system\bin32> tamtool.exe -clone c:\b7\DV7334\spec\gbrspec.xdb c:\b7\DV7334\spec\gbrspec.ddb c:\b7\DV7334\gbrspec.xdb c:\b7\DV7334\gbrspec.ddb -copydata


This should eliminate the performance issues related to Get and Checkin being slow.

I have only have to do this once in my last 8 years of being a CNC. Only did it on ERP 8. Will update the post when i get a chance to try this on the newer versions.

Object Transfer between Disconnected Instances

At various occassions we come up with a requirement where we need to send pobjects built on one instance to another instance at a remote location. Product packaging is one way of dealing with it.

Lets try and install objects created in one JDE instance on a separate JDE instance of E812 bypassing the Product Packaging technique.

 To make things clear, lets say we have one instance of JDE running at a consulting company in USA. Let their Deployment Server be DEPUSA. They need to send some objects in a project to a client in Canada. The client's deployment server is DEPCANADA and their instance is completely independent of the consulting company's instance. Now lets transfer opjects from USA to CANADA.

I ) On Source JDE Installation:

1.    Make a list of all objects that have to be deployed on Target JDE installation.

2.    Check in all the objects from development workstation to the server.

3.    Go to Menu GH9083 (Package Development Tools), take option “Package Assembly” (P9601) and follow the Director. Select all the objects that are to be deployed on   target machine. Make sure that the following instructions are followed while package assembly :
a)    Package type should be an  ‘Update Package’.
d)    Parent package should be a ‘Full Package’.
c)    Check the option ‘Include Object specification’.
d)    The ‘Path code’ and ‘Parent Package’, which you are specifying, should      exist on target JDE.
e)    Activate the Update Package before taking the Define Build option.

5. Take Option Package Build (P9621) and follow the Director. Select the package that you have assembled in the above steps to be built as above and make sure that the package is built for  Client Workstation only. Check in Build Specifications options.  If you have a Business Function in your Package, check in Build Function Option. 

6. Activate the  Package and submit the  Package for Build Process .

7.    Verify the report generated after Build Process is completed .If any errors are reported take option Package Build History (P9622) and verify the Logs, Status  of the Package. Then take option Submit Rebuild from this screen if any errors are reported for that Package. Make sure that the Package and All Objects of the Package are built successfully. 

8    The Package build process creates PackageName.INF file in the directory \\Servername\E812\PACKAGE_INF (Ex: \\DEPUSA\E812\PACKAGE_INF) and also a PackageName Directory in \\Servername\E812\Pathcode\PACKAGE directory  (Ex:  \\DEPUSA\E812\ProdE812\PACKAGE)

 9.  Copy the above PackageName.INF file and PackageName directory on a CD or any other portable media. This is now ready to transfer to the Target JDE  Installation.    
 
II) On Target JDE Installation


1    Go to Menu GH9083 (Package Development Tools), take option “Package Assembly” (P9601) and follow the Director similar as you did in STEP-I .Do not select any Objects for the Package to build i.e., Assemble an empty Update Package with the same name as given in STEP-I . The Path code, Parent Package should be the same as given in while assembling an Update package on source JDE installation.

2.    Activate the assembled Package before taking the Define Build option.

3    Take Option Package Build (P9621) and follow the Director. Select the package that you have assembled in the above steps to be built as above and make sure that the package is built for  Client Workstation only.

4.   Activate the package before taking the Define Build option

5.    Check the report for any errors. Ensure that the package is built successfully.

6.    Similar to STEP-I the Package build process creates PackageName.INF file in the directory \\Servername\E812\PACKAGE_INF  and also a PackageName  Directory in \\Servername\E812\Pathcode\PACKAGE directory .

7.    Replace the PackageName.INF and PackageName directory created in this step with those created in earlier step.

8.    Now open the file PackageName.INF in edit mode and change the source Severname wherever you find in the file to the target Servername.

9.    Now select a development workstation in the network and create OL entries for all the new objects, which you are going to deploy. If the objects are existing objects checkout  the objects to the workstation.

10.    Deploy the Package to the development workstation.

11.    Open all the objects in the design mode, check whether the specifications are transferred are not. Compile all the objects and check in the objects in to the server.
 

Done! Your objects have reached the target server :)