Wednesday, 10 August 2022

OIC and JDE - the perfect mix (like oil and water)?

I recently did an OIC bootcamp, oh man.  There was so much I liked about the product.  In terms of an integration platform - it seemed to be really well thought out.  I love the fact that you could add human interfaces to integrations to make decisions.  That's right - web forms and human interaction on the fly for an integration!  That is nice.

I also really liked the native connections to VBCS and the fact that if you were running cloud ERP of any of the other oracle cloud products - it seems silly if you are not using Oracle Integration Cloud as part of (or all of) your integration solution.  So much so that we are strategically recruiting in this space and want to own customers cloud ERP integrations.

Though, this is a JDE blog and I want to comment on the JDE connector that is available with OIC...  I was just about to start writing accelerators for linking Cloud ERP data with JDE data.  I was ready to create synchronisation of popular transactions - potentially starting with financials.  This was going to be the foundation of capability.  Modernising my teams consulting skills from JDE to Cloud ERP.  An accelerator for customers to migrate their data and run things SBS - side by side.

Then, it hit me...




Nice interface...  Love the drag and drop.  Love the fact that the orchestration studio developers MUST be working with the OIC team, because this all looks too cool and too similar!





This is really neat and a well thought out design.  Like I said though, similar to orchestration.  And what do people find frustrating about orchestration (especially hard core developers) - the lack of smashing a keyboard.  We love writing code, finding code, massive changes...  we love regex...  The above does not give you much love for this.

But, I digress.


I want to create a connection and I need to use an adapter (of course, an API is harder to create and the generic rest connector looks way too "RESTY" to me (and if you know JDE's implementation of rest, you will support me here).

Looking at the details of this connect (can't WAIT to paste in my discovery URL or perhaps the swagger or OpenAPI definitions of all the orchestrations I want to call...).

Getting excited...



Nearly there!




Huh??  WSDL??  SOAP...  BSSV... Oh no...  I'm crying all over my plans to take over the world...

I need to get my team to write a connection to REST-ify calls to JDE.  This could be done.

If you need to expose some relative resource URIs and wrap them up into some funky orchestration calls...  Otherwise this is not going to be an amazing integration.


Anyway, I'm going to fight the good fight with the REST connection to see if I can get some orchestration calls working.  I'll be sure to post how I go.

But, OIC - please modernise your connector for JDE to support orchestrations.  PowerApps does it SOOOO nicely.  I know powerApps is not an integration platform, but you know what I mean.





Wednesday, 3 August 2022

JD Edwards on AWS - with all the fruit

Fusion5  did something amazing about 3 years ago - before we thought it was possible.  We created an elastic JD Edwards in public cloud that used ephemeral pods to run JDE workloads.  This incredible architecture allows the JD Edwards server count to expand and contract with the requisite user demand.  So, as people log in... servers expand...  

Then as demand wains, then the servers also contract - but they contract ONLY when users and batch jobs have finished running on them.  The entire architecture is aware of itself.  The architecture is bought up with all signals being monitored (bespoke ones too - connected users, running UBE's) and these signals are interpreted for expansion and contraction rules.   All of the standard CPU data can be used to trigger any actions with the architecture too.

What does this allow us to do?  Well - once again, let's let the picture do the talking...  We can see that on any given day, how many users logged into JDE and how many unique ephemeral hosts were started to process that load.  We have a cost from AWS in the table [oh yes! this is the public cloud that I'm talking about in this instance], which is the actual costs for the class of machine that is running the pod workload.  We also have the average server response time listed, which is critical for us to determine if the number of servers is appropriate or not.

Finally you will see the mesmerising (oh yes, I think it is!!!) pattern of host growth (bar) vs. connected users (line) which track each other exactly over the month period.

Now, I have the ability to test different hosts, different tools releases, different OS's if I want.  I can release that POD into the farm and measure how it performs.  I can change the server class or anything and know the exact impact that this has on JDE.  I also have all of the logs coming back to a central cloud console (in AWS of course) to do my troubleshooting on.

This is by far the most elegant and easy to manage JD Edwards instance I have ever worked on.  Do you want yours to be this cool?  reach out!

\


Monday, 1 August 2022

JDE oracle licence audit, again and again

There are numerous ways of interpreting oracle licence audits, but my guess is that if oracle wanted to pursue you - you could get in trouble pretty easy.

I invite you  all to pull out your licence agreements and pay special attention to the definition of an application server.  This is someone that is authorized by you to use an application regardless of whether they are using the application.

If you take this as a lawyer would, I would argue that you do not authorise people if you leave security all doors open.  The words and context of this definition seem to imply an active act of authorisation.  This is not active authorisation, you have not performed any actions to authorise.  But - if you lock everything out and then start to authorise back (all doors closed), then you are opening yourself up for issues.  Therefore a solid security model where you are actively allowing people to use JDE programs, you are going to need to ensure that the end users are licenced.

It's strange that this 2010 definition then goes into breaking down particular modules and transactions.



Just testing of the definition has survived the test of time: from https://www.oracle.com/a/ocom/docs/license-definition-rules-v091120.pdf 

Application User: is defined as an individual authorized by You to use the applicable licensed application Programs which are installed on a single server or on multiple servers regardless of whether the individual is actively using the Programs at any given time. If You license the Oracle Self Service Work Request option in conjunction with Oracle Enterprise Asset Management, You are required to maintain licenses for the equivalent number of Application Users licensed and You are granted unlimited access to initiate work requests, view work request status and view scheduled completion dates for Your entire employee population. Application Users licensed for Oracle Order Management are allowed to manually enter orders directly into the Programs but any orders entered electronically from other sources must be licensed separately. For Oracle Sourcing, Oracle Fusion Sourcing, Oracle iSupplier Portal, Oracle Fusion Supplier Portal, Oracle Services Procurement, PeopleSoft eSupplier Connection, PeopleSoft Strategic Sourcing, PeopleSoft Supplier Contract Management and JD Edwards Supplier Self Service Programs, use by Your external suppliers is included with Your application user licenses. For the purposes of the Oracle Financial Services Operational Risk Solution Program, employees who are just contributing information to the Program via the applicable user interface shall not be counted as application users

So yes, seems the same.

Given the above two definitions, it seems that the best thing you can do to avoid any problems is to ensure that you have the appropriate security in place which will enforce your licence metrics.  The old saying that "enterprise licences" prevented audits is not right, as an enterprise licence is generally only for a certain number of modules.

Short addendum - Enterprise Metric:

Enterprise licences can allow customers to have any number of users using JDE and they only need to pay a certain amount of fees based upon revenue of their business.  There are generally additional provisions for additional revenue - i.e. if you increase your revenue over the number listed - you will need to buy additional units.


For example, a customer might have a table like the above.  It means that they can have heaps of users, but will pay oracle based upon the perceived revenue number above (313 $M).  They will pay a fixed fee to oracle based upon this number.  Note that this is only for 19 named modules, not all of the JDe modules.

If the revenue goes above this, then they'll need to "top up" in increments that are generally listed in their licence agreement.  The increments are generally in tranches, therefore if the revenue is above the 313$M and in the next tranche (156$M), then they'll need to pay the additional amounts.





Tuesday, 26 July 2022

RTE and JDE - what is stored in the BLOB?

I'm chasing down a missing RTE message.

RTE's are good, but confusing and the subject of many of my posts.

This is talking a little about converting the BLOB from and RTE into something slightly readable (or at least searching).

We all know that out of the box, RTE write to a single copy of the F90710 - that is the first thing that it does.

The following statement allowed me to find the owner for F90710 in PY - I remember that we spilt them and I could not remember where.  This is quicker than looking up OCM.

select owner from all_tables where table_name = 'F90710';

I then take a look at some messages, I have history enabled, so there are a heap of messages at status 5.

select * from py920.f90710  order by etevntseq desc;

I then start to rip apart the BLOB field to work out the contents, of course -we know [and are frustrated by] the fact that these are stored as BLOBs in some kinda weird and AS/400 proof format.

Using the UTL_RAW package I can view the contents of the ETEDATA field:

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where etevntseq > 17000 order by etevntseq desc;


So we can see from above that we can make out the characters in the BLOB using cast to varchar2.  This is neat...  We kinda think that because all of those spaces are there, we could use cast_to_nvarchar2, but this leaves us with a bunch of rubbish.  I think I have blogged on this before that JDE is flipping the double bytes into something that the standard NVARCHAR string functions do not like, so we need to do our comparisons (of strings) using S P A C E S between the characters.

There are a bunch of util functions that you can use over the results of the function, as it's basically a VARCHAR.  The other nice thing is that you can also see the DSTR that is storing the values in the BLOB:

D4202310A and D4202310B

So use this to reverse engineer what you are seeing above.

Once would also guess that the 1252 in the header was something related to https://en.wikipedia.org/wiki/Windows-1252

Anyway, back to what we want...  Tracking down events for certain sales orders.

I know that the SO number is 19274775

select utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)) from py920.f90710 where instr(utl_raw.cast_to_varchar2(dbms_lob.substr(ETEDATA,2000,1)),'19274775') > 1 order by etevntseq desc;

Above shows how I can work through the data.  Note that this does not have spaces because the sales order number is a numeric [in the scheme of things].  Also, important to note that you can return the value of instr to then know the index of the sales order number and make your SQL much more efficient.


We only have a single row - which is a shame, we seem to be missing the status code of ADD, but we have the update.  So I need to go searching.

Hopefully that shows some basics of how you might be able to start debugging some RTE problems in JDE.




Friday, 17 June 2022

Simple and secure Azure SSO for JDE

Who'd like to be able to use all native security options available to them via Azure AD used in JD Edwards - me!

Perhaps take advantage of self enrolling to be able to use the application in the fist place.

Our SSO solution will work side by side with the native capability of JD Edwards to use AD for long username and password authentication - but we give you the additional ability to use Azure and the power of Azure (for things like conditional access / MFA) natively in JDE.

We let Azure do all of the heavy lifting, see https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-protocols-oidc We cleverly use openID connect (an extension of oauth2.0) to allow users to authenticate to Azure for their access to JD Edwards.  

You can see from the above that we do a couple of modifications to JDE to allow this to happen.  Firstly, we allow a redirect (manually with a button or automatically) to send users to Azure:



This button is the second dotted line in the MSFT supplied schematic.

The user then enters credentials with Azure - not with our products - they negotiate.  Said negotiation might include conditional access from various available signals - see below:


The browser then gets a login token from Azure - thanks for that and passes this back to the /auth handler that we have installed on JAS WLS instance.  This handler needs to go out to the internet to validate that the id_token is valid and then generates a PS_TOKEN to all of the user to log into JD Edwards.

The PS_TOKEN is generated for a custom security node, which improves the security posture of the JD Edwards installation.

Note that if the user already has a id_token - then the visit to Azure is ignored and goes directly to myAccess to validate the id_token - see below for a JDE centric view of things.



Once myAccess has generated A PS_TOKEN, this is sent to JDE for verification.  If JDE likes what it's sent, the user is granted login permissions and can continue to use said token for it's defined lifecycle.


So hopefully you can see from the above that all of the heavy lifting is being done by Azure.  We are neatly and securely playing the JDE specific part of logging the user into JD Edwards.

The creation of an enterprise application in Azure is trivial and only takes about 30 minutes.  We are then able to patch your tools release to have an additional "login" button as seen above and add some redirects into a couple of jsp files.  

Finally, the glue in all of this is an enterprise application that sits in WLS and does the heavy lifting in terms of validating the id_token and also generating the PS_TOKEN.  Note that this also rips apart the authentication JWT to extract the long username that is used next to the PS_TOKEN to ensure that the correct user is being logged into JDE.

The design above easily caters for additional Enterprise Applications to be defined in Azure, that will have different id_tokens / cookies - which will also allow for native logging into different JD Edwards environment.

see more here:  https://www.fusion5.com.au/jd-edwards/myaccess/ 

Stored procedure calling from JDE - yes please

I've been involved in a couple of projects that need the ability to call stored procedures from JD Edwards directly.  It does get messy.  Previously I've written an intermediate DLL which loads the OCI DLL's and eventually calls OCIExecuteStmt, which worked well and was secure.  This also did really nice and granular security [kinda] and transaction processing was a breeze.

Despite this being neat, it required constant maintenance, as you needed to repack the tools release with the intermediate DLL each time.  I also had to store the password for the user that needed to run the stored procedures in the JDE.INI.  This was cool and I wrote some encryption algorithms to that would encrypt and decrypt the passwords that I wanted to use.

I've changed tact, I don't know which method I prefer.  I now created a trigger on a standard JD Edwards table that would fire off and run the SP (with up to 3 parameters) before insert into the table.  I chose before insert, as this gave me transaction control.  It's pretty native that if the SP fails when the trigger calls it, then this will also bomb out the insert and return an error to JDE.  This allows for the transaction processing and error handling I need.

I actually do not really like this solution, an insert that calls a trigger that calls a stored procedure - but we are limited in how we are able to execute statements.

I created table F55TRIGX:

Name        Null?    Type          
----------- -------- --------------
TGMATH01    NOT NULL NUMBER        
TGY55SPROCN          NVARCHAR2(256)
TGY55PARM1           NVARCHAR2(512)
TGY55PARM2           NVARCHAR2(512)
TGY55PARM3           NVARCHAR2(512)

It's a little annoying that JDE needs a PK for a table like this, but hey-  I just soaked up a NN.  So, when you insert into the above, my trigger essentially runs:

execute immediate TGY55SPROCN[(TGY55PARM1,TGY55PARM2,TGY55PARM3)]

Note that everything in [] is optional.

I don't really need to provide much more than the trigger text below to get you working at an oracle site.  The trigger writing process is SO terrible for logging and debugging, so thank me later that you can start with a working example.

create or replace trigger TESTDTA.F55TRIGX_GO

before insert on TESTDTA.F55TRIGX

for each row

declare

  results2 varchar2(256);

  szProcedureName varchar(256);

  szParm1 varchar(512);

  szParm2 varchar(512);

  szParm3 varchar(512);

  szCommand varchar2(2048);

begin

  szParm1:=ltrim(rtrim(:new.TGY55PARM1));

  szParm2:=ltrim(rtrim(:new.TGY55PARM2));

  szParm3:=ltrim(rtrim(:new.TGY55PARM3));

  szProcedureName:=ltrim(rtrim(:new.TGY55SPROCN));

  if length(szProcedureName)>0 then

    -- Call this procedure with the parameters

    if(length(szParm1)>0 and length(szParm2)>0 and length(szParm3)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''',''' || szParm3 || ''')' || '; end;';

    elsif (length(szParm1)>0 and length(szParm2)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''')' || '; end;';

    elsif (length(szParm1)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''')' || '; end;';

    else

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '; end;';

    end if;

    execute immediate szCommand ;

    results2 := 'True';

  end if;

end;


This is a nice way for you to automate tasks or perform some "heavy lifting" in the database.  note that you probably need to prefix the SP with the owner or manage that in a consistent way.


Friday, 20 May 2022

JDE and IFTTT - post 1 triggers

 IFTTT is awesome - like really awesome.  I think that PowerAutomate would be awesome if I was an accountant, or an actuary - but IFTTT is a little more fun and seems to be more tactile.

I recently did a presentation at the innovation symposium on using IFTTT and JDE.  Unfortunately my demo did not go so well, so I thought that I would write a little more on the solution that we've put together for JDE and IFTTT.

Firstly, you need to understand some basics about IFTTT.

IF event occurs (see trigger) then perform action(s).  Easy...

So in my JDE examples, it could be the trigger or it could be the action - easy too.

Let's think about JDE being the trigger:

We need to write a standard connection to IFTTT trigger services, we have done this via a lambda based node project hosted in AWS using their API gateway...  Which BTW - is ULTRA cool.  We get to handle all of the security.

What you are quickly going to find is that JDE cannot talk native IFTTT - but you just need a little layer of coercion.

https://ifttt.com/docs/connect_api#triggers - shows what you need to do.

But it's all pretty easy if you are a guru like my friend Max (May or may not be their real name).

So you can create your connection, which essentially calls your API gateway functions that manipulate the payload to talk native IFTTT.  Therefore you can implement your own quasi RTE functionality by overriding all of the OK buttons on JDE forms with form customisations which call orchestrations which get's to IFTTT via connectors and API gateway and lambda...  Simple hey?  What could possibly go wrong?

But it's really nice.



My triggers are pretty simple and have a payload too (or in IFTTT speak, ingredients)


So I can add items to the payload and use them in subsequent IFTTT processes.  So, in the example about.  I can override the WO entry screen to call P1 work order created.  I can ensure that only P1s are trapped, because I can used specific logic in the orchestration to control that.

Therefore my trigger is fired and IFTTT is listening for this with it's magic.  It then looks at it's applets to see what to do when this trigger event receives a payload.



AS you can see from above, I have just automated the fact that when an urgent P1 WO is created in JDE, I get an SMS message.  That is pretty cool.  I could choose many actions, I could email partners or suppliers based on the equipment.  So many nice options are available for me to automate processes out of JDE.

I could illuminate lights and change flapz boards too.  Really easy to get up and running.

I will over off JDE as the "action" creator in my next blog entry.

We will look to publish this capability publicly in time, currently it's all working for our demo system.