About Me

My photo
I work for Fusion5 Australia . Connect with me on linked in here . I'm raising money for a good cause at the moment, follow donate to leukemia foundation

Thursday, 6 February 2014

JDE user overrides–a couple of facts that perhaps you did not know:

User overrides are storing more and becoming very important for users and productivity.  You can do lots with them, they follow you around – they are great.  But if you are going to harness the power of them, you need to know some of the pitfalls / nuances.  Here are a couple that are important.

In current releases, such as 8.97, new records will be created for each user when they launch the application.  For example, if there is a *PUBLIC override on P01012, when the user launches that application, a new record in the F98950 table will be created using that users ID.  This is working as designed and was a necessary change for the web client.  If changes to the *PUBLIC user override are made, delete the users current overrides.  Then the new *PUBLIC override will create the entries when the user launches the application.  Please note that this is wrong, In my testing (9.1.3.4) this does not occur unless you change the UO, when you change it and log out – it’ll save it as an individual UO – of course it will not change public.

When promoting an interactive application within Object Management Workbench (P98220), the *PUBLIC User Override records for the application will be automatically promoted when the interactive application is promoted. Therefore, it is not a requirement that *PUBLIC User Override records be added to a project in Object Management Workbench in order to be promoted. However, individual override record (i.e. User Override record by User ID/Role) will still need to be added in the Object Management Workbench in order for it to be promoted.  Wow, that is new for me…  Might drag up rubbish from DV910 that you did not intend!

Wednesday, 5 February 2014

JD Edwards seeing the content of your BLOBS (ooohhh errr)

I know it sounds a little personal, but I’ve often wanted to look into the contents of some ones UO (user overrride) BLOBs to verify problems.

It’s not overly simple, but with the help of friends in high places:  www.dba-oracle.com/t_convert_blob_varchar_datatype.htm

So, if you create the function then you can use it some SQL – first create the function F

create or replace function F(B BLOB)
return clob is
  c clob;
  n number;
begin
  if (b is null) then
    return null;
  end if;
  if (length(b)=0) then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  return c;
end;

Then use it in your SQL

select f(uobindta) from dv910.f98950
where UOOBNM = 'P5310' and uovers = 'CMS0002' and uouoty in ('GF','GD')
and uouser = '*PUBLIC';

You’ll get something like the following, note that this is a corruption:  Not ending with the correct tag.

"(CLOB) <?xml version="1.0" encoding="UTF-8"?>
<GridOverride><formatType>0</formatType><seqNumber>1</seqNumber><numCols>48</numCols><retCol>0</retCol><freezeCol>0</freezeCol><freezeRow>0</freezeRow><zoomFactor>100</zoomFactor><formatName>CMS</formatName><isPixel>1</isPixel><gridColumn><virtCol>1</virtCol><origCol>1</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>22</idObject><colWidth>68</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>MCU</alias><sortSeq>1</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>2</virtCol><origCol>2</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>23</idObject><colWidth>62</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>CONO</alias><sortSeq>2</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>3</virtCol><origCol>3</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>24</idObject><colWidth>221</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>DL01</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>13</virtCol><origCol>4</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>25</idObject><colWidth>60</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>REQB</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>4</virtCol><origCol>5</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>26</idObject><colWidth>46</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>BSTC</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>8</virtCol><origCol>6</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>27</idObject><colWidth>57</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>OREQ</alias><sortSeq>0</sortSeq><sortOr..."

 

See how this output is truncated in SQLDeveloper whether I run it as a script or just run it – a real pain.

 

I need to go “old school” on it to get all of the unadulterated data, I use a script like this is SQL plus:

spool F98950.txt
set linesize 30000
set long 80000
set pagesize 0
set wrap on
select f(uobindta) from dv910.f98950
where UOOBNM = 'P5310' and uovers = 'CMS0002' and uouoty in ('GF','GD')
and uouser = '*PUBLIC';
spool off

 

See below for a nicely truncated UO:

 

<?xml version="1.0" encoding="UTF-8"?>
<GridOverride><formatType>0</formatType><seqNumber>1</seqNumber><numCols>146</nu
mCols><retCol>19</retCol><freezeCol>4</freezeCol><freezeRow>-32767</freezeRow><z
oomFactor>100</zoomFactor><formatName>GW Format</formatName><isPixel>1</isPixel>
<gridColumn><virtCol>2</virtCol><origCol>1</origCol><backColor>-1</backColor><fo
reColor>0</foreColor><idObject>135</idObject><colWidth>235</colWidth><colHidden>
0</colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight
><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</u
nderline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><widt
h>0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><it
alic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>
DL01</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn
><virtCol>3</virtCol><origCol>2</origCol><backColor>-1</backColor><foreColor>0</
foreColor><idObject>137</idObject><colWidth>55</colWidth><colHidden>0</colHidden
><fontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1<
/charset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><st
rikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><
weight>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</ital
ic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>SCOS</alias>

<sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>5<
/virtCol><origCol>3</origCol><backColor>-1</backColor><foreColor>0</foreColor><i
dObject>167</idObject><colWidth>111</colWidth><colHidden>0</colHidden><fontHeade
r><height>-12</height><width>0</width><weight>700</weight><charset>1</charset><f
aceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</
strikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400<
/weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underli
ne>0</underline><strikeout>0</strikeout></fontCol><alias>OQTC</alias><sortSeq>0<
/sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>6</virtCol><o
rigCol>4</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>390
</idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><height>-12
</height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial
</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></f
ontHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><char
set>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underli
ne><strikeout>0</strikeout></fontCol><alias>CO</alias><sortSeq>0</sortSeq><sortO
rder>A</sortOrder></gridColumn><gridColumn><virtCol>7</virtCol><origCol>5</origC
ol><backColor>-1</backColor><foreColor>0</foreColor><idObject>163</idObject><col
Width>126</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><wid
th>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><i
talic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><fo
ntCol><height>-12</height><width>0</width><weight>400</weight><charset>1</charse
t><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeout
>0</strikeout></fontCol><alias>CURO</alias><sortSeq>0</sortSeq><sortOrder>A</sor
tOrder></gridColumn><gridColumn><virtCol>8</virtCol><origCol>6</origCol><backCol
or>-1</backColor><foreColor>0</foreColor><idObject>138</idObject><colWidth>111</
colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width
><weight>700</weight><charset>1</charset><faceName>Arial</faceName><italic>0</it
alic><underline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><heig
ht>-12</height><width>0</width><weight>400</weight><charset>1</charset><faceName
>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeo
ut></fontCol><alias>COAM</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gr

idColumn><gridColumn><virtCol>13</virtCol><origCol>7</origCol><backColor>-1</bac
kColor><foreColor>0</foreColor><idObject>180</idObject><colWidth>112</colWidth><
colHidden>0</colHidden><fontHeader><height>-12</height><width>0</width><weight>7
00</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><unde
rline>0</underline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</he
ight><width>0</width><weight>400</weight><charset>1</charset><faceName>Arial</fa
ceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontC
ol><alias>ROAM</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><
gridColumn><virtCol>14</virtCol><origCol>8</origCol><backColor>-1</backColor><fo
reColor>0</foreColor><idObject>148</idObject><colWidth>0</colWidth><colHidden>1<
/colHidden><fontHeader><height>-12</height><width>0</width><weight>700</weight><
charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</und
erline><strikeout>0</strikeout></fontHeader><fontCol><height>-12</height><width>
0</width><weight>400</weight><charset>1</charset><faceName>Arial</faceName><ital
ic>0</italic><underline>0</underline><strikeout>0</strikeout></fontCol><alias>MC
U</alias><sortSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><v
irtCol>15</virtCol><origCol>9</origCol><backColor>-1</backColor><foreColor>0</fo
reColor><idObject>149</idObject><colWidth>0</colWidth><colHidden>1</colHidden><f
ontHeader><height>-12</height><width>0</width><weight>700</weight><charset>1</ch
arset><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strik
eout>0</strikeout></fontHeader><fontCol><height>-12</height><width>0</width><wei
ght>400</weight><charset>1</charset><faceName>Arial</faceName><italic>0</italic>
<underline>0</underline><strikeout>0</strikeout></fontCol><alias>CONO</alias><so
rtSeq>0</sortSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>16</v
irtCol><origCol>10</origCol><backColor>-1</backColor><foreColor>0</foreColor><id
Object>150</idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><
height>-12</height><width>0</width><weight>700</weight><charset>1</charset><face
Name>Arial</faceName><italic>0</italic><underline>0</underline><strikeout>0</str
ikeout></fontHeader><fontCol><height>-12</height><width>0</width><weight>400</we
ight><charset>1</charset><faceName>Arial</faceName><italic>0</italic><underline>
0</underline><strikeout>0</strikeout></fontCol><alias>REQB</alias><sortSeq>0</so
rtSeq><sortOrder>A</sortOrder></gridColumn><gridColumn><virtCol>17</virtCol><ori
gCol>11</origCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>253<
/idObject><colWidth>0</colWidth><colHidden>1</colHidden><fontHeader><height>-12<
/height><width>0</width><weight>700</weight><charset>1</charset><faceName>Arial<
/faceName><italic>0</italic><underline>0</underline><strikeout>0</strikeout></fo
ntHeader><fontCol><height>-12</height><width>0</width><weight>400</weight><chars
et>1</charset><faceName>Arial</faceName><italic>0</italic><underline>0</underlin
e><strikeout>0</strikeout></fontCol><alias>RQBD</alias><sortSeq>0</sortSeq><sort
Order>A</sortOrder></gridColumn><gridColumn><virtCol>4</virtCol><origCol>12</ori
gCol><backColor>-1</backColor><foreColor>0</foreColor><idObject>134</idObject><c
olWidth>72</colWidth><colHidden>0</colHidden><fontHeader><height>-12</height><wi
dth>0</width><weight>700</weight><charset>1</charset><faceName>Arial</faceName><
italic>0</italic><underline>0</underline><strikeout>0</strikeout></fontHeader><f
ontCol><height>-12</height><width>0</width><weight>400</weight><charset>1</chars
et><faceName>Arial</faceName><italic>0</italic><underline>0</underline><strikeou
t>0</strikeout></fontCol><alias>LNID</alias><sortSeq>1</sortSeq><sortOrder>A</so
rtOrder></gridColumn><gridColumn><virtCol>18</virtCol><origCol>13</origCol><back
Color>-1</backColor><foreColor>0</foreColor><idObject>254</idObject><colWidth>0<
/colWidth><colHidden>1</colHidden><fontH

 

When a GD and GF is saved properly, the output is more like the below (note that this is 50Kb for a single UO!!!!

<foreColor>0</foreColor>
<idObject>121</idObject>
<colWidth>65</colWidth>
<colHidden>0</colHidden>
<colFrzHidden>0</colFrzHidden>
<colFrozen>0</colFrozen>
<fontHeader>
<height>-12</height>
<width>0</width>
<weight>700</weight>
<charset>1</charset>
<faceName>Arial</faceName>
<italic>0</italic>
<underline>0</underline>
<strikeout>0</strikeout>
</fontHeader>
<fontCol>
<height>-12</height>
<width>0</width>
<weight>400</weight>
<charset>1</charset>
<faceName>Arial</faceName>
<italic>0</italic>
<underline>0</underline>
<strikeout>0</strikeout>
</fontCol>
<alias>USER</alias>
<sortSeq>0</sortSeq>
<sortOrder>A</sortOrder>
</gridColumn>
</GridOverride>

Types of user overrides for JDE JD Edwards 9.1

CK : User Preference Cookies

(what is saved in user preferences – Enable Hover Forms etc)

GF : Grid Format

(added when user adds a frid format) One per version/form/sub form

GD : Grid Format Detail

UO records created when user adds a grid format, one record per grid format.

'GD' records are not shown in P98950 application but shown in UTB or Data Browser. Only GF record is shown in P98950 application. When a user deletes a GF record in P98950, GD records for the form are also deleted.

Note that these can become corrupt, especially after an upgrade.  Note that one bad GD can spoil many good ones, as they are loaded all together and then it’s determined which ones will be shown.

You’ll get a message like the below if you have corrupt GD’s.  Remember you are going to need to get rid of them with SQL, as they do not come up in P98950.

05 Feb 2014 09:24:23,909 [Line -1] [SEVERE] SMOIR - [RUNTIME] GridTabFormatModelCache.byteArrayToXMLDocument(): SAXException. org.xml.sax.SAXParseException: XML document structures must start and end within the same entity.

HC : Hyper Control

Fat boy remebers menu and toolbar preferences

JC: Legacy Type (also known as Job Cost)

OL : OLE Object

PC : Portal Component

PP : Portlet Preferences

SQ : Saved Query

When you save a Query using the save query control.  Data browser or form

TT : Translation Tool Settings

FA : Favorites / Favourites

These are them, remember this

AQ : Advanced Query

Any advanced queries that you save

UI : E1 Pages

RI : Related Information Application Framework

Tuesday, 4 February 2014

iPad Application for E1

If you want to try this and are not on the latest tools release, then it’s still possible.  What is the iPad application you ask?  It’s a fully functioning JD Edwards application for the iPad.  It does some really nice interface things to make the iPad for functional for the end user. 

So, what do you need to do?

1.  goto the app Store on your IPad and search for Jd Edwards IPad application

image

2.  Install it

3.  Enter your website URL, if you don’t have one – use ours:  http://e91demo1.myriad-it.com/jde

photo

4.  Enter your JDE username and password

image

NOTE:  If you do not have a demo account with Myriad, then register for one here:  http://e91demo.myriad-it.com/jde/E1Menu.maf  this registration is immediate.

Enjoy – now was that not easy!

image

Remember that if your company does not expose the URL to the internet, you’re going to need an VPN connection to login in.

JD Edwards and LDAP integration–easily support long usernames and long passwords in JDE

There is no typo in the page title, it’s possible to support long usernames and long passwords with JD Edwards using a simple IIS application that we can provide for you.  The myLUNCH (Long UserName Credential Handler) product not only has one of the best names in the industry, it also works like a treat! 

MyLunch is able to authenticate users using their LDAP (Microsoft Active Directory) username and password, not matter what the length or complexity!  The product can either authenticate natively (good for some users) or via LDAP on a user by user basis.

The product is a very simple IIS site that can be setup in hours, it can also do load balancing of your production web servers using it’s built in multiple destination functionality.

Instead of getting the JDEdwards login page, we direct the user to a New login page asking for LDAP credentials.  If the user attempts to login, the software firstly determines if the username is an LDAP user or a JD Edwards authentication user. As soon as this is determined, the authentication method is executed against the relevant target.  The user is then simply signed into JD Edwards.

This can be done via SSL or standard sockets:

image

Enter your domain username and password of any length

image

You are signed into E1.

When you sign out you are back to the customised login page.  It’s that simple.

We give you the ability to map your long usernames with your JD Edwards usernames:

image

Any changes of mapping are written to the standard security audit tables (F9312).

This works with shortcuts too.

So, this is a simple JD Edwards solution for allowing users with long usernames and passwords to log into JD Edwards.  Remember that ANY solution will have some username translation, as the DD field in e1 for USER (audit stamps, users etc) is only 10 characters long.

The software includes the ability to bulk load the JD Edwards tables with the usernames and mappings, so you can convert to AD authentication in minutes, not days. 

Contact me at shannon. moir @ myriad-it . com for a demo.  I plan to post a youtube video soon.