Batch Posting of Journal Lines

Draqos
Draqos Member Posts: 36
edited 2010-06-24 in NAV/Navision Classic Client
Hi everybody,

I have the following request: if a batch of 20 Item Journal Lines is posted, and one of them has an error (e.g. Location Code not specified, item tracking incorrect etc), that the other 19 would be posted and the 1 that has errors remains in the journal (or is transfered) .
Here I see two problems:
1. I was thinking of doing something like this 
REPEAT
  ItemJnlLine2.COPY(ItemJnlLine);
  ItemJnlLine2.SETRECFILTER;
  IF NOT CODEUNIT.RUN(CODEUNIT::"Item Jnl.-Post",ItemJnlLineL2) THEN
    //do something - transfer the line to a new journal etc
UNTIL ItemJnlLine.NEXT = 0
However, the above code does not post anything... any ideea why?

2. I would need to post about 10.000 lines pro batch, and not just 10-20. Does anyone have any ideea if this can be done in a reasonable time frame (2-3 hrs, assuming a top of the line server is used and configured) ?

Any input is really apreciated.
"Simplicity is the ultimate sophistication."
Leonardo DaVinci

Comments

  • Draqos
    Draqos Member Posts: 36
    Corection: The code works :) I tested it falsely. My only question is then related to performance.
    "Simplicity is the ultimate sophistication."
    Leonardo DaVinci
  • FDickschat
    FDickschat Member Posts: 380
    For a data transfer I posted 21.500 Item Journal lines (no item tracking, no reservations, ~12 Dimensions per Line) in ~60mins. incl. fully automatic cost posting (Adjust Cost Item Entries and Post to GL). ~20 mins for the item posting, ~40mins for cost adjustment/post to GL.

    As it was data transfer time no other user was using the system at that time.

    This was on a heavily modified 6.01 DE (Code base 4.03) on SQL2005 x64/WS2003R2 x64. If you would like to have the Specs of the machine/SAN let me know.
    Frank Dickschat
    FD Consulting
  • Draqos
    Draqos Member Posts: 36
    Thank you a lot for the input - I will also be using a heavily customized 6.01 DE.
    I am thinking about doing the posting at night, when no other user is connected, and I was afraid that the job would not be done by the second morning.

    Gruß aus Gütersloh :)
    "Simplicity is the ultimate sophistication."
    Leonardo DaVinci
  • mrsa
    mrsa Member Posts: 35
    I think that you are missining COMMIT in your code.I would write it like this

    REPEAT
    ItemJnlLine2.COPY(ItemJnlLine);
    ItemJnlLine2.SETRECFILTER;
    CODEUNIT.RUN(CODEUNIT::"Item Jnl.-Post",ItemJnlLineL2)
    COMMIT;
    //do something - transfer the line to a new journal etc
    UNTIL ItemJnlLine.NEXT = 0

    If you run it without commit code never exits codeunit and on first error your transaction is rollbacked.

    If i wouldbuild solution like this I would take care for:

    - I would build it in single instance codeunit with timer (timer disabled during posting function and enabled after post routine for 1 line of item journal).
    - codeunit starts with loop thru jounral and calls post routine for 1 item jouranl line with commit (1 commit also after falging current jouran line for posting)
    - before calling post routine I would falg line wich is posted and call commit before posting
    - In Error handler I would enable timer again and call again loop thru journal routine.

    Negative:

    You must realize that in this approach you would have thousnads of item regeister record and G/L registers (if automatic cost post in G/L is setup).


    Positive:

    posting would be faster because of smaller transactions (much less for keep track in case of rollback)
  • Draqos
    Draqos Member Posts: 36
    I figured out the COMMIT, this is why I posted the second comment.
    About the thousands of Item Registers - thanks for pointing it out, I completely missed that part. I'll see if the customer can live with that - if not i will change the mechanism of Item Register creation for this codeunit.
    Fortunately we will not use automatic cost posting.

    Once again, thanks.
    "Simplicity is the ultimate sophistication."
    Leonardo DaVinci
  • kapamarou
    kapamarou Member Posts: 1,152
    I think that COMMIT is an expensive command. I'm not sure that performing 20000 commits is a pretty good idea.

    Commit should be used to separate "logical" transactions. If each record is a different "transaction" but you use commit for performance then maybe it might be better to use it with a counter, for example committing every 100 records.
  • mrsa
    mrsa Member Posts: 35
    You are right, but I think that this is example where one line is one logical transaction. In case of posting routine the commit comes to register creation. If register is problem and if it is one time job: registers could be deleted and merged to one (less time then codeunit investigatin for registers creations and modification).

    I think that problem in this case is performance and avialibilty.
    If you post 20000 lines in night and one line is wrong suppose last (check journal is passed,and it cracks in post) you spent double time (post and rollback) and did nothing (System is unaviable for posting to oteher users).

    If you post it row by row, bad lines are left and you can check it and post it.

    If you have in this procedure and some kind of timeout, journal could be posted, and system is live to other people post requests.

Categories