RE: accessing foreign tables from interactive applications

BBritain

VIP Member
RE: accessing foreign tables from interactive applications

Stefan,

Since Zoltan has not yet asked you for the obligatory system info, I can
only respond with what has worked for me and my clients.......If you can
translate each of the actions to your system your in luck.

My particular client is running Oracle (don't know version) on a Unix box
(it is called sun02 if that helps). The physical file is a collection of
fields in a filename all of which are invalid to JDE OneWorld. In OneWorld
I created a TBLE named F55AP02 which specified JDE DD items that matched the
foriegn table types as much as possible. I then generated the table and
then went into Oracle and dropped the table. I also created a BSVW on the
table. Then, in Oracle, I created a view on the foriegn table -- sort of
like mapping from the foriegn table to the JDE TBLE specs. example is
below. The part I kept missing is I then needed to issue the command: GRANT
SELECT ON F55AP02 TO PUBLIC. Once I did this I can now see the table in UTB
as well as I can look at the table in an APPL. I have found a few caveats
though --- 1) If you wish to be able to update the file then the field to
field typing must be exact. 2) If you wish to be able to delete records
then your keys need to be exact OR you must 'play' with the key values until
the SQL statement created by JDE matches the key values. 3) Your best bet
is to create the view while logged into your SQL session as OneWorld logs in
(your CNC person can tell you what that is) and, 4) Be sure you Create your
view in each environment.

The following sample is used to interface with MP5 (DataStream) but only MP5
into JDE.

Ben (again)

SAMPLE SQL CODE FOR CREATING A VIEW TO A FOREIGN TABLE

CREATE OR REPLACE VIEW F55AP02
AS SELECT
IMI_CODE AS APDOC
, IMI_SUPPLIER AS APAN8
, IMI_INVLINE*100 AS APLIN
, TO_CHAR(IMI_DATE, 'MM/DD/YY') AS APIT09 --Date of Transaction/Invoice
, TO_CHAR(IMI_PAYDATE, 'MM/DD/YY') AS APCEHD --Date of Payment/Due
, TO_CHAR(IMI_RECORD, 'MM/DD/YY') AS APUPDA --Date of Reg/Batch
, TO_CHAR(IMI_GLDATE, 'MM/DD/YY') AS APDATE --Date of G/L
, IMI_PRICE*100 AS APAEXP
, IMI_QTY AS APSOQS
, IMI_MRC AS APMCU
, IMI_REF AS APVINV
, IMI_ORDER AS APDOCO
, IMI_ORDERLINE*1000 AS APLNID
, IMI_DESC AS APDESC
, IMI_ACCOUNT AS APANI
, IMI_REMARKS AS APRMK
, IMI_TYPE AS APAA04
FROM jdedba.jdeinvoicemp5interface
 
I just want to thank this really great idea.
It would be useful for us in the future.
I will keep it mind.
Zoltán
PS: ... I have almost forgotten my (and Eric's?) favorite
Place your system configuration information in your signature!
:)
How is your post starting?
"Since Zoltan has not yet asked you for the obligatory system info,"
:)

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Back
Top