How to Import Text File that has UTime (JDEUTime) Data Type into JDE Table

Purpose of Document

Purpose of this document is to explain possible way to import Text file that has Universal Time data to JDE table through standard UBE R47002C (Inbound Flat File Conversion). This document explains detail steps for importing to any JDE tables in EnterpriseOne.

Currently EnterpriseOne Table Conversion Tool does not have data type of UTime (or JDEUTime) to import text file to JDE table. So you will not be able to map text string into JDE table column directly.

Currently, available data types are Date, Number and String.

Available Data Type for Table Conversion

As a result, currently Table Conversion application is not able to handle conversion when both source (input text file) and target (EnterpriseOne table) contain UTime data type.

Through R47002C

The following steps describe one of the ways to perform this operation:

  1. Review and verify text file (Source)
     
    Text file example
     
    Note:

        The first column is entered manually by hand as required by R47002C. For this example, either 1 or A or any value defined in P47002 (See below)
        Text qualifier (single quote) is used even for number in the text file (e.g., AN8 in second column with value of 99999998)
      
  2. Review and verify target table
     
    SQL Format File
     
  3. Locate text file where your network can access. For this example
     
    Locate your text format file
     
    Note: For detail, refer to <Document 1305796.1> E1: 47: Representation of Path for EDI in EnterpriseOne
      
    Now you are ready to run R47002C as described below.
     
  4. Run P47002 (Flat File Cross-Reference) to define Source  (text file) and Target table as below:
     
    Define source and target through P47002
     

    Note:

    • Transaction is UTIME (a UDC, add if does not exist)
    • Direction Indicator = 1 (Inbound from Text file to Database table)
    • Flat File Name = Path with file name and File Extension
    • Record Type = A or 1 or any other value (this value is the first columns in your text file)
    • File Name = Target table name
    • <Internal_Only>If you want to create text file through macro, refer to <Document 1305159.1> E1: 47: Flat File Creation Procedure to Run R47002C - Flat File Conversion (Macro: QuoteCommaExport; Export a Text File with Comma and Quote Delimiters)</Internal_Only>
     
  5. Add a version for R47002C (Inbound Flat File Conversion) because you are implement custom table.
     
  6. Set correct processing option for newly added version above

    Processing Option

    Note: 

    • Enter the transaction: transaction UDC used in P47002, R47002C is using this information. In this case UTIME.
    • Enter field delimiter: , (comma for this example)
    • Enter text qualifier: single quote or double quote matching the actual character used in the text file. R47002C expect all columns to be in string.
     
  7. Run R47002C and verify result

    Outcome

     

Using Business Function to convert to JDEUTIME

Make use of EnterpriseOne standard business function SetUTimeComponents (B0130100 - Set UTime Components) to convert to JDEUTime. It takes Year, Month, Day, Hour, Minute and Seconds as parameters and returns JDEUTime.