E9.1 Export Data For Integrations To Third Party Application

FrankCLT

Well Known Member
Hi All,

I don't have much experience with JSON, Orchestrator or XML data formatting. Are there any tools to allow packaging data from a JD table to one of these formats?

Thank you in advance,
FrankCLT
 
Orchestrator does it natively from DB/view to JSON without much need for other programming. Well worth learning if it's at all an option. It is the natural and best integration point for JDE in my opinion, though some companies I spoke with at last conference have a corporate integrations strategy requiring a non-JDE middleware such as Boomi or MuleSoft.

I have some pre-orchestrator data integration experience as well, specifically Boomi as the "data translation" and API endpoint tool, on top of JDE XE. To do that I needed to: Buy boomi ($$$$$, though you can work on a fully-functional demo for free), learn Boomi (~100 hours of classes and personal training), work with networking, security, and CNC to allow Boomi to talk to our database via JDBC drivers, write the SQL in Boomi, and then surface the connection using a db-to-xml/json profile. Last but not least, spin that up as an API connection on Boomi. You can do simple APIs wth standard Boomi but if you need more API security than basic you might be forced to buy their Advanced API module ($$), spin up an API gateway, and do a bunch more security and networking work to hook everything up properly.

I assume there are lower-cost, lower-barrier solutions such as IIS/.net with custom code, but I haven't done DB work on vb.net and IIS for a long time. I bet it's easier than when I did it, and that there might be libraries out there to handle the db-to-JSON translation.

Orchestrator in comparison is an INSANELY cheap and simple solution for this issue if it's an option on your version.
 
Thank you, unfortunately, we don't have Orchestrator setup. If we did our tools release would limit the functionality from what I've been told.
 
Do you have BI publisher? There is a way to use XSL template there to convert UBE to nice-looking XML.
 
I should have also mentioned that we would also need to write\update certain JDE tables with data we received back from this external application.

So, it would be....

JDE Table === > JSON or XML? === > 3rd Party Application

3rd Party Application === > JSON or XML? === > Update\Insert JDE Table

That is the process in a nutshell.....

Thank you,
FrankCLT
 
do you currently have any integrations middleware? This sounds like a job for that if you're not going to upgrade & orchestrator.

Depending on your team's talent you might be able to spin something up on your own (.net/iis) or commission a custom solution, but there's a tipping point somewhere that pushes you to middlware or orchestrator/upgrade IMO
 
We may look outside for a custom solution. Does anyone know of any organizations for JDE \ XML Read Write integrations?
 
HI Frank

This may not be exactly what you want but have you seen the R98403A and R98403B UBEs?

R98403A runs over an E1 table and converts the entire contents to XML
R98403B takes an XML input file and depening on the POs you set it will either append or replace XML data into a target table

I use them for copying UDCs down from PD mainly but with a bit of lateral thinking I also use them for data extraction by targeting a worktable selected from a base table
Example output file
Code: 
<?xml version="1.0" encoding="UTF-8"?>

-<table name="F0004">


-<row>

<col name="DTSY">00</col>

<col name="DTRT">15</col>

<col name="DTDL01">Business Unit Reporting Code15</col>

<col name="DTUSEQ">.0</col>

<col name="DTUCD1"> </col>

<col name="DTCDL">3</col>

<col name="DTLN2">Y</col>

<col name="DTCNUM">N</col>

<col name="DTMRCT">2</col>

<col name="DTMRTY"> </col>

<col name="DTUSER">JDE</col>

<col name="DTPID"> </col>

<col name="DTUPMJ">108106</col>

<col name="DTJOBN">JDE</col>

<col name="DTUPMT">80000</col>

</row>


-<row>

<col name="DTSY">00</col>

<col name="DTRT">16</col>

<col name="DTDL01">Business Unit Reporting Code16</col>

<col name="DTUSEQ">.0</col>

<col name="DTUCD1"> </col>

<col name="DTCDL">3</col>

<col name="DTLN2">Y</col>

<col name="DTCNUM">N</col>

<col name="DTMRCT">2</col>

<col name="DTMRTY"> </col>

<col name="DTUSER">JDE</col>

<col name="DTPID"> </col>

<col name="DTUPMJ">108106</col>

<col name="DTJOBN">JDE</col>

<col name="DTUPMT">80000</col>

</row>
 
Hey JD2,


Thanks for the reply. A little background to my latest project.

We had a video from a company called Leverage that takes PO Data and sends that data to our suppliers for approval, change or cancellation.

Suppliers either Approve, Edit or Cancel. This is at the line level. We have numerous functionalities related to our business processes that I don't think this software company has considered. I think their model receives a file, package that file with PO data send to supplier for any edits, they receive the returned file and update our tables.

In addition, they have never worked with JD Edwards and have no idea of when a buyer changes a PO line and the processing that goes on in the P4310. They haven’t given us a firm answer as to where their application stops, and we take over.

I'm trying to get a list of questions to ask. If you've done any integration with an organization that had no JDE experience, what would be some questions that could shed some light on this being a mess or something that may work out.

Here is a link to the company, maybe someone has worked with them in the past......

https://www.tryleverage.ai/platform

Thank you,
FrankCLT
 
...but they have a white glove systems integration team to do the heavy lift!!! Lol. Salesperson speak. Integrations are hard work, as I've learned. There's no real magic going on despite what your execs were sold.

I am guessing that you'll end up at a pub/sub or webhooks method of talking back and forth. You cut a PO, your system needs to "publish" to this fancy software that a new PO has been cut. You can either publish a minimum of data "po 12345 created" and then let the software do the querying if it is interested in what you published, such as "what line items are in po 12345?" and your system replies with line items, or you can publish a complete event "po 12345 created. line 1 = item # 123, quantity 3, price 5.45"

line status changes in JDE, your system publishes "po 12345 line 3 changed field: quantity from: 1 to: 2"

supplier using that site or a supplier portal ships partial, which publishes an event "po 12345 line 3 shipped quantity 1" which your system needs to handle

and on and on.

What are the transaction set(s) you're needing to publish to this software?
What are the transactions you need JDE to listen for? Are the transactions information-complete or do they require API lookups?
What protocols, security, and file formats are required for these communications?

Those are the transaction & interface questions you should be able to work out with a cloud software provider. You need to spin something up to publish and listen for whatever events that they may publish or listen for. Then you on your end need to make sure the events are handled properly within JDE.

It's a nightmare and a big important project especially if you don't have middleware in place already such as orchestrator or boomi. You might be able to leverage F47/EDI methods if not Z file methods to put items into, change, and send items out of your JDE properly and through the correct business logic.

Hope this is helpful in some way. Best of luck!
 
Last edited:
Oh yes and Mulesoft has some really good theory on layers of APIs that applies to integrations middleware even Orchestrator.

Read up at https://blogs.mulesoft.com/api-integration/patterns/patterns-to-debunk-api-led-connectivity-myths/

img_6059d08aeba68.png
 
Yeah, their typical go-live is 90 days. I'm supposed to have all the data needed to pass them to recreate the identical PO on their side. Get that data formatted so they can read it for ALL our PO types, receive it back for updating our data and so on and on and on and on.
 
JD2,

The R98403A that runs over an E1 table and converts the entire contents to XML, did you create a new C function for the B9840G to use the table as in your example?

Thanks,
FrankCLT
 
JD2,

One more quick question....

Where is the XML created?

Thank you,
FrankCLT
 
Has anyone had any success writing to an XML file through Table Conversions?

I get to the point of mapping and creating fields but can't quite figure out how to map my JD Table to the XML file.

Thank you,
FrankCLT
 
No worries...

I was testing the R98403XA, but that can only be run locally. In 9.1 several of the BSFN's are compiled to only run locally. Release 9.2 may have changed to where they run off the sever as well...

I've looked at the R98403A PO and didn't see where the output would go.

Thank you,
FrankCLT
 

Similar threads

E9.1 Export\Inport JDE Table to JSON
Replies
4
Views
662
alfredorz
alfredorz
E9.2 BSNF timing out in Logic Extensions
Replies
4
Views
166
dtujo2022
E9.2 Extracting BLOB data from F95631 via a Data Request in Orchestrator - Base64?
Replies
14
Views
1K
Luyendijkleo
E9.2 How To Read A Web Page for Processing
Replies
3
Views
2K
DaveWagoner
DaveWagoner
alfredorz
E9.2 Allow input in Associate Orchestrations to Grid
Replies
2
Views
168
alfredorz
alfredorz
Back
Top