Welcome Guest Login SignUp

Home

Technical

Functional

CNC

AS400

DB2

    Citrix

JDE News

Live Radio

Feedback

Latest Updated Posts
JDE TECHNICAL 2020-01-27 21:52:46

FAQ - Frequently Asked Questions on Table Conversion (TC) in JDE E1 2020-01-10 09:50:09

What is MVC Architecture? 2020-01-08 03:26:12

Power Forms in JDE E1 2019-12-29 12:59:17

Difference Between EDI Process and Z files 2019-12-10 11:32:31

test 2019-07-06 14:18:19

SQL query

Dated: 2017-11-30 11:17:01

OCM

Dated: 2017-11-17 12:22:22

Data browser access

Dated: 2017-10-31 01:27:56

Data Browser

Dated: 2017-10-31 01:05:48

INFINITE LOOP DETECTION

Dated: 2017-10-01 06:27:12

JDE 9.2 Table conversion

Dated: 2017-07-18 08:49:06

How to Drop and Restore Indices for EnterpriseOne Tables Using P95150

Dated: 2016-04-05 22:48:30

JDE E1 JAS “Record invalid” random errors

Dated: 2015-11-26 03:07:39

JDE EnterpriseOne UBE Runtime Performance Audit

Dated: 2015-10-20 20:46:02

QUERY TO FIND THE ROW COUNT FOR ALL FILES IN AN ISERIES LIBRARY AND ALL TABLES IN AN SQL SERVER DATA

Dated: 2015-07-31 00:18:47
 Click here to Start a new topic
JDE Forum Comments/Reviews/Queries
912 day(s) ago   #315
Name: Gopal
Category: CNC
Location:
Date: 2017-11-30 11:17:01
Replies(1)

Status:
  #315

SQL query

SQL query to change the project status from let`s say 26 to 21, without going through OMW.
925 day(s) ago   #312
Name: SimonAmerica
Category: CNC
Location:
Date: 2017-11-17 12:22:22
Replies(0)

Status:
  #312

OCM

Can Interactive Versions (IVs) Be mapped to run locally?
942 day(s) ago   #308
Name: Puppy
Category: CNC
Location:
Date: 2017-10-31 01:27:56
Replies(1)

Status:
  #308

Data browser access

We are using JDE 9.0 with 9.1.5.5 TR By default,data browser is available under tools menu of all applications. We need to disable this. Please let me know is there any way to disable this. Thanks in Advance. Puppy.
942 day(s) ago   #307
Name: Puppy
Category: CNC
Location:
Date: 2017-10-31 01:05:48
Replies(0)

Status:
  #307

Data Browser

We are using JDE E1 9.0 with 9.1.5.5 TR. By default data browser is available under tools in all application. How to restrict this? Thanks in advance.
972 day(s) ago   #303
Name: INFINITE LOOP DETECTION
Category: CNC
Location:
Date: 2017-10-01 06:27:12
Replies(1)

Status:
  #303

INFINITE LOOP DETECTION

Hi All, Has anybody come across the following issue which is captured in the E1root log [SEVERE] - [RUNTIME] *** INFINITE LOOP DETECTION WITHIN APPLICATION ER:**** Form Name :P4314_W4314A On Event: Button clicked, at ER Line = 346 While Statement: VA[168] != LI[String] = 0 On Control-ID: 470 ================================================================== This is the standard application P4314. We are using JDE 9.0 with 9.1.5.5 Toll release. Thanks in Advance. Thanks & Regards, Puppy.
1047 day(s) ago   #291
Name: Ryan
Category: CNC
Location:
Date: 2017-07-18 08:49:06
Replies(1)

Status:
  #291

JDE 9.2 Table conversion

I have done a new install of JDE 9.2 with all enviroments. i would like to now convert just my buisness data and control tables from my 9.0 release i have tried to do this using the custom Installation plan but there is somthing i am still missing. any help would be appreciated thankyou
1516 day(s) ago   #222
Name: Teo
Category: CNC
Location: Singapore
Date: 2016-04-05 22:48:30
Replies(0)

Status:
  #222

How to Drop and Restore Indices for EnterpriseOne Tables Using P95150

The P95150 (Index Selection Tool) application displays the table indexes in a tree structure and allows to flag the indexes that we wish to drop. We can drop single or multiple indexes at a time. P95150 only allows to drop indexes that are not unique or primary to the table. As Dropping these types of indexes would disrupt the integrity of the table.

Please refer to Doc ID 1114664.1 for more details.
Oracle docs also provides detailed documentation about Dropping Indexes from a Table.

Cheers!
1647 day(s) ago   #214
Name: Teo
Category: CNC
Location: Singapore
Date: 2015-11-26 03:07:39
Replies(0)

Status:
  #214

JDE E1 JAS “Record invalid” random errors

This is a classic and is occurring everywhere most of the time. If you are getting errors like this, you need to change the LocalLogicCatalog.xml file in the WEB-INF/Classes directory, find the function that is failing (use the little yellow triangle when the error occurs on the browser) to find the function name.

Classics are b0000069.c and b0000095.c, there are heaps of others in the file.

Generally all of the table NERS (FXXXXX) run locally on the JAS server, but also the following “B”’s. Note that this is only in 8.98.2.2 (note that this sample is from 8.98.2.2, the functionality has been there for ages!).

module="B0000045"
module="B0000069"
module="B0000095"
module="B0000128"
module="B0000130"
module="B0000198"
module="B0000399"
module="B0000580"
module="B0200098"
module="B03b0108"
module="B1100005"
module="B1100006"
module="B1100007"
module="B1100008"
module="B1100009"
module="B1100010"
module="B1100011"
module="B1100013"
module="B1100015"
module="B1100016"
module="B1100017"
module="B1100022"
module="B1701560"
module="B4000770"
module="B4002410"
module="B4301010"
module="B7600580"
module="B95400"
module="B9800100"
module="B9800150"
module="B9800181"
module="B9861A"

If these are bombing randomly for you,
Change the
module="B0000045"
function="ConvertNumericToString"
run-local="true"/>

Cheers!
1684 day(s) ago   #210
Name: Teo
Category: CNC
Location: Singapore
Date: 2015-10-20 20:46:02
Replies(0)

Status:
  #210

JDE EnterpriseOne UBE Runtime Performance Audit

Here`s a SQL script which can be used to evaluate the runtimes of your UBEs.

SELECT JCPID as UBE,
JCVERS as [Version],
ltrim(rtrim(simd)) + `: ` + ltrim(rtrim(vrjd)) as UBE_Name,
count(1) as [Count],
Avg(datediff(s,jcstdtim,jcetdtim)) AS Avg_Time,
min(datediff(s,jcstdtim,jcetdtim)) AS Min_Time,
max(datediff(s,jcstdtim,jcetdtim)) AS Max_Time
from svm910.f986114,ol910.f9860, [JDE_PD910].pd910.f983051
where ltrim(rtrim(jcpid)) = ltrim(rtrim(siobnm))
and ltrim(rtrim(jcvers)) = ltrim(rtrim(vrvers))
and ltrim(rtrim(jcpid)) = ltrim(rtrim(vrpid))
and JCETDTIM > `01-01-2015`
and JCETDTIM < `06-30-2015`
group by jcpid, JCVERS, ltrim(rtrim(simd)) + `: ` + ltrim(rtrim(vrjd))


Courtsy- e1tips

Thanks
1765 day(s) ago   #203
Name: Teo
Category: CNC
Location: India
Date: 2015-07-31 00:18:47
Replies(0)

Status:
  #203

QUERY TO FIND THE ROW COUNT FOR ALL FILES IN AN ISERIES LIBRARY AND ALL TABLES IN AN SQL SERVER DATA

Below are the SQL queries that can be used to get row count on all tables in a database and all files in a library:-

iSeries - SQL query to get a row count on all files in an iSeries library:

SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = `PRODDTA`
AND NUMBER_ROWS > 0
ORDER BY NUMBER_ROWS DESC, TABLE_NAME DESC

NOTE:- the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.

SQL Server - SQL query to get a row count on all tables in an SQL Server database:-

SELECT `[` + SCHEMA_NAME(t.schema_id) + `].[` + t.name + `]` AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid > 2
WHERE i.rows > 0
ORDER BY i.rows DESC, t.name DESC

UPDATE: The following SQL statement will not only give you the row counts of all the tables in the SQL Server database it will also give you the amount of disk space that it is taking:-

SELECT
t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE `dt%` AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceMB desc, t.Name

Courtsy:- e1tips

Cheers!!
1933 day(s) ago   #176
Name: TechZone
Category: CNC
Location: Singapore
Date: 2015-02-13 00:47:06
Replies(0)

Status:
  #176

SQL To Get List Of Column Names Of a Table On SQL Server

As we know that most database tables in EnterpriseOne have a large number of columns. In fact, they usually push to the limits of the RDBMS.

If we need a list of those columns for things like INSERT or UPDATE statements.

USE JDE_PY910 SELECT [name] AS [Column Name] FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = `U` AND [Name] = `F98950`)

We can use either type V (views) or U (tables).

Courtsy:-e1Tips
1934 day(s) ago   #175
Name: Teo
Category: CNC
Location: India
Date: 2015-02-12 22:29:50
Replies(0)

Status:
  #175

SQL To Extract List of E1 Roles With Description

Please refer to following script -

SELECT AUUSER, AUROLEDESC FROM SY900.F00926

Cheers!
1979 day(s) ago   #169
Name: TechZone
Category: CNC
Location:
Date: 2014-12-29 00:38:21
Replies(1)

Status:
  #169

Can JD Edwards upgrades get better? Perhaps 9.2 will provide some project savings with this.

When CNC people get together at the pub, they dont talk about their families, wife's or girlfriends They talk about package builds, JDBj timeouts and enterprise servers. They trash talk AS/400s until the bloke that loves them starts crying and goes back to his shandy I do however recall one night talking about upgrades and why they are no shipped as ASUs. Honest

I thought it would be simple enough to ship the update as one massive ESU, or ASU and then clients could apply that nice and easy. We no longer have an exact correlation between tools release and application release, so why not just ship us the ASU and not force us into the DD merges, spec merges, control table merges etc ESUs are much easier [I know they still merge, but they do not create ol920, dd920 etc). Or so the psyche tells us.

So it seems that what might be in release with 9.2 is a better way to upgrade.

Everything will still work the same in terms of all of the steps (Doh!), but instead of using the 9.2 central objects as a base, theyll use your central objects as the base. Okay, so lets think about this How are they going to know what youve modified and hence what to replace Well, this is where it starts to get funky, they are going to run a manifest of code changes between 9.1 and 9.2, a total list of changed objects. Okay, following me? So then, they are going to determine what of THESE objects your might have had your sticky fingers in and blast some pristine 9.2 code over the top.

Of course this will not take into consideration the ESUs that youve applied, but at the end of the day this will just mean your mods are closer to 9.2, and that the retrofit will be easier Whats the less, try and stay code current!

Are you still with me? So this means that instead of blasting over the top of EVERY object that youve modified, they are only going to change the ones that have changed between 9.1 and 9.2 [as per the manifest]. This is going to mean less retrofit and this is also going to mean less testing and perhaps less training because things are not going to change as much.

Wow, that is pretty cool. Ive never met a CNC person that has ever been concerned about the speed of the R98700 either, and the funny thing about this UBE, is that it generally only gets run once, the first time. You might run it again if you did not have your modifications identified properly when you ran it the first time (not speaking from experience at all there) So perhaps not quicker upgrade, but quicker upgrade project.

The oracle laboratory tells that this might mean some crazy savings for some clients (95%) and reasonable for others (45%). Save 45% on retrofit, yes please.

This coupled with all of the other nice things that are out there is going to make for a much better upgrade. We must remember that if this makes it to GA, we need to entice our clients with leaner estimates on retrofit and smaller numbers of objects to be affected. So, all we need is the manifest and then well be able to do some sort of impact analysis on this to work out what we want to retrofit. We can then demonstrate downstream savings too, with less change there is less testing, less rework etc.

Please note that this is not GA and Ill not be held responsible for you entering into contracts on the basis of the information supplied in my blog haha, my safe harbour statement.

Source: Shannon's JD Edwards CNC Blog
2024 day(s) ago   #166
Name: TechZone
Category: CNC
Location: Singapore
Date: 2014-11-14 19:25:40
Replies(1)

Status:
  #166

Important FAQ about PathCode, Environment, Datasources and OCM

Path Code

Question1: In which format EnterpriseOne Specs get stored for a path code?
Answer1: Prior to 8.12, specs were stored in TAM (Table Access Management) format. In the 8.12 release and later, this metadata is now stored in XML format in an XML schema in the relational database. This change allows all servers to view and match their code up to one central source.

Question2: How to create central objects for custom path code?
Answer2: One can create using either of the two methods:

1- Using Platform Pack Installation: Installing the platform pack selecting only the EnterpriseOne DB components. Once done with the platform pack, one need to create F983051 manually and need to update the table with the name of your Deployment server and path code name.

2- Runing R98403, version XJDE0019.This is automatically copy F983051 and updates the table with the name of your Deployment server and path code name.

Question3: How to create check-in location for custom path code?
Answer3: Check-in location can be created in two ways:

1- Manually copying the directory structure on your deployment server and create a folder with the same name as your custom path code under EnterpriseOneE812. Copy the contents depending on whether you creating the path code at all new or copying it from existing one. In case of copy, make sure to copy it from Pristine.

2- Running the UBE R9800942, Version XJDE0001. Please run the UBE LOCALLY.

Question4: In 8.12, why copying path code will not copy the packages?
Answer4: This is because specifications for packages are now stored in the XML format in the database. The names of the tables are same as the name of the package.

Question5: Is there any UBE or method to create the path code in the Enterprise Server automatically?
Answer5: The only way to get a path code onto the enterprise server is by manual copy the path code and updates the spec.ini. This file tells the server where to look for the XML specifications.

Question6: How to delete a Path Code?
Answer6: To delete a path code, you need to perform the following:

1. Delete the record from the table F00942.
2. Delete the central object tables.
3. Manually delete the check-in location on the deployment and path code directory on enterprise servers.

Question7: Is there any business function to retrieve the path code?
Answer7: The business function B989400 Get Pathcode for an Environment can be used to retrieve the path code.

Question8: Can multiples Data Dictionaries be used for a single path codes?
Answer8: GCS do not recommend setting up separate data dictionaries.
Please refer to the solution for further details: E1: DD: Use of multiple DD for single path code Note 631426.1

Data Sources

Question1: What are data sources and where definition gets stored?
Answer1: The data sources define where the database tables reside and where the software runs logic objects for the enterprise. Data source definitions are stored in the Data Source Master table (F98611). Workstations use a Common table F98611, which generally resides in the system data source on the enterprise server. Oracles JD Edwards

EnterpriseOne servers that process logic and request data require their own unique definitions for data sources; therefore, they have their own table F98611 in the server map data source.

Note: EnterpriseOne having two types of data sources: Database Data Sources and Logic Machine Data Sources. First DB and second one by SVR are marked in the OMOCM1 column of the table F98611.

Question2: Are all data sources Unicode complaint?
Answer2: EnterpriseOne now ships with all data sources in Unicode except for Business Data and Control tables. These data sources can be converted to Unicode on an upgrade.

Note: There are 12 primary types of data sources that can be configured with EnterpriseOne. There are more than 4500 tables within a normal installation of EnterpriseOne (8.12) that qualify as business data.

Question3: Would you need to add new data dictionary, object librarian, system, server, server map, local, or logic data sources?
Answer3: No. These data sources are all shared data sources throughout the EnterpriseOne implementation. Because of this, under a normal EnterpriseOne configuration, these data sources contain data that is shared among all the environments.

Question4: What do you mean by OCM Data Source flag?
Answer4: It indicates that the data source contains atleast F98611 and F986101 tables. The system and all server map data sources use this flag. This flag determines whether the data source is shown in the Machine Search_Select form of the P986115 application.

Question5: What do you mean by Data Class while defining the data source?
Answer5: This defines the type of data that will be held in the data source being defined. This new field helps provide better data selection for Universal Batch Engines (UBEs) and servers to further identify what kind of data a data source is referencing.

Note: The classes of data available are: Business Data, Central Objects, Data Dictionary, Logic, Host Server Map, Local, Object Librarian, Installation Planner, System, Control Tables, and Versions.

Question6: Are tables with identical name within the same database is supported by EnterpriseOne?
Answer6: Only two of the four supported RDBMSs support table owners (SQL Server and Oracle). These databases allow you to have tables with identical names within the same database differentiated by the owner. Use of this system allows you to keep multiple copies of some tables (such as F986101 OCM) in the exact same location.

Note: The Object Owner ID field indicates the table owner for that specific data source. You can have multiple data sources with identical table owners, depending on your specific setup. This field is used when EnterpriseOne structures the SQL statements that return data to the EnterpriseOne applications.

Question7: How to split business data in multiple data sources?
Answer7: The data class is at the center of automatic OCM generation. It categorizes the type of data stored in data sources and tables. For example, Business Data is data class B and System is data class S. If you want to split business data into multiple data sources, you need to create a new data class, modify the data source template (so Environment Director or Installation Planner prompts for a data source) and assign tables and GT objects to that data class. OCM will be created based on the information provided previously.

You can create a new data class by adding a value to the H96/DU UDC for data sources and H96/CL UDC for table and GT objects.

Note. Modifying the data class does not affect existing OCM mappings; the data class is only used when creating mappings for a new environment.

OCM

Question1: What are the parameters comprise the primary, unique index to the OCM?
Answer1: Here are the parameters:
  • Environment
  • User, which is either a specific User ID / Role or *PUBLIC.
  • Object name
  • Database path
Question2: How to delete OCM and how deletion comes into effect?
Answer2: While in Work with object mappings, highlight the desired OCM record and click the Delete button on the toolbar. This will remove this record from the F986101 table.

Note: If you remove a mapping from the system data source, your client machines will need to log out and back into E1 before they will be able to see the change. If you change a server map F986101 entry, you will need to stop and start your host services to ensure that the mapping takes effect. You will also need to clear the cache for the HTML clients via SAW/Server Manager (8.97) or by stopping and restarting the Web Services (WebSphere or OAS).

Question3: Is it possible that different batch servers points to the same F986110 so that WSJ can show all the jobs submitted to different servers?
Answer3: Yes, by keeping the object owner different for all the server map data source, so that all point to table object_owner.F986110.

Question4: While making any changes in OCM, what would be recomendation, log on to a client machine or deployment server?
Answer4: To add an OCM mapping, log on to a client machine. The reason you are logging on to a client machine is that it will update the system F986101. When you log on to the deployment server in the planner environment, it will change the F986101 contained in the planner MSDE or SSE database. Since this is not the database accessed by your end users for mappings, you normally do not want to change your mappings in this database.

Question5: Is it mandatory to make changes in both System and Server Map while making changes to OCM?
Answer5: Generally, you need to choose a system to change a mapping for your client workstations. However, if you think changes are required, then changes should be made to server map.

Note: If you change the mappings for your users in the system data source, but not the server map data source, you may see different results when running reports locally compared to reports run on the server.

Question6: What R986101A (Create System Table Mappings) does?
Answer6: This program adds active Object Configuration Manager (OCM) records for a specified table, user ID, and data source for all environments listed in the Environment Detail table (F00941). You can use data selection to filter the environments to which this process adds OCM records.

Question7: How to Create OCM Records for Business Functions using R98640?
Answer7: This batch process (R986140) reads the Object Librarian tables for server business functions, and then creates Object Configuration Manager records for those business functions in the target data source that you specify in processing options. Processing options also enable you to specify the source data source and environment to use when creating these Object Configuration Manager records.

Please refer to E1: ENV: EnterpriseOne 8.9 OCM Mappings for Business Functions Invalid <> for further details.

Question8: How many copies of F986101 can exist in EnterpriseOne installation?
Answer8: There can be many copies of this table on an EnterpriseOne system. The workstations will use one copy, in the system data source, to find their data. The enterprise and logic servers have their own copy of this table, which is used when a report is run on an enterprise or logic server.

The F986101 table really only consists of what is called OCM mappings. These mappings tell the system what data source the table resides in, where to run its business functions, and how to process its reports.

Question9: How to generate server map for newly created environment?
Answer9: You need to generate server map using the application P9654A.

Please refer to E1: ENV: Created a new environment and have no OCM mappings in the Server Map <>.

Environment

Question1: What is an Environment?
Answer1: An environment can be described as the framework on which the system rests. This framework is made up of object configuration mappings and a path code. These are what tell EnterpriseOne where to find the data and specification files needed for its applications to run.

Question2: How to delete an Environment?
Answer2: When you are in the Environment Master program, P0094, highlight an environment and click the Delete button on the toolbar. This will remove the records for the Environment from F0093, F0094, F00941, and F986101.

Note: When you delete an environment, you are not automatically deleting the data sources and business data associated with that environment. This will need to be done as a separate step.

Question3: What is the recommended way of Environment Creation?
Answer3: While Environment Director is a useful tool for creating environments, we recommended using Installation Planner (P9840) and Installation Workbench (P9841) to create custom environments.

Question4: What environment director will do?
Answer4: Environment Director performs the following:
  • Creates OCM if it does not already exist.
  • Writes F98403 record for the new environment.
  • Loads business data.
  • Loads control tables.
  • Configures enterprise servers.
  • Creates server map OCM.
  • Creates server map F98611 records.
  • Writes F9651 records for enterprise servers.
  • Configures BSFN and UBE mappings.
Note: you can refer to E1: ENV: How To Create Custom Environment Using Environment Director (P989400)? (Doc ID 847534.1) for complete process.

Question5: What would be the recommendation: Create custom environment using plan or Environment Director?
Answer5: It is recommended that you create custom environments from the PSFTPLAN environment on the deployment server using Environment Director. Doing so creates the environment in the planner databases and deploys it to the system data source. The environment must exist in the planner databases to apply software updates to the environment.

Question6: Can we run Environment Director logging to deployment environment?
Answer6: You cannot run Environment Director from the deployment environment. The deployment environment exists only on the deployment server. It uses the bootstrap tables (F98611 and F986101) from the Planner; the remainder of the system tables are in the system data source. This split will cause inconsistencies between the planner and system definitions.

References-
JDESource
OracleSupport
2091 day(s) ago   #158
Name: Eddie
Category: CNC
Location: Singapore
Date: 2014-09-08 01:29:36
Replies(1)

Status:
  #158

OMW Error - Terminal Server Not Authorized

Recently we encountered an error "Terminal Server Not Authorized" and was unable to design in OMW on deployment server (Citrix). All services to Terminal server got disabled.

After some research found that under [Debug] in jde INI file there should be a entry
OWDeveloper=TRUE.

After adding above entry all the OMW buttons like "Get", "Design" were enabled back.

Thanks
JDE E1 CNC Admin.
2123 day(s) ago   #156
Name: Eddie
Category: CNC
Location: India
Date: 2014-08-07 20:30:30
Replies(0)

Status:
  #156

Broadcast a message at login within JDE

This can be achieved using Server Manager, it has a feature called "Send Message".

This can be used to send messages to the applications active within a user session. We can select active users and send a message. Messages will be received only by users who have an application running, and only when they perform an activity within the application such as pressing Find on the toolbar.

Please refer Server Manager Guide - JD Edwards EnterpriseOne Tools 8.98 Server Manager Guide, Update Note:705509.1 (search for "Send Message" in the guide).

Thanks
JDE E1 CNC Admin
2172 day(s) ago   #152
Name: Chris
Category: CNC
Location: India
Date: 2014-06-19 01:22:19
Replies(0)

Status:
  #152

Is it possible to set up Deployment Server on a Non-Windows Machine?

Good Question.

Not possible

Deployment server and FAT client are basically GUI[Win32] based applications. So they are windows only applications and is not possible to set up Deployment Server on a Non-Windows Machine.

Cheers!
2173 day(s) ago   #151
Name: Eddie
Category: CNC
Location: India
Date: 2014-06-18 03:11:59
Replies(0)

Status:
  #151

Quicker Way to Refresh Cross Reference

Normally the cross reference facility takes quite a long time to populate the tables when running the R980011.

The steps mentioned below will drastically shorten the runtime of the R980011:-
  1. Copy the All Objects version of R980011 to create a new version.
  2. In data selection add a condition for BC Date Update (F9861) (UPMJ) is greater than the date you last ran the rebuild.


This will cause the R980011 to check the Last Updated Date (UPMJ) field in Object Librarian and only process those records with a date more recent than the date specified.

Source:-e1tips

Thanks
JDE E1 CNC Admin
2313 day(s) ago   #139
Name: Eddie
Category: CNC
Location: Singapore
Date: 2014-01-29 00:17:43
Replies(0)

Status:
  #139

Special Data Selection When Purging Work Submitted Jobs

Usually we purge out all records from the Work Submitted Jobs (WSJ) that are at least x day old and days can be passed from processing option. Recently we had a requirement to purge a particular report everyday.

While setting up the R9861101 data selection, I found that the Literal –> Range of Values method worked in a interesting way. System allows to enter the following range on the F986110|RNDFUF2 (Foundation – Future Use 2):

R5543500_MRI0000_0000_PDF - R5543500_MRI9999_9999_PDF

That is about the most robust string/integer range. And the way system allowed to do data selection is very interesting.

Well, as we already know, SQL has a “BETWEEN” SQL operator. The SQL equivilant looks something like this:

select * from svm910.F986110
where JCFNDFUF2 between `R5543500_MRI0000_0000_PDF` and `R5543500_MRI9999_9999_PDF`

Cheers
JDE CNC Admin
2361 day(s) ago   #131
Name: CNC Admin
Category: CNC
Location: Singapore
Date: 2013-12-12 02:52:13
Replies(3)

Status:
  #131

Query To Get The Email Address From The Address Book Who`s Who For Each User Profile

Hi All,

Following SQL Query can be used to Get the Email address from the Address Book Who`s Who Table for each user profile -

SELECT A.ULUSER,A.ULAN8, B.EAEMAL
FROM JDE910.SY910.F0092 A, JDE_PRODUCTION.PRODDTA.F01151 B
WHERE ULUGRP<>`*GROUP` AND B.EAAN8 IN (SELECT ABAN8 FROM TESTDTA.F0101 WHERE ABAT1=`E`) AND A.ULAN8=B.EAAN8 ORDER BY ULAN8


Regards
Oracle JDE E1 CNC Admin.
2432 day(s) ago   #129
Name: CNC Admin
Category: CNC
Location: Singapore
Date: 2013-10-02 20:24:53
Replies(2)

Status:
  #129

Query to Compare Field Values of a Table in Different JDE release

Hi All,

Following SQL can be used to compare the difference between the F9200 (Data Dictionary) used for EnterpriseOne 9.1 and the one used for EnterpriseOne 8.12, that was copied to the SQL Server using R98403 -

SELECT a.FRDTAI,a.FRCDEC AS a_FRCDEC, b.FRCDEC AS b_FRCDEC
FROM JDE910.DD910.F9210 a INNER JOIN DD812.PRODDTA.F9210 b
ON (a.FRDTAI COLLATE DATABASE_DEFAULT = b.FRDTAI COLLATE DATABASE_DEFAULT)
WHERE a.FRCDEC COLLATE DATABASE_DEFAULT <> b.FRCDEC COLLATE DATABASE_DEFAULT

Regards
Oracle JDE E1 CNC
2472 day(s) ago   #123
Name: CNC Admin
Category: CNC
Location: Singapore
Date: 2013-08-23 22:22:57
Replies(0)

Status:
  #123

Underscore and the Other SQL Wildcard Characters

Hi Techzone,

There are few wildcard characters available in SQL but we seldom used this very useful thing, it can be used as a wildcard for a single character: the underscore (_).
The underscore can be used in a where like clause as a wildcard for a single character.

It works like so:
SELECT * FROM OL910.F9860 WHERE SIOBNM LIKE `_55%`

Ranges can also be specified in a where like clause by using [] or [^].

Wildcard Description Example
% Any string of zero or more characters WHERE title LIKE `%computer%` finds all book titles with the word `computer` anywhere in the book title.
_ (underscore) A substitute for a single character WHERE au_fname LIKE `_ean` finds all four-letter first names that end with ean (Dean, Sean, and so on).
[charlist] Sets and ranges of characters to match, range ([a-f]) or set ([abcdef]) WHERE au_lname LIKE `[C-P]arsen` finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^] Matches only a character NOT specified within the brackets WHERE au_lname LIKE `de[^l]%` all author last names starting with de and where the following letter is not l.

source: W3schools

Cheers
Oracle JDE E1 CNC Admin
2496 day(s) ago   #121
Name: CNC Admin
Category: CNC
Location: Singapore
Date: 2013-07-30 01:00:12
Replies(0)

Status:
  #121

Could Not Find UBE Logs On EnterpriseOne Tools Release 9.1?

Recently we installed E1 9.1 with Tools Release 9.1.3.1, we experienced that the logs for UBEs that finished without error were not available.

Apparently for Oracle, Save Empty Debug Log = UBESaveLogFile. The Save Empty Debug Log is what we see when looking at the configuration information in Server Manager and UBESaveLogFile is what is in the JDE.INI.

Not sure how they think these 2 things are equal but enabling this setting makes the JDE.LOG available after running a UBE.



Courtsy - e1Tips

Cheers
JDE E1 CNC Admin.
2530 day(s) ago   #114
Name: CNC Admin
Category: CNC
Location: SG
Date: 2013-06-26 23:21:23
Replies(0)

Status:
  #114

Query to Disable all Users not in a Particular JDE EnterpriseOne Role

Hello Everyone,

Following SQL can be used to disable all users except those having SYSADMIN role.

UPDATE SY910.F98OWSEC
SET SCEUSER = `02`
WHERE SCUSER NOT IN
(SELECT RLTOROLE FROM SY910.F95921 WHERE RLFRROLE = `SYSADMIN`)
AND SCUSER <> `*POLICY`

Note:-This Query will work for JDE release 8.10 or higher, please mention the library name accordingly.

Thanks
Oracle JDE E1 CNC Admin.
2573 day(s) ago   #110
Name: TechZone
Category: CNC
Location: India
Date: 2013-05-14 03:16:13
Replies(1)

Status:
  #110

Finding The Row Count For All Files In An iSeries Library And All Tables In An SQL Server Database

We have been moving several JDE EnterpriseOne databases to SQL Server lately. To do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables.
Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:

iSeries SQL query to get a row count on all files in an iSeries library:-

SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = `PRODDTA`
AND NUMBER_ROWS > 0

SQL Server SQL query to get a row count on all tables in an SQL Server database:-

SELECT `[` + SCHEMA_NAME(t.schema_id) + `].[` + t.name + `]` AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id
AND i.indid < 2
WHERE i.rows > 0


Guys please suggest if there are any better methods that you use to do a quick data integrity check?

Input from E1Tips

Regards
TechZone
2627 day(s) ago   #103
Name: CNC Admin
Category: CNC
Location: India
Date: 2013-03-21 00:46:28
Replies(1)

Status:
  #103

Central Objects Relationships - Spec vs Table

Below is the relationship between the spec and the Central Objects tables, it can be used as a guideline which Central Object tables need to be reindexed : -

2627 day(s) ago   #102
Name: CNC Admin
Category: CNC
Location: India
Date: 2013-03-21 00:19:21
Replies(0)

Status:
  #102

Check Checkin/out status of Objects

Useful Queries we need time and again -

Objects Checked out -
select sipathcd as "Path Code", simkey as "Machine", SIOBNM, siuser as "User ID" from OBJ7334.F9861 where SISTCE != `1` order by siuser, siobnm;

Versions Checked out -
select VRENHV, VRUSER as "User", VRPID as "Object", VRVERS as "Version", VRMKEY as "Machine" from DV7334.F983051 where VRCHKOUTSTS=`Y` order by vruser, vrpid, vrvers;

Objects created but never Checked-in -
Select POOMWUSER, POOMWOBJID, POOMWMKLEY, POPATHCD from SYS7334.F98222 where POOMWOT !=`UBEVER` and POOMWOST=01 and POOMWCHS=`1` order by POOMWUSER, POOMWOBJID, POPATHCD;

Versions created but never Checked-in -
Select POOMWUSER, POOMWOBJID, POOMWMKEY, POPATHCD from SYS7334.F98222 where POOMWOST=01 and POOMWCHS=`1` and POOMWOT=`UBEVER` order by POOMWUSER, POOMWOBJID, POPATHCD;

Regards
JDE E1 CNC
2628 day(s) ago   #101
Name: CNC Admin
Category: CNC
Location: India
Date: 2013-03-20 22:14:49
Replies(2)

Status:
  #101

Query to find Out all the active users in the system

Execute the following SQL command to get all the active users with a particular role (ABCD)

select distinct SCUSER NT_ID, ABALPH UserName from sys7334.F0092 a, sys7334.F98OWSEC b, devdta.F0101 c where a.ULUSER = b.SCUSER and a.ULUSER = c.ABALKY and a.ULUGRP like `%ABCD%` and b.SCEUSER = `01` and c.ABAT1 = `E` and b.SCUSER not in (`JDEXYZ`, `JDE`, `AFPCRPMON`);

Regards
JDE E1 CNC
2788 day(s) ago   #89
Name: Saaheen
Category: CNC
Location: India
Date: 2012-10-11 01:06:29
Replies(1)

Status:
  #89

Adding a JDE E1 Environment to every Role/Group

Hi Guys,

Following SQL can be used to add an JDE EnterpriseOne environment to every role or group in your installation:

INSERT INTO SY910/F0093 (LLUSER,LLLL,LLSEQ,LLMNI) SELECT ULUSER, `PD910`,`1.00`,' FROM SY910/F0092

Believe this information to be useful for many of us!!

Regards
Saaheen
So called JDE geek!
2872 day(s) ago   #74
Name: Anjeleo
Category: CNC
Location: Canada
Date: 2012-07-19 06:37:06
Replies(1)

Status:
  #74

Oracle JDE EnterpriseOne and MTXW (Mutex) HELL!

Recently we experienced that after an update package deployment, when a user tried to login many of the iSeries jobs went in to a MTXW (mutex wait) state.
Then, we brought all services down and deleted all SQLPKGs (SQL Packages) that were owned by ONEWORLD. Did not worked. Then, after much frustration I deleted the following files from the IFS system:

- dddict.ddb
- dddict.xdb
- ddtext.ddb
- ddtext.xdb
- glbltbl.ddb
- glbltbl.xdb

These files get re-generated on-the-fly.
What ever was corrupt was in these files.

Regards
CNC Consultant
Oracle JDE EnterpriseOne
2878 day(s) ago   #68
Name: TechZone
Category: CNC
Location: India
Date: 2012-07-13 19:19:17
Replies(3)

Status:
  #68

How To Know The OS/400 Version

Issue the following command to determine what version of OS/400 you are using:

dspdtaara qss1mri

And the version name will be displayed on the green screen.
Thanks & Regards
TechZone.
2956 day(s) ago   #59
Name: TechZone
Category: CNC
Location: Singapore
Date: 2012-04-26 01:06:34
Replies(0)

Status:
  #59

Escape From SQL - Query % in a table column

How to use SQL to look for a "%" in a column?

Possibly the first guess is to use something like:
SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE "%%%"

But this statement will not work. However, DB2/OS400 provides a very cool way of escaping a special character. It is the ESCAPE keyword. This keyword allows to specify the character that you would like to use as an escape character. So, this would be the correct syntax:

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE "%+%%"
2956 day(s) ago   #58
Name: TechZone
Category: CNC
Location: Singapore
Date: 2012-04-26 00:49:12
Replies(0)

Status:
  #58

How To Throttle The Job Queue

NOTE: The following is intended for use on the IBM System i (iSeries,AS400, etc.)

CHGJOBQE SBSD(QBATCH) JOBQ(JDEOWPACK) MAXACT(2)

Explanation:

CHGJOBQE- Change Job Queue Entry

SBSD- Subsystem Description

JOBQ- Job Queue

MAXACT- Maximum Active Jobs
3015 day(s) ago   #49
Name: JDE Freak
Category: CNC
Location: Canada
Date: 2012-02-27 20:40:43
Replies(0)

Status:
  #49

Tables used by Object Management Workbench (OMW) or Object Librarian (OL)

All the tables used by Object Management Workbench are located in the SYSTEM data source (e.g.: System 810). OMW/OL uses the following tables:

1) F98210 Logging Header
Contains header information including the project, object type, object name, time stamps, path codes, data sources, locations and action type.

2) F98211 Logging Detail
Contains detailed information on the errors and error codes for the projects and objects included in the F98210.

3) F98220 Project Master
Detailed information on all newly created projects and all default projects.

4) F98221 Project Users
Includes information on all the users in the EnterpriseOne software, the projects they are added to and the role numbers they are assigned with.

5) F98222 Project Objects
This log details all EnterpriseOne objects that are added to Object Management Workbench projects. It includes the object name and type, the path code and project name.

6) F98223 Allowed Actions
All the allowed actions assigned to user roles in the Object Management Configuration (OMC) are detailed in this table.

7) F98224 Status Activity Rules
Details all project status activity rules.

8) F98225 Transfer Activity Rules
Details all the object transfer activity rules.

9) F98230 System Settings
Contains all OMC information other than activity rules and notification subscriptions.

10) F9829 Notification Subscriptions
3040 day(s) ago   #46
Name: CNC Admin
Category: CNC
Location: 185
Date: 2012-02-02 20:31:08
Replies(0)

Status:
  #46

How to default a UBE Version to run as Print Immediate?

We can set any UBE Version to run as Print Immediate without having user going to advance override and selecting Print Immediate on a day to day basis.
This can be done by changing the flag to 1 in column VRVCC3 in Version Table (F983051) under each Environment`s Central Objects.
3120 day(s) ago   #41
Name: TZ
Category: CNC
Location: India
Date: 2011-11-14 23:33:21
Replies(0)

Status:
  #41

Changing Status of Package Using SQL

Below is the SQL statement that I use to mark the package as not deployed if it shows deployed:- update sy900/f9603 set PHISPTF=0 where phpkgname="PACKAGENAME"
3146 day(s) ago   #38
Name: TZ
Category: CNC
Location: India
Date: 2011-10-19 23:16:40
Replies(0)

Status:
  #38

Determining the current version of Oracle JDEdwards E1

Logon to Enterprise Server - Using windows explorer find the file: ptf.log located at - X:/JDEdwardsOneWorld/ddp/B7334/system/bin32, where B7334 is current version. Just open the ptf.log file, this is a text file containing the software version.
3167 day(s) ago   #32
Name: TZ
Category: CNC
Location: SG
Date: 2011-09-28 20:46:10
Replies(1)

Status:
  #32

Who Has Access To That In JDE E1?

Our manager gets a lot of CNC-type questions from users and doesnt like to always bother us with them. One of the questions she gets is Who has access to the application or UBE?. She got tired of wading through the multiple GUI applications required to get that information and came up with a very slick SQL query that returns the information quickly:- SQL query comes in the following post...
3177 day(s) ago   #29
Name: Chris
Category: CNC
Location: SG
Date: 2011-09-18 23:35:34
Replies(0)

Status:
  #29

Deleting User Overrides In EnterpriseOne (E1)

Deleting User Overrides In EnterpriseOne (E1)-- Once in a while my power-user wants to delete some User Overrides because they created some that they want everyone to use. Well, the UO app usually works pretty well, until your get some can not be deleted with the normal process. Here is the process that I go through: 1. Fastpath to UO 2. Use the QBE to find the UOs that you would like to delete 3. Highlight them and click delete 4. NOTE: If some of them do not delete and give you an error like ?The object is in an open project?: * Run a SQL statement similar to the following from the green screen to select the records desired and then delete them: SELECT * FROM copy811/f98950 WHERE UOUSER *PUBLIC and UOOBNM=Pxxxx and UOFMNM=WxxxxA and UOVERS=XJDE0016?
3177 day(s) ago   #28
Name: Chris
Category: CNC
Location: SG
Date: 2011-09-18 23:22:34
Replies(0)

Status:
  #28

Package Build Count

How many packages of each type per environment did you build last year? Here is the SQL that I used to gather the information required: select hhpkgname, hhpathcd, year(date(char(hhblddte+1900000))) as hhblddte from sy811/f96215 where hhpkgname not like 'JJ%' --remove the ESUs and hhdatp='CLIENT' --type of build and hhbldsts=50 --build completed successfully
3177 day(s) ago   #27
Name: TZ
Category: CNC
Location: SG
Date: 2011-09-18 23:11:27
Replies(0)

Status:
  #27

Problems Deploying A Package

We had an issue over the weekend. We built an update package successfully. However, we were unable to deploy it. The R98825D just kept running and running. It wouldnt end. Stop services, Start services, Delete SQLPKGs, Reboot Deployment server, but none of these worked. I then started looking through the server kernel logs. When I got to the security kernel, I noticed that the PSFT userid was disabled. Now, we normally dont login with that ID and hadnt for quite some time. Although, I do remember changing the password a few months ago. Anyway, I re-enabled the profile and reset the password to PSFT and everything worked great. Now, I guess we need to go and change the PSFT password everywhere before users come to work.
3177 day(s) ago   #25
Name: Chris
Category: CNC
Location: SG
Date: 2011-09-18 22:33:01
Replies(0)

Status:
  #25

How To Find EnterpriseOne Web Only Versions

Dealing with EnterpriseOne Web Only versions can be a real pain. They are deleted with each full package deployment and can cause a lot of confusion for the users. In order to find these troublesome quasi-objects you can run the sql below: select * from copy811/f983051 where VRVCC2 ='1'
3180 day(s) ago   #16
Name: Chris
Category: CNC
Location: SG
Date: 2011-09-15 20:00:20
Replies(1)

Status:
  #16

Access To Environments

To find out what users or groups have access to a certain environment run the following SQL: select * from sy811/f0093 where llll='<enviornment>'
3180 day(s) ago   #14
Name: TZ
Category: CNC
Location: Singapore
Date: 2011-09-15 02:55:10
Replies(0)

Status:
  #14

How To Copy OCM Records From One Environment To Another Using SQL

Here is the query-

INSERT INTO SY900/F986101( OMENHV, OMOBNM, OMDATP, OMUGRP,OMOAPP, OMDATM, OMSY, OMSTSO, OMPID, OMFUNO, OMUSER, OMJOBN, OMOCM2, OMUPMJ, OMUPMT) (SELECT OMENHV, '', OMDATP, OMUGRP, OMOAPP, OMDATM, OMSY, OMSTSO, 'SQL', OMFUNO, '', '', OMOCM2, <6DIGITDATE>, <6DIGITTIME> FROM SY900/F986101 WHERE OMOBNM='')

When using this SQL you will need to replace the following:
The target object to which you would like to copy the records.
(optional) The UserID that you would like to credit making this change.
<6DIGITDATE> & <6DIGITTIME> (optional) The JDE Date and Time that you are making the change.
The ObjectID of the OCM record that you wish to copy.

This method can also be used to copy OCM records from one environment to another by setting the environment in the select statement and where clause.
3180 day(s) ago   #13
Name: TZ
Category: CNC
Location: SG
Date: 2011-09-15 01:10:16
Replies(0)

Status:
  #13

Add Environments To User Or Group Using SQL

insert into sy900/f0093
values('48APINQ','JDV900','3.00','')
3181 day(s) ago   #11
Name: TZ
Category: CNC
Location: SG
Date: 2011-09-14 22:41:46
Replies(0)

Status:
  #11

Find out all checked out objects using SQL

select * from ol900/f9861 where sistce=3
Post Comments/Queries
Role:*
Subject:*
Email:*
Name:*
Details:*
 
   
* Please keep the forum clean.  * Your comments may subject to varification.  * Your email ID will not appear in the forum.
Related
JDE List JDE Source    JDE Upgrade JDE Fusion     The CNC Guy     Itz about ERP JDE Dev JDE Tech
E1 Tips & Tricks JDE CNC JDE Tips IT Toolbox JDE Tech Tips JDE ERP JDE Advisor JDE Wikipedia
 
Disclaimer: Most of the posts in this blog cater solutions/suggestions/workaround to issues for specific tools release or JDE E1 version and are just information only. Please be carefule while applying it in your environment. JDEthread will not be responsible for any data loss or spec corruption (if any).
Copyright © 2010 - 2020 JDEthread.in