Accessing non JDE tables from E1

johndanter

johndanter

Legendary Poster
Hi List,

Is there any way in E1 that I can access read/update non E1 tables?

Sadly, these tables and columns don't have names like F4211 and DOCO, more like they are named table B2BHEADEROB and column EDIDOCNO_EDOC

In the past I have used a DROPPED table and created a view to the new actual table and E1 can talk to external tables. Table name and columns would be the same
But these table names are different and not standard E1 format.

So any idea?

Can I 'rename' both the columns and tables in the view?

Thanks

John
 
Last edited:
Hi John,

Yes, you should be able to rename both cols and table and even cast DB data types into something E1 understands whcih may help with reading the data. There's also an API JDB_OpenForeignTable which I've had some limited success with when trying to read custom DB2 tables.

Regards

Neil.
 
Brilliant, cheers Neil.

I just noticed you can change columns names easily enough. So job done :)
I just need to make sure the alias lengths/types match
 
Also make sure to grant permissions on the view. Note that the data source you setup to access external data can be configured to use non-julian dates.

Craig
 
Guys do you know if E1 can then update these external tables? Update, insert, delete etc.

Or will it be just a view and read only?
 
Sure. It just comes down to the user that is mapped to the data source having access to perform such actions on the external tables.

Craig
 
Cheers Craig.

I think the issue is, the guys here have done something like this before, but with a dropped and replaced 'view' as opposed to a table.
So E1 can only view the external data. E1 can't perform any I/O.....but this is what I'm being told. I haven't tested it yet.

So if we do this drop and replace with the table, we should be good to go
 
Just use a SQL view with the naming conventions for JDE. converting types and decimals were needed. JDE knows no different.

We do it all the time. Insert/update/select/delete etc. No issues.
 
John,

it depends on how much data transformation is being used in the view. For instance a "formula" field in the view (ExtCost = Price * Qty) can't be updated.
But if you control that sort of thing and use a different data source like Craig says to turn off Julian Date handling AND setup/define user access in P98OWSEC (form E) its doable assuming you have your external DB permissions setup correctly.
 
Brilliant

Thanks gents. We'll give it a whirl and any issues, I'll ask again :)
 
Hi guys.
Sorry to reawaken this thread but I'm finally going to start attempting this and have question on column length.

Before I create my E1 table, can someone have a look at the create table SQL for the external table I've pasted below.
In particular, this guy EDIDOCNO_EDOC

Now that's clearly EDOC right? but it's 22 long on the external table as it's length was not defined (like EDIUSER is at 10)

So when I create my table in E1, what alias would I use to mimic EDIDOCNO_EDOC?
Would I use EDOC (which is 9 math numeric) or would I create a new alias that 22 math numeric?

There are a few columns listed below that have not had their length specified.


CREATE TABLE
B2BHEADEROB
(
EDIUSER_EDUS NCHAR(10),
EDIBATCHNO_EDBT NCHAR(15),
EDITRANSNO_EDTN NCHAR(22),
EDIDOCKEYCO_EKCO NCHAR(5) NOT NULL,
EDIDOCNO_EDOC NUMBER NOT NULL,
EDIDOCTYPE_EDCT NCHAR(2) NOT NULL,
EDILINENO_EDLN NUMBER NOT NULL,
EDISUBLINE_LINS NUMBER NOT NULL,
ZTYPENAME_TYTN NCHAR(8),
ZFILENAME_FILE NCHAR(10),
ZENVIRONMENT_ENHV NCHAR(10),
EDIRECORDTYPE_EDTY NCHAR(1),
EDISEQUENCE_EDSQ NUMBER,
EDITRANSSET_EDST NCHAR(6),
EDITRANSFORMAT_EDFT NCHAR(10),
EDITRANSDATE_EDDT NUMBER(6),
EDISENDRCV_EDER NCHAR(1),
EDIDETLINES_EDDL NUMBER,
EDIPROCESSED_EDSP NCHAR(1),
TRADINGPARTNER_PNID NCHAR(15),
TRANSPURPOSE_TPUR NCHAR(2),
TRANSACTION_TNAC NCHAR(2),
TRANSDIRECTION_DRIN NCHAR(1),
HIERARCHYLEVEL_HLVL NUMBER,
HIERARCHYCODE_HL03 NCHAR(2),
REFERENCE1_VR01 NCHAR(25),
REFERENCE2_VR02 NCHAR(25),
REFERENCE3_VR03 NCHAR(25),
ADDRESSNO_AN8 NUMBER,
ORDERDOCKEYCO_KCOO NCHAR(5),
ORDERDOCNO_DOCO NUMBER,
ORDERDOCTYPE_DCTO NCHAR(2),
ORDERDOCSUFFIX_SFXO NCHAR(3),
ORDERDOCLINENO_LNID NUMBER,
BUSINESSUNT_MCU NCHAR(12),
TRANREF_$TRF NCHAR(30),
PALLETNO_$PAL NCHAR(30),
CARTONNO_$CTN NCHAR(30),
DATASTRUCTURE_DSNM NCHAR(6),
BATCHDATEJUL_DICJ NUMBER(6),
BATCHTIME_TME0 NUMBER(6),
BATCHDATE_DATE DATE,
SHIPMENTID_SPID NCHAR(20),
SHIPDATEJUL_SHPJ NUMBER(6),
SHIPRELTIME_RDT NUMBER(6),
SHIPDATE_DATE DATE,
DELCONFIRMDATEJUL_DTCT NUMBER(6),
DELCONFIRMTIME_DLTM NUMBER(6),
DELCONFIRMDATE_DATE DATE,
LOCALRETCODE_LCR1 NCHAR(2),
FORECASTTYPE_TYPF NCHAR(2),
FORESCHEDTYPQUAL_STQC NCHAR(2),
FORESCHEDQTYQUAL_SQQC NCHAR(1),
SETGROUPING_SET NCHAR(3),
BANKREFNAME_RFNM NCHAR(18),
ALLOWEDMAXVALUE_AMAX NCHAR(15),
TYPECRITERIA_TYPC NCHAR(2),
RECORDTYPE_IRT NCHAR(2),
DEFAULTREASON_BDDR NCHAR(2),
ALPHASPEC18_AA18 NCHAR(18),
OPENQTY1_POQ1 NUMBER(15),
OPENQTY1UOM_UM NCHAR(2),
OPENQTY2_POQ2 NUMBER(15),
OPENQTY2UOM_UOM1 NCHAR(2),
INVOICENO_IN NCHAR(12),
VERSION10_VER0 NCHAR(10),
ORIGORDERNO_OORN NCHAR(8),
PONUMBER_$PO1 NCHAR(25),
REFERENCEORDER2_ONR2 NCHAR(10),
ASSISTANCETYPE_AADT NCHAR(2),
NEXTNUMBER_NNBR NUMBER,
TRANSACTIONDATE_XTND NUMBER(6),
TRANSACTIONDATE_DATE DATE,
ALPHASPEC02_AA02 NCHAR(2),
LINEEXTENDCODE_EXTL NCHAR(2),
ITEMSHORT_ITM NUMBER,
ITEMSECOND_LITM NCHAR(25),
ITEMTHIRD_AITM NCHAR(25),
ITEMCUSTOMER_CITM NCHAR(25),
DESCRIPTION00_DL0 NCHAR(30),
DESCRIPTION01_DL01 NCHAR(30),
DESCRIPTION02_DL02 NCHAR(30),
DESCRIPTION03_DL03 NCHAR(30),
DESCRIPTION04_DL04 NCHAR(30),
DESCRIPTION05_DL05 NCHAR(30),
DESCRIPTION06_DL06 NCHAR(30),
DESCRIPTION07_DL07 NCHAR(30),
DESCRIPTION08_DL08 NCHAR(30),
DESCRIPTION09_DL09 NCHAR(30),
DESCRIPTION10_DL10 NCHAR(30),
DESCRIPTION11_DL11 NCHAR(30),
DESCRIPTION12_DL12 NCHAR(30),
DESCRIPTION13_DL13 NCHAR(30),
DESCRIPTION14_DL14 NCHAR(30),
DESCRIPTION15_DL15 NCHAR(30),
DESCRIPTION16_DL16 NCHAR(30),
USERCODE_URCD NCHAR(2),
USERDATE_URDT NUMBER(6),
USERAMOUNT_URAT NUMBER,
USERNUMBER_URAB NUMBER,
USERREFRENCE_URRF NCHAR(15),
ORIGINUSER_TORG NCHAR(10),
AUDITUSER_USER NCHAR(10),
AUDITPROGRAM_PID NCHAR(10),
AUDITVERSION_VERS NCHAR(10),
AUDITJOB_JOBN NCHAR(10),
AUDITDATEJUL_UPMJ NUMBER(6),
AUDITTIME_UPMT NUMBER(6),
AUDITDATE_DATE DATE,
CONSTRAINT B2BHEADEROB_PK PRIMARY KEY (EDIDOCKEYCO_EKCO, EDIDOCNO_EDOC, EDIDOCTYPE_EDCT,
EDILINENO_EDLN, EDISUBLINE_LINS)
)

Thanks

John :)
 
Its not an issue John.
For whatever reason Oracle/JDE doesn't care to generate size limits on "some" types of numeric fields.

Dates field (UPMJ) - Yes - size limits
Time Stamp (UPMT) - No - no size limit
Address Number (AN8) - No
Order Number (DOCO) - No
Short Item (ITM) - No
Quantity (UORG) - No
Price/Cost/Amt - No

Your only concern s/b if any of the values in the external table (written to by 3rd party) exceeds the defined number limit in JDE. If EDOC won't be > 999999999999 then you're OK.
 
I'd just like to say thanks for the help.

We've got it working fine - apart from INSERT.
I can select, update and delete just I can't INSERT through E1. In SQL we can. So I suspect it maybe a middleware DD Item exception somewhere. Time to logging on :)

EDIT: Tracked to a date field (doesn't tell me which)

I had to create 5 new 11 digit fields to match the external format. So it's one of those.
As some fields in the external table were DD/MMM/YYYY.
Well, in SQL I see them as follows 1969-12-31 18:00:00

Is there a way around this?
 
Last edited:
1. How are the Date fields defined in the Database?
2. Is there any transformation happening to these fields in the View?
3. Is the virtual JDE table defined in a separate Data Source?
4. Does the Data Source have the flag checked to not use JDE Date format?
 
Hi Larry,

In my mind, I just need to pick the right alias for these date fields. I was thinking UTIME maybe? 11 Long, my new DD items are a copy of this.
So maybe I need to correctly add the values to my UTIME clones. Use B40G0010 maybe?

Here's the error
INSERT INTO TESTDTA.F57B2BHI (HIEDUS, HIEDBT, HIEDTN, HIEKCO, HIEDOC, HIEDCT, HIEDLN, HIEDSLN, HITYTN, HIFILE, HIENV, HIEDTY, HIEDSQ, HIEDST, HIEDFT, HIEDDT, HIEDER, HIEDDL, HIEDSP, HIPNID, HITPUR, HITNAC, HIDRIN, HIHLVL, HIHL03, HIVR01, HIVR02, HIVR03, HIAN8, HIKCOO, HIDOCO, HIDCTO, HISFXO, HILNID, HIMCU, HIRF1, HIPLT, HIPAK, HIDSNM, HIDICJ, HITME0, HIY57BDT, HISPID, HISHPJ, HIRDT, HIY57SHD, HIDCDT, HIDLTM, HIY57DCD, HILCR1, HITYPF, HISTQC, HISQQC, HISET, HIRFNM, HIAMAX, HITYPC, HIIRT, HIBDDR, HIAA18, HIPOQ1, HIUM, HIPOQ2, HIUOM1, HIVINV, HIVER0, HIOORN, HICSTPO, HIONR2, HIAADT, HINNBR, HIXTND, HIY57TXD, HIAA02, HIEXTL, HIITM, HILITM, HIAITM, HICITM, HIDL0, HIDL01, HIDL02, HIDL03, HIDL04, HIDL05, HIDL06, HIDL07, HIDL08, HIDL09, HIDL10, HIDL11, HIDL12, HIDL13, HIDL14, HIDL15, HIDL16, HIURCD, HIURDT, HIURAT, HIURAB, HIURRF, HITORG, HIUSER, HIPID, HIVERS, HIJOBN, HIUPMJ, HIUPMT, HIY57AUD) VALUES (' ',' ',' ','00728',1111111.000000,'OP',1.000000,1.000000,' ',' ',' ',' ',0.000000,' ',' ',0,'B',0.000000,' ',' ',' ',' ',' ',1.000000,' ',' ',' ',' ',0.000000,' ',0.000000,' ',' ',0.000000,' ',' ',' ',' ',' ',0,0.000000,115219,' ',0,0.000000,115219,0,0.000000,115219,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0.000000,' ',0.000000,' ',' ',' ',' ',' ',' ',' ',0.000000,0,115219,' ',' ',0.000000,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,0.000000,0.000000,' ',' ',' ',' ',' ',' ',0,0.000000,115219)

WRK:Starting jdeCallObject OCI0000179 - Error - ORA-01861: literal does not match format string

HIY57AUD is one of my new 11 digit dates

To answer your questions

1. How are the Date fields defined in the Database?
E1 or the external? E1 = 11 long Date : External defined as Date

2. Is there any transformation happening to these fields in the View?
Nope

3. Is the virtual JDE table defined in a separate Data Source?
No

4. Does the Data Source have the flag checked to not use JDE Date format?
No as the tables are all in the same schema


I think I chose the wrong alias type. I need to create them using UTIME (55) not Date (11) for data type field
 
Last edited:
Woooo

All sorted.

For future readers, when creating the table in E1, if your target table has a date field in the following format: 8/10/2015 10:09:37

You will need to create new DD items in E1 copied from UTIME.
Length will be 11 and data type will be (55) UTIME

Use BSFN Get Audit Information with UUPMJ to populate these types of dates

Works like a charm then. Insert update and delete
 
Last edited:
Back
Top