12 Using Flat Files

This chapter contains the following topics:

Section 12.1, "Understanding Flat Files"
Section 12.2, "Formatting Flat Files"
Section 12.3, "Setting Up Flat Files"
Section 12.4, "Converting Flat Files Using the Flat File Conversion Program"
Section 12.5, "Importing Flat Files Using a Business Function"
Section 12.6, "Converting Flat Files Using APIs"

12.1 Understanding Flat Files

Flat files (also known as user-defined formats) are usually text files that are stored on your workstation or server and typically use the ASCII character set. Because data in a flat file is stored as one continuous string of information, flat files do not have relationships defined for them as relational database tables do. Flat files can be used to import or export data from applications that have no other means of interaction. For example, you might want to share information between JD Edwards EnterpriseOne and another system. If the non-JD Edwards EnterpriseOne system does not support the same databases that JD Edwards EnterpriseOne supports, then flat files might be the only way to transfer data between the two systems.

When you use flat files to transfer data to JD Edwards EnterpriseOne, the data must be converted to JD Edwards EnterpriseOne format before it can be updated to the live database. You can use JD Edwards EnterpriseOne interface tables along with a conversion program, electronic data interface (EDI), or table conversion to format the flat file data. You can use EDI or table conversion to retrieve JD Edwards EnterpriseOne data for input to a flat file.

Some JD Edwards EnterpriseOne batch interfaces, such as the batch extraction programs, can accept flat files and parse the information to data format.

Note:

JD Edwards EnterpriseOne supports flat file conversion on the Windows platform only.

12.2 Formatting Flat Files

When you import data using JD Edwards EnterpriseOne interface tables, the format for flat files can be user-defined or character-delimited. This example illustrates a single database character record that has a user-defined format with five columns (Last, First, Addr (address), City, and Phone):

Last	First	Addr	City	Phone	Table Column Heading
Doe	John	123 Main	Any town	5551234	← database record

The user-defined format example is a fixed-width column format in which all of the data for each column starts in the same relative position in each row of data.

This is an example of the same data in a character-delimited format:

"Doe", "John", "123 Main", "Anytown", "5551234"

12.3 Setting Up Flat Files

The format of the record in the flat file must follow the format of the interface table. This means that every column in the table must be in the flat file record and the columns must appear in the same order as the interface table. Every field in the interface table must be written to, even if the field is blank. Each field must be enclosed by a symbol that marks the start and end of the field. Typically, this symbol is a double quotation mark (" "). In addition, each field must be separated from the next field with a field delimiter. Typically, this separator value is a comma (,). However, any field delimiter and text qualifier may be used as long as they do not interfere with the interpretation of the fields. You set the processing options on the conversion program to define the text qualifiers and field delimiters. If you are receiving documents with decimal numbers, you must use a placeholder (such as a period) to indicate the position of the decimal. You define the placeholder in the User Preference table.

The first field value in a flat file record indicates the record type. In other words, the first field value indicates into which interface table the conversion program should insert the record. Record type values are defined and stored by the record type user defined code table (00/RD). The hard-coded values are:

1: Header
2: Detail
3: Additional Header
4: Additional Detail
5: SDQ
6: Address
7: Header Text
8: Detail Text

For example, suppose a record in the header table has this information (this example ignores table layout standards):

Record Type	Name	Address	City	Zip Code
1	Joe	<Blank>	Denver	80237

This is how the record in the flat file appears:

1, Joe,,Denver,80237

Note that "1" corresponds to a header record type, and the blank space corresponds to the <Blank> in the Address column.

Dates must be in the format MM/DD/YY. Numeric fields must have a decimal as the place keeper. A comma cannot be used.

12.4 Converting Flat Files Using the Flat File Conversion Program

If you have a Windows platform, you can use the Inbound Flat File Conversion program (R74002C) or the Import Flat File To JDE File (B4700240) business function.

If you are on a Windows platform, you can use the Inbound Flat File Conversion program (R47002C) to import flat files into JD Edwards EnterpriseOne interface tables. You create a separate version of the Inbound Flat File Conversion program for each interface table.

Note:

To use the Inbound Flat File Conversion program, you must map a drive on your PC to the location of the flat file.

This diagram shows the process for updating JD Edwards EnterpriseOne interface tables using flat files:

Figure 12-1 Flat file conversion program process flow

Description of "Figure 12-1 Flat file conversion program process flow"

You use the Flat File Cross-Reference program (P47002) to update the F47002 table. The conversion program uses the F47002 table to determine which flat file to read based on the transaction type that is being received. This list identifies some of the information that resides in the F47002 table:

Transaction Type

The specific transaction type. The transaction type must be defined in UDC 00/TT.
Direction Indicator

A code that indicates the direction of the transaction. The direction indicator code must be defined in UDC 00/DN.
Flat File Name

The path to the flat file on your Windows PC.
Record Type

An identifier that marks transaction records as header, detail, and so on. The record type indicator must be defined in UDC 00/RD.
File Name

A valid JD Edwards EnterpriseOne interface table.

The conversion program uses the Flat File Cross-Reference table to convert the flat file to the JD Edwards EnterpriseOne interface tables. The conversion program recognizes both the flat file it is reading from and the record type within that flat file. Each flat file contains records of differing lengths based on the corresponding interface table record.

The conversion program reads each record in the flat file and maps the record data into each field of the interface table based on the text qualifiers and field delimiters specified in the flat file. All fields must be correctly formatted for the conversion program to correctly interpret each field and move it to the corresponding field in the appropriate inbound interface table.

The conversion program inserts the field data as one complete record in the interface table. If the conversion program encounters an error while converting data, the interface table is not updated. Because the flat file is an external object that is created by third-party software, the conversion program is not able to determine which flat file data field is formatted incorrectly. You must determine what is wrong with the flat file. When the conversion program successfully converts all data from the flat file to the interface tables, the conversion program automatically deletes the flat file after the conversion. After the data is successfully converted and if you set the processing option to start the next process in the conversion program, the conversion program automatically runs the inbound processor batch process for that interface table. If you did not set up the processing option to start the inbound processor batch program, you must manually run the Flat File Conversion (R47002C) batch process.

If the flat file was not successfully processed, you can review the errors in the Employee Work Center, which you can access from the Workflow Management menu (G02). After you correct the error condition, run R47002C again.

12.4.1 Forms Used to Convert Flat File Information

Form Name	FormID	Navigation	Usage
Work With Flat File Cross-Reference	W47002A	From an application that supports flat file conversion, open the Flat File Cross-Reference Program.	Identify the transaction type.
Flat File Cross-Reference	W47002B	On Work With Flat File Cross Reference, select the appropriate transaction in the detail area and then select Define from the Row menu.	Enter the name of the flat file, define the record types, and indicate the JD Edwards EnterpriseOne destination file.

12.4.2 Defining the Flat File Cross Reference Table

Access the Flat File Cross Reference form.

Figure 12-2 Flat File Cross Reference form

Description of "Figure 12-2 Flat File Cross Reference form"

12.4.2.1 Flat File Cross Reference

Flat File Name: The name of the flat file. This includes the directory path where the flat file exists.
Record Type: The identifier that marks EDI transaction records as header and detail information. This is an EDI function only.
Record Type Description: A user-defined name or remark.
File Name: The number of a specific table. For example, the Account Master table name is F0901.

12.5 Importing Flat Files Using a Business Function

If you are on a Windows platform, you can use the business function named Import Flat File To JDE File (B4700240). Because of changes to server operating systems and the various ways that operating systems store files, JD Edwards EnterpriseOne supports the business function only when run from a Windows platform. If you use the Import Flat File To JDE File (B4700240) business function, note these constraints:

Transaction Type and Flat File Name fields must contain data.
Only one character is allowed in the Record Type field.
The maximum length per line is 4095 characters.
The maximum record types are 40.
Every line must have a record.
The text qualifier cannot be the same as the column delimiter.

To ensure that flat file data is properly formatted before it is inserted into interface tables, the business function uses the F98713 table to obtain primary index key information. Normally, the F98713 table is located under the Default Business Data table mapping in the Object Configuration Manager. So that the business function can find the F98713 table, you must take one of these actions:

Map the F98713 table in the system data source.
Ensure the F98713 table exists in the business data source.

12.5.1 Map the F98713 table in the System Data Source

To map the table in the system data source, add an OCM mapping that points the F98713 table to the central objects data source.

12.5.2 Ensure the F98713 table Exists in the Business Data Source

If you generate the F98713 table in the business data source, you must ensure that file extensions on your PC are hidden. To hide file extensions, complete these steps:

From Start/Settings/Control Panel/Folder Options, click the View tab.
Select the Hide file extension for known file types option, and then click OK.

You must also ensure that the Flat File Name field in the F47002 table has a file extension. For example: C:\flatfiles\850.txt.

12.5.3 Flat File Conversion Error Messages

These two errors might occur when you use the business function to convert flat files:

4363 Null Pointer
4377 Invalid Input Parameter

Both of the errors are internal problems within the business function.

These errors might occur as a result of problems with user setup or with the configurable network computing (CNC) implementation:

0073 Invalid File Name
128J (filename) Insert Failed
3003 Open of File Unsuccessful
4569 Invalid Format

12.6 Converting Flat Files Using APIs

In addition to the existing flat file APIs, JD Edwards EnterpriseOne provides APIs for non-Unicode flat files. The Unicode APIs are required when flat file data is written to or read by a process outside of JD Edwards EnterpriseOne. The JD Edwards EnterpriseOne APIs, such as jdeFWrite() and jdeFRead(), do not convert flat file data, which means that the default flat file I/O for character data is in Unicode. If you use JD Edwards EnterpriseOne-generated flat files and the recipient system is not expecting Unicode data, you will not be able to read the flat file correctly. For example, if the recipient system is not Unicode enabled and the system is expecting data in the Japanese Shift_JIS code page (or encoding), you will not be able to read the flat file correctly. To enable the creation of the flat file in the Japanese Shift_JIS page, the application that creates the flat file must be configured using the Unicode Flat File Encoding Configuration program (P93081). If the flat file is a work file or debugging file and will be written and read by JD Edwards EnterpriseOne only, the existing flat file APIs should be used. For example, if the business function is doing some sort of caching in a flat file, that flat file data does not need to be converted.

The JD Edwards EnterpriseOne conversion to Unicode uses UCS-2 encoding in memory, or two bytes per character (JCHAR), for representation of all character data. The character data that is passed to the output flat file APIs needs to be in JCHAR (UCS-2). The input flat file APIs converts the character data from a configured code page to UCS-2 and returns the character in JCHAR (or JCHAR string). The flat file conversion APIs enable you to configure a code page for the flat file at runtime. You use P93081 to set up the flat file code page. Flat file encoding is based on attributes such as application name, application version name, user name, and environment name.

If no code page is specified in the configuration application, the APIs perform flat file I/O passing through the data as it was input to the specific function. For example, jdeFWriteConvert() writes Unicode data and no conversion is performed.

12.6.1 Forms Used to Convert Flat File Information

Form Name	FormID	Navigation	Usage
Work With Flat File Encoding	W93081A	From the Windows client, select System Administration Tools (GH9011), System Administration Tools, User Management, User Management Advanced and Technical Operations, Unicode Flat File Encoding Configuration	Locate and review existing Unicode flat file encoding configurations.
Flat File Encoding Revision	W93081B	On Work With Flat File Encoding, click Add	Add or change Unicode flat file encoding configuration information.
Work With Flat File Encoding	W93081A	On Work With Flat File Encoding, click Find, select your newly added Unicode configuration record in the detail area, and then select Change Status from the Row menu.	Activate or deactivate a Unicode configuration record.

12.6.2 Setting Up Flat File Encoding

Access the Unicode Flat File Encoding Configuration form from the Windows client.

Figure 12-3 Unicode Flat File Encoding Configuration form

Description of "Figure 12-3 Unicode Flat File Encoding Configuration form"

12.6.2.1 Flat File Encoding Configuration

User / Role

A profile that classifies users into groups for system security purposes. You use group profiles to give the members of a group access to specific programs.

Some rules for creating a profile for a user class or group include:

The name of the user class or group must begin with an asterisk (*) so that it does not conflict with any system profiles.
The User Class/Group field must be blank when you enter a new group profile.

Environment

For install applications, the environment name is also called the Plan Name and uniquely identifies an upgrade environment for install/reinstall.

For environment or version applications, this is the path code that identifies the location of the application or version specification data.

Program ID

The number that identifies the batch or interactive program (batch or interactive object). For example, the number of the Sales Order Entry interactive program is P4210, and the number of the Print Invoices batch process report is R42565.

The program ID is a variable length value. It is assigned according to a structured syntax in the form TSSXXX, where:

T is an alphabetic character and identifies the type, such as P for Program, R for Report, and so on.

For example, the value P in the number P4210 indicates that the object is a program.
SS are numeric characters and identify the system code.

For example, the value 42 in the number P4210 indicates that this program belongs to system 42, which is the Sales Order Processing system.
XXX (the remaining characters) are numeric and identify a unique program or report.

For example, the value 10 in the number P4210 indicates that this is the Sales Order Entry program.

Version

A user-defined set of specifications that control how applications and reports run. You use versions to group and save a set of user-defined processing option values and data selection and sequencing options. Interactive versions are associated with applications (usually as a menu selection). Batch versions are associated with batch jobs or reports. To run a batch process, you must select a version.

Encoding Name

A code that indicates the name of the encoding that the system uses to produce or consume flat files.