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.

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.

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.

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 


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 


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.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().

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.

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)

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.

  • 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.
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

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. 

Sunday, 29 January 2012

Financial Consolidations - Dynamics Ax 2009

Financial Consolidations process:

3 steps to perform consolidation in Microsoft Dynamics Ax 2009.

  1. Consolidation company setup
  2. Subsidiary company setup
  3. Perform consolidation

I) Consolidation company setup:

Consolidation company creation:  Consolidation designated company will not be available for any operational process except having consolidation entries posted in here.

Path: Dynamics Ax > Administration > Common Forms > Company accounts
General Ledger Parameters setup:

Path: Dynamics Ax > General Ledger > Setup > Parameters > Ledger tab
Set value "Consolidated company accounts ' = True

System account:
Path: Dynamics Ax > General Ledger > Setup > Posting > System accounts

Balance account for consolidation differences
Profit & loss account for consolidation differences.
Currency and Exchange Rates:

Path: Dynamics Ax > General Ledger > Setup > Exchange rates > Consolidate tab

The Exchange rates form displays an additional tab, Consolidate, when you select the consolidated company accounts field in the General ledger Parameters form.  Closing rate field is used for balance sheet accounts and the value in the Average rate field is used for profit and loss accounts. Add exchange rates for each currency and specify 'Closing rate' & 'Average rate' for all currencies in this form (Overview tab will display all currencies, specify exchange rates & select each one of them and click Consolidate tab and consolidation rates).

Chart of Accounts:
Path: Dynamics Ax > General Ledger > Common Forms > Chart of accounts details

Closing (or) Average:  Use Closing or Average options for utilizing rates specified in 'Consolidate tab' of the exchange rates form.
Historical : Use Historical to utilize exchange rates specified in the setup.
This process completes consolidation company setup for consolidations in Dynamics Ax 2009.

II)  Subsidiary Company:

The subsidiary setup depends on chart of accounts setup, so following are possible alternative options.

Chart of Accounts Option 1: Identical Chart of Accounts

When this is the situation, the Consolidation account field is selected in the Consolidate Online form during the consolidation process to transfer the balances to the correct accounts.

Chart of Accounts Option 2: No Consolidated Chart of Accounts

In this scenario consolidated company does not have chart of accounts, here two alternatives to perform consolidation. 

  • Enter a manual mapping on the Chart of Accounts in the subsidiary. This causes the mapped accounts to create automatically.
  • Do not enter any mapping. This causes the Chart of Accounts to be created in the consolidated company when data transfers from the subsidiary.
Note: This method is not appropriate for consolidation of a foreign currency subsidiary because conversion principles cannot be applied.

Chart of Accounts Option 3: Different Chart of Accounts

Having different chart of accounts in subsidiary & consolidated companies must be mapped to the consolidated company.

 Map Chart of Accounts: (This process should be done for all subsidiary companies)

 Path: Dynamics Ax > General Ledger > Common Forms > Chart of account details
Map Dimensions: (This process should be done for all subsidiary companies chart of accounts)

Path: Dynamics Ax > General Ledger > Common Forms > Dimensions

This completes subsidiary company setup and Consolidations.
III) Perform a Consolidation:

Options to perform consolidation:

  • From the same database using the online option
  • From and, or to another database using the Import/Export option

Before you perform a consolidation at the close of a period, ensure that the period closing preparatory activities are performed, but do not close the subsidiary accounts until the consolidation is completed.

Consolidation, Online:

Path: Dynamics Ax > General Ledger > Periodic > Consolidate > Consolidation, Online
Dimension tab: Gives option to select none, dimension, group dimension, company accounts and account.  Select 'Group dimension' to rollup dimension transactions to consolidation company dimension values (Group dimensions must be specified for all dimension values in all subsidiary companies for this option)

Companies tab:

Share: Indicate the percentage of the selected company accounts to be included in the consolidation. This is used if the consolidated company owns part of the subsidiary.

Note: Share field is not available or relevant on the consolidation, Export form

For the selected subsidiary company account - if the subsidiary company currency differs from that of the consolidated company - select the type of account that consolidation differences are posted to:

  • Profit & loss – Differences are posted to the consolidated company ledger account that is indicated for the Profit & loss account for consolidate Financial Consolidations - Dynamics Ax 2009 (Part 1)on differences value in the Posting type field on the System accounts form of the consolidated company.
  • Balance – Differences are posted to the consolidated company ledger account that is indicated for the Balance account for consolidation differences value in the Posting type field on the System accounts form of the consolidated company.
  • You must make an appropriate selection according to the accounting practice of the country/region of the consolidated company.

Eliminations tab:

Note: Elimination rules can be applied here.

Click button 'OK'

Then validate balances through General Ledger > Common Forms> Chart of account details (Select respective account and validate the balance of CEC & CEU companies, notice the balance in consolidation company).

Then validate balances through General Ledger > Common Forms> Chart of account details (Select respective account and validate the balance of CEC & CEU companies, notice the balance in consolidation company).

To  eliminate internal transaction. You have to set up the elimination rules.
General ledger > Setup > Posting > Ledger elimination rule
When you run consolidate
General ledger > Periodic > Consolidate > Consolidation, Online.
You have to select the elimination rule in tab Elimination ( should be Proposal post instead of Posting).
After running that proccess The system will automatically generate the elimination transacion . You can inquiry and check it before posting.

Monday, 16 January 2012

Dynamics Ax 2009 Financials : Year-End process

Please find the Year-end closing process below:

Step 1: Create a new fiscal year
Path: Dynamics Ax > General Ledger > Setup > Periods > Periods
Click button 'Create new fiscal year'

Click 'OK'

12 monthly lines get created with open status.

Step 2: Control transactions in a closing period

Path: Dynamics Ax > General Ledger > Setup > Periods > Periods

A closed period cannot be reopened. Therefore, permission to close periods and years should be highly restricted.

Select 'Stop' from list and this prevents transactions posting in the system for that period, perfom same operation for 12 monthly periods.
Step 3: Change module status
Select the appropriate module and set the user group required access by selecting from list view in the above screen.

Step 4: Create Closing sheet
Path: Dynamics Ax > General Ledger > Periodic > Fiscal year close > Closing sheet.

Set field 'Closing sheet'    = 06/30/2011
Set field 'Name'              = Closing Sheet as on 06/30/2011
Set value 'Posting layer'   = Current
Set value 'Period code'     = Closing
Note: Only 'Normal' and 'Closing' options can be used in closing sheet.  'Opening' option can be used for beginning balance transaction.
Click button 'Closing accounts'
Click button 'Load balances'
Notice the accounts and balances got updated in the screen.
Step 5: Make adjustments or transfers between accounts
Select an account line from the loaded balances and click button 'Transfer'
Note: For system reconciled accounts 'transfer' button would be disabled.

Set field 'Transaction text' = Closing transfer
Set field 'Amount' = -1500 (difference amount which needs to be transferred)
Select field 'Offset account' = 999999  (The offset account could be any relevant account, here I took example of 'Error account - 999999')
Click 'Save'
Close form 'Transfers'
Form 'Closing accounts' becomes active
After the necessary transfers or adjustments are complete, click the Post button from the Closing accounts form to post the closing sheet. The closing sheet only posts to the closing period.

At this time, also run reports and verify results before you close the period and transferring ending balances into the new year as opening balances.

Note: To post the closing sheet, you must open the closing period in the Periods form. After you post the closing sheet, ensure that you change the period back to Stopped.

Step 6: Transfer Opening Balances & Setup Fiscal year close parameters

Enable GL Parameters:

Path: Dynamics Ax > General Ledger > Setup > Parameters > Ledger tab

Fiscal year close:

Delete close-of-year transactions during transfer : If this check box is selected, opening transactions and system-generated closing transactions that exist for the year to be closed are deleted when the transfer is processed again.
Create closing transactions during transfer : By enabling this option, have the system create closing transactions when running the opening transactions job.
Set period status to year closed : Select this check box to display a status of Year closed for all fiscal periods for the year that is being closed.
Voucher number must be filled in : If this check box is selected, a voucher number must be entered when opening transactions are created for a new fiscal year.
Note: If a period is closed, adjustments, which may be required by the auditor, are not possible.
Transfer opening balances procedure:
Path: Dynamics Ax > General Ledger > Periodic > Fiscal year close > Opening transactions
Click tab 'Dimension'
Dimension selection can be done here to transfer opening balances

Click button 'OK'

Please find the report of Closing transactions & Opening transactions.