Skip Headers
JD Edwards EnterpriseOne Tools Development Tools: Data Access Tools Guide
Release 8.98 Update 4

Part Number E14702-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Setting Up Table Conversions

This chapter contains the following topics:

9.1 Understanding Table Conversion Setup

Oracle's JD Edwards EnterpriseOne Table Conversion Design Aid uses a director to guide you through the steps of setting up table conversion templates. Using the director, you can design table conversions for:

You can also define user-defined formats for use as input or output tables in table conversion templates. User-defined formats are flat-file tables that store data as a continuous string of information, such as bank tapes.

After you set up a table conversion template, you can save it, create a version, and run the version multiple times.

9.2 Understanding the Table Conversion Process Flow

When you process a table conversion, the system triggers events that are similar to the events that are triggered when a report or application is run. These events are specific to the table conversion that you defined. Events provide pauses in the processing of the table conversion where you can attach logic.

In general, the event flow is the same for all table conversion types because these conversion types are all subsets of a data conversion:

The flexibility of events enables you to use table conversion types within other conversion types, if necessary.

Depending on the type of conversion that you define, some events might not be triggered. This diagram illustrates all events that can be triggered in a table conversion; events are indicated by yes or no decisions:

Figure 9-1 Table conversion event flow.

Description of Figure 9-1 follows
Description of "Figure 9-1 Table conversion event flow."

This table describes when each event occurs during the table conversion process:

Event Description
Process Begin Before fetching records from the input table, the system invokes the Process Begin event. At this point, you can attach logic that needs to run only once at the beginning of a table conversion, or attach any value that does not change for each record. This event is useful for mapping output fields that do not change for each record.
Data Changed When you use data sequencing, the system invokes a Data Changed event for any sequenced field that changes. Data Changed events are not cascaded or hierarchical. For example, you can attach an event rule to this event to total a field or group of values.
Format Fetched When you use user-defined formats (also known as flat files) in the input table, the system invokes a Format Fetched event for each record that is fetched from the input table. If you use multiple user-defined formats in a table conversion, the Format Fetched event is called for the particular format that is found in the record.
Row Fetched An input table invokes a Row Fetched event after each row is fetched from the input table.
Process End After the processing of all records is complete, the system invokes the Process End event. Attach event rules to the Process End event when you want the system to process logic after all input records have been read; for example, to write a total record to an output table or to write a record to a log file to record the status of the table conversion.

9.3 Adding Table Conversions

This section provides an overview of the navigation assistant, lists prerequisites, and discusses how to:

9.3.1 Understanding the Navigation Assistant

The JD Edwards EnterpriseOne Table Conversion director includes a navigation assistant. The companion Navigation Assistant tracks where you are in the table conversion development process. You can right-click the Navigation Assistant to hide it for the current design process. The navigation assistant provides a visual indicator of the progress in designing the table conversion. Click a step on the navigation assistant to move forward or backward to another step in the process.

9.3.2 Prerequisites

Before you use the table conversion process, ensure that you:

9.3.3 Forms Used to Add New Table Conversions

Form Name FormID Navigation Usage
Object Management Workbench W98220A EnterpriseOne Life Cycle Tools, Application Development (GH902), Object Management, Object Management Workbench Select and view objects in projects, and access the JD Edwards EnterpriseOne design tools.
Add EnterpriseOne Object to the Project W98220C Click the Objects node of a project and click Add. on the Object Management Workbench form. Add a new object to a project.
Add Object W9861AF Select Batch Application and click OK on the Add EnterpriseOne Object to the Project form. Create a batch application object.
Batch Application Design W9860AL Complete the object information and click OK on the Add Object form. Access the Table Conversion director.

9.3.4 Adding New Table Conversions

Access the Add Object form.

Object Name

Enter a unique name for the table conversion. Use the recommended naming convention for JD Edwards EnterpriseOne objects.

Description

Enter a meaningful description of the table conversion.

Product Code

Select a user-defined code (UDC) (98/SY) from the range of values that is reserved for clients, 55–59.

Product System Code

Select a UDC (98/SY) that represents the Oracle's JD Edwards EnterpriseOne system where the data is used. This value is used for reporting and description override purposes. Example values include:

01: Oracle's JD Edwards EnterpriseOne Address Book

03B: Oracle's JD Edwards EnterpriseOne Accounts Receivable

04: Oracle's JD Edwards EnterpriseOne Accounts Payable

09: Oracle's JD Edwards EnterpriseOne General Accounting

11: Oracle's JD Edwards EnterpriseOne Multicurrency

Object Use

Select a UDC (98/FU) that indicates the use of the object. For example, the object might be used to create a program, a master file, or a transaction journal. A 132 value represents a data file conversion.

Object Type

Displays an abbreviation that identifies the type of object that is being created. The object type for a table conversion is UBE. This field is populated by the system based on the type of object that you selected on the Add Object form.

Table Conversion

Select to enable JD Edwards EnterpriseOne Table Conversion Design Aid. If you do not select this option, Oracle's JD Edwards EnterpriseOne Report Design Aid is launched by default.

9.3.5 Starting the Table Conversion Director

Click OK on the Add Object form:

  1. Select the Design Tools tab, and click Start Table Conversion Design Aid.

    The system displays the introduction form for the JD Edwards EnterpriseOne Table Conversion director.

  2. Proceed to the task for the type of conversion that you want to perform.

9.4 Converting Data

This section provides an overview of data conversion and discusses how to:

9.4.1 Understanding Data Conversion

Use the Data Conversion feature on the JD Edwards EnterpriseOne Table Conversion director to move data into tables from:

  • A single table.

  • Multiple tables that are defined in a business view.

  • A single text file.

9.4.1.1 Data Conversion Example

This data conversion example illustrates how to copy employee records from the F0101 table in the Login Environment to the F0101 table in the PY812 environment.

  1. On the Introduction form of the JD Edwards EnterpriseOne Table Conversion director, select Data Conversion, and click Next.

  2. On the External Data form, accept the default selections, and click Next.

  3. On the Select Environments form, select <LOGIN ENV> as the input and PY812 as the output environment, and click Next.

  4. On the Select Input form, drag the Address Book Master table to the Description column on the Table tab, and click Next.

  5. On the Sequencing form, accept the default selection, and click Next.

  6. On the Data Selection form, define the data selection as Where AT1 (Search Type) (F0101) is equal to E to select current employees, and click Next.

  7. On the Select Outputs form, drag the Address Book Master table to the Description column on the Tables tab, and click Next.

  8. On the Table Options form, clear the Run Currency Triggers option because the Address Book Master file does not contain currency information, and click Next.

  9. On the Mapping form, select the Row Fetched event because you are converting data from similar files, click Map Same, and click Next.

    The system automatically maps data elements with the same aliases.

  10. On the Logging Options form, select Log All Errors and Log Outputs, and click Next.

    These options enable you to review the conversion after it is complete.

  11. On the Finish form, select No, I will create a version of this table conversion later, and click Finish.

  12. On the Table Conversion form, click Save before quitting.

9.4.2 Forms Used to Convert Data

Form Name FormID Navigation Usage
Object Management Workbench W98220A EnterpriseOne Life Cycle Tools, Application Development (GH902), Object Management, Object Management Workbench Select and view objects in projects, and access the JD Edwards EnterpriseOne design tools.
Add EnterpriseOne Object to the Project W98220C Click the Objects node of a project and click Add. on the Object Management Workbench form. Add a new object to a project.
Add Object W9861AF Select Batch Application and click OK on the Add EnterpriseOne Object to the Project form. Create a batch application object.
Batch Application Design W9860AL Complete the object information and click OK on the Add Object form. Access the Table Conversion director.

9.4.3 Defining External Data for Table Conversions

Access the JD Edwards EnterpriseOne Table Conversion Director.

  1. Select Data Conversion, and click Next.

  2. On the External Data form, click Select to attach a predefined processing option template to the table conversion.

  3. On the Select Processing Option Template form, select an appropriate processing option template, and click OK.

  4. On the External Data form, click Define to create a data structure.

  5. On the Report Data Structure form, define the table conversion data structure, and click OK.

    Data structures contain a list of parameters that can be used to pass data into the table conversion when called through a report interconnect.

  6. Click Next.

9.4.4 Defining Input and Output Environments for Table Conversions

Click Next on the Introduction form of the JD Edwards EnterpriseOne Table Conversion director to access the Select Environments form.

  1. Select the input and output environments to use.

    Note:

    Select <LOGIN ENV> if you are creating table conversions for a user who might not have the same environments that you have. This option ensures that the table conversion uses the environment that the user is signed in to.
  2. When creating a table conversion to be run in a different environment from the one where you create it, and the <LOGIN ENV> is not appropriate for the type of conversion that you are creating, select either Force Version to Override Input Environment or Force Version to Override Output Environment, and click Next.

    For example, if you create a conversion to be used by a client who does not have the environments that you have, use these options. When the conversion is invoked at the client site, the table conversion does not run until the user selects the appropriate environments in which to run it.

9.4.5 Defining Table Conversion Input

Click Next on the Select Environments form to access the Select Input form.

  1. Select the appropriate tab based on whether the input is a table, a business view, a foreign table, or a text file.

  2. Use the query by example (QBE) line to search for the appropriate table, business view, or foreign table.

    You can select only one table or one business view per table conversion. If the input consists of multiple tables, you must create a joined table business view.

  3. Enter the name of the text file on the Text File tab, or use the Browse button to locate the file.

    If you want to rename the selected file, enter the new file name, and click Rename.

    For the iSeries, input text files are stored in the Integrated File System (IFS). Enter the path to the IFS before the file name.

    If you are using a text file, or if you need to define a format for a table or business view, select the User Defined Format option.

    Note:

    Text files are the only type of file that you can rename.
  4. If the input is a table or a business view, drag it to the Description column.

    If you change a table or business view, the system warns you that deleting tables removes all mappings from the table conversion.

  5. To delete an input file, select it and press Delete.

  6. Click Next.

  7. On the Sequencing form, click Data Sequencing to define data sequencing for a table or business view.

    If you specify a text file for input, you cannot define data sequencing or data selection for that file.

    Note:

    When you define data sequencing, you create new events that are available to you in the Mapping section of the director. One new event is created for each sequence column that you define. The event is called XXXX Data Changed, where XXXX is the column alias, for example, ALPH Data Changed. Each time the value in one of these columns changes from its previous value, the Data Changed event is invoked for that column. This event is similar to a level break in report templates, except that the Data Changed events are not related to each other. Invoking one does not invoke the others.
  8. Click Next.

  9. On the Data Selection form, click in the Operator column, and select an appropriate operator.

    On the Data Selection form, Where is the default value in the Operator column for the first set of criteria. For subsequent statements, And and Or become the available values for the Operator column.

  10. Click the Left Operand column, and select from a list of available objects.

    Note:

    You can define selection criteria for database table columns only. User-defined format columns are not available because they do not exist in the database.
  11. Click the Comparison column, and select one of these comparison operators:

    • is equal to.

    • is equal to or empty.

    • is greater than.

    • is greater than or equal to.

    • is less than.

    • is less than or equal to.

    • is not equal to.

  12. Click the Right Operand column, and select from a list of available objects, special values, or variables.

    The options in this column depend on the selection that you made in the Comparison column. Some of these options might be available:

    • Blank

      Enters a blank (space) value.

    • Literal

      Enables you to enter specific values, as described in the next step.

    • Null

      Indicates that no value is associated with the field.

    • Zero

      Enters a value of 0.

    • IC

      Indicates input table columns.

    • RI

      Indicates values passed through a report interconnect to this table conversion.

    • PO

      Indicates processing option values for this table conversion.

    • SL

      Indicates system literals.

  13. If you enter a literal in the Right Operand column, you can enter values on these tabs of the Single value form:

    • Single value

      Enables you to enter a single value. An example might be a value that indicates a specific company.

    • Range of values

      Enables you to enter a range of values. An example might include companies from 00001 to 00060. When you are using a range of values, only the is equal to, is equal to or empty, and is not equal to operators are valid.

    • List of values

      Enables you to enter a list of values. Enter each value individually in the field, and click Add after each entry.

      An example list of values might include several user-defined codes for search types, such as C for customers, E for employees, and V for vendors. When you are using the list of values, only is equal to, is equal to or empty, and is not equal to are valid operators.

      To delete a value, select the value and click Delete.

  14. To delete a line of criteria on the Data Selection form, select the row header and click Delete.

  15. To change the order of the criteria, select the row header, and click the Up or Down button.

  16. Click Next.

9.4.6 Defining Table Conversion Output

Click Next on the Select Input form to access the Select Outputs form.

  1. Drag the table (or tables) that you want to use as outputs to the Description column, and click Next.

  2. For text file conversions, from the Text File tab, select the file to use as the output, and click Use.

    If you want to rename the selected file, enter the new file name, and click Rename.

    For the iSeries, input text files are stored in the IFS. Enter the path to the IFS before the file name.

    If you are using a text file, or if you need to define a format for a table or business view, select the User Defined Format option.

    Note:

    Text files are the only type of file that you can rename.
  3. Click Next.

  4. To delete an output, select the row and press Delete.

    If you are using a text file, or if you need to define a user-defined format for a table or business view, click User Defined Format.

  5. Click Next.

  6. On the Table Options form, select from these options:

    • Run Currency Triggers

      Select this option if a JD Edwards EnterpriseOne table that is included in the table conversion contains currency triggers. If a table contains currency fields and you do not select this option, the system cannot determine where the decimal should be placed within a field. If you do not select the currency trigger option, and the source or destination fields are currency fields that are used in a calculation, you might receive unexpected results.

      Do not select the Run Currency Triggers option if the input and output data sources are the same type (for example, Oracle, iSeries, or SQL Server) and no calculations are performed. Selecting this option results in slower performance.

      You should not use currency triggers for an environment that has a different path code from the sign-in environment.

    • Clear Output Tables

      Select this option to clear the output table before the table conversion runs.

    • Force Row by Row Processing

      Select this option to test the table conversion or to ensure that the conversion always runs in row-by-row mode.

      You can test a conversion to ensure that the mapping logic is correct. In this case, specify the number of rows to process. The number of rows can be defined in the jde.ini under [TCEngine] or when you submit the table conversion.

      Select this option if you know that the values in the input table will produce duplicate keys in the output, and you want only the non-duplicate keys to be inserted.

      Selecting this option results in slower processing.

    • Buffer Inserts To Output Tables

      Select this option to improve conversion performance if the conversion does not include any event rules to process insertion errors and you are processing row by row.

  7. Click Next.

9.4.7 Mapping Inputs to Outputs

Click Next on the Select Outputs form to access the Mapping form.

  1. Select the event on which mapping will occur.

    In most cases, you use either the Row Fetched event or Format Fetched event. Use the Format Fetched event if you are working with a user-defined format.

  2. Click Advanced ER to include additional event rule logic in the table conversion.

    The Event Rules Design form appears for the event that you selected on the Mapping form.

  3. Click Map Same to allow the system to map the inputs directly to the outputs.

    If the input and outputs share some of the same data, these fields are a direct map. For JD Edwards EnterpriseOne tables, the system maps fields by data dictionary item. For foreign tables, the system maps by column name.

  4. Drag inputs to outputs to manually map the inputs to the outputs.

    Note:

    Click Delete to erase the mapping for a selected output. Click Delete All to erase the mapping for all outputs.
  5. If multiple output files exist, select each file from the outputs list, and map the appropriate input columns to the appropriate output columns.

  6. To define advanced output, double-click an output column.

    The Advanced Outputs form enables you to define literals, calculations, and other mappings without using Advanced ER. You can use an advanced input to add a literal value into a field. You can also define a calculation to populate an output field, such as adding two input fields together.

  7. On the Advanced Outputs form, select one of these tabs, and add the appropriate input:

    • Available objects

      Select the appropriate object, and click Apply.

    • Literal

      Enter the appropriate value, and click Apply.

    • Defaults

      Select Use Dictionary Defaults, and click Apply.

      Select this option to use the default value in the data dictionary at runtime. If no default values exist in the data dictionary, the system displays a warning message.

    • Calculation

      Click Define Calculation, create a calculation in Expression Manager, and click Apply.

  8. Click Close on the Advanced Outputs form.

  9. On the Mapping form, select Issue a Write for this Event? to insert a row to the selected output after performing all column mappings for this event.

    When you select Issue a Write for this Event?, the system attaches the TC Insert Row event system function. This system function is automatically inserted at the end of the event rules. You cannot move it to another area. To specify when and where a row is inserted, attach the User Insert Row system function using Advanced ER, and move it to wherever it is needed.

  10. Click Next.

9.4.8 Specifying Table Conversion Logging Options

Click Next on the Mapping form to access the Logging Options form.

  1. Select from these options:

    • Log All Errors.

      Select this option to log every error, regardless of the logging option for type of operation that failed.

    • Log Every Input Record.

      Select this option to log every input record in the table conversion log file.

    • Log Outputs.

      Select this option to log every output record in the table conversion log file.

    • Log Deletes.

      Select this option to log every record that is deleted in the table conversion log file.

    • Log Updates.

      Select this option to log every record that is updated in the table conversion log file.

    • Log Copy Table Actions.

      Select this option to log the tables copied in the table conversion log file.

    • Log Details of Copy Table Actions.

      Select this option to log all of the details of each Copy Table action that is performed.

    • Run in Proof Mode.

      Select this option to run the conversion in proof mode. In proof mode, the conversion is simulated and all actions are logged, but no data is changed.

      Note:

      Proof mode is not an absolute proof mode. In some situations, the proof output might differ from the real output. If you insert the same record twice, for example, it may seem as though it worked in proof mode but, in reality, only one of the inserts works when you run the conversion in final mode.
  2. Click Next.

9.4.9 Reviewing Table Conversion Results

Click Next on the Logging Options form to access the Finish form.

  1. Select one of these options:

    • Yes, create a version of this table conversion.

      If you select yes, enter the version name.

    • No, I will create a version of this table conversion later.

  2. Click Finish to complete the process.

  3. On the Table Conversion form, select the Table Conversion Mappings form to review the mapping.

  4. Make changes, as necessary, by selecting the appropriate option from the View menu, and then click Save.

  5. To review the runtime conversion mode, select Check Conversion Mode from the Conversion menu.

    A dialog box provides you with details of the conversion mode that is defined.

  6. From the Conversion menu, select Exit.

9.5 Defining File Formats for Table Conversion Input or Output

This section provides an overview of user-defined formats, lists a prerequisite, and discusses how to:

9.5.1 Understanding User-Defined Formats

JD Edwards EnterpriseOne Table Conversion Design Aid uses user-defined formats to handle fixed-width or comma separated value (CSV) files in a table or text file. These files are collectively known as flat files because they do not have relationships defined like relational database tables. Usually, flat files are text files that are stored on a workstation or server. They are used to import or export data from applications that have no other means of interaction. For example, you might need to share information between JD Edwards EnterpriseOne and an external application. If the external application does not support one of the same databases that JD Edwards EnterpriseOne supports, flat files might be the only way to transfer data between the two applications.

In a flat file, records are stored as a continuous string of information. The user-defined format provides instructions on how data is presented.

This table illustrates a single database record with five columns of data: Last, First, Addr, City, and Phone:

Last First Addr City Phone
Doe John 123 Main Anytown 5551234

This record can be formatted as a comma-delimited string:

Doe, John, 123 Main, Anytown, 5551234

This record can also be formatted as a fixed-width column format, in which each column is eight characters long.

9.5.1.1 Importing and Exporting Text Files

When you select a text file for input or output in a table conversion and do not specify a path, a default path is used. Table conversions that use the default path run on any platform. If an explicit path or iSeries library name is indicated for the file, then the file is created exactly as specified. Table conversions that are stored in this way might not work on other platforms, depending on the nature of the file system on each platform.

The default paths on non-iSeries platforms are:

  • path code\Import\file name

  • path code\Export\file name

You cannot specify a default path for the iSeries. Rather, the default is always the Import or the Export directory under the path code of the input or output environment. For example, if you are running a table conversion against the DV812 environment, the path in the file system might be \E812\DV812\import\myfile.txt.

If the conversion specifies a file name that includes anything other than the file name and extension, such as \mytextfiles\myfile.txt, the conversion attempts to open the file as specified.

9.5.1.2 Using User-Defined Formats as Input

If you use user-defined input formats, add event rules on the Format Fetched event. Without these event rules, the system ignores the format, and the data from the input table is never made available to the table conversion. If the table conversion does not require event rule logic, add comments in the Format Fetched event in Event Rules Design.

User-defined formats work with text files and tables.

Because the procedure for importing and exporting data is database-specific, consult a database administrator for details.

9.5.1.3 Using User-Defined Formats as Output

If you use user-defined output formats, add event rules on the Format Fetched event. Without these event rules, the system ignores the format, and the data from the output table is never made available to the table conversion. If the table conversion does not require event rule logic, add comments in the Format Fetched event in Event Rules Design.

User-defined formats work with text files and tables.

Because the procedure for importing and exporting data is database-specific, you should consult a database administrator for details.

9.5.2 Prerequisite

Before you begin defining delimited file formats, ensure that you have selected the input and output files, as appropriate.

9.5.3 Defining Delimited File Formats with a Single Format

From the JD Edwards EnterpriseOne Table Conversion director, access the Select Input form or the Select Outputs form.

  1. Select User Defined Format, and then click Define Format(s).

  2. On the User Defined Format - Type form, select the Delimited - Characters such as commas or tabs separate each column option.

  3. Select the Single Format - All rows have the same format option, and click Next.

  4. On the User Defined Format - Column Delimiter form, select the delimiter that separates the columns in the file:

    • Tab

    • Comma

    • Semicolon

    • Space

    • Other

  5. Select the textual qualifier that is used to enclose a string of text:

    • None

      Select this option if no character is used to qualify text in this user-defined file.

    • Single Quotations

      Select this option if a single quotation mark is used to qualify text in this user-defined file.

    • Double Quotations

      Select this option if a double quotation mark is used to qualify text in this user-defined file.

  6. If the user-defined format incudes column headings in the first row, select The first row contains column headers, and click Next.

  7. On the User Defined Format - Column Layout form, select a format from the list of available formats.

    You define the columns for the format so that the system can parse the information from the file.

  8. For each column, click Add to define the column.

  9. On the New Column Properties form, modify these fields as required, and click OK.

    • Name

    • Length

    • Type

  10. To edit an existing column, select it, click Edit, and modify the properties on the Column Properties form.

  11. To model the columns after an existing table, business view, or foreign table, click Model, select the appropriate tab, and then select the table or business view that you want to use as a model for the user-defined format.

    Note:

    You cannot model the columns after an existing object unless the layout of the two objects match.
  12. Click OK, and then click Next.

    The system copies the format from the model that you selected and places it into the column layout grid.

  13. On the User Defined Format - Finish form, click Finish.

9.5.4 Defining Delimited File Formats with Multiple Formats

From the JD Edwards EnterpriseOne Table Conversion director, access the Select Input form or the Select Outputs form.

  1. Select User Defined Format, and then click Define Format(s).

  2. On the User Defined Format - Type form, select the Delimited - Characters such as commas or tabs separate each column option.

  3. Select the Multiple Formats - Rows are in two (2) or more formats option, and click Next.

  4. On the User Defined Format - Column Delimiter form, select the delimiter that separates the columns in the file:

    • Tab

    • Comma

    • Semicolon

    • Space

    • Other

  5. Select the textual qualifier that is used to enclose a string of text, and click Next:

    • None

      Select this option if no character is used to qualify text in this user-defined file.

    • Single Quotations

      Select this option if a single quotation mark is used to qualify text in this user-defined file.

    • Double Quotations

      Select this option if a double quotation mark is used to qualify text in this user-defined file.

  6. On the User Defined Format - Multiple Format Definition form, enter the number of formats that are included in the user-defined format.

  7. To define the character length of the Form Designator Column, enter a value in the Length field, and click Next.

  8. On the User Defined Format - Multiple Format Names form, name each format and define the values for each using the Name and Designator columns, and then click Next.

    The Designator name should describe the data in the user-defined formats.

    For example, suppose that a text file contains purchase order information. Lines in the table have information for a whole purchase order with the first field designated as POH; lines with a first field designated as POI contain information about individual items in the purchase order; and lines in the table with a first field designated as POT contain information about purchase order totals. In this scenario, you would enter POH as the designator of the first format, POI as the designator of the second format, and POT as the designator of the third format.

    Note:

    You can rename the columns for each format to help you remember the formats with which you are working. For example, you can rename the columns according to their function in the file, such as Header, Detail, and Total. These names appear in the Inputs list on the Mapping form of the table conversion.
  9. On the User Defined Format - Column Layout form, select a format from the list of available formats.

    You define the columns for the format so that the system can parse the information from the file.

    Note:

    To move rows, including the Format Designator, select the row and drag it to a new location.
  10. To add additional columns, click Add to define the column.

  11. On the New Column Properties form, modify these fields as required, and click OK:

    • Name

    • Length

    • Type

  12. To edit an existing column, select it, click Edit, and change the properties on the Column Properties form.

  13. To model the columns after an existing table, business view, or foreign table, click Model, select the appropriate tab, and then select the table or business view that you want to use as a model for the user-defined format.

    Note:

    You cannot model the columns after an existing object unless the layout of the two objects match.
  14. Click OK, and then click Next.

    The system copies the format from the model that you selected and places it into the column layout grid.

  15. On the User Defined Format - Finish form, click Finish.

9.5.5 Defining Fixed-Width File Formats with a Single Format

Access the Select Input form or the Select Outputs form.

  1. Select User Defined Format. and then click Define Format(s).

  2. On the User Defined Format - Type form, select the Fixed Width - Columns are aligned at specific positions with specific widths option.

  3. Select the Single Format - All rows have the same format option, and click Next.

  4. On the User Defined Format - Column Layout form, click Add to define the column layout.

  5. On the New Column Properties form, modify these fields as required, and click OK:

    • Name

    • Start

    • Length

    • Type

  6. To edit an existing column, select the column, click Edit, and change the properties on the Column Properties form.

  7. To model the columns after an existing table, business view, or foreign table, click Model, select the appropriate tab, and then select the table or business view that you want to use as a model for the user-defined format.

    Note:

    You cannot model the columns after an existing object unless the layout of the two objects match.
  8. Click OK, and then click Next.

    The system copies the format from the model that you selected, and places it into the column layout grid.

  9. On the User Defined Format - Finish form, click Finish.

9.5.6 Defining Fixed-Width File Formats with Multiple Formats

Access the Select Input form or the Select Outputs form.

  1. Select User Defined Format, and then click Define Format(s).

  2. On the User Defined Format - Type form, select the Fixed Width - Columns are aligned at specific positions with specific widths option.

  3. Select the Multiple Formats - Rows are in two (2) or more formats option, and click Next.

  4. On the User Defined Format - Multiple Format Definition form, enter the number of formats that are included in the user-defined format, and click Next.

    To define the character length of the Designator column, complete the Length field.

  5. On the User Defined Format - Multiple Format Names form, enter a name for each format in the Name column, and click Next.

    The Designator name should describe the data in the user-defined formats.

    For example, suppose that a text file contains purchase order information. Lines in the table have information for a whole purchase order with the first field designated as POH; lines with a first field designated as POI contain information about individual items in the purchase order; and lines in the table with a first field designated as POT contain information about purchase order totals. In this scenario, you would enter POH as the designator of the first format, POI as the designator of the second format, and POT as the designator of the third format.

    Note:

    You can rename the columns for each format to help you remember the formats with which you are working. For example, you can rename the columns according to their function in the file, such as Header, Detail, and Total. These names appear in the Inputs list on the Mapping form of the table conversion.
  6. On the User Defined Format - Column Layout form, select a format from the list of available formats.

    You define the columns for the format so that the system can parse the information from the file.

  7. To add additional columns, click Add to define the columns.

  8. On the New Column Properties form, modify these fields as required, and click OK:

    • Name

    • Start

    • Length

    • Type

  9. To edit an existing column, select the column, click Edit, and change the properties on the Column Properties form.

  10. To model the columns after an existing table, business view, or foreign table, click Model, select the appropriate tab, and then select the table or business view that you want to use as a model for the user-defined format.

    Note:

    You cannot model the columns after an existing object unless the layout of the two objects match.
  11. Click OK, and then click Next.

    The system copies the format from the model that you selected and places it into the column layout grid.

  12. On the User Defined Format - Finish form, click Finish.

9.6 Copying Data

This section provides an overview of copying data, lists prerequisites, and discusses how to define data copy actions.

9.6.1 Understanding Copying Data

You use the Data Copy feature on the JD Edwards EnterpriseOne Table Conversion director to copy tables from one environment or data source to another. You can also import a copy table script to use in the conversion.

9.6.2 Prerequisites

Before you begin defining the input and output environments for copying data, ensure that you review these tasks:

9.6.3 Defining Data Copy Actions

Access the JD Edwards EnterpriseOne Table Conversion Director.

  1. Select Data Copy, and click Next.

  2. On the External Data form, select a processing option template, define a data structure, as appropriate, and click Next.

  3. On the Select Environments form, select an input environment and an output environment, and click Next.

  4. On the Select Actions form, enter the name of a table in the Table field of the grid.

    If you want to copy a single table, select <Literal>, and enter the name of that table on the Single Value tab. If you do not know the name of the table that you want to copy, use the <Find a Table> option.

    When you enter the name of a table, the system automatically populates the remaining fields for you.

  5. On the Select Actions form, modify these fields as necessary:

    • To Table

      Enter either the last table in a range of tables to be copied, or leave the field blank if you are copying a single table.

    • Source Type

      Select Data Source if the input and output sources are data sources. Select Environment if the input and output sources are environments.

      When you select Data Source, the system retrieves table definitions from the specifications in the sign-in environment.

      When you select Environment, the system uses the input and output environments to locate data and specifications for the tables. This enables the specifications to be different in the input and output environment, but the data is copied.

    • Input Source

      Select the data source or environment from which the input is read.

    • Output Source

      Select the data source or environment to which the output is written.

    • Create

      When you select <If Table Exists>, the system creates the table in the output and runs the conversion only if both the table specifications and the actual table exist in the input.

      When you select <Yes>, the system creates the table in the output. If the table already exists in the output, the system deletes it and creates a new table.

      When you select <No>, the system assumes that the table already exists in the output and does not create it.

    • Clear

      When you select <If Table Exists>, the system clears the table in the output only if it exists in the input.

      When you select <Yes>, the system deletes all rows in the output table before copying the table.

      When you select <No>, the output table is not cleared.

      Note:

      If you select <No> to not clear the output table, the conversion could result in key conflicts.
    • Copy

      When you select <Yes>, the system copies the data from the input table to the output table using Map Same.

      When you select <No>, no data is copied.

    • Owner ID

    • Owner Pwd (owner password)

      If the data source requires an owner ID and password, enter them here. If you leave these fields blank, the system enters the ID and password of the sign-in user, or <None> if the data source does not have security.

  6. To import an existing copy table script from another location, click Import, locate the file that you want to import, and click Open.

    The system adds an action for each copy table item in the copy table script.

  7. Click Advanced ER to add event rule logic to the copy table process, and click Next.

    You can use event rules to write a custom copy table script.

  8. On the Logging Options form, select the appropriate logging options, and click Next.

  9. On the Finish form, select a version option, and click Finish.

9.7 Copying Data with Table Input

This section provides an overview of how to copy data with table input, lists prerequisites, and discusses how to define data copy with table input actions.

9.7.1 Understanding How to Copy Data with Table Input

The Data Copy with Table Input feature is similar to the Data Copy option, except that it allows information for the process to come from an input table. The input table might provide information about which tables are copied, where they are copied, and so on. The Data Copy with Table Input feature enables you to select data.

For example, suppose that you create a table that includes:

  • A table name

  • The next backup date

  • The backup frequency

You might populate this table with a list of tables to be archived and information specifying how often they are archived. You can then use the Data Copy with Table Input feature to select all rows in which the backup date is less than or equal to the current date, and calculate a new backup date.

9.7.3 Defining Data Copy with Table Input Actions

Access the JD Edwards EnterpriseOne Table Conversion Director.

  1. Select Data Copy with Table Input, and click Next.

  2. On the External Data form, select a processing option template, define a data structure, as appropriate, and click Next.

  3. On the Select Environments form, select an input environment and an output environment, and click Next.

  4. On the Select Input form, select a table, business view, or foreign table, and click Next.

  5. On the Sequencing form, define data sequencing as appropriate, and click Next.

  6. On the Data Selection form, define data selection as appropriate, and click Next.

  7. On the Table Options form, select Run Currency Triggers, if appropriate, and click Next.

    Select this option if a JD Edwards EnterpriseOne table included in the table conversion contains currency triggers. If a table contains currency fields and you do not select this option, the system cannot determine where the decimal should be placed within a field. If you do not select the currency trigger option and the source or destination fields are currency fields used in a calculation, you might receive unexpected results.

    Do not select Run Currency Triggers if the input and output data sources are the same type (for example, Oracle, iSeries, or SQL Server) and no calculations are being performed. Selecting this option results in slower processing.

    You should not use currency triggers for an environment that has a different path code than the login environment.

  8. On the Select Actions form, enter the name of a table in the Table field, modify the remaining fields as appropriate, and click Next.

  9. To import an existing copy table script from another location, click Import, locate the file that you want to import, and click Open.

  10. Click Advanced ER to add event rule logic to the copy table process, and click Next.

  11. On the Logging Options form, select logging options, and click Next.

  12. On the Finish form, select a version option, and click Finish.

9.8 Deleting Groups of Records

This section provides an overview of batch deletion, lists prerequisites, and discusses how to define batch deletion actions.

9.8.1 Understanding Batch Deletion

The Batch Delete feature enables you to delete a range of records from a JD Edwards EnterpriseOne 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 records in an input table that does not contain valid data. You can also set up table conversions that delete all records from a particular table.

9.8.1.1 Example: Creating Purge Programs as a Batch Delete

This example illustrates how you can create a table conversion to delete records from the input environment. Designing purge programs as batch delete programs enables you to purge records with control and accuracy. You can archive the purged data or remove it from the system permanently. The archiving process is explained in this example.

Before you start this example, create a handle for the table.

See Understanding Handles.

  1. On the introduction form of the JD Edwards EnterpriseOne Table Conversion director, select Batch Delete and click Next.

  2. On the External Data form, select the Purge Processing Option (T42000P) processing option template, and click Next.

  3. On the Select Environment form, select <LOGIN ENV> as the source environment, select Force Version to Override Input Environment, and click Next.

    The Force Version to Override Input Environment option prompts the person who runs the purge program to provide a valid source environment from which to run the batch-delete.

  4. On the Select Input form, drag the User Defined Code Types (F0004) table to the Description column, and click Next.

  5. On the Table Options form, select Run Currency Triggers, and click Next.

  6. On the Data Selection form, define data selection as Where IC UCD1 (User Defined Code - Class Code 1) is equal to Null, and click Next.

  7. On the Logging Options form, do not select any options, and click Next.

  8. On the Finish form, select Yes, create a version of this table conversion, and enter VER0001 as the version name.

  9. On the warning form that indicates that the table conversion needs to be saved, click OK.

  10. On the Selection for Batch Delete form, select the Process Begin event, and click Advanced ER.

  11. In Event Rules Design, add these event rule variables with a report scope:

    • FXXXXHandle_HFXXXX

    • szArchiveDataSource_DATS

    • szPurgeDataSource_DATS

    • szErrorCode_DTAI

    • cRenameFlag_EV01

    • mnErrorNumber_MATH01

  12. Enter these begin process event rules from R42119P, save the event rules, and quit Event Rules Design:

    0001 // Check to see if the purged data is being archived
    0002 If PO cArchive⇒
    Records is equal to "1"
    0003 // If the environment processing option is blank, stop⇒
     processing.
    0004 If PO szArchiveEnvironmentName is equal to <Blank>
       Or PO sz⇒
    ArchiveEnvironmentName is equal to <Null>
    0005  Stop Conversion Processing("The⇒
     archive environment is invalid.")
    0006 Else
    0007  // Check to make sure that the⇒
     archive environment and data source is not the
    0008  // same as the input⇒
     environment and data source
    0009  If PO szArchiveEnvironmentName is equal to SL⇒
     SourceEnvironment
    0010   Stop Conversion Processing("The source and archive⇒
     environments are the same")
    0011  Else
    0012   //Get the data source of the archive⇒
     environment
    0013            Get and validate the data source for an environment/table ⇒
    (B98700)
         PO szArchiveEnvironmentName -> szEnvironment
         "FXXXX" -> szTable⇒
    Name (Replace x's with the name of your table.)
         VA rpt_szArchiveDataSource_⇒
    DATS <- szDataSource
         VA rpt_szErrorCode_DTAI <- szErrorDataItem
         VA rpt_mn⇒
    ErrorNumber_MATH01 <- mnErrorNumber
    0014            //SAR #4337575 - B98700 has been modified⇒
     to return an error if the table is
    0015            //not found in the data source. Since we⇒
     do not want the table to exist in the
    0016            //archive environment, we need to⇒
     bypass that error condition.
    0017   If VA rpt_szErrorCode_DTAI is not equal to⇒
     <Blank>
            And VA rpt_szErrorCode_DTAI is not equal to <Null>
                                    And VA rpt_⇒
    szErrorCode_DTAI is not equal to "072W"
    0018    Stop Conversion Processing("No⇒
     data source was found for the 
    archive environment")
    0019   Else
    0020    //Get the⇒
     data source of the source environment
    0021                    Get and validate the data source for⇒
     an environment/table (B98700)
          SL SourceEnvironment -> szEnvironment
         ⇒
     "FXXXX" -> szTableName (Replace x's with the name of your table.)
          VA⇒
     rpt_szPurgeDataSource_DATS <- szDataSource
          VA rpt_szErrorCode_DTAI <- sz⇒
    ErrorDataItem
          VA rpt_mnErrorNumber_MATH01 <- mnErrorNumber
    0022                    If VA rpt_sz⇒
    ErrorCode_DTAI is equalto "072W"
    0023                            Stop Conversion Processing ("The table⇒
     could not be found
    in the source environment.")
    0024                    Else
    0025                            If VA rpt_sz⇒
    ErrorCode_DTAI is not equal to <Blank>
                                                    And VA rpt_szErrorCode_DTAI is not⇒
     equal to <Null>
    0026                                    Stop Conversion Processing ("No data source was found for
    ⇒
    the source environment.")
    0027                            End If
    0028                    End If
    0029    If VA rpt_szArchive⇒
    DataSource_DATS is equal to VA rpt_szPurgeDataSource_DATS
    0030    Stop Conversion⇒
     Processing ("The source and archive
    environments have the same data source")
    0031  ⇒
      Else
    0032    // Open a table with the same table name in the output
    environment.⇒
     The table
    0033    // will be renamed later if the table name processing option was⇒
     populated.
    0034    //Replace the X's in the following statement to reflect the
    name⇒
     of your table.
    0035    Copy Table Environment("FXXXX", <None>, SL Source⇒
    Environment,
    PO szArchiveEnvironmentName, <Yes>, <Yes>, <No>, <None>, <None>,⇒
     <Null>,
    <Null>)
    0036    // Open a handle to the archive table
    0037    //⇒
     Replace the X's in the following statements to reflect the
    name of your handle and⇒
     table.
    0038    VA rpt_FXXXXHandle_HFXXXX = FXXXX.Open Handle
    0039    If VA rpt_⇒
    FXXXXHandle_HFXXXX is equal to <Null>
    0040     Stop Conversion Processing ("Failed⇒
     to open FXXXX in the
    archive environment")
    0041    End If
    0042    End If
    0043   End If⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    0044  End If
    0045 End If
    0046 End If
    

    Note:

    Ensure that you map all event rule variables, even if you do not use every value.

    In this example, the system writes log messages to the JDE.log and JDEDEBUG.log files on the Stop Conversion Processing event.

  13. On the Selection for Batch Delete form, select the Row Fetched event, and click Advanced ER.

  14. On Event Rules Design, enter these row fetched event rules from R42119P, save the event rules, and quit Event Rules Design:

    001 // If we are archiving the purged records, write the record to the archive⇒
     table
    0002 //Replace the X's in the following statements to reflect the name of
    ⇒
    your handle and table. Map the fields included in your table.
    0003 If PO cArchive⇒
    Records is equal to "1"
    0004 FXXXX(VA rpt_FXXXXHandle_HFXXXX).Insert
       IC Order⇒
     Company(Order Number) -> TK Order Company(Order Number)
       IC Document(Order⇒
     No,Invoice,etc.) -> TK Document(Order No,Invoice,etc.)
       IC Order Type -> TK⇒
     Order Type
       IC Line Number -> TK Line Number
       IC Order Suffix -> TK Order Suffix
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
       IC Business Unit -> TK Business Unit
       IC Company -> TK Company
       IC Document⇒
     Company(Original Order) -> TK Document Company(Original Order)
       IC Original⇒
     Order Number -> TK Original Order Number
       IC Original Order Type -> TK Original⇒
     Order Type
       IC Original Line Number -> TK Original Line Number
       IC Company-Key ⇒
    (Related Order) -> TK Company-Key (Related Order)
       IC Related PO/SO/WO Number ->⇒
     TK Related PO/SO/WO Number
       IC Related PO/SO/WO Order Type -> TK Related PO/SO⇒
    /WO Order Type
       IC Related PO/SO Line Number -> TK Related PO/SO Line Number
       IC⇒
     Agreement Number-Distribution -> TK Agreement Number-Distribution
       IC Agreement⇒
     Supplement-Distribution -> TK Agreement Supplement-Distribution
       IC Address⇒
     Number -> TK Address Number
       IC Address Number-Ship To -> TK Address Number-Ship⇒
     To
       IC Address Number-Parent -> TK Address Number-Parent
       IC Date-Requested ->⇒
     TK Date-Requested
       IC Date-Order/Transaction -> TK Date-Order/Transaction
       IC⇒
     Date-Scheduled Pick -> TK Date-Scheduled Pick
       IC Date-Actual Ship Date -> TK⇒
     Date-Actual Ship Date
       IC Date-Invoice -> TK Date-Invoice
       IC Date-Cancel -> TK⇒
     Date-Cancel
       IC Date-For G/L(and Voucher) -> TK Date-For G/L(and Voucher)
       IC⇒
     Date-Promised Delivery -> TK Date-Promised Delivery
       IC Date-Price Effective⇒
     Date -> TK Date-Price Effective Date
       IC Date-Promised Shipment -> TK Date-⇒
    Promised Shipment
       IC Reference -> TK Reference
       IC Reference 2 -> TK Reference⇒
     2
       IC Item Number-Short -> TK Item Number-Short
       IC 2nd Item Number -> TK 2nd⇒
     Item Number
       IC 3rd Item Number -> TK 3rd Item Number
       IC Location -> TK⇒
     Location
       IC Lot/Serial Number -> TK Lot/Serial Number
       IC From Grade -> TK⇒
     From Grade
       IC Thru Grade -> TK Thru Grade
       IC From Potency -> TK From Potency
     ⇒
      IC Thru Potency -> TK Thru Potency
       IC Days Before Expiration -> TK Days Before⇒
     Expiration
       IC Description -> TK Description
       IC Description-Line 2 -> TK⇒
     Description-Line 2
       IC Line Type -> TK Line Type
       IC Status Code-Next -> TK⇒
     Status Code-Next
       IC Status Code-Last -> TK Status Code-Last
       IC Business Unit -⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
     Header -> TK Business Unit - Header
       IC Item Number - Related (Kit) -> TK Item⇒
     Number - Related (Kit)
       IC Kit Master Line Number -> TK Kit Master Line Number
      ⇒
     IC Component Line Number -> TK Component Line Number
       IC Related Kit Component -⇒
    > TK Related Kit Component
       IC Number of Component Per Parent -> TK Number of⇒
     Component Per Parent
       IC Sales Catalog Section -> TK Sales Catalog Section
       IC⇒
     Sub Section -> TK Sub Section
       IC Sales Category Code 3 -> TK Sales Category⇒
     Code 3
       IC Sales Category Code 4 -> TK Sales Category Code 4
       IC Sales Category⇒
     Code 5 -> TK Sales Category Code 5
       IC Commodity Class -> TK Commodity Class
      ⇒
     IC Commodity Sub Class -> TK Commodity Sub Class
       IC Supplier Rebate Code -> TK⇒
     Supplier Rebate Code
       IC Master Planning Family -> TK Master Planning Family
      ⇒
     IC Purchasing Category Code 5 -> TK Purchasing Category Code 5
       IC Unit of⇒
     Measure as Input -> TK Unit of Measure as Input
       IC Units-Order/Transaction⇒
     Quantity -> TK Units-Order/Transaction Quantity
       IC Quantity Shipped -> TK⇒
     Quantity Shipped
       IC Units-Qty Backordered/Held -> TK Units-Qty Backordered/Held
    ⇒
       IC Units-Quantity Canceled/Scrapped -> TK Units-Quantity Canceled/Scrapped
       IC⇒
     Units-Future Quantity Committed -> TK Units-Future Quantity Committed
       IC Units-⇒
    Open -> TK Units-Open
       IC Units-Shipped to Date -> TK Units-Shipped to Date
       IC⇒
     Units-Relieved -> TK Units-Relieved
       IC Committed (H/S) -> TK Committed (H/S)
      ⇒
     IC Other Quantity (1/2) -> TK Other Quantity (1/2)
       IC Amount-Price per Unit ->⇒
     TK Amount-Price per Unit
       IC Amount-Extended Price -> TK Amount-Extended Price
      ⇒
     IC Amount-Open -> TK Amount-Open
       IC Price Override Code -> TK Price Override⇒
     Code
       IC Temporary Price (Y/N) -> TK Temporary Price (Y/N)
       IC Unit of Measure-⇒
    Entered for Unit Price -> TK Unit of Measure-
    Entered for Unit Price
       IC Amount-⇒
    List Price -> TK Amount-List Price
       IC Amount-Unit Cost -> TK Amount-Unit Cost
      ⇒
     IC Amount-Extended Cost -> TK Amount-Extended Cost
       IC Cost Override Code -> TK⇒
     Cost Override Code
       IC Extended Cost-Transfer -> TK Extended Cost-Transfer
       IC⇒
     Print Message -> TK Print Message
       IC Payment Terms Code -> TK Payment Terms Code
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
       IC Payment Instrument -> TK Payment Instrument
       IC Based on Date -> TK Based⇒
     on Date
       IC Discount-Trade -> TK Discount-Trade
       IC Trade Discount (Old) -> TK⇒
     Trade Discount (Old)
       IC Price and Adjustment Schedule -> TK Price and⇒
     Adjustment Schedule
       IC Item Price Group -> TK Item Price Group
       IC Pricing⇒
     Category Level -> TK Pricing Category Level
       IC Discount %-Cash -> TK Discount %-⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    Cash
       IC Document Company -> TK Document Company
       IC Document⇒
    (Voucher,Invoice,etc.) -> TK Document(Voucher,Invoice,etc.)
       IC Document Type ->⇒
     TK Document Type
       IC Document-Original -> TK Document-Original
       IC Document⇒
     Type-Original -> TK Document Type-Original
       IC Document Company-Original -> TK⇒
     Document Company-Original
       IC Pick Slip Number -> TK Pick Slip Number
       IC⇒
     Delivery Number -> TK Delivery Number
       IC Sales Taxable(Y/N) -> TK Sales Taxable⇒
    (Y/N)
       IC Tax Rate/Area -> TK Tax Rate/Area
       IC Tax Expl Code 1 -> TK Tax Expl⇒
     Code 1
       IC Associated Text -> TK Associated Text
       IC Priority-Processing -> TK⇒
     Priority-Processing
       IC Printed Code -> TK Printed Code
       IC Backorders Allowed ⇒
    (Y/N) -> TK Backorders Allowed (Y/N)
       IC Substitutes Allowed (Y/N) -> TK⇒
     Substitutes Allowed (Y/N)
       IC Partial Line Shipments Allowed (Y/N) -> TK Partial⇒
     Line Shipments Allowed (Y/N)
       IC Line of Business -> TK Line of Business
       IC⇒
     End Use -> TK End Use
       IC Duty Status -> TK Duty Status
       IC Nature of⇒
     Transaction -> TK Nature of Transaction
       IC Primary/Last Supplier Number -> TK⇒
     Primary/Last Supplier Number
       IC Carrier Number -> TK Carrier Number
       IC Mode⇒
     of Transport -> TK Mode of Transport
       IC Route Code -> TK Route Code
       IC Stop⇒
     Code -> TK Stop Code
       IC Zone Number -> TK Zone Number
       IC Container I.D. -> TK⇒
     Container I.D.
       IC Freight Handling Code -> TK Freight Handling Code
       IC⇒
     Shipping Commodity Class -> TK Shipping Commodity Class
       IC Shipping Conditions⇒
     Code -> TK Shipping Conditions Code
       IC Serial Number-Lot -> TK Serial Number-Lot
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
       IC Unit of Measure-Primary -> TK Unit of Measure-Primary
       IC Units-Primary⇒
     Quantity Ordered -> TK Units-Primary Quantity Ordered
       IC Unit of Measure-⇒
    Secondary -> TK Unit of Measure-Secondary
       IC Units-Secondary Quantity Ordered -⇒
    > TK Units-Secondary Quantity Ordered
       IC Unit of Measure-Pricing -> TK Unit⇒
     of Measure-Pricing
       IC Unit Weight -> TK Unit Weight
       IC Weight Unit of Measure⇒
     -> TK Weight Unit of Measure
       IC Unit Volume -> TK Unit Volume
       IC Volume Unit⇒
     of Measure -> TK Volume Unit of Measure
       IC Reprice (Basket Price) Category ->⇒
     TK Reprice (Basket Price) Category
       IC Order Reprice Category -> TK Order⇒
     Reprice Category
       IC Order Repriced Indicator -> TK Order Repriced Indicator
      ⇒
     IC Costing Method-Inventory -> TK Costing Method-Inventory
       IC G/L Offset -> TK⇒
     G/L Offset
       IC Century -> TK Century
       IC Fiscal Year -> TK Fiscal Year
       IC⇒
     Inter Branch Sales -> TK Inter Branch Sales
       IC On Hand Updated -> TK On Hand⇒
     Updated
       IC Configurator Print Flag -> TK Configurator Print Flag
       IC Sales⇒
     Order Status 04 -> TK Sales Order Status 04
       IC Substitute Item Indicator -> TK⇒
     Substitute Item Indicator
       IC Preference Commitment Indicator -> TK Preference⇒
     Commitment Indicator
       IC Ship date (PDDJ) overridden -> TK Ship date (PDDJ)⇒
     overridden
       IC Price Adjustment Line Indicator -> TK Price Adjustment Line⇒
     Indicator
       IC Price Adj. History Indicator -> TK Price Adj. History Indicator
      ⇒
     IC Preference Production Allocation -> TK Preference Production Allocation
       IC⇒
     Transfer/Direct Ship/Intercompany Flag -> TK Transfer/Direct Ship/
    Intercompany⇒
     Flag
       IC Deferred entries flag -> TK Deferred entries flag
       IC Euro Conversion⇒
     Status Flag -> TK Euro Conversion Status Flag
       IC Sales Order Status 14 -> TK⇒
     Sales Order Status 14
       IC Sales Order Status 15 -> TK Sales Order Status 15
       IC⇒
     Apply Commission(Y/N) -> TK Apply Commission(Y/N)
       IC Commission Category -> TK⇒
     Commission Category
       IC Reason Code -> TK Reason Code
       IC Gross Weight -> TK⇒
     Gross Weight
       IC Gross Weight Unit of Measure -> TK Gross Weight Unit of Measure
    ⇒
       IC Subledger-G/L -> TK Subledger-G/L
       IC Subledger Type -> TK Subledger Type
      ⇒
     IC Code-Location Tax Status -> TK Code-Location Tax Status
       IC Price Code 1 ->⇒
     TK Price Code 1
       IC Price Code 2 -> TK Price Code 2
       IC Price Code 3 -> TK⇒
     Price Code 3
       IC Status-In Warehouse -> TK Status-In Warehouse
       IC Work Order⇒
     Freeze Code -> TK Work Order Freeze Code
       IC Send Method -> TK Send Method
       IC⇒
     Currency Code-From -> TK Currency Code-From
       IC Currency Conversion Rate-Spot⇒
     Rate -> TK Currency Conversion Rate-Spot Rate
       IC Amount-List Price per Unit ->⇒
     TK Amount-List Price per Unit
       IC Amount-Foreign Price per Unit -> TK Amount-⇒
    Foreign Price per Unit
       IC Amount-Foreign Extended Price -> TK Amount-Foreign⇒
     Extended Price
       IC Amount-Foreign Unit Cost -> TK Amount-Foreign Unit Cost
       IC⇒
     Amount-Foreign Extended Cost -> TK Amount-Foreign Extended Cost
       IC User⇒
     Reserved Code -> TK User Reserved Code
       IC User Reserved Date -> TK User⇒
     Reserved Date
       IC User Reserved Amount -> TK User Reserved Amount
       IC User⇒
     Reserved Number -> TK User Reserved Number
       IC User Reserved Reference -> TK⇒
     User Reserved Reference
       IC Transaction Originator -> TK Transaction Originator
     ⇒
      IC User ID -> TK User ID
       IC Program ID -> TK Program ID
       IC Work Station ID -⇒
    > TK Work Station ID
       IC Date-Updated -> TK Date-Updated
       IC Time of Day ->⇒
     TK Time of Day
       IC Manufacturing Variance Accounting Flag -> TK Manufacturing
    ⇒
    Variance Accounting Flag
       IC Sales Order Status 17 -> TK Sales Order Status 17
      ⇒
     IC Sales Order Status 18 -> TK Sales Order Status 18
       IC Sales Order Status 19 -⇒
    > TK Sales Order Status 19
       IC Sales Order Status 20 -> TK Sales Order Status⇒
     20
       IC Integration Reference 01 -> TK Integration Reference 01
       IC Integration⇒
     Reference 02 -> TK Integration Reference 02
       IC Integration Reference 03 -> TK⇒
     Integration Reference 03
       IC Integration Reference 04 -> TK Integration⇒
     Reference 04
       IC Integration Reference 05 -> TK Integration Reference 05
       IC⇒
     Source of Order -> TK Source of Order
       IC Reference -> TK Reference
       IC Demand⇒
     Unique Key ID -> TK Demand Unique Key ID
       IC Pull Signal -> TK Pull Signal
       IC⇒
     Release Number -> TK Release Number
       IC Scheduled Shipment Time -> TK Scheduled⇒
     Shipment Time
       IC Time-Rlease -> TK Time-Rlease
       IC Date-Rlease -> TK Date-⇒
    Rlease
       IC Requested Delivery Time -> TK Requested Delivery Time
       IC Actual⇒
     Shipment Time -> TK Actual Shipment Time
       IC Time-Original Promised Delivery ->⇒
     TK Time-Original Promised Delivery
       IC Time-Scheduled Pick -> TK Time-Scheduled⇒
     Pick
       IC Time-Future Time 2 -> TK Time-Future Time 2
       IC Cross-Docking Flag ->⇒
     TK Cross-Docking Flag
       IC Cross-Docking Priority for Sales Orders -> TK Cross-⇒
    Docking
    Priority for Sales Orders
       IC Dual Unit of Measure Item -> TK Dual Unit⇒
     of Measure Item
       IC Buying Segment Code -> TK Buying Segment Code
       IC Current⇒
     Buying Segment Code -> TK Current Buying Segment Code
       IC Change Order Number ->⇒
     TK Change Order Number
       IC Address Number-Deliver To -> TK Address Number-⇒
    Deliver To
       IC Pending Approval Flag -> TK Pending Approval Flag
       IC Revision⇒
     Reason  -> TK Revision Reason
       IC Matrix Control Line Number -> TK Matrix⇒
     Control Line Number
       IC Shipment Number -> TK Shipment Number
       IC Promised⇒
     Delivery Time -> TK Promised Delivery Time
       IC Project Number -> TK Project⇒
     Number
       IC Sequence Number -> TK Sequence Number
       IC Item Revision Level -> TK⇒
     Item Revision Level
       IC Hold Orders Code -> TK Hold Orders Code
       IC Business⇒
     Unit-Header -> TK Business Unit-Header
       IC Business Unit-Demand -> TK Business⇒
     Unit-Demand
       IC Currency Code-Base -> TK Currency Code-Base
       IC Document Line⇒
     Number-Original -> TK Document Line Number-Original
       IC Date-Original Promised⇒
     Delivery -> TK Date-Original Promised Delivery
       IC Cross Dock Order Company⇒
    (OrderNumber) -> TK Cross Dock Order
    Company(OrderNumber)
       IC Cross Dock Order No -⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    ⇒
    > TK Cross Dock Order No
       IC Cross Dock Order Type -> TK Cross Dock Order Type
      ⇒
     IC Cross Dock Line Number -> TK Cross Dock Line Number
       IC Cross Dock Order⇒
     Suffix -> TK Cross Dock Order Suffix
       IC Port of Entry or Exit -> TK Port of⇒
     Entry or Exit
       IC Payment Terms Override Code -> TK Payment Terms Override Code
     ⇒
      IC Buyer Number -> TK Buyer Number
       IC Promotion ID -> TK Promotion ID
       IC⇒
     Asset Item Number -> TK Asset Item Number
       IC Parent Number -> TK Parent Number
    ⇒
    0005 // Do not delete the record if the insert to the archive table failed.
    0006 If⇒
     SV Error_Status is not equal to CO ERROR
    0007            Delete Current Input Row
    0008    End⇒
     If
    0009 Else
    0010    Delete Current Input Row
    0011 End If
    

    Note:

    Ensure that you map all parameters to a field, even if you do not use every value.
  15. On the Selection for Batch Delete form, select the Process End event, and click Advanced ER.

  16. On Event Rules Design, enter these process end event rules from R42119P, save the event rules, and quit Event Rules Design:

    0001 If PO cArchiveRecords is equal to "1"
    0002 // Close the table
    0003 //Replace⇒
     the X's in the following statement to reflect the
    name of your handle and table.
    ⇒
    0004 FXXXX(VA rpt_FXXXXHandle_HFXXXX).Close
    0005 // If the data was archived and⇒
     the table name processing option was
    populated,
    0006 // rename the table.
    0007 If PO⇒
     szArchiveTableName is not equal to <Blank>
      And PO szArchiveTableName is not⇒
     equal to <Null>
    0008  Rename Table (B0000202)
       "FXXXX" -> szOldTableName
       PO sz⇒
    ArchiveTableName -> szNewTableName
       "<Blank>" -> szTableOwnerID
       "<Blank>" -> sz⇒
    Password
       VA rpt_szArchiveDataSource_DATS -> szDataSource
       VA rpt_cRenameFlag_⇒
    EV01 <- cRenameTableSuccessful
    0009 End If
    0010 End If
    

    Note:

    Ensure that you map all parameters to a field, even if you do not use every value.

9.8.3 Defining Batch Deletion Actions

Access the JD Edwards EnterpriseOne Table Conversion Director.

  1. Select Batch Delete, and click Next.

  2. On the External Data form, select a processing option template, define a data structure, as appropriate, and click Next.

  3. On the Select Environments form, select a source environment, and click Next.

  4. On the Select Input form, drag the appropriate table to the Description column, and click Next.

    To delete an input table, select it and press Delete.

    Note:

    If you change the table, the system warns you that deleting tables removes all mappings from the table conversion.
  5. On the Table Options form, select Run Currency Triggers, if appropriate, and click Next.

    Select this option if a JD Edwards EnterpriseOne table included in the table conversion contains currency triggers. If a table contains currency fields and you do not select this option, the system cannot determine where the decimal should be placed within a field. If you do not select the currency trigger option and the source or destination fields are currency fields used in a calculation, you might receive unexpected results.

    Do not select Run Currency Triggers if the input and output data sources are the same type (for example, Oracle, iSeries, or SQL Server) and no calculations are being performed. Selecting this option results in slower processing.

    You should not use currency triggers for an environment that has a path code that is different from the sign-in environment.

  6. On the Data Selection form, define data selection as appropriate.

  7. Select the Row Fetched event when deleting rows; otherwise, no records are deleted.

    When you run the table conversion, the system fetches the rows one at a time, runs the conversion for each row, and deletes the record from the input.

  8. Click Advanced ER to insert additional event rule logic.

  9. Verify that the Delete All Selected Records option is selected, and click Next.

    This option inserts the Delete Current Input Row system function into event rules.

  10. On the Logging Options form, select logging options, as appropriate, and click Next.

  11. On the Finish form, select a version option, and click Finish.

9.9 Using Event Rules in Table Conversions

This section provides an overview of event rules in table conversions and discusses how to create event rules in table conversions.

9.9.1 Understanding Event Rules in Table Conversions

You can use event rules to build complex functional logic into table conversions. For example, you can use event rules to insert information into a table or delete rows in a table based on specific conditions.

You attach event rules to an event, such as:

  • Process Begin

  • Row Fetched

  • Format Fetched

  • Process End

Event rules in table conversions include system functions that are specific to JD Edwards EnterpriseOne Table Conversion Design Aid.

9.9.1.1 Table Conversion System Functions

This table describes each system function that you can use in table conversion event rules:

System Function Description
CopyTableDataSource Enables you to copy a table or range of tables from one data source to another. The system copies tables based on specifications in the sign-in environment.
CopyTableEnvironment Enables you to copy a table or range of tables from one environment to another. The system copies tables based on specifications in the input and output environments. If the specifications differ, the system performs a map-and-drop. This means that it creates a mapping between like fields in the source and destination tables and ignores all other fields.
TCInsertRow The table conversion system inserts this system function when you select the Issue a write for this event? option and it cannot be moved. This function instructs the system that data should be written to the output table.
UserInsertRow Enables you to specify when and where a row is inserted into the specified output table.
DeleteCurrentInputRow Enables you to delete the current record from the input table.
UpdateCurrentInputRow Enables you to update the current record in the input table after it has been modified.
SetSelectionAppendFlag Enables you to determine whether selection criteria that is added by the system function SetUserSelection on the input table should be appended to the existing selection criteria or if it should replace the existing selection criteria.
SetUserSelection Enables you to conditionally modify data selection on the input table. Call SetSelectionAppendFlag before calling SetUserSelection to indicate whether to replace or append to the existing data selection.

9.9.2 Creating Event Rules in Table Conversions

Access a table conversion form that includes an Advanced ER button.

  1. From the Events drop-down list, select the event to which you want to attach event rules, and click Advanced ER.

  2. On the Event Rules Design form, select any of these options to define specific business logic:

    • Assignment

      Select this option to assign a fixed value, a field, or a mathematical expression to a field or variable.

    • If\While

      Select this option to create If and While logic statements, which are conditional instructions for event rule logic.

    • Business Function

      Select this option to attach an existing JD Edwards EnterpriseOne business function to an event. Business functions can retrieve a next number value for a new customer or convert Julian calendar dates to month, day, and year.

    • System Function

      Select this option to attach an existing JD Edwards EnterpriseOne system function, such as Copy Table Environment or User Insert Row.

    • Variables

      Select this option to create event rule variables, using existing data items, to store values for additional processing. Event rule variables can be used to accumulate totals, record the number of records that are read, and so on.

    • Else

      Select this option to create Else logic statements. An Else statement is automatically inserted after an If statement.

    • Table I/O

      Select this option to open tables in the input, output, or sign-in environment. Table I/O enables you to read data from tables other than the input table and use the data to create an output record. For example, suppose that you create a table conversion that loops through records in the F0101 table. The table conversion copies the records to another table, loops back through the records to find each customer that has a specific employee as a contact, and copies the information to the output table.

    • Report Interconnect

      Select this option to connect a batch process or report to the table conversion.

  3. Define the logic, save the event rules, and quit Event Rules Design.