JDE PGMR,
Welcome to the realm of a Virtual Table - one 'good' solution for your
situation.
As has been discussed before - If you can create an SQL Statement over a
table (or tables), you can probably create an SQL View over it/them, also.
1. Determine which Columns you want to make available to E1
2. Create an SQL Statement that will pull back those values
3. Duplicate that SQL Statement - this time renaming the
fields (Select ExampleField1 V1FIELD1, ExampleField2 V1FIELD2, ExampleField3
V1FIELD3 from XampleTable). Note - you have to use JDE Naming Conventions
for all the column names. Each Column name must be prefixed by a
two-character value, followed by a VALID E1 Data Dictionary Field/Value name
4. Create the SQL View: Create View F55XMPL as ("Use the SQL
Statement created in step 3")
5. CHANGE SECURITY TO *PUBLIC at the Database Level (you can fix this
later, after the proof of concept)
6. DESCRIBE THE VIEW - so you have a template for the next steps
7. Create Data Dictionary items for fields in external table that did
not previously exist - making absolute sure they match types and constraints
(Char ar Char, Numeric are Numeric and Length/Decimal match exactly). Place
as few constraints as possible - you don't want to limit, till the proof of
concept is 'proven'.
8. Create the Virtual E1 Table - In FDA create a new table that:
1. Exactly matches the name of the SQL View
2. Exactly matches the Column Header (First two characters of
Column name)
3. EXACTLY matches the Field Sequences (match the DD items
agains the newly renamed column titles
4. DO NOT GENERATE THE TABLE
9. Test using UTB (yes, UTB is available in ALL versions of E1 -
though it may have been secured, it is there). Use UTB so you can look at
the JDE.LOG if there are any issues.
Now - you have a 'Virtual Table' in E1 that can see data from a third-party
application. Things to consider include:
1. The SQL View 'should' exist in an existing E1 Datasource (at least
for testing). If the Target Data is in a third-party location, consider
creating a new datasource ( you do not want to cross
databased/libraries/tablespaces with SQL Views.
2. If you accidentally Generate the table in FDA, simply Drop the View
and recreate it using the Database script/statement
3. Most common issue is Security - at the DB level, *Public to start,
then close the hatches till it breaks
Enjoy!
(db)
--
