Best way to split check line and post line of invoice

n.topalov

Hello,
I have an idea about posting an invoice that I want to do.
Basically I have a client that post about 1000 invoices per day. They also want to be able to start reports (analysis by dimension) at every moment. So since they have about 20 ladies that only post invoices we have a locking issue. My idea is to use NAS (I haven't any experience with it) and to make some kind of queue of invoices so when the account lady check the invoice she press F11 but instead of posting it it only checks it and then it goes to the queue (so that I will be sure it will go with no errors after that). Meanwhile the NAS is working background checking the queue if there is something to be post. For the purpose I need to split the check line from the post line as two different process (so that I am able to run only check).
I will be very happy if you advise me which will be best way to do this. Any other advises for solving the locking issue are more than welcome.

P.S. I am using NAV 5.01 SQL

kriki

Using the NAS is the correct way to go.

You can also use JobQueue to have a basis to regularly check and post invoices.

For checking, the best is to create a new codeunit in which you put all possible tests. Which tests? I am afraid you will have to check codeunit 80 (and codeunits called by C80) to see which checks you need to do.

This should make the locking issues less of a problem.
Another way that might help is in : File=>Database=>Alter=>tab Advanced. Put toggle "Always Rowlock" to TRUE.

BUT be careful. Your SQL server needs enough memory for the locking. If it hasn't enough memory, you might create other problems. So you need to check this. And if you have enough memory, test it by enabling the toggle (everyone must log out and then log in again) and see during the day how things are going.

Something else. 1000 invoices is not so much. At least if you have only a few lines per invoice. If you have several 100's of lines per invoice, 1000 invoices is a lot.

Another way to mitigate locking problems is to make sure the database is working at maximum performance. So some performance tuning might be necessary. If you search the forum, you can find a lot of info.

rmv_RU

kriki wrote:

Using the NAS is the correct way to go.
You can also use JobQueue to have a basis to regularly check and post invoices.
For checking, the best is to create a new codeunit in which you put all possible tests. Which tests? I am afraid you will have to check codeunit 80 (and codeunits called by C80) to see which checks you need to do.

Using NAS and batch posting sales orders (report 296) is more simple way to go:
First:
You must configure the Job Scheduler and add report 296 into scheduled tasks.
When account lady check the invoice she must release the document by pressing Ctrl+F11 (instead of posting).
Second:
Analyze all errors during posting released invoices and put test routlines from cu 80 into cu 414 only if necessary.
To do this more quickly, you can add "Last Posting Error" field in 36 table and modify 296 report :

IF SalesPost.RUN("Sales Header") THEN BEGIN
......
end
else begin
................
   "Sales Header"."Last Posting Error":=System.GetLastErrorText;
   "Sales Header".modify;
end;

n.topalov

Thanks for the reply.

Yeah the solution with the releasing the order seems good to me. However I still need to analyze all the checks done by cu80 (maybe also codeunit 11,21) so that I am sure that when the order is released it will for sure (or at least more confident) post without problems by the job. I am afraid that there is no easy way to find the necessary checks but have to be done manually very carefully?

Kriki, Yes the invoices have several lines (usually no more than 15) but posting is set up with automatic post costing(sometimes for such invoice it created 50 lines in GL entry) and also Updating analyze view by dimension during posting. This slow up the process and sometimes we are locked. I am also wondering to increase the locking timeout (File->Database->Alert->Advanced). I guess it gives more time to the system to wait until the tables are unlocked and less chance to the user to get locked.

rmv_RU

n.topalov wrote:

I am afraid that there is no easy way to find the necessary checks but have to be done manually very carefully?

I think it's easy .
If you got error during posting then turn on debugger run posting again and find the check routline. Analize error, configure setup data (Setup tables, Posting Groups, Dimension .. etc) or put check into 414 cu.
Turn off debugger .. and wait for other errors .

n.topalov

Yes this is good point.

I think most of the checks are done when the Sales Order transform to the general/item journal and is past to some of the check codeunits (11,21).

Thanks for the suggestion

kriki

n.topalov wrote:

Kriki, Yes the invoices have several lines (usually no more than 15) but posting is set up with automatic post costing(sometimes for such invoice it created 50 lines in GL entry) and also Updating analyze view by dimension during posting. This slow up the process and sometimes we are locked. I am also wondering to increase the locking timeout (File->Database->Alert->Advanced). I guess it gives more time to the system to wait until the tables are unlocked and less chance to the user to get locked.

It would be best NOT to do that online, but do it in batch during the night. As you can see, it gives a lot of performance/locking problems.

Instead of increasing the locktimeout, you might try to do some SQL tuning to see if you can speed up the posting process.

n.topalov

Thanks for you comments and advises.

Okay. Sorry I forgot to mention that the client is not working live with the system yet, but we have made several posting tests in the office (with out server which i suppose will be worst than the client) with my colleges and got locked when 5-6 colleges post at a time.

So at this point we are able to make any set up now. I have call with client again and understand that actually there about 2000 invoices per day. So my idea is manly to move all posting process in background but during the day (not a over night schedule job)

SQL tunnng could be done but I guess we will have to figure out some additional way of posting.

kriki

But maybe, you can move the cost posting and analysis view update to a nightly batch. This will really help performance of invoice posting during the day.

n.topalov

Yes, for sure. I am trying to convince now out client but maybe I won't succeed because they are used to make a lot of actual reports daily.