How are you using E1 Pages? Share your E1 Pages in our E1 Pages Repository!
Want example on how to use E1 Pages? Check out our E1 Pages Repository!
E1 Pages: What Are They?
What are E1 Pages?
From the Oracle documentation:
EnterpriseOne (E1) Pages are a mechanism for embedding HTML based content directly into E1. Content can include:
- Business Process Flows
- Other Task Based Navigation
- External URLs (e.g. www.oracle.com )
- Embedded links to BI Analytics Pages
- Corporate Branding
- Documentation
- Any Rich HTML Content
In short, they are a collection of HTML Pages which can be assigned to a user, role or *PUBLIC and effectively serve as a simpler version of a Portal Solution. Upon being published to a given user, these E1 Pages appear as a set of tabs to the user upon logging into EnterpriseOne.
Oracle Documentation:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1401833.1
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1394280.1
How are you using E1 Pages? Share your E1 Pages in our E1 Pages Repository!
Want example on how to use E1 Pages? Check out our E1 Pages Repository!
How To Change The Schema Of All Tables In A SQL Server Database
Below is the SQL that can be used to change the database schema of all tables in an SQL Server Database. We use it when restoring databases from Production to CRP or Development.
USE JDE_CRP_NEW --TODO: Change this to DB Name
SET NOCOUNT ON
DECLARE @NewSchema nvarchar(256)
DECLARE @OldSchema nvarchar(256)
Set @NewSchema = 'CRPDTA'; --TODO: Change to New Schema
Set @OldSchema = 'PRODDTA'; --TODO: Change to Old Schema
--Don’t need to change anything below this
DECLARE @TableName nvarchar(256)
DECLARE TableName CURSOR FOR
SELECT DISTINCT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @OldSchema
OPEN TableName
FETCH TableName INTO @TableName
WHILE @@Fetch_Status = 0
BEGIN
Print 'Modifying ' + @TableName
EXEC('ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName)
FETCH TableName INTO @TableName
END
CLOSE TableName
DEALLOCATE TableName
Oracle JDEdwards EnterpriseOne in FireFox 20
As some may have noticed, the latest version of FireFox breaks Oracle JDEdwards EnterpriseOne.
Well, we found a fix on JDEList posted by cpb (Charles Baker):
Add the following to JAS server browscap.ini:
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; MOZILLA FIREFOX 20.0 [MOZILLA FIREFOX 20.0] parent=MOZILLA FIREFOX 10.0 version=20.0 majorver=20 minorver=0 [*(X11*) Gecko/* Firefox/2?.*] parent=MOZILLA FIREFOX 20.0 platform=Linux [*(Mac*) Gecko/* Firefox/2?.*] parent=MOZILLA FIREFOX 20.0 platform=Mac [*(Win*) Gecko/* Firefox/2?.*] parent=MOZILLA FIREFOX 20.0 platform=Win
You will want to make this change in the browscap.ini file located in your Server Manager Agent target directory, which may be something like this:
E:\jde_home\SCFHA\targets\pd83-1\owl_deployment\webclient.ear\app\webclient.war\WEB-INF\classes
How To Send Email Attachments Using Native Oracle JDEdwards EnterpriseOne
On his blog, Shannon Moir has a great post, ”Simple JDE BSFN to send an email with an attachment”, about how to send email attachments using a custom Business Function (BSFN).
From the post:
It’s a BSFN that takes a few parameters:
- to
- From
- file to attach
- name of attachment
- subject
- Body
- CC
- BCC
The source code can be found here.
Great post, Shannon! Thanks!
Finding The Row Count For All Files In An iSeries Library And All Tables In An SQL Server Database
I’ve been moving several EnterpriseOne databases to SQL Server lately. In order 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 > 0SQL 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 > 0NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.
Are there any methods that you use to do a quick data integrity check?
How To Drop All Tables In An SQL Server Database
I’ve been doing a lot of work with SQL Server lately and have found the following SQL that can be used to drop (delete) all tables in an SQL Server database:
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
Do you have any really handy SQLs? Post them in the comments.
Great iSeries SQL Reference When Working With Dates & Times
I really used this page a lot when working with SQL and dates & times.
DB2 Basics: Fun With Dates and Times
End Subsystem Jobs Using SQL
We have noticed many orphaned subsystem jobs piling up in the F986113.
In tracking them down we found that the way we stop the subsystem jobs each night for things like backups, nightly processing, etc. may have been causing jobs to just sit there and not be processed. The process that was in place just deleted the subsystem processing job records from the F986113.
I did some research and found that an article on JDEList.com says to essentially use SQL to set the subsystem processing jobs to an “ERROR” state.
That didn’t seem quite right to me either. So, I set out to find out what the application does to end the subsystem jobs and found that it inserts a record into the F986113 that triggers the job to end.
Below is the that can be used to end the subsystem jobs gracefully:
insert into svm900.f986113 (SSPID,SSVERS,SSSBMDATE,SSSBMTIME,SSJOBSTS,SSOPCR,SSENHV,SSJOBNBR,SSUSER,SSORGHOST,SSJOBPTY,SSEXEHOST) select SSPID,SSVERS,SSSBMDATE,SSSBMTIME+1,'R','W',SSENHV,SSJOBNBR,'JDEPKG',SSORGHOST,'0',SSEXEHOST FROM SVM900.F986113 WHERE SSPID='R43500'
EnterpriseOne SubSystem Jobs Not Starting
For a while now we have had an issue where our subsystem Purchase Order Print job (R43500) will not run in JPD900 but it will in PD900. After going through the debug log I found a something:
--UBE--[0]-- SS0000009:Subsystem Limit is reached. --UBE--[0]-- SS:Cannot start new subsystem.
Above this notification I found an SQL was ran looking for subsystem jobs in the F986113:
SELECT COUNT(*) FROM SVM900/F986113 WHERE ( SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 AND SSEXEHOST = MODENT AND SSJOBSTS = S )
The results of this SQL was 3. Which is the UBE Subsystem Limit specified in the Enterprise Servers JDE.INI [UBE] section (Configurations – Batch Processing from Oracle Server Manager). 3 is also the default value.
However, there is a problem with this SQL querie. It only finds the number of subsystem jobs that have been submitted. It doesnt check to see if they are running. If all subsystem jobs have been stopped properly, there would be zero records returned from this SQL. However, if you have had any issues with subsystem jobs or even if things dont get cleaned up correctly when bouncing services, you can end up with subsystem jobs in an error state.
The only way to clean up these records is to go into the database and remove them:
DELETE FROM SVM900/F986113 WHERE (SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 AND SSEXEHOST = MODENT AND SSJOBSTS = S AND SSOPCR=E)
Do you have any tips for SubSystem jobs? If so, please leave them in the comments.
