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)

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.

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!