Alternative to UES jdeDirect

astradyneuk

Member
Hi List

We are moving from E1 9.2 on IBMi to E1 9.2 on Oracle Cloud using the Oracle 19c database. We currently use Crystal Reports 2016 as our reporting tool and connect to the database on the IBMi using the enhanced jdeDirect drivers from UES (Unity). As this does not work with Oracle databases is anybody aware of an alternative enhanced driver? I have searched and seen references to ODA as well as Oracle JDE Drivers, but these all seem to be deprecated and no longer available. I am aware of the standard Oracle JDBC drivers, but am looking for something with the enhanced features such as virtualizing UDC code descriptions, converting Julian dates to real dates, field and table descriptions, etc.

Alternatively does anybody have any recommendations on other tools to replace Crystal Reports, ideally something that integrates specifically with E1 rather than a general tool.

Thanks
Jonathan

E1 9.2, TR 9.2.3.3, IBMi 7.3
 
Jonathan,

You won't find what you're looking for.
Good news is you don't need to.
Invest a little skull sweat and you'll have a superior solution in terms of performance.
What we did (a long time ago) was to:
1. Create simple DB Function that normalizes JDE Dates to standard
2. Ditton for Time values
3. On as needed basis create DB Views for the Tables you need to report on. Below is a simple example


USE JDE_PRODUCTION
GO
EXECUTE AS USER = 'PRODDTA';

DROP VIEW IF EXISTS F4105_VIEW
GO
CREATE VIEW F4105_VIEW (
SHORT_ITEM_NUMBER_COITM,
ITEM_NUMBER_COLITM,
CATALOG_NUMBER_COAITM,
BRANCH_COMCU,
LOCATION_COLOCN,
LOT_SERIAL_COLOTN,
LOT_GRADE_COLOTG,
COST_METHOD_COLEDG,
UNIT_COST_COUNCS,
PURCH_COST_METHOD_FLAG_COCSPO,
INVEN_COST_METHOD_FLAG_COCSIN,
USER_RESERVED_CODE_COURCD,
DATE_USER_RESERVED_COURDT,
AMT_USER_RESERVED_COURAT,
USER_RESERVED_NUMBER_COURAB,
USER_RESERVED_REFERENCE_COURRF,
USER_ID_COUSER,
PROGRAM_ID_COPID,
WORK_STATION_ID_COJOBN,
DATE_UPDATED_COUPMJ,
TIME_OF_DAY_COTDAY)
AS
SELECT
F4105.COITM,
F4105.COLITM,
F4105.COAITM,
F4105.COMCU,
F4105.COLOCN,
F4105.COLOTN,
F4105.COLOTG,
F4105.COLEDG,
F4105.COUNCS / 10000,
F4105.COCSPO,
F4105.COCSIN,
F4105.COURCD,
DBO.JDE2DATE(F4105.COURDT),
F4105.COURAT / 100,
F4105.COURAB,
F4105.COURRF,
F4105.COUSER,
F4105.COPID,
F4105.COJOBN,
DBO.JDE2DATE(F4105.COUPMJ),
F4105.COTDAY
FROM F4105
GO

GRANT SELECT ON f4105_view TO XXX_role;
...

DROP SYNONYM IF EXISTS Item_Cost_f4105;
CREATE SYNONYM Item_Cost_f4105 FOR PRODDTA.F4105_view;
 
This.

But if returning millions of rows beware the performance impact of stored functions (oracle). SQL to PLSQL context switches on every row to evaluate the return values can be significant after a large number of rows. We ended up removing them and using long verbose highly repetitive sql and got a big performance benefit.
 
Back
Top