featured-image   

Overview

Every enterprise holds a forest of branched system spread across a number of business units deployed over time. Moreover these systems bring a natural challenge for data migration at the time of system migration towards any ERP suite such as Oracle JD Edwards. Fortunately these data migration challenges are well covered inside JDE through multiple mechanisms. In the series of blog posts I am going to cover some of the possibilities of data migration with Oracle JD Edwards

Definition of Interoperability

According to Wikipedia

“Interoperability is the ability of diverse systems and organizations to work together (inter-operate).”

As any ERP system needs to work together in a heterogeneous environment, Oracle JD Edwards is also not far off, and provides number of mechanism to interoperate with other legacy system with not only the ability to import the data from other systems but also provide communication mechanism to export the data out through number of different mediums.

Available Models for Interoperability

Following diagram outlines the number of interoperability options available with JD Edwards Enterprise One

JD Edwards E1 Interoperability Models

As you can see a number of different in/out communication options are available ranging from exposed Web Services, COM or Java based Connectors, but also to standard EDI based flat file imports.

 

Interoperability Scenario

In this article we are going to cover a typical interoperability scenario as follows

We are going to be receiving inventory records from our legacy system using a flat file to populate our Item Master table (F4101). In the first stage, we will be using Table Conversion(TC) to import our incoming flat file in to our intermediary Z Table (F4101Z1) and in the second stage  we will be using a batch application(R4101Z1I) to transfer these imported records from our Z Table safely to our Item Master table (F4101).

Important Terminologies

You must be wondering about some of the terminologies in the given scenario so its best to explain them first

  • Batch Applications, These applications allows the execution of large variety of business related functions and can be scheduled with the power of CNC (Configurable Network Computing) and can share their process load through the use of different application servers.
  • Table Conversion, A batch application that allows multiple modes of mechanism to transfer data from one source to another. A source/destination can be internal JDE table or a flat file etc. It provides multitude of features for connectivity & filtration of data stream.
  • Z Table, In a nutshell,these hosts of internal tables provides a safe mechanism to review & audit input data before the final migration in live environment.

Now with these definitions in place, let’s begin

Stage-1: Import from Flat File to Z Table through Table Conversion

Prerequisite

Ensure a properly formatted file format with all the required fields to be populated in the Z-Table. It is highly recommended to review and understand the target table structure

JD Edwards Table Conversion flat file EDI format

Execution

  1. From the Object Management Workbench (OMW) create a new batch application.
  2. On the “Add Object” screen, provide an “Object Name” & “Description” for your object & select the “Table Conversion” checkbox. Provide other information & Save your entry.
  3. On the “Batch Application Design” screen, switch to “Design Tools” tab and press the “Start Table Design Aid” button. This will launch the “Table Conversion Director”. At this point a Wizard will be launched to guide you in a step by step formation to create your table conversion.
  4.  On the “Introduction” screen, select the “Data Conversion” option (selected by default) and press the “Next” button.
  5. On the “Select Environment” screen, select “<LOGIN ENV>” for both input and output environment and press the “Next” button.
  6.  On the “Select Input” screen, switch to “Text File” tab. Provide the complete path to your source flat file in the top textbox and press the “Use” button. As your file is in a custom format, now you need to define it. Check the “User Defined Format” checkbox and press the “Define format(s)” button.
    • On the “User Defined Format-Type” screen, select the “Delimited” option in format type and select “Single Format” in row formats. Press the “Next” button.
    • On the “Column Delimiter” screen, select the “Comma” in the “Column Delimiter” and “Double Quotation” for “Textual Qualifier”. Press the “Next” button.
    • On the “Column Layout” screen, define the format of your source file. Use the “Add” button to add entries as shown in the following diagram. Press “Next” when you are done.JD Edwards Table Conversion User Defined Format Column Layout
    •  Press “Next” on the Summary screen.
  7. On the “Select Outputs” screen, select the “Table” tab and filter out for “F4101Z1” table. Double click to select it. Press the “Next” button to proceed.
  8. On the “Table Options”, uncheck the “Run Currency Trigger” checkbox as we are not doing any currency conversions. Press the “Next” button to proceed.
  9. On the “Mapping” screen, we now have to defined the mapping for our transfer from our source file format to our Z table. Follow the steps below
    • From the “Events” drop down, select the “Row Fetched” event and check the “Issue a Write for this Event?”.
    • Next, from the “Events” drop down, select the “TextLine1 Format Fetched” event and press the “Advanced ER” button.
    • In the “Event Rules Design”, you now need to provide your mapping from input to output. Use the “x=” option to provide these mappings as shown below. JD Edwards Table Conversion Event Rules Design Mapping
    • Save your entries and Exit from “Events Rules Design”. Press “Next” button to proceed.
  10. On the “Logging Options” screen, select “Log All Errors” and press the “Next” button to proceed.
  11. On the final screen, select the “Yes, create a version of this table conversion” and provide name for your table conversion version. Press “Finish” to close.
  12. Exit from “Table Conversion Director”.
  13. On the “Batch Application Design” screen, on “Design Tools” tab, select the “Version List” button. This launches the (P98305)“Working with Batch Versions – Available Versions” application.
  14. At this point you should be able to see your Table Conversion created version, double click to select it.
  15. On the “Table Conversion Prompting” screen, leave all the values to default and press the “Submit” button.
  16. At this point, your table conversion will be submitted for processing.
  17. Once done you can review your Z Table (F4101Z1) using Universal Table Browser(UTB) to confirm your entries.

Stage-2: Import from Z Table to Master Table

Prerequisite

At this point, your entries have been transferred from the flat file to the target Z Table. Review your entries using the UTB and ensure appropriate transfer of the entries. Once satisfied, you are now ready to transfer these entries from your Z Table to your Master table.

Execution

  1. From the FAT client, type “bv” in the FAST Path to launch the (P98305) “Working with Batch Versions – Available Versions”.
  2. In the “Batch Application” field type “R4101Z1I” (without quotes) and press tab key. Your version list should now be populated.
  3. Select the “ZJDE001” version by double clicking on it.
  4. At this point, if you want to filter your specific records, you can check the “Data Selection” checkbox, otherwise press the Submit button to submit your batch version.
  5. On the “Processing Options” screen, press OK to proceed.
  6. On the “Report Output Destination”, select the “On Screen” entry press “OK”.
  7. Upon completion of the execution, your PDF report will be displayed that specifies the entries processed. If the entries were successfully transferred, the “Processed (Y/N)” column will say “Y” for those entries.

Troubleshooting

In case of issues, the first thing you should do is review the “Work Center” which can be accessed from the FAT client (Tools > Message Management > Work Center) for any reported errors in your data / import process. Review this information very closely as provides relative guideline for corrective actions as well. If that doesn’t resolute to a solution, JDE Logs along with Debugging your table conversion and batch application are the best available option for troubleshooting the entire process.

Finale

As you can see that Oracle JD Edwards provide a huge variety of solution for managing your data migration, in the subsequent post we will discuss other forms of methods available.