Thursday 23 February 2012

Margin Vs. Markup in dynamics Ax


Item Pricing:
Item price is the default price in item master. MS dynamics Ax stores 3 prices for each item:
  1. Base purchase price
  2. Base cost price
  3. Base sales price
Path: Inventory > items details > price/discount tab

The purchase or cost prices are your cost. They can be manually entered or updated from the last purchase price or the last production receipt of the inventory. The sales order price is the selling price if trade agreements do not exist. A trade agreement can override from the price listed here.
Sales price can be manually entered or it can be calculated as the margin percentage or markup percentage from cost.  Since the item displayed two different costs, the purchase order price and the item price, the user can decide which cost should be used for markup.

Item Pricing option:


The setup tab of item master is where pricing option are determined.

If latest purchase price is checked, then the purchase order price on price/discount tab will be updated with most recent purchase price each time an invoice is posted against the purchase order.

If latest cost price is checked, then the cost price on price/discount tab will be updated with most recent price when the production is completed and item is received into inventory. The cost price is used in inventory and production modules.

The sales price model contains 3 options:
  • None: manually enter selling price on the price/discount tab.
  • Contribution ratio: calculate price based on a desired margin.
                              Price = (cost / 1- contribution margin) 
  • Misc. charges pct: calculate price based on a markup percent.
      Price = (cost + cost x Misc. charges pct.)


If the sales price model is set to Contribution ratio or misc charges pct. Then the base price is use to determine whether the purchase or cost price from the price/discount tab should be use. This would be the basis of markup. The contribution ratio and misc charges pct. fields are used with respective options selected for the sales price model. 

Margin vs. Markup
What is the difference between Contribution ratio and Misc. charges pct. ?

Example: Cost price $100

Price = cost / (1- contribution ratio)
Price = 100 / ( 1- 0.20)
Price = 100/0.80
Price = $125
Price = cost + (cost x Misc charges pct.)
Price = 100 + ( 100 x 0.20)
Price = 100 + 20
Price = $120


If contribution ration is selected as sales price model, then the contribution ratio must be enter. If misc charges pct is selected as sales price model, then the misc charges pct field must be enter. 
The price will be calculated from the source selected as the base price either purchase or cost price. In this example we have selected cost price as base. Microsoft dynamics ax will find cost price listed on price/discount tab.

The contribution ratio is margin and Misc charges pct is markup. The percentage entered in fields is whole numbers. Divide the number by 100 to arrive at decimal number that is used in calculation shown here.

If the price is calculated from cost, then the sales order price on price/discount tab will automatically updated when the record is saved, the date of price will also be updated. 








Thursday 16 February 2012

Picking, Registration, Reservation, Marking..What’s the Difference?


The different ways of selecting inventory for issue and receipt and their effects on inventory are a source of confusion for many Microsoft Dynamics AX users. The common denominator between them all is this: They are all ways that the user specifies exact inventory (specific to dimension) to be issued or received. Below is a brief definition of each function and an example of its use. The examples shown are not the only ways to perform each function in AX, but a simple demonstration of the functionality.


Picking in AX is the process of designating which items will be issued based on storage dimension, such as location, batch or serial. Usually, picking in the system coincides with the business process of picking inventory for inventory issue, such as shipment.


Example:
Sales Order 101278 is for 100 High Definition DVD Players. The DVD Players are stored in more than one Location. The user can pick inventory from the specific location from the Sales Order line by clicking the Inventory button and selecting Pick from the menu.
In the Pick form, you can specify the Location from which to pick in the upper Transactions window before clicking Auto-create or in the lower Pick Now window after clicking Auto-create.
The act of picking reduces the inventory on hand.
Registration is the process of recording a receipt for the purpose of tracking by storage dimension such as location, batch or serial. Usually registration in the system coincides with the business process of recording a receipt, for example, during the receipt of purchased goods.

Example:
Purchase Order 000391 is for 200 Standard Digital Video Recorders. The Recorders are received into a Location called Inbound Dock, and they are stored in Pallets. You can register the receipt of the goods into the location and assign the pallet number from the Purchase Order line by clicking the Inventory button and selecting Registration from the menu.
The resulting Registration form works similarly to the Pick form. You can specify the dimensions related to the receipt. In this example case, the item is tracked by Pallet and Location, so you would enter this information into the Registration form.
The act of registration physically receives the inventory, and it will appear available in the On Hand form.

Transaction showing physical receipt:

On Hand form showing 200 on hand after registration:
Reservation is the process of holding or reserving specific inventory for a particular Sales Order. You can set AX up to reserve inventory automatically (to ensure first come, first served) or you can do it manually from the Sales Order Line. The inventory you reserve can either be on hand or ordered but not yet received.

To manually reserve inventory on the Sales Order Line, click the Inventory button and then Reservation.
In the Reservation form, you can specify the amount to be reserved.
The act of reserving inventory that is currently on hand reduces the Available Physical inventory in the On Hand inquiry form. If you reserve inventory that is ordered but not yet received, the quantity will appear in the Ordered Reserved field of the On Hand form. Both types of reservation are calculated into the Total available.
Marking is the process of linking an issue transaction to a receipt transaction for the purpose of specifying the lot issued. This will allow you to use the exact cost of the inventory instead of following the costing setup.


Example:
Sales Order 101278 has a line for 100 High Definition DVD Players. This is a purchased part. Instead of charging the usual $133 for this item, the vendor sold it for $123. In order to capture the reduced cost, you want to link the Sales Order with the exact Purchase Order where the unit price was $10 less than normal. This is Purchase Order 000390. You can mark the Purchase Order from the Sales Order form by clicking the Inventory button on the Sales Order line followed by Marking on the menu.
In the Marking form, you can select the receipt by clicking the Set Mark Now checkbox. The transactions against which you can mark are listed by Order type and number.
The act of marking ensures that the cost associated with the receipt is the one recorded with issue. Once the Sales Order is invoiced, you can see that the cost on the transaction is the unit price from the marked Purchase Order.
PO 000390:
Inventory Transactions from Sales Order 101278:

Basically the feature when doing the reservation/marking is the same. However, the way that the transactions are treated afterwards are different. 

Ex: if your cost model is FIFO 

Reservation 

01/06/06  PO1  1  pcs  100$ 
10/06/06  PO2  1  pcs  200$ 
21/06/06  SO1 -1  pcs  ? 

If you have reserved the salesorder against PO2 then the FIFO is calculated as normal, and the costprice going out of the inventory is 100. 

Month end inventory value = 200 

Marking 

01/06/06  PO1   1  pcs  100$ 
10/06/06  PO2   1  pcs  200$ 
21/06/06  SO1  -1  pcs  ? 

If you have marked against PO 2, then the FIFO calculation will treat markings as a 1-1 setting. This means that when closing the inventory the FIFO calculation will not look at the salesorder and PO2, because these have allready been settled. and the cost of the salesorder is 200. 

Month end inventory value = 100 

This is one of the major differences between marking and reservation. 

Also depending on the inventory dimension group, then the system can re-reserve against another lot of the same item, but the system cannot mark against other items automatically. 


To summarize quickly, Picking is for Issue, Registration is for Receipt, Reservation is to hold inventory, and Marking is to link a specific receipt to an issue.

Monday 13 February 2012

refresh, reread, research, executeQuery - which one to use?

I got the useful article posted by Vanya Kashperuk 26th March 2010.


X++ developers seem to be having a lot of trouble with these 4 datasource methods, no matter how senior they are in AX.

1. Common mistakes
Often, developers call 2 of the mentioned methods in the following order:

formDataSource.refresh()
formDataSource.research()


or

formDataSource.reread()
formDataSource.research()


or

formDataSource.research()
formDataSource.executeQuery()


or

formDataSource.research()
formDataSource.refresh() / formDataSource.reread()


All of these are wrong, or at least partially redundant.
Hopefully, after reading the full post, there will be no questions as to why they are wrong. Leave a comment to this post if one of them is still unclear, and I will try to explain in more detail.

2. Refresh

This method basically refreshes the data displayed in the form controls with whatever is stored in the form cache for that particular datasource record. Calling refresh() method will NOT reread the record from the database. So if changes happened to the record in another process, these will not be shown after executing refresh().

refreshEx
Does a redraw of the grid rows, depending on the optional argment for specifying the number of the record to refresh (and this means the actual row number in the grid, which is less useful for AX devs). Special argument values include -1, which means that all records will be redrawn, and -2, which redraws all marked records and records with displayOptions. Default argument value is -2.
This method should be used sparingly, in cases where multiple rows from the grid are updated, resulting in changes in their displayOptions, as an example. So you should avoid using it as a replacement for refresh(), since they actually have completely different implementations in the kernel.
Also, note, that refreshEx() only redraws the grid, so the controls not in the grid might still contain outdated values. Refresh() updates everything, since this is its intention. 

3. Reread

Calling reread() will query the database and re-read the current record contents into the datasource form cache. This will not display the changes on the form until a redraw of the grid contents happens (for example, when you navigate away from the row or re-open the form).
You should not use it to refresh the form data if you have through code added or removed records. For this, you would use a different method described below.
How are these 2 methods commonly used?
Usually, when you change some values in the current record through some code (for example, when the user clicks on a button), and update the database by calling update method on the table buffer, you would want to show the user the changes that happened.
In this case, you would call reread() method to update the datasource form cache with the values from the database (this will not update the screen), and then call refresh() to actually redraw the grid and show the changes to the user.

Clicking buttons with SaveRecord == Yes
Each button has a property SaveRecord, which is by default set to Yes. Whenever you click a button, the changes you have done in the current record are saved to the database. So calling reread will not restore the original record values, as some expect. If that is the user expectation, you as a developer should set the property to No

4. Research

Calling research() will rerun the existing form query against the database, therefore updating the list with new/removed records as well as updating all existing rows. This will honor any existing filters and sorting on the form, that were set by the user.

Research(true)
The research method starting with AX 2009 accepts an optional boolean argument _retainPosition. If you call research(true), the cursor position in the grid will be preserved after the data has been refreshed. This is an extremely useful addition, which solves most of the problems with cursor positioning (findRecord method is the alternative, but this method is very slow).

5. ExecuteQuery

Calling executeQuery() will also rerun the query and update/add/delete the rows in the grid. The difference in behavior from research is described below. 
ExecuteQuery should be used if you have modified the query in your code and need to refresh the form to display the data based on the updated query.

formDataSource.queryRun().query() vs formDataSource.query()
An important thing to mention here is that the form has 2 instances of the query object - one is the original datasource query (stored in formDataSource.query()), and the other is the currently used query with any user filters applied (stored in formDataSource.queryRun().query()). 
When the research method is called, a new instance of the queryRun is created, using the formDataSource.queryRun().query() as the basis. Therefore, if the user has set up some filters on the displayed data, those will be preserved.
This is useful, for example, when multiple users work with a certain form, each user has his own filters set up for displaying only relevant data, and rows get inserted into the underlying table externally (for example, through AIF).
Calling executeQuery, on the other hand, will use the original query as the basis, therefore removing any user filters.
This is a distinction that everyone should understand when using research/executeQuery methods in order to prevent possible collisions with the user filters when updating the query.

RecVersion - Dynamics Axapta and OCC (Optimistic Concurreny Control)


Definition
Optimistic Concurrency Control (OCC) helps increase database performance. Pessimistic Concurrency Control locks records as soon as they are fetched from the database for an update. However, Optimistic Concurrency only locks records from the time when the actual update is performed.
Pessimistic concurrency was the only option available in Microsoft Axapta 3.0 (now a part of Microsoft Dynamics). You can now choose which concurrency model to use—optimistic or pessimistic.

RecVersion is a 32-bit signed INTEGER. But it will never get a negative value assingned through the AOS.

Advantages
  • Fewer resources are used to hold the locks during the update process.
  • Records are locked for a shorter length of time.
  • Records remain available for other processes to update if they have been selected from the database but haven't yet been updated.
Disadvantages
The disadvantage of using OCC is that the update can fail if another process updates the same record. If the update fails, it must be retried. This can lead to a reduction in database performance.
How to catch Update Conflicts
Update Conflicts can be handled by catching the UpdateConflict and UpdateConflictNotRecoveredevents.
This pattern is used across AX API's for retrying the update conflicts and if it exceeds the maximum retry count then UpdateConflictNotRecovered exception is thrown.
e.g. AOT\Classes\PurchTableType\delete

Update Conflicts and recversion field
Update conflicts are detected by the kernel. It checks the value of the recVersion system field on the table at the time the record is selected for update. This value is matched to the value of the field when the record is subsequently updated. The default value of recVersion is 1. This is changed to a random value when a record is updated.



Let’s assume we have a table with data like this

Column A
Column B
A
1
A
2
B
3
B
4
C
5
D
6


We create a non unlike index on Column A.

Now we have 2 independent AX clients with both want to update a record.

User 1 : select forupdate table where ColumnA =='A' && ColumnB == '1';
User 2 : select forupdate table where ColumnA =='A' && ColumnB == '2';

When SQL processes the query it will use the non unique index on column A. Next it will do a page scan on all find records to search for ColumnB. Because we are doing an update all records in that page are locked.
so User1 locks also the record with ColumnB = '2'

User 2 has to wait till user 1 is ready. This called page locking.

In Ax 4.0 a new option Optimistic concurrency is introduced. This option allows that user 2 can continue.

OCC in ax 4.0 is done by adding a new collumn to every table. The name of this column is recversion.
So in above case user 2 wants to update the record in the same page. When the update is executed, the system checks if the recversion of the record in the database is equal to the recversion on the moment the records was selected. If that was the case. The update succeed.