dynamicsusers: Item Tracking upgrade from 3.x to 5.0

[Blog bot]

Recently we completed a very complex upgrade from version 3.10 to 5.0. This project was done together with Liberty Grove Software in the USA.


The company involved has a period where they are extremely busy. In that period the performance was terrible. It was holding up the business.


We examined the database and decided to do a full upgrade to 5.0 and migrate them to SQL 2005.


The most difficult part was Item Tracking. This was highly customised in their situation. I've learned a lot during this upgrade and some of those things I want to share.


In the old database there were two tables.

• 
  6500 - Item Tracking Line
• 
  6501 - Item Tracking Entry

The 6500 table contained all open document information; 6501 had posting information.


In 5.0 this has been replaced with 3 tables. two existing and 1 new

• 
  336 - Tracking Specification
• 
  337 - Reservation Entries
• 
  32 - Item Ledger Entries

This is not per-se a bad change, but I do not like using the 337 table. This table is already used by to many different funcionalities in NAV. I do like using the ILE as single posting entry instead of 6501.


So how does it work.


When you assign a tracking number on a new document, a new record is created in table 337. This is connected to the master line by some fields:



* 10 Source Type Integer
* 11 Source Subtype Option
* 12 Source ID Code 20
* 13 Source Batch Name Code 10
* 14 Source Prod. Order Line Integer
* 15 Source Ref. No. Integer


Depending on what is the source table some fields are left empty. Please note that the 6500 Item Tracking Line had the same constuction, difference however is that the old version had another field: The source line table record had a pointer to the 6500 table as well. This has been dropped. Probably because of reduncancy and locking issues.


The record stays in 337 as long as the document is not posted. Once the document is posted as receipt, the record in 337 disapears. The ILE is created. For some reason, a new record is created in table 336. This record looks like 337 but has a different key. The key is equal to the key of the ILE. Once the document is invoiced the 336 gets checked to the ILE and dissapears as wel.


I really am puzzled why this complex structure exists. The form to assign tracking (6510) has become much more complex since it supports both 336 and 337 records in one form based on temporary data. It is your worst nightmare,


We've done quire a few redesign in order to make it work. Problem was that the customer added about 50 custom fields to both the 6500 and 6501 table.


I am curious to hear other experiences with upgading this.


Result is a better database. The performance is perfect. So far we only measured two very short blocking situations during the first production week and no deadlocks or locktimeouts






Подробнее... http://dynamicsuser.net/blogs/mark_brummel...3-x-to-5-0.aspx

[RedFox]

Цитата:

Сообщение от Blog bot

Recently we completed a very complex upgrade from version 3.10 to 5.0. This project was done together with Liberty Grove Software in the USA.

Так, ради инфы - в 5.0 SP1 еще переделали тассировки