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!