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

Wednesday, 12 September 2018

hacky post 2–bulk promotion of UDOs (specifically cafe1)

This really follows on from the last post (not the emotive Aussie song played on ANZAC day), but my last post about UDO’s and renames.

This involves synchronisation of UDO’s that are cafe1 screens that point to an E1page.

As you know, if you promote and e1page (or create it again), it’ll have a different URL – depending on the environment that you are signed into.  They cannot work with a relative path (HEY – JDE enhancement idea)!  So, if you have a e1page that does some nice AIS lookups and renders some cool information, there is a bit of admin getting this promoted.

So, captain hack comes to the rescue.

First, we are dealing with F9861W (for pathcode related UDO records), they are seen in P98220u in JDE.  These are actually pointers to F952450, which are the actual UDO’s – there is a blob field.

below is the view of P98220u for cafe1 (composite app framework if you are posh)image

When looking at the BLOB, we can work out what we need to change:

select convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB)) from JDE_PY920.PY920.F952450
where UPPER(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB))) like '%MYDEMO.FUSION5.COM%';

Which reveals something like the following – the actual cafe1 definition:

--<?xml version="1.0" encoding="UTF-16"?> <RIAF CATALOGMAX="0" VERSION="1"><CONTAINER CONTAINER_TYPE="2"         LAYOUT_TYPE="1" MODE="0" PERCENTAGE="100.0" PREFER_OBJECT_ID=""             WIN_STATE="0"><CONTAINER CONTAINER_TYPE="1" LAYOUT_TYPE="2"             MODE="0" PERCENTAGE="71.15" PREFER_OBJECT_ID=""             WIN_STATE="0"/><CONTAINER CONTAINER_TYPE="2" LAYOUT_TYPE="2"             MODE="0" PERCENTAGE="28.85" PREFER_OBJECT_ID="1534329490322"         WIN_STATE="0"/></CONTAINER><CONTENT ID="1534329490322"><OBJECTTYPE>GENERICURL</OBJECTTYPE><TITLE><TABNAME>View MO Attachment</TABNAME><TABDESCRIPTION>View MO Attachment</TABDESCRIPTION></TITLE><CONTENTDATA><PR_PATH_TYPE>0</PR_PATH_TYPE><PR_TEMPLATE>https://mydemo.fusion5.com/jde/e1pages/E1P_1808150001CUST_55/home.e1page?struct=AB&amp;key=100
--</PR_TEMPLATE><PR_PARTS><PR_PART><PR_INDEX>11</PR_INDEX><PR_DESCRIPTION>struct</PR_DESCRIPTION><PR_COMPONENT_LIST><PR_CUSTOMIZED_TEXT_COMPONENT>GT0411S</PR_CUSTOMIZED_TEXT_COMPONENT></PR_COMPONENT_LIST></PR_PART><PR_PART><PR_INDEX>13</PR_INDEX><PR_DESCRIPTION>key</PR_DESCRIPTION><PR_COMPONENT_LIST><PR_CONTROL_COMPONENT>GC0_1.80</PR_CONTROL_COMPONENT><PR_SEPARATOR_COMPONENT>|</PR_SEPARATOR_COMPONENT><PR_CONTROL_COMPONENT>GC0_1.27</PR_CONTROL_COMPONENT><PR_SEPARATOR_COMPONENT>|</PR_SEPARATOR_COMPONENT><PR_CONTROL_COMPONENT>GC0_1.26</PR_CONTROL_COMPONENT><PR_SEPARATOR_COMPONENT>|</PR_SEPARATOR_COMPONENT><PR_CONTROL_COMPONENT>GC0_1.47</PR_CONTROL_COMPONENT></PR_COMPONENT_LIST></PR_PART></PR_PARTS></CONTENTDATA><KEYCTRLST/><KEYVALLST/><KEYALIALST/><URLUSER>SHANNONM</URLUSER><USER>SHANNONM</USER><UPMJ>2018-08-15</UPMJ><UPMT>212531</UPMT></CONTENT><TITLECATALOG><TITLE><TABNAME>View MO Attachment</TABNAME><TABDESCRIPTION>View MO Attachment</TABDESCRIPTION><CONTENTID>1534329490322</CONTENTID></TITLE></TITLECATALOG></RIAF>

Nice, so again, the power of SQL I can actually change this without checking out the UDO, making the change and checking it back in.

create a backup file of what you want to change

SET IMPLICIT_TRANSACTIONS ON ;

select * into JDE_PD920.PD920.F952450SRM from JDE_PD920.PD920.F952450 ;

commit;

UPDATE JDE_PD920.PD920.F952450SRM
set WOOMRBLOB = replace(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB)),’FROMVALUE.com.au', ‘TOVALUE.com.au')
where UPPER(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB))) like '%FROMVALUE.COM%';

commit;

okay, now we have modified all the UDO’s, we can put them into another environment!  I’m inserting into PY from PD – after changing those URLS

insert into jde_py920.py920.F952450 select * from pjdesql01.JDE_PD920.PD920.F952450SRM
where UPPER(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB))) like '%TOVALUE.COM%';

commit;

But they are not in P98220U – What?

Need to do P9861W

select * from pjdesql01.jde920.ol920.f9861W
where SIPATHCD = 'PD920'
--and SIWOBNM like 'CAF%'
and SIUSER = 'SHANNONM';

select * into JDE_PY920.PY920.f9861WSRM
from pjdesql01.jde920.ol920.f9861W
where SIPATHCD = 'PD920'
and SIWOBNM like 'CAF%'
and SIUSER = 'SHANNONM';

select * from JDE_PY920.PY920.f9861WSRM ;

update JDE_PY920.PY920.f9861WSRM set sipathcd = 'PY920' ;

insert into pjdesql01.jde920.ol920.f9861W select * from JDE_PY920.PY920.f9861WSRM;

drop table JDE_PY920.PY920.f9861WSRM;

Done!  You’ll see that my selection criteria is specific for my use case, you could join F9861W to F952450 to get all of the items that you need.

Friday, 7 September 2018

Hacky post... cafe1 bulk rename / change of attributes

Once again, it takes me a while to get to the meat of the post, but I want to set the scene of what has been done so that I can finally describe the work around.

I've recently been involved with integrating sharepoint with JD Edwards for media object storage. 

This is a fairly complex solution, but it's highly strategic.

This is another way of reducing the need for the deployment server and also being more efficient on how media objects work.  If you can store all of your media objects in SharePoint (@sharepoint.com) too, then you do not need to worry about backups and recovery and stuff and stuff.  You can then think about writing some cool power apps that might feed from the media objects (scanned images for example), and drill back to JDE - that'd be nice.

There are 3 major deliverables for their piece of work:

  1. Historical media object conversion
  2. Ad hoc upload of files from JDE to sharepoint
  3. display context sensitive media objects to end user
1 is easy, we used a powershell script that uploads all of the physical files to sharepoint.  We created some different directories and put the objects in relevant folders in SharePoint.

2. Slight challenge, but there is an existing UBE that uploads all of the scanned images.  This was modified slightly (caressed) to call a powershell script that too, uploads the file immediately to sharepoint and then writes an F00165 records (type 5) for consumption down the track.

3.  Finally - the hardest bit.  Displaying the MO's natively in E1.  This was a bit of a challenge.  We needed to write an e1page that would take a couple of parameters (GDTXKY GDOBNM) and do an AIS call to find the relevant MO's and then display this content.  EASY!!  NO, it was not.  This was difficult because of the cross domain content rules using sharepoint online.  You cannot natively display the contents of sharepoint in an iframe.  A REAL PAIN!

Azure functions to the rescue.  The team wrote an azure function that takes the URL for the attachment as a parameter and actually sends back the binary representation of the file.  So that we get around all of the cross domain security problems.  This was painful, but at the end of the day it works.  Once this e1page has been created, all you need to do is hook up cafe1's to reference the e1page and pass in the data from the forms to generate the GDOBNM and the GTTXKY

This shows flexibility in assigning the MO window URL

This is configuration NOT code... Actually think about the solution above...  It's basically all configuration in JDE, not code.  We are using lots of UDOs to get this working.  If you give your users access to the e1page and also access to the cafe1's - they can all see their PDF's in the cafe1 window...  Oh - and they do not need to download them first.


Okay, so now we are at the point where I'm describing my problem.  I created 27 e1pages that reference the test website jdepd920.client.com, but this is going to change to jde.client.com on the go-live weekend.  I do not want to edit 27 UDO's to cater for this.

So, SQL to the rescue.  Clever JDE store the UDO's as an XML document in the BLOB.  Don't bother trying to hack the e1page on the server, that is going to be replaced all of the time.  You need to hack the e1page definition in F952450.


--backup
select * into JDE_DV920.DV920.F952450SRM from JDE_DV920.DV920.F952450 ;

--update
UPDATE JDE_DV920.DV920.F952450
set WOOMRBLOB = replace(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB)),'jdedv920.client.com.au', 'jdedv.client.com.au') 
where UPPER(convert(varchar(max) , convert (varbinary (max) , WOOMRBLOB))) like '%JDEDV920%'
--WHERE WOWOOBNMS like '%MO%';

Great, so I was just able to update the URL for 27 UDOs (and any personal copies) with one statement.  This would have taken me a long time to reserve them all, edit, share  etc.
Native sharepoint window / attachment without the user doing ANYTHING.  No clicking paper clip and no downloading PDFs.
 Normal warnings, you need to be very careful changing this sort of stuff.  Test in lower environments too!




Monday, 27 August 2018

Infocus and Summit–what did I learn?

Firstly it was great to see so many familiar faces, and meet some new people.  I had a number of people come and say hi at the conference and said thanks for blogging – that may have been my favourite part of the conference, for the people that did come and say hi – I say thanks!

Infocus is a quest run customer event, specific for JD Edwards clients and partners.   This was followed by a 2 day partner conference, known as summit.  Both conferences were in Denver Colorado, who spoilt attendees with 30 degree (celsius) days with 0 humidity.

I must admit that when I went into the conference, I was worried.  I was a little worried about investing strategically into JD Edwards.  My thoughts were somewhat reiterated when I did not hear about any huge announcements.  But, then I heard Lyle at summit (a lot of clients will not hear this), and I was once again excited about what was to come.  I don’t know what specifically Lyle did say to change my opinion, but it was made up of the following:

  • Acknowledgement of the formation of a specific sales team at oracle that are rewarded and driven to sell JDE – cool
  • Premiere support of JD Edwards 9.2 to at least 2030 – amazing!
  • That NetSuite and cloud ERP are going to be the dominant SaaS offerings, but there is a lot of whitespace between these two products
  • Lyle showed passion for JD Edwards and wanting to “protect his turf” when it came to SaaS sales trying to take away the JDE opportunities
  • reiteration of what JDE is and what it can do for an organisation -
    • awesome integration is easy now with AIS –> orchestration
    • awesome integration with Cafe1
    • useability through the roof with all of the UDO’s that are supporting citizen development
  • Remember that ERP’s (especially mature ones) are going to create sales orders, track stock and do all of the basic transactions that you need.  JD Edwards especially is a mature product that will do ALL of these things easily.  Now you need to look at how you are going to augment this base functionality.  You’ll do that by embracing all mega-trends – vis-a-vis this diagram:


image

In this diagram I’ve tried to show that JD Edwards is awesome and does what it is told.  Great security, great database, stable, single source of truth.  As clients we need to respect master data and expose JDE securely using great integration (AIS / Orchestration) to enable amazing technology to be used and consumed side by side with your ERP.  You do not need to wait for AI enabled ERP – you can have it today!

This is being put into action today,

  • Fusion5 are already using AI to interpret images attached to media objects and indexing and reporting on these.  This is a SIMPLE integration that can be plugged into any public cloud ERP implementation.  And this is just the beginning.
  • Fusion5 are integrating IOT devices and raising work orders when things get too hot or too humid – this is being done today.
  • Any many more examples of using the strength of your core ERP – but being innovative about hooking into new technology.

But, how can everyone do this? I personally think that you need 3 capabilities to deliver continuous innovation.

image

Both summit and infocus reiterated the need for all clients to get to 9.2 and start to embrace configuration not code.  reduce your technical debt by retiring modifications, which will allow you to embrace continuous innovation.

Get a partner to help your (like fusion5) adopt continuous innovation, but keeping you code current.  Fusion5 have CD3 (continuous development, continuous deployment, continuous delivery) which allows continuous innovation.  It’s important to start to get economies of scale by getting a partner to help you stay code current and allow you to focus on business while we take are of the platform.

If you are looking for ways that you can reinvigorate your JD Edwards and your passion for innovation, think about having a hackathon, or perhaps an innovation bootcamp.  We run these internally and for clients and they are a great way of coming up with ideas that could make a difference to your business or perhaps change an industry.

I attended a couple of great interactive sessions on 1 click provisioning and 64bit JD Edwards – two things that are going to affect you and your JD Edwards installation positively.  I also attended a great session on containers and JDE, if you are not looking into this technology (as I have said before) you should be.  This is going to change the way we think about large systems.

9.2.3 is going to contain some great enhancements (especially in orchestration) and you should look closely for the next large oracle conference (Open World?) for this to be announced.

My summary

What it lacked in large announcements, it made up for in core messaging and core product acknowledgment.  Embrace continuous delivery and find a partner to help you.  Embrace innovation and find a partner to help you.  JD Edwards is here for the long haul, use it’s strengths and augment and extend to the cloud – pugging in AI, ML and other megatrends to improve your decision making capabilities.  Configure your ERP, don’t modify it.

Thursday, 16 August 2018

Use Google to Find My Oracle Support Content


What??  This could be great.  Use google search for https://support.oracle.com 
You can now use Google and other search engines to find My Oracle Support Content!
Go to Google and search using all of the Google capabilities you have come to rely on.
Even better, bookmark site:support.oracle.com and search Oracle content exclusively.


Nice announcement in Nov last year, but does not really provide the results that I need.

For example: site:support.oracle.com e1 jvm size



Wow, how cool! or is it?  I'm going to need to fine tune my searching




Not really what I want - not enough information

but...

I get a lot more information from the actual support site.  I get a lot more suggestions and quality documentation.



Can I find anything in particular?  Let's search for exact knowledge documents, cannot find them...

I cannot find this for example:



A bit of a toy, a bit hit an miss for me.

Wednesday, 15 August 2018

powershell command line options starter kit

I don’t know how I’ve been able to do this until now, but I’ve avoided powershell until last week.

I might quickly become addicted to it though, after my brief introduction.

I have a requirement to upload files into sharepoint from a UBE, simple enough.

I got some amazing fusion5 people to cut me a script, and it’s great for 90% of scenarios, but I need to do a couple of mods for 100% of scenarios.

My modifications revolve around the ability to process parameters, $? $# etc – I miss ksh.

So, here is some tips for me in 3 months time when I need to do this again.

First and foremost, your Param function needs to be 1st line of the script!


Param (
     [string]$filetoupload,
     [string]$username = $( Read-Host "Input username, please" ),
     [string]$password = $( Read-Host "Input password, please" ),
     [switch]$force = $false,
     [switch]$run = $false
)

This terseness is AWESOME!

When calling this function with the following, it assigns all of the variables

PS C:\fusion5> .\uploadContentsToSP_BM_Test_singlefile.ps1 -filetoupload c:\shannon.pd -username shannon.moir@fusion5.com.au -password hello

So, if I did a:

write-host $username

in the script, it’d tell me the username.  The other nice thing is that if you do not specify the parameter and there is a $read-Host directive, it’s going to prompt only for the items that have not been entered.  So cool!

Also, you’ll see that I’ve used a mix of [switch] and [string] parameters.  string of course is a variable, but switch is cool – it’s binary

if your code, you can then use:


if ($force) {
write-host "file to upoload: " $filetoupload
}

So therefore, if the script is called with –force then it’ll execute the write-host function – easy


Param (

    [Parameter(Mandatory=$true)]
    [string]$filetoupload,
     [string]$username = $( Read-Host "Input username, please" ),
     [string]$password = $( Read-Host "Input password, please" ),
     [switch]$force = $false,
     [switch]$run = $false
)

Calling this:

PS C:\fusion5> .\uploadContentsToSP_BM_Test_singlefile.ps1 -filetouplad c:\shannon.pd -username shannon.moir@fusion5.com.au -password hello
C:\fusion5\uploadContentsToSP_BM_Test_singleFile.ps1 : A parameter cannot be found that matches parameter name
'filetouplad'.
At line:1 char:45
+ .\uploadContentsToSP_BM_Test_singlefile.ps1 -filetouplad c:\shannon.p ...
+                                             ~~~~~~~~~~~~
     + CategoryInfo          : InvalidArgument: (:) [uploadContentsT..._singleFile.ps1], ParameterBindingException
     + FullyQualifiedErrorId : NamedParameterNotFound,uploadContentsToSP_BM_Test_singleFile.ps1

So if you do not specify the mandatory parameter (see that I have a spelling mistake) we get the error that the parameter has not been specified

It could be more graceful if you manage it (less big red writing)

The directive only affects the following parameter, not all of them.

Tuesday, 14 August 2018

SQL Server miracles

I have not used SQL Server for ages, in fact – it’s been spotty throughout my career.

But, I was just doing some hectic Media Object massaging (F00165) – I’m converting them from type 1 (physical files) to type 5 HTML links.

My SQL caused a duplicate for a large insert, but look at what SQL tells me!!!

image

It tells me the bad record!!!

How many group by queries has this just saved me, so awesome.

Monday, 13 August 2018

Oracle SE2 license clarification for oracle technology foundation



I think it's really important to know your rights when it comes to database licensing and the cloud.

I'm only going to talk about the database here, that it to say - oracle SE2.

What can I do with SE2, are there going to be significant performance issues if I used SE2? Perhaps, or perhaps not...



Description of support for SE2:In September, 2015 Oracle announced the withdrawal of Oracle Database Standard Edition and the availability of a new product, Oracle Database Standard Edition 2. This announcement is relevant for all JD Edwards customers who have licensed Oracle Technology Foundation for JD Edwards EnterpriseOne because this product includes a limited use license for Oracle Database Standard Edition.In light of the new Oracle Database Standard Edition 2 product offering, Oracle Technology Foundation for JD Edwards EnterpriseOne has been updated to include a restricted use license of Oracle Database Standard Edition 2. Refer to the JD Edwards EnterpriseOne Licensing Information User Manual for a detailed description of the restricted use licenses provided in the Oracle Technology Foundation for JD Edwards EnterpriseOne product.


augmented with


Licence informationOracle Technology Foundation for JD Edwards EnterpriseOne may be licensed instead of EnterpriseOne Core Tools and Infrastructure for customers wanting the Oracle components but are not currently licensed for EnterpriseOne Core Tools and Infrastructure. The Oracle components included with Oracle Technology Foundation for JD Edwards EnterpriseOne are listed under "Entitled Products and Restricted User Licenses" below.Oracle Technology Foundation for JD Edwards EnterpriseOne would cover the JD Edwards EnterpriseOne Core Tools and Infrastructure prerequisite requirement.
Entitled Products and Restricted Use LicensesA license for Oracle Technology Foundation for JD Edwards EnterpriseOne includes the restricted-use licenses of: Oracle Database Standard Edition 2; Oracle Internet Application Server Standard Edition; Oracle WebLogic Server Standard Edition; JRockit JVM; Oracle Application Server Portal; Oracle WebCenter Services; Oracle BPEL Process Manager; Oracle Business Activity Monitoring; Oracle Application Server Single Sign-On; Oracle Access Manager Basic; Oracle Application Server Web Cache; and Business Intelligence Publisher (formerly XML Publisher).
As noted in the preceding paragraph, a license for Oracle Technology Foundation for JD Edwards EnterpriseOne includes a restricted-use license for Oracle Database Standard Edition 2. Oracle Database Standard Edition 2 may be used solely in conjunction with any and all JD Edwards EnterpriseOne programs licensed under your agreement, including third party programs licensed for use with JD Edwards EnterpriseOne programs. Oracle Database Standard Edition 2 may be installed on an unlimited number of processors. When used with Oracle Real Application Clusters, Oracle Database Standard Edition 2 may be installed on any number of RAC nodes. If you require features and functions beyond those included with the Oracle Database Standard Edition 2, or if you require use of Oracle Database beyond your JD Edwards EnterpriseOne implementation, you may purchase a non-limited use license by contracting directly with Oracle or one of its authorized distributors.
A license for Oracle Technology Foundation for JD Edwards EnterpriseOne also includes a restricted-use license for the following components of Oracle Fusion Middleware: Oracle Application Server Standard Edition or Oracle WebLogic Server Standard Edition (either of these products may be used, but both products cannot be used for the same function); Oracle Application Server Portal; Oracle WebCenter Services; Oracle BPEL Process Manager; Oracle Business Activity Monitoring; Oracle Application Server Single Sign-On; Oracle Access Manager Basic; Oracle Application Server Web Cache; and Business Intelligence Publisher. These components may be used solely in conjunction with any and all JD Edwards EnterpriseOne programs licensed under your agreement, including third party programs licensed for use with JD Edwards EnterpriseOne programs. These components may be installed on an unlimited number of processors. If you require use of these components beyond your JD Edwards EnterpriseOne implementation you may purchase a non-limited use license for any of the Oracle components by contracting directly with Oracle or one of its authorized distributors.
As noted in the preceding paragraph, a license for Oracle Technology Foundation for JD Edwards EnterpriseOne includes a restricted-use license for Oracle Business Intelligence Publisher. Oracle Business Intelligence Publisher may be used to create or modify reports that use the Oracle supplied database schema, or modifications to that schema done to support modifications to supplied Oracle JD Edwards EnterpriseOne programs. For the avoidance of doubt, examples of uses that are not permitted include, but are not limited to, the following: adding new reports that support different applications or database schemas other than JD Edwards EnterpriseOne.
Summary


Point 1: Oracle Database Standard Edition 2 may be used solely in conjunction with any and all JD Edwards EnterpriseOne programs licensed under your agreement

Q:  Does this mean things like DSI or reportsnow? I'm told YES


Point 2: Oracle Database Standard Edition 2 may be installed on an unlimited number of processors

Wow, so if you use RAC, you can have unlimited nodes for JDE with standard edition.

Point 3: Okay, you cannot used statistics and performance packs, that's a shame. But if you have a mature environment - you'll get away with it.

Point 4:
data-guard. RDS is pretty much looking after this for you (and all the complexity). They are maintaining and shipping your logs to a remote replica and doing all of the network aliasing behind the scenes. This is SO easy to implement.

AWS summary:

This is where things get a little exciting. Lets look at RDS options for Se2





AWS tells us (and I believe their lawyers have done the work), that they can commission a 16 way SE2 machine with BYOL. And as clearly stated about, you have BYOL for as many cores as you can point your SE2 at... but limited to 16 because SE2 does that.

You could be addressing 16 cores and 488 GB of RAM under your standard JD Edwards agreement. For a multi AZ deployment in this situation, you'd be paying approximately 12,000 US per month, but a more modest 16 way with 122GB of ram is 3500 a month - not bad. 

With my load testing experience in JDE, 16 cores goes a LONG way - you don't need parallel either for JDE (IMHO).

Remember that this is Highly available (without RAC), so you actually have two machines ready to process your requests if AWS lose an availability zone.




Above you can see all of the server classes that you can address for SE2.

I appreciate that oracle will probably have a different view on this and I recommend that you seek specific advice before acting on the above recommendation / opinion.