Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: GL Upload from Excel

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    GL Upload from Excel

    We are looking for solutions/workarounds for our month end uploads of 5k+ line Journal Entries to E1. We have tried the built in options: Z-tables, AIS/Orchestrator and both have some limitations. Appreciate any input on this.

  2. #2
    Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    63
    Hi, and welcome to JDEList!
    It's usually a good idea to add your JDE version and platform information into your signature or at least into the test of your question, as the capabilities vary considerably from the oldest to the most recent versions of JDE. Regardless, my gut instinct whenever a large import into the G/L needs to be made is to use the Z file process. How have you been trying to load your z file, with a grid upload or a flat file import? The grid upload has a limit that can be modified - my company has the maximum set at 3000 rows for example. If you export Excel into a CSV or other flat file format, a table conversion works well for loading the z file, just be wary of embedded punctuation in description or other string columns (especially the same character being used as a field delimiter).
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  3. #3
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,579
    I agree with Kim, for a large amount of JEs like you are talking about I would think the Z-Tables would be your best option. What limitation are you running into with Z-tables?
    Brian Oster
    Application Development Manager
    E1: 9.0 (TR9.1.5.1) / 9.2 (TR9.2.2.2)
    JAS/BSSV: Weblogic 12.1.2 / Weblogic 12.2
    ES: Win2008 / Win2016
    DB: MSSQL 2014 / 2016
    WebDev Client: Win7Pro / Win10Pro

  4. #4
    Thank you for your responses Kim and Brian!

    We are on E1 9.2, Tools Release 9.2.3, Windows servers using Oracle database.

    We have two use cases: Users uploading data manually and an automated process. In both cases, Z-table process is cumbersome for us: It is a disconnected process with multiple steps, and the error handling is through the Work Center. The upload has multiple journal entries and cross referencing the messages to the line in the file can take time.

  5. #5
    You mentioned Excel in the title of this thread. Are both the user and automated uploads in Excel or a different format?
    Regards,

    Hari Sharma
    Aellius
    EnterpriseOne: Integration (.Net, Web Services) | Output Management | Monitoring

  6. #6
    Member
    Join Date
    Feb 2002
    Location
    Florida, USA
    Posts
    179
    One way to handle the multi-step z-file process is to create a custom program that reads the external data, parses, scrutinizes and then populates the z-file table. It then calls the z-file processor (and version) which in turn creates the journal entries (F0911). In the initial stages of developing the custom program, you will need to test out all possible scenarios that may cause errors. The source of the data format must always be the same and theoretically should always be. You can also have the journal entry batch program run automatically in the same job stream but this is dependent on user requirements. Most of my past clients preferred to be able to review, approve, and post as they do with manually entered journal entries. The above custom program should have an error listing just in case. But remember, if the original format of the source is always the same and you have rigorously tested out all scenarios there should be no errors. If so, keep working at it. There should also be tight controls on making sure the source of the data remains in the same format. My last client simply chose an option on an E1 page and voilŕ, thousands of journal entries appeared a couple minutes later!
    JD Edwards E1 and World
    Blasenheim Solutions, LLC
    Technical and functional consultant specializing in Financials

  7. #7
    The user upload is in Excel. The automated one is in CSV.

  8. #8
    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.

  9. #9
    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.

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

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.