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.