Real Time OneWorld to the Web Application

We are trying to determine a method to access and pass information on a real-time basis between JDE OneWorld on a AS/400 and SQL Box.... Does anyone have any ideas?

For example: The system at a store sends down a record to the SQL server an item number. When that record gets there I want to access JDE OneWorld to tell it the QOH, and send it back...REAL TIME.

I would really appreciate any help on this.

Thanks.
rhwingotif

I am not a complete expert on this whole process but I can provide some insight. You can define any type of datasource in Enterprise One whether it is DB2, Oracle, SQL ..etc. Therefore, you could define the SQL DB TABLE(s) directly. Then you could program a Oneword interface to process the data. I am not a developer so someone else could provide more info. Another way, is to get the data required on SQL and DTS specific data to a Oneworld table using ODBC (ADO, OLE DB...) connection.

Overall you would need a CNC, DBA and a developer to make this work the way you want.

There are LOTS of ways to do this. Can you define 'sends down a record'? How is all the communication done?

The point of sale system will write a record to the SQL server. For example the item number and store. It will want me to fill in the quantity available. We are using a AS/400 and the stores are using SQL servers.

Are the SQL Servers on the same network as the as/400? I know you can set up an ODBC connection from the SQL server box to the as/400. It seems the simplest thing would be to put a trigger on the sql server table that runs a program to read and calculate the available quantity from E1. I'm not sure how SQL server handles triggers though.

If they're not on the same server, you could put a winsock server on a pc (and/or) client that's connected to E1. Write the sql server trigger program to log onto the client and get the data. You, or course, get to write the winsock server and client . If you want to use the PSFT inventory calc programs put the winsock server on a fat client. Then, write your server to use the interoperability api to log onto E1 so you can call the desired BSFN.

This sounds like a lot, but just think of how big your ego will get when you do it. By the way, my tip for the winsock server is to write it to be multi-threaded. That way you can have multiple simultaneous sign-ons from many stores.

GOOD LUCK!

Yes everything is on the same network....

This what I have done up to now:

1. Created a SQL ODBC Data Source
2. Created the Data Source with JDE
3. Created an Environment for this data
4. Entered the Specs for the tables - but not gen the table
(I did this to access outside the table conversion program.)
5. WIth the TC program - I'm getting the following error:
"Unable to initialize input environment"
JDE.LOG - No Default record exist for tables for environment SRDV7SQL in OCM.

Well - I do have it there.................

Not sure what is going on..........

Ritch Wingo

Here is some supporting documents:

Personally I have only created a local datasource on the enterprise server (WIN\SQL 2K). I did this to separate the F0911P Archive table into a separate database. I am trying to get some of the logic that you have put in place. Since the key database is the web server and you want to update it real time why don't you instead create a DB2 ODBC connection on the WEB Server. Make sure you provide proper security. When the SQL table receives a record, you could trigger an SQL query to the DB2 database and return the quantity. There would be no need for table conversions and this would leave your E1 system with less modifications.

Well we are going to take this concept deeper than just a quantity.

I'm going to be developing a On-Line Registery system. At the time of Point of Sale, I want to go out and remove the SKU from the Registery database that is on the AS/400. Create a Sales Order Detail line to committ the inventory. Go out to a next numbers field and return a reservation number for the item just purchased. These items need reservation numbers, because the reservation number is used in our warehouse for items that are to big for the store locations.

I need to make this all real-time and get away from our batch processing at night.

I've done almost the exact thing you're describing when I wrote the E1 interface for a company that did all sales on the web. I can't take credit for the web front-end, which was written by another consulting company (NOT the JDE web interface). But, that place is now selling nearly $1 million dollars of inventory every day in thousands of daily transactions. It may even be 10s of thousands of transactions because all inventory is referenced and sales orders created in real time. We also did advanced pricing price books, but they were done nightly and moved to the web server for the next day. This was 2 years ago, so I don't recall everything I did, but my interfaces took about 9 months to create. The project was such a success that the entire E1 sales entry system was tossed out in favor of using the web site in-house. I should point out that zero mods were made to the E1 system. We did some copy-and-change stuff though.

Now, on your project. First of all, you don't need a new environment. You can create your tables(reference, or real) and let CNC just create OCM maps right to the SQL server. (Database user/pwds need to be set up on the SQL box to match those in E1, btw.) Just be sure that the table defs are EXACTLY correct in terms of field size and type. With my solution above we went ahead and generated the table from E1 since it was a custom solution from start to finish.

I should mention too that unless something has changed at IBM, you will never get your AS/400 to see your SQL server box. We could never do it anyway. All the updates to the sql db had to be run on a pc server (aka fat client). This only matters for data coming OUT of the 400. Data going in has no restrictions because it's simple to set up an ODBC TO the 400.

Now, on the 'running BSFNs' issue, this company bought MQ Series to handle the communication. They bought the OneWorld connector whatchmacallit. This solution required that they write their own XML parser since that's how that product works. Inventory lookups proved to be too slow with MQ, so they went to GENJAVA for a direct connection to by inventory BSFNs. That solution saved the project because it was 'smoking' fast. Sales Orders and everything else kept the MQ Series route because they were pretty much one-way and had to have guaranteed delivery, which MQ promises.

Now, like I said before, you have enormous latitude here. Lots of it depends on your companies needs, requirements, budget, and your know-how. You can get products like MQ, or the built in Xpi/COM/Java/C APIs. You can also get tricky with Winsock connections, or whatever. I wrote an HTML Server that could maintain distinct statefull connections to E1 once. I guess I’m saying that nobody is going to be able to tell you ‘how-to’ and be right. If you need to do something ‘on the cheap’ then maybe we could throw some ideas out. I like the winsock idea because you can make it multi-threaded and therefore scalable to the number of users. Plus, with C, you can go straight into the E1 system with the interoperability API. Also, I've done it before and I know it works.

Whipping boy, you have now gone to the extreme end of this interface which was not cheap, though I think MR. Tiff is looking for the quick and easy solution given that he already started on it

Anyway, any insight of what Peoplesoft are planning for E1 and portals to the web and web services?

Well, I think MQ is the only 'extreme' part of the project I referenced. The rest is part of the product, right out of the box.

1. Ok, here's the 'simple' solution:
We're looking at two things: transactions initiated from the 400 and those initiated from the SQL Server.

A. Create trigger programs on the SQL Server to update required tables on the 400. These will read and write data. The programs could calculated stuff too.

B. Create either a batch, or Timer controlled interactive program that will run on a Fat Client. I say Fat Client with the assumption that you'd use BSFNs, or some other PSFT tool. This function will update the SQL Server tables with data from the 400. You can't run the program on the 400 because it's a black hole and can't see the SQL Server.

2. 'Medium' Solution (no 'cost', but needs more techy ability):

Write a multi-threaded 'listener/server' that runs on a Fat Client. I think the 400 does winsock, so this could be on the 400. But you'll be stuck with the overhead of IBMs strict compiler. Anyhow, this program waits for contact from the ouside world. It will take requests and return real-time data (both raw and calculated).

Write a winsock client that sends one request at a time and receives an answer from the 'server'. This can be placed in a trigger program.

This solution gives you 100% access to PSFT and all its build in functions.

3. 'Hard' solution
Hire a consulting company to come in and do it for you while simultaneously charging $150 per hour. They will also suggest you buy specialized products costing several hundred thousand dollars. In the years ahead you will pay millions in license renewal fees.