Batch Posting of Journal Lines

Draqos

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.

Draqos

Corection: The code works I tested it falsely. My only question is then related to performance.

FDickschat

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.

Draqos

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

mrsa

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

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.

kapamarou

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

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.