E9.2 JDE 9.2 APIs/Integrations

[Patrickjk]

JDE 9.2 32 bit
Oracle: 19c

Hello,

I'm looking for any input regarding integrations with JD Edwards and external applications. Currently, my company utilizes BizTalk and SQL Server SSIS Packages for integrations -- SSIS Packages for batch jobs and BizTalk for real time updates.

The IT Department is in the design/development phase of a new project for a vendor software package called Critical Manufacturing. One of the JDE developer groups is researching the use of JDE's RTE (Real Time Events), which is new for all of the JDE developers and the CNCs at my company. Has anyone had any experience with RTE?

Questions
1) Is RTE a viable integrations solution for optimal performance?

2) What are the pros and cons of RTE?

3) Is RTE able to handle high volume JDE databases? To give a perspective of my company's data volume, in PD table "F0911 - Account Ledger" has approximately 1.503 billion records and table "F4111 - Item Ledger File (Cardex)" has approximately 636 million records.

4) Other than the integration software packages I mentioned, does anyone have other suggestions for possible integration software?

My main concern has always been performance, which includes future, data growth estimates. For example, those familiar with UBE "R30601 - Bill of Material Structure Analysis" knows how long that job can take to run. This job was scheduled to run daily, and it was taking 26 to 32 hours to run. I was tasked with optimizing that job. I am well versed in Oracle's PL/SQL procedural language, so I rewrote it in PL/SQL, and now on average it takes 25 to 30 minutes to run. (Apologies...I got a little off track there.)

Thanks to everyone for any input.

Regards,

Patrick

 

[harshteki]

JD Edwards offers robust integration capabilities with external applications. Its integration framework enables seamless data exchange and communication between JD Edwards and various third-party systems, such as CRM, HR, supply chain management, and more. These integrations enhance operational efficiency, streamline business processes, and facilitate real-time information sharing across the enterprise.

 

[JohnDanter2]

No1 - Can you tell me what you did to speed up R30601 please?
No2 - Can you tell me again
No3 - Have you looked into Orchestrations and the Orchestration Studio for you connection issues?

It's super powerful and scaleable and can easily talk both ways using the AIS server and either using the API to call BSFNs directly or via another API to call orchestrations for inbound and even RTE type from within JDE itself and also using connectors and connections to call external stuff. All free too.

The RTE can be mimicked by adding orchestrations to E1 forms (when users click OK) or by running watchlists on a scheduler and triggering events when thresholds or criteria are met

No4 - Can you tell me more about how you sped up R30601 again please

 

[Patrickjk]

Well John, That would cost you money. Like I said, I rewrote the UBE itself in PL/SQL - Oracle's procedural language utilizing bulk collections whereas with JDE UBEs you're stuck with single row processing. NOTE: In this case it's a PL/SQL Package with multiple stored procedures.

I'll give you a little bit, and hopefully you can take it from there.

Step 1
----------
--Initialize working tables.
EXECUTE IMMEDIATE 'truncate table proddta.f30UI010';
EXECUTE IMMEDIATE 'truncate table proddta.f5530010';
EXECUTE IMMEDIATE 'truncate table proddta.f5530011';

Load data into table proddta.F30UI010 - Bill of Material Work File with the following code.

PROCEDURE p_f30ui010_load IS

/*Declare the collections for array processing. */
TYPE f3002_nbr IS TABLE of NUMBER INDEX BY BINARY_INTEGER;

-- Host variables for table F30UI010 fields.
v_ixitm f3002_nbr;
v_ixkit f3002_nbr;
v_f3002_idx PLS_INTEGER;

CURSOR c_f3002 IS
SELECT DISTINCT
ixitm
,ixkit
FROM proddta.f3002
WHERE ixitm != 0
AND ixlnty NOT IN ('T','TX','J','JT');

BEGIN

--Log p_f30ui010_load start time.
gv_err_nbr := '0';
gv_err_msg := 'P_F30UI010_LOAD Start Time.';
p_log_job_error;

OPEN c_f3002;

LOOP
FETCH c_f3002 BULK COLLECT
INTO v_ixitm
,v_ixkit LIMIT 500000;

IF v_ixitm.COUNT > 0 THEN
FORALL v_f3002_idx IN v_ixitm.first..v_ixitm.last

INSERT INTO proddta.F30UI010
( wfitm
,wfkit)
VALUES
( v_ixitm(v_f3002_idx)
,v_ixkit(v_f3002_idx));
END IF;

--30 Aug 2016, P. Konkle, Move commit outside of the IF statement
--in order to commit once for all records that are inserted.
COMMIT;

EXIT WHEN c_f3002%NOTFOUND;
END LOOP;

CLOSE c_f3002;

After loading table F30ui010, use Oracle's recursive query with SYS_CONNECT_BY_PATH. In this case it would be a recursive query about the BOM. The nice thing about the query is that it checks for a recursive BOM issue and returns the entire BOM.

PROCEDURE p_bom_hierarchy_process(i_bom_parent IN NUMBER) IS

/*Declare the collections for array processing. */
TYPE bom_var IS TABLE of VARCHAR2(400) INDEX BY BINARY_INTEGER;
TYPE itm_prev_var IS TABLE of VARCHAR2(8) INDEX BY BINARY_INTEGER;
TYPE itm_curr_var IS TABLE of VARCHAR2(8) INDEX BY BINARY_INTEGER;
TYPE f5530010_nbr IS TABLE of NUMBER INDEX BY BINARY_INTEGER;

-- Host variables.
v_bom bom_var;
v_wkitm f5530010_nbr;
v_wkllx f5530010_nbr;
v_itm_curr itm_curr_var;
v_itm_prev itm_prev_var;
v_bom_parse VARCHAR2(400);
v_parse_idx PLS_INTEGER;
v_slash_idx PLS_INTEGER;
v_curr_idx PLS_INTEGER;
v_prev_idx PLS_INTEGER;
v_llx_idx PLS_INTEGER := 0;
v_bom_idx PLS_INTEGER;
v_bom_cnt PLS_INTEGER;
v_itm_curr_cnt PLS_INTEGER;
v_itm_prev_cnt PLS_INTEGER;

--Select the entire bom hierarchy and all its levels
CURSOR c_bom_hierarchy IS
SELECT SYS_CONNECT_BY_PATH(LPAD(f30UI010.wfkit, 8, '0'), '/')
|| '/' || LPAD(f30UI010.wfitm, 8, '0')
FROM proddta.f30ui010 f30ui010
START WITH f30ui010.wfkit = i_bom_parent
CONNECT BY PRIOR f30ui010.wfitm = f30ui010.wfkit
ORDER BY 1;

I hope this helps and good luck!!!

Regards,

Patrick

 

[BOster]

Well John, That would cost you money. Like I said, I rewrote the UBE itself in PL/SQL - Oracle's procedural language utilizing bulk collections whereas with JDE UBEs you're stuck with single row processing.

Only if you stick to ER code. In C BSFNs you can probably do the same logic you did with PL/SQL or something pretty close that would be comparable in performance.

 

[Alex_Pastuhov]

[...] I rewrote it [...]

Just wondering how much effort / time that took?

It's really the best approach, but it will probably be the most laborious...

 

[Patrickjk]

It took me about two weeks to do the design and development based on the requirements and then another week with unit testing and some rework.

The rework was due to a DB trigger on table F4102 - Item Branch. I didn't realize at the time there was an update DB trigger on the table, which was really slowing down the run time. The DB trigger was inserting into a tag table. Every time the stored procedure code would update the LLX - Lower Level Code in F4102, the update DB trigger would fire. I spoke with the business, and in this case records didn't need to be inserted into the tag table when updated. At that point, I worked with the DBA and we decided the best option was to create a DB context that was invoked within the stored procedure, so when the DB trigger would fire it would immediately exit without inserting into the tag table based on an IF check for the DB context.

Here is part of that code as a reference...
------------------------------------------------
--====================================================================================
-- MAIN PROCEDURE FOR PROCESSING ITEM/LOWER LEVEL CODE UPDATES.
--====================================================================================
PROCEDURE p_item_llx_update IS

llx_update_err EXCEPTION;
v_context_name VARCHAR2(30) := 'F4102_LLX';
v_context_value VARCHAR2(30) := 'LLX_UPDATE';

BEGIN

--Log Job start time.
gv_err_nbr := '0';
gv_err_msg := 'Job Start Time.';
p_log_job_error;

--Set Name/Value pair of the context associated to this package.
--This will be used for exiting out of trigger PRODDTA.T_F4102ATU
vpd.pkg_plsql_context.p_context_name_value(v_context_name, v_context_value); --THIS INVOKES THE DATABASE CONTEXT.


CREATE OR REPLACE TRIGGER PRODDTA.T_F4102ATU BEFORE UPDATE ON "PRODDTA"."F4102" FOR EACH ROW
DECLARE p_Count INTEGER;
p_Count2 INTEGER;
p_Context VARCHAR2(30);
BEGIN
SELECT SYS_CONTEXT('PLSQL_CONTEXT','F4102_LLX') INTO p_Context FROM DUAL; --***CONTEXT LOGIC***
IF (p_Context IS NULL) THEN --***CONTEXT CHECK***
SELECT COUNT(*) INTO p_Count FROM "PRODDTA".F5541086 WHERE ATCVVTABNM = 'F4102' AND ATJPID = :NEW.IBPID;
IF (p_Count > 0) THEN
SELECT COUNT(*) INTO p_Count2 FROM "PRODDTA".F554102T WHERE IBMCU = :NEW.IBMCU AND IBLITM = :NEW.IBLITM AND IBUPMJ = :NEW.IBUPMJ AND IBTDAY = :NEW.IBTDAY;
IF (p_Count2 > 0) THEN
UPDATE "PRODDTA".F554102T...

Regards,

Patrick

 

[Alex_Pastuhov]

[...] about two weeks [...] and then another week [...]

Ok, cool, thanks! That's less that I would have normally expected, kudos! At this rate, it would surely be worth reworking all slow processes in JDE.

 

[Chan Rana]

JDE 9.2 32 bit
Oracle: 19c

Hello,

I'm looking for any input regarding integrations with JD Edwards and external applications. Currently, my company utilizes BizTalk and SQL Server SSIS Packages for integrations -- SSIS Packages for batch jobs and BizTalk for real time updates.

The IT Department is in the design/development phase of a new project for a vendor software package called Critical Manufacturing. One of the JDE developer groups is researching the use of JDE's RTE (Real Time Events), which is new for all of the JDE developers and the CNCs at my company. Has anyone had any experience with RTE?

Questions
1) Is RTE a viable integrations solution for optimal performance?

2) What are the pros and cons of RTE?

3) Is RTE able to handle high volume JDE databases? To give a perspective of my company's data volume, in PD table "F0911 - Account Ledger" has approximately 1.503 billion records and table "F4111 - Item Ledger File (Cardex)" has approximately 636 million records.

4) Other than the integration software packages I mentioned, does anyone have other suggestions for possible integration software?

My main concern has always been performance, which includes future, data growth estimates. For example, those familiar with UBE "R30601 - Bill of Material Structure Analysis" knows how long that job can take to run. This job was scheduled to run daily, and it was taking 26 to 32 hours to run. I was tasked with optimizing that job. I am well versed in Oracle's PL/SQL procedural language, so I rewrote it in PL/SQL, and now on average it takes 25 to 30 minutes to run. (Apologies...I got a little off track there.)

Thanks to everyone for any input.

Regards,

Patrick

I have used RTE extensively and are very reliable to use. There are standard RTE events that are available ready out of box. We ended up modifying them later on to fit the business needs. You can also setup your own custom RTEs. Benefit of RTE is that you can get update on PO & SO even if it changes asynchronous by any JDE process.
When we implemented we did stress testing and didn't find any issues.

Only downside is for every change you make to RTE structure , you need system bounce to make it work without any issues.

 

[Chan Rana]

We went to multi currency and found similar issues where JDE Conversion UBEs were running way longer then our outage window and we had to mimic the same thru SQL script which was way much faster then UBEs.

 

[Patrickjk]

Thank you everyone for your input. I greatly appreciate it. This has been extremely helpful. Forward with vigor.