Page 2 of 2 FirstFirst 1 2
Results 11 to 15 of 15

Thread: GL Upload from Excel

  1. #11
    Hello carterjde,

    I am helping my client import GL and AP data from CSV files (from Concur). Can you tell limitations you saw with Orchestrator and AIS? Client is on release 9.2.

  2. #12
    Performance: Smaller journal entries are not a problem. When we got over 1000 lines, it slowed down significantly.

    Quirks: The AIS form service requests are like a remote control to the user interface (UI), so you need to pay attention to how the UI responds to data. Certain fields are visible/editable under certain conditions, so you need to issue multiple requests to get the state of the form before punching the data in. If one of the JEs fails, you can't just continue with the next JE. The data has to be "reset" to start over. In AP, if there is an error in the pay item, you can't process the next voucher right away. The header is locked. You have to cancel and start over, and the next voucher will be in a different batch.

    Unit of work: An external file (like the one you have from Concur) should be treated as one unit of work, so ideally, partial uploads should not be allowed.
    There are some challenges with error handling as well, especially when you want to collate an error to a line in the file.

  3. #13
    Thanks for the detailed response. We came across some of these during our analysis.
    EnterpriseOne 9.2

  4. #14
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,370
    I wrote a UBE that picks up a CSV from a mapped network folder and parses the data to a worktable.
    Then you go from there


    in order to bring the file pointer back into the UBE for future reads
    00012 | -FetchNextLineFromFlatFile(B76B0220.FetchNextLineFr omFlatFile)
    | VA rpt_FullFilePath_PTH [PTH] -> szFlatFileName [PTH]
    | VA rpt_GenericLongFilePtr_GENLNG [GENLNG] <> idFilePtr [GENLNG]
    | VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1] <- szFlatFileRecord [APTA]
    | "100" -> mnRecordLength [RECL]
    | VA rpt_cFileReadError_EV01 [EV01] <- cErrorCode [EV01]


    // Loop through the CSV file until the last row has been read
    00015 | -While VA rpt_cFileReadError_EV01 [EV01] is not equal to "1"

    Do Custom Section(RS Get Next Field from CSV Record)
    (do this for each field)
    VA rpt_Field_To_Create_Worfile = rtrim(ltrim([VA rpt_szDTA1_FieldValue [DTA1]], <Blank>), <Blank>)

    Insert target table when done

    Pass in CSV file pointer. No need to bring it back on subsequent reads
    00162 | | -FetchNextLineFromFlatFile(B76B0220.FetchNextLineFr omFlatFile)
    | | VA rpt_FullFilePath_PTH [PTH] -> szFlatFileName [PTH]
    | | VA rpt_GenericLongFilePtr_GENLNG [GENLNG] -> idFilePtr [GENLNG]
    | | VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1] <- szFlatFileRecord [APTA]
    | | "100" -> mnRecordLength [RECL]
    | | VA rpt_cFileReadError_EV01 [EV01] <- cErrorCode [EV01]
    00163 | End While



    Section: Get Next Field from CSV Record [3]
    Event: Do Section
    Event GUID: 71f25299-1a9a-4657-abf7-ed3e618de000
    00001 //
    00002 // This code keeps a running moving count of where it is in the parsed data
    00003 // and actually chops it off field by field as it goes along. This way it is
    00004 // only every processing the first field in the remaining data
    00005 //
    00006 VA rpt_szDTA1_FieldValue [DTA1] = <NULL>
    00007 VA sec_mnMATH01_CharPosition [MATH01] = <NULL>
    00008 //
    00009 -FindCharPositionInString(B7500150.FindCharPosition InString)
    VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1] -> szString [BDS4]
    "," -> cCharToFind [EV01]
    "0" -> mnStartingPosition [MATH80]
    VA sec_mnMATH01_CharPosition [MATH01] <- mnPositionFound [MATH80]
    00010 //
    00011 -If VA sec_mnMATH01_CharPosition [MATH01] is less than "0"
    00012 | VA rpt_szDTA1_FieldValue [DTA1] = VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1]
    00013 -Else
    00014 | VA rpt_szDTA1_FieldValue [DTA1] = substr([VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1]], "0", [VA sec_mnMATH01_CharPosition [MATH01]])
    00015 | //
    00016 | VA sec_SubstStart_INT01 [INT01] = [VA sec_mnMATH01_CharPosition [MATH01]]+1
    00017 | VA sec_SubstrFor_INT01 [INT01] = length([VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1]])-[VA sec_mnMATH01_CharPosition [MATH01]]
    00018 | //
    00019 | // Substr (DataField,StartPos,ForLength)
    00020 | VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1] = substr([VA rpt_F574211AGTSText01_Y57TXT1 [Y57TXT1]], [VA sec_SubstStart_INT01 [INT01]], [VA sec_SubstrFor_INT01 [INT01]])
    00021 | //
    00022 End If

    I can send you the whole UBE if you like?

  5. #15
    Thanks John. My client has similar UBEs for other uploads. We are looking for a more real time solution with better error handling, just like carterjde described earlier in this thread.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.