Table Conversion

Table Conversion Event

• Begin Process
• End Process
• Row Fetched
• Format Fetch
• Data Changed 

1.  Process Begin event

2.  Database fetch occurs

3.  If data sequencing is set up, Data Changed

4.  If user-defined format on input,

     Format Fetched, else Row Fetched

5.  Repeat 2-4 until all input records processed

      6.     Process End event

Process Begin

       ·         First event

       ·         One-time event

       ·         No records have been fetched

       ·         Use this event to:  

       ·         Map processing options and other values to output (will not change as individual records             are fetched)

         Initialize variables and set flags

Process End

• Last event
• One-time event
• Input buffer contains the last record fetched
• Use this event to:
• Write a grand total to the output table
• Write to a log file, e.g. status of conversion
• Write the input buffer to the output table

Row fetched

·         Used when the input is a table, not a user-defined format (“flat file”)

·         Repeats for each input record

·         Input buffer contains the current record

Format Fetched

• Used when the input is a user-defined format (“flat file”), not a table
• Repeats for each input record
• Input buffer contains the current record

Data Changed

• Only available if data sequencing is specified
• Will occur on the first record in the input file
• Similar to a level-break
• Use this event to: 
• Insert a total each time the data in the sort field changes

Table Conversion Utility (TCU)

Table Conversion Navigation Assistance

Introduction                : Data Conversion, Data Copy, Data Copy with table input, Batch                                                        Delete

External Data              : Can specify the processing option or create the data structure.

Select Environment  : Select the input and output environment

Select Input                 : Select table, business view, foreign table or text file

Sequencing                 : If you want to process the selected input in a particular sequence.

Data Selection            : You can limit the input records by defining an appropriate data                                                          selection.

Select Output            : Select the table, foreign table or text file.

                                    Table Options           : Run currency triggers, clear output table, force row by row processing,                                            buffer Inserts to output table

Mapping                     : Write the even rule.

Logging option         : logging option and run in proof mode.

Finish                                     :  Give the version Name

Data Conversion:  Data Conversion consists of a single input table, business view or file and one or more output table or files. Data from records in the input is inserted into records in the outputs through mapping defined by you.

Data Copy: Data copy consists of one or more table, which are exactly copied from one environment or data source to another environment or data source.

Data Copy with Table Input: Data Copy with Table Input is similar to Data Copy except that it also allows information for the process to come from an input table. The input table might provide information about which tables should be copied, where they should be copied, and so on. Data Copy with Table Input also allows you to do data selection.

For example, suppose you create a table that has a table name, the next backup date, and the backup frequency. You might populate the table with a list of tables to be archived and how often they should be archived. You can then use Data Copy with Table Input to select all rows in which the backup date is less than or equal to today’s date, and then calculate a new backup date.

Batch Delete: The Batch Delete option allows you to delete a range of records from a OneWorld input table or foreign table, based on selection criteria that you define. For example, you can set up a batch delete table conversion that deletes any records in an input table that do not contain invalid data or records. You might also want to set up a conversion that deletes all records from a particular table.

Q1.What is a processing option and where do you attach it in a TC?

It a run time user input and you attach the PO in the “External Data” of the navigation window.

Q2.What is difference between User Insert Row and TC Insert Row?

A User Insert row can be defined anywhere but TC Insert has to be defined at the end of the event.

Q3.What is a business view?

 Business view is a link between application and data. Business view is generated on one or more tables.

      Q4.What is the flow of Events in a TC?

Processes begin, Data Changed, Row fetched, Format Fetched and Process End.

      Q5.What type of Conversion you can do in a TC?

Data Conversion, Data Copy, Data Copy with Input Table and Batch delete.

      Q6.Why User is defined format used?

It is used to deal with fixed-width or Character separated value (CSV) in a table or a text file.

      Q7.When the data is changed event invoked?

Each time the value in the column defined in the sequencing changes, the event is invoked.

      Q8.What is use of a handle?

It is used to create multiple instances of a table in the same TC.  To safe guard the table, it is advisable to use handles or business view.

      Q9.How many outputs can be attached in a Batch delete? None.

Q10.What is difference between “Fetch Single” and  “Select, Fetch Next”?

Fetch single retrieves only a single row (based on primary index) and select Fetch Next retrieves more than 1 record.

Q11.What is foreign table?

Non-JDE table are called foreign table not defined by One World.

Q12.How many inputs a TC can have?

One.

I would like to know whether a data item created in OMW can be deleted.

Answer1
There are multiple ways to delete a data item created in OMW. Yes you can delete it. What you have to be careful about is that no applications are accessing it, and you have to re-generate your data dictionary tables.

Answer2
Data items created by users can be deleted from dd. but before deleting please consider the impact on all applications referring to the data item.

How do I write a CSV file on the second worksheet in XLS?

Answer:

If you are writing a CSV file, you can not simply write to the second worksheet in an XLS. Below are some ways it can be done; there may be more.

1) Produce a CSV file for each worksheet desired. Then pre-build an Excel Worksheet with a Macro that reads the CSV files and loads them in as desired. I have used this method, and it worked great in 7.5. In 8.4, it is more cumbersome; each user must download the XLS file and CSV files to their hard drive, then execute the macro.

2) Create an XML document in SQR that can be read in Microsoft's Excel. One limitation is that the user must have a newer version of Excel to read the XML effectively. If you have Excel 2003 (maybe Excel XP), take a look at the output of saving an Excel document in XML. It comes back into Excel with the same level of detail.

I need to do a table conversion without using the TC tool. Is it just a matter of doing the ER in a BV instead of in the TC?

Answer1
we used the report writer to read the tab or comma delimited flat files we created from our Legacy systems, and have them outputted to a JDE table or z-file. Within the UBE, we performed the necessary checks to ensure the data was valid.

Answer2
There are a couple of ways to do this.

If you are writing to a table defined within JDE you can use Table I/O to write records through the UBE Event Rules.

The Report Design Aid (RDA) also has the functionality to export data from a section to a non-JDE table using the Database Output option. Basically, you set focus on the section that you want exported, select "Section" and select "Database Output".

Then, define the Database Output parameters. The Name field will be the name of the output file. The Operation(s) can be Inserting Only, Update, Insert or Update or Delete. The Type can be a fixed length record file, or Comma delimited file. For a Fixed length record file or Comma delimited file, the file name must end in ".txt".

Next, map the parameters of the input business view columns to the output file. The Source Section is the name of the Section and the Source Object will allow you to select the business view column or variable you wish to export (double click on the blank field).

We are migrating to JDE OneWorld Xe. What might be considered the minimum core courses to take following the overview of OW Xe?

Answer

First, OneWorld is much different than World, obviously there is a whole client/server structure. Programmers need OneWorld Overview Xe (as does
almost anyone on the cutover project team to take training) and System Fundamentals Xe.

C is rarely required in OneWorld programming, less than 95% of the time. This was my gut feel and is backed up by three of my programmers.

Development Tools 1 Xe is important because of the obvious overview to development tools, forms, forms design aid and OMW, and essentially project based code control. This also gives an introduction to Event Rules.

Development Tools 2 is more detailed programming of Event Rules. Essentially Event Rules allow you to create business logic. From the JDE documentation:

Use Event Rules Design to create business logic for an application. For example, create event rules that:
Perform a mathematical calculation
Pass data from a field on a form to a field on another form
Count grid rows that are populated with data
Interconnect two forms
Hide or display a control using a system function
Evaluate If/While and Else conditions
Assign a value or expression to a field
Create variables or programmer defined fields at runtime
Perform a batch process upon completion of an interactive application
Process table input and output, data validations, and record retrieval

Your technical staff should likely be able to learn ERW (Enterprise Report  Writer, now a single reporting tool in OneWorld) but should learn it on its own. The class is Enterprise Report Writing Xe.

You'd likely want to send someone (not necessarily all programmers) to Design Batch Applications Xe and J.D. Edwards OneWorld Scripting Tool Xe. We've found having someone at Table Conversions Xe helps during conversions and interfaces. You may be able to learn the table conversion tool on your own.

If you are going to access the data from other applications, use XPi, etc. there is additional training.

I am having a problem with interactive versions. I need to show and hide certain columns depending on versions. I am using SL VersionName to solve this, but the problem is that the value in SL VersionName is the same for all versions. Does anyone have any suggestions?

Answer1
To show or hide columns depending upon versions, you should have a Processing Option attached to the Report. You can have an item in the Processing Option for this purpose and based on the PO value you can hide/show the columns.

Answer2
there is another workaround I have used successfully without having to change the Processing Options template. It's not elegant, but it works. Try this

Create one or more custom UDC tables to store your version and the flags you need, and read them during the Dialog is Initialized or Post Dialog is Initialized event at runtime.

For example:
I used UDC table 58/P1 to store a flag to display or not display certain form controls on the application:
Code Description 1 Special Handling
IAA0001 Display date columns 1
IAA0002 No date display 0

Then during the Dialog is Initialized event, I added code to get the Special Handling Code from the UDC table using the N0800031 Business Function N0800031. I used the SL VersionName to pass into the Business Function, then used the output to hide or show the columns as applicable.

Answer3

It will be difficult to handle this situation if you don't use the PO template for handling different versions. There are other ways to do it, though. If you know the situations where a particular version is used, then by specifying that situation flag you can hide or show columns with different versions.

How can I retrieve Ms Access data from my OneWorld application in real-time
mode?

Answer1

You can create a business function to make the ODBC connection, and retrieve data from Access tables as long as you know how to write the code.

Also, there is a straight forward way through JDE Table conversion tool, you can manage the data transfer to and from OneWorld. There is a complete documentation for TC at Oneworld documentation/Technical section.

Answer2

I've created an ODBC to point to an SQL database, and used the Data Conversion program to look at Foreign Tables. Works like a charm.

I understand that JDE will be supporting World indefintely. Does that mean that there will be new versions of World being distributed, or is 8.12 the final version?

There will be new versions of World. Current functionality will be enhanced and upgraded at some point. The biggest issue will be the lack of new functionality in One World or at least the lag time involved in functionality being built into One World being incorporated into World. XPIe, for example, was available in One World long before the adapter was even written for World and it still is supposed to be much less functional. An automotive piece is being worked on in One World and I did not hear it would be done in World as well. So if you can do without some functionality and the new web enabled tools, or are going to do the web enabled portion on your own it is less of a problem. It all depends on your situation. The products will not be mirror images of each other. If you are happy with World and the screen scraper or even green screen environment is good enough then you can stay with it until you are no longer happy with it and then move to One World later.

ActivEra Explorer

Question 1 - Why is a specific Task View hidden?

Go to User Defined Code (UDC) table H90/TR. If the special handling code is N, the task view will not appear on the task view list.

Question 2 - How is the documentation window made visible?

 Select View, then Show from the menu bar. Verify that the Task Documentation box is checked.

Question 3 - How can the documentation on a specific tab be edited?

The tabs are all listed on the User Defined Code (UDC) table H90/IN. J.D. Edwards ships the product with the Summary, Detail, Task View Summary, Before You Begin, and Interview Question tabs as tabs that may not be edited (i.e. there is no E in the Special Handling field of the UDC table.)

The UDC table can be changed to permit the editing of these tabs. If the UDC table is changed, a new release of the software will override the documentation changes made by the user and the tabs again will have a blank in the Special Handling field of the UDC table. If other tabs are changed, or new tabs are added, a new release will not override the changes or new tabs. They will merge with new content without being overridden.

Add a new tab to this UDC table by adding a record that identifies the title of the documentation with the extension .html. For example, identify a new tab called "Summary" as "Summary.html."

Question 4 - Why does double- clicking a program task not launch the program?

Is the icon next to the task the expected type of icon?

If the icon is not correct, right mouse click on the task and select Task Revisions. On Task Revisions, select the Executable tab. Verify that the correct radio button is selected.

If the above does not correct the issue, place a call with J.D. Edwards' Global Support Services for assistance in resolving the issue.

Question 5 - Why is no documentation showing in the documentation window?

Select Media Objects under System Administration Tools (GH9011). Select Media Object Queues. Click Find. On the field called Queue Name, look for ActivEra Data. Double-click on this selection, and verify that the path correctly identifies the location of the documentation.

Question 6 - How can a group of tasks be run as an activator?

Right-click on the parent task, and select Task Revisions. Enter a 1 in the Activator Type field. Click OK. When reviewing the task after this change, a light bulb should display on the parent task indicating that the task is an activator.

Question 7 - A link from an Implementation Approach task to the Configuration (program) tasks is not working correctly. A red arrow on the task indicates there is another task view. How can this be corrected?

Right-click on the task, then select Task Revisions. On the Form menu select Link To. Do the fields contain values?

Re-do the fields by clicking on Find. Select Task View 96 (Configuration Tasks) and click Find again. Go through the tree to locate the task for the link. Press Select. This should populate the fields in the Link To form appropriately. Test again.

If the link still does not work, go to the Configuration tree from the Task View list and verify the task is "turned on" (i.e. it shows up on the tree). If the tasks do not appear on the Configuration Tree, it is possible that the tasks were disabled by Rough Cut or by Fine Cut. Enable the tasks with Fine Cut if they are disabled. To enable a task, click on the button that displays scissors. The "on" tasks have a green check mark. The "off" have a red X. Double click to turn a task on or off. Then test the link again.

Question 8 - How can a known task be located when it is not visible in the tree structure?

If the task was disabled with either Rough Cut or Fine Cut, it can be seen by clicking the button on the tool bar called Show/Hide Disabled Tasks. This will show all tasks. Disabled tasks are identified by a red circle on their left. To enable tasks, click the Fine Cut button, double-click the tasks to enable, then click Save.

Question 9 - How can Fast Path be activated?

Select Show from the menu bar. Place a check mark in the box beside Fast Path